MySQL Bugs: #1543: incorrect results SELECT ... WHERE start >="2003-09-01" AND start<="2003-09-30"

Bug #1543	incorrect results SELECT ... WHERE start >="2003-09-01" AND start<="2003-09-30"
Submitted:	13 Oct 2003 13:24	Modified:	13 Oct 2003 14:30
Reporter:	Cyril Zlachevsky	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	3.23.58	OS:	Linux (Red Hat Linux 7.2)
Assigned to:		CPU Architecture:	Any

Description:
Only 30 days in September.
I have table with datetime column type named start.

When I run SQL-query:
----------------------cut----------------------
mysql> SELECT distinct substring(start,1,10) from test where start >= "2003-09-01" AND start <= "2003-09-30";
+-----------------------+
| substring(start,1,10) |
+-----------------------+
| 2003-09-01            |
| 2003-09-02            |
| 2003-09-03            |
| 2003-09-04            |
| 2003-09-05            |
| 2003-09-06            |
| 2003-09-07            |
| 2003-09-08            |
| 2003-09-09            |
| 2003-09-10            |
| 2003-09-11            |
| 2003-09-12            |
| 2003-09-13            |
| 2003-09-14            |
| 2003-09-15            |
| 2003-09-16            |
| 2003-09-17            |
| 2003-09-18            |
| 2003-09-19            |
| 2003-09-20            |
| 2003-09-21            |
| 2003-09-22            |
| 2003-09-23            |
| 2003-09-24            |
| 2003-09-25            |
| 2003-09-26            |
| 2003-09-27            |
| 2003-09-28            |
| 2003-09-29            |
+-----------------------+
29 rows in set (1.29 sec)

mysql> SELECT distinct substring(start,1,10) from test where start >= "2003-09-01" AND start < "2003-09-30";
+-----------------------+
| substring(start,1,10) |
+-----------------------+
| 2003-09-01            |
| 2003-09-02            |
| 2003-09-03            |
| 2003-09-04            |
| 2003-09-05            |
| 2003-09-06            |
| 2003-09-07            |
| 2003-09-08            |
| 2003-09-09            |
| 2003-09-10            |
| 2003-09-11            |
| 2003-09-12            |
| 2003-09-13            |
| 2003-09-14            |
| 2003-09-15            |
| 2003-09-16            |
| 2003-09-17            |
| 2003-09-18            |
| 2003-09-19            |
| 2003-09-20            |
| 2003-09-21            |
| 2003-09-22            |
| 2003-09-23            |
| 2003-09-24            |
| 2003-09-25            |
| 2003-09-26            |
| 2003-09-27            |
| 2003-09-28            |
| 2003-09-29            |
+-----------------------+
29 rows in set (1.60 sec)
----------------------cut----------------------

I found result of first query not included 30 September. The differences in first and second SQL-quries is "<=" and "<" in second condition. But results of these differen SQL-queries is identical.
If in second condition make change "2003-09-30" to "2003-09-31":
----------------------cut----------------------
SELECT distinct substring(start,1,10) from test where start >= "2003-09-01" AND start <= "2003-09-31";
+-----------------------+
| substring(start,1,10) |
+-----------------------+
| 2003-09-01            |
| 2003-09-02            |
| 2003-09-03            |
| 2003-09-04            |
| 2003-09-05            |
| 2003-09-06            |
| 2003-09-07            |
| 2003-09-08            |
| 2003-09-09            |
| 2003-09-10            |
| 2003-09-11            |
| 2003-09-12            |
| 2003-09-13            |
| 2003-09-14            |
| 2003-09-15            |
| 2003-09-16            |
| 2003-09-17            |
| 2003-09-18            |
| 2003-09-19            |
| 2003-09-20            |
| 2003-09-21            |
| 2003-09-22            |
| 2003-09-23            |
| 2003-09-24            |
| 2003-09-25            |
| 2003-09-26            |
| 2003-09-27            |
| 2003-09-28            |
| 2003-09-29            |
| 2003-09-30            |
+-----------------------+
30 rows in set (1.32 sec)

mysql> SELECT distinct substring(start,1,10) from test where start >= "2003-09-01" AND start < "2003-09-31";
+-----------------------+
| substring(start,1,10) |
+-----------------------+
| 2003-09-01            |
| 2003-09-02            |
| 2003-09-03            |
| 2003-09-04            |
| 2003-09-05            |
| 2003-09-06            |
| 2003-09-07            |
| 2003-09-08            |
| 2003-09-09            |
| 2003-09-10            |
| 2003-09-11            |
| 2003-09-12            |
| 2003-09-13            |
| 2003-09-14            |
| 2003-09-15            |
| 2003-09-16            |
| 2003-09-17            |
| 2003-09-18            |
| 2003-09-19            |
| 2003-09-20            |
| 2003-09-21            |
| 2003-09-22            |
| 2003-09-23            |
| 2003-09-24            |
| 2003-09-25            |
| 2003-09-26            |
| 2003-09-27            |
| 2003-09-28            |
| 2003-09-29            |
| 2003-09-30            |
+-----------------------+
30 rows in set (1.66 sec)
----------------------cut----------------------

As you can see, 30 September is included in result of query now. Again "<=" and "<" in second condition get absolutely identical result.

If only one clause is used in SQL-query, result is correct:
----------------------cut----------------------
mysql> SELECT distinct substring(start,1,10) from test where substring(start,1,7)="2003-09";
+-----------------------+
| substring(start,1,10) |
+-----------------------+
| 2003-09-01            |
| 2003-09-02            |
| 2003-09-03            |
| 2003-09-04            |
| 2003-09-05            |
| 2003-09-06            |
| 2003-09-07            |
| 2003-09-08            |
| 2003-09-09            |
| 2003-09-10            |
| 2003-09-11            |
| 2003-09-12            |
| 2003-09-13            |
| 2003-09-14            |
| 2003-09-15            |
| 2003-09-16            |
| 2003-09-17            |
| 2003-09-18            |
| 2003-09-19            |
| 2003-09-20            |
| 2003-09-21            |
| 2003-09-22            |
| 2003-09-23            |
| 2003-09-24            |
| 2003-09-25            |
| 2003-09-26            |
| 2003-09-27            |
| 2003-09-28            |
| 2003-09-29            |
| 2003-09-30            |
+-----------------------+
30 rows in set (3.73 sec)
----------------------cut----------------------

Table test not contained records with incorrect date 31 September:
----------------------cut----------------------
mysql> SELECT *  from test where substring(start,1,10)='2003-09-31';
Empty set (10.09 sec)
----------------------cut----------------------

How to repeat:
CREATE TABLE test (
  count int(8) unsigned NOT NULL auto_increment,
  start datetime default NULL,
  PRIMARY KEY  (count),
  KEY start_idx (start)
) TYPE=InnoDB PACK_KEYS=1;

INSERT INTO test VALUES (1,'2003-09-01 02:18:55');
INSERT INTO test VALUES (2,'2003-09-02 02:18:55');
INSERT INTO test VALUES (3,'2003-09-03 02:18:55');
INSERT INTO test VALUES (4,'2003-09-04 02:18:55');
INSERT INTO test VALUES (5,'2003-09-05 02:18:55');
INSERT INTO test VALUES (6,'2003-09-06 02:18:55');
INSERT INTO test VALUES (7,'2003-09-07 02:18:55');
INSERT INTO test VALUES (8,'2003-09-08 02:18:55');
INSERT INTO test VALUES (9,'2003-09-09 02:18:55');
INSERT INTO test VALUES (10,'2003-09-10 02:18:55');
INSERT INTO test VALUES (11,'2003-09-11 02:18:55');
INSERT INTO test VALUES (12,'2003-09-12 02:18:55');
INSERT INTO test VALUES (13,'2003-09-13 02:18:55');
INSERT INTO test VALUES (14,'2003-09-14 02:18:55');
INSERT INTO test VALUES (15,'2003-09-15 02:18:55');
INSERT INTO test VALUES (16,'2003-09-16 02:18:55');
INSERT INTO test VALUES (17,'2003-09-17 02:18:55');
INSERT INTO test VALUES (18,'2003-09-18 02:18:55');
INSERT INTO test VALUES (19,'2003-09-19 02:18:55');
INSERT INTO test VALUES (20,'2003-09-20 02:18:55');
INSERT INTO test VALUES (21,'2003-09-21 02:18:55');
INSERT INTO test VALUES (22,'2003-09-22 02:18:55');
INSERT INTO test VALUES (23,'2003-09-23 02:18:55');
INSERT INTO test VALUES (24,'2003-09-24 02:18:55');
INSERT INTO test VALUES (25,'2003-09-25 02:18:55');
INSERT INTO test VALUES (26,'2003-09-26 02:18:55');
INSERT INTO test VALUES (27,'2003-09-27 02:18:55');
INSERT INTO test VALUES (28,'2003-09-28 02:18:55');
INSERT INTO test VALUES (29,'2003-09-29 02:18:55');
INSERT INTO test VALUES (30,'2003-09-30 02:18:55');

You will need to compare and analyse results of this SQL queries:

SELECT distinct substring(start,1,10) from test where start >= "2003-09-01" AND start <= "2003-09-30";

SELECT distinct substring(start,1,10) from test where start >= "2003-09-01" AND start < "2003-09-30";

SELECT distinct substring(start,1,10) from test where start >= "2003-09-01" AND start <= "2003-09-31";

SELECT distinct substring(start,1,10) from test where start >= "2003-09-01" AND start < "2003-09-31";

SELECT distinct substring(start,1,10) from test where substring(start,1,7)="2003-09";

Suggested fix:
Only workaround:
you must use "<=" instead "<" in second condition in SQL-query like
SELECT ... WHERE start >="2003-09-01" AND start<="2003-09-30"

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Thank you very much for your excelent bug report.
However what you describe is not a bug, but expected behaviour.

The problem comes from the fact that you are comparing DATETIME column to Date type values. In this case Date value is first converted to DATETIME by adding zero hour:minute:seconds part.

So your WHERE clause:

... where start >= "2003-09-01" AND start <= "2003-09-30";

is processed as if it was:

... where start >= "2003-09-01 00:00:00" AND start <= "2003-09-30 00:00:00";

With regard of this I hope you will agree results of all your queries are correct.

mysql> SELECT * FROM test;
+-------+---------------------+
| count | start               |
+-------+---------------------+
|     1 | 2003-09-01 02:18:55 |
|     2 | 2003-09-02 02:18:55 |
|     3 | 2003-09-03 02:18:55 |
|     4 | 2003-09-04 02:18:55 |
|     5 | 2003-09-05 02:18:55 |
|     6 | 2003-09-06 02:18:55 |
|     7 | 2003-09-07 02:18:55 |
|     8 | 2003-09-08 02:18:55 |
|     9 | 2003-09-09 02:18:55 |
|    10 | 2003-09-10 02:18:55 |
|    11 | 2003-09-11 02:18:55 |
|    12 | 2003-09-12 02:18:55 |
|    13 | 2003-09-13 02:18:55 |
|    14 | 2003-09-14 02:18:55 |
|    15 | 2003-09-15 02:18:55 |
|    16 | 2003-09-16 02:18:55 |
|    17 | 2003-09-17 02:18:55 |
|    18 | 2003-09-18 02:18:55 |
|    19 | 2003-09-19 02:18:55 |
|    20 | 2003-09-20 02:18:55 |
|    21 | 2003-09-21 02:18:55 |
|    22 | 2003-09-22 02:18:55 |
|    23 | 2003-09-23 02:18:55 |
|    24 | 2003-09-24 02:18:55 |
|    25 | 2003-09-25 02:18:55 |
|    26 | 2003-09-26 02:18:55 |
|    27 | 2003-09-27 02:18:55 |
|    28 | 2003-09-28 02:18:55 |
|    29 | 2003-09-29 02:18:55 |
|    30 | 2003-09-30 02:18:55 |
+-------+---------------------+

mysql> SELECT * FROM test WHERE start >= "2003-09-01" AND start <= "2003-09-30";
+-------+---------------------+
| count | start               |
+-------+---------------------+
|     1 | 2003-09-01 02:18:55 |
|     2 | 2003-09-02 02:18:55 |
|     3 | 2003-09-03 02:18:55 |
|     4 | 2003-09-04 02:18:55 |
|     5 | 2003-09-05 02:18:55 |
|     6 | 2003-09-06 02:18:55 |
|     7 | 2003-09-07 02:18:55 |
|     8 | 2003-09-08 02:18:55 |
|     9 | 2003-09-09 02:18:55 |
|    10 | 2003-09-10 02:18:55 |
|    11 | 2003-09-11 02:18:55 |
|    12 | 2003-09-12 02:18:55 |
|    13 | 2003-09-13 02:18:55 |
|    14 | 2003-09-14 02:18:55 |
|    15 | 2003-09-15 02:18:55 |
|    16 | 2003-09-16 02:18:55 |
|    17 | 2003-09-17 02:18:55 |
|    18 | 2003-09-18 02:18:55 |
|    19 | 2003-09-19 02:18:55 |
|    20 | 2003-09-20 02:18:55 |
|    21 | 2003-09-21 02:18:55 |
|    22 | 2003-09-22 02:18:55 |
|    23 | 2003-09-23 02:18:55 |
|    24 | 2003-09-24 02:18:55 |
|    25 | 2003-09-25 02:18:55 |
|    26 | 2003-09-26 02:18:55 |
|    27 | 2003-09-27 02:18:55 |
|    28 | 2003-09-28 02:18:55 |
|    29 | 2003-09-29 02:18:55 |
+-------+---------------------+
29 rows in set (0.00 sec)

Here row 30 does not match because for WHERE clause there is:

"2003-09-30 02:18:55" <= "2003-09-30 00:00:00" -> False

Similar for other queries. 

"2003-09-30 02:18:55" < "2003-09-30 00:00:00" -> False

mysql> SELECT * FROM test WHERE start >= "2003-09-01" AND start <= "2003-09-31";
+-------+---------------------+
| count | start               |
+-------+---------------------+
|     1 | 2003-09-01 02:18:55 |
|     2 | 2003-09-02 02:18:55 |
|     3 | 2003-09-03 02:18:55 |
|     4 | 2003-09-04 02:18:55 |
|     5 | 2003-09-05 02:18:55 |
|     6 | 2003-09-06 02:18:55 |
|     7 | 2003-09-07 02:18:55 |
|     8 | 2003-09-08 02:18:55 |
|     9 | 2003-09-09 02:18:55 |
|    10 | 2003-09-10 02:18:55 |
|    11 | 2003-09-11 02:18:55 |
|    12 | 2003-09-12 02:18:55 |
|    13 | 2003-09-13 02:18:55 |
|    14 | 2003-09-14 02:18:55 |
|    15 | 2003-09-15 02:18:55 |
|    16 | 2003-09-16 02:18:55 |
|    17 | 2003-09-17 02:18:55 |
|    18 | 2003-09-18 02:18:55 |
|    19 | 2003-09-19 02:18:55 |
|    20 | 2003-09-20 02:18:55 |
|    21 | 2003-09-21 02:18:55 |
|    22 | 2003-09-22 02:18:55 |
|    23 | 2003-09-23 02:18:55 |
|    24 | 2003-09-24 02:18:55 |
|    25 | 2003-09-25 02:18:55 |
|    26 | 2003-09-26 02:18:55 |
|    27 | 2003-09-27 02:18:55 |
|    28 | 2003-09-28 02:18:55 |
|    29 | 2003-09-29 02:18:55 |
|    30 | 2003-09-30 02:18:55 |
+-------+---------------------+

Here row 30 matches because:

"2003-09-30 02:18:55" < "2003-09-31 00:00:00" -> True

Let me add that there are more convinient ways to extract parts of DATETIME than using SUBSTRING(). General function for this is DATE_FORMAT() it's several format specifiers make it very flexible.

You can also use BETWEEN for your query. It is equivalent to >= AND <=, but is shorter and mor ereadable.

Always pay special attention when you mix DATETIME and DATE in same expression.

Example:
mysql> SELECT DATE_FORMAT(start, "%Y-%m-%d") AS Date FROM test WHERE start BETWEEN "2003-09-01" AND "2003-09-31";
+------------+
| Date       |
+------------+
| 2003-09-01 |
| 2003-09-02 |
| 2003-09-03 |
| 2003-09-04 |
| 2003-09-05 |
| 2003-09-06 |
| 2003-09-07 |
| 2003-09-08 |
| 2003-09-09 |
| 2003-09-10 |
| 2003-09-11 |
| 2003-09-12 |
| 2003-09-13 |
| 2003-09-14 |
| 2003-09-15 |
| 2003-09-16 |
| 2003-09-17 |
| 2003-09-18 |
| 2003-09-19 |
| 2003-09-20 |
| 2003-09-21 |
| 2003-09-22 |
| 2003-09-23 |
| 2003-09-24 |
| 2003-09-25 |
| 2003-09-26 |
| 2003-09-27 |
| 2003-09-28 |
| 2003-09-29 |
| 2003-09-30 |
+------------+