MySQL Bugs: #13241: STRAIGHT_JOIN + LEFT JOIN is ambiguous in 5.0.14-rc

Bug #13241	STRAIGHT_JOIN + LEFT JOIN is ambiguous in 5.0.14-rc
Submitted:	15 Sep 2005 23:09	Modified:	16 Sep 2005 8:39
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.14-rc	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Hi,

When mixing STRAIGHT_JOIN + LEFT JOIN + USING syntax, MySQL 5.0.14-rc fails where 4.1.14 succeed.

How to repeat:
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
CREATE TABLE t1 (a int);
CREATE TABLE t2 (a int);
CREATE TABLE t3 (a int);

SELECT t1.a FROM t1 STRAIGHT_JOIN t2 LEFT JOIN t3 USING (a) WHERE t1.a=t2.a;
ERROR 1052 (23000): Column 'a' in from clause is ambiguous

Works ok in 4.1.14.

SELECT t1.a FROM t1,t2 LEFT JOIN t3 USING (a) WHERE t1.a=t2.a;
works ok in both 5.0.14-rc and 4.1.14

Suggested fix:
This query should work in 5.0.

Regards,
  Jocelyn

I verified this as reported; the select query is OK in 4.1.8 and not OK in 5.0.12

from the 5.0.12 changelog: 

Bugs fixed:

    *  Note: Natural joins and joins with USING, including outer join variants, now are processed according to the SQL:2003 standard. (Bug #4789, Bug #6136, Bug #6276, Bug #6495, Bug #6558, Bug #9978, Bug #10646, Bug #10972, Bug #11710)

      This change may necessitate rewriting of certain queries. For example, the following query will work as written before 5.0.12, but now will fail with an Unknown column 't1.id' in 'on clause' error:

      SELECT t1.id,t2.id,t3.id FROM t1,t2 LEFT JOIN t3 ON (t3.id=t1.id)
      WHERE t1.id=t2.id;

      To rewrite the query, use parentheses to group the tables in the inner join:

      SELECT t1.id,t2.id,t3.id FROM (t1,t2) LEFT JOIN t3 ON (t3.id=t1.id)
      WHERE t1.id=t2.id; 

      For that particular query, it is also possible to rewrite it as a natural join:

      SELECT t1.id,t2.id,t3.id FROM t1,t2 NATURAL LEFT JOIN t3
      WHERE t1.id=t2.id;

from the 5.0.12 changelog

 Bugs fixed:

    *  Note: Natural joins and joins with USING, including outer join variants, now are processed according to the SQL:2003 standard. (Bug #4789, Bug #6136, Bug #6276, Bug #6495, Bug #6558, Bug #9978, Bug #10646, Bug #10972, Bug #11710)

      This change may necessitate rewriting of certain queries. For example, the following query will work as written before 5.0.12, but now will fail with an Unknown column 't1.id' in 'on clause' error:

      SELECT t1.id,t2.id,t3.id FROM t1,t2 LEFT JOIN t3 ON (t3.id=t1.id)
      WHERE t1.id=t2.id;

      To rewrite the query, use parentheses to group the tables in the inner join:

      SELECT t1.id,t2.id,t3.id FROM (t1,t2) LEFT JOIN t3 ON (t3.id=t1.id)
      WHERE t1.id=t2.id; 

      For that particular query, it is also possible to rewrite it as a natural join:

      SELECT t1.id,t2.id,t3.id FROM t1,t2 NATURAL LEFT JOIN t3
      WHERE t1.id=t2.id;

Just to clarify:

SELECT t1.a FROM t1 STRAIGHT_JOIN t2 LEFT JOIN t3 USING (a) WHERE t1.a=t2.a

first you join t1 and t2, than you join the result of that with t3.
The result of (t1 STRAIGHT_JOIN t2) contains two columns - both named 'a'. And as you use USING the following paragraph of SQL2003 (part 2, section 7.7 <joined table> applies)

b) If a <named columns join> is specified, then every <column name> in the <join column list> shall be equivalent to the <field name> of exactly one field of RT1 and the <field name> of exactly one field of RT2.

Note - "exactly one"