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]
