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:
Category:MySQL Server Severity:S1 (Critical)
Version:5.0 OS:Any (any)
Assigned to: Igor Babaev CPU Architecture:Any

[10 Aug 2004 15:27] Ramil Kalimullin
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;
[13 Aug 2004 0:41] Hartmut Holzgraefe
can't reproduce with 5.0.0-alpha or latest 5.0bk on linux
[13 Aug 2004 6:16] Ramil Kalimullin
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.
[22 Aug 2004 7:52] Igor Babaev
The bug existed. The server really crashed for the test specified in the initial bug report.
[22 Aug 2004 8:04] Igor Babaev
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:
  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.
    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.