Bug #20264 | OR with NULLs produces incorrect result | ||
---|---|---|---|
Submitted: | 5 Jun 2006 5:30 | Modified: | 1 Jul 2006 11:15 |
Reporter: | Taras Di | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 5.0.22-community-nt | OS: | Windows (windows 2000 v5.0 sp4) |
Assigned to: | CPU Architecture: | Any |
[5 Jun 2006 5:30]
Taras Di
[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