[MDEV-17329] Inconsistency of NULL date value conversion to zero date in comparison context Created: 2018-09-30  Updated: 2019-11-18

Status: Open
Project: MariaDB Server
Component/s: Data types, Temporal Types
Affects Version/s: 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: regression

Issue Links:
Relates
relates to MDEV-16991 Rounding vs truncation for TIME, DATE... Closed
relates to MDEV-17318 CAST(LEAST(zero_date,non_zero_date) A... Closed
relates to MDEV-17335 COALESCE() respects NO_ZERO_DATE, NO_... Open
Epic Link: Data type cleanups

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

 Comments   
Comment by Alexander Barkov [ 2018-10-01 ]

A related problem is reported in MDEV-17335.

Comment by Alexander Barkov [ 2018-10-05 ]

Tests for this bug should include the following:

--echo #
 
--echo # MDEV-17351 MICROSECOND(XXX(int_number_out_of_range)) erroneously returns 999999
 
--echo #
 
 
 
SET sql_mode='';
 
CREATE TABLE t1 AS SELECT
 
  GREATEST(8395959, TIME'00:00:00') AS c1,
 
  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)  AS c2,
 
  TIMESTAMP('2001-01-01 00:00:00', 8395959) AS c3;
 
SHOW CREATE TABLE t1;
 
SELECT * FROM t1;
 
DROP TABLE t1;
 
SET sql_mode='';
 
 
 
SELECT
 
  MICROSECOND(GREATEST(8395959, TIME'00:00:00')) AS c1,
 
  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)) AS c2,
 
  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00', 8395959)) AS c3;
 
 
 
SELECT
 
  MICROSECOND(GREATEST(8395959.0, TIME'00:00:00')) AS c1,
 
  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0)) AS c2,
 
  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00', 8395959.0)) AS c3;
 
 
 
SELECT
 
  MICROSECOND(GREATEST(8395959.00, TIME'00:00:00')) AS c1,
 
  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.00)) AS c2,
 
  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00', 8395959.00)) AS c3;
 
 
 
SELECT
 
  MICROSECOND(GREATEST(8395959.000000, TIME'00:00:00')) AS c1,
 
  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.000000)) AS c2,
 
  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00', 8395959.000000)) AS c3;
 
 
 
SELECT
 
  MICROSECOND(GREATEST(8395959.0000000, TIME'00:00:00')) AS c1,
 
  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0000000)) AS c2,
 
  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00', 8395959.0000000)) AS c3;
 
 
 
 
 
SELECT
 
  MICROSECOND(GREATEST(8395959, TIME'00:00:00.0')) AS c1,
 
  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00.0', 8395959)) AS c2,
 
  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00.0', 8395959)) AS c3;
 
 
 
SELECT
 
  MICROSECOND(GREATEST(8395959, TIME'00:00:00.00')) AS c1,
 
  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00.00', 8395959)) AS c2,
 
  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00.00', 8395959)) AS c3;
 
 
 
SELECT
 
  MICROSECOND(GREATEST(8395959, TIME'00:00:00.00')) AS c1,
 
  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00.00', 8395959)) AS c2,
 
  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00.00', 8395959)) AS c3;
 
 
 
SELECT
 
  MICROSECOND(GREATEST(8395959, TIME'00:00:00.0000000')) AS c1,
 
  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00.0000000', 8395959)) AS c2,
 
  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00.0000000', 8395959)) AS c3;
 
 
 
SELECT
 
  MICROSECOND(GREATEST(8395959, TIME'00:00:00.0000000')) AS c1,
 
  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00.0000000', 8395959)) AS c2,
 
  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00.0000000', 8395959)) AS c3;
 
 
 
 
 
SELECT TIME(8395959), MICROSECOND(8395959);
 
SELECT TIME(8395959.0), MICROSECOND(8395959.0);
 
SELECT TIME(8395959.00), MICROSECOND(8395959.00);
 
SELECT TIME(8395959.000), MICROSECOND(8395959.000);
 
SELECT TIME(8395959.0000), MICROSECOND(8395959.0000);
 
SELECT TIME(8395959.00000), MICROSECOND(8395959.00000);
 
SELECT TIME(8395959.000000), MICROSECOND(8395959.000000);
 
SELECT TIME(8395959.0000000), MICROSECOND(8395959.0000000);
 
 
 
SELECT TIME('839:59:59'), MICROSECOND('839:59:59');
 
SELECT TIME('839:59:59.0'), MICROSECOND('839:59:59.0');
 
SELECT TIME('839:59:59.00'), MICROSECOND('839:59:59.00');
 
SELECT TIME('839:59:59.000'), MICROSECOND('839:59:59.000');
 
SELECT TIME('839:59:59.0000'), MICROSECOND('839:59:59.0000');
 
SELECT TIME('839:59:59.00000'), MICROSECOND('839:59:59.00000');
 
SELECT TIME('839:59:59.000000'), MICROSECOND('839:59:59.000000');
 
SELECT TIME('839:59:59.0000000'), MICROSECOND('839:59:59.0000000');
 
 
 
 
 
SELECT
 
  TIME(CAST(8395959 AS SIGNED)) AS c1,
 
  MICROSECOND(CAST(8395959 AS SIGNED)) AS c2;
 
 
 
SELECT
 
  TIME(CAST(8395959 AS DECIMAL(30,0))) AS c1,
 
  MICROSECOND(CAST(8395959 AS DECIMAL(30,0))) AS c2;
 
 
 
SELECT
 
  TIME(CAST(8395959 AS DECIMAL(30,1))) AS c1,
 
  MICROSECOND(CAST(8395959 AS DECIMAL(30,1))) AS c2;
 
 
 
SELECT
 
  TIME(CAST(8395959 AS DECIMAL(30,2))) AS c1,
 
  MICROSECOND(CAST(8395959 AS DECIMAL(30,2))) AS c2;
 
 
 
SELECT
 
  TIME(CAST(8395959 AS DECIMAL(30,3))) AS c1,
 
  MICROSECOND(CAST(8395959 AS DECIMAL(30,3))) AS c2;
 
 
 
SELECT
 
  TIME(CAST(8395959 AS DECIMAL(30,4))) AS c1,
 
  MICROSECOND(CAST(8395959 AS DECIMAL(30,4))) AS c2;
 
 
 
SELECT
 
  TIME(CAST(8395959 AS DECIMAL(30,5))) AS c1,
 
  MICROSECOND(CAST(8395959 AS DECIMAL(30,5))) AS c2;
 
 
 
SELECT
 
  TIME(CAST(8395959 AS DECIMAL(30,6))) AS c1,
 
  MICROSECOND(CAST(8395959 AS DECIMAL(30,6))) AS c2;
 
 
 
SELECT
 
  TIME(CAST(8395959 AS DECIMAL(30,7))) AS c1,
 
  MICROSECOND(CAST(8395959 AS DECIMAL(30,7))) AS c2;
 
 
 
SELECT
 
  TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS SIGNED)) AS c1,
 
  MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS SIGNED)) AS c2;
 
 
 
SELECT
 
  TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,0))) AS c1,
 
  MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,0))) AS c2;
 
 
 
SELECT
 
  TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,1))) AS c1,
 
  MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,1))) AS c2;
 
 
 
SELECT
 
  TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,2))) AS c1,
 
  MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,2))) AS c2;
 
 
 
SELECT
 
  TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,3))) AS c1,
 
  MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,3))) AS c2;
 
 
 
SELECT
 
  TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,4))) AS c1,
 
  MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,4))) AS c2;
 
 
 
SELECT
 
  TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,5))) AS c1,
 
  MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,5))) AS c2;
 
 
 
SELECT
 
  TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,6))) AS c1,
 
  MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,6))) AS c2;
 
 
 
SELECT
 
  TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,7))) AS c1,
 
  MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,7))) AS c2;

Generated at Thu Feb 08 08:35:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.