MDEV-16849 Extending indexed VARCHAR column should be instantaneous

Analysis:
========
Increasing the length of the indexed varchar column is not an instant operation for
innodb.

Fix:
===
- Introduce the new handler flag 'Alter_inplace_info::ALTER_COLUMN_INDEX_LENGTH' to
indicate the index length differs due to change of column length changes.

- InnoDB makes the ALTER_COLUMN_INDEX_LENGTH flag as instant operation.

This is a port of Mysql fix.

    commit 913071c0b16cc03e703308250d795bc381627e37
    Author: Nisha Gopalakrishnan <nisha.gopalakrishnan@oracle.com>
    Date:   Wed May 30 14:54:46 2018 +0530

        BUG#26848813: INDEXED COLUMN CAN'T BE CHANGED FROM VARCHAR(15)
                      TO VARCHAR(40) INSTANTANEOUSLY

2 files changed, 797 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/r/alter_varchar_change.result b/mysql-test/suite/innodb/r/alter_varchar_change.result
new file mode 100644
index 00000000000..0b06fddd35e
--- /dev/null
+++ b/mysql-test/suite/innodb/r/alter_varchar_change.result
@@ -0,0 +1,461 @@
+CREATE PROCEDURE get_index_id(IN tbl_id INT, IN idx_name char(100), OUT idx_id INT)
+BEGIN
+SELECT index_id into idx_id FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE
+NAME=idx_name and TABLE_ID=tbl_id;
+END|
+CREATE PROCEDURE get_table_id(IN tbl_name char(100), OUT tbl_id INT)
+BEGIN
+SELECT table_id into tbl_id FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE
+NAME = tbl_name;
+END|
+SET @tbl_id = 0;
+SET @tbl1_id = 0;
+SET @idx_id = 0;
+SET @idx1_id = 0;
+CREATE TABLE t1(f1 INT NOT NULL,
+f2 VARCHAR(100) PRIMARY KEY)ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200);
+CALL get_table_id("test/t1", @tbl1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+1
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(200) NOT NULL,
+  PRIMARY KEY (`f2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL,
+f2 VARCHAR(100),
+INDEX idx(f2))ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200);
+CALL get_table_id("test/t1", @tbl1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+1
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(200) DEFAULT NULL,
+  KEY `idx` (`f2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL, f2 VARCHAR(100), f3 VARCHAR(100),
+INDEX idx(f2, f3), index idx1(f3, f2))ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200), MODIFY f3 VARCHAR(150);
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+1
+SELECT @idx1_id = @idx_id;
+@idx1_id = @idx_id
+1
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(200) DEFAULT NULL,
+  `f3` varchar(150) DEFAULT NULL,
+  KEY `idx` (`f2`,`f3`),
+  KEY `idx1` (`f3`,`f2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL, f2 VARCHAR(100),
+INDEX idx(f2(40)))ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200);
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+1
+SELECT @idx1_id = @idx_id;
+@idx1_id = @idx_id
+1
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(200) DEFAULT NULL,
+  KEY `idx` (`f2`(40))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL,
+f2 VARCHAR(100), FULLTEXT idx(f2))ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200);
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+1
+SELECT @idx1_id = @idx_id;
+@idx1_id = @idx_id
+1
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(200) DEFAULT NULL,
+  FULLTEXT KEY `idx` (`f2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL,
+f2 VARCHAR(100),
+f3 VARCHAR(50) as (f2) VIRTUAL,
+INDEX idx(f3))ENGINE=InnoDB;
+INSERT INTO t1(f1, f2) VALUES(1, repeat('a', 40));
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(100);
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+1
+SELECT @idx1_id = @idx_id;
+@idx1_id = @idx_id
+1
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(100) DEFAULT NULL,
+  `f3` varchar(50) GENERATED ALWAYS AS (`f2`) VIRTUAL,
+  KEY `idx` (`f3`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL,
+f2 VARCHAR(100),
+INDEX idx(f2(10)),
+INDEX idx1(f1))ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200), DROP INDEX idx1;
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+1
+SELECT @idx1_id = @idx_id;
+@idx1_id = @idx_id
+1
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(200) DEFAULT NULL,
+  KEY `idx` (`f2`(10))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL,
+f2 VARCHAR(100),
+INDEX idx(f2(10)))ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200), DROP INDEX idx, ADD INDEX idx(f2(10));
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+1
+SELECT @idx1_id = @idx_id;
+@idx1_id = @idx_id
+1
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(200) DEFAULT NULL,
+  KEY `idx` (`f2`(10))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL,
+f2 VARCHAR(100),
+INDEX idx(f2(10)))ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200), DROP INDEX idx, ADD INDEX idx(f2(50));
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+1
+SELECT @idx1_id = @idx_id;
+@idx1_id = @idx_id
+0
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(200) DEFAULT NULL,
+  KEY `idx` (`f2`(50))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL,
+f2 VARCHAR(100),
+INDEX idx(f2(100)))ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200), ADD INDEX idx1(f1);
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+1
+SELECT @idx1_id = @idx_id;
+@idx1_id = @idx_id
+1
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(200) DEFAULT NULL,
+  KEY `idx` (`f2`),
+  KEY `idx1` (`f1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL,
+f2 VARCHAR(100),
+INDEX idx(f2(10)))ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200), DROP INDEX idx, ADD INDEX idx(f2(6));
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+1
+SELECT @idx1_id = @idx_id;
+@idx1_id = @idx_id
+0
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(200) DEFAULT NULL,
+  KEY `idx` (`f2`(6))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL,
+f2 VARCHAR(100),
+INDEX idx(f2))ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200), ADD COLUMN f3 INT;
+CALL get_table_id("test/t1", @tbl1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+0
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(200) DEFAULT NULL,
+  `f3` int(11) DEFAULT NULL,
+  KEY `idx` (`f2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL, f2 VARCHAR(100) PRIMARY KEY)ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200), ADD COLUMN f3 INT;
+CALL get_table_id("test/t1", @tbl1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+0
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(200) NOT NULL,
+  `f3` int(11) DEFAULT NULL,
+  PRIMARY KEY (`f2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL, f2 VARCHAR(100))ENGINE=INNODB;
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200), ADD FULLTEXT idx(f2);
+Warnings:
+Warning	124	InnoDB rebuilding table to add column FTS_DOC_ID
+CALL get_table_id("test/t1", @tbl1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+0
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(200) DEFAULT NULL,
+  FULLTEXT KEY `idx` (`f2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL,
+f2 CHAR(100) PRIMARY KEY)ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 CHAR(200);
+CALL get_table_id("test/t1", @tbl1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+0
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` char(200) NOT NULL,
+  PRIMARY KEY (`f2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL,
+f2 VARCHAR(100),
+INDEX idx(f2(10)),
+INDEX idx1(f1))ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(50);
+CALL get_table_id("test/t1", @tbl1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+0
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(50) DEFAULT NULL,
+  KEY `idx` (`f2`(10)),
+  KEY `idx1` (`f1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL,
+f2 VARCHAR(100),
+INDEX idx(f2(10)),
+INDEX idx1(f1))ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(5), DROP INDEX idx1;
+CALL get_table_id("test/t1", @tbl1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+0
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(5) DEFAULT NULL,
+  KEY `idx` (`f2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL,
+f2 VARCHAR(100), FULLTEXT idx(f2))ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(50);
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+0
+SELECT @idx1_id = @idx_id;
+@idx1_id = @idx_id
+0
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(50) DEFAULT NULL,
+  FULLTEXT KEY `idx` (`f2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL,
+f2 VARCHAR(100),
+INDEX idx(f2))ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 CHAR(200);
+CALL get_table_id("test/t1", @tbl1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+0
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` char(200) DEFAULT NULL,
+  KEY `idx` (`f2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL,
+f2 VARCHAR(100),
+INDEX idx(f2(40)))ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 TEXT;
+CALL get_table_id("test/t1", @tbl1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+0
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` text DEFAULT NULL,
+  KEY `idx` (`f2`(40))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL,
+f2 VARCHAR(100),
+INDEX idx(f2(40)))ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(300);
+CALL get_table_id("test/t1", @tbl1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+0
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(300) DEFAULT NULL,
+  KEY `idx` (`f2`(40))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL,
+f2 VARCHAR(100),
+INDEX idx(f2(40)))ENGINE=InnoDB;
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200) CHARACTER SET UTF16;
+CALL get_table_id("test/t1", @tbl1_id);
+SELECT @tbl1_id = @tbl_id;
+@tbl1_id = @tbl_id
+0
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(200) CHARACTER SET utf16 DEFAULT NULL,
+  KEY `idx` (`f2`(40))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1(f1 INT NOT NULL,
+f2 VARCHAR(100),
+f3 VARCHAR(50) as (f2) VIRTUAL,
+INDEX idx(f3))ENGINE=InnoDB;
+# If varchar virtual column extension is allowed in the future then
+#	InnoDB must rebuild the index
+ALTER TABLE t1 MODIFY f3 VARCHAR(100);
+ERROR HY000: This is not yet supported for generated columns
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `f1` int(11) NOT NULL,
+  `f2` varchar(100) DEFAULT NULL,
+  `f3` varchar(50) GENERATED ALWAYS AS (`f2`) VIRTUAL,
+  KEY `idx` (`f3`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t1;
+DROP PROCEDURE get_index_id;
+DROP PROCEDURE get_table_id;
diff --git a/mysql-test/suite/innodb/t/alter_varchar_change.test b/mysql-test/suite/innodb/t/alter_varchar_change.test
new file mode 100644
index 00000000000..f435125e581
--- /dev/null
+++ b/mysql-test/suite/innodb/t/alter_varchar_change.test
@@ -0,0 +1,336 @@
+--source include/have_innodb.inc
+
+DELIMITER |;
+CREATE PROCEDURE get_index_id(IN tbl_id INT, IN idx_name char(100), OUT idx_id INT)
+BEGIN
+SELECT index_id into idx_id FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE
+	NAME=idx_name and TABLE_ID=tbl_id;
+END|
+
+CREATE PROCEDURE get_table_id(IN tbl_name char(100), OUT tbl_id INT)
+BEGIN
+SELECT table_id into tbl_id FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE
+	NAME = tbl_name;
+END|
+
+DELIMITER ;|
+
+SET @tbl_id = 0;
+SET @tbl1_id = 0;
+SET @idx_id = 0;
+SET @idx1_id = 0;
+
+# Table should avoid rebuild for the following varchar change.
+
+CREATE TABLE t1(f1 INT NOT NULL,
+		f2 VARCHAR(100) PRIMARY KEY)ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200);
+CALL get_table_id("test/t1", @tbl1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+# Index should avoid rebuild
+CREATE TABLE t1(f1 INT NOT NULL,
+		f2 VARCHAR(100),
+		INDEX idx(f2))ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200);
+CALL get_table_id("test/t1", @tbl1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL, f2 VARCHAR(100), f3 VARCHAR(100),
+		INDEX idx(f2, f3), index idx1(f3, f2))ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200), MODIFY f3 VARCHAR(150);
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SELECT @idx1_id = @idx_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL, f2 VARCHAR(100),
+		INDEX idx(f2(40)))ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200);
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SELECT @idx1_id = @idx_id;
+
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL,
+		f2 VARCHAR(100), FULLTEXT idx(f2))ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200);
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SELECT @idx1_id = @idx_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL,
+		f2 VARCHAR(100),
+		f3 VARCHAR(50) as (f2) VIRTUAL,
+		INDEX idx(f3))ENGINE=InnoDB;
+
+INSERT INTO t1(f1, f2) VALUES(1, repeat('a', 40));
+
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(100);
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SELECT @idx1_id = @idx_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL,
+		f2 VARCHAR(100),
+		INDEX idx(f2(10)),
+		INDEX idx1(f1))ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200), DROP INDEX idx1;
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SELECT @idx1_id = @idx_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL,
+		f2 VARCHAR(100),
+		INDEX idx(f2(10)))ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200), DROP INDEX idx, ADD INDEX idx(f2(10));
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SELECT @idx1_id = @idx_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL,
+		f2 VARCHAR(100),
+		INDEX idx(f2(10)))ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200), DROP INDEX idx, ADD INDEX idx(f2(50));
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SELECT @idx1_id = @idx_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+# Newly added index should built
+
+CREATE TABLE t1(f1 INT NOT NULL,
+		f2 VARCHAR(100),
+		INDEX idx(f2(100)))ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200), ADD INDEX idx1(f1);
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SELECT @idx1_id = @idx_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL,
+		f2 VARCHAR(100),
+		INDEX idx(f2(10)))ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200), DROP INDEX idx, ADD INDEX idx(f2(6));
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SELECT @idx1_id = @idx_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+# Table should rebuild
+
+CREATE TABLE t1(f1 INT NOT NULL,
+		f2 VARCHAR(100),
+		INDEX idx(f2))ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200), ADD COLUMN f3 INT;
+CALL get_table_id("test/t1", @tbl1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL, f2 VARCHAR(100) PRIMARY KEY)ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200), ADD COLUMN f3 INT;
+CALL get_table_id("test/t1", @tbl1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL, f2 VARCHAR(100))ENGINE=INNODB;
+
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200), ADD FULLTEXT idx(f2);
+CALL get_table_id("test/t1", @tbl1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL,
+		f2 CHAR(100) PRIMARY KEY)ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 CHAR(200);
+CALL get_table_id("test/t1", @tbl1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL,
+		f2 VARCHAR(100),
+		INDEX idx(f2(10)),
+		INDEX idx1(f1))ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(50);
+CALL get_table_id("test/t1", @tbl1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL,
+		f2 VARCHAR(100),
+		INDEX idx(f2(10)),
+		INDEX idx1(f1))ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(5), DROP INDEX idx1;
+CALL get_table_id("test/t1", @tbl1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL,
+		f2 VARCHAR(100), FULLTEXT idx(f2))ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+CALL get_index_id(@tbl_id, "idx", @idx_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(50);
+CALL get_table_id("test/t1", @tbl1_id);
+CALL get_index_id(@tbl1_id, "idx", @idx1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SELECT @idx1_id = @idx_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL,
+		f2 VARCHAR(100),
+		INDEX idx(f2))ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 CHAR(200);
+CALL get_table_id("test/t1", @tbl1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL,
+		f2 VARCHAR(100),
+		INDEX idx(f2(40)))ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 TEXT;
+CALL get_table_id("test/t1", @tbl1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL,
+		f2 VARCHAR(100),
+		INDEX idx(f2(40)))ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(300);
+CALL get_table_id("test/t1", @tbl1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(f1 INT NOT NULL,
+		f2 VARCHAR(100),
+		INDEX idx(f2(40)))ENGINE=InnoDB;
+
+CALL get_table_id("test/t1", @tbl_id);
+ALTER TABLE t1 MODIFY f2 VARCHAR(200) CHARACTER SET UTF16;
+CALL get_table_id("test/t1", @tbl1_id);
+
+SELECT @tbl1_id = @tbl_id;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+# Show error when virtual varchar column got changed
+
+CREATE TABLE t1(f1 INT NOT NULL,
+		f2 VARCHAR(100),
+		f3 VARCHAR(50) as (f2) VIRTUAL,
+		INDEX idx(f3))ENGINE=InnoDB;
+
+--echo # If varchar virtual column extension is allowed in the future then
+--echo #	InnoDB must rebuild the index
+
+--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
+ALTER TABLE t1 MODIFY f3 VARCHAR(100);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+DROP PROCEDURE get_index_id;
+DROP PROCEDURE get_table_id;