Bug #9017 Rows are omitted from result set when using JOIN, XOR and ISNULL

Description:
In the following query rows with bug2_section NULL are not returned even when they have section=1: 
select * from Bug1 left join Bug2 on (id_bug1=bug2_id_bug1) where section=1 xor ((not isnull(bug2_section)) and (bug2_section=1));

Detailed description, tables, data and queries are in "How to repeat:"

I tested on:
WinXP SP2 - wrong result - MySQL 4.1.10:
mysql> status;
--------------
C:\Program Files\MySQL\MySQL Server 4.1\bin\mysql.exe  Ver 14.7 Distrib 4.1.10, for Win95/Win98 (i32)

Connection id:          1
Current database:       test
Current user:           ODBC@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         4.1.10
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 19 min 15 sec

Threads: 1  Questions: 30  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 2  Queries per second avg: 0.026
--------------

Linux 2.4.22 - wrong result - MySQL 4.1.10 - note we use 4.0.x client, but server version is 4.1.10:
mysql> status
--------------
mysql  Ver 12.22 Distrib 4.0.18, for pc-linux (i686)

Connection id:          232742
Current database:       test
Current user:           root@spiker
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Server version:         4.1.10-log
Protocol version:       10
Connection:             financedb via TCP/IP
Client characterset:    latin2
Server characterset:    latin2
TCP port:               3306
Uptime:                 19 hours 14 min 0 sec

Threads: 9  Questions: 10306569  Slow queries: 245  Opens: 2515  Flush tables: 4  Open tables: 476  Queries per second avg: 148.853
--------------

Linux 2.4.19-grsec - correct result - MySQL 4.0.13
mysql> status;
--------------
mysql  Ver 12.20 Distrib 4.0.13, for pc-linux (i686)

Connection id:          120478
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Server version:         4.0.13-log
Protocol version:       10
Connection:             Localhost via UNIX socket
Client characterset:    latin2
Server characterset:    latin2
UNIX socket:            /var/run/mysql/mysql.sock
Uptime:                 27 days 21 hours 7 min 21 sec

Threads: 1  Questions: 242099  Slow queries: 1  Opens: 130  Flush tables: 1  Open tables: 64  Queries per second avg: 0.101
--------------

How to repeat:
create table Bug1 (
	id_bug1 int(5) unsigned not null auto_increment,
	section int(5) unsigned not null,
	primary key(id_bug1)
);
insert into Bug1 values(1,1);
insert into Bug1 values(2,1);
insert into Bug1 values(3,1);
insert into Bug1 values(4,2);
insert into Bug1 values(5,2);

create table Bug2 (
	bug2_id_bug1 int(5) unsigned not null,
	bug2_section int(5) unsigned not null,
	primary key(bug2_id_bug1,bug2_section)
);
insert into Bug2 values(1,2);
insert into Bug2 values(2,2);

#print out of condition value for all rows:
select *, section=1 xor ((not isnull(bug2_section)) and (bug2_section=1)) from Bug1 left join Bug2 on (id_bug1=bug2_id_bug1);
+---------+---------+--------------+--------------+----------------------------------------------------------------------+
| id_bug1 | section | bug2_id_bug1 | bug2_section | section=1 xor ((not isnull(bug2_section)) and (Bug2.bug2_section=1)) |
+---------+---------+--------------+--------------+----------------------------------------------------------------------+
|       1 |       1 |            1 |            2 |                                                                    1 |
|       2 |       1 |            2 |            2 |                                                                    1 |
|       3 |       1 |         NULL |         NULL |                                                                    1 |
|       4 |       2 |         NULL |         NULL |                                                                    0 |
|       5 |       2 |         NULL |         NULL |                                                                    0 |
+---------+---------+--------------+--------------+----------------------------------------------------------------------+

#i think MySQL should return rows with id_bug1 1,2, and 3 when I use the same condition in where
select * from Bug1 left join Bug2 on (id_bug1=bug2_id_bug1) where section=1 xor ((not isnull(bug2_section)) and (bug2_section=1));
+---------+---------+--------------+--------------+
| id_bug1 | section | bug2_id_bug1 | bug2_section |
+---------+---------+--------------+--------------+
|       1 |       1 |            1 |            2 |
|       2 |       1 |            2 |            2 |
+---------+---------+--------------+--------------+
#oops - row with id_bug1=3 is missing

#strange workaround - extend condition with "or isnull(id_bug1)" - this always evaluates as false - column id_bug1 is unsigned not null 
select * from Bug1 left join Bug2 on (id_bug1=bug2_id_bug1) where (section=1 xor ((not isnull(bug2_section)) and (bug2_section=1))) or isnull(id_bug1);
+---------+---------+--------------+--------------+
| id_bug1 | section | bug2_id_bug1 | bug2_section |
+---------+---------+--------------+--------------+
|       1 |       1 |            1 |            2 |
|       2 |       1 |            2 |            2 |
|       3 |       1 |         NULL |         NULL |
+---------+---------+--------------+--------------+
#this is ok - this result is what returns MySQL Ver 12.20 Distrib 4.0.13, for pc-linux (i686) for the query without "or isnull(id_bug1)"

Suggested fix:
#strange workaround - extend condition with "or isnull(id_bug1)" - this always evaluates to false - column id_bug1 is unsigned not null 

select * from Bug1 left join Bug2 on (id_bug1=bug2_id_bug1) where (section=1 xor ((not isnull(bug2_section)) and (bug2_section=1))) or isnull(id_bug1);
+---------+---------+--------------+--------------+
| id_bug1 | section | bug2_id_bug1 | bug2_section |
+---------+---------+--------------+--------------+
|       1 |       1 |            1 |            2 |
|       2 |       1 |            2 |            2 |
|       3 |       1 |         NULL |         NULL |
+---------+---------+--------------+--------------+
#this is ok - this result is what returns MySQL Ver 12.20 Distrib 4.0.13, for pc-linux (i686) for the query without "or isnull(id_bug1)"

SQL Test case - just copy into test db - creates table, add records, executes test queries

Attachment: queries.sql (text/plain), 1.35 KiB.

Detailed description in txt - queries, result, status from test machines

Attachment: description.txt (text/plain), 5.48 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/22969

Fixed in 4.1.11 and 5.0.4

Noted in 4.1.11, 5.0.4 changelogs.