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

ALTER IGNORE TABLE only sometimes converts NULL to DEFAULT value

    XMLWordPrintable

Details

    Description

      While reviewing MDEV-32523, I noticed the following anomaly:

      --source include/have_innodb.inc
      		 
      CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT DEFAULT 1) ENGINE=INNODB;
      		 
      INSERT INTO t1 VALUES(1, NULL);
      		 
      ALTER IGNORE TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2;
      		 
      SELECT * FROM t1;
      		 
      DROP TABLE t1;

      When the test is run with

      ./mtr --mysqld=--alter-algorithm=copy

      the SELECT will report the value of the column c2 as 0, not the previous or current DEFAULT value.

      When the test is run with

      ./mtr --mysqld=--alter-algorithm=inplace

      it will (before a fix of MDEV-32523) report c2=1, corresponding to the old DEFAULT value of the column.

      What is the expected outcome here?

      Another anomaly:

      --source include/have_innodb.inc
      		 
      CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT DEFAULT 1, CHECK (c2>3))
      		 
      ENGINE=INNODB;
      		 
      INSERT INTO t1 VALUES(1, NULL);
      		 
      ALTER IGNORE TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2;
      		 
      SELECT * FROM t1;
      		 
      DROP TABLE t1;

      This will end up with an empty table when using ALGORITHM=COPY, so the IGNORE is not ignoring CHECK constraints. If I use ALGORITHM=INPLACE, the CHECK constraint will wrongly be ignored.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32523 ASAN errors or assertion failure in row_merge_buf_add

          • Major - Major loss of function.
          • Stalled

          Activity

            People

              monty Michael Widenius
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.