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

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)).