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

    XMLWordPrintable

    Details

    • 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;

        Attachments

          Issue Links

            Activity

              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: