>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)
>Fix:
If you qualify the joined table reference with an ignore index(PRIMARY),
the results are correct.
mysql> select * from foobar fb left join foo f ignore index (primary) on
f.fooID = fb.fooID and f.fooID = 30;
+-------+-------+-------+
| fooID | barID | fooID |
+-------+-------+-------+
| 10 | 1 | NULL |
| 20 | 2 | NULL |
| 30 | 3 | 30 |
+-------+-------+-------+
3 rows in set (0.00 sec)
Obviously, ignoring a useful index like this on a large table is not
a particularly useful workaround, but it does produce correct results.
>Submitter-Id: <submitter ID>
>Originator:
>Organization:
>MySQL support: none
>Synopsis: LEFT JOIN incorrectly eliminates joined row selections with multiple
>same-key references
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.41 (Source distribution)
I've also tested this under 3.23.36 (solaris) and the bug exists there too.
>Server: /opt/mysql/bin/mysqladmin Ver 8.21 Distrib 3.23.41, for pc-linux-gnu on i586
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.41-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /opt/db/mysql-3.23.41/db.sock
Uptime: 1 day 6 hours 43 min 5 sec
Threads: 6 Questions: 3610 Slow queries: 0 Opens: 1499 Flush tables: 1 Open
tables: 60 Queries per second avg: 0.033
>Environment:
System: Linux murphy 2.2.19 #2 Thu Mar 29 13:57:33 EST 2001 i586 unknown
Architecture: i586
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /opt/gcc/bin/gcc /usr/bin/cc
GCC: Reading specs from /opt/gcc-2.95.2/lib/gcc-lib/i586-pc-linux-gnu/2.95.2/specs
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Jun 15 1999 /lib/libc.so.6 -> libc-2.1.1.so
-rwxr-xr-x 1 root root 4016683 Apr 16 1999 /lib/libc-2.1.1.so
-rw-r--r-- 1 root root 19533408 Apr 16 1999 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Apr 16 1999 /usr/lib/libc.so
Configure command: ./configure --with-unix-socket-path=/opt/db/mysql-3.23.41/db.sock
--prefix=/opt/db/mysql-3.23.41 --libexecdir=/opt/db/mysql-3.23.41/bin
--localstatedir=/opt/db/mysql-3.23.41/data --with-mysqld-user=mysql --enable-assembler
--enable-thread-safe-client --with-charset=latin1 --with-innodb --with-named-z-libs=no
--without-bench --without-debug
Perl: This is perl, version 5.005_03 built for i386-linux
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
