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