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

ADD COLUMN with DROP COLUMN wrongly inherits attributes

    XMLWordPrintable

    Details

      Description

      In the following test case (which depends on the MDEV-13134 ALGORITHM=NOCOPY syntax and the MDEV-15562 instant DROP COLUMN), the ALTER TABLE is being unexpectedly refused on MariaDB 10.4:

      --source include/have_innodb.inc
      CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE) ENGINE=InnoDB;
      ALTER TABLE t1 DROP COLUMN b, ADD COLUMN b INT NOT NULL DEFAULT 0,
      ALGORITHM=NOCOPY;
      DROP TABLE t1;
      

      The reason for this is that while the DROP COLUMN will correctly imply DROP INDEX, the ADD COLUMN will unexpectedly imply ADD INDEX. The new column b should not inherit anything from the old column b!

      If we split the ADD COLUMN clause into a separate ALTER TABLE statement, then the table will be altered as I would expect, with no index on the new column b. I can also work around this by adding a (redundant) clause DROP INDEX b. Likewise, if I use a different column name c for the new column, the operation will succeed:

      --source include/have_innodb.inc
      CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE) ENGINE=InnoDB;
      ALTER TABLE t1 DROP COLUMN b, ADD COLUMN c INT NOT NULL DEFAULT 0,
      ALGORITHM=NOCOPY;
      SHOW CREATE TABLE t1;
      DROP TABLE t1;
      

      If I change the column type to TEXT, it gets even more weird. The following test produces the same weird results already on 10.1 (the first still-supported version of MariaDB Server that uses the ALTER TABLE API that was introduced in MySQL 5.6):

      CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE);
      ALTER TABLE t1 DROP COLUMN b, ADD COLUMN b TEXT;
      SHOW CREATE TABLE t1;
      DROP TABLE t1;
      

      This will create a UNIQUE INDEX (b(4)), instead of (also wrongly) returning an error that only a prefix index on TEXT columns may be defined. We never specified any column prefix for the index; it was b INT UNIQUE.

      10.1 b01a95f6fc47151345bf298b606492a962e28150

      CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE);
      ALTER TABLE t1 DROP COLUMN b, ADD COLUMN b TEXT;
      SHOW CREATE TABLE t1;
      Table	Create Table
      t1	CREATE TABLE `t1` (
        `a` int(11) NOT NULL,
        `b` text,
        PRIMARY KEY (`a`),
        UNIQUE KEY `b` (`b`(4))
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1
      

      Even if we assumed that it is correct to inherit the attributes from an old column that had the same name, I think that an error

      1170: BLOB/TEXT column 'b' used in key specification without a key length
      

      should be returned, just like for this case:

      CREATE TABLE t1(a INT PRIMARY KEY, b TEXT UNIQUE);
      

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated: