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

Inconsistency of NULL date value conversion to zero date in comparison context

    XMLWordPrintable

Details

    Description

      MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:

      • 0000-00-00
      • 2001-00-01
      • 2001-01-00
      • 2001-02-30

      Functions DATE() and TIMESTAMP() convert invalid dates to zero date (instead of NULL) in comparison context.
      However, this conversion works differently in different versions.

      • In 5.5 and 10.4 both functions DATE() and TIMESTAMP() convert invalid dates to NULL, so comparison operators return non-NULL values
      • 10.0, 10.1, 10.2, 10.3, DATE() does not convert invalid dates to zero dates, while the function TIMESTAMP() does.

      It's not clear which way is correct.

      Details:

      In 10.4 both DATE() and TIMESTAMP() convert invalid dates to zero dates (rather than NULL) for comparison

      SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
      SELECT
        DATE('0000-00-00') AS d,
        DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
        TIMESTAMP('0000-00-00 00:00:00') AS dt,
        TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
      

      +------+------+------+-------+
      | d    | cmpd | dt   | cmpdd |
      +------+------+------+-------+
      | NULL |    1 | NULL |     1 |
      +------+------+------+-------+
      

      Just for information: with NO_ZERO_DATE set, zero date literals are rejected by the parser:

      SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
      SELECT DATE'0000-00-00' < DATE'2001-01-01';
      

      ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
      

      At the same time, comparison between an invalid-date-in-string converts invalid dates to zero dates:

      SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
      SELECT
        '0000-00-00' < DATE'2001-01-01' AS c1,
        '0000-00-00 00:00:00' < TIMESTAMP'2001-01-01 00:00:00' AS c2;
      

      +----+----+
      | c1 | c2 |
      +----+----+
      |  1 |  1 |
      +----+----+
      

      String literals and functions DATE() and TIMESTAMP() work the same way.

      In version starting from 10.0 and up to 10.3 only TIMESTAMP() converts invalid dates to zero dates for comparison

      SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
      SELECT
        DATE('0000-00-00') < DATE'2001-01-01' AS c1,
        TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
      

      +------+------+
      | c1   | c2   |
      +------+------+
      | NULL |    1 |
      +------+------+
      

      Notice, DATE() converted an invalid date to NULL, TIMESTAMP() converted an invalid datetime value to zero.

      In 5.5, both DATE() and DATETIME() convert invalid dates to zero dates for comparison

      SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
      SELECT
         DATE('0000-00-00') < DATE'2001-01-01' AS c1,
         TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
      

      +------+------+
      | c1   | c2   |
      +------+------+
      |    1 |    1 |
      +------+------+
      

      Conclusions

      During a Slack discussion between serg and bar, the following decision was made:

      Functions:

      SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
      SELECT
        DATE('0000-00-00') AS d,
        DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
        TIMESTAMP('0000-00-00 00:00:00') AS dt,
        TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;```
      

      should return NULL in all columns. A result of a function, DATE() and DATETIME() in this example, should not depend on its context. So it should work as follows:

      • Functions values are calculated normally, NULL is returned.
      • NULLs is passed into comparison operators.
      • Comparison operators return NULL.

      Literal:

      SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
      SELECT
       '0000-00-00' < DATE'2001-01-01' AS cmpd,
       '0000-00-00 00:00:00' < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
      

      will still return non-NULL values. In this query we convert string literals '0000-00-00' to DATE and DATETIME for comparison.

      Tests

      Tests, in addition to functions DATE() and TIMESTAMP(), should also conver:

      • CAST(AS DATE)
      • CAST(AS DATETIME(N)).

      Attachments

        Issue Links

          Activity

            People

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

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.