UNION is mean to removed duplicate rows. Use "UNION ALL" if you don't want
this to happen.

http://dev.mysql.com/doc/refman/5.0/en/union.html

-----Original Message-----
From: list account [mailto:discussions.account@googlemail.com]
Sent: 11 July 2007 09:19
To: mysql@lists.mysql.com
Subject: BUG in UNION implementation?! Confimation or Explaination
please


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

This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email postmaster@touchlocal.com. You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm.

Touch Local Limited
Registered Number: 2885607
VAT Number: GB896112114
Cardinal Tower, 12 Farringdon Road, London EC1M 3NN
+44 (0)20 7452 5300