» MySQL5 JOIN Syntax Changes

Changes in the SQL parser in MySQL5 have exposed previously unnoticed errors in several open source applications I support recently. These changes are to comply more accurately with the SQL:2003 standard. A good thing. The bad thing is most apps throw a db error when they encounter a JOIN query of this specific form.

Until MySQL 5.0.1 parentheses in a table_reference were simply dropped and join operations were grouped to the left. As of MySQL 5.0.1, nested join operations are allowed, with the JOIN operator having a higher precendence than the comma (,) operator. Queries that happened to work before will produce error now if MySQL doesn’t understand how the tables should be joined. In some cases, result sets could change for some queries. I haven’t seen one of these in action yet, but I have a few example cases in mind.

Queries affected by this change may result in one of the following errors.

Unknown column ‘xxx’ in ‘on clause’
Unknown column ‘xxx’ in ‘where clause’

In several ZenCart installations, I was able to fix the bug by adding the proper parenthesis around the table names included in the JOINs. Other instances of this error were fixed by re-ordering the table_references.

From the MySQL5 documentation:

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);

For full details see:
http://dev.mysql.com/doc/refman/5.0/en/join.html

The ZenCart people are currently working to straighten these bugs out with their forthcoming ZenCart v1.3 release. Patches for the latest two versions are available at http://www.dataweb.no/dm/index.php/Downloads.

Popularity: 15%

Share This