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

[10 Nov 2004 17:59] Trudy Pelzer
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  |
+--------+--------+-----+
[10 Nov 2004 18:19] Victoria Reznichenko
Verified with 5.0.2-alpha-debug-log
[11 Nov 2004 17:09] Trudy Pelzer
See also Bug#6489, which has additional information.
[13 Feb 2005 22:39] Oleksandr Byelkin
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
[15 Aug 2005 14:33] Matthias Leich
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.
[22 Aug 2005 14:05] Bugs System
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
[22 Aug 2005 14:09] Timour Katchaounov
The bug is fixed by WL#2486 which is pushed into 5.0.12.

The changeset above adds only a test case.
[27 Aug 2005 19:27] Paul Dubois
Noted in 5.0.12 changelog.