| 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: | |
| 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: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
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.