Bug #57723 Wrong results when milisecond precision is passed to WHERE >= clause

• Submitted: 26 Oct 2010 1:47
• Modified: 26 Oct 2010 2:01
• Reporter: Roel Van de Paar
• Status: Duplicate
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.1.49,5.5.6rc
• OS: Any
• CPU Architecture: Any

[26 Oct 2010 1:47] Roel Van de Paar

Description:
mysql> SELECT * FROM `a` WHERE updated >= '2010-04-20 12:00:00' AND updated < '2010-04-20 12:00:04';    /* Works */
+---------------------+---------------------+
| added               | updated             |
+---------------------+---------------------+
| 0000-00-00 00:00:00 | 2010-04-20 12:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM `a` WHERE updated >= '2010-04-20 12:00:00' AND updated <= '2010-04-20 12:00:04.0'; /* Works */
+---------------------+---------------------+
| added               | updated             |
+---------------------+---------------------+
| 0000-00-00 00:00:00 | 2010-04-20 12:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM `a` WHERE updated >= '2010-04-20 12:00:00.0' AND updated <= '2010-04-20 12:00:04'; /* Fails */
Empty set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
  `added` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
   KEY `IDX_UPDATED` (`updated`)
) ENGINE=InnoDB;
INSERT INTO `a` VALUES ('0000-00-00 00:00:00','2010-04-20 12:00:00');
SELECT * FROM `a` WHERE updated >= '2010-04-20 12:00:00' AND updated < '2010-04-20 12:00:04';    /* Works */
SELECT * FROM `a` WHERE updated >= '2010-04-20 12:00:00' AND updated <= '2010-04-20 12:00:04.0'; /* Works */
SELECT * FROM `a` WHERE updated >= '2010-04-20 12:00:00.0' AND updated <= '2010-04-20 12:00:04'; /* Fails */
SELECT * FROM `a` WHERE updated >= ADDDATE('2010-04-20 12:00:00.0',0) AND updated <= '2010-04-20 12:00:04';  /* Workaround */

[26 Oct 2010 2:01] Roel Van de Paar

Marking this as a duplicate of bug #50774