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

The meaning of NO_ZERO_DATE is not clear for DATETIME

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL)
    • 10.1.6
    • Temporal Types
    • None
    • 10.1.6-2

    Description

      The documentation days:

      The NO_ZERO_DATE mode affects whether the server permits '0000-00-00' as a valid date. Its effect also depends on whether strict SQL mode is enabled.

      But it does not say if DATETIME values with zero YYYYMMDD and non-zero hhmmss.uuuuuu parts (e.g. '0000-00 10:20:30') is permitted.

      Different parts of the code tread it differently for DATETIME.

      Example1: '0000-00-00 10:20:30' is not permitted

      SET sql_mode='NO_ZERO_DATE';
      SELECT TIMESTAMP'0000-00-00 10:20:30', TIMESTAMP'0000-00-01 10:20:30';

      returns

      +--------------------------------+--------------------------------+
      | TIMESTAMP'0000-00-00 10:20:30' | TIMESTAMP'0000-00-01 10:20:30' |
      +--------------------------------+--------------------------------+
      | NULL                           | 0000-00-01 10:20:30            |
      +--------------------------------+--------------------------------+
      1 row in set, 1 warning (0.00 sec)

      That is only the YYYYMMDD part is used to decide if the value is "ZERO DATE".

      Example2: '0000-00-00 10:20:30' behaves differently in the same script:

      SET sql_mode='NO_ZERO_DATE';
      DROP TABLE IF EXISTS t1; 
      CREATE TABLE t1 (a DATETIME);
      INSERT INTO t1 VALUES ('0000-00-00 10:20:30');
      SELECT a, LEAST(a,'2001-01-01 10:20:30') FROM t1;

      returns:

      +---------------------+--------------------------------+
      | a                   | LEAST(a,'2001-01-01 10:20:30') |
      +---------------------+--------------------------------+
      | 0000-00-00 10:20:30 | NULL                           |
      +---------------------+--------------------------------+
      1 row in set, 1 warning (0.00 sec)

      That is INSERT and "SELECT a FROM t1" treated '0000-00-00 10:20:30' as a permitted value, while LEAST() treated it as a non-permitted value.

      It should be fixed to treat in the same way in all context.

      Example3:

      SET sql_mode='NO_ZERO_DATE';
      SELECT STR_TO_DATE('0000-00-00 10:20:30','%Y-%m-%d %h:%i:%s');

      Example4:

      SET sql_mode='NO_ZERO_DATE';
      SELECT TIMESTAMP'0000-00-00 10:20:30';

      Exapmple 5:

      SET old_mode=zero_date_time_cast;
      SET SQL_MODE='NO_ZERO_DATE';
      SELECT CAST(TIME'10:20:30' AS DATETIME);

      Example 6:

      SET old_mode=zero_date_time_cast;
      SET SQL_MODE='NO_ZERO_DATE';
      SELECT CAST((CAST('10:20:30' AS TIME)) AS DATETIME);

      Example 7:

      SET old_mode=zero_date_time_cast;
      SET SQL_MODE='NO_ZERO_DATE';
      SELECT CAST((CAST(TIMESTAMP'0000-00-00 10:20:30' AS TIME)) AS DATETIME);

      Attachments

        Activity

          People

            bar Alexander Barkov
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.