Bug #89469 <= condition is not working in select query for date column.
Submitted: 30 Jan 2018 11:00 Modified: 6 Feb 2018 22:45
Reporter: sheetal umathe Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5/5.6/5.7/8.0 OS:Any
Assigned to: CPU Architecture:Any

[30 Jan 2018 11:00] sheetal umathe
Description:
"<=" i.e. less than or equals to operator is not working for date format which is given in the below query.

example: select * from table where tableDate <= '11-30-2017';

While for the same date format ">=" i.e. greater than or equals to operator is working fine.
example: select * from table where tableDate >= '11-30-2017'; This works absolutely fine.

There is a bug regarding <= for the date format "mm-dd-yyyy".

How to repeat:
fire query i.e. with greater than operator
--> this will work
select * from table where tableDate >= '11-30-2017';

fire same query i.e. with less than operator
--> This will return null values
select * from table where tableDate <= '11-30-2017'

Suggested fix:
Both the operators should work fine with same date format or else please allow only specific date format which will work in all the scenarios.
[30 Jan 2018 11:33] MySQL Verification Team
Thank you for the bug report. Please provide a complete test case: server version, create table, insert data, queries with real result and expected one. Thanks in advance.
[30 Jan 2018 15:06] Peter Laursen
Isn't this test case enough:

SELECT '2017-12-30'  >= '11-30-2017'; -- returns "1"
SELECT '2017-12-30'  <= '11-30-2017'; -- returns "0"

??

-- Peter
-- not a MySQL/Oracle person
[30 Jan 2018 15:08] Peter Laursen
Server version 5.6.34, BTW.
[30 Jan 2018 15:10] Peter Laursen
Sorry! Please correct the statements to:

SELECT '2017-11-30'  >= '11-30-2017'; -- returns "1"
SELECT '2017-11-30'  <= '11-30-2017'; -- returns "0"

-- Peter
[1 Feb 2018 11:21] MySQL Verification Team
Thank you for the feedback.
[6 Feb 2018 22:45] Roy Lyseng
As far as I understand, this is not a bug. The reproduction case is a bit
brief, but I think this is what is requested here:

Create a table with a DATE column and insert a few rows:
CREATE TABLE t (d DATE);
INSERT INTO t VALUES (DATE '2017-11-29'), (DATE '2017-11-30'), (DATE '2017-12-01');

Now, the SQL way to specify DATE literal values is with the keyword DATE
followed by a text string on the format 'YYYY-MM-DD'. Thus, we can query our
DATE column like this:

SELECT * FROM t WHERE d >= DATE '2017-11-30';

+------------+
| d          |
+------------+
| 2017-11-30 |
| 2017-12-01 |
+------------+

SELECT * FROM t WHERE d <= DATE '2017-11-30';

+------------+
| d          |
+------------+
| 2017-11-29 |
| 2017-11-30 |
+------------+

MySQL also allows abbreviated DATE constants: A literal string may be converted
into a DATE literal when compared against a DATE expression. Thus, these two
queries are equivalent to the two above:

SELECT * FROM t WHERE d >= '2017-11-30';

+------------+
| d          |
+------------+
| 2017-11-30 |
| 2017-12-01 |
+------------+

SELECT * FROM t WHERE d <= '2017-11-30';

+------------+
| d          |
+------------+
| 2017-11-29 |
| 2017-11-30 |
+------------+

But, any other format on the DATE string is not accepted. See what happens
with a DATE value on the format MM-DD-YYYY:

SELECT * FROM t WHERE d >= '11-30-2017';

+------------+
| d          |
+------------+
| 2017-11-29 |
| 2017-11-30 |
| 2017-12-01 |
+------------+
3 rows in set, 1 warning (0,00 sec)

SELECT * FROM t WHERE d <= '11-30-2017';

Empty set, 1 warning (0,00 sec)

This can be explained if we display the warning:

mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '11-30-2017' for column 'd' at row 1 |
+---------+------+------------------------------------------------------------+