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:
None 
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
Description:
MyISAM/Maria:
-------------
SELECT * FROM t1 WHERE c1 BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' ORDER BY c1 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
2009-01-29 11:11:27     2009-01-29 11:11:27     2009-01-29      2009-01-29 11:11:27
Warnings:
Warning 1292    Incorrect datetime value: '2010-00-01 00:00:00' for column 'c1' at row 1
Warning 1292    Incorrect datetime value: '2010-00-01 00:00:00' for column 'c1' at row 1

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      c3      c4
2038-01-09 03:14:07     2038-01-09 03:14:07     2009-01-05      2009-01-06 00:00:00
2009-01-29 11:11:27     2009-01-29 11:11:27     2009-01-29      2009-01-29 11:11:27
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

Innodb/Falcon:
---------------
SELECT * FROM t1 WHERE c1 BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' ORDER BY c1 DESC LIMIT 2;
c1      c2      c3      c4
2009-01-29 11:11:27     2009-01-29 11:11:27     2009-01-29      2009-01-29 11:11:27
2008-01-01 00:00:00     NULL    2008-01-02      2008-01-03 00:00:00

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      c3      c4
2009-01-29 11:11:27     2009-01-29 11:11:27     2009-01-29      2009-01-29 11:11:27
2007-05-27 00:00:00     2007-05-25 00:00:00     2007-05-26      2007-05-26 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

How to repeat:
Please find attached the test case file to reproduce.
[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.