bug: STR_TO_DATE() Function does not work well

[davidshiz]

Have you read the Contributing Guidelines on issues?

• I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Describe the problem

STR_TO_DATE() Function return wrong results
as follows

mysql> SELECT date, format, STR_TO_DATE(date, format) as str_to_date FROM t1;
+---------------+---------------+----------------------------+
| date          | format        | str_to_date                |
+---------------+---------------+----------------------------+
| 10:20:10      | %H:%i:%s      | 0000-00-00 00:00:00.000000 |
| 10:20:10      | %h:%i:%s.%f   | 0000-00-00 00:00:00.000000 |
| 10:20:10      | %T            | 0000-00-00 00:00:00.000000 |
| 10:20:10AM    | %h:%i:%s%p    | 0000-00-00 00:00:00.000000 |
| 10:20:10AM    | %r            | 0000-00-00 00:00:00.000000 |
| 10:20:10.44AM | %h:%i:%s.%f%p | 0000-00-00 00:00:00.000000 |
+---------------+---------------+----------------------------+
6 rows in set (0.00 sec)

Expected behavior

mysql> SELECT date, format, STR_TO_DATE(date, format) as str_to_date FROM t1;
+---------------+---------------+----------------------------+
| date          | format        | str_to_date                |
+---------------+---------------+----------------------------+
| 10:20:10      | %H:%i:%s      | 0000-00-00 10:20:10.000000 |
| 10:20:10      | %h:%i:%s.%f   | 0000-00-00 10:20:10.000000 |
| 10:20:10      | %T            | 0000-00-00 10:20:10.000000 |
| 10:20:10AM    | %h:%i:%s%p    | 0000-00-00 10:20:10.000000 |
| 10:20:10AM    | %r            | 0000-00-00 10:20:10.000000 |
| 10:20:10.44AM | %h:%i:%s.%f%p | 0000-00-00 10:20:10.440000 |
+---------------+---------------+----------------------------+
6 rows in set (0.00 sec)

How To Reproduce

CREATE TABLE t1 (date CHAR(30), format CHAR(30));
INSERT INTO t1 VALUES
('10:20:10', '%H:%i:%s'),
('10:20:10', '%h:%i:%s.%f'),
('10:20:10', '%T'),
('10:20:10AM', '%h:%i:%s%p'),
('10:20:10AM', '%r'),
('10:20:10.44AM', '%h:%i:%s.%f%p');

SELECT date, format, STR_TO_DATE(date, format) as str_to_date FROM t1;

Environment

root@localhost:/# /stonedb57/install/bin/mysqld --version
/stonedb57/install/bin/mysqld  Ver 5.7.36-StoneDB 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: d0c2e01b6
        Last commit time: Date:   Wed Nov 2 19:58:00 2022 +0800
        Build time: Date: Sun 06 Nov 2022 08:50:06 AM UTC
root@ub01:~# cat /etc/os-release
NAME="Ubuntu"
VERSION="20.04.5 LTS (Focal Fossa)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 20.04.5 LTS"
VERSION_ID="20.04"

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

• Yes, I will!

[lujiashun]

in tianmu/types/value_parser4txt.cpp

  if (!TianmuDateTime::CanBeDate(year, month, day)) {
    rcv = TianmuDateTime(TianmuDateTime::GetSpecialValue(at));
    return common::ErrorCode::OUT_OF_RANGE;
  }

[lujiashun]

In mysql5.7 reference:

• By setting the SQL mode to the appropriate value, you can specify more exactly what kind of dates you
want MySQL to support. (See Section 5.1.10, “Server SQL Modes”.) You can get MySQL to accept
certain dates, such as '2009-11-31', by enabling the ALLOW_INVALID_DATES SQL mode. This is
useful when you want to store a “possibly wrong” value which the user has specified (for example, in a
web form) in the database for future processing. Under this mode, MySQL verifies only that the month is
in the range from 1 to 12 and that the day is in the range from 1 to 31.
• MySQL permits you to store dates where the day or month and day are zero in a DATE or DATETIME
column. This is useful for applications that need to store birthdates for which you may not know the
exact date. In this case, you simply store the date as '2009-00-00' or '2009-01-00'. However, with
dates such as these, you should not expect to get correct results for functions such as DATE_SUB()
or DATE_ADD() that require complete dates. To disallow zero month or day parts in dates, enable the
NO_ZERO_IN_DATE mode.
• MySQL permits you to store a “zero” value of '0000-00-00' as a “dummy date.” In some cases,
this is more convenient than using NULL values, and uses less data and index space. To disallow
'0000-00-00', enable the NO_ZERO_DATE mode.