Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8778

Temporal fields with zero dates do not respect sql_mode=traditional

        Details

        • Type: Bug
        • Status: Open
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 10.1, 10.0, 5.5
        • Fix Version/s: 10.1
        • Component/s: Data Definition - Temporary
        • Labels:
          None

          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.

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

                  • Assignee:
                    bar Alexander Barkov
                    Reporter:
                    bar Alexander Barkov
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated: