MySQL Bugs: #3524: the of inner join performed on a result of out join it is a cross product

Bug #3524	the of inner join performed on a result of out join it is a cross product
Submitted:	21 Apr 2004 7:55	Modified:	27 Apr 2004 16:35
Reporter:	Philip	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.16-max-debug/ 4.0.18-log	OS:	i686 GNU/Linux/Windows
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
## bug
## platform i686 GNU/Linux/Windows
## mysql version 4.0.16-max-debug/ 4.0.18-log

## out join does not give the same result if the order of joins is changed when mixed
## with inner joins. If the inner join is performed on a result of out join it 
## is treated as a cross product.

the inner join is just ignored when it is joined to an inner join...

How to repeat:

drop table if exists first,second,third;

create table first(fid int primary key);
create table second(sid int primary key,fid int);
create table third(tid int primary key,sid int);
insert first values(1),(2);
insert second values(1,1),(2,1);

SELECT f.fid, s.sid, t.sid
FROM (first AS f INNER JOIN second  AS s ON f.fid = s.fid) LEFT JOIN third AS t ON s.sid = t.sid;

SELECT f.fid, s.sid, t.sid
FROM first AS f INNER JOIN (second AS s LEFT JOIN third AS t ON s.sid=t.sid) ON f.fid=s.fid;

##both actions give different number of rows
##and the last query must be identical to a

##temp == select s.sid,t.sid t_sid from second as s left join third as t ON ##s.sid=t.sid;

##SELECT f.fid, s.sid, t_sid
##FROM first AS f INNER JOIN
##temp s
##ON f.fid=s.fid;

Suggested fix:
I hope it easy  to fix somewhere in the query optimization or treat each result as sub-query.

The real issue is that you are attempting to use nested joins, which MySQL does not currently support.  The unpredictable results are due to MySQL silently ignoring the parentheses which produce your nesting.

Support for nested joins is scheduled for a future release, however.

I still believe it is a bug because if it just ignores the parenthesis we have two consecative ON | USING clauses which shoul,d be a syntax error. And indeed it is if using only 2 tables. 

## gives syntax error
SELECT f.fid, s.sid
FROM first AS f LEFT JOIN second  AS s USING(fid) ON f.fid = s.fid;
## gives syntax error
SELECT f.fid, s.sid
FROM first AS f LEFT JOIN second  AS s  ON f.fid = s.fid ON f.fid = s.fid;

## oops no syntax error
SELECT f.fid, s.sid, t.sid
FROM first AS f INNER JOIN second AS s LEFT JOIN third AS t ON s.sid=t.sid ON
s.sid=t.sid;

Maybe the bug is not with the joins and nested joins. And speaking about the statement:

logically the ‘ON f.fid=s.fid’ must be assigned to the tables it belongs to. i.e. 
SELECT f.fid, s.sid, t.sid
FROM first AS f INNER JOIN second AS s  ON f.fid=s.fid LEFT JOIN third AS t ON s.sid=t.sid; 
Which is the best solution but if not the parser must throw a syntax error.

Sorry, in my previous message after the text: “  And speaking about the statement:”
I forgot to put the statement:

SELECT f.fid, s.sid, t.sid
FROM first AS f INNER JOIN (second AS s LEFT JOIN third AS t ON s.sid=t.sid) ON
f.fid=s.fid;