| Bug #52849 | datetime index not work | ||
|---|---|---|---|
| Submitted: | 15 Apr 2010 4:32 | Modified: | 4 Aug 2010 20:03 |
| Reporter: | Jarod Liu | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S2 (Serious) |
| Version: | 5.5m3 | OS: | Any (CentOS 5.4 32bit) |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
| Tags: | INDEX | ||
[15 Apr 2010 5:17]
Sveta Smirnova
Thank you for the report. I can not repeat described behavior. Please run OPTIMIZE TABLE, try again and if problem still exists provide dump problem is repeatable with.
[15 Apr 2010 5:18]
Peter Laursen
on 5.1.44 EXPLAIN returns:
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ------ ------ ------------- ------ ------- ------ ------ ------
1 SIMPLE table1 system PRIMARY (NULL) (NULL) (NULL)
1
(with a single row inserted mathcing the WHERE).
This looks OK. 'Possible keys' is detected correctly, but optimizer won't use the key with a single row in table only.
[15 Apr 2010 5:46]
Jarod Liu
@Sveta Smirnova I tried OPTIMIZE TABLE but explain result is the same. And this problem happen in two different machine(both running 5.5m3).
[15 Apr 2010 5:51]
Sveta Smirnova
Thank you for the feedback. Please provide dump problem is repeatable with.
[15 Apr 2010 6:07]
Jarod Liu
how could I provide that. please give me the instructions to do.
[15 Apr 2010 6:19]
Sveta Smirnova
Thank you for the feedback. Run `mysqldump CONNECT_OPTIONS DATABASE_NAME table1 >bug52849.sql` Then attach file bug52849.sql as described in "Files" section of this interface
[15 Apr 2010 6:26]
Jarod Liu
table1
Attachment: bug52849.sql (application/octet-stream, text), 1.80 KiB.
[15 Apr 2010 6:38]
Sveta Smirnova
Thank you for the feedback. So this is empty table? Although as in this case you should get "Impossible WHERE noticed after reading const tables" this looks like not a bug. In my environment I get correct message. Please add some rows to the table in your environment, then try EXPLAIN query again and inform us about results.
[15 Apr 2010 6:49]
Jarod Liu
table2
Attachment: table2.sql.tar.gz (application/gzip, text), 29.00 KiB.
[15 Apr 2010 6:50]
Jarod Liu
mysql> EXPLAIN SELECT * FROM table2 WHERE AtTime>CURDATE(); +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | table2 | ALL | NULL | NULL | NULL | NULL | 9581 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+
[15 Apr 2010 6:59]
Sveta Smirnova
Thank you for the feedback. I still can not repeat described behavior. Please provide your configuration file and indicate accurate package name you use (file name you downloaded).
[15 Apr 2010 7:04]
Jarod Liu
I tried on three machine, two centos 5.4 and one windows xp, the windows version works fine. but both linux encounter this problem, one linux(intel cpu) use package mysql-5.5.3-m3-linux2.6-i686-icc.tar.gz, the other one(amd cpu) use package mysql-5.5.3-m3-linux2.6-i686.tar.gz
[15 Apr 2010 7:10]
Jarod Liu
seems like this is a "SELECT *" specified problem mysql> EXPLAIN SELECT AtTime FROM table2 WHERE AtTime>CURDATE(); +----+-------------+--------+-------+---------------+--------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+--------+---------+------+------+--------------------------+ | 1 | SIMPLE | table2 | index | NULL | AtTime | 8 | NULL | 9581 | Using where; Using index | +----+-------------+--------+-------+---------------+--------+---------+------+------+--------------------------+ mysql> EXPLAIN SELECT COUNT(*) FROM table2 WHERE AtTime>CURDATE(); +----+-------------+--------+-------+---------------+--------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+--------+---------+------+------+--------------------------+ | 1 | SIMPLE | table2 | index | NULL | AtTime | 8 | NULL | 9581 | Using where; Using index | +----+-------------+--------+-------+---------------+--------+---------+------+------+--------------------------+
[15 Apr 2010 8:20]
Peter Laursen
"SELECT * .." or "SELECT AtTime .." makes no difference for me, but 5.1.44 detects the PK as a 'possible key' for the example query - 5.5.3 does not. Windows XP/SP3 here.
-- 5.1.44:
SELECT * FROM table1;
/*
AtTime
-------------------
2010-02-22 18:40:07*/
EXPLAIN SELECT * FROM `table1` WHERE `AtTime` = '2010-02-22 18:40:07';
/*
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ------ ------ ------------- ------ ------- ------ ------ ------
1 SIMPLE table1 system PRIMARY (NULL) (NULL) (NULL) 1
*/
EXPLAIN SELECT AtTime FROM `table1` WHERE `AtTime` = '2010-02-22 18:40:07';
/*
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ------ ------ ------------- ------ ------- ------ ------ ------
1 SIMPLE table1 system PRIMARY (NULL) (NULL) (NULL) 1
*/
-- 5.5.3;
SELECT * FROM table1;
/*
AtTime
-------------------
2010-02-22 18:40:07*/
EXPLAIN SELECT * FROM `table1` WHERE `AtTime` = '2010-02-22 18:40:07';
/*
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ------ ------ ------------- ------ ------- ------ ------ ------
1 SIMPLE table1 system (NULL) (NULL) (NULL) (NULL) 1
*/
EXPLAIN SELECT AtTime FROM `table1` WHERE `AtTime` = '2010-02-22 18:40:07';
/*
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ------ ------ ------------- ------ ------- ------ ------ ------
1 SIMPLE table1 system (NULL) (NULL) (NULL) (NULL) 1
*/
[15 Apr 2010 9:11]
Sveta Smirnova
Thank you for the feedback. I still can not repeat described behavior. Please send us configuration files from Linux machines.
[15 Apr 2010 11:19]
Jarod Liu
config file
Attachment: my.cnf (application/octet-stream, text), 1.18 KiB.
[15 Apr 2010 17:56]
MySQL Verification Team
same results with mysql-5.5.3-m3-linux2.6-x86_64.tar.gz i have no my.cnf
[15 Apr 2010 18:02]
MySQL Verification Team
The reason: why does optimizer try converting charset of a date?? mysql> EXPLAIN extended SELECT * FROM `table2` force index(attime) WHERE `AtTime` = '2010-02-22 18:40:07'; +----+-------------+--------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | table2 | ALL | NULL | NULL | NULL | NULL | 9581 | 100.00 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.05 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select `test`.`table2`.`Id` AS `Id`,`test`.`table2`.`AtTime` AS `AtTime` from `test`.`table2` FORCE INDEX (`attime`) where (convert(`test`.`table2`.`AtTime` using cp850) = '2010-02-22 18:40:07') 1 row in set (0.08 sec)
[15 Apr 2010 18:23]
MySQL Verification Team
workaround:run set names .... . the 5.5.X client sets charset according to the environment it detects, seemingly.
[22 Apr 2010 14:53]
Gleb Shchepa
Simplified test case (repeatable on Linux): CREATE TABLE table2 (Id INT, AtTime DATETIME, KEY AtTime (AtTime)); SET NAMES CP850; INSERT INTO table2 VALUES (1,'2010-04-12 22:30:12'), (2,'2010-04-12 22:30:12'), (3,'2010-04-12 22:30:12'); EXPLAIN extended SELECT * FROM table2 force index(attime) WHERE AtTime = '2010-02-22 18:40:07';
[26 Apr 2010 8:22]
Sveta Smirnova
Bug #53149 was marked as duplicate of this one
[28 Apr 2010 12:22]
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/106806 3025 Alexander Barkov 2010-04-28 Bug#52849 [Com]: datetime index not work Problem: after introduction of "WL#2649 Number-to-string conversions" This query: SET NAMES cp850; -- Or any other non-latin1 ASCII-based character set SELECT * FROM t1 WHERE datetime_column='2010-01-01 00:00:00' started to add extra character set conversion: SELECT * FROM t1 WHERE CONVERT(datetime_column USING cp850)='2010-01-01 00:00:00'; so index on DATETIME column was not used anymore. Fix: avoid convertion of NUMERIC/DATETIME items (i.e. those with derivation DERIVATION_NUMERIC).
[29 Apr 2010 17:56]
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/106972 3026 Alexander Barkov 2010-04-29 Bug#52849 [Com]: datetime index not work Problem: after introduction of "WL#2649 Number-to-string conversions" This query: SET NAMES cp850; -- Or any other non-latin1 ASCII-based character set SELECT * FROM t1 WHERE datetime_column='2010-01-01 00:00:00' started to add extra character set conversion: SELECT * FROM t1 WHERE CONVERT(datetime_column USING cp850)='2010-01-01 00:00:00'; so index on DATETIME column was not used anymore. Fix: avoid convertion of NUMERIC/DATETIME items (i.e. those with derivation DERIVATION_NUMERIC).
[5 May 2010 9:39]
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/107428 3033 Alexander Barkov 2010-05-05 Bug#52849 [Com]: datetime index not work Problem: after introduction of "WL#2649 Number-to-string conversions" This query: SET NAMES cp850; -- Or any other non-latin1 ASCII-based character set SELECT * FROM t1 WHERE datetime_column='2010-01-01 00:00:00' started to add extra character set conversion: SELECT * FROM t1 WHERE CONVERT(datetime_column USING cp850)='2010-01-01 00:00:00'; so index on DATETIME column was not used anymore. Fix: avoid convertion of NUMERIC/DATETIME items (i.e. those with derivation DERIVATION_NUMERIC).
[5 May 2010 9:45]
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/107433 3169 Alexander Barkov 2010-05-05 [merge] Merging bug#52849 from mysql-trunk-bugfixing
[5 May 2010 10: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/107452 3867 Alexander Barkov 2010-05-05 [merge] Merging bug#52849 from mysql-next-mr-bugfixing
[5 May 2010 12:13]
Alexander Barkov
Pushed into mysql-trunk-bugfixing (mysql-5.5.5-m3) Pushed into mysql-6.0-codebase-bugfixing (6.0.14)
[7 May 2010 9:20]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100507091908-vqyhpwf2km0aokno) (version source revid:alik@sun.com-20100507091737-12vceffs11elb25g) (merge vers: 6.0.14-alpha) (pib:16)
[7 May 2010 9:22]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100507091655-349gwq21ursz8y4p) (version source revid:alik@sun.com-20100507091655-349gwq21ursz8y4p) (merge vers: 5.5.5-m3) (pib:16)
[7 May 2010 9:23]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100507091823-nzao4h3qosau4tin) (version source revid:alik@sun.com-20100507091720-ib9r8uny2aeazvas) (pib:16)
[8 May 2010 17:01]
Paul DuBois
Noted in 5.5.5, 6.0.14 changelogs. With a non-latin1 ASCII-based current character set, the server inappropriately converted DATETIME values to strings. This resulted in the optimizer not using indexes on such columns.
[4 Aug 2010 8:10]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:alik@sun.com-20100507093958-2y0wy6svnc3zfgqb) (merge vers: 5.6.99-m4) (pib:18)
[4 Aug 2010 8:25]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:alik@sun.com-20100507093958-2y0wy6svnc3zfgqb) (merge vers: 5.6.99-m4) (pib:18)
[4 Aug 2010 20:03]
Paul DuBois
Bug is not present in any released 5.6.x version.
[4 Dec 2010 9:20]
MySQL Verification Team
Bar, I think a consequence of this fixing bug might be that the following doesn't work on 5.5.5 and later. 5.1 still works fine. set names latin1; drop table if exists c; create table c(a time,b char(8) charset utf8)engine=myisam; select * from c where a=b; We get ERROR 1271 (HY000): Illegal mix of collations for operation '=' Is this expected ??
[3 Jan 2012 17:09]
Carlos Smanioto
I solved the problem with workaround: Use "CAST()" function! Exemple:
mysql> SELECT SQL_NO_CACHE count(1) from t where date_t >= CAST('2011-10-10 00:00:00:00' as datetime);
+----------+
| count(1) |
+----------+
| 11748 |
+----------+
1 row in set, 5 warnings (0.00 sec)
mysql> SELECT SQL_NO_CACHE count(1) from t where date_t >= '2011-10-10 00:00:00:00';
+----------+
| count(1) |
+----------+
| 11748 |
+----------+
1 row in set, 3 warnings (6.59 sec)
Description: datetime index not work How to repeat: CREATE TABLE IF NOT EXISTS `table1` ( `AtTime` datetime NOT NULL, PRIMARY KEY (`AtTime`) USING BTREE ) ENGINE=MyISAM; mysql> EXPLAIN SELECT * -> FROM `table1` -> WHERE `AtTime` = '2010-02-22 18:40:07'; +----+-------------+-----------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | table| ALL | NULL | NULL | NULL | NULL | 1185 | Using where | +----+-------------+-----------+------+---------------+------+---------+------+------+-------------+