Bug #47963 Wrong results when index is used
Submitted: 9 Oct 2009 23:07 Modified: 12 Mar 2010 17:17
Reporter: Todd Farmer (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.1.39, 5.1.41 OS:Any
Assigned to: Ramil Kalimullin CPU Architecture:Any
Tags: regression

[9 Oct 2009 23:07] Todd Farmer 
Description:
Beginning in 5.1.39, queries against InnoDB tables with certain composite keys can produce incorrect results:

mysql> SELECT VERSION();
+----------------------+
| VERSION()            |
+----------------------+
| 5.1.39-community-log |
+----------------------+
1 row in set (0.00 sec)

mysql>
mysql> DROP TABLE IF EXISTS not_found;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `not_found` (
    ->   `col1` varchar(5) NOT NULL DEFAULT '',
    ->   `col2` varchar(15) NOT NULL DEFAULT '',
    ->   `col3` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   KEY `col2` (`col1`, `col2`, `col3`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO not_found (col1, col2, col3) VALUES
    -> ('TEST', 'TEST', '2009-10-09 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> -- not found!
mysql> SELECT * FROM not_found
    -> WHERE col1 = 'TEST'
    -> AND col3 >= '2009-10-09 00:00:00.0'
    -> AND col3 <= '2009-10-09 00:00:00.0';
Empty set (0.00 sec)

mysql>
mysql> -- equivalent logic finds row:
mysql> SELECT * FROM not_found
    -> WHERE col1 = 'TEST'
    -> AND col3 = '2009-10-09 00:00:00.0';
+------+------+---------------------+
| col1 | col2 | col3                |
+------+------+---------------------+
| TEST | TEST | 2009-10-09 00:00:00 |
+------+------+---------------------+
1 row in set (0.00 sec)

mysql>
mysql> -- dropping the key finds it
mysql> ALTER TABLE not_found DROP KEY `col2`;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM not_found
    -> WHERE col1 = 'TEST'
    -> AND col3 >= '2009-10-09 00:00:00.0'
    -> AND col3 <= '2009-10-09 00:00:00.0';
+------+------+---------------------+
| col1 | col2 | col3                |
+------+------+---------------------+
| TEST | TEST | 2009-10-09 00:00:00 |
+------+------+---------------------+
1 row in set (0.00 sec)

mysql>
mysql> -- an index on just col1 and col2 doesn't cause the problem:
mysql> ALTER TABLE not_found ADD KEY `col2` (`col1`,`col2`);
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM not_found
    -> WHERE col1 = 'TEST'
    -> AND col3 >= '2009-10-09 00:00:00.0'
    -> AND col3 <= '2009-10-09 00:00:00.0';
+------+------+---------------------+
| col1 | col2 | col3                |
+------+------+---------------------+
| TEST | TEST | 2009-10-09 00:00:00 |
+------+------+---------------------+
1 row in set (0.00 sec)

mysql> -- but an index on all three does:
mysql> ALTER TABLE not_found DROP KEY `col2`;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE not_found ADD KEY `col2` (`col1`,`col2`, `col3`);
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM not_found
    -> WHERE col1 = 'TEST'
    -> AND col3 >= '2009-10-09 00:00:00.0'
    -> AND col3 <= '2009-10-09 00:00:00.0';
Empty set (0.00 sec)

mysql>
mysql> -- so does an index on first and third columns:
mysql> ALTER TABLE not_found DROP KEY `col2`;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE not_found ADD KEY `col2` (`col1`, `col3`);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM not_found
    -> WHERE col1 = 'TEST'
    -> AND col3 >= '2009-10-09 00:00:00.0'
    -> AND col3 <= '2009-10-09 00:00:00.0';
Empty set (0.00 sec)

mysql>
mysql> EXPLAIN SELECT * FROM not_found
    -> WHERE col1 = 'TEST'
    -> AND col3 >= '2009-10-09 00:00:00'
    -> AND col3 <= '2009-10-09 00:00:00.0'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: not_found
         type: range
possible_keys: col2
          key: col2
      key_len: 15
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.03 sec)

mysql>
mysql> -- eliminating the milliseconds component eliminates problem:
mysql> SELECT * FROM not_found
    -> WHERE col1 = 'TEST'
    -> AND col3 >= '2009-10-09 00:00:00'
    -> AND col3 <= '2009-10-09 00:00:00';
+------+------+---------------------+
| col1 | col2 | col3                |
+------+------+---------------------+
| TEST | TEST | 2009-10-09 00:00:00 |
+------+------+---------------------+
1 row in set (0.03 sec)

mysql>
mysql> -- MyISAM isn't affected:
mysql> ALTER TABLE not_found ENGINE = MyISAM;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM not_found
    -> WHERE col1 = 'TEST'
    -> AND col3 >= '2009-10-09 00:00:00.0'
    -> AND col3 <= '2009-10-09 00:00:00.0';
+------+------+---------------------+
| col1 | col2 | col3                |
+------+------+---------------------+
| TEST | TEST | 2009-10-09 00:00:00 |
+------+------+---------------------+
1 row in set (0.00 sec)

This is particularly problematic because C/J (and perhaps other drivers) explicitly add the millisecond component when converting java.sql.Timestamp objects to strings.

How to repeat:
DROP TABLE IF EXISTS not_found;
CREATE TABLE `not_found` (
  `col1` varchar(5) NOT NULL DEFAULT '',
  `col2` varchar(15) NOT NULL DEFAULT '',
  `col3` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  KEY `col2` (`col1`, `col2`, `col3`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO not_found (col1, col2, col3) VALUES 
('TEST', 'TEST', '2009-10-09 00:00:00');
-- not found!
SELECT * FROM not_found 
WHERE col1 = 'TEST'
AND col3 >= '2009-10-09 00:00:00.0' 
AND col3 <= '2009-10-09 00:00:00.0';

Suggested fix:
Produce correct results.
[9 Oct 2009 23:16] Todd Farmer 
Not limited to composite indexes:

mysql> DROP TABLE IF EXISTS not_found;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `not_found` (
    ->   `col1` varchar(5) NOT NULL DEFAULT '',
    ->   `col2` varchar(15) NOT NULL DEFAULT '',
    ->   `col3` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   KEY `col3` (`col3`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO not_found (col1, col2, col3) VALUES
    -> ('TEST', 'TEST', '2009-10-09 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> -- not found!
mysql> SELECT * FROM not_found
    -> WHERE col1 = 'TEST'
    -> AND col3 >= '2009-10-09 00:00:00.0'
    -> AND col3 <= '2009-10-09 00:00:00.0';
Empty set (0.00 sec)

mysql>
mysql> SELECT * FROM not_found
    -> WHERE col1 = 'TEST'
    -> AND col3 + INTERVAL 0 SECOND >= '2009-10-09 00:00:00.0'
    -> AND col3 + INTERVAL 0 SECOND <= '2009-10-09 00:00:00.0';
+------+------+---------------------+
| col1 | col2 | col3                |
+------+------+---------------------+
| TEST | TEST | 2009-10-09 00:00:00 |
+------+------+---------------------+
1 row in set (0.00 sec)
[11 Oct 2009 8:53] Calvin Sun 
This appears to be a server bug. There is no change in the InnoDB from 5.1.38 to 5.1.39. And I use the latest InnoDB 5.1 repo with 5.1.38, the result is correct.
[11 Oct 2009 9:37] Valeriy Kravchuk 
Verified with recent 5.1.41 from bzr also. Indeed, repeatable only with InnoDB tables, but this is an optimizer bug for sure. Look:

mysql> explain SELECT * FROM not_found WHERE col1 = 'TEST' AND col3 >= '2009-10-09 00:00:00.0' AND col3 <= '2009-10-09 00:00:00.0'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.00 sec)

mysql> explain SELECT * FROM not_found IGNORE INDEX(col2) WHERE col1 = 'TEST' AND col3 >= '2009-10-09 00:00:00.0' AND col3 <= '2009-10-09 00:00:00.0'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: not_found
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

mysql> SELECT * FROM not_found IGNORE INDEX(col2) WHERE col1 = 'TEST' AND col3 >= '2009-10-09 00:00:00.0' AND col3 <= '2009-10-09 00:00:00.0'\G
*************************** 1. row ***************************
col1: TEST
col2: TEST
col3: 2009-10-09 00:00:00
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.1.41-debug |
+--------------+
1 row in set (0.00 sec)

mysql> alter table not_found engine=MyISAM;
Query OK, 1 row affected (0.41 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM not_found WHERE col1 = 'TEST' AND col3 >= '2009-10-09 00:00:00.0' AND col3 <= '2009-10-09 00:00:00.0'\G
*************************** 1. row ***************************
col1: TEST
col2: TEST
col3: 2009-10-09 00:00:00
1 row in set (0.00 sec)

mysql> explain SELECT * FROM not_found WHERE col1 = 'TEST' AND col3 >= '2009-10-09 00:00:00.0' AND col3 <= '2009-10-09 00:00:00.0'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: not_found
         type: system
possible_keys: col2
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: 
1 row in set (0.00 sec)
[12 Oct 2009 18:41] Ramil Kalimullin 
The regression was introduced by fix for bug#46362.
Looks like it doesn't take into account milliseconds component somehow.
[13 Oct 2009 4:43] 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/86638

3163 Ramil Kalimullin	2009-10-13
      Fix for bug#47963: Wrong results when index is used
      
      Problem: using null microsecond part (e.g. "YYYY-MM-DD HH:MM:SS.0000") 
      in a WHERE condition may lead to wrong results due to improper
      DATETIMEs comparison in some cases.
      
      Fix: as we compare DATETIMEs as strings we must trim trailing 0's
      in such cases.
     @ mysql-test/r/innodb_mysql.result
        Fix for bug#47963: Wrong results when index is used
          - test result.
     @ mysql-test/t/innodb_mysql.test
        Fix for bug#47963: Wrong results when index is used
          - test case.
     @ sql/item.cc
        Fix for bug#47963: Wrong results when index is used
          - comparing DATETIMEs trim trailing 0's in the 
        microsecond part.
[18 Oct 2009 16:27] 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/87243

3182 Ramil Kalimullin	2009-10-18 [merge]
      Fix for bug#47963: Wrong results when index is used
      
      Problem: using null microsecond part in a WHERE condition 
      (e.g. WHERE date_time_field <= "YYYY-MM-DD HH:MM:SS.0000") 
      may lead to wrong results due to improper DATETIMEs 
      comparison in some cases.
      
      Fix: comparing DATETIMEs as strings we must trim trailing 0's
      in such cases.
     @ mysql-test/r/innodb_mysql.result
        Fix for bug#47963: Wrong results when index is used
          - test result.
     @ mysql-test/t/innodb_mysql.test
        Fix for bug#47963: Wrong results when index is used
          - test case.
     @ sql/item.cc
        Fix for bug#47963: Wrong results when index is used
          - comparing DATETIMEs as strings we must trim trailing 0's in the 
        microsecond part to ensure
        'YYYY-MM-DD HH:MM:SS.000' == 'YYYY-MM-DD HH:MM:SS'
[4 Nov 2009 9:25] Bugs System 
Pushed into 5.1.41 (revid:joro@sun.com-20091104092152-qz96bzlf2o1japwc) (version source revid:ramil@mysql.com-20091018162655-z4dlolfx5s0zem8l) (merge vers: 5.1.41) (pib:13)
[11 Nov 2009 6:53] Bugs System 
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091110093407-rw5g8dys2baqkt67) (version source revid:alik@sun.com-20091109080109-7dxapd5y5pxlu08w) (merge vers: 6.0.14-alpha) (pib:13)
[11 Nov 2009 6:58] Bugs System 
Pushed into 5.5.0-beta (revid:alik@sun.com-20091109115615-nuohp02h8mdrz8m2) (version source revid:alik@sun.com-20091105090203-cls5j6k3ohu04xpt) (merge vers: 5.5.0-beta) (pib:13)
[17 Nov 2009 16:48] Paul DuBois 
Noted in 5.1.41, 5.5.0, 6.0.14 changelogs.

In some cases, using a null microsecond part in a WHERE condition
(for example, WHERE date_time_field <= 'YYYY-MM-DD HH:MM:SS.0000')
could lead to incorrect results due to improper DATETIME comparison.
[7 Dec 2009 16:44] Paul DuBois 
Noted in 5.1.40sp1 changelog.
[8 Dec 2009 9:29] Bugs System 
Pushed into 5.1.43 (revid:build@mysql.com-20091208092611-pbno5awyb0v38hs7) (version source revid:build@mysql.com-20091208092611-pbno5awyb0v38hs7) (merge vers: 5.1.43) (pib:13)
[16 Dec 2009 8:39] Bugs System 
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091215065750-5m04ogppd5l0pol5) (merge vers: 6.0.14-alpha) (pib:14)
[16 Dec 2009 8:46] Bugs System 
Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alik@sun.com-20091211070127-kl8uvlrv9cr11kva) (merge vers: 5.5.0-beta) (pib:14)
[16 Dec 2009 8:53] Bugs System 
Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)
[18 Dec 2009 10:34] Bugs System 
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:50] Bugs System 
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 11:05] Bugs System 
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:19] Bugs System 
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)
[1 Feb 2010 8:59] Tonci Grgin 
Bug#50710 was marked as duplicate of this report.
[11 Feb 2010 19:49] Mike Hoeffner 
As Todd mentioned this is particularly problematic due to how C/J formats times but only actually newer versions as I recently got bitten by:

http://forums.mysql.com/read.php?39,353773,353773

I'm a big fan of MySQL, but this is what we'd refer to as a head-shaving bug on our dev team. ;-)
[12 Mar 2010 14:14] Bugs System 
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:30] Bugs System 
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:46] Bugs System 
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[22 Mar 2010 16:52] Tonci Grgin 
Bug#52277 was marked as duplicate of this report.
[1 Nov 2010 0:54] Roel Van de Paar 
See bug #50774