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:
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.
In 10.4 both DATE() and TIMESTAMP() convert invalid dates to zero dates (rather than NULL) for comparison
Just for information: with NO_ZERO_DATE set, zero date literals are rejected by the parser:
At the same time, comparison between an invalid-date-in-string converts invalid dates to zero dates:
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
Notice, DATE() converted an invalid date to NULL, TIMESTAMP() converted an invalid datetime value to zero.
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.
will still return non-NULL values. In this query we convert string literals '0000-00-00' to DATE and DATETIME for comparison.
Tests, in addition to functions DATE() and TIMESTAMP(), should also conver:
- CAST(AS DATE)
- CAST(AS DATETIME(N)).