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

ALTER_COLUMN_VCOL is not set for generated stored columns

    XMLWordPrintable

Details

    Description

      The flag ALTER_COLUMN_VCOL is supposed to be set when an ALTER TABLE operation affects the base column of a generated column in a way that something may have to be recalculated. Consider the following example, which I am about to add to the test innodb.innodb-alter-nullable in 10.2:

      CREATE TABLE t1(c INT, g INT AS (c) PERSISTENT) ENGINE=InnoDB;
      CREATE TABLE t2(c INT, v INT AS (c) VIRTUAL) ENGINE=InnoDB;
      CREATE TABLE t3(c INT, v INT AS (c) VIRTUAL, INDEX(v)) ENGINE=InnoDB;
      INSERT INTO t1 SET c=NULL;
      INSERT INTO t2 SET c=NULL;
      INSERT INTO t3 SET c=NULL;
      SET @old_sql_mode = @@sql_mode;
      # Allow lossy conversions of data
      SET sql_mode = '';
      --enable_info
      ALTER TABLE t1 MODIFY c INT NOT NULL;
      ALTER TABLE t2 MODIFY c INT NOT NULL;
      ALTER TABLE t3 MODIFY c INT NOT NULL;
      --disable_info
      SET sql_mode = @old_sql_mode;
      SELECT * FROM t1;
      SELECT * FROM t2;
      SELECT * FROM t3;
      SELECT v FROM t3 FORCE INDEX(v);
      CHECK TABLE t1,t2,t3;
      DROP TABLE t1,t2,t3;
      

      For tables t1,t3 in the above example, we would have to recompute something when the ALTER TABLE updates c=NULL to c=0. For t3, we are doing the right thing, because all three flags will be set:

      ha_alter_info->handler_flags =
        ALTER_COLUMN_VCOL |
        ALTER_COLUMN_DEFAULT |
        ALTER_COLUMN_NOT_NULLABLE;
      

      For t2, it is correct to set only the following flags, because the virtual column that depends on the modified column is not being materialized anywhere (not in the table nor in any indexes):

      ha_alter_info->handler_flags =
        ALTER_COLUMN_DEFAULT |
        ALTER_COLUMN_NOT_NULLABLE;
      

      But, for t1, instead of setting only the 2 flags, we should set all 3 flags, because the stored generated column g should be updated in sync with its base column c.

      Starting with MDEV-14168 and MDEV-16365 in MariaDB 10.3.7 and 10.3.8, InnoDB supports ALGORITHM=INPLACE in non-strict mode (as well as for ALTER IGNORE TABLE) for converting a column from NULL to NOT NULL.

      I do not think that anything needs to be changed in MariaDB 10.2. With 10.3, we should set the flag if NULL values are allowed to be modified during the ALTER TABLE. The condition for that would be as follows:

      ha_alter_info->ignore || !thd_is_strict_mode(thd)
      

      Actually, we are currently unnecessarily setting the ALTER_COLUMN_VCOL flag when changing the base column of an indexed virtual column in strict mode. In this mode, InnoDB should be able to rebuild the table within ALGORITHM=INPLACE, without any changes to any virtual column values. (The operation would fail if any NULL values were encountered in the column.)

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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