[MDEV-8778] Temporal fields with zero dates do not respect sql_mode=traditional Created: 2015-09-09  Updated: 2019-04-26

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Temporary
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.4

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

Issue Links:
Blocks
blocks MDEV-4654 Wrong warning for CAST(TIME('10:20:30... Open

 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.
Generated at Thu Feb 08 07:29:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.