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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.100 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.