Bug #118022 between date and str_TO_DATE in select where returns incorrect result

• Submitted: 21 Apr 4:12
• Modified: 28 Apr 12:23
• Reporter: Chunling Qin
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• OS: Any
• CPU Architecture: Any

[21 Apr 4:12] Chunling Qin

Description:
MySQL [test1]> insert into chqin values(1,'2020-01-01');
Query OK, 1 row affected (0.01 sec)

MySQL [test1]> insert into chqin values(1,'2019-03-21');
Query OK, 1 row affected (0.00 sec)

Here, it should return 'Empty set'.

MySQL [test1]> select t1.col_date from chqin t1 where t1.col_date NOT BETWEEN '2019-03-20' AND str_TO_DATE( '2019-03-20' ,'YYYY-MM-DD HH24:MI:SS') + 1 ;
+------------+
| col_date   |
+------------+
| 2020-01-01 |
| 2019-03-21 |
+------------+
2 rows in set, 2 warnings (0.00 sec)
---

MySQL [test1]> 

---Here, it works fine with the const value '2020-01-01'.

MySQL [test1]> select 1 from chqin t1 where '2020-01-01' NOT BETWEEN '2019-03-20' AND str_TO_DATE( '2019-03-20' ,'YYYY-MM-DD HH24:MI:SS') + 1 ;
Empty set, 3 warnings (0.00 sec)

MySQL [test1]> 
MySQL [test1]> select t1.col_date from chqin t1 where t1.col_date  BETWEEN '2019-03-20' AND str_TO_DATE( '2019-03-20' ,'YYYY-MM-DD HH24:MI:SS') + 1 ;
Empty set, 2 warnings (0.00 sec)

---Here, when I use NULL instead of STR_TO_DATE('2019-03-20', 'YYYY-MM-DD HH24:MI:SS'), it functions correctly and returns an empty result.

MySQL [test1]> select t1.col_date from chqin t1 where t1.col_date NOT BETWEEN '2019-03-20' AND null ;
Empty set (0.00 sec)

How to repeat:
drop table chqin;
create table chqin(id int, col_date date);
insert into chqin values(1,'2020-01-01');
insert into chqin values(1,'2019-03-21');
select t1.col_date from chqin t1 where t1.col_date NOT BETWEEN '2019-03-20' AND str_TO_DATE( '2019-03-20' ,'YYYY-MM-DD HH24:MI:SS') + 1 ;  

select 1 from chqin t1 where '2020-01-01' NOT BETWEEN '2019-03-20' AND str_TO_DATE( '2019-03-20' ,'YYYY-MM-DD HH24:MI:SS') + 1 ;

select t1.col_date from chqin t1 where t1.col_date  BETWEEN '2019-03-20' AND str_TO_DATE( '2019-03-20' ,'YYYY-MM-DD HH24:MI:SS') + 1 ;

select t1.col_date from chqin t1 where t1.col_date NOT BETWEEN '2019-03-20' AND null ;

[23 Apr 12:47] Chunling Qin

It returns incorrect rusult, and can be easily reproduced.

[28 Apr 12:23] MySQL Verification Team

Hello Chunling Qin,

Thank you for the bug report.
IMHO this is not a bug. Please use '%Y-%m-%d' instead of 'YYYY-MM-DD'

Result of str_TO_DATE( '2019-03-20' ,'%Y-%m-%d HH24:MI:SS') + 1 gives 2019-03-21
The date not between '2019-03-20' and 2019-03-21 is 2020-01-01 as per inserted data.

select t1.col_date from chqin t1 where t1.col_date NOT BETWEEN '2019-03-20' AND str_TO_DATE( '2019-03-20' ,'%Y-%m-%d HH24:MI:SS') + 1 ;
col_date
2020-01-01

Regards,
Ashwini Patil