Bug #6557 JOINS: INNER JOIN returns incorrect column list

• Submitted: 10 Nov 2004 17:51
• Modified: 11 Nov 2004 10:05
• Reporter: Trudy Pelzer
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.0-alpha-max-debug
• OS: Windows (WindowsXP)
• CPU Architecture: Any

[10 Nov 2004 17:51] Trudy Pelzer

Description:
Per the SQL Standard, the result of an INNER JOIN should have
a column list that consists of all specified columns of the joined 
tables, except that the common columns are coalesced into a 
single column that has the same attributes (e.g. name, data type) 
as the columns from which it is derived. The result is that the join 
result set should contain only one column for each set of common
columns. This is not happening; instead, the INNER JOIN result
contains duplicate columns for each set of common columns.

How to repeat:
mysql> CREATE TABLE GROUPS2 (EMPNUM SMALLINT, GRP INT);
Query OK, 0 rows affected (0.12 sec)

mysql> CREATE TABLE NAMES2 (EMPNUM INT, NAME CHAR(5));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into GROUPS2 VALUES(1,1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO NAMES2 VALUES(1,'BOB');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM NAMES2 INNER JOIN GROUPS2 USING (EMPNUM);
+---------+-------+--------+------+
| EMPNUM | NAME | EMPNUM | GRP  |
+---------+-------+--------+------+
|      1     | BOB     |      1     |    1  |
+---------+-------+--------+------+
-- This is the incorrect result. The common column EMPNUM should
be coalesced into a single EMPNUM column, so the result should be:
+--------+--------+-----+
| EMPNUM | NAME |  GRP  |
+--------+--------+-----+
|      1    | BOB     |     1  |
+--------+--------+-----+

[10 Nov 2004 18:19] MySQL Verification Team

Verified with 5.0.2 Windows server.

[11 Nov 2004 10:05] Sergei Golubchik

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

see http://bugs.mysql.com/6489