Re: LEFT JOIN fails to correctly join tables

[Sinisa Milivojevic <sinisa@xxxxxxxxx>]

dale-list-mysql-bug@xxxxxxxxxxxxx writes:
> >Description:
> 	It seems that that the use of LEFT JOIN when the joined table
> 	uses multiple primary key conditions fails to include rows which
> 	are clearly matching on those conditions.
> 
> >How-To-Repeat:
> 	mysql> create table foo (fooID smallint unsigned auto_increment, primary key (fooID));
> 	Query OK, 0 rows affected (0.08 sec)
> 
> 	mysql> create table foobar (fooID smallint unsigned not null, barID smallint unsigned not null, primary key (fooID,barID));
> 	Query OK, 0 rows affected (0.05 sec)
> 
> 	mysql> insert into foo (fooID) values (10),(20),(30);
> 	Query OK, 4 rows affected (0.00 sec)
> 	Records: 4  Duplicates: 0  Warnings: 0
> 
> 	mysql> insert into foobar values (10,1),(20,2),(30,3);
> 	Query OK, 3 rows affected (0.01 sec)
> 	Records: 3  Duplicates: 0  Warnings: 0
> 
> 	mysql> select * from foobar fb left join foo f on f.fooID = fb.fooID and f.fooID = 30;
> 	+-------+-------+-------+
> 	| fooID | barID | fooID |
> 	+-------+-------+-------+
> 	|    10 |     1 |  NULL |
> 	|    20 |     2 |  NULL |
> 	|    30 |     3 |  NULL |
> 	+-------+-------+-------+
> 	3 rows in set (0.00 sec)
> 

Thank you for the repeatable test case.

-- 
Regards,
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Mr. Sinisa Milivojevic <sinisa@xxxxxxxxx>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Fulltime Developer
/_/  /_/\_, /___/\___\_\___/   Larnaca, Cyprus
       <___/   www.mysql.com


---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <mysql-thread92830@xxxxxxxxxxxxxxx>
To unsubscribe, e-mail <mysql-unsubscribe-treed=ultraviolet.org@xxxxxxxxxxxxxxx>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php