Bug #18183 Unknown column error when mixing cross join and left join

• Submitted: 13 Mar 2006 9:38
• Modified: 13 Mar 2006 11:21
• Reporter: Ciaran McNulty
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.19
• OS: Linux (Debian Sarge)
• CPU Architecture: Any

[13 Mar 2006 9:38] Ciaran McNulty

Description:
When mixing a cross join and a left join, MySQL appears to sometimes lose track of the column names in a table, depending on the order in which the cross-joined tables are listed.

How to repeat:
CREATE TABLE `a` (
  `a_id` int(11) NOT NULL,
  `a_val` varchar(1) NOT NULL
) ;
INSERT INTO `a` VALUES (1, 'A');

CREATE TABLE `b` (
  `b_id` int(11) NOT NULL,
  `a_id` int(11) NOT NULL,
  `b_val` varchar(1) NOT NULL
);
INSERT INTO `b` VALUES (1, 1, 'B');

CREATE TABLE `c` (
  `c_id` int(11) NOT NULL,
  `a_id` int(11) NOT NULL,
  `c_val` varchar(1) NOT NULL
);
INSERT INTO `c` VALUES (1, 1, 'C');

# Example working query

SELECT *
FROM  b, a
LEFT JOIN c ON c.a_id = a.a_id
WHERE b.a_id = a.a_id;

# This gives the following (correct) result 
#
#+------+-------+------+------+-------+------+------+-------+
#| a_id | a_val | b_id | a_id | b_val | c_id | a_id | c_val |
#+------+-------+------+------+-------+------+------+-------+
#|    1 | A     |    1 |    1 | B     |    1 |    1 | C     |
#+------+-------+------+------+-------+------+------+-------+
#1 row in set (0.00 sec)

#Example broken query:

SELECT *
FROM a, b
LEFT JOIN c  ON c.a_id = a.a_id
WHERE b.a_id = a.a_id;

# This fails with the error 1054 - Unknown column 'a.a_id' in 'on clause' 

Suggested fix:
It's fixable in most cases by experimenting with the order of the tables in the join or rewriting the query to do explicit INNER JOINs, but a big problem for legacy codebases.

[13 Mar 2006 10:14] Ciaran McNulty

This is still present in 5.0.19

[13 Mar 2006 10:53] Valeriy Kravchuk

Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/join.html):

"- Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.

Example:

CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);
INSERT INTO t1 VALUES(1,1);
INSERT INTO t2 VALUES(1,1);
INSERT INTO t3 VALUES(1,1);
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

Previously, the SELECT was legal due to the implicit grouping of t1,t2 as (t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

Alternatively, avoid the use of the comma operator and use JOIN instead:

SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

This change also applies to INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which now have higher precedence than the comma operator."

So, it is not a bug, but intended and documented behaviour.

[13 Mar 2006 11:21] Ciaran McNulty

Thanks for the very clear explanation.  I suppose this is the sort of thing I should expect when upgrading by 2 major revisions!