MySQL Bugs: #42683: Maria returns wrong results for <= NULL and <> NULL

Bug #42683	Maria returns wrong results for <= NULL and <> NULL
Submitted:	9 Feb 2009 7:41	Modified:	7 May 2009 8:31
Reporter:	Nidhi Shrotriya	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Maria storage engine	Severity:	S3 (Non-critical)
Version:	6.0.10	OS:	Any
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
As mentioned in how to repeat section. 

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

SELECT * FROM t1;
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

With MyISAM/Innodb/Falcon:
----------------------------
SELECT * FROM t1 WHERE c2 <> NULL ORDER BY c1,c2 LIMIT 2;
c1      c2      c3      c4

With Maria:
-----------------------------
SELECT * FROM t1 WHERE c2 <> NULL ORDER BY c1,c2 LIMIT 2;
c1      c2      c3      c4
0000-00-00      0000-00-00      2008-01-04 00:00:00     2008-01-05 00:00:00

With MyISAM/Innodb/Falcon:
-------------------------------
SELECT * FROM t1 WHERE c2 <= NULL ORDER BY c1,c2;
c1      c2      c3      c4

With Maria:
--------------------------
SELECT * FROM t1 WHERE c2 <= NULL ORDER BY c1,c2;
c1      c2      c3      c4
0000-00-00      0000-00-00      2008-01-04 00:00:00     2008-01-05 00:00:00

Thank you for the report.

Verified as described.

test case

Attachment: bug42683.test (application/octet-stream, text), 1.69 KiB.

Similar happens here-
CREATE TABLE t3(c1 TINYINT UNSIGNED NOT NULL, c2 TINYINT NULL, c3 SMALLINT, c4 MEDIUMINT , c5 INT, c6 INTEGER, c7 BIGINT, index idx(c2,c7));

With other engines:
SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c2,c7 LIMIT 2;
c1      c2      c3      c4      c5      c6      c7
SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c2,c7;
c1      c2      c3      c4      c5      c6      c7
SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c2,c7 LIMIT 2;
c1      c2      c3      c4      c5      c6      c7
SELECT * FROM t3 WHERE c2 >= NULL AND c2 < -123 AND c7 = 121 ORDER BY c2,c7;
c1      c2      c3      c4      c5      c6      c7

With Maria:
SELECT * FROM t3 WHERE c2 <> NULL ORDER BY c2,c7 LIMIT 2;
c1      c2      c3      c4      c5      c6      c7
122     -123    124     125     126     127     128
SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c2,c7;
c1      c2      c3      c4      c5      c6      c7
122     -123    124     125     126     127     128
SELECT * FROM t3 WHERE c2 <= NULL ORDER BY c2,c7 LIMIT 2;
c1      c2      c3      c4      c5      c6      c7
122     -123    124     125     126     127     128
SELECT * FROM t3 WHERE c2 >= NULL AND c2 < -123 AND c7 = 121 ORDER BY c2,c7;
c1      c2      c3      c4      c5      c6      c7
122     -123    124     125     126     127     128

goes away when removing HA_DO_INDEX_COND_PUSHDOWN from ha_maria.h

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/69152

2726 Guilhem Bichot	2009-03-13
      Fix for multiple symptoms sharing the same cause:
      BUG#42297 Maria: crash in multi-range-read code
      BUG#42298 Maria: SELECT with join returns no rows
      BUG#42299 Maria: SELECT using cp1251-table returns no rows
      BUG#42681 Maria returns duplicate rows with range access on 'date type
      BUG#42683 Maria returns wrong results for <= NULL and <> NULL 
      BUG#43527 Maria returns no rows on multi range access with limit clause
      BUG#43530 Maria has Issues with range select <>, < with -ve range values on signed index
      BUG#43552 Maria returned wrong rows with range access on float
      BUG#43620 Maria throws 'Got error 176 from storage engine' on a range query
      BUG#43623 Maria returns no rows with date index on range access >, >=, BETWEEN
     @ mysql-test/suite/maria/r/maria.result
        after fixing the bug, we can see one more row in the result. Ah, if we had paid attention
        to maria.result when we added this straight_join test, we would have caught the bug immediately.
     @ mysql-test/suite/maria/r/maria4.result
        result
     @ mysql-test/suite/maria/t/maria4.test
        test for fixed bugs. All its pieces would fail (errno 176, missing rows, too many rows) without the entire
        bugfix of ma_rkey.c
     @ storage/maria/ma_rkey.c
        Because of missing (), icp_res was inverted compared to the result of
        ma_check_index_cond(), which wasn't desired (0==0 -> 1, 1==0 -> 0). We would go
        to "err:" wrongly and thus pick up the value of my_errno which was left from previous
        functions (for example, 176 left by the ha_tina CSV log write at start of statement!);
        sometimes the errno would be returned to client, sometimes it would just cause
        a matching row to be missed.
        This fixed BUG#42297 BUG#42298. But was not enough for BUG#43552:
        - icp_res==2 was not converted to "key not found", causing non-matching rows to be returned.
        Now the usage of icp_res is closer to ma_rnext.c and ma_rnext_same.c.

Pushed into 6.0.11-alpha (revid:guilhem@mysql.com-20090402210815-lu17n4kj8c73cfe8) (version source revid:guilhem@mysql.com-20090313141043-73a6mr7hsrqm3djc) (merge vers: 6.0.11-alpha) (pib:6)

A note has been added to the 6.0.11 changelog: 

When performing SELECT queries on tables containing TIMESTAMP or DATETIME colums with indexes using a WHERE clause comparing the field value to NULL using the <= or <> operators, the wrong information would be returned.