Bug #15171 Nested left joins and derived tables can cause query parsing to fail
Submitted: 23 Nov 2005 3:29 Modified: 14 Apr 2006 13:38
Reporter: Sharif Ibrahim Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.15 OS:Linux (Linux)
Assigned to: Georgi Kodinov CPU Architecture:Any

[23 Nov 2005 3:29] Sharif Ibrahim
Description:
Attempting to execute the query

SELECT * FROM (SELECT 1 AS foo) t1 LEFT JOIN ((SELECT 1 AS bar) t2 LEFT JOIN (SELECT 1 AS baz) t3 ON t2.bar = t3.baz) combined ON t1.foo = combined.baz

yields the error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'combined ON t1.foo = combined.baz' at line 1

Expected result set has the columns foo, bar and baz with a single row with the values 1,1,1.

How to repeat:
Attempt to execute the following query:

SELECT * FROM (SELECT 1 AS foo) t1 LEFT JOIN ((SELECT 1 AS bar) t2 LEFT JOIN (SELECT 1 AS baz) t3 ON t2.bar = t3.baz) combined ON t1.foo = combined.baz
[23 Nov 2005 8:07] Jorge del Conde
Thanks for your bug report.  I was able to reproduce this bug under FC4/5.0.16bk
[7 Apr 2006 13:37] Timour Katchaounov
The problem is not LEFT JOIN specific.
It is reproducible with this example:

create database bug15171;
use bug15171;
create table t1 (foo int);
create table t2 (bar int);
create table t3 (baz int);

SELECT *
FROM t1
           JOIN
         (t2 JOIN t3 ON t2.bar = t3.baz) t4 ON t1.foo = t4.baz;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't4 ON t1.foo = t4.baz' at line 1
[14 Apr 2006 13:38] Georgi Kodinov
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Hi,

According to the MySQL documentation section 13.2.7.1 (http://dev.mysql.com/doc/refman/5.0/en/join.html) : the nested joins cannot have aliases. So your query can be reformulated as :

SELECT * FROM (SELECT 1 AS foo) t1 LEFT JOIN ((SELECT 1 AS bar) t2 LEFT JOIN
(SELECT 1 AS baz) t3 ON t2.bar = t3.baz) ON t1.foo = t3.baz