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)"
