Bug #15700 left join return null
Submitted: 13 Dec 2005 4:52 Modified: 13 Dec 2005 9:46
Reporter: lucols lucols Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.16 OS:Linux (linux,windows)
Assigned to: CPU Architecture:Any

[13 Dec 2005 4:52] lucols lucols
Description:
create table t(ID integer not null auto_increment,p integer,
        m integer, primary key (ID)) type=InnoDB;

create table p(ID integer not null auto_increment,code,
         primary key (ID)) type=InnoDB;

create table m(ID integer not null auto_increment,code,
         primary key (ID)) type=InnoDB;

create table u(ID integer not null auto_increment,name,
         primary key (ID)) type=InnoDB;
alter table t add index FK9B76E465CB38F0B3 (p), 
        add constraint FK9B76E465CB38F0B3 
        foreign key (p) 
        references p (ID);
alter table t add index FK9B76E465D40EDB1(m), 
        add constraint FK9B76E465D40EDB1
        foreign key (m) 
        references m(ID);
alter table p
        add index FKA9AB0F89BF18CCA3 (ID), 
        add constraint FKA9AB0F89BF18CCA3 
        foreign key (ID) 
        references u(ID);
alter table m
        add index FKC6C1F799BF18CCA3 (ID), 
        add constraint FKC6C1F799BF18CCA3 
        foreign key (ID) 
        references u(ID);
after i insert some data,  use as flower:
select  p2_1_.NAME  ,m1_1_.NAME 
    from t this_
    inner join p p2_  on this_.p=p2_.ID
    left outer join p2_1_  on p2_.ID=p2_1_.ID  
    inner join m m1_   on this_.m=m1_.ID
    left outer join u m1_1_  on m1_.ID=m1_1_.ID  
    where  p2_.ID=904;
the result is : p2_1_.NAME  =null, m1_1_.NAME =m100, but  realy ,p2_1_.NAME   is not realy in database. Then . I change like this:
select  p2_1_.NAME  ,m1_1_.NAME 
    from t this_
    inner join p p2_  on this_.p=p2_.ID
    inner join p2_1_  on p2_.ID=p2_1_.ID  -- change : 'left join' to 'inner join'
    inner join m m1_   on this_.m=m1_.ID
    left outer join u m1_1_  on m1_.ID=m1_1_.ID  
    where  p2_.ID=904 ;
The result is right.
It's a BUG(mysql server 5.0.13-5.1.3beat ) .who can tell me how to solve it?

How to repeat:
Please tell me : lucols@163.com , thanks!
[13 Dec 2005 9:46] Aleksey Kishkin
http://bugs.mysql.com/bug.php?id=15701