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

sql_mode NO_ZERO_DATE conflicts with versioned tables

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.5.8
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      The sql_mode variable NO_ZERO_DATE specifies to reject any INSERT and UPDATE queries where a date value is submitted as '0000-00-00'. Though if a new column is added to a table, then the value '0000-00-00' is still inserted for all rows. It is a session variable and not an attribute of the table column, which causes problems if the sql mode is entered after a table has been created. For example:

      1:

      CREATE TABLE abc (a DATE NOT NULL DEFAULT '0000-00-00');
      

      Now the following ALTER TABLE will fail, despite that it does not touch column 'a'.

      SET SESSION sql_mode='NO_ZERO_DATE';
      ALTER TABLE abc ADD b INT NOT NULL;
      

      This is especially apparent in versioned tables. Both these situations will cause error code 1067:

      2:

      SET SESSION sql_mode='NO_ZERO_DATE';
      CREATE TABLE abc (a INT NOT NULL) WITH SYSTEM VERSIONING;
      

      3:

      CREATE TABLE abc (a INT NOT NULL) WITH SYSTEM VERSIONING;
      SET SESSION system_versioning_alter_history=KEEP, SESSION sql_mode='NO_ZERO_DATE';
      ALTER TABLE abc ADD b INT NOT NULL;
      

      Given that '0000-00-00' values can still occur in a table, despite that NO_ZERO_DATE may at one point in time become configured, I think that a column's default value should not be validated against this restriction, especially when a table alteration does not even affect that column. Perhaps an even better solution would be to make NO_ZERO_DATE an attribute of the column, rather than a session variable.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              user2180613 Remy Fox
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration