MySQL Bugs: #4976: Server crashes on a query with nested join

Bug #4976	Server crashes on a query with nested join
Submitted:	10 Aug 2004 15:27	Modified:	22 Aug 2004 8:04
Reporter:	Ramil Kalimullin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0	OS:	Any (any)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
The following sequence of queries crashes mysqld:

How to repeat:
drop table if exists t1, t2, t3;
create table t1 (a int, key (a));                                               
insert into t1 values (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), 
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19);
create table t2(b int, key (b));                                                
insert into t2 values (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), 
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19);
create table t3(c int, key (c));                                                
insert into t3 values (0), (1), (2), (3), (4), (5); 
explain select a, b, c from t1 left join (t2, t3) on c < 3 and b = c;
explain select a, b, c from t1 left join (t2, t3) on b < 3 and b = c;

can't reproduce with 5.0.0-alpha or latest 5.0bk on linux

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Additional info:

Just tried with the latest 5.0. No crash.
Probably it was fixed somehow.

The bug existed. The server really crashed for the test specified in the initial bug report.

The bug was fixed in two commits. The first one was based on an erronious idea. It was reversed by the second commit that contain a correct fix.

The changeset with the erronious fix was 1.1715  of 04/08/10 
In this changeset the test case for the bug was added in join_nested.test.

Here's important info about the second correct changeset:
ChangeSet
  1.1719 04/08/12 23:41:06 igor@rurik.mysql.com +5 -0
  join_nested.test, join_nested.result:
    Added a case for bug #4976 when one of the inner tables is empty.
  sql_select.cc:
    The previous fix for bug 4976 was reversed as it
    erroniously converted an outer join into an innner join
    when on_expression does not refer to outer tables.
    This is not valid if inner tables return an empty set.
    Setting dependency on outer tables was added for the above cases.
    To fix the crash in the test case of bug #4976  
    a guard was added that blocks running the crashing code for
    nested outer joins.