[MDEV-15324] Implicit and explicit conversion to DATE/TIME from empty/bad values works inconsistently Created: 2018-02-15  Updated: 2023-04-27

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

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


 Description   

During implicit conversion on INSERT, empty/bad values are translated to zero datetime values:

SET sql_mode='';
 
CREATE OR REPLACE TABLE t1 (a DATETIME);
 
INSERT INTO t1 VALUES ('');
 
INSERT INTO t1 VALUES ('20011-01-01 00:00:00'); -- Notice wrong year
 
SELECT * FROM t1;


+---------------------+
 
| a                   |
 
+---------------------+
 
| 0000-00-00 00:00:00 |
 
| 0000-00-00 00:00:00 |
 
+---------------------+

Explicit conversion using CAST translates empty/bad values to NULL:

SELECT CAST('' AS DATETIME), CAST('20011-01-01 00:00:00' AS DATETIME);


+----------------------+------------------------------------------+
 
| CAST('' AS DATETIME) | CAST('20011-01-01 00:00:00' AS DATETIME) |
 
+----------------------+------------------------------------------+
 
| NULL                 | NULL                                     |
 
+----------------------+------------------------------------------+
 
1 row in set, 2 warnings (0.00 sec)

However, when CAST is in a comparison, it translates empty/bad values to zero datetime again:

SELECT
 
  CAST('' AS DATETIME)='0000-00-00 00:00:00' AS c1,
 
  CAST('' AS DATETIME)='0000-00-00 00:00:00' AS c2;


+------+------+
 
| c1   | c2   |
 
+------+------+
 
|    1 |    1 |
 
+------+------+
 
1 row in set, 2 warnings (0.00 sec)

Note, when fixing it, please mind this special behaviour for how IS NULL treats zero dates in NOT NULL datetime columns:
https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_is-null
Generated at Thu Feb 08 08:20:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.