Bug #11284 left join with empty derived table
Submitted: 13 Jun 2005 10:37 Modified: 13 Jul 2005 19:04
Reporter: Yoshiaki Tajika (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.7-beta-nightly-20050521 OS:Any (*)
Assigned to: Igor Babaev CPU Architecture:Any

[13 Jun 2005 10:37] Yoshiaki Tajika 
Description:
left join with empty derived table becomes empty.

How to repeat:
create table t1(c1 int) engine=myisam;
create table t2(c1 int) engine=myisam;
create table t3(c1 int) engine=myisam;
insert into t1 values(4);

select t1.*,t2.*,t3.* from
t1 left join t2 on t1.c1=t2.c1 left join t3 on t2.c1=t3.c1; -- (a)

select t1.*,t2.*,t3.* from
t1 left join (t2 left join t3 on t2.c1=t3.c1) on t1.c1=t2.c1; -- (b)

(a) returns 1 row. It's OK.
(b) returns empty set. It's NG, isn't it?
[13 Jun 2005 12:05] Aleksey Kishkin 
tested on 5.0.6 also
[15 Jun 2005 12:56] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26021
[15 Jun 2005 14:59] Igor Babaev 
ChangeSet
  1.1944 05/06/15 05:56:19 igor@rurik.mysql.com +3 -0
  join_nested.result, join_nested.test:
    Added a test case for bug #11284.
  sql_select.cc:
    Fixed bug #11284.
    Optimization with empty inner table currently cannot be
    used in the case of nested outer join.

  The fix will appear in 5.0.8.
[13 Jul 2005 19:04] Paul DuBois 
Noted in 5.0.8 changelog.