MySQL Bugs: #6558: Views: CREATE VIEW fails with JOIN ... USING

Bug #6558	Views: CREATE VIEW fails with JOIN ... USING
Submitted:	10 Nov 2004 17:59	Modified:	27 Aug 2005 19:27
Reporter:	Trudy Pelzer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.0-alpha-max-debug	OS:
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:
See Bug#6557. Because the result of an INNER JOIN incorrectly
contains a duplicate column for each set of common columns
in the joined tables, a CREATE VIEW statement will fail when
the view definition contains INNER JOIN ... USING. This should
not happen; the correct result is to create a view that contains
only one column for each common column pair.

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> CREATE VIEW NAMGRP2 AS SELECT * FROM NAMES2 INNER JOIN GROUPS2 USING (EMPNUM);
ERROR 1064 (42000): You have an error in your SQL syntax.  ...
-- This is the incorrect result. The view should be created with
three columns: EMPNUM, NAME, GRP

mysql> SELECT * FROM NAMGRP2;
-- should return:
+--------+--------+-----+
| EMPNUM | NAME |  GRP  |
+--------+--------+-----+
|      1    | BOB     |     1  |
+--------+--------+-----+

Verified with 5.0.2-alpha-debug-log

See also Bug#6489, which has additional information.

It is not view bug, mysql behave in such way in normal queries:
+ CREATE TABLE GROUPS2 (EMPNUM SMALLINT, GRP INT);
+ CREATE TABLE NAMES2 (EMPNUM INT, NAME CHAR(5));
+ insert into GROUPS2 VALUES(1,1);
+ INSERT INTO NAMES2 VALUES(1,'BOB');
+ SELECT * FROM NAMES2 INNER JOIN GROUPS2 USING (EMPNUM);
+ EMPNUM        NAME    EMPNUM  GRP
+ 1     BOB     1       1

It seems to be that some of the source code pushes most probably
WL#2486 - "Process NATURAL and USING joins according 
                 to SQL:2003"
      ChangeSet@1.1918.3.1, 2005-08-12
within the last days fixed this bug and also #6489 and #6557.
The NIST testscripts dml104 and dml112 suffered from these bugs
since months and today all these problems disappeared.
My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1991, 2005-08-13
 
I did only check the NIST testcases + the testcase attached to #6557.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28636

The bug is fixed by WL#2486 which is pushed into 5.0.12.

The changeset above adds only a test case.

Noted in 5.0.12 changelog.