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

The meaning of NO_ZERO_DATE is not clear for DATETIME

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Critical
        • Resolution: Fixed
        • Affects Version/s: 10.1, 10.0, 5.5
        • Fix Version/s: 10.1.6
        • Component/s: Temporal Types
        • Labels:
          None
        • Sprint:
          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);

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

                  • Assignee:
                    bar Alexander Barkov
                    Reporter:
                    bar Alexander Barkov
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    3 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved:

                      Agile