Bug #11880 UNION GROUPS RESULTS

• Submitted: 12 Jul 2005 10:04
• Modified: 26 Jul 2005 18:29
• Reporter: Tom Schindl
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.12a
• OS: Windows (WinXP)
• CPU Architecture: Any

[12 Jul 2005 10:04] Tom Schindl

Description:
SELECT 1,1,1,1,1 UNION SELECT * FROM person p, contracts c WHERE p.p_id = c_ref_p;
Produces:
1	1	1	1	1
1	1	1	1	1
1	Tom	Schindl	1	1
2	Udo	Rader	2	2
3	Bernd	Reitmaier	3	3
1	Tom	Schindl	4	1

But 
SELECT 1,1,1 UNION SELECT p.* FROM person p, contracts c WHERE p.p_id = c_ref_p;
Produces:
1	1	1
1	1	1
1	Tom	Schindl
2	Udo	Rader
3	Bernd	Reitmaier

How to repeat:
Use the attached database

[12 Jul 2005 10:04] Tom Schindl

The database to reproduce the problem

Attachment: wrong-db2.dump (application/octet-stream, text), 2.05 KiB.

[12 Jul 2005 10:07] Tom Schindl

The first line of 1s is a copy and paste error, sorry

[12 Jul 2005 11:42] Vasily Kishkin

Thanks for test file.
Tested on Win 2000 Sp4, MySQL server 4.1.13.

[12 Jul 2005 14:27] Tom Schindl

Rereading the docs I'm uncertain that this is a bug because UNION automatically means UNION DISTINCT.

[12 Jul 2005 15:54] MySQL Verification Team

I truly don't see what is a bug here.

A single '*' means columns from all tables. p.* means only columns from p.

[12 Jul 2005 18:31] MySQL Verification Team

Definitely looks like bug, but needs verification on Linux.

[26 Jul 2005 18:02] MySQL Verification Team

c:\mysql\bin>mysql -uroot db2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.12a-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT 1,1,1,1,1 UNION SELECT * FROM person p, contracts c WHERE p.p_id =
    -> c_ref_p;
+---+-------+-----------+---+------+
| 1 | 1     | 1         | 1 | 1    |
+---+-------+-----------+---+------+
| 1 | 1     | 1         | 1 |    1 |
| 1 | Tom   | Schindl   | 1 |    1 |
| 2 | Udo   | Rader     | 2 |    2 |
| 3 | Bernd | Reitmaier | 3 |    3 |
| 1 | Tom   | Schindl   | 4 |    1 |
+---+-------+-----------+---+------+
5 rows in set (0.00 sec)

mysql> SELECT 1,1,1 UNION SELECT p.* FROM person p, contracts c WHERE p.p_id =
    -> c_ref_p;
+---+-------+-----------+
| 1 | 1     | 1         |
+---+-------+-----------+
| 1 | 1     | 1         |
| 1 | Tom   | Schindl   |
| 2 | Udo   | Rader     |
| 3 | Bernd | Reitmaier |
+---+-------+-----------+
4 rows in set (0.00 sec)

mysql> select * from person;
+------+-------------+-----------+
| p_id | p_givenname | p_surname |
+------+-------------+-----------+
|    1 | Tom         | Schindl   |
|    2 | Udo         | Rader     |
|    3 | Bernd       | Reitmaier |
+------+-------------+-----------+
3 rows in set (0.00 sec)

mysql>

[26 Jul 2005 18:29] Sergei Golubchik

You're right - as UNION means UNION DISTINCT, there's no bug here.