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

Change NO_ZERO_IN_DATE to disallow date '0000-01-01'

    Details

    • Type: Task
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Fix Version/s: 10.5
    • Component/s: Data types
    • Labels:
      None

      Description

      According to the SQL standard, valid date values are described as follows:

      Table 9 — Valid values for datetime fields

      • YEAR 0001 to 9999
      • MONTH 01 to 12
      • DAY Within the range 1 to 31, but further constrained by the value of MONTHand YEAR fields, according to the rules for well-formed dates in the Gregorian calendar.

      MariaDB allows dates with zero YYYY, MM, DD components with an empty sql_mode, and has two flags to provide a more SQL standard behavior:

      • NO_ZERO_DATE disallows dates '0000-00-00' and datetimes '0000-00-00 00:00:00' (when all components are zeros)
      • NO_ZERO_IN_DATE disallows dates 'YYYY-00-00' (i.e. non-zero YYYY, but zero MM-DD)
      • NO_ZERO_IN_DATE disallows dates 'YYYY-MM-00' (i.e. non-zero YYYY and non-zero MM, but zero DD)
      • NO_ZERO_IN_DATE disallows dates 'YYYY-00-DD' (i.e. non-zero YYYY and non-zero DD, but zero MM)

      This combination provides best SQL standard compatibility:

      SET sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE';
      

      However, dates with zero year are still possible:

      SET sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
      CREATE OR REPLACE TABLE t1 (a DATE);
      INSERT INTO t1 VALUES ('0000-01-01');
      SELECT * FROM t1;
      

      +------------+
      | a          |
      +------------+
      | 0000-01-01 |
      +------------+
      

      SET sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
      CREATE OR REPLACE TABLE t1 (a DATETIME);
      INSERT INTO t1 VALUES ('0000-01-01 00:00:00');
      SELECT * FROM t1;
      

      +---------------------+
      | a                   |
      +---------------------+
      | 0000-01-01 00:00:00 |
      +---------------------+
      

      In the above example, '0000-01-01' is not standard, because it has zero YYYY.
      But there is no a way to disallow such values, as neither NO_ZERO_DATE nor NO_ZERO_IN_DATE disallow it.

      Under terms of this task we'll change the meaning of NO_ZERO_IN_DATE to disallow all DATE and DATETIME values that have zero in any component of YYYY-MM-DD.

      The above two scripts will return errors.

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated: