Re: BUG in UNION implementation?! Confimation or Explaination please
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 ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: BUG in UNION implementation?! Confimation or Explaination please
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]
Re: BUG in UNION implementation?! Confimation or Explaination please
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
