Hi all,
I believe to have found a bug in MySQL's union implementation. Can someone
confirm this, please or convince me that this is not a buggy behaviour of
mysql :

UNION seems to behave like DISTINCT by default:

mysql> select 2 c1
-> union
-> select 1 c1
-> union
-> select 2 c1
-> union
-> select 1 c1;
+----+
| c1 |
+----+
| 2 |
| 1 |
+----+
2 rows in set (0.00 sec)

mysql> select 2 c1,1 union select 1 c1,2 union select 2 c1,3 union select 1
c1,4;
+----+---+
| c1 | 1 |
+----+---+
| 2 | 1 |
| 1 | 2 |
| 2 | 3 |
| 1 | 4 |
+----+---+
4 rows in set (0.00 sec)

mysql> select 2 c1,1 union select 1 c1,2 union select 2 c1,3 union select
1,2;
+----+---+
| c1 | 1 |
+----+---+
| 2 | 1 |
| 1 | 2 |
| 2 | 3 |
+----+---+
3 rows in set (0.00 sec)

mysql> select avg(c1),avg(distinct c1),sum(c1),count(c1),count(distinct
c1),count(*) from
-> (
-> select 2 c1
-> union
-> select 1 c1
-> union
-> select 1 c1
-> union
-> select 1
-> ) a
-> ;
+-------+----------------+-------+---------+-------------------+----------+
|avg(c1)|avg(distinct c1)|sum(c1)|count(c1)|count(distinct c1) | count(*) |
+-------+----------------+-------+---------+-------------------+----------+
|1.5000 | 1.5000 | 3 | 2 | 2 | 2 |
+-------+----------------+-------+---------+-------------------+----------+
1 row in set (0.00 sec)

but I would have expected:

+-------+----------------+-------+---------+-------------------+----------+
|avg(c1)|avg(distinct c1)|sum(c1)|count(c1)|count(distinct c1) | count(*) |
+-------+----------------+-------+---------+-------------------+----------+
|1.2500 | 1.5000 | 5 | 4 | 2 | 4 |
+-------+----------------+-------+---------+-------------------+----------+


TIA,

CVH

On 11 Jul, 09:10, CVH <please_do_...@spam.me> wrote:
> Hi all,
> I believe to have found a bug in MySQL's union implementation. Can someone
> confirm this, please or convince me that this is not a buggy behaviour of
> mysql :
>
> UNION seems to behave like DISTINCT by default:
>
> mysql> select 2 c1
> -> union
> -> select 1 c1
> -> union
> -> select 2 c1
> -> union
> -> select 1 c1;
> +----+
> | c1 |
> +----+
> | 2 |
> | 1 |
> +----+
> 2 rows in set (0.00 sec)
>
> mysql> select 2 c1,1 union select 1 c1,2 union select 2 c1,3 union select 1
> c1,4;
> +----+---+
> | c1 | 1 |
> +----+---+
> | 2 | 1 |
> | 1 | 2 |
> | 2 | 3 |
> | 1 | 4 |
> +----+---+
> 4 rows in set (0.00 sec)
>
> mysql> select 2 c1,1 union select 1 c1,2 union select 2 c1,3 union select
> 1,2;
> +----+---+
> | c1 | 1 |
> +----+---+
> | 2 | 1 |
> | 1 | 2 |
> | 2 | 3 |
> +----+---+
> 3 rows in set (0.00 sec)
>
> mysql> select avg(c1),avg(distinct c1),sum(c1),count(c1),count(distinct
> c1),count(*) from
> -> (
> -> select 2 c1
> -> union
> -> select 1 c1
> -> union
> -> select 1 c1
> -> union
> -> select 1
> -> ) a
> -> ;
> +-------+----------------+-------+---------+-------------------+----------+
> |avg(c1)|avg(distinct c1)|sum(c1)|count(c1)|count(distinct c1) | count(*) |
> +-------+----------------+-------+---------+-------------------+----------+
> |1.5000 | 1.5000 | 3 | 2 | 2 | 2 |
> +-------+----------------+-------+---------+-------------------+----------+
> 1 row in set (0.00 sec)
>
> but I would have expected:
>
> +-------+----------------+-------+---------+-------------------+----------+
> |avg(c1)|avg(distinct c1)|sum(c1)|count(c1)|count(distinct c1) | count(*) |
> +-------+----------------+-------+---------+-------------------+----------+
> |1.2500 | 1.5000 | 5 | 4 | 2 | 4 |
> +-------+----------------+-------+---------+-------------------+----------+
>
> TIA,
>
> CVH

UNION always behaves like DISTINCT in any SQL implementation. That's
why there is also UNION ALL.

As the manual so clearly says (http://dev.mysql.com/doc/refman/5.0/en/
union.html)

"The default behavior for UNION is that duplicate rows are removed
from the result. The optional DISTINCT keyword has no effect other
than the default because it also specifies duplicate-row removal. With
the optional ALL keyword, duplicate-row removal does not occur and the
result includes all matching rows from all the SELECT statements."

Don't know how you missed that when you were reading that section
before you posted here!

Captain Paralytic wrote:

> On 11 Jul, 09:10, CVH <please_do_...@spam.me> wrote:
>> Hi all,
>> I believe to have found a bug in MySQL's union implementation. Can
>> someone confirm this, please or convince me that this is not a buggy
>> behaviour of mysql :
>>
>> UNION seems to behave like DISTINCT by default:
>>
>> mysql> select 2 c1
>> -> union
>> -> select 1 c1
>> -> union
>> -> select 2 c1
>> -> union
>> -> select 1 c1;
>> +----+
>> | c1 |
>> +----+
>> | 2 |
>> | 1 |
>> +----+
>> 2 rows in set (0.00 sec)
>>
>> mysql> select 2 c1,1 union select 1 c1,2 union select 2 c1,3 union
>> select 1 c1,4;
>> +----+---+
>> | c1 | 1 |
>> +----+---+
>> | 2 | 1 |
>> | 1 | 2 |
>> | 2 | 3 |
>> | 1 | 4 |
>> +----+---+
>> 4 rows in set (0.00 sec)
>>
>> mysql> select 2 c1,1 union select 1 c1,2 union select 2 c1,3 union
>> select 1,2;
>> +----+---+
>> | c1 | 1 |
>> +----+---+
>> | 2 | 1 |
>> | 1 | 2 |
>> | 2 | 3 |
>> +----+---+
>> 3 rows in set (0.00 sec)
>>
>> mysql> select avg(c1),avg(distinct c1),sum(c1),count(c1),count(distinct
>> c1),count(*) from
>> -> (
>> -> select 2 c1
>> -> union
>> -> select 1 c1
>> -> union
>> -> select 1 c1
>> -> union
>> -> select 1
>> -> ) a
>> -> ;
>>
+-------+----------------+-------+---------+-------------------+----------+
>> |avg(c1)|avg(distinct c1)|sum(c1)|count(c1)|count(distinct c1) | count(*)
>> ||
>>
+-------+----------------+-------+---------+-------------------+----------+
>> |1.5000 | 1.5000 | 3 | 2 | 2 | 2
>> ||
>>
+-------+----------------+-------+---------+-------------------+----------+
>> 1 row in set (0.00 sec)
>>
>> but I would have expected:
>>
>>
+-------+----------------+-------+---------+-------------------+----------+
>> |avg(c1)|avg(distinct c1)|sum(c1)|count(c1)|count(distinct c1) | count(*)
>> ||
>>
+-------+----------------+-------+---------+-------------------+----------+
>> |1.2500 | 1.5000 | 5 | 4 | 2 | 4
>> ||
>>
+-------+----------------+-------+---------+-------------------+----------+
>>
>> TIA,
>>
>> CVH
>
> UNION always behaves like DISTINCT in any SQL implementation. That's
> why there is also UNION ALL.
>
> As the manual so clearly says (http://dev.mysql.com/doc/refman/5.0/en/
> union.html)
>
> "The default behavior for UNION is that duplicate rows are removed
> from the result. The optional DISTINCT keyword has no effect other
> than the default because it also specifies duplicate-row removal. With
> the optional ALL keyword, duplicate-row removal does not occur and the
> result includes all matching rows from all the SELECT statements."
>
> Don't know how you missed that when you were reading that section
> before you posted here!
Hmm, You're right. How could i have missed that.
Thanks