| Bug #47963 | Wrong results when index is used | ||
|---|---|---|---|
| Submitted: | 10 Oct 1:07 | Modified: | 17 Nov 17:48 |
| Reporter: | Todd Farmer | ||
| Status: | Closed | ||
| Category: | Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.1.39, 5.1.41 | OS: | Any |
| Assigned to: | Ramil Kalimullin | Target Version: | 5.1.41+ |
| Tags: | regression | ||
| Triage: | Triaged: D2 (Serious) | ||
[10 Oct 1: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 10: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 11: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 20: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 6: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 18: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 10: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 7: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 7: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 17: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.
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.