Bug #57018 Incorrect result when using BETWEEN comparison on an indexed DATETIME column.

• Submitted: 25 Sep 2010 15:32
• Modified: 25 Sep 2010 15:59
• Reporter: John Brown
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.1.49, 5.1.52-bzr
• OS: Any
• CPU Architecture: Any
• Tags: between, datetime, INDEX
• Triage: Triaged: D2 (Serious)

[25 Sep 2010 15:32] John Brown

Description:
Incorrect result when using BETWEEN comparison on an indexed DATETIME column.

How to repeat:
CREATE TABLE games
(
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  start DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (id),
  KEY ind_start (start)
);

INSERT INTO games VALUES
(0, '2010-10-01 16:00:00'),
(0, '2010-10-01 17:00:00'),
(0, '2010-10-01 18:00:00');

incorrect result:

SELECT *
FROM games
WHERE start BETWEEN SUBTIME('2010-10-01 17:00:00', '00:30:00')
                AND ADDTIME('2010-10-01 17:00:00', '00:30:00');

correct result when the ind_start index is deleted or not used:

SELECT *
FROM games IGNORE INDEX (ind_start)
WHERE start BETWEEN SUBTIME('2010-10-01 17:00:00', '00:30:00')
                AND ADDTIME('2010-10-01 17:00:00', '00:30:00');

Suggested fix:
Fix the incorrect behavior when using a datetime column index.

[25 Sep 2010 15:59] Valeriy Kravchuk

Thank you for the bug report. Verified as described with current mysql-5.1 from bzr:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.52-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE games
    -> (
    ->   id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   start DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   PRIMARY KEY (id),
    ->   KEY ind_start (start)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> 
mysql> INSERT INTO games VALUES
    -> (0, '2010-10-01 16:00:00'),
    -> (0, '2010-10-01 17:00:00'),
    -> (0, '2010-10-01 18:00:00');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT *
    -> FROM games
    -> WHERE start BETWEEN SUBTIME('2010-10-01 17:00:00', '00:30:00')
    ->                 AND ADDTIME('2010-10-01 17:00:00', '00:30:00');
Empty set (0.04 sec)

mysql> SELECT *
    -> FROM games IGNORE INDEX (ind_start)
    -> WHERE start BETWEEN SUBTIME('2010-10-01 17:00:00', '00:30:00')
    ->                 AND ADDTIME('2010-10-01 17:00:00', '00:30:00');
+----+---------------------+
| id | start               |
+----+---------------------+
|  2 | 2010-10-01 17:00:00 |
+----+---------------------+
1 row in set (0.36 sec)