MySQL Bugs: #15701: left join return null (additional for BUg#15700 )

Bug #15701	left join return null (additional for BUg#15700 )
Submitted:	13 Dec 2005 5:03	Modified:	29 Jun 2006 15:35
Reporter:	lucols lucols	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	?	OS:	Linux (linux, windows)
Assigned to:		CPU Architecture:	Any

Description:
Sorry. In Bug #15700,  I   describe something wrong. UnderLine is Complete:

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 and use query 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    m1_1_.NAME
 null                    m100

But  realy p2_1_.NAME's value  is exists and not null in database. 

Then i change the query 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 ;

and the result 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.

Thank you for a problem report. Sorry, but your statements are incorrect (there is no data types for columns):

mysql> create table t(ID integer not null auto_increment,p integer,
    ->         m integer, primary key (ID)) type=InnoDB;
Query OK, 0 rows affected, 1 warning (0,07 sec)

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

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
         primary key (ID)) type=InnoDB' at line 1

mysql> create table m(ID integer not null auto_increment,code,
    ->          primary key (ID)) type=InnoDB;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
         primary key (ID)) type=InnoDB' at line 1

mysql> create table u(ID integer not null auto_increment,name,
    ->           primary key (ID)) type=InnoDB;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
         primary key (ID)) type=InnoDB' at line 1

So, please, send the correct set of statements to repeat the problematic behaviour (including data you inseterted into that tables).

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 |
+----+------+------+

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.
Thanks.

Thank you for a test case. I was not able to repeat the results you described for the first query with 5.0.18-BK on Linux:

...
mysql> insert into u values(2, 'p100');
Query OK, 1 row affected (0,01 sec)

mysql> insert into p values(2);
Query OK, 1 row affected (0,00 sec)

mysql> insert into m values(1);
Query OK, 1 row affected (0,01 sec)

mysql> insert into t values(1,2,1);
Query OK, 1 row affected (0,00 sec)

mysql> 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 ;
+-------+-------+
| pName | mName |
+-------+-------+
| p100  | m100  |
+-------+-------+
1 row in set (0,05 sec)

mysql> 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 ;
+------+------+
| NAME | NAME |
+------+------+
| p100 | m100 |
+------+------+
1 row in set (0,01 sec)

So, they both work OK. But, if I understood you right, the problem is with MySQL Query Browser really (mysql client worked for you too). So, please, specify the exact version of MySQL Query Browser used.

Thanks for repeat. But also ,the problem be found in java web application,eg hibernate(www.hibernate.org), and the table struct like befoe(t,p,m,u).

So, if the problem is Java-related, I have to know the MySQL Connector/J version you use. Please, inform.

I use hibernate 3.1 , mysql-connector-java-3.1.12-bin.jar, and tomcat 5.5.12.

Can you, please, send the results of the following command:

show variables like 'char%';

executed from mysql client (where you join works OK) and from Query Browser (where it gives you NULL)?

As you said , I excute the command :show variables like 'char%', and the result like this:

mysql clinet:
mysql  -u admin --password=www  --default-character-set=gbk -h 192.168.0.1
mysql> use dev_opts
Database changed
mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | gbk                        |
| character_set_connection | gbk                        |
| character_set_database   | gbk                        |
| character_set_results    | gbk                        |
| character_set_server     | gbk                        |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
 query browser:
 
Variable_name         Value 
character_set_client utf8 
character_set_connection utf8 
character_set_database gbk 
character_set_results utf8 
character_set_server gbk 
character_set_system utf8 
character_sets_dir /usr/share/mysql/charsets/ 
(note: query borwser(1.1.17) connect's advance parameters not be set .)

So, can you, please, try to set 

character_set_client=gbk
character_set_connection=gbk
character_set_results=gbk

in the QB and check the results of your join after that?

After  ' SET character_set_client =gbk ' in QB , the result with '  show variables like 'char%'' no change , the value still is 'utf8'. How can i properly set this varialbes's value  ?

I've checked and discussed with other support engineers, and it looks like QB does not allow you to use set commands properly. So, it is a separate QB bug (or feature request) that you may add. 

In any case, we have to check that MySQL server works properly. So, please, set

character_set_client=utf8
character_set_connection=utf8
character_set_results=utf8

in mysql client (so, that these variables will match default settings in the QB) and repeat the query. If it gives you NULL, then, I think, it is a bug somewere with handling gbk <--> utf8 conversions.

This problem is no relate to character , may be hibernate's bug . i will discuss  with hibernate's guy .Thanks for  Valeriy Kravchuk 's repeat.

Please, inform about the results of the discussion. If there is no (obvious) bug in MySQL code, can we close this bug report?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hello,

Did you have some news for last Valeriy's question?

Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".