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 will only return distinct rows. This is according to spec and to
the SQL Standard. To avoid this, use UNION ALL instead of UNION. Try
that with your queries and you'll see that this will do the trick. This
is, as I said, in accordance with the standard and the way all SQL based
databases work.

Quoting SQL 2003 section 4.10.6.2:
"MULTISET UNION is an operator that computes the union of two multisets.
There are two variants, specified
using ALL or DISTINCT, to either retain duplicates or remove duplicates."
Where UNION DISTINCT is the default if neither DISTINCT nor ALL is
specified then.

Cheers
/Karlsson
list account 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
>


--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Anders Karlsson (anders@mysql.com)
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
/_/ /_/\_, /___/\___\_\___/ Stockholm
<___/ www.mysql.com Cellphone: +46 708 608121
Skype: drdatabase

On Wednesday 11 July 2007 00:34, Anders Karlsson wrote:
> UNION will only return distinct rows. This is according to spec and to
> the SQL Standard.

And of course, to no one's surprise, this also matches the mathematical
definition of union:

j

--
Joshua Kugler
Lead System Admin -- Senior Programmer
http://www.eeinternet.com
PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE
PO Box 80086 -- Fairbanks, AK 99708 -- Ph: 907-456-5581 Fax: 907-456-3111