Bug #43617 Innodb returns wrong results with timestamp's range value in IN clause
Submitted: 13 Mar 2009 5:01 Modified: 18 Dec 2009 12:14
Reporter: Nidhi Shrotriya Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0.11-bzr OS:Any
Assigned to: Assigned Account
Tags: index_condition_pushdown, optimizer_switch
Triage: Triaged: D2 (Serious)

[13 Mar 2009 5:01] Nidhi Shrotriya
Description:
CREATE TABLE t1(c1 TIMESTAMP NOT NULL, c2 TIMESTAMP NULL, c3 DATE, c4 DATETIME, PRIMARY KEY(c1), UNIQUE INDEX(c2));

Other engines (MyISAM, Maria, Falcon, PBXT)
-------------------------------------------
SELECT * FROM t1 WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') ORDER BY c2;
c1      c2      c3      c4
2038-01-09 03:14:07     2038-01-09 03:14:07     2009-01-05      2009-01-06 00:00:00
SELECT * FROM t1 WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') ORDER BY c2 LIMIT 2;
c1      c2      c3      c4
2038-01-09 03:14:07     2038-01-09 03:14:07     2009-01-05      2009-01-06 00:00:00
SELECT * FROM t1 WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') ORDER BY c2 DESC;
c1      c2      c3      c4
2038-01-09 03:14:07     2038-01-09 03:14:07     2009-01-05      2009-01-06 00:00:00
SELECT * FROM t1 WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') ORDER BY c2 DESC LIMIT 2;
c1      c2      c3      c4
2038-01-09 03:14:07     2038-01-09 03:14:07     2009-01-05      2009-01-06 00:00:00

Innodb:
------------
SELECT * FROM t1 WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') ORDER BY c2;
c1      c2      c3      c4
2038-01-09 03:14:07     NULL    2009-01-05      2009-01-06 00:00:00
SELECT * FROM t1 WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') ORDER BY c2 LIMIT 2;
c1      c2      c3      c4
2038-01-09 03:14:07     NULL    2009-01-05      2009-01-06 00:00:00
SELECT * FROM t1 WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') ORDER BY c2 DESC;
c1      c2      c3      c4
SELECT * FROM t1 WHERE c2 IN ('1971-01-01 00:00:01','2038-01-09 03:14:07') ORDER BY c2 DESC LIMIT 2;
c1      c2      c3      c4

How to repeat:
Please find attached the test case file to populate the table.
[13 Mar 2009 5:02] Nidhi Shrotriya
Test Case

Attachment: timestamp_bug_innodb.test (application/octet-stream, text), 3.78 KiB.

[13 Mar 2009 6:58] Sveta Smirnova
Thank you for the report.

Verified as described. Bug was introduced lately.
[13 Mar 2009 19:56] Calvin Sun
The wrong results are caused by 6.0 new feature "index condition pushdown". If you set the following at the beginning of the test:

SET engine_condition_pushdown=OFF;

The results will be right.
[31 Mar 2009 18:44] Sveta Smirnova
SET engine_condition_pushdown=OFF;

fixes the problem.

Currently bug is repeatable not every time without option SET engine_condition_pushdown=OFF; and not repeatable with the option.

SET optimizer_use_mrr="disable"; periodically leads to wrong results.
[8 Oct 2009 13:02] Guilhem Bichot
goes away / comes back when disabling/enabling ICP in InnoDB
[2 Dec 2009 9:45] Manyi Lu
Contribution from sca@askmonty.org 11/23/09:
http://lists.mysql.com/internals/37545
[2 Dec 2009 9:46] Manyi Lu
Contribution from sca@askmonty.org 11/23/09:
http://lists.mysql.com/internals/37545
[18 Dec 2009 12:14] Jørgen Løland
Duplicate of BUG#42580