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!