MySQL Bugs: #42760: Select doesn't return desired results when we have null values

Bug #42760	Select doesn't return desired results when we have null values
Submitted:	11 Feb 2009 12:57	Modified:	12 Mar 2010 17:43
Reporter:	Josep Maria Pons Roca	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.67, 5.0.74, 5.0.79-bzr, 5.1.32-bzr, 6.0.10	OS:	Linux
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	ref_or_null, regression

Description:
A working query in older mysql versions is not working. This doesn't return the desired results. Changing the index used, taking out the order by, or taking out some conditions make that this return some results.

How to repeat:
CREATE TABLE `test` (
  `id_test` int(11) unsigned NOT NULL auto_increment,
  `a` int(11) unsigned NOT NULL default '0',
  `b` varchar(100) NOT NULL default '',
  `c` int(11) unsigned default NULL,
  PRIMARY KEY  (`id_test`),
  UNIQUE KEY `index_a_c` (`a`,`c`),
  KEY `index_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

insert into test values (null, 1, "a", 10);
insert into test values (null, 2, "b", null);

This query doesn't return any results:
select * from test where a=2 and (c=10 or c is null) order by c desc limit 1;
Empty set (0.00 sec)

These querys returns results:
select * from test where a=2 and (c is null) order by c desc limit 1;
+---------+---+---+------+
| id_test | a | b | c    |
+---------+---+---+------+
|       2 | 2 | b | NULL | 
+---------+---+---+------+
1 row in set (0.00 sec)

select * from test force key (index_a) where a=2 and (c=10 or c is null) order by c desc limit 1;
+---------+---+---+------+
| id_test | a | b | c    |
+---------+---+---+------+
|       2 | 2 | b | NULL | 
+---------+---+---+------+
1 row in set (0.00 sec)

select * from test where a=2 and (c=10 or c is null) limit 1;
+---------+---+---+------+
| id_test | a | b | c    |
+---------+---+---+------+
|       2 | 2 | b | NULL | 
+---------+---+---+------+
1 row in set (0.00 sec)

As you can see these querys are modifications of the first.

The first query in Mysql 5.0.45-log works with no problem.

Thank you for the bug report. Verified just as described:

openxs@suse:/home2/openxs/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
Server version: 5.0.79-debug Source distribution

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

mysql> drop table test;
ERROR 1051 (42S02): Unknown table 'test'
mysql> CREATE TABLE `test` (
    ->   `id_test` int(11) unsigned NOT NULL auto_increment,
    ->   `a` int(11) unsigned NOT NULL default '0',
    ->   `b` varchar(100) NOT NULL default '',
    ->   `c` int(11) unsigned default NULL,
    ->   PRIMARY KEY  (`id_test`),
    ->   UNIQUE KEY `index_a_c` (`a`,`c`),
    ->   KEY `index_a` (`a`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test values (null, 1, "a", 10);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values (null, 2, "b", null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test where a=2 and (c=10 or c is null) order by c desc limit 1;
Empty set (0.03 sec)

mysql> select * from test where a=2 and (c is null) order by c desc limit 1;
+---------+---+---+------+
| id_test | a | b | c    |
+---------+---+---+------+
|       4 | 2 | b | NULL |
+---------+---+---+------+
1 row in set (0.00 sec)

Looks like ref_or_null access path is broken:

mysql> explain select * from test where a=2 and (c=10 or c is null) order by c desc limit 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ref_or_null
possible_keys: index_a_c,index_a
          key: index_a_c
      key_len: 9
          ref: const,const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

mysql> explain select * from test where a=2 and (c is null) order by c desc limit 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ref
possible_keys: index_a_c,index_a
          key: index_a_c
      key_len: 9
          ref: const,const
         rows: 1
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

5.1 is also affected:

openxs@suse:/home2/openxs/dbs/5.1> 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
Server version: 5.1.32-debug Source distribution

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

mysql> CREATE TABLE `test` (   `id_test` int(11) unsigned NOT NULL auto_increment,   `a` int(11) unsigned NOT NULL default '0',   `b` varchar(100) NOT NULL default '',   `c` int(11) unsigned default NULL,   PRIMARY KEY  (`id_test`),   UNIQUE KEY `index_a_c` (`a`,`c`),   KEY `index_a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test values (null, 1, "a", 10);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values (null, 2, "b", null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test where a=2 and (c=10 or c is null) order by c desc limit 1;
Empty set (0.03 sec)

mysql> select * from test where a=2 and (c is null) order by c desc limit 1;
+---------+---+---+------+
| id_test | a | b | c    |
+---------+---+---+------+
|       4 | 2 | b | NULL |
+---------+---+---+------+
1 row in set (0.01 sec)

Engine independent. Same with MyISAM

Both ORDER BY ASC and DESC trigger the bug.

Interesting enough both IGNORE and FORCE INDEX (index_a_c) produce correct result. index_a_c is the one the optimizer uses without hints when the result set is empty according to the explain. Tested with both MyISAM and InnoDB on 5.0.79 from bzr revno: 2741

Doesn't make any sense to me:

mysql> SELECT VERSION();
+------------------+
| VERSION()        |
+------------------+
| 5.0.79-debug-log | 
+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
Empty set (0.00 sec)

mysql> SELECT * FROM test IGNORE INDEX (index_a_c) WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+---------+---+---+------+
| id_test | a | b | c    |
+---------+---+---+------+
|       4 | 2 | b | NULL | 
+---------+---+---+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test FORCE INDEX (index_a_c) WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
+---------+---+---+------+
| id_test | a | b | c    |
+---------+---+---+------+
|       4 | 2 | b | NULL | 
+---------+---+---+------+
1 row in set (0.00 sec)

With force key (index_a_c) I can see some strange differences, in extra col:

mysql> explain select * from test force key (index_a_c) where a=2 and (c=10 or c is null) order by c desc limit 1;
+----+-------------+-------+-------------+---------------+-----------+---------+-------------+------+-----------------------------+
| id | select_type | table | type        | possible_keys | key       | key_len | ref         | rows | Extra                       |
+----+-------------+-------+-------------+---------------+-----------+---------+-------------+------+-----------------------------+
|  1 | SIMPLE      | test  | ref_or_null | index_a_c     | index_a_c | 9       | const,const |    2 | Using where; Using filesort | 
+----+-------------+-------+-------------+---------------+-----------+---------+-------------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> explain select * from test where a=2 and (c=10 or c is null) order by c desc limit 1;
+----+-------------+-------+-------------+-------------------+-----------+---------+-------------+------+-------------+
| id | select_type | table | type        | possible_keys     | key       | key_len | ref         | rows | Extra       |
+----+-------------+-------+-------------+-------------------+-----------+---------+-------------+------+-------------+
|  1 | SIMPLE      | test  | ref_or_null | index_a_c,index_a | index_a_c | 9       | const,const |    1 | Using where | 
+----+-------------+-------+-------------+-------------------+-----------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

I'm not sure, but... can it be some kind of query parsing error?

This is a regression bug happened some time in between 5.0.51 and 5.0.67. 5.0.51 works OK:

C:\work\mysql-5.0.51a-win32\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.51a-community MySQL Community Edition (GPL)

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

mysql> CREATE TABLE `test` (
    ->   `id_test` int(11) unsigned NOT NULL auto_increment,
    ->   `a` int(11) unsigned NOT NULL default '0',
    ->   `b` varchar(100) NOT NULL default '',
    ->   `c` int(11) unsigned default NULL,
    ->   PRIMARY KEY  (`id_test`),
    ->   UNIQUE KEY `index_a_c` (`a`,`c`),
    ->   KEY `index_a` (`a`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> insert into test values (null, 1, "a", 10);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values (null, 2, "b", null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test where a=2 and (c=10 or c is null) order by c desc limi
t 1;
+---------+---+---+------+
| id_test | a | b | c    |
+---------+---+---+------+
|       4 | 2 | b | NULL |
+---------+---+---+------+
1 row in set (0.05 sec)

mysql> explain select * from test where a=2 and (c=10 or c is null) order by c d
esc limit 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ref
possible_keys: index_a_c,index_a
          key: index_a
      key_len: 4
          ref: const
         rows: 1
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

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/commits/87793

2823 Georgi Kodinov	2009-10-22
      Bug #42760: Select doesn't return desired results when we have null
      values
      
      We should re-set the access method functions when changing the access
      method when switching to another index to avoid sorting.
      
      Fixed by doing a little re-engineering : encapsulating all the function
      assignment into a special function and calling it when flipping the 
      indexes.

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/commits/89592

2823 Georgi Kodinov	2009-11-06
      Bug #42760: Select doesn't return desired results when we have null
      values
      
      We should re-set the access method functions when changing the access
      method when switching to another index to avoid sorting.
      
      Fixed by doing a little re-engineering : encapsulating all the function
      assignment into a special function and calling it when flipping the 
      indexes.

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/commits/89842

2843 Georgi Kodinov	2009-11-06
      Bug #42760: Select doesn't return desired results when we have null
      values
      
      We should re-set the access method functions when changing the access
      method when switching to another index to avoid sorting.
      
      Fixed by doing a little re-engineering : encapsulating all the function
      assignment into a special function and calling it when flipping the 
      indexes.

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/commits/89887

2843 Georgi Kodinov	2009-11-10
       Bug #42760: Select doesn't return desired results when we have null
       values
       
       We should re-set the access method functions when changing the access
       method when switching to another index to avoid sorting.
       
       Fixed by doing a little re-engineering : encapsulating all the function
       assignment into a special function and calling it when flipping the 
       indexes.

Pushed into 5.0.89 (revid:joro@sun.com-20091202075830-mzl79q7mc1v72pf1) (version source revid:joro@sun.com-20091110082141-ldr8p6s1joczve2j) (merge vers: 5.0.88) (pib:13)

Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:joro@sun.com-20091110085843-uiz4zu251q16v5nt) (merge vers: 5.1.41) (pib:13)

On the current mysql-5.1 tree:

Test case provided in 'How to repeat section' still does not work -- with the same structure and data, the same SELECT returns an empty result set.

In regard to the test added with this bugfix, the test itself passes:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  a INT,
  c INT,
  UNIQUE KEY a_c (a,c),
  KEY (a));

INSERT INTO t1 VALUES (1, 10), (2, NULL);

EXPLAIN
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;
SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c;

#+-----+
#| col |
#+-----+
#|   1 |
#+-----+

# But the same select with ORDER BY c DESC returns an empty set:

SELECT 1 AS col FROM t1 WHERE a=2 AND (c=10 OR c IS NULL) ORDER BY c DESC;
# Empty set (0.00 sec)

Additional note: 

I'm observing a slight change in behavior in regard to NULL values, which is not explicitly mentioned in this bug, but I suppose might be related to the bugfix:

DROP TABLE IF EXISTS t3;
CREATE TABLE t3(c1 TIME NOT NULL, c2 TIME NULL);
INSERT INTO t3 VALUES ('123456',null);

#In the current version, the following SELECT returns the row from the table:

SELECT * FROM t3 WHERE c2 < '-838:59:59';
#+----------+------+
#| c1       | c2   |
#+----------+------+
#| 12:34:56 | NULL |
#+----------+------+
#1 row in set (0.00 sec)

#In previous versions, it would return an empty result set:

SELECT * FROM t3 WHERE c2 < '-838:59:59';
#Empty set (0.00 sec)

It seems OK as long as it is expected/planned.

In regard to the note about comparing to NULL -- the behavior is not quite consistent when it comes to integer types, e.g.:

DROP TABLE IF EXISTS t3;
CREATE TABLE t3(c1 BIGINT UNSIGNED NOT NULL, c2 BIGINT NULL, c3 INT NULL);
INSERT INTO t3 VALUES(105,NULL,NULL);
SELECT * FROM t3 WHERE c2 < -102;

#+-----+------+------+
#| c1  | c2   | c3   |
#+-----+------+------+
#| 105 | NULL | NULL |
#+-----+------+------+
#1 row in set (0.00 sec)

SELECT * FROM t3 WHERE c3 < -102;

# Empty set (0.00 sec)

That is, for BIGINT <NULL> < <NUMBER> returns true, while for INT (SMALLINT, TINYINT) it does not.

My fix was only fixing the case when the ORDER BY was not descending. Re-opening the bug.

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/commits/93074

2861 Georgi Kodinov	2009-12-07
      Bug #42760: Select doesn't return desired results when we have null values
      
      Part 2 : 
      There was a special optimization on the ref access method for 
      ORDER BY ... DESC that was set without actually looking on the type of the 
      selected index for ORDER BY.
      Fixed the SELECT ... ORDER BY .. DESC (it uses a different code path compared
      to the ASC that has been fixed with the previous fix).

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/commits/93545

2865 Georgi Kodinov	2009-12-07
      Bug #42760: Select doesn't return desired results when we have null values
      
      Part 2 : 
      There was a special optimization on the ref access method for 
      ORDER BY ... DESC that was set without actually looking on the type of the 
      selected index for ORDER BY.
      Fixed the SELECT ... ORDER BY .. DESC (it uses a different code path compared
      to the ASC that has been fixed with the previous fix).

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alexey.kopytov@sun.com-20091124081906-6pqi7e7sajimog71) (merge vers: 5.5.0-beta) (pib:14)

Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)

Noted in 5.5.1, 6.0.14 changelogs.

Comparison with NULL values sometimes did not produce a correct
result. 

Setting report to NDI pending push to 5.0.x, 5.1.x.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091219082307-f3i4fn0tm8trb3c0) (version source revid:alik@sun.com-20091216180721-eoa754i79j4ssd3m) (merge vers: 6.0.14-alpha) (pib:15)

Pushed into 5.5.1-m2 (revid:alik@sun.com-20091219082021-f34nq4jytwamozz0) (version source revid:alexey.kopytov@sun.com-20091211164058-ycpe0f20d1c4h1gl) (merge vers: 5.5.0-beta) (pib:15)

Pushed into mysql-next-mr (revid:alik@sun.com-20091219082213-nhjjgmphote4ntxj) (version source revid:alik@sun.com-20091216180221-a5ps59gajad3pip9) (pib:15)

Setting report to NDI pending push to 5.0.x, 5.1.x.

Pushed into 5.0.90 (revid:joro@sun.com-20100114082402-05fod2h6z9x9wok8) (version source revid:aelkin@mysql.com-20091214144410-630vanwyllvvacad) (merge vers: 5.0.89) (pib:16)

Noted in 5.0.90 changelog.

Setting report to NDI pending push to 5.1.x, Celosia.

Pushed into 5.1.43 (revid:joro@sun.com-20100115085139-qkh0i0fpohd9u9p5) (version source revid:joro@sun.com-20091210153801-ozpah1jkucqjdufw) (merge vers: 5.1.42) (pib:16)

Noted in 5.1.43 changelog.

Setting report to NDI pending push to Celosia.

Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

Fixed in earlier 5.1.x, 5.5.x.