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!
© 2024, Oracle Corporation and/or its affiliates
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!