| Bug #118022 | between date and str_TO_DATE in select where returns incorrect result | ||
|---|---|---|---|
| Submitted: | 21 Apr 2025 4:12 | Modified: | 28 Apr 2025 12:23 |
| Reporter: | Chunling Qin | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[23 Apr 2025 12:47]
Chunling Qin
It returns incorrect rusult, and can be easily reproduced.
[28 Apr 2025 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
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 ;