Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11341

STR_TO_DATE does not return NULL for invalid dates

Details

    Description

      MariaDB 10.1

      MariaDB [test]> SELECT STR_TO_DATE('1949-02-30','%Y-%m-%d');
      +--------------------------------------+
      | STR_TO_DATE('1949-02-30','%Y-%m-%d') |
      +--------------------------------------+
      | 1949-02-30                           |
      +--------------------------------------+
      1 row in set (0.00 sec)
      

      According to specification, it should be NULL for invalid date values.
      Reproducible with MySQL 5.5/5.6, but fixed in MySQL 5.7 (only it produces duplicate warnings, let's not do it):

      MySQL 5.7

      MySQL [test]> SELECT STR_TO_DATE('1949-02-30','%Y,%m,%d');
      +--------------------------------------+
      | STR_TO_DATE('1949-02-30','%Y,%m,%d') |
      +--------------------------------------+
      | NULL                                 |
      +--------------------------------------+
      1 row in set, 2 warnings (0.00 sec)
       
      MySQL [test]> show warnings;
      +---------+------+-----------------------------------------------------------------+
      | Level   | Code | Message                                                         |
      +---------+------+-----------------------------------------------------------------+
      | Warning | 1411 | Incorrect datetime value: '1949-02-30' for function str_to_date |
      | Warning | 1411 | Incorrect datetime value: '1949-02-30' for function str_to_date |
      +---------+------+-----------------------------------------------------------------+
      2 rows in set (0.00 sec)
      

      Found on stackoverflow.

      Attachments

        Activity

          svoj Sergey Vojtovich added a comment - bar , please review https://github.com/MariaDB/server/pull/3829
          svoj Sergey Vojtovich added a comment - - edited

          MySQL fix: https://github.com/mysql/mysql-server/commit/3bf25efe1bdc92618e88584b339cf2eaf55e5423
          Apparently it was fixed in 5.7.44, 8.0.35, and 8.2.0 and new behaviour was documented.

          MariaDB manual says: "If str contains an illegal date, time, or datetime value, STR_TO_DATE() returns NULL".

          The TIME_INVALID_DATES flag was introduced in https://github.com/mysql/mysql-server/commit/2a49121590930ad9ef69718caad67304e94b9c00
          It was implemented such that if this flag is set, provided date is not checked for correctness. SQL interface to this flag was sql_mode='ALLOW_INVALID_DATES'. However this revision didn't cover STR_TO_DATE().

          check_date(TIME_INVALID_DATES) was added to STR_TO_DATE() in 4dddafb95cf4c6eb7120402e3c218e348bb1156e, and it preserved old behaviour.

          svoj Sergey Vojtovich added a comment - - edited MySQL fix: https://github.com/mysql/mysql-server/commit/3bf25efe1bdc92618e88584b339cf2eaf55e5423 Apparently it was fixed in 5.7.44, 8.0.35, and 8.2.0 and new behaviour was documented. MariaDB manual says: "If str contains an illegal date, time, or datetime value, STR_TO_DATE() returns NULL". The TIME_INVALID_DATES flag was introduced in https://github.com/mysql/mysql-server/commit/2a49121590930ad9ef69718caad67304e94b9c00 It was implemented such that if this flag is set, provided date is not checked for correctness. SQL interface to this flag was sql_mode='ALLOW_INVALID_DATES' . However this revision didn't cover STR_TO_DATE() . check_date(TIME_INVALID_DATES) was added to STR_TO_DATE() in 4dddafb95cf4c6eb7120402e3c218e348bb1156e, and it preserved old behaviour.
          bar Alexander Barkov added a comment - The patch https://github.com/MariaDB/server/pull/3829/commits/7196d69d922ea9312ca0691927c5446b1880b7df is ok to push.

          serg, https://github.com/MariaDB/server/pull/3829 is now approved. We need your decision if it can go directly to main or it has to go via preview first.

          svoj Sergey Vojtovich added a comment - serg , https://github.com/MariaDB/server/pull/3829 is now approved. We need your decision if it can go directly to main or it has to go via preview first.

          Directly, it's a bug fix.

          serg Sergei Golubchik added a comment - Directly, it's a bug fix.

          People

            svoj Sergey Vojtovich
            elenst Elena Stepanova
            Votes:
            1 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.