Bug #42580 Innodb's ORDER BY ..LIMIT returns no rows for null-safe operator <=> NULL
Submitted: 4 Feb 2009 6:25 Modified: 22 Nov 2010 1:42
Reporter: Nidhi Shrotriya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0 OS:Any
Assigned to: Jørgen Løland CPU Architecture:Any
Tags: index_condition_pushdown, optimizer_switch

[4 Feb 2009 6:25] Nidhi Shrotriya 
Description:
As described in How to repeat section, Innodb's ORDER BY ..LIMIT returns no rows for null-safe operator <=> NULL where the rows with NULL values exist.

SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
gives no rows.

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_equal-to

says that "<=>" is a "NULL-safe" comparison operator, which means that NULL <=> NULL returns 1. This causes the WHERE statement to match the columns where c2 is NULL.

Other SEs MyISAM, Falcon, Maria report correct results.

How to repeat:
CREATE TABLE t1(c1 DATE NOT NULL, c2 DATE NULL, c3 DATETIME, c4 TIMESTAMP, PRIMARY KEY(c1), UNIQUE(c2));

SELECT c1,c2 FROM t1;
c1      c2
0000-00-00 00:00:00     0000-00-00 00:00:00
1983-09-05 13:28:00     1983-09-05 13:28:00
1983-09-07 00:00:00     1983-09-07 00:00:00
1998-12-28 00:00:00     1998-12-28 00:00:00
1998-12-29 00:00:00     1998-12-29 00:00:00
1998-12-30 11:30:45     1998-12-30 11:30:45
1998-12-31 11:30:45     1998-12-31 11:30:45
2007-05-23 09:15:28     2007-05-23 09:15:28
2007-05-25 00:00:00     2007-05-25 00:00:00
2008-01-01 00:00:00     NULL
2008-01-17 00:00:00     NULL
2009-01-29 11:11:27     2009-01-29 00:00:00 

MyISAM/Falcon/Maria:
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2;
c1      c2      c3      c4
2008-01-01 00:00:00     NULL    2008-01-02      2008-01-03 00:00:00
2008-01-17 00:00:00     NULL    NULL    NULL 

SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
c1      c2      c3      c4
2008-01-01 00:00:00     NULL    2008-01-02      2008-01-03 00:00:00
2008-01-17 00:00:00     NULL    NULL    NULL 

Innodb:
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2;
c1      c2      c3      c4
2008-01-01 00:00:00     NULL    2008-01-02      2008-01-03 00:00:00
2008-01-17 00:00:00     NULL    NULL    NULL 

SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
c1      c2      c3      c4 

returns no rows.
[4 Feb 2009 7:05] Sveta Smirnova 
Thank you for the report.

I can not repeat described behavior. Please indicate accurate version of MySQL you use.
[5 Feb 2009 6:50] Nidhi Shrotriya 
I am able to reproduce it with the latest mysql-6.0 from bzr.
[5 Feb 2009 7:38] Sveta Smirnova 
Thank you for the feedback.

I still can not repeat described behavior. Please indicate operating system you use and provide your configuration file and configure options you used to build the server.
[5 Feb 2009 7:49] Nidhi Shrotriya 
I have done-

mysql-sources/mysql-6.0>autoreconf --force --install
mysql-sources/mysql-6.0>./BUILD/compile-pentium-debug-max --prefix=$HOME/mysql-builds/mysql-6.0
mysql-sources/mysql-6.0>make install
[5 Feb 2009 8:06] Nidhi Shrotriya 
Please make sure the engine in create table is Innodb as
CREATE TABLE t1(c1 DATE NOT NULL, c2 DATE NULL, c3 DATETIME, c4 TIMESTAMP, PRIMARY
KEY(c1), UNIQUE(c2)) engine=Innodb;

I didn't mention it as this test (in development) is run with mtr and --mysqld=--default-storage-engine=innodb
[5 Feb 2009 21:42] Sveta Smirnova 
Thank you for the feedback.

You wrote:

> CREATE TABLE t1(c1 DATE NOT NULL, c2 DATE NULL, c3 DATETIME, c4 TIMESTAMP,
> PRIMARY KEY(c1), UNIQUE(c2)) engine=Innodb;

But in this case SELECT c1,c2 FROM t1; would not return results provided (which are of DATETIME type). Please provide correct dump of the table.
[6 Feb 2009 12:36] Nidhi Shrotriya 
Table's dump

Attachment: innodb_42580.txt (text/plain), 2.76 KiB.
[6 Feb 2009 12:38] Nidhi Shrotriya 
Please see the dump file 'innodb_42580.txt' attached.

mysql> select * from innodb_42580;
+------------+------------+---------------------+---------------------+
| c1         | c2         | c3                  | c4                  |
+------------+------------+---------------------+---------------------+
| 0000-00-00 | 0000-00-00 | 2008-01-04 00:00:00 | 2008-01-05 00:00:00 |
| 1983-09-05 | 1983-09-05 | 1983-09-06 13:28:00 | 1983-09-06 13:28:00 |
| 1983-09-07 | 1983-09-07 | 1983-09-08 00:00:00 | 1983-09-08 00:00:00 |
| 1998-12-28 | 1998-12-28 | 1998-12-28 00:00:00 | 1998-12-28 00:00:00 |
| 1998-12-29 | 1998-12-29 | 1998-12-29 00:00:00 | 1998-12-29 00:00:00 |
| 1998-12-30 | 1998-12-30 | 1998-12-30 11:30:45 | 1998-12-30 11:30:45 |
| 1998-12-31 | 1998-12-31 | 1998-12-31 11:30:45 | 1998-12-31 11:30:45 |
| 2007-05-23 | 2007-05-23 | 2007-05-24 09:15:28 | 2007-05-24 09:15:28 |
| 2007-05-25 | 2007-05-25 | 2007-05-26 00:00:00 | 2007-05-26 00:00:00 |
| 2008-01-01 | NULL       | 2008-01-02 00:00:00 | 2008-01-03 00:00:00 |
| 2008-01-17 | NULL       | NULL                | 2009-01-29 11:11:27 |
| 2009-01-29 | 2009-01-29 | 2009-01-29 11:11:27 | 2009-01-29 00:00:00 |
+------------+------------+---------------------+---------------------+
12 rows in set (0.00 sec)

mysql> SELECT * FROM innodb_42580 WHERE c2 <=> NULL ORDER BY c1,c2;
+------------+------+---------------------+---------------------+
| c1         | c2   | c3                  | c4                  |
+------------+------+---------------------+---------------------+
| 2008-01-01 | NULL | 2008-01-02 00:00:00 | 2008-01-03 00:00:00 |
| 2008-01-17 | NULL | NULL                | 2009-01-29 11:11:27 |
+------------+------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM innodb_42580 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
Empty set (0.01 sec)
[9 Feb 2009 6:41] Sveta Smirnova 
Thank you for the feedback.

Verified result inconsistency, although on 32-bit Linux I get 1 row instead of 0 rows:

SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2;
c1      c2      c3      c4
2008-01-17      NULL    NULL    2009-01-29 05:41:27
alter table t1 engine=falcon;
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2;
c1      c2      c3      c4
2008-01-01      NULL    2008-01-02 00:00:00     2008-01-02 18:30:00
2008-01-17      NULL    NULL    2009-01-29 05:41:27
alter table t1 engine=maria;
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2;
c1      c2      c3      c4
2008-01-01      NULL    2008-01-02 00:00:00     2008-01-02 18:30:00
2008-01-17      NULL    NULL    2009-01-29 05:41:27
alter table t1 engine=myisam;
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2;
c1      c2      c3      c4
2008-01-01      NULL    2008-01-02 00:00:00     2008-01-02 18:30:00
2008-01-17      NULL    NULL    2009-01-29 05:41:27
[9 Feb 2009 6:50] Sveta Smirnova 
Only version 6.0 is affected
[11 Feb 2009 9:23] Calvin Sun 
This appears another bug with MRR/condition pushdown in 6.0. The followings are four cases - the second SELECT (whether LIMIT 2 or not) returns wrong result when both MRR and condition pushdown are enabled, and correct results are returned when both are disabled. The server was restarted for each test.

C:\MySQL\MySQL Server 5.1.30\bin>mysql --user=root --password=mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.8-alpha-community-debug MySQL Community Server - Debug (GPL)

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

mysql> use test
Database changed
mysql> SELECT * FROM innodb_42580 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
+------------+------+---------------------+---------------------+
| c1         | c2   | c3                  | c4                  |
+------------+------+---------------------+---------------------+
| 2008-01-01 | NULL | 2008-01-02 00:00:00 | 2008-01-02 18:30:00 |
| 2008-01-17 | NULL | NULL                | 2009-01-29 05:41:27 |
+------------+------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM innodb_42580 WHERE c2 <=> NULL ORDER BY c1,c2;
Empty set (0.00 sec)

mysql> exit
Bye

C:\MySQL\MySQL Server 5.1.30\bin>mysql --user=root --password=mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.8-alpha-community-debug MySQL Community Server - Debug (GPL)

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

mysql> use test
Database changed
mysql> SELECT * FROM innodb_42580 WHERE c2 <=> NULL ORDER BY c1,c2;
+------------+------+---------------------+---------------------+
| c1         | c2   | c3                  | c4                  |
+------------+------+---------------------+---------------------+
| 2008-01-01 | NULL | 2008-01-02 00:00:00 | 2008-01-02 18:30:00 |
| 2008-01-17 | NULL | NULL                | 2009-01-29 05:41:27 |
+------------+------+---------------------+---------------------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM innodb_42580 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
Empty set (0.00 sec)

mysql> exit
Bye

C:\MySQL\MySQL Server 5.1.30\bin>mysql --user=root --password=mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.8-alpha-community-debug MySQL Community Server - Debug (GPL)

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

mysql> use test
Database changed
mysql> SET engine_condition_pushdown=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> SET optimizer_use_mrr='disable';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM innodb_42580 WHERE c2 <=> NULL ORDER BY c1,c2;
+------------+------+---------------------+---------------------+
| c1         | c2   | c3                  | c4                  |
+------------+------+---------------------+---------------------+
| 2008-01-01 | NULL | 2008-01-02 00:00:00 | 2008-01-02 18:30:00 |
| 2008-01-17 | NULL | NULL                | 2009-01-29 05:41:27 |
+------------+------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM innodb_42580 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
+------------+------+---------------------+---------------------+
| c1         | c2   | c3                  | c4                  |
+------------+------+---------------------+---------------------+
| 2008-01-01 | NULL | 2008-01-02 00:00:00 | 2008-01-02 18:30:00 |
| 2008-01-17 | NULL | NULL                | 2009-01-29 05:41:27 |
+------------+------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> exit
Bye

C:\MySQL\MySQL Server 5.1.30\bin>mysql --user=root --password=mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.8-alpha-community-debug MySQL Community Server - Debug (GPL)

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

mysql> use test
Database changed
mysql> SET engine_condition_pushdown=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> SET optimizer_use_mrr='disable';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM innodb_42580 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
+------------+------+---------------------+---------------------+
| c1         | c2   | c3                  | c4                  |
+------------+------+---------------------+---------------------+
| 2008-01-01 | NULL | 2008-01-02 00:00:00 | 2008-01-02 18:30:00 |
| 2008-01-17 | NULL | NULL                | 2009-01-29 05:41:27 |
+------------+------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM innodb_42580 WHERE c2 <=> NULL ORDER BY c1,c2;
+------------+------+---------------------+---------------------+
| c1         | c2   | c3                  | c4                  |
+------------+------+---------------------+---------------------+
| 2008-01-01 | NULL | 2008-01-02 00:00:00 | 2008-01-02 18:30:00 |
| 2008-01-17 | NULL | NULL                | 2009-01-29 05:41:27 |
+------------+------+---------------------+---------------------+
2 rows in set (0.00 sec)
[11 Jun 2009 15:03] Sanjay Manwani 
a
Setting as duplicate of 45029
[8 Jul 2009 6:08] Nidhi Shrotriya 
Closing as duplicate of 45029.
[7 Dec 2009 12:50] Bugs System 
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/93054

3756 Jorgen Loland	2009-12-07
      Bug#42580: "Innodb's ORDER BY ..LIMIT returns no rows for 
                  null-safe operator <=> NULL"
      
      When Index Condition Pushdown is used in InnoDB, the row returned
      to MySQL contains both the index columns and the table columns.
      Before, the null bit of the index columns were not set correctly 
      in the MySQL formated record. This resulted in missing records 
      in queries using the <=> operator.
      
      The patch was originally submitted by Igor and has been through 
      SCA.
     @ mysql-test/include/icp_tests.inc
        Added test for BUG#42580. Note to reviewer: Stole test configuration for ICP from Olav Sandsta. Will wait for push of BUG#43360 before pushing these tests.
     @ mysql-test/r/innodb_icp.result
        Added test for BUG#42580. Note to reviewer: Stole test configuration for ICP from Olav Sandsta. Will wait for push of BUG#43360 before pushing these tests.
     @ mysql-test/r/myisam_icp.result
        Added test for BUG#42580. Note to reviewer: Stole test configuration for ICP from Olav Sandsta. Will wait for push of BUG#43360 before pushing these tests.
     @ mysql-test/t/innodb_icp.test
        Added test for BUG#42580. Note to reviewer: Stole test configuration for ICP from Olav Sandsta. Will wait for push of BUG#43360 before pushing these tests.
     @ mysql-test/t/myisam_icp.test
        Added test for BUG#42580. Note to reviewer: Stole test configuration for ICP from Olav Sandsta. Will wait for push of BUG#43360 before pushing these tests.
     @ storage/innobase/row/row0sel.c
        For index condition pushdown, the null-bit of index columns were
        not copied correctly from the InnoDB row to the MySQL formated
        row.
[16 Dec 2009 8:05] Bugs System 
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/94377

3774 Jorgen Loland	2009-12-16
      Bug#42580: "Innodb's ORDER BY ..LIMIT returns no rows for 
                  null-safe operator <=> NULL"
            
      When Index Condition Pushdown is used in InnoDB, the row returned
      to MySQL contains both the index columns and the table columns.
      Before, the null bit of the index columns were not set correctly 
      in the MySQL formated record. This resulted in missing records 
      in queries using the <=> operator.
      
      This patch also fixes bugs 43617 and 43249. Regression tests for
      these bugs are included.
           
      The patch was originally submitted by Igor and has been through 
      SCA.
     @ mysql-test/include/icp_tests.inc
        Added test for BUG#42580 43617 and 43249. Note to reviewer: Stole test configuration for ICP from Olav Sandsta. Will wait for push of BUG#43360 before pushing these tests.
     @ mysql-test/r/innodb_icp.result
        Added test for BUG#42580 43617 and 43249. Note to reviewer: Stole test configuration for ICP from Olav Sandsta. Will wait for push of BUG#43360 before pushing these tests.
     @ mysql-test/r/myisam_icp.result
        Added test for BUG#42580 43617 and 43249. Note to reviewer: Stole test configuration for ICP from Olav Sandsta. Will wait for push of BUG#43360 before pushing these tests.
     @ mysql-test/suite/optimizer_unfixed_bugs/r/bug43249.result
        Bug fixed; test case incorporated into {innodb|myisam}_icp.test
     @ mysql-test/suite/optimizer_unfixed_bugs/r/bug43617.result
        Bug fixed; test case incorporated into {innodb|myisam}_icp.test
     @ mysql-test/suite/optimizer_unfixed_bugs/t/bug43249.test
        Bug fixed; test case incorporated into {innodb|myisam}_icp.test
     @ mysql-test/suite/optimizer_unfixed_bugs/t/bug43617.test
        Bug fixed; test case incorporated into {innodb|myisam}_icp.test
     @ mysql-test/t/innodb_icp.test
        Added test for BUG#42580 43617 and 43249. Note to reviewer: Stole test configuration for ICP from Olav Sandsta. Will wait for push of BUG#43360 before pushing these tests.
     @ mysql-test/t/myisam_icp.test
        Added test for BUG#42580 43617 and 43249. Note to reviewer: Stole test configuration for ICP from Olav Sandsta. Will wait for push of BUG#43360 before pushing these tests.
     @ storage/innobase/row/row0sel.c
        For index condition pushdown, the null-bit of index columns were
        not copied correctly from the InnoDB row to the MySQL formated
        row.
[26 Feb 2010 11:23] Bugs System 
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/101588

2971 Jorgen Loland	2010-02-26
      Bug#42580: "Innodb's ORDER BY ..LIMIT returns no rows for 
                  null-safe operator <=> NULL"
            
      When Index Condition Pushdown is used in InnoDB, the row returned
      to MySQL contains both the index columns and the table columns.
      Before, the null bit of the index columns were not set correctly 
      in the MySQL formated record. This resulted in missing records 
      in queries using the <=> operator.
      
      This patch also fixes bugs 43617 and 43249. Regression tests for
      these bugs are included.
           
      The patch was originally submitted by Igor and has been through 
      SCA.
     @ mysql-test/include/icp_tests.inc
        Added test for BUG#42580 43617 and 43249
     @ mysql-test/r/innodb_icp.result
        Added test for BUG#42580 43617 and 43249
     @ mysql-test/r/myisam_icp.result
        Added test for BUG#42580 43617 and 43249
     @ mysql-test/suite/optimizer_unfixed_bugs/r/bug43249.result
        Bug fixed; test case incorporated into {innodb|myisam}_icp.test
     @ mysql-test/suite/optimizer_unfixed_bugs/r/bug43617.result
        Bug fixed; test case incorporated into {innodb|myisam}_icp.test
     @ mysql-test/suite/optimizer_unfixed_bugs/t/bug43249.test
        Bug fixed; test case incorporated into {innodb|myisam}_icp.test
     @ mysql-test/suite/optimizer_unfixed_bugs/t/bug43617.test
        Bug fixed; test case incorporated into {innodb|myisam}_icp.test
     @ storage/innobase/row/row0sel.c
        For index condition pushdown, the null-bit of index columns were
        not copied correctly from the InnoDB row to the MySQL formated
        row.
[6 Mar 2010 10:29] Bugs System 
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100306102742-yw9zzgw9ac5r65m5) (version source revid:bar@mysql.com-20100305074327-h09o5lw290s04lcf) (merge vers: 6.0.14-alpha) (pib:16)
[14 Mar 2010 0:58] Paul DuBois 
Noted in 6.0.14 changelog.

With index condition pushdown enabled, InnoDB mishandled the <=>
operator.
[19 May 2010 9:10] Bugs System 
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/108587

3180 oystein.grovlen@sun.com	2010-05-19
      Bug#42580: "Innodb's ORDER BY ..LIMIT returns no rows for 
                  null-safe operator <=> NULL"
            
      When Index Condition Pushdown is used in InnoDB, the row returned
      to MySQL contains both the index columns and the table columns.
      Before, the null bit of the index columns were not set correctly 
      in the MySQL formated record. This resulted in missing records 
      in queries using the <=> operator.
      
      This patch also fixes bugs 43617 and 43249. Regression tests for
      these bugs are included.
           
      The patch was originally submitted by Igor and has been through 
      SCA.
     @ mysql-test/include/icp_tests.inc
        Added test for BUG#42580 43617 and 43249
     @ mysql-test/r/innodb_icp.result
        Added test for BUG#42580 43617 and 43249
     @ mysql-test/r/myisam_icp.result
        Added test for BUG#42580 43617 and 43249
     @ storage/innobase/row/row0sel.c
        For index condition pushdown, the null-bit of index columns were
        not copied correctly from the InnoDB row to the MySQL formated
        row.
[16 Aug 2010 6:35] Bugs System 
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:06] Bugs System 
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[22 Nov 2010 1:42] Paul DuBois 
Bug is not in any released 5.6.x version. No changelog entry needed.