Bug #11470 SELECT with UNION

• Submitted: 20 Jun 2005 23:03
• Modified: 21 Jun 2005 0:21
• Reporter: Joerg Michel
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.24 and 4.0.19
• OS: Linux (Linux)
• CPU Architecture: Any

[20 Jun 2005 23:03] Joerg Michel

Description:
Hi,
I tell an a example my problem. I have 4 tables with severaldatasets.
I can with the "SELECT count(column a) FROM table a;" get the output a value from count(column a).
That I can make with the column b FROM table b, with column c FROM table c and with column d from table d.
I can also the statement "SELECT count(column a) FROM table a UNION SELECT count(column b) FROM table b and UNION SELECT count(column d) FROM table d;" and I get the same output value´s.
When but the value count(column c) and the value count(column d) are same, then kill mysql the output value count(column d).

Example:

mysql> select count(a) from a \G;
*************************** 1. row ***************************
count(a): 551
1 row in set (0.00 sec)

mysql> select count(b) from b \G;
*************************** 1. row ***************************
count(a): 131
1 row in set (0.00 sec)

mysql> select count(c) from c \G;
*************************** 1. row ***************************
count(a): 122
1 row in set (0.00 sec)

mysql> select count(d) from d \G;
*************************** 1. row ***************************
count(a): 122
1 row in set (0.00 sec)

mysql> select count(a) from a UNION  select count(b) from b UNION  select count(c) from c UNION  select count(d) from d\G;
*************************** 1. row ***************************
count(a): 551
*************************** 2. row ***************************
count(a): 131
*************************** 3. row ***************************
count(a): 122
3 rows in set (0.04 sec)

Where is the error ?

How to repeat:
Example:

mysql> select count(a) from a \G;
*************************** 1. row ***************************
count(a): 551
1 row in set (0.00 sec)

mysql> select count(b) from b \G;
*************************** 1. row ***************************
count(a): 131
1 row in set (0.00 sec)

mysql> select count(c) from c \G;
*************************** 1. row ***************************
count(a): 122
1 row in set (0.00 sec)

mysql> select count(d) from d \G;
*************************** 1. row ***************************
count(a): 122
1 row in set (0.00 sec)

mysql> select count(a) from a UNION  select count(b) from b UNION  select count(c) from c UNION  select count(d) from d\G;
*************************** 1. row ***************************
count(a): 551
*************************** 2. row ***************************
count(a): 131
*************************** 3. row ***************************
count(a): 122
3 rows in set (0.04 sec)

[21 Jun 2005 0:21] MySQL Verification Team

mysql> create table a (a int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into a values (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> 
mysql> create table b (b int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into b values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> create table c (c int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into c values (1), (2), (3), (4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> 
mysql> create table d (d int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into d values (1), (2), (3), (4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> 
mysql> select count(a) from a UNION  select count(b) from b UNION 
    -> select count(c) from c UNION  select count(d) from d\G
*************************** 1. row ***************************
count(a): 2
*************************** 2. row ***************************
count(a): 3
*************************** 3. row ***************************
count(a): 4
3 rows in set (0.01 sec)

mysql> select count(a) from a UNION ALL  select count(b) from b UNION all 
    -> select count(c) from c UNION all select count(d) from d\G
*************************** 1. row ***************************
count(a): 2
*************************** 2. row ***************************
count(a): 3
*************************** 3. row ***************************
count(a): 4
*************************** 4. row ***************************
count(a): 4
4 rows in set (0.00 sec)