Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Description
This script:
SET SQL_MODE=DEFAULT;
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a DATE);
|
INSERT INTO t1 VALUES ('0000-00-00'),('0000-00-00');
|
SELECT a,DATE(a) FROM t1;
|
returns these results as expected:
+------------+------------+
|
| a | DATE(a) |
|
+------------+------------+
|
| 0000-00-00 | 0000-00-00 |
|
| 0000-00-00 | 0000-00-00 |
|
+------------+------------+
|
Now if I change sql_mode and rerun the query:
SET SQL_MODE=TRADITIONAL;
|
SELECT a,DATE(a) FROM t1;
|
it returns
+------------+---------+
|
| a | DATE(a) |
|
+------------+---------+
|
| 0000-00-00 | NULL |
|
| 0000-00-00 | NULL |
|
+------------+---------+
|
Notice, the function DATE() respects TRADITIONAL (which includes NO_ZERO_DATES), but the field does not.
Equality with a zero date is also allowed in sql_mode=TRADITIONAL:
MariaDB [test]> SELECT * FROM t1 WHERE a='0000-00-00';
|
+------------+
|
| a |
|
+------------+
|
| 0000-00-00 |
|
| 0000-00-00 |
|
+------------+
|
2 rows in set (0.00 sec)
|
It's not clear that the expected behavior would be and needs a discussion.
Invalid and zero dates were added to store temporal intervals. Perhaps implementing the SQL-standard intervals and disallowing invalid/zero dates in "normal" DATE/DATETIME columns would be a solution.
Attachments
Issue Links
- blocks
-
-
- Open
-