Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4(EOL)
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
- relates to
-
MDEV-16991 Rounding vs truncation for TIME, DATETIME, TIMESTAMP
- Closed
-
MDEV-17318 CAST(LEAST(zero_date,non_zero_date) AS numeric_data_type) returns a wrong result
- Closed
-
MDEV-17335 COALESCE() respects NO_ZERO_DATE, NO_ZERO_IN_DATE but ignores unset ALLOW_INVALID_DATES
- Open