str_to_date return different result on no zero date sql mode

[windtalker]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> CREATE TABLE `sun` ( `dest` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.17 sec)

mysql> insert into sun values('20231020');
Query OK, 1 row affected (0.04 sec)

mysql> select str_to_date(substr(dest,1,6),'%H%i%s') from sun;
+----------------------------------------+
| str_to_date(substr(dest,1,6),'%H%i%s') |
+----------------------------------------+
| NULL                                   |
+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2. What did you expect to see? (Required)

str_to_date(substr(dest,1,6),'%H%i%s') returns 20:23:10 even the sql mode contains NO_ZERO_IN_DATE,NO_ZERO_DATE

3. What did you see instead (Required)

str_to_date(substr(dest,1,6),'%H%i%s') returns NULL

4. What is your TiDB version? (Required)

master@3765abbd434756b01212d7debf49406456620224

[windtalker]

This bug is reported by one of our customers, and need to be fixed in release-6.1.