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

• Submitted: 18 Apr 2:24
• Modified: 18 Apr 5:33
• Reporter: Chunling Qin
• Status: Analyzing
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.4.3
• OS: Any
• Assigned to: MySQL Verification Team
• 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;