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

INPLACE ALTER does not fail on bad implicit default value

    XMLWordPrintable

    Details

      Description

      In all below scripts we use ALTER for a table that:

      • is not empty
      • adds a new DATE NOT NULL field with no implicit default, so '0000-00-00' is implied as a default
      • set sql_mode in the way to disallow zero dates

      Such ALTER should fail, because new fields in all existing rows should be assigned to '0000-00-00', which is not allowed.

      It fails with MyISAM:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DATE) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (now());
      SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_DATE';
      ALTER TABLE t1 ADD b DATE NOT NULL;
      

      ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column `test`.`t1`.`b` at row 1
      

      It fails with INNODB, using COPY:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DATE) ENGINE=INNODB;
      INSERT INTO t1 VALUES (now());
      SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_DATE';
      ALTER TABLE t1 ALGORITHM=COPY, ADD b DATE NOT NULL;
      

      It does not fail with INNODB, using INPLACE:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a DATE) ENGINE=INNODB;
      INSERT INTO t1 VALUES (now());
      SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_DATE';
      ALTER TABLE t1 ALGORITHM=INPLACE, ADD b DATE NOT NULL;
      

      Query OK, 0 rows affected (0.007 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      

      Notice, it silently performed the ALTER without any warnings.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              thiru Thirunarayanan Balathandayuthapani
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: