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:
None 
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
Triage: Triaged: D2 (Serious)

[15 Apr 2010 4:32] Jarod Liu
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 |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
[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] Shane Bester
same results with mysql-5.5.3-m3-linux2.6-x86_64.tar.gz
i have no my.cnf
[15 Apr 2010 18:02] Shane Bester
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] Shane Bester
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] Shane Bester
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)