Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2, 10.3
-
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