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
