MySQL Bugs: #6557: JOINS: INNER JOIN returns incorrect column list

Bug #6557	JOINS: INNER JOIN returns incorrect column list
Submitted:	10 Nov 2004 18:51	Modified:	11 Nov 2004 11:05
Reporter:	Trudy Pelzer
Status:	Duplicate
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0.0-alpha-max-debug	OS:	Microsoft Windows (WindowsXP)
Assigned to:		Target Version:

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  |
+--------+--------+-----+

Verified with 5.0.2 Windows server.

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