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, primary key (ID)) type=InnoDB;
create table m(ID integer not null auto_increment, primary key (ID)) type=InnoDB;
create table u(ID integer not null auto_increment,name varchar(10), primary key (ID)) type=InnoDB;
alter table t add index (p),
add constraint
foreign key (p)
references p (ID);
alter table t add index (m),
add constraint
foreign key (m)
references m(ID);
alter table p
add index (ID),
add constraint
foreign key (ID)
references u(ID);
alter table m
add index (ID),
add constraint
foreign key (ID)
references u(ID);
Insert some datas:
mysql> select * from u;
+----+------+
| ID | name |
+----+------+
| 1 | m100 |
| 2 | p100 |
+----+------+
mysql> select * from p;
+----+
| ID |
+----+
| 2 |
+----+
mysql> select * from m;
+----+
| ID |
+----+
| 1 |
+----+
mysql> select * from t;
+----+------+------+
| ID | p | m |
+----+------+------+
| 1 | 2 | 1 |
+----+------+------+
Then Use query with 'MySQL Query Browser' like this:
select p2_1_.NAME as pName ,m1_1_.NAME as mName
from t this_
inner join p p2_ on this_.p=p2_.ID
left join u 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=2 ;
The result is :
pName mName
Null m100
But realy p2_1_.NAME's value is exist and not null in database.
Then i change the select like this:
select p2_1_.NAME ,m1_1_.NAME
from t this_
inner join p p2_ on this_.p=p2_.ID
-- change : 'left join' to 'inner join'
inner join u 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=2 ;
and the result is right.
In some jdbc's application , As hibernate, this problem also be found. But use mysql client , both query is right.
It's a BUG (mysql server 5.0.13-5.1.3).who can tell me how to solve it?
How to repeat:
tell me : lucols@163.com , please, thanks!
Suggested fix:
I hope this bug be fix in 5.0.16.1.
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, primary key (ID)) type=InnoDB; create table m(ID integer not null auto_increment, primary key (ID)) type=InnoDB; create table u(ID integer not null auto_increment,name varchar(10), primary key (ID)) type=InnoDB; alter table t add index (p), add constraint foreign key (p) references p (ID); alter table t add index (m), add constraint foreign key (m) references m(ID); alter table p add index (ID), add constraint foreign key (ID) references u(ID); alter table m add index (ID), add constraint foreign key (ID) references u(ID); Insert some datas: mysql> select * from u; +----+------+ | ID | name | +----+------+ | 1 | m100 | | 2 | p100 | +----+------+ mysql> select * from p; +----+ | ID | +----+ | 2 | +----+ mysql> select * from m; +----+ | ID | +----+ | 1 | +----+ mysql> select * from t; +----+------+------+ | ID | p | m | +----+------+------+ | 1 | 2 | 1 | +----+------+------+ Then Use query with 'MySQL Query Browser' like this: select p2_1_.NAME as pName ,m1_1_.NAME as mName from t this_ inner join p p2_ on this_.p=p2_.ID left join u 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=2 ; The result is : pName mName Null m100 But realy p2_1_.NAME's value is exist and not null in database. Then i change the select like this: select p2_1_.NAME ,m1_1_.NAME from t this_ inner join p p2_ on this_.p=p2_.ID -- change : 'left join' to 'inner join' inner join u 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=2 ; and the result is right. In some jdbc's application , As hibernate, this problem also be found. But use mysql client , both query is right. It's a BUG (mysql server 5.0.13-5.1.3).who can tell me how to solve it? How to repeat: tell me : lucols@163.com , please, thanks! Suggested fix: I hope this bug be fix in 5.0.16.1.