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 CPU Architecture:Any
Tags: index_condition_pushdown, optimizer_switch

[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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.064 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.