| Bug #31710 | date type problems with using index | ||
|---|---|---|---|
| Submitted: | 19 Oct 2007 9:33 | Modified: | 19 Oct 2007 9:51 |
| Reporter: | Tom OYA | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S1 (Critical) |
| Version: | 5.0.45 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | date, datetime, INDEX, SELECT, type | ||
[19 Oct 2007 9:51]
Sveta Smirnova
Thank you for the report. I can not repeat described behaviour with current development sources, although bug is repeatable with version 5.0.45. Please wait next release.
[14 Nov 2007 14:04]
Holger Thiel
I can replicate this bug on 5.0.45. Use this table with and without the index "compositeindex". You will get different plans and different results!!! In Version 4.1.10 the behaviour is not so: both statements are treated the same. It's an problem with the index and the optimizer. CREATE TABLE `basetable` ( `id` int(11) NOT NULL default '0', `name_id` int(11) NOT NULL default '0', `month` date NOT NULL default '1970-01-01', `task_id` int(11) NOT NULL default '0', PRIMARY KEY (`effort_id`), KEY `name_id` (`name_id`), KEY `month` (`month`), KEY `task_id` (`task_id`), KEY `compositeindex` (`name_id`,`month`,`task_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 mysql> EXPLAIN select count(*) from basetable where name_id = 655464 and month= "2007-11-04"; +----+-------------+------------+------+--------------------------------------------+----------------------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+--------------------------------------------+----------------------+---------+-------------+------+-------------+ | 1 | SIMPLE | basetable | ref | name_id,month,compositeindex | compositeindex | 7 | const,const | 5 | Using index | +----+-------------+------------+------+--------------------------------------------+----------------------+---------+-------------+------+-------------+ 1 row in set (0.00 sec) mysql> EXPLAIN select count(*) from basetable where name_id = 655464 and month= "2007-11-04 00:00:00"; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ 1 row in set (0.00 sec)
Description: MySQL Version 5.0.41,5.0.45 When creating index, the result of select query which includes datetime type seems wrong. The problem seems the transformation between date type and datetime type. How to repeat: MySQL Version 5.0.41,5.0.45 mysql> use test; Database changed mysql> create table dtest (d date); Query OK, 0 rows affected (0.05 sec) mysql> insert into dtest values ('2007-01-10'); Query OK, 1 row affected (0.00 sec) mysql> insert into dtest values ('2007-01-10'); Query OK, 1 row affected (0.00 sec) mysql> select * from dtest where d = '2007-01-10 00:00:00'; +------------+ | d | +------------+ | 2007-01-10 | | 2007-01-10 | +------------+ 2 rows in set (0.00 sec) mysql> create index index_dtest_d on dtest (d); Query OK, 2 rows affected (0.36 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from dtest where d = '2007-01-10 00:00:00'; Empty set (0.00 sec)