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

Effect of NO_ZERO_DATE and NO_ZERO_IN_DATE is reversed

    Details

    • Type: Bug
    • Status: In Review (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1, 10.2
    • Fix Version/s: 10.1, 10.2
    • Component/s: Temporal Types
    • Labels:
      None

      Description

      The data and query example were taken from date_formats.test.

      I'm not sure SELECTs are supposed to be affected by NO_ZERO... modes at all, at least the manual doesn't say anything about it, it only mentions INSERTs.

      NO_ZERO_IN_DATE

      The NO_ZERO_IN_DATE mode affects whether the server permits dates in which the year part is nonzero but the month or day part is 0. (This mode affects dates such as '2010-00-01' or '2010-01-00', but not '0000-00-00'. To control whether the server permits '0000-00-00', use the NO_ZERO_DATE mode.)

      If this mode is enabled, dates with zero parts are inserted as '0000-00-00' and produce a warning.

      If NO_ZERO_IN_DATE and strict mode are enabled, dates with zero parts are not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, dates with zero parts are inserted as '0000-00-00' and produce a warning.

      NO_ZERO_DATE

      The NO_ZERO_DATE mode affects whether the server permits '0000-00-00' as a valid date.

      If this mode is enabled, '0000-00-00' is permitted and inserts produce a warning.

      If NO_ZERO_DATE mode and strict mode are enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning.

      However, assuming that SELECTs are also supposed to obey the modes, they seem to do it in an exactly opposite fashion:

      MariaDB [test]> set sql_mode='';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> create table t1 (date char(30), format char(30) not null);
      Query OK, 0 rows affected (0.28 sec)
       
      MariaDB [test]> insert into t1 values ('10:20:10', '%H:%i:%s');
      Query OK, 1 row affected (0.09 sec)
      

      Without any special modes, the query returns zero date:

      MariaDB [test]> set sql_mode='';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> 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 |
      +----------+----------+----------------------------+
      1 row in set (0.01 sec)
      

      MariaDB [test]> set sql_mode='NO_ZERO_DATE';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> 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 |
      +----------+----------+----------------------------+
      1 row in set (0.00 sec)
      

      MariaDB [test]> set sql_mode='NO_ZERO_IN_DATE';
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> 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 | NULL        |
      +----------+----------+-------------+
      1 row in set, 1 warning (0.00 sec)
       
      MariaDB [test]> show warnings;
      +---------+------+---------------------------------------------------------------+
      | Level   | Code | Message                                                       |
      +---------+------+---------------------------------------------------------------+
      | Warning | 1411 | Incorrect datetime value: '10:20:10' for function str_to_date |
      +---------+------+---------------------------------------------------------------+
      1 row in set (0.00 sec)
      

      It started happening in 10.1. MariaDB 10.0 has the reverse behavior, and so does MySQL 5.7:

      MySQL [test]> select @@version;
      +--------------+
      | @@version    |
      +--------------+
      | 5.7.17-debug |
      +--------------+
      1 row in set (0.01 sec)
      

      MySQL [test]> set sql_mode='NO_ZERO_DATE';
      Query OK, 0 rows affected, 2 warnings (0.00 sec)
       
      MySQL [test]> 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 | NULL        |
      +----------+----------+-------------+
      1 row in set, 1 warning (0.00 sec)
       
      MySQL [test]> show warnings;
      +---------+------+---------------------------------------------------------------+
      | Level   | Code | Message                                                       |
      +---------+------+---------------------------------------------------------------+
      | Warning | 1411 | Incorrect datetime value: '10:20:10' for function str_to_date |
      +---------+------+---------------------------------------------------------------+
      1 row in set (0.00 sec)
      

      MySQL [test]> set sql_mode='NO_ZERO_IN_DATE';
      Query OK, 0 rows affected, 1 warning (0.00 sec)
       
      MySQL [test]> 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 |
      +----------+----------+----------------------------+
      1 row in set (0.00 sec)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                elenst Elena Stepanova
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated: