Incorrect results with LEFT JOIN and indexes

[bbaetz]

>Description:

When doing a query on a table LEFT JOINed to one with an index, mysql
gives the wrong answer.

Tested on mysql 3.23.36 (RH7.1 package) and 3.22.30.

>How-To-Repeat:
Run the following script:

drop table if exists bugs, cc;

create table bugs (bug_id mediumint, reporter mediumint);

create table cc (bug_id mediumint, who mediumint, index(who));

insert into cc values (1,1);
insert into cc values (1,2);

insert into bugs values (1,1);
insert into bugs values (2,1);

SELECT bugs.bug_id FROM bugs LEFT JOIN cc ON (bugs.bug_id = cc.bug_id AND
cc.who = 2) WHERE (bugs.reporter = 2 OR cc.who = 2);

Expected result:
---------------
*************************** 1. row ***************************
bug_id: 1
1 row in set (0.00 sec)

Actual result:
-------------
*************************** 1. row ***************************
bug_id: 1
*************************** 2. row ***************************
bug_id: 2
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT bugs.bug_id FROM bugs LEFT JOIN cc ON (bugs.bug_id = 
cc.bug_id AND cc.who = 2) WHERE (bugs.reporter = 2 OR cc.who = 2);
*************************** 1. row ***************************
        table: bugs
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: 
*************************** 2. row ***************************
        table: cc
         type: ref
possible_keys: who
          key: who
      key_len: 4
          ref: const
         rows: 1
        Extra: where used
2 rows in set (0.00 sec)

(This is a simplified test case; the original problem had an additional 
table included twice in the select (as "foo as bar, foo as baz"), with no 
constraints on foo, bar, or baz. The explicit test against null appears to 
fix that test case, too.

>Fix:

Change the query to:

SELECT bugs.bug_id FROM bugs LEFT JOIN cc ON (bugs.bug_id = cc.bug_id AND
cc.who = 2) WHERE (bugs.reporter = 2 OR (not isnull(cc.who) AND cc.who = 
2));

mysql> EXPLAIN SELECT bugs.bug_id FROM bugs LEFT JOIN cc ON (bugs.bug_id = 
cc.bug_id AND cc.who = 2) WHERE (bugs.reporter = 2 OR (not isnull(cc.who) 
AND cc.who = 2));
*************************** 1. row ***************************
        table: bugs
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: 
*************************** 2. row ***************************
        table: cc
         type: ref
possible_keys: who
          key: who
      key_len: 4
          ref: const
         rows: 1
        Extra: where used
2 rows in set (0.01 sec)

OR remove the index.

Note that adding bugs.bug_id=2 as an additional constraint correctly
causes 0 rows to be returned.

>Submitter-Id:	<submitter ID>
>Originator:	Bradley Baetz
>Organization:
 
>MySQL support: none
>Synopsis:	SELECT returning wrong answer with JOIN and indexes
>Severity:	serious 
>Priority:	medium
>Category:	mysql
>Class:		sw-bug
>Release:	mysql-3.23.36 (Source distribution)
>Server: /usr/bin/mysqladmin  Ver 8.18 Distrib 3.23.36, for redhat-linux-gnu on i386
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.36
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/lib/mysql/mysql.sock
Uptime:			6 hours 52 min 45 sec

Threads: 1  Questions: 1788  Slow queries: 0  Opens: 558  Flush tables: 1  Open tables: 32
Queries per second avg: 0.072
>Environment:
	
System: Linux banana.home 2.4.12 #2 Thu Oct 11 11:54:48 EDT 2001 i686 unknown
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.1 2.96-85)
Compilation info: CC='gcc'  CFLAGS='-O2 -march=i386 -mcpu=i686'  CXX='c++'  CXXFLAGS='-O2
-march=i386 -mcpu=i686'  LDFLAGS=''
LIBC: 
lrwxrwxrwx    1 root     root           13 May  2 13:44 /lib/libc.so.6 -> libc-2.2.2.so
-rwxr-xr-x    2 root     root      1236396 Apr  6  2001 /lib/libc-2.2.2.so
-rw-r--r--    1 root     root     26350254 Apr  6  2001 /usr/lib/libc.a
-rw-r--r--    1 root     root          178 Apr  6  2001 /usr/lib/libc.so
Configure command: ./configure  i386-redhat-linux --prefix=/usr --exec-prefix=/usr
--bindir=/usr/bin --sbindir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share
--includedir=/usr/include --libdir=/usr/lib --libexecdir=/usr/libexec
--localstatedir=/var --sharedstatedir=/usr/com --mandir=/usr/share/man
--infodir=/usr/share/info --without-debug --without-readline --enable-shared
--with-extra-charsets=complex --with-bench --localstatedir=/var/lib/mysql
--with-unix-socket-path=/var/lib/mysql/mysql.sock --with-mysqld-user=mysql
--with-extra-charsets=all --with-berkeley-db