Bug #1962 Undocumented JOIN Syntax Change

• Submitted: 26 Nov 2003 13:12
• Modified: 9 Dec 2003 21:16
• Reporter: Dean Ellis
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Assigned to: Paul DuBois
• CPU Architecture: Any

[26 Nov 2003 13:12] Dean Ellis

Description:
The JOIN Syntax chapter of the manual does not call attention to the fact that version 3.23's "JOIN" does not accept ON/USING clauses.

How to repeat:
Read the manual.

Suggested fix:
Document that the behavior changed; similar to the 3.23.17 INNER JOIN notice.

[9 Dec 2003 11:44] Paul DuBois

Do you know when this change occurred?

[9 Dec 2003 13:50] Dean Ellis

Not specifically (sometime early in 4.0).

[9 Dec 2003 21:07] Paul DuBois

The change occurs in 4.0.11.  The nature of the change seems to be
that queries such as these:

SELECT * FROM t JOIN t t2 ON t.i = t2.i;
SELECT * FROM t JOIN t t2 USING(i);

are *legal* in 4.0.11 and up, but not before.  Prior to 4.0.11,
the ON and USING clauses are not recognized.

As it turns out, there *is* a change note for this version 
that seems related to the issue, although the note is so
non-specific that it's difficult to figure out what it
actually entails. I diffed the 4.0.10 and 4.0.11 grammar
files, but I have to confess that it's far from being
readily apparent to me what the implications are. :-(

The relevant changes are:

@@ -2092,19 +2099,22 @@
 join_table_list:
        '(' join_table_list ')' { $$=$2; }
        | join_table            { $$=$1; }
+       | join_table_list ',' join_table_list { $$=$3; }
        | join_table_list normal_join join_table_list { $$=$3; }
        | join_table_list STRAIGHT_JOIN join_table_list
          { $$=$3 ; $$->straight=1; }
-       | join_table_list INNER_SYM JOIN_SYM join_table_list ON expr
-         { add_join_on($4,$6); $$=$4; }
-       | join_table_list INNER_SYM JOIN_SYM join_table_list
+       | join_table_list normal_join join_table_list ON expr
+         { add_join_on($3,$5); $$=$3; }
+       | join_table_list normal_join join_table_list
+         USING 
          {
            SELECT_LEX *sel=Select;
            sel->db1=$1->db; sel->table1=$1->alias;
-           sel->db2=$4->db; sel->table2=$4->alias;
+           sel->db2=$3->db; sel->table2=$3->alias;
          }
-         USING '(' using_list ')'
-         { add_join_on($4,$8); $$=$4; }
+         '(' using_list ')'
+         { add_join_on($3,$7); $$=$3; }
+
        | join_table_list LEFT opt_outer JOIN_SYM join_table_list ON expr
          { add_join_on($5,$7); $5->outer_join|=JOIN_TYPE_LEFT; $$=$5; }
        | join_table_list LEFT opt_outer JOIN_SYM join_table_list
@@ -2133,9 +2143,10 @@
          { add_join_natural($1,$4); $$=$4; };
 
 normal_join:
-       ',' {}
-       | JOIN_SYM {}
-       | CROSS JOIN_SYM {};
+       JOIN_SYM                {}
+       | INNER_SYM JOIN_SYM    {}
+       | CROSS JOIN_SYM        {}
+       ;
 
 join_table:
        {

[9 Dec 2003 21:16] Paul DuBois

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).