Bug #118007 str_TO_DATE( '1980-07-06','YYYY-MM-DD HH24:MI:SS') in dml reports error

• Submitted: 18 Apr 2:24
• Modified: 25 Apr 13:30
• Reporter: Chunling Qin
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.4.3
• OS: Any
• CPU Architecture: Any

[18 Apr 2:24] Chunling Qin

Description:
"str_TO_DATE( '1980-07-06','YYYY-MM-DD HH24:MI:SS')"  in a select clause of insert command reports error "ERROR 1411 (HY000): Incorrect datetime value: '1980-07-06' for function str_to_date", while it work fine in a standalone select clause.

MySQL [test1]> begin;
Query OK, 0 rows affected (0.00 sec)

MySQL [test1]> insert into chqin SELECT a2.* FROM  chqin1 a2  WHERE a2.col_date NOT BETWEEN '1980-07-06' AND str_TO_DATE( '1980-07-06','YYYY-MM-DD HH24:MI:SS') + 1   ;
ERROR 1411 (HY000): Incorrect datetime value: '1980-07-06' for function str_to_date
MySQL [test1]> 
MySQL [test1]> SELECT a2.* FROM  chqin1 a2  WHERE a2.col_date NOT BETWEEN '1980-07-06' AND str_TO_DATE( '1980-07-06','YYYY-MM-DD HH24:MI:SS') + 1 limit 1;
+------+------------+
| id   | col_date   |
+------+------------+
|    1 | 2029-01-01 |
+------+------------+
1 row in set, 2 warnings (0.00 sec)

MySQL [test1]> rollback;
Query OK, 0 rows affected (0.00 sec)

How to repeat:
drop table chqin;
drop table chqin1;
create table chqin(id int, col_date date);
insert into chqin values (1,'2029-01-01 11:00:00');
insert into chqin values (2,'1980-07-06 11:00:00');

create table chqin1(id int, col_date date);
insert into chqin1 values (1,'2029-01-01 11:00:00');
insert into chqin1 values (2,'1980-07-06 11:00:00');

begin;
insert into chqin SELECT a2.* FROM  chqin1 a2  WHERE a2.col_date NOT BETWEEN '1980-07-06' AND str_TO_DATE( '1980-07-06','YYYY-MM-DD HH24:MI:SS') + 1   ;

SELECT a2.* FROM  chqin1 a2  WHERE a2.col_date NOT BETWEEN '1980-07-06' AND str_TO_DATE( '1980-07-06','YYYY-MM-DD HH24:MI:SS') + 1 limit 1;
rollback;

[25 Apr 10:13] MySQL Verification Team

Hello Chunling Qin,

Thank you for the bug report.
Please note that replacing YYYY-MM-DD with %Y-%m-%d not throwing any error and insertion was successfully.

insert into chqin SELECT a2.* FROM  chqin1 a2  WHERE a2.col_date NOT BETWEEN '1980-07-06' AND str_TO_DATE( '1980-07-06','%Y-%m-%d HH24:MI:SS') + 1   ;

Kindly check it.

Regards,
Ashwini Patil

[25 Apr 12:41] Chunling Qin

Hi, it works with '%Y-%m-%d HH24:MI:SS'.

MySQL [fuzztest]> insert into chqin SELECT a2.* FROM  chqin1 a2  WHERE a2.col_date NOT BETWEEN '1980-07-06' AND str_TO_DATE( '1980-07-06','%Y-%m-%d HH24:MI:SS') + 1   ;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

[25 Apr 12:49] MySQL Verification Team

Thank you for the feedback.

[25 Apr 13:30] Chunling Qin

Since the select command ('SELECT a2.* FROM  chqin1 a2  WHERE a2.col_date NOT BETWEEN '1980-07-06' AND str_TO_DATE( '1980-07-06','YYYY-MM-DD HH24:MI:SS') + 1 limit 1;' ) can work , should the insert behavior keep consistent?