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

The meaning of NO_ZERO_DATE is not clear for DATETIME

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

          Ascending order - Click to sort in descending order
          bar Alexander Barkov created issue -
          bar Alexander Barkov made changes -
          Field Original Value New Value
          Priority Major [ 3 ] Critical [ 2 ]
          bar Alexander Barkov made changes -
          Sprint 10.1.6-2 [ 7 ]
          bar Alexander Barkov made changes -
          Rank Ranked lower
          bar Alexander Barkov made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          bar Alexander Barkov made changes -
          Description The documentation days:
          {quote}
          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.
          {quote}
          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
          {code}
          SET sql_mode='NO_ZERO_DATE';
          SELECT TIMESTAMP'0000-00-00 10:20:30', TIMESTAMP'0000-00-01 10:20:30';
          {code}
          returns
          {noformat}
          +--------------------------------+--------------------------------+
          | 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)
          {noformat}
          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:
          {code}
          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;
          {code}
          returns:
          {noformat}
          +---------------------+--------------------------------+
          | 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)
          {noformat}
          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.
          		 The documentation days:
          {quote}
          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.
          {quote}
          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
          {code}
          SET sql_mode='NO_ZERO_DATE';
          SELECT TIMESTAMP'0000-00-00 10:20:30', TIMESTAMP'0000-00-01 10:20:30';
          {code}
          returns
          {noformat}
          +--------------------------------+--------------------------------+
          | 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)
          {noformat}
          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:
          {code}
          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;
          {code}
          returns:
          {noformat}
          +---------------------+--------------------------------+
          | 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)
          {noformat}
          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:
          {code}
          SET sql_mode='NO_ZERO_DATE';
          SELECT STR_TO_DATE('0000-00-00 10:20:30','%Y-%m-%d %h:%i:%s');
          {code}
          -> NULL

          Example4:
          {code}
          SET sql_mode='NO_ZERO_DATE';
          SELECT TIMESTAMP'0000-00-00 10:20:30';
          {code}
          -> NULL
          bar Alexander Barkov made changes -
          Description The documentation days:
          {quote}
          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.
          {quote}
          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
          {code}
          SET sql_mode='NO_ZERO_DATE';
          SELECT TIMESTAMP'0000-00-00 10:20:30', TIMESTAMP'0000-00-01 10:20:30';
          {code}
          returns
          {noformat}
          +--------------------------------+--------------------------------+
          | 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)
          {noformat}
          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:
          {code}
          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;
          {code}
          returns:
          {noformat}
          +---------------------+--------------------------------+
          | 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)
          {noformat}
          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:
          {code}
          SET sql_mode='NO_ZERO_DATE';
          SELECT STR_TO_DATE('0000-00-00 10:20:30','%Y-%m-%d %h:%i:%s');
          {code}
          -> NULL

          Example4:
          {code}
          SET sql_mode='NO_ZERO_DATE';
          SELECT TIMESTAMP'0000-00-00 10:20:30';
          {code}
          -> NULL
          		 The documentation days:
          {quote}
          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.
          {quote}
          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
          {code}
          SET sql_mode='NO_ZERO_DATE';
          SELECT TIMESTAMP'0000-00-00 10:20:30', TIMESTAMP'0000-00-01 10:20:30';
          {code}
          returns
          {noformat}
          +--------------------------------+--------------------------------+
          | 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)
          {noformat}
          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:
          {code}
          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;
          {code}
          returns:
          {noformat}
          +---------------------+--------------------------------+
          | 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)
          {noformat}
          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:
          {code}
          SET sql_mode='NO_ZERO_DATE';
          SELECT STR_TO_DATE('0000-00-00 10:20:30','%Y-%m-%d %h:%i:%s');
          {code}

          Example4:
          {code}
          SET sql_mode='NO_ZERO_DATE';
          SELECT TIMESTAMP'0000-00-00 10:20:30';
          {code}

          Exapmple 5:
          {code}
          SET old_mode=zero_date_time_cast;
          SET SQL_MODE='NO_ZERO_DATE';
          SELECT CAST(TIME'10:20:30' AS DATETIME);
          {code}

          Example 6:
          {code}
          SET old_mode=zero_date_time_cast;
          SET SQL_MODE='NO_ZERO_DATE';
          SELECT CAST((CAST('10:20:30' AS TIME)) AS DATETIME);
          {code}

          Example 7:
          {code}
          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);
          {code}
          bar Alexander Barkov made changes -
          Component/s Temporal Types [ 11000 ]
          Fix Version/s 10.1.6 [ 19401 ]
          Fix Version/s 10.1 [ 16100 ]
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 70040 ] MariaDB v4 [ 149284 ]

          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.