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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
