| Bug #43618 | MyISAM&Maria returns wrong results with 'between' on timestamp | ||
|---|---|---|---|
| Submitted: | 13 Mar 2009 5:36 | Modified: | 23 Nov 2010 2:51 |
| Reporter: | Nidhi Shrotriya | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 6.0.11-bzr | OS: | Any |
| Assigned to: | Jørgen Løland | CPU Architecture: | Any |
| Tags: | index_condition_pushdown, optimizer_switch | ||
[13 Mar 2009 5:36]
Nidhi Shrotriya
[13 Mar 2009 7:01]
Sveta Smirnova
Thank you for the report. You forgot to attach test case. Please attach it.
[13 Mar 2009 7:59]
Nidhi Shrotriya
Test Case
Attachment: timestamp_bug_myisam_maria_43618.test (application/octet-stream, text), 2.11 KiB.
[13 Mar 2009 8:00]
Nidhi Shrotriya
Please find it attached.
[13 Mar 2009 8:36]
Sveta Smirnova
Thank you for the report. Verified as described. In version 5.1 bug does not exist.
[13 Mar 2009 10:11]
Nidhi Shrotriya
Another scenario where it occurs with Maria. MyISAM is doing fine here.
CREATE TABLE t1(c1 TIMESTAMP NOT NULL PRIMARY KEY, c2 TIMESTAMP NULL, c3 INT, INDEX idx2(c2));
SET TIMESTAMP=1235553613;
INSERT INTO t1 VALUES(ADDTIME(NOW(),'2 01:01:01'),ADDTIME(NOW(),'2 01:01:01'),9),(ADDTIME(NOW(),'3 01:01:01'),ADDTIME(NOW(),'3 01:01:01'),10),('2001-01-01',NOW(),11),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),12),(ADDTIME(NOW(),'4 01:01:01'),NULL,13),(ADDTIME(NOW(),'5 01:01:01'),NULL,14);
SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
DELETE FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
With Other engines and MyISAM too:
------------------------------------
SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
c1 c2 c3
2009-02-26 13:21:14 2009-02-26 13:21:14 12
2009-02-27 13:21:14 2009-02-27 13:21:14 9
DELETE FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
c1 c2 c3
With Maria:
-----------------
on the second select Maria returns a row
SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
c1 c2 c3
2009-02-26 13:21:14 2009-02-26 13:21:14 12
2009-02-27 13:21:14 2009-02-27 13:21:14 9
DELETE FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1 LIMIT 2;
SELECT * FROM t1 WHERE c1 BETWEEN NOW() AND ADDTIME(NOW(),'2 01:01:01') ORDER BY c1;
c1 c2 c3
2009-02-28 13:21:14 2009-02-28 13:21:14 10
[9 Jul 2009 8:35]
Nidhi Shrotriya
This issue looks ICP related. Verified the first issue with MyISAM gets resolved on engine_condition_pushdown=off. Changing the category to Optimizer.
[8 Oct 2009 13:09]
Guilhem Bichot
bug is in ICP, not engine condition pushdown
[10 Nov 2009 8:39]
Jørgen Løland
The problem goes away if the datetime format in the query is valid: # Simplified example data SELECT * FROM t1; c1 c2 1971-01-01 00:00:00 1980-01-01 00:00:00 1990-01-01 00:00:00 2000-01-01 00:00:00 2007-05-25 00:00:00 2007-05-25 00:00:00 2008-01-01 00:00:00 NULL 2038-01-09 00:00:00 2038-01-09 00:00:00 # Invalid date format SELECT * FROM t1 WHERE c2 BETWEEN '1971-01-01 00:00:01' AND '2010-10-00 00:00:00' ORDER BY c2 DESC LIMIT 2; c1 c2 2038-01-09 00:00:00 2038-01-09 00:00:00 2007-05-25 00:00:00 2007-05-25 00:00:00 Warnings: Warning 1292 Incorrect datetime value: '2010-10-00 00:00:00' for column 'c2' at row 1 Warning 1292 Incorrect datetime value: '2010-10-00 00:00:00' for column 'c2' at row 1 # Valid date format SELECT * FROM t1 WHERE c2 BETWEEN '1971-01-01 00:00:01' AND '2010-10-01 00:00:00' ORDER BY c2 DESC LIMIT 2; c1 c2 2007-05-25 00:00:00 2007-05-25 00:00:00 1990-01-01 00:00:00 2000-01-01 00:00:00
[10 Nov 2009 9:07]
Jørgen Løland
This bug is likely an ICP bug, not a MyISAM bug. InnoDB and Falcon return the correct rows, but the data is retrieved without using ICP: # Explain for InnoDB: explain SELECT * FROM t1 WHERE c1 BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' ORDER BY c1 DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 5 Using where # Explain for Falcon # (Falcon is not in current 6.0, so this is checked with # 6.0 codebase as of Jan 1, 2009): explain SELECT * FROM t1 WHERE c1 BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' ORDER BY c1 DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using MRR; Using filesort
[11 Nov 2009 10:13]
Jørgen Løland
There is no problem if the table is changed to use DATE instead of TIMESTAMP.
In the case of TIMESTAMP columns, the "Incorrect datetime" warning is printed from my_time.c#str_to_datetime() because check_date() returns 1 (error). For DATE, check_date() return 0 (success).
The difference between how these two date formats are checked in check_date() lies in the flags sent to str_to_datetime():
* For TIMESTAMP, str_to_date() is called from FIELD_TIMESTAMP::store()
(field.cc around line 4796):
=> (thd->variables.sql_mode & MODE_NO_ZERO_DATE) |
MODE_NO_ZERO_IN_DATE
* For DATE, str_to_date() is called from FIELD_NEWDATE::store()
(field.cc around line 5725):
=> TIME_FUZZY_DATE |
(thd->variables.sql_mode & (MODE_NO_ZERO_IN_DATE |
MODE_NO_ZERO_DATE |
MODE_INVALID_DATES))
* thd->variables.sql_mode is equal in the two cases
* There is a mysterious comment in Field_timestamp::store():
"We don't want to store invalid or fuzzy datetime values in TIMESTAMP"
[11 Nov 2009 10:49]
Jørgen Løland
As expected, the TIMESTAMP query start behaving correctly if the flags passed from Field_timestamp::store() to str_to_datetime is changed to match that of Field_newdate::store() and the following if is modified to accept dates with month 0: - if (have_smth_to_conv && l_time.month) + if (have_smth_to_conv)
[12 Nov 2009 8:24]
Jørgen Løland
Comparing execution of the query with DATE and TIMESTAMP:
DATE:
- get_quick_record_count finds this range select:
0000-00-00 <= X <= 2010-00-01
- The between-conditions have been pushed to the index (ICP)
- The between-conditions have been removed from the JOIN_TABLE because these
have been pushed to the index
- The first execution of QUICK_SQL_SELECT::get_next() uses
ha_myisam::index_read_map
- The record read from the index is evaluated. There is no condition, so the
record is returned.
TIMESTAMP:
- get_quick_record_count finds this range select:
0000-00-00 <= X <==== MISSING UPPER BOUND
- The between-conditions have been pushed to the index (ICP)
- The between-conditions have been removed from the JOIN_TABLE because these
have been pushed to the index
- The first execution of QUICK_SQL_SELECT::get_next() uses
ha_myisam::index_last *because there is no upper bound in the range*.
index_last does not care about the pushed index conditions.
- The record read from the index is evaluated. There is no condition, so the
record is returned.
[12 Nov 2009 9:23]
Jørgen Løland
- mi_rnext() checks the ICP and returns the next record that satisfies the pushed conditions. - mi_rprev() (which is called by index_last()) does not check the ICP. It returns the previous record regardless of pushed conditions. The best way to fix this bug seems to be to check pushed conditions in mi_rprev() as well.
[12 Nov 2009 12:55]
Bugs System
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/90233 3707 Jorgen Loland 2009-11-12 Bug#43618: MyISAM&Maria returns wrong results with 'between' on timestamp When conditions are pushed to an index (ICP), the same conditions is not checked for records returned by the index. It is assumed that all records qualify for these conditions. However, for MyISAM, these conditions were checked only for forward index lookups (mi_rnext), and not for reverse order lookups (mi_rprev). Thus, records that did not qualify could be returned if mi_rprev was used. This patch makes mi_rprev check the pushed conditions in the same way as mi_rnext does. @ mysql-test/r/select.result Added test for BUG#43618 @ mysql-test/r/select_jcl6.result Added test for BUG#43618 @ mysql-test/r/subselect3.result Records that did not qualify for insertion due to non-matching WHERE condition were inserted due to missing condition check for reverse index lookup with ICP. These records are no longer inserted. @ mysql-test/r/subselect3_jcl6.result Records that did not qualify for insertion due to non-matching WHERE condition were inserted due to missing condition check for reverse index lookup with ICP. These records are no longer inserted. @ mysql-test/t/select.test Added test for BUG#43618 @ storage/myisam/mi_rnext.c Modify comment to reflect that record is skipped if it does not qualify for some pushed condition (ICP) @ storage/myisam/mi_rprev.c Skip record if record does not qualify for a pushed condition (ICP)
[16 Nov 2009 10:14]
Bugs System
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/90493 3711 Jorgen Loland 2009-11-16 Bug#43618: MyISAM&Maria returns wrong results with 'between' on timestamp When conditions are pushed to an index (ICP), the same conditions is not checked for records returned by the index. It is assumed that all records qualify for these conditions. However, for MyISAM, these conditions were checked only for forward index lookups (mi_rnext), and not for reverse order lookups (mi_rprev). Thus, records that did not qualify could be returned if mi_rprev was used. This patch makes mi_rprev check the pushed conditions in the same way as mi_rnext does. @ mysql-test/r/select.result Added test for BUG#43618 @ mysql-test/r/select_jcl6.result Added test for BUG#43618 @ mysql-test/r/subselect3.result Records that did not qualify for insertion due to non-matching WHERE condition were inserted due to missing condition check for reverse index lookup with ICP. These records are no longer inserted. @ mysql-test/r/subselect3_jcl6.result Records that did not qualify for insertion due to non-matching WHERE condition were inserted due to missing condition check for reverse index lookup with ICP. These records are no longer inserted. @ mysql-test/t/select.test Added test for BUG#43618 @ storage/myisam/mi_rnext.c Modify comment to reflect that record is skipped if it does not qualify for some pushed condition (ICP) @ storage/myisam/mi_rprev.c Skip record if record does not qualify for a pushed condition (ICP)
[18 Nov 2009 6:13]
Bugs System
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/90786 3713 Jorgen Loland 2009-11-18 Bug#43618: MyISAM&Maria returns wrong results with 'between' on timestamp When conditions are pushed to an index (ICP), the same conditions is not checked for records returned by the index. It is assumed that all records qualify for these conditions. However, for MyISAM, these conditions were checked only for forward index lookups (mi_rnext), and not for reverse order lookups (mi_rprev). Thus, records that did not qualify could be returned if mi_rprev was used. This patch makes mi_rprev check the pushed conditions in the same way as mi_rnext does. @ mysql-test/r/select.result Added test for BUG#43618 @ mysql-test/r/select_jcl6.result Added test for BUG#43618 @ mysql-test/r/subselect3.result Records that did not qualify for insertion due to non-matching WHERE condition were inserted due to missing condition check for reverse index lookup with ICP. These records are no longer inserted. @ mysql-test/r/subselect3_jcl6.result Records that did not qualify for insertion due to non-matching WHERE condition were inserted due to missing condition check for reverse index lookup with ICP. These records are no longer inserted. @ mysql-test/t/select.test Added test for BUG#43618 @ storage/myisam/mi_rnext.c Modify comment to reflect that record is skipped if it does not qualify for some pushed condition (ICP) @ storage/myisam/mi_rprev.c Skip record if record does not qualify for a pushed condition (ICP)
[18 Nov 2009 6:30]
Jørgen Løland
Pushed to 6.0-codebase-bugfixing
[20 Nov 2009 12:57]
Bugs System
Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:jorgen.loland@sun.com-20091118061420-kb4asp6ltdlehm8s) (merge vers: 6.0.14-alpha) (pib:13)
[26 Nov 2009 15:52]
Paul DuBois
Noted in 6.0.14 changelog. Queries for MyISAM tables that were processed using index condition pushdown could return incorrect results.
[7 May 2010 8:05]
Bugs System
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/107712 3141 oystein.grovlen@sun.com 2010-05-07 Bug#43618: MyISAM&Maria returns wrong results with 'between' on timestamp (Backporting of jorgen.loland@sun.com-20091118061420-kb4asp6ltdlehm8s) When conditions are pushed to an index (ICP), the same conditions is not checked for records returned by the index. It is assumed that all records qualify for these conditions. However, for MyISAM, these conditions were checked only for forward index lookups (mi_rnext), and not for reverse order lookups (mi_rprev). Thus, records that did not qualify could be returned if mi_rprev was used. This patch makes mi_rprev check the pushed conditions in the same way as mi_rnext does. @ mysql-test/r/select.result Added test for BUG#43618 @ mysql-test/r/select_jcl6.result Added test for BUG#43618 @ mysql-test/r/subselect3.result Records that did not qualify for insertion due to non-matching WHERE condition were inserted due to missing condition check for reverse index lookup with ICP. These records are no longer inserted. @ mysql-test/r/subselect3_jcl6.result Records that did not qualify for insertion due to non-matching WHERE condition were inserted due to missing condition check for reverse index lookup with ICP. These records are no longer inserted. @ mysql-test/t/select.test Added test for BUG#43618 @ storage/myisam/mi_rnext.c Modify comment to reflect that record is skipped if it does not qualify for some pushed condition (ICP) @ storage/myisam/mi_rprev.c Skip record if record does not qualify for a pushed condition (ICP)
[16 Aug 2010 6:35]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[3 Sep 2010 1:16]
Marc ALFF
See Bug#56523 Instrumentation regression in myisam/mi_rprev.c
[13 Nov 2010 16:24]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[23 Nov 2010 2:51]
Paul DuBois
Bug does not appear in any released 5.6.x version. No 5.6.1 changelog entry needed.
