bug: create table as select str_to_date() return error

[davidshiz]

Have you read the Contributing Guidelines on issues?

• [X] I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• [X] I confirm there is no existing issue for this

Describe the problem

mysql> SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select str_to_date("02 10", "%d %H");
+-------------------------------+
| str_to_date("02 10", "%d %H") |
+-------------------------------+
| 58:00:00                      |
+-------------------------------+
1 row in set (0.00 sec)

mysql> create table t2 select str_to_date("02 10", "%d %H");
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+-------------------------------+
| str_to_date("02 10", "%d %H") |
+-------------------------------+
| 82:00:00                      |
+-------------------------------+
1 row in set (0.00 sec)

Expected behavior

mysql> select * from t2;
+-------------------------------+
| str_to_date("02 10", "%d %H") |
+-------------------------------+
| 58:00:00                      |
+-------------------------------+
1 row in set (0.00 sec)

How To Reproduce

No response

Environment

root@ub01:~# /stonedb57/install//bin/mysqld --version
/stonedb57/install//bin/mysqld  Ver 5.7.36-StoneDB-v1.0.3 for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
        Branch name: stonedb-5.7-dev
        Last commit ID: 1fa5661e4
        Last commit time: Date:   Thu Mar 23 20:19:17 2023 +0800
        Build time: Date: Wed Mar 29 10:31:02 CST 2023

Are you interested in submitting a PR to solve the problem?

• [ ] Yes, I will!

[davidshiz]

another scenario tianmu:

mysql> create table t1 select "02 10" as a, "%d %H" as b;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select str_to_date(a,b) from t1;
+----------------------------+
| str_to_date(a,b)           |
+----------------------------+
| 0000-00-00 00:00:00.000000 |
+----------------------------+
1 row in set (0.00 sec)

innodb:

mysql> create table t1 select "02 10" as a, "%d %H" as b;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select str_to_date(a,b) from t1;
+----------------------------+
| str_to_date(a,b)           |
+----------------------------+
| 0000-00-02 10:00:00.000000 |
+----------------------------+
1 row in set (0.00 sec)

[davidshiz]

tianmu:

mysql> create table t1 (date char(30), format char(30) not null);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values('0003-01-02 8:11:2.123456', '%Y-%m-%d %H:%i:%S.%#'),('2003-01-02 01:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f%p');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select date,format,DATE(str_to_date(date, format)) as date2 from t1;
+-----------------------------+------------------------+------------+
| date                        | format                 | date2      |
+-----------------------------+------------------------+------------+
| 0003-01-02 8:11:2.123456    | %Y-%m-%d %H:%i:%S.%#   | 2003-01-02 |
| 2003-01-02 01:11:12.12345AM | %Y-%m-%d %h:%i:%S.%f%p | 2003-01-02 |
+-----------------------------+------------------------+------------+
2 rows in set (0.00 sec)

mysql> select date,format,TIME(str_to_date(date, format)) as time from t1;
+-----------------------------+------------------------+-----------------+
| date                        | format                 | time            |
+-----------------------------+------------------------+-----------------+
| 0003-01-02 8:11:2.123456    | %Y-%m-%d %H:%i:%S.%#   | 08:11:02.000000 |
| 2003-01-02 01:11:12.12345AM | %Y-%m-%d %h:%i:%S.%f%p | 01:11:12.000000 |
+-----------------------------+------------------------+-----------------+
2 rows in set (0.00 sec)

innodb:

mysql> select date,format,DATE(str_to_date(date, format)) as date2 from t1;
+-----------------------------+------------------------+------------+
| date                        | format                 | date2      |
+-----------------------------+------------------------+------------+
| 0003-01-02 8:11:2.123456    | %Y-%m-%d %H:%i:%S.%#   | 0003-01-02 |
| 2003-01-02 01:11:12.12345AM | %Y-%m-%d %h:%i:%S.%f%p | 2003-01-02 |
+-----------------------------+------------------------+------------+
2 rows in set (0.01 sec)

mysql> select date,format,TIME(str_to_date(date, format)) as time from t1;
+-----------------------------+------------------------+-----------------+
| date                        | format                 | time            |
+-----------------------------+------------------------+-----------------+
| 0003-01-02 8:11:2.123456    | %Y-%m-%d %H:%i:%S.%#   | 08:11:02.000000 |
| 2003-01-02 01:11:12.12345AM | %Y-%m-%d %h:%i:%S.%f%p | 01:11:12.123450 |
+-----------------------------+------------------------+-----------------+
2 rows in set (0.00 sec)

[duanjr]

Have you read the Contributing Guidelines on issues?

• [x] I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• [x] I confirm there is no existing issue for this

Describe the problem

mysql> SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select str_to_date("02 10", "%d %H");
+-------------------------------+
| str_to_date("02 10", "%d %H") |
+-------------------------------+
| 58:00:00                      |
+-------------------------------+
1 row in set (0.00 sec)

mysql> create table t2 select str_to_date("02 10", "%d %H");
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+-------------------------------+
| str_to_date("02 10", "%d %H") |
+-------------------------------+
| 82:00:00                      |
+-------------------------------+
1 row in set (0.00 sec)

Expected behavior

mysql> select * from t2;
+-------------------------------+
| str_to_date("02 10", "%d %H") |
+-------------------------------+
| 58:00:00                      |
+-------------------------------+
1 row in set (0.00 sec)

How To Reproduce

No response

Environment

root@ub01:~# /stonedb57/install//bin/mysqld --version
/stonedb57/install//bin/mysqld  Ver 5.7.36-StoneDB-v1.0.3 for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
        Branch name: stonedb-5.7-dev
        Last commit ID: 1fa5661e4
        Last commit time: Date:   Thu Mar 23 20:19:17 2023 +0800
        Build time: Date: Wed Mar 29 10:31:02 CST 2023

Are you interested in submitting a PR to solve the problem?

• [ ] Yes, I will!

This part looks similar to #1175, and may be fixed by #1752, other part of this issue still exists.