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

[Bug #68041] Zero date can be inserted in strict no-zero mode through a default value

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 10.0.17, 10.1.3, 5.5.42
        • Fix Version/s: 10.1.6
        • Component/s: Temporal Types
        • Labels:
        • Sprint:
          10.1.6-1

          Description

          Upstream bug: http://bugs.mysql.com/bug.php?id=68041

          I create a table with zero date:

          SET sql_mode=DEFAULT;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DATE  DEFAULT '0000-00-00');

          Now change sql_mode to traditional:

          SET sql_mode=traditional;
INSERT INTO t1 VALUES ('0000-00-00');

          It correctly rejects the value:

          ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'a' at row 1

          However, It's still possible to insert the bad value through the default:

          INSERT INTO t1 VALUES ();
SELECT * FROM t1;

          The INSERT statement works (even without warnings) and the SELECT statement returns:

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

          The expected behaviour would be to reject the INSERT that uses the default value '0000-00-00'.

          Using DEFAULT also does not reject:

          SET sql_mode=DEFAULT;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a TIMESTAMP DEFAULT '0000-00-00 00:00:00');
SET sql_mode=traditional;
INSERT INTO t1 VALUES (DEFAULT);

          It also does not reject bad values when using INSERT..SELECT:

          SET sql_mode=DEFAULT;
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (a DATE DEFAULT '0000-00-00');
CREATE TABLE t2 (a DATE DEFAULT '0000-00-00');
INSERT INTO t2 VALUES ('0000-00-00');
SET sql_mode=traditional;
INSERT INTO t1 (a) SELECT a FROM t2;
DROP TABLE t1, t2;

            Gliffy Diagrams

              Attachments

                Issue Links

                blocks

                Task - A task that needs to be done. MDEV-3929 Add system variable explicit_defaults_for_timestamp for compatibility with MySQL

                • Critical - Crashes, loss of data, severe memory leak.
                • Closed
                links to

                Web Link Bug #68041 Zero date can be inserted in strict no-zero mode through a default value

                  Activity

                  Hide
                  Permalink
                  bar Alexander Barkov added a comment -

                  In MySQL-5.7, the patch for explicit_defaults_for_timestamp depends on the code that fixes this bug.

                  Show
                  bar Alexander Barkov added a comment - In MySQL-5.7, the patch for explicit_defaults_for_timestamp depends on the code that fixes this bug.

                    People

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

                      Dates

                      • Created:
                        Updated:
                        Resolved:

                        Time Tracking

                        Estimated:
                        Original Estimate - 0 minutes
                        0m
                        Remaining:
                        Remaining Estimate - 0 minutes
                        0m
                        Logged:
                        Time Spent - 2 hours, 15 minutes
                        2h 15m

                          Agile