Bug #20264 OR with NULLs produces incorrect result

• Submitted: 5 Jun 2006 5:30
• Modified: 1 Jul 2006 11:15
• Reporter: Taras Di
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.22-community-nt
• OS: Microsoft Windows (windows 2000 v5.0 sp4)
• CPU Architecture: Any

[5 Jun 2006 5:30] Taras Di

Description:
Query below doesn't give correct result:

select distinct(ftId), dId FROM s as s2, ft where (ft.dId = s2.lId and ft.sId = 2) or (ft.dId IS NULL and s2.lId = 1);

It doesn't return those rows where ft.sId = 2. The presence of 'distinct' doesn't effect the correctness. Deleting the 'and s2.lId = 1' criterion results in the query returning the correct result.

More details
--------------

Might be the same bug as mentioned in forum message http://forums.mysql.com/read.php?108,84056,84056#msg-84056 (it has the same characteristics). Originally I thought this was a bug in the Query Browser as the command line client was returning the correct number of results in that particular case.

In the database where this bug originally presented, the result set alternated between including the row where ft.sId = 2 and not including that row. I was unable to repeat this exact behavour using the test case - the test case below always returns the incorrect result.

My guess is the same bug either presents itself only in the Query Browser (not at all in the command line client), alternating in the command line client (as in the original dataset), or always at the command line client.

Might be related to bug #20088, which has a similar type of query. I was hoping the bug fix to #20088 would also fix this problem.

How to repeat:
Run the following code:

DROP TABLE IF EXISTS `ft`;
DROP TABLE IF EXISTS `s`;

create table ft
(
  ftId int unsigned not null auto_increment primary key,
  sId smallint unsigned not null,
  dId smallint unsigned
);

create table s
(
  lId smallint unsigned not null auto_increment primary key
);

insert into ft values(NULL,1,1);
insert into ft values(NULL,2,5);
insert into ft values(NULL,2,6);
insert into ft values(NULL,3,NULL);
insert into ft values(NULL,4,NULL);
insert into ft values(NULL,7,NULL);
insert into ft values(NULL,8,NULL);

insert into s values(NULL);
insert into s values(NULL);
insert into s values(NULL);
insert into s values(NULL);
insert into s values(NULL);
insert into s values(NULL);
insert into s values(NULL);
insert into s values(NULL);

------------------------------------------
Run the query: 

select distinct(ftId), dId FROM s as s2, ft where (ft.dId = s2.lId and ft.sId = 2) or (ft.dId IS NULL and s2.lId = 1);

[5 Jun 2006 5:31] Taras Di

Sorry about the grammatical mistake :)

[1 Jul 2006 11:15] Valeriy Kravchuk

Thank you for your bug report. This issue (repeatable with 5.0.22 on Windows) is solved in our source repository of that product and will be incorporated into the next release:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.24

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS `ft`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS `s`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table ft
    -> (
    ->   ftId int unsigned not null auto_increment primary key,
    ->   sId smallint unsigned not null,
    ->   dId smallint unsigned
    -> );

Query OK, 0 rows affected (0.01 sec)

mysql> create table s
    -> (
    ->   lId smallint unsigned not null auto_increment primary key
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ft values(NULL,1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ft values(NULL,2,5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ft values(NULL,2,6);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ft values(NULL,3,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ft values(NULL,4,NULL);
Query OK, 1 row affected (0.01 sec)

mysql> insert into ft values(NULL,7,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ft values(NULL,8,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into s values(NULL);
Query OK, 1 row affected (0.01 sec)

mysql> insert into s values(NULL);
Query OK, 1 row affected (0.01 sec)

mysql> insert into s values(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into s values(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into s values(NULL);
Query OK, 1 row affected (0.01 sec)

mysql> insert into s values(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into s values(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into s values(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select distinct(ftId), dId FROM s as s2, ft where (ft.dId = s2.lId and f
t.sId = 2) or (ft.dId IS NULL and s2.lId = 1);
+------+------+
| ftId | dId  |
+------+------+
|    4 | NULL |
|    5 | NULL |
|    6 | NULL |
|    7 | NULL |
|    2 |    5 |
|    3 |    6 |
+------+------+
6 rows in set (0.01 sec)

mysql> select ftId, dId FROM s as s2, ft where (ft.dId = s2.lId and ft.sId = 2)
 or (ft.dId IS NULL and s2.lId = 1);
+------+------+
| ftId | dId  |
+------+------+
|    4 | NULL |
|    5 | NULL |
|    6 | NULL |
|    7 | NULL |
|    2 |    5 |
|    3 |    6 |
+------+------+
6 rows in set (0.00 sec)

mysql> select ftId, ft.sId, dId FROM s as s2, ft where (ft.dId = s2.lId and ft.
sId = 2) or (ft.dId IS NULL and s2.lId = 1);
+------+-----+------+
| ftId | sId | dId  |
+------+-----+------+
|    4 |   3 | NULL |
|    5 |   4 | NULL |
|    6 |   7 | NULL |
|    7 |   8 | NULL |
|    2 |   2 |    5 |
|    3 |   2 |    6 |
+------+-----+------+
6 rows in set (0.01 sec)

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://www.mysql.com/doc/en/Installing_source_tree.html