MDEV-5800 InnoDB support for indexed vcols

  * remove old 5.2+ InnoDB support for virtual columns
  * enable corresponding parts of the innodb-5.7 sources
  * copy corresponding test cases from 5.7
  * copy detailed Alter_inplace_info::HA_ALTER_FLAGS flags from 5.7
     - and more detailed detection of changes in fill_alter_inplace_info()
  * more "innodb compatibility hooks" in sql_class.cc to
     - create/destroy/reset a THD (used by background purge threads)
     - find a prelocked table by name
     - open a table (from a background purge thread)

  * different from 5.7:
    - new service thread "thd_destructor_proxy" to make sure all THDs are
      destroyed at the correct point in time during the server shutdown
    - proper opening/closing of tables for vcol evaluations in
       + FK checks (use already opened prelocked tables)
       + purge threads (open the table, MDLock it, add it to tdc, close
         when not needed)
    - cache open tables in vc_templ
    - avoid unnecessary allocations, reuse table->record[0] and table->s->default_values
    - not needed in 5.7, because it overcalculates:
      + tell the server to calculate vcols for an on-going inline ADD INDEX
      + calculate vcols for correct error messages

  * update other engines (mroonga/tokudb) accordingly

1 files changed, 1474 insertions, 0 deletions

diff --git a/mysql-test/suite/gcol/t/innodb_virtual_basic.test b/mysql-test/suite/gcol/t/innodb_virtual_basic.test
new file mode 100644
index 00000000000..342d9e42976
--- /dev/null
+++ b/mysql-test/suite/gcol/t/innodb_virtual_basic.test
@@ -0,0 +1,1474 @@
+--source include/have_innodb.inc
+--source include/have_partition.inc
+
+call mtr.add_suppression("\\[Warning\\] InnoDB: Compute virtual");
+
+set default_storage_engine=innodb;
+
+CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a), c CHAR(10), d CHAR(20), e CHAR(10) GENERATED ALWAYS AS (c), g INT);
+INSERT INTO t VALUES(10, DEFAULT, "aa", "bb", DEFAULT, 20);
+INSERT INTO t VALUES(11, DEFAULT, "jj", "kk", DEFAULT, 21);
+
+CREATE INDEX idx ON t(e) algorithm=inplace;
+INSERT INTO t VALUES(12, DEFAULT, 'mm', "nn", DEFAULT, 22);
+
+SELECT e FROM t;
+
+DROP TABLE t;
+
+CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
+
+INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
+INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
+INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
+INSERT INTO t VALUES (null, null, DEFAULT, 'mm');
+
+CREATE INDEX idx ON t(c);
+SELECT c FROM t;
+
+UPDATE t SET a = 10 WHERE a = 11;
+SELECT c FROM t;
+
+SELECT * FROM t;
+
+DELETE FROM t WHERE a = 18;
+
+SELECT c FROM t;
+
+START TRANSACTION;
+
+INSERT INTO t VALUES (128, 22, DEFAULT, "xx");
+INSERT INTO t VALUES (1290, 212, DEFAULT, "xmx");
+ROLLBACK;
+
+SELECT c FROM t;
+SELECT * FROM t;
+
+DROP TABLE t;
+
+CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT  GENERATED ALWAYS AS(b + j), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p));
+
+INSERT INTO t VALUES(11, 22, DEFAULT, "AAA", 8, DEFAULT, "XXX", DEFAULT);
+INSERT INTO t VALUES(1, 2, DEFAULT, "uuu", 9, DEFAULT, "uu", DEFAULT);
+INSERT INTO t VALUES(3, 4, DEFAULT, "uooo", 1, DEFAULT, "umm", DEFAULT);
+
+SELECT c FROM t;
+SELECT m FROM t;
+SELECT p FROM t;
+SELECT * FROM t;
+
+update t set a = 13 where a =11;
+
+delete from t where a =13;
+
+DROP INDEX idx1 ON t;
+DROP INDEX idx2 ON t;
+DROP TABLE t;
+
+/* Test large BLOB data */
+CREATE TABLE `t` (
+  `a` BLOB,
+  `b` BLOB,
+  `c` BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
+  `h` VARCHAR(10) DEFAULT NULL
+) ENGINE=InnoDB;
+
+INSERT INTO t VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, "kk");
+
+CREATE INDEX idx ON t(c(100));
+
+SELECT length(c) FROM t;
+
+START TRANSACTION;
+
+INSERT INTO t VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm');
+
+ROLLBACK;
+
+INSERT INTO t VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm');
+
+START TRANSACTION;
+
+UPDATE t SET a = REPEAT('m', 16000) WHERE a like "aaa%";
+
+ROLLBACK;
+
+# This SELECT did not give correct answer, even though InnoDB return
+# all qualified rows
+SELECT COUNT(*) FROM t WHERE c like "aaa%";
+
+DROP TABLE t;
+
+CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
+
+INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
+INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
+INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
+CREATE INDEX idx ON t(c);
+
+START TRANSACTION;
+
+UPDATE t SET a = 100 WHERE a = 11;
+
+UPDATE t SET a =22 WHERE a = 18;
+
+UPDATE t SET a = 33 WHERE a = 22;
+
+SELECT c FROM t;
+
+ROLLBACK;
+
+SELECT c FROM t;
+DROP TABLE t;
+
+CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
+
+INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
+INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
+INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
+CREATE INDEX idx ON t(c);
+SELECT c FROM t;
+
+connect(con1,localhost,root,,test);
+START TRANSACTION;
+SELECT c FROM t;
+
+connection default;
+UPDATE t SET a = 19 WHERE a = 11;
+
+# this should report the same value as previous one (14, 19, 29).
+connection con1;
+SELECT c FROM t;
+
+ROLLBACK;
+
+SELECT c FROM t;
+
+connection default;
+disconnect con1;
+
+DROP TABLE t;
+
+# CREATE a more complex TABLE
+CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT  GENERATED ALWAYS AS(b + x), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, y)), x INT, y CHAR(20), z INT, INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p));
+
+INSERT INTO t VALUES(1, 2, DEFAULT, "hhh", 3, DEFAULT, "nnn", DEFAULT, 4, "yyy", 5);
+
+INSERT INTO t VALUES(2, 3, DEFAULT, "hhha", 4, DEFAULT, "nnna", DEFAULT, 5, "yyya", 6);
+
+INSERT INTO t VALUES(12, 13, DEFAULT, "hhhb", 14, DEFAULT, "nnnb", DEFAULT, 15, "yyyb", 16);
+
+# CREATE an INDEX ON multiple virtual COLUMN
+CREATE INDEX idx6 ON t(p, c);
+
+SELECT p, c FROM t;
+
+START TRANSACTION;
+INSERT INTO t VALUES(32, 33, DEFAULT, "hhhb", 34, DEFAULT, "nnnb", DEFAULT, 35, "yyyb", 36);
+ROLLBACK;
+
+UPDATE t SET a = 100 WHERE a = 1;
+
+START TRANSACTION;
+UPDATE t SET a = 1 WHERE a = 100;
+ROLLBACK;
+
+DROP TABLE t;
+
+CREATE TABLE t1(a INT);
+ALTER TABLE t1 add COLUMN (b INT generated always as (a+1) virtual, c INT as(5) virtual);
+ALTER TABLE t1 add COLUMN (d INT generated always as (a+1) virtual, e INT as(5) virtual);
+
+SELECT pos, base_pos FROM informatiON_schema.innodb_sys_virtual;
+
+#--error ER_UNSUPPORTED_ACTION_ON_VIRTUAL_COLUMN
+ALTER TABLE t1 add COLUMN (f INT generated always as (a+1) virtual, g INT as(5) virtual), DROP COLUMN e;
+
+SELECT pos, base_pos FROM informatiON_schema.innodb_sys_virtual;
+
+DROP TABLE t1;
+
+CREATE TABLE t1(a INT);
+INSERT INTO t1 VALUES(1);
+
+ALTER TABLE t1 add COLUMN (f INT generated always as (a+1) virtual, g INT );
+
+# Inplace ADD/DROP virtual COLUMNs can only go with their own for
+# inplace algorithm, not to combine with other operations, except create index
+ALTER TABLE t1 add COLUMN (h INT generated always as (a+1) virtual), add INDEX idx (h), algorithm=inplace;
+
+--enable_info
+ALTER TABLE t1 add COLUMN (h1 INT generated always as (a+1) virtual), add INDEX idx1 (h1);
+--disable_info
+
+ALTER TABLE t1 DROP COLUMN h1, DROP INDEX idx;
+
+DROP TABLE t1;
+
+# Virtual COLUMN cannot be INDEXed
+CREATE TABLE t1(a INT);
+CREATE INDEX idx ON t1(a);
+CREATE TABLE t3(a INT, b INT , INDEX(b), CONSTRAINT x FOREIGN KEY(b) REFERENCES t1(a));
+--error ER_CANT_CREATE_TABLE
+CREATE TABLE t2(a INT, b INT generated always as (a+1) virtual, INDEX(b), CONSTRAINT x FOREIGN KEY(b) REFERENCES t1(a));
+CREATE TABLE t2(a INT, b INT generated always as (a+1) virtual, INDEX(b));
+DROP TABLE t3;
+DROP TABLE t2;
+DROP TABLE t1;
+
+CREATE TABLE t1(a INT);
+ALTER TABLE t1 add COLUMN (b INT generated always as (a+1) virtual, c INT as(5) virtual);
+
+ALTER TABLE t1 change b x INT generated always as (a+1) virtual;
+
+SELECT pos, base_pos FROM informatiON_schema.innodb_sys_virtual;
+
+DROP TABLE t1;
+
+# We do not support Fulltext or Spatial INDEX ON Virtual Columns
+--error ER_UNSUPPORTED_ACTION_ON_VIRTUAL_COLUMN
+CREATE TABLE t (a TEXT, b TEXT GENERATED ALWAYS AS (a), fulltext INDEX idx (b));
+CREATE TABLE t (a TEXT, b TEXT GENERATED ALWAYS AS (a));
+--error ER_UNSUPPORTED_ACTION_ON_VIRTUAL_COLUMN
+ALTER TABLE t ADD FULLTEXT INDEX (b);
+DROP TABLE t;
+
+--error ER_SPATIAL_CANT_HAVE_NULL
+CREATE TABLE t (a geometry not null, b geometry GENERATED ALWAYS AS (a), spatial INDEX idx (b));
+CREATE TABLE t (a geometry not null, b geometry GENERATED ALWAYS AS (a));
+--error ER_SPATIAL_CANT_HAVE_NULL
+ALTER TABLE t ADD SPATIAL INDEX (b);
+DROP TABLE t;
+
+#test DEFAULT value
+CREATE TABLE t (a INT DEFAULT 1, b INT DEFAULT 2, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
+CREATE INDEX idx ON t(c);
+INSERT INTO t(h)VALUES ('mm');
+SELECT c FROM t;
+
+CREATE unique INDEX idx1 ON t(c);
+
+--error ER_DUP_ENTRY
+INSERT INTO t(h)VALUES ('mm');
+
+DROP TABLE t;
+
+CREATE TABLE `t1` ( `a` INT(11) DEFAULT NULL,   `b` INT(11) DEFAULT NULL,   `c` INT(11) GENERATED ALWAYS AS (a+b) VIRTUAL,   `x` INT(11) NOT NULL,   `h` VARCHAR(10) DEFAULT NULL,   PRIMARY KEY (`x`),   KEY `idx` (`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+INSERT INTO t1 VALUES (1, 2, DEFAULT, 3, 'mm');
+INSERT INTO t1 VALUES (11, 22, DEFAULT, 23, 'mm');
+
+connect(con1,localhost,root,,test);
+UPDATE t1 SET x = 4 WHERE x =3;
+DROP TABLE t1;
+
+CREATE TABLE `t1` ( `a` INT(11) DEFAULT NULL,   `b` INT(11) DEFAULT NULL,   `c` INT(11) GENERATED ALWAYS AS (a+b) VIRTUAL,   `x` INT(11) NOT NULL,   `h` VARCHAR(10) DEFAULT NULL,   KEY `idx` (`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+INSERT INTO t1 VALUES (1, 2, DEFAULT, 3, 'mm');
+INSERT INTO t1 VALUES (11, 22, DEFAULT, 23, 'mm');
+
+START TRANSACTION;
+SELECT * FROM t1;
+
+connection con1;
+START TRANSACTION;
+UPDATE t1 SET x = 15 WHERE x = 3;
+
+UPDATE t1 SET b = 10 WHERE b=2;
+ROLLBACK;
+
+connection default;
+SELECT c FROM t1;
+
+disconnect con1;
+
+DROP TABLE t1;
+
+CREATE TABLE `t` (
+  `a` INT(11) DEFAULT NULL,
+  `b` INT(11) DEFAULT NULL,
+  `c` INT(11) GENERATED ALWAYS AS (a+b) VIRTUAL,
+  `d` INT(11) GENERATED ALWAYS AS (a) VIRTUAL,
+  `h` INT(11) NOT NULL,
+  PRIMARY KEY (`h`),
+  KEY `idx` (`c`)
+) ENGINE=InnoDB;
+
+INSERT INTO t VALUES (11, 3, DEFAULT, DEFAULT, 1);
+INSERT INTO t VALUES (18, 1, DEFAULT, DEFAULT, 2);
+INSERT INTO t VALUES (28, 1, DEFAULT, DEFAULT, 3);
+INSERT INTO t VALUES (null, null, DEFAULT, DEFAULT, 4);
+
+delimiter |;
+CREATE PROCEDURE UPDATE_t()
+begin
+        DECLARE i INT DEFAULT 1;
+        WHILE (i <= 2000) DO
+		UPDATE t SET a = 100 + i WHERE h = 1;
+                SET i = i + 1;
+        END WHILE;
+END|
+
+CREATE PROCEDURE DELETE_insert_t()
+begin
+        DECLARE i INT DEFAULT 1;
+        WHILE (i <= 2000) DO
+		UPDATE t SET a = 100 + i WHERE h = 1;
+                SET i = i + 1;
+        END WHILE;
+END|
+delimiter ;|
+
+CALL UPDATE_t();
+SELECT c FROM t;
+
+CALL DELETE_insert_t();
+SELECT c FROM t;
+
+DROP INDEX idx ON t;
+CALL UPDATE_t();
+SELECT c FROM t;
+
+DROP PROCEDURE DELETE_insert_t;
+DROP PROCEDURE UPDATE_t;
+
+DROP TABLE t;
+
+--echo # Bug#20767937: WL8149:ASSERTION FAILED IN ROW_UPD_SEC_INDEX_ENTRY
+CREATE TABLE b (
+col_INT_nokey INTEGER NOT NULL,
+col_INT_key INTEGER GENERATED ALWAYS AS (col_INT_nokey) VIRTUAL,
+col_date_nokey DATE,
+col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,
+        INTerval 30 day)) VIRTUAL,
+
+col_datetime_nokey DATETIME NOT NULL,
+col_time_nokey TIME NOT NULL,
+
+col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey,
+      col_time_nokey)),
+col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey,
+      col_time_nokey)),
+
+col_VARCHAR_nokey VARCHAR(1) NOT NULL,
+col_VARCHAR_key VARCHAR(2) GENERATED ALWAYS AS(CONCAT(col_VARCHAR_nokey,
+      col_VARCHAR_nokey)),
+
+KEY (col_INT_key),
+KEY (col_VARCHAR_key),
+KEY (col_date_key),
+KEY (col_time_key),
+KEY (col_datetime_key),
+KEY (col_INT_key, col_VARCHAR_key),
+KEY (col_INT_key, col_VARCHAR_key, col_date_key,
+col_time_key, col_datetime_key)
+);
+INSERT INTO b (
+  col_INT_nokey,
+  col_date_nokey,
+  col_time_nokey,
+  col_datetime_nokey,
+  col_VARCHAR_nokey
+) VALUES
+(0, NULL, '21:22:34.025509', '2002-02-13 17:30:06.013935', 'j'),
+(8, '2004-09-18', '10:50:38.059966', '2008-09-27
+00:34:58.026613', 'v');
+
+EXPLAIN SELECT col_INT_key FROM b;
+SELECT col_INT_key FROM b;
+SELECT col_INT_nokey, col_INT_key FROM b;
+DELETE FROM b;
+
+DROP TABLE b;
+
+let $row_format=COMPACT;
+--source inc/innodb_v_large_col.inc
+
+CREATE TABLE `t` (
+  `a` BLOB,
+  `b` BLOB,
+  `c` BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
+  `d` BLOB GENERATED ALWAYS AS (b) VIRTUAL,
+  `e` INT(11) GENERATED ALWAYS AS (10) VIRTUAL,
+  `h` INT(11) NOT NULL,
+  PRIMARY KEY (`h`)
+) ENGINE=InnoDB;
+
+INSERT INTO t VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, DEFAULT, DEFAULT, 1);
+INSERT INTO t VALUES (REPEAT('a', 32000), REPEAT('b', 11000), DEFAULT, DEFAULT, DEFAULT, 2);
+INSERT INTO t VALUES (REPEAT('m', 18000), REPEAT('n', 46000), DEFAULT, DEFAULT, DEFAULT, 3);
+
+CREATE INDEX idx ON t(c(100), d(20));
+
+UPDATE t SET a = NULL WHERE h=1;
+
+UPDATE t SET a = REPEAT(CAST(1 AS CHAR), 2000) WHERE h = 1;
+
+UPDATE t SET a = REPEAT(CAST(1 AS CHAR), 1000) WHERE h = 1;
+
+delimiter |;
+
+CREATE PROCEDURE UPDATE_t()
+begin
+        DECLARE i INT DEFAULT 1;
+        WHILE (i <= 200) DO
+                UPDATE t SET a = REPEAT(CAST(i AS CHAR), 2000) WHERE h = 1;
+                SET i = i + 1;
+        END WHILE;
+END|
+
+CREATE PROCEDURE DELETE_insert_t()
+begin
+        DECLARE i INT DEFAULT 1;
+        WHILE (i <= 200) DO
+                DELETE FROM t WHERE h = 1;
+                INSERT INTO t VALUES (REPEAT(CAST(i AS CHAR), 2000) ,  REPEAT('b', 2000), DEFAULT, DEFAULT, DEFAULT, 1);
+                SET i = i + 1;
+        END WHILE;
+END|
+delimiter ;|
+
+CALL UPDATE_t();
+CALL DELETE_insert_t();
+
+UPDATE t SET a = NULL WHERE h=1;
+
+DROP PROCEDURE DELETE_insert_t;
+DROP PROCEDURE UPDATE_t;
+DROP TABLE t;
+
+CREATE TABLE `t` (
+  `m1` INT(11) DEFAULT NULL,
+  `m2` INT(11) DEFAULT NULL,
+  `m3` INT(11) GENERATED ALWAYS AS (m1 + m2) VIRTUAL,
+  `m4` INT(11) DEFAULT NULL,
+  `m5` CHAR(10) DEFAULT NULL,
+  `m6` CHAR(12) GENERATED ALWAYS AS (m5) VIRTUAL,
+  `a` VARCHAR(10000) DEFAULT NULL,
+  `b` VARCHAR(3000) DEFAULT NULL,
+  `c` VARCHAR(14000) GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
+  `d` VARCHAR(5000) GENERATED ALWAYS AS (b) VIRTUAL,
+  `e` INT(11) GENERATED ALWAYS AS (10) VIRTUAL,
+  `h` INT(11) NOT NULL,
+  PRIMARY KEY (`h`),
+  KEY `m3` (`m3`),
+  KEY `c` (`c`(100)),
+  KEY `e` (`e`,`d`(20))
+) ENGINE=InnoDB;
+
+INSERT INTO t VALUES (1, 2, DEFAULT, 3, "aaa", DEFAULT, REPEAT('g', 10000), REPEAT('x', 2800), DEFAULT, DEFAULT, DEFAULT, 1);
+
+INSERT INTO t VALUES (11, 21, DEFAULT, 31, "bbb", DEFAULT, REPEAT('a', 9000), REPEAT('b', 2000), DEFAULT, DEFAULT, DEFAULT, 2);
+
+INSERT INTO t VALUES (21, 31, DEFAULT, 41, "zzz", DEFAULT, REPEAT('m', 8000), REPEAT('n', 3000), DEFAULT, DEFAULT, DEFAULT, 3);
+
+ALTER TABLE t DROP COLUMN c;
+
+DELETE FROM t;
+
+DROP TABLE t;
+
+CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
+
+INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
+INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
+INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
+INSERT INTO t VALUES (null, null, DEFAULT, 'mm');
+
+CREATE INDEX idx ON t(a, c);
+SELECT a, c FROM t;
+
+START TRANSACTION;
+
+UPDATE t SET a = 13 where a = 11;
+
+ROLLBACK;
+DELETE FROM t;
+
+DROP TABLE t;
+
+CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), m int);
+
+INSERT INTO t VALUES (11, 3, DEFAULT, "a", 1);
+INSERT INTO t VALUES (18, 1, DEFAULT, "b", 2);
+INSERT INTO t VALUES (28, 1, DEFAULT, "c", 3 );
+INSERT INTO t VALUES (null, null, DEFAULT, "d", 4);
+
+CREATE INDEX idx ON t(a, c, h);
+SELECT a, c FROM t;
+
+START TRANSACTION;
+UPDATE t SET m =10 WHERE m = 1;
+UPDATE t SET h = "e" WHERE h="a";
+ROLLBACK;
+SELECT a, c, h FROM t;
+
+DROP TABLE t;
+
+# bug#21065137 - WL8149:FAILING ASSERTION: NAME_OFS < FULL_LEN 
+CREATE TABLE `t1` (
+  `col1` int(11) NOT NULL,
+  `col2` int(11) NOT NULL,
+  `col3` int(11) NOT NULL,
+  `col4` int(11) DEFAULT NULL,
+  `col5` int(11) GENERATED ALWAYS AS (col2 % col3) VIRTUAL,
+  `col7` int(11) GENERATED ALWAYS AS (col5 * col5) VIRTUAL,
+  `col8` int(11) GENERATED ALWAYS AS (col5 % col5) VIRTUAL,
+  `col9` text,
+  `extra` int(11) DEFAULT NULL,
+  UNIQUE KEY `uidx` (`col5`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+ALTER TABLE t1 CHANGE COLUMN extra col6 INT;
+
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+# No spatial and FTS index on virtual columns
+--error ER_SPATIAL_CANT_HAVE_NULL
+CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a), c point, d point GENERATED ALWAYS AS (c), spatial index idx (d));
+
+--error ER_UNSUPPORTED_ACTION_ON_VIRTUAL_COLUMN
+CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a), c CHAR(10), d char(20) GENERATED ALWAYS AS (c), fulltext index idx (d));
+
+CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT  GENERATED ALWAYS AS(b + j), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p));
+
+INSERT INTO t VALUES(11, 22, DEFAULT, "AAA", 8, DEFAULT, "XXX", DEFAULT);
+INSERT INTO t VALUES(1, 2, DEFAULT, "uuu", 9, DEFAULT, "uu", DEFAULT);
+INSERT INTO t VALUES(3, 4, DEFAULT, "uooo", 1, DEFAULT, "umm", DEFAULT);
+
+alter table t add  x int, add xx int generated ALWAYS AS(x);
+
+DROP TABLE t;
+
+CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT  GENERATED ALWAYS AS(b + j), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p));
+
+INSERT INTO t VALUES(11, 22, DEFAULT, "AAA", 8, DEFAULT, "XXX", DEFAULT);
+INSERT INTO t VALUES(1, 2, DEFAULT, "uuu", 9, DEFAULT, "uu", DEFAULT);
+INSERT INTO t VALUES(3, 4, DEFAULT, "uooo", 1, DEFAULT, "umm", DEFAULT);
+
+ALTER TABLE t DROP COLUMN c, algorithm=inplace;
+ALTER TABLE t DROP COLUMN p, ADD COLUMN s VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), algorithm=inplace;
+
+# This should fail
+#ALTER TABLE t ADD  x INT, DROP COLUMN m, algorithm=inplace;
+SELECT s FROM t;
+
+ALTER TABLE t ADD  x VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), ADD INDEX idx (x), algorithm=inplace;
+DROP TABLE t;
+
+CREATE TABLE `t1` (
+  `col1` int(11) DEFAULT NULL,
+  `col2` int(11) DEFAULT NULL,
+  `col3` int(11) DEFAULT NULL,
+  `col4` int(11) DEFAULT NULL,
+  `col5` int(11) GENERATED ALWAYS AS (col4 * col2) VIRTUAL,
+  `col6` int(11) GENERATED ALWAYS AS (col2 % col4) VIRTUAL,
+  `col7` int(11) GENERATED ALWAYS AS (col5 / col6) VIRTUAL,
+  `col8` int(11) GENERATED ALWAYS AS (col5 + col5) VIRTUAL,
+  `col9` text,
+  `extra` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+ALTER TABLE t1 DROP COLUMN col7;
+
+DROP TABLE t1;
+
+CREATE TABLE t1 (
+  col1 INTEGER NOT NULL,
+  gv_col INTEGER GENERATED ALWAYS AS (col1) VIRTUAL,
+  txt1 TEXT,
+  FULLTEXT INDEX fi(txt1)
+);
+
+select * from t1; 
+
+DROP TABLE t1;
+
+CREATE TABLE t1 (
+  col1 INTEGER NOT NULL,
+  col2 INTEGER NOT NULL,
+  col3 INTEGER DEFAULT NULL,
+  col4 INTEGER DEFAULT NULL,
+  col5 INTEGER DEFAULT NULL,
+  col6 INTEGER DEFAULT NULL,
+  col7 INTEGER DEFAULT NULL,
+  col8 INTEGER DEFAULT NULL,
+  col9 INTEGER DEFAULT NULL,
+  col10 INTEGER DEFAULT NULL,
+  col11 INTEGER DEFAULT NULL,
+  col12 INTEGER DEFAULT NULL,
+  col13 INTEGER DEFAULT NULL,
+  col14 INTEGER DEFAULT NULL,
+  col15 INTEGER DEFAULT NULL,
+  col16 INTEGER DEFAULT NULL,
+  col17 INTEGER DEFAULT NULL,
+  col18 INTEGER DEFAULT NULL,
+  col19 INTEGER DEFAULT NULL,
+  col20 INTEGER DEFAULT NULL,
+  col21 INTEGER DEFAULT NULL,
+  col22 INTEGER DEFAULT NULL,
+  col23 INTEGER DEFAULT NULL,
+  col24 INTEGER DEFAULT NULL,
+  col25 INTEGER DEFAULT NULL,
+  col26 INTEGER DEFAULT NULL,
+  col27 INTEGER DEFAULT NULL,
+  col28 INTEGER DEFAULT NULL,
+  col29 INTEGER DEFAULT NULL,
+  col30 INTEGER DEFAULT NULL,
+  col31 INTEGER DEFAULT NULL,
+  col32 INTEGER DEFAULT NULL,
+  col33 INTEGER DEFAULT NULL,
+  gcol1 INTEGER GENERATED ALWAYS AS (col1 + col2) VIRTUAL,
+  KEY idx1 (gcol1)
+);
+
+INSERT INTO t1 (col1, col2)
+  VALUES (0,1), (1,2), (2,3), (3,4), (4,5);
+
+SELECT gcol1 FROM t1 FORCE INDEX(idx1);
+
+ALTER TABLE t1 ADD COLUMN extra INTEGER;
+
+SELECT gcol1 FROM t1 FORCE INDEX(idx1);
+
+DROP TABLE t1;
+
+CREATE TABLE t1 (
+    id INT NOT NULL,
+    store_id INT NOT NULL,
+    x INT GENERATED ALWAYS AS (id + store_id)
+)
+PARTITION BY RANGE (store_id) (
+    PARTITION p0 VALUES LESS THAN (6),
+    PARTITION p1 VALUES LESS THAN (11),
+    PARTITION p2 VALUES LESS THAN (16),
+    PARTITION p3 VALUES LESS THAN (21)
+);
+
+INSERT INTO t1 VALUES(1, 2, default);
+INSERT INTO t1 VALUES(3, 4, default);
+
+INSERT INTO t1 VALUES(3, 12, default);
+INSERT INTO t1 VALUES(4, 18, default);
+
+CREATE INDEX idx ON t1(x);
+
+SELECT x FROM t1;
+
+DROP TABLE t1;
+
+CREATE TABLE t1 (
+    id INT NOT NULL,
+    store_id INT NOT NULL,
+    x INT GENERATED ALWAYS AS (id + store_id)
+)
+PARTITION BY RANGE (x) (
+    PARTITION p0 VALUES LESS THAN (6),
+    PARTITION p1 VALUES LESS THAN (11),
+    PARTITION p2 VALUES LESS THAN (16),
+    PARTITION p3 VALUES LESS THAN (21)
+);
+
+insert into t1 values(1, 2, default);
+insert into t1 values(3, 4, default);
+
+insert into t1 values(3, 12, default);
+--error ER_NO_PARTITION_FOR_GIVEN_VALUE
+insert into t1 values(4, 18, default);
+
+CREATE INDEX idx ON t1(x);
+SELECT x FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a INT, b INT GENERATED ALWAYS AS (a+1) ,c int) PARTITION BY RANGE (b) (
+PARTITION p0 VALUES LESS THAN (6),
+PARTITION p1 VALUES LESS THAN (11),
+PARTITION p2 VALUES LESS THAN (16),
+PARTITION p3 VALUES LESS THAN (21) );
+
+INSERT INTO t1 VALUES (10,DEFAULT,2);
+INSERT INTO t1 VALUES (19,DEFAULT,8);
+
+CREATE INDEX idx ON t1 (b);
+
+INSERT INTO t1 VALUES (5,DEFAULT,9);
+
+SELECT * FROM t1;
+
+ALTER TABLE t1 REMOVE PARTITIONING;
+
+DROP TABLE t1;
+
+CREATE TABLE `t#P#1` (a INT, bt INT GENERATED ALWAYS AS (a+1) ,c int)
+PARTITION BY RANGE (bt)
+subpartition by hash (bt)
+ (
+    PARTITION p0 VALUES LESS THAN (6) (
+        SUBPARTITION s0,
+        SUBPARTITION s1),
+    PARTITION p1 VALUES LESS THAN (11) (
+        SUBPARTITION s2,
+        SUBPARTITION s3),
+    PARTITION p2 VALUES LESS THAN (16) (
+        SUBPARTITION s4,
+        SUBPARTITION s5),
+    PARTITION p3 VALUES LESS THAN (21) (
+        SUBPARTITION s6,
+        SUBPARTITION s7)
+ );
+insert into `t#P#1` values (10,DEFAULT,2);
+insert into `t#P#1` values (19,DEFAULT,8);
+create index idx on `t#P#1` (bt);
+insert into `t#P#1` values (5,DEFAULT,9);
+select * from `t#P#1`;
+alter table `t#P#1` remove partitioning;
+drop table `t#P#1`;
+
+let $row_format=DYNAMIC;
+--source inc/innodb_v_large_col.inc
+
+let $row_format=REDUNDANT;
+--source inc/innodb_v_large_col.inc
+
+let $row_format=COMPRESSED;
+--source inc/innodb_v_large_col.inc
+
+# Make sure FTS_DOC_ID for FULLTEXT index set with correct column id with
+# virtual columns
+CREATE TABLE t(a TEXT CHARSET UTF8)ENGINE=INNODB;
+ALTER TABLE t ADD COLUMN b BLOB GENERATED ALWAYS AS (a) VIRTUAL ;
+ALTER TABLE t ADD FULLTEXT INDEX (a) ;
+ALTER TABLE t ADD INDEX (b(1)) ;
+
+DROP TABLE t;
+
+CREATE TABLE t(a TEXT CHARSET UTF8, FULLTEXT INDEX(a))ENGINE=INNODB;
+ALTER TABLE t ADD COLUMN b BLOB GENERATED ALWAYS AS (a) VIRTUAL ;
+ALTER TABLE t ADD INDEX (b(1)) ;
+DROP TABLE t;
+
+# Virtual column cannot be used as DOC ID column for FULLTEXT index
+CREATE TABLE t(a TEXT CHARSET UTF8)ENGINE=INNODB;
+ALTER TABLE t ADD COLUMN FTS_DOC_ID BLOB GENERATED ALWAYS AS (a) VIRTUAL ;
+--error ER_INNODB_FT_WRONG_DOCID_COLUMN
+ALTER TABLE t ADD FULLTEXT INDEX (a) ;
+DROP TABLE t;
+
+# Test uses ICP on column h and d
+create table t (a int,b int,c int,d int,e int,
+f int generated always as (a+b) virtual,
+g int,h blob,i int,unique key (d,h(25))) engine=innodb;
+
+select h from t where d is null;
+drop table t;
+
+# Test Add virtual column of MySQL long true type
+create table t(a blob not null) engine=innodb;
+alter table t add column b int;
+alter table t add column c varbinary (1000) generated always as (a) virtual;
+alter table t add unique index (c(39));
+replace into t set a = 'a',b =1;
+replace into t set a = 'a',b =1;
+drop table t;
+
+CREATE TABLE t (a INT, b INT,  h VARCHAR(10));
+INSERT INTO t VALUES (12, 3,  "ss");
+INSERT INTO t VALUES (13, 4,  "ss");
+INSERT INTO t VALUES (14, 0,  "ss");
+alter table t add  c INT GENERATED ALWAYS AS(a/b);
+create index idx on t(c);
+DROP TABLE t;
+
+CREATE TABLE t (
+  pk INTEGER AUTO_INCREMENT,
+  col_int_nokey INTEGER /*! NULL */,
+  col_int INT GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) STORED,
+  col_int_key INTEGER GENERATED ALWAYS AS (col_int + col_int_nokey) VIRTUAL,
+  col_date_nokey DATE /*! NULL */,
+  col_date DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) STORED,
+  col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date,interval 30 day)) VIRTUAL,
+  col_datetime_nokey DATETIME /*! NULL */,
+  col_time_nokey TIME /*! NULL */,
+  col_datetime DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) STORED,
+  col_time TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) STORED,
+  col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime, col_time_nokey)) VIRTUAL,
+  col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time)) VIRTUAL,
+  col_varchar_nokey VARCHAR(1) /*! NULL */,
+  col_varchar VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey,col_varchar_nokey)) STORED,
+  col_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar, 'x')) VIRTUAL,
+  unique KEY (pk,col_int_key),
+  KEY(col_int),
+  KEY(col_date),
+  KEY(col_datetime),
+  KEY(col_time),
+  KEY(col_varchar),
+  UNIQUE KEY (col_int_key),
+  KEY (col_time_key),
+  KEY (col_datetime_key),
+  UNIQUE KEY (col_int_key, col_varchar_key),
+  KEY (col_int_key, col_int_nokey),
+  KEY(col_int_key,col_date_key),
+  KEY(col_int_key, col_time_key),
+  KEY(col_int_key, col_datetime_key),
+  KEY(col_date_key,col_time_key,col_datetime_key),
+  KEY (col_varchar_key, col_varchar_nokey),
+  UNIQUE KEY (col_int_key, col_varchar_key, col_date_key, col_time_key, col_datetime_key)
+)  AUTO_INCREMENT=10 ENGINE=INNODB PARTITION BY KEY(col_int_key) PARTITIONS 3;
+
+ALTER TABLE t DROP COLUMN `pk`;
+
+SHOW CREATE TABLE t;
+
+DROP TABLE t;
+
+CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
+
+INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
+INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
+INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
+INSERT INTO t VALUES (null, null, DEFAULT, 'mm');
+
+--error ER_ALTER_OPERATION_NOT_SUPPORTED
+ALTER TABLE t ADD COLUMN xs INT GENERATED ALWAYS AS(a+b), ADD COLUMN mm INT
+GENERATED ALWAYS AS(a+b) STORED, ALGORITHM = INPLACE;
+
+ALTER TABLE t ADD COLUMN x INT GENERATED ALWAYS AS(a+b), ALGORITHM = INPLACE;
+
+ALTER TABLE t DROP COLUMN x,  ALGORITHM = INPLACE;
+
+ALTER TABLE t ADD COLUMN x1 INT GENERATED ALWAYS AS(a+b), DROP COLUMN c,
+ALGORITHM = INPLACE;
+
+DROP TABLE t;
+
+if (0) {
+# Some test on virtual/stored column numbering for spatial indexing
+CREATE TABLE `t` (
+  `a` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `b` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `c` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `d` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `e` POINT GENERATED ALWAYS AS (1) STORED
+) ENGINE=INNODB;
+ALTER TABLE t ADD SPATIAL INDEX (`e`);
+DROP TABLE t;
+CREATE TABLE `t` (
+  `a` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `b` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `c` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `d` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `e` POINT GENERATED ALWAYS AS (1) STORED,
+   SPATIAL INDEX (`e`)
+) ENGINE=INNODB;
+DROP TABLE t;
+CREATE TABLE `t` (
+  `a` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `b` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `c` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `d` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `e2` POINT GENERATED ALWAYS AS (1) STORED,
+  `e` POINT GENERATED ALWAYS AS (1) STORED
+) ENGINE=INNODB;
+ALTER TABLE t ADD SPATIAL INDEX (`e`);
+DROP TABLE t;
+CREATE TABLE `t` (
+  `a` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `b` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `c` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `d` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `e2` POINT GENERATED ALWAYS AS (1) STORED,
+  `d2` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `e` POINT GENERATED ALWAYS AS (1) STORED
+) ENGINE=INNODB;
+ALTER TABLE t ADD SPATIAL INDEX (`e`);
+DROP TABLE t;
+CREATE TABLE `t` (
+  `a` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `b` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `c` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `d` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `e2` POINT GENERATED ALWAYS AS (1) STORED,
+  `d2` INT GENERATED ALWAYS AS (1) VIRTUAL,
+  `e` int
+) ENGINE=INNODB;
+ALTER TABLE t ADD INDEX (`e`);
+DROP TABLE t;
+}
+
+CREATE TABLE t (a INT GENERATED ALWAYS AS(1) VIRTUAL,KEY(a)) ENGINE=INNODB;
+INSERT INTO t VALUES(default);
+SELECT a FROM t FOR UPDATE;
+DROP TABLE t;
+
+# Test add virtual column and add index at the same time
+CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
+
+INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
+
+INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
+
+INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
+
+INSERT INTO t VALUES (null, null, DEFAULT, 'mm');
+
+--enable_info
+ALTER TABLE t ADD COLUMN x INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (x);
+--disable_info
+
+SELECT x FROM t;
+
+DROP TABLE t;
+
+CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
+
+INSERT INTO t1 VALUES (11, 3, DEFAULT, 'mm');
+
+INSERT INTO t1 VALUES (18, 1, DEFAULT, 'mm');
+
+INSERT INTO t1 VALUES (28, 1, DEFAULT, 'mm');
+
+ALTER TABLE t1 ADD INDEX idx12 (c) , FORCE, LOCK=NONE;
+ALTER TABLE t1 DROP COLUMN h,  ADD INDEX idx (c) , FORCE, LOCK=NONE;
+
+DROP TABLE t1 ;
+
+# Check ALTER TABLE CHANGE VIRTUAL COLUMN TYPE and ORDER
+CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), d INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
+
+INSERT INTO t1 VALUES (11, 3, DEFAULT, DEFAULT, 'mm');
+
+INSERT INTO t1 VALUES (18, 1, DEFAULT, DEFAULT, 'mm');
+
+INSERT INTO t1 VALUES (28, 1, DEFAULT, DEFAULT, 'mm');
+
+--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
+ALTER TABLE t1 CHANGE  d d INT GENERATED ALWAYS AS(a+b) FIRST, ALGORITHM = INPLACE;
+
+# Change column type is not allow for inplace also
+--error ER_ALTER_OPERATION_NOT_SUPPORTED
+ALTER TABLE t1 CHANGE  d d VARCHAR(10) GENERATED ALWAYS AS(h), ALGORITHM = INPLACE;
+
+ALTER TABLE t1 CHANGE  d d INT GENERATED ALWAYS AS(a+b) FIRST;
+
+SHOW CREATE TABLE t1;
+
+DROP TABLE t1;
+
+# Test foreign key which could be a base column of indexed virtual column
+CREATE TABLE parent (id INT PRIMARY KEY) ENGINE=INNODB;
+
+CREATE TABLE child (
+    id INT,
+    parent_id INT,
+    x int(11) GENERATED ALWAYS AS (parent_id+1),
+    INDEX par_ind (parent_id),
+    FOREIGN KEY (parent_id)
+        REFERENCES parent(id)
+        ON DELETE CASCADE
+) ENGINE=INNODB;
+
+ALTER TABLE child ADD INDEX `i1` (x);
+
+# If foreign constrain does not have cascade clause, or with "no action" clause
+# the index can still be created
+CREATE TABLE child_1 (
+    id INT,
+    parent_id INT,
+    x int(11) GENERATED ALWAYS AS (parent_id+1),
+    INDEX par_ind (parent_id),
+    FOREIGN KEY (parent_id)
+        REFERENCES parent(id)
+) ENGINE=INNODB;
+
+# This should be successful
+ALTER TABLE child_1 ADD INDEX `i1` (x);
+
+DROP TABLE child_1;
+
+DROP TABLE child;
+
+CREATE TABLE child (
+    id INT,
+    parent_id INT,
+    x int(11) GENERATED ALWAYS AS (parent_id+1),
+    INDEX par_ind (parent_id),
+    INDEX i1 (x),
+
+    FOREIGN KEY (parent_id)
+        REFERENCES parent(id)
+        ON DELETE CASCADE
+) ENGINE=INNODB;
+
+DROP TABLE child;
+
+CREATE TABLE child (
+    id INT,
+    parent_id INT,
+    x int(11) GENERATED ALWAYS AS (parent_id+1),
+    INDEX par_ind (parent_id),
+    INDEX `i1` (x)
+) ENGINE=INNODB;
+
+ALTER TABLE child ADD FOREIGN KEY (parent_id)
+REFERENCES parent(id)
+ON DELETE CASCADE;
+
+# Check inplace option
+SET foreign_key_checks = 0;
+
+ALTER TABLE child ADD FOREIGN KEY (parent_id)
+REFERENCES parent(id)
+ON DELETE CASCADE;
+
+ALTER TABLE child ADD FOREIGN KEY (parent_id)
+REFERENCES parent(id)
+ON DELETE SET NULL;
+
+ALTER TABLE child ADD FOREIGN KEY (parent_id)
+REFERENCES parent(id)
+ON UPDATE CASCADE;
+
+# this should be successful
+ALTER TABLE child ADD FOREIGN KEY (parent_id)
+REFERENCES parent(id);
+
+SET foreign_key_checks = 1;
+
+DROP TABLE child;
+
+DROP TABLE parent;
+
+# Test for Bug 21890816 - ASSERT UPDATE->OLD_VROW, VIRTUAL COLUMNS
+CREATE TABLE `ibstd_16` (
+  `a` int(11) DEFAULT NULL,
+  `d` int(11) DEFAULT NULL,
+  `b` varchar(198) DEFAULT NULL,
+  `c` char(179) DEFAULT NULL,
+  `vadcol` int(11) GENERATED ALWAYS AS (a+length(d)) STORED,
+  `vbcol` char(2) GENERATED ALWAYS AS (substr(b,2,2)) VIRTUAL,
+  `vbidxcol` char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL,
+  UNIQUE KEY `b` (`b`(10),`d`),
+  KEY `d` (`d`),
+  KEY `a` (`a`),
+  KEY `c` (`c`(99),`b`(33)),
+  KEY `b_2` (`b`(5),`c`(10),`a`),
+  KEY `vbidxcol` (`vbidxcol`),
+  KEY `a_2` (`a`,`vbidxcol`),
+  KEY `vbidxcol_2` (`vbidxcol`,`d`)
+)  ENGINE=INNODB;
+
+# Block when FK constraint on base column of stored column.
+#--error ER_CANNOT_ADD_FOREIGN
+CREATE TABLE `ibstd_16_fk` (
+  `a` int(11) DEFAULT NULL,
+  `d` int(11) DEFAULT NULL,
+  `b` varchar(198) DEFAULT NULL,
+  `c` char(179) DEFAULT NULL,
+  `vadcol` int(11) GENERATED ALWAYS AS (a+length(d)) STORED,
+  `vbcol` char(2) GENERATED ALWAYS AS (substr(b,2,2)) VIRTUAL,
+  `vbidxcol` char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL,
+  UNIQUE KEY `b` (`b`(10),`a`,`d`),
+  KEY `d` (`d`),
+  KEY `a` (`a`),
+  KEY `c` (`c`(99),`b`(33)),
+  KEY `b_2` (`b`(5),`c`(10),`a`),
+  KEY `vbidxcol` (`vbidxcol`),
+  KEY `a_2` (`a`,`vbidxcol`),
+  KEY `vbidxcol_2` (`vbidxcol`,`d`),
+  CONSTRAINT `fk_16` FOREIGN KEY (`a`) REFERENCES `ibstd_16` (`a`) ON DELETE SET NULL
+) ENGINE=InnoDB;
+DROP TABLE ibstd_16_fk;
+
+# Take out "KEY `a_2` (`a`,`vbidxcol`)", this should then be successful
+CREATE TABLE `ibstd_16_fk` (
+  `a` int(11) DEFAULT NULL,
+  `d` int(11) DEFAULT NULL,
+  `b` varchar(198) DEFAULT NULL,
+  `c` char(179) DEFAULT NULL,
+  `vbcol` char(2) GENERATED ALWAYS AS (substr(b,2,2)) VIRTUAL,
+  `vbidxcol` char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL,
+  UNIQUE KEY `b` (`b`(10),`a`,`d`),
+  KEY `d` (`d`),
+  KEY `a` (`a`),
+  KEY `c` (`c`(99),`b`(33)),
+  KEY `b_2` (`b`(5),`c`(10),`a`),
+  KEY `vbidxcol` (`vbidxcol`),
+  KEY `vbidxcol_2` (`vbidxcol`,`d`),
+  CONSTRAINT `fk_16` FOREIGN KEY (`a`) REFERENCES `ibstd_16` (`a`) ON DELETE SET NULL
+) ENGINE=InnoDB;
+
+ALTER TABLE ibstd_16_fk ADD INDEX `a_2` (`a`,`vbidxcol`);
+
+# Now try to add a table with virtual index, and then add constraint
+DROP TABLE ibstd_16_fk;
+
+# Create a table without constraint
+CREATE TABLE `ibstd_16_fk` (
+  `a` int(11) DEFAULT NULL,
+  `d` int(11) DEFAULT NULL,
+  `b` varchar(198) DEFAULT NULL,
+  `c` char(179) DEFAULT NULL,
+  `vbcol` char(2)  GENERATED ALWAYS AS (substr(b,2,2)) VIRTUAL,
+  `vbidxcol` char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL,
+  UNIQUE KEY `b` (`b`(10),`a`,`d`),
+  KEY `d` (`d`),
+  KEY `a` (`a`),
+  KEY `c` (`c`(99),`b`(33)),
+  KEY `b_2` (`b`(5),`c`(10),`a`),
+  KEY `vbidxcol` (`vbidxcol`),
+  KEY `a_2` (`a`,`vbidxcol`),
+  KEY `vbidxcol_2` (`vbidxcol`,`d`)
+) ENGINE=InnoDB;
+
+ALTER TABLE `ibstd_16_fk` ADD CONSTRAINT `fk_16` FOREIGN KEY (`a`) REFERENCES `ibstd_16` (`a`) ON DELETE SET NULL;
+
+# DROP the index
+DROP INDEX a_2 ON ibstd_16_fk;
+
+INSERT INTO ibstd_16 VALUES (1, 2, "aaa", "bbb", default, default, default);
+INSERT INTO ibstd_16_fk VALUES(1, 3, "mmm", "SSS", default, default);
+
+# Cascading delete/update on column non-related to virtual column or virtual
+# index will be fine
+DELETE FROM ibstd_16 WHERE a = 1;
+
+DROP TABLE ibstd_16_fk;
+DROP TABLE ibstd_16;
+
+# Bug 21941320 - GCOLS: FAILING ASSERTION: N_IDX > 0
+create table t(a int) engine=innodb;
+insert into t set a=1;
+alter table t add column c int generated always as (1) virtual;
+insert into t set a=2;
+
+# Following will cause create index fail, we need to make sure the column
+# ord_part is reset
+--error ER_DUP_ENTRY
+alter table t add unique index(c);
+insert into t set a=1;
+drop table t;
+
+# Bug 21875974 - VCOL : READ OF FREED MEMORY IN DTUPLE_GET_N_FIELDS
+# CAUSE CRASH
+
+create table t (
+  x int,
+  a int generated always as (x) virtual,
+  b int generated always as (1) stored,
+  c int not null,
+  unique (b),
+  unique (a,b)
+) engine=innodb;
+
+insert into t(x, c) values(1, 3);
+
+# This will exercise row_vers_impl_x_locked_low() for virtual columns
+replace into t(x, c) values(1, 0);
+
+drop table t;
+
+# Bug22123674 VCOL:INNODB: FAILING ASSERTION: !UT_STRCMP(NAME,
+# FIELD->FIELD_NAME)
+
+CREATE TABLE t(
+c7c CHAR(1)GENERATED ALWAYS AS (c3) VIRTUAL,
+c1 int(1),
+c2 int(1),
+c3 int(1),
+c4 int(1),
+c5 int(1)GENERATED ALWAYS AS ((c2 - c4)) VIRTUAL,
+UNIQUE KEY c5_9(c5)
+)ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+--error ER_ALTER_OPERATION_NOT_SUPPORTED
+ALTER TABLE t CHANGE COLUMN c5 c5 INT(1) GENERATED ALWAYS AS(c2 -
+c4)VIRTUAL AFTER c3,ALGORITHM=INPLACE;
+
+--error ER_ALTER_OPERATION_NOT_SUPPORTED
+ALTER TABLE t CHANGE COLUMN c7c c7c INT(1) GENERATED ALWAYS AS(c3)
+VIRTUAL AFTER c5,ALGORITHM=INPLACE;
+
+--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
+ALTER TABLE t DROP COLUMN c7c,ADD COLUMN c5c INT GENERATED ALWAYS AS(c4/
+c3)VIRTUAL AFTER c3,ALGORITHM=INPLACE;
+
+DROP TABLE t;
+
+# Bug22111464	VCOL:INNODB: FAILING ASSERTION: I < TABLE->N_DEF
+
+CREATE TABLE `t` (
+  `col1` int(11) DEFAULT NULL,
+  `col2` int(11) DEFAULT NULL,
+  `col4` int(11) DEFAULT NULL,
+  `col5` int(11) GENERATED ALWAYS AS ((`col2` % `col4`)) VIRTUAL,
+  `col6` int(11) GENERATED ALWAYS AS ((`col2` - `col2`)) VIRTUAL,
+  `col5x` int(11) GENERATED ALWAYS AS ((`col1` / `col1`)) VIRTUAL,
+  `col6x` int(11) GENERATED ALWAYS AS ((`col2` / `col4`)) VIRTUAL,
+  `col7x` int(11) GENERATED ALWAYS AS ((`col6` % `col6x`)) VIRTUAL,
+  `col8x` int(11) GENERATED ALWAYS AS ((`col6` / `col6`)) VIRTUAL,
+  `col9` text,
+  `col7c` int(11) GENERATED ALWAYS AS ((`col6x` % `col6x`)) VIRTUAL,
+  `col1b` varchar(20) GENERATED ALWAYS AS (`col1`) VIRTUAL,
+  `col3` int(11) DEFAULT NULL,
+  `col7` int(11) DEFAULT NULL,
+  `col5c` int(11) GENERATED ALWAYS AS ((`col5x` * `col6`)) VIRTUAL,
+  `col6c` varchar(20) GENERATED ALWAYS AS (`col5x`) VIRTUAL,
+  `col3b` bigint(20) GENERATED ALWAYS AS ((`col6x` * `col6`)) VIRTUAL,
+  `col1a` varchar(20) GENERATED ALWAYS AS (`col1`) VIRTUAL,
+  `col8` int(11) DEFAULT NULL,
+  UNIQUE KEY `col5` (`col5`),
+  UNIQUE KEY `col6x` (`col6x`),
+  UNIQUE KEY `col5_2` (`col5`),
+  KEY `idx2` (`col9`(10)),
+  KEY `idx4` (`col2`),
+  KEY `idx8` (`col9`(10),`col5`),
+  KEY `idx9` (`col6`),
+  KEY `idx6` (`col6`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+ALTER TABLE t CHANGE COLUMN col3b col8a BIGINT GENERATED ALWAYS AS
+(col6x * col6) VIRTUAL, ADD UNIQUE KEY uidx ( col8a );
+
+DROP TABLE t;
+
+--echo #
+--echo # Bug 22141031 - GCOLS: PURGED THREAD DIES: TRIED TO PURGE
+--echo # NON-DELETE-MARKED RECORD IN INDEX
+--echo #
+create table t (
+  a int,b int,c text,d int,e int,f int,g int,
+  h text generated always as ('1') virtual,
+  i int,j int,k int,l int,m int,
+  primary key (c(1)),unique key (c(1)),
+  key (i),key (h(1))
+) engine=innodb default charset latin1;
+
+replace into t(c) values ('');
+replace into t(c) values ('');
+alter table t drop column d ;
+
+drop table t;
+
+--echo #
+--echo # Bug 22139917 - ASSERTION: DICT_TABLE_GET_NTH_COL(USER_TABLE, NTH_COL)
+--echo # ->LEN < NEW_LEN
+--echo #
+
+create table t (
+  a int generated always as (1) virtual,
+  b varbinary(1),
+  c varbinary(1) generated always as (b) virtual
+) engine=innodb;
+alter table t change column b b varbinary(2), algorithm=inplace;
+alter table t change column c c varbinary(2) generated always as (b) virtual, algorithm=inplace;
+
+drop table t;
+
+# Bug22202788	GCOL:ASSERTION:0 IN ROW_SEL_GET_CLUST_REC_FOR_MYSQL AT
+# ROW0SEL.CC
+SET @@SESSION.sql_mode=0;
+
+CREATE TABLE t(
+	c1 INT AUTO_INCREMENT,
+	c2 INT,
+	c3 INT GENERATED ALWAYS AS(c2 + c2)VIRTUAL,
+	c3k INT GENERATED ALWAYS AS(c2 + c3)VIRTUAL,
+	c4 DATE,
+	c5 DATE GENERATED ALWAYS AS(DATE_ADD(c4,interval 30 day)) VIRTUAL,
+	c5k DATE GENERATED ALWAYS AS(DATE_ADD(c4,interval 30 day)) VIRTUAL,
+	c5time_gckey DATE,
+	c6 TIME,
+	c5time DATE GENERATED ALWAYS AS(ADDTIME(c5time_gckey,c6)) VIRTUAL,
+	c7 TIME GENERATED ALWAYS AS(ADDTIME(c5time_gckey,c6)) VIRTUAL,
+	c5timek DATE GENERATED ALWAYS AS(ADDTIME(c5time_gckey,c7)) VIRTUAL,
+	c7k TIME GENERATED ALWAYS AS(ADDTIME(c5time,c6)) VIRTUAL,
+	c8 CHAR(10),
+	c9 CHAR(20)GENERATED ALWAYS AS (CONCAT(c8,c8)) VIRTUAL,
+	c9k CHAR(15)GENERATED ALWAYS AS (CONCAT(c8,0)) VIRTUAL,
+	PRIMARY KEY(c1),
+	KEY(c3),
+	KEY(c9(10)),
+	UNIQUE KEY(c9k),
+	UNIQUE KEY(c3k,c9k(5),c5k,c7k,c5timek,c3,c9(5),c5,c7,c5time)
+)ENGINE=INNODB;
+
+--error ER_DUP_ENTRY
+INSERT INTO
+t(c2,c4,c6,c5time_gckey,c8)VALUES(1,0,0,0,0),(0,0,0,0,'ityzg'),(0,0,1,0,'tyzgk
+t'),(0,1,0,1,'yzgktb'),(0,0,0,0,'zgktb'),(0,0,0,0,'gktbkj'),(0,0,0,0,0),(0,0,1
+,0,1),(0,0,0,0,1),(0,0,0,0,'tbkjrkm'),(0,0,0,0,'bkjr'),(0,0,0,0,0),(0,0,0,0,0)
+,(0,0,0,0,'rk'),(0,0,0,0,'kmqmknbtoe'),(1,0,0,0,'mqmknbt'),(0,1,0,0,'qmknb'),(
+0,0,0,0,'mkn'),(0,0,0,0,'knbtoervql'),(0,0,1,0,1),(0,0,0,0,'nbtoerv'),(0,0,0,0
+,'btoerv'),(0,0,1,0,'toer'),(1,0,0,0,0),(0,0,0,0,'ervq'),(0,0,0,0,'rvqlzsvasu'
+),(0,0,0,0,'vqlzs'),(0,0,0,0,0),(0,1,0,0,'lzsvasu'),(0,0,0,0,'zsvasurq');
+
+SELECT
+DISTINCT * FROM t
+FORCE KEY(PRIMARY,c3k,c3,c9k,c9)
+WHERE
+(c9 IS NULL AND (c9=0))
+OR(
+(c9k NOT IN ('ixfq','xfq','New Mexico','fq')OR c9 IS NULL)
+)
+OR(c9 BETWEEN 'hwstqua' AND 'wstquadcji' OR (c9k=0))
+AND(c3 IS NULL OR c3 IN (0,0,0));
+
+drop table t;
+
+#
+# BUG#22082762 RE-ENABLE SUPPORT FOR ADDING VIRTUAL INDEX WHILE DROPPING VIRTUAL COLUMN
+#
+
+CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), d INT
+GENERATED ALWAYS AS(a+b+b), e INT  GENERATED ALWAYS AS(a), h VARCHAR(10));
+
+INSERT INTO t VALUES (11, 3, DEFAULT, DEFAULT, DEFAULT, 'mm');
+INSERT INTO t VALUES (18, 1, DEFAULT, DEFAULT, DEFAULT, 'mm');
+INSERT INTO t VALUES (28, 1, DEFAULT, DEFAULT, DEFAULT, 'mm');
+INSERT INTO t VALUES (null, null, DEFAULT, DEFAULT, DEFAULT, 'mm');
+CREATE INDEX idx ON t(c, d);
+CREATE INDEX idx1 ON t(c);
+CREATE INDEX idx2 ON t(e, c, d);
+
+# This will drop column c, drop index idx1 on column c, and build index
+# idx and idx2, so they become idx(d) and idx2(e, d) respectively.
+ALTER TABLE t DROP COLUMN c, ALGORITHM=INPLACE;
+
+SELECT d FROM t;
+
+SHOW CREATE TABLE t;
+
+# Drop a column, adding a new column and also adding a index on this new column
+# is not allowed for INPLACE algorithm
+--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
+ALTER TABLE t DROP COLUMN d, ADD COLUMN c INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (c), ALGORITHM=INPLACE;
+
+# Add an index on existing column along with dropping a column is allowed
+ALTER TABLE t DROP COLUMN d, ADD COLUMN c INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (e), ALGORITHM=INPLACE, LOCK=NONE;
+SHOW CREATE TABLE t;
+
+# Add an index on existing column along with adding a virtual column and droping a virtual index
+ALTER TABLE t ADD INDEX idx4(c, e), ADD COLUMN x VARCHAR(10) GENERATED ALWAYS AS(h), DROP INDEX idx, ALGORITHM=INPLACE, LOCK=NONE;
+SHOW CREATE TABLE t;
+
+--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
+ALTER TABLE t ADD COLUMN i INT GENERATED ALWAYS AS(a+a+b), ADD COLUMN j INT, ALGORITHM=INPLACE;
+
+# Add an index along with adding a regular column is allowed.
+ALTER TABLE t ADD INDEX (x), ADD COLUMN j INT, ALGORITHM=INPLACE, LOCK=NONE;
+SHOW CREATE TABLE t;
+
+# Online add an index on newly added virtual column is not allowed.
+--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
+ALTER TABLE t ADD COLUMN i INT GENERATED ALWAYS AS(a+a+b), ADD INDEX (i), ALGORITHM=INPLACE, LOCK=NONE;
+
+ALTER TABLE t ADD COLUMN i INT GENERATED ALWAYS AS(a+a+b), ADD INDEX (i), ALGORITHM=INPLACE, LOCK=SHARED;
+SHOW CREATE TABLE t;
+
+SELECT i FROM t;
+
+SELECT * FROM t;
+
+DROP TABLE t;
+
+#
+# BUG#22469459 WRONG VALUES IN ADDED INDEX WHILE DROPPING VIRTUAL COLUMN
+#
+
+# Drop column with existing index on it.
+CREATE TABLE t (
+  a INT,
+  b INT,
+  c INT GENERATED ALWAYS AS(a+b),
+  d INT GENERATED ALWAYS AS(a+b+b),
+  KEY vidx (c, d)
+)ENGINE=INNODB;
+
+INSERT INTO t (a,b) VALUES (0, 0), (1, NULL), (NULL, 2), (NULL, NULL);
+
+SELECT c, d FROM t;
+
+SELECT * FROM t;
+
+ALTER TABLE t DROP COLUMN c, ALGORITHM=INPLACE;
+
+SELECT d FROM t;
+
+SELECT * FROM t;
+
+DROP TABLE t;
+
+# Drop column with a new index.
+CREATE TABLE t (
+  a INT,
+  b INT,
+  c INT GENERATED ALWAYS AS(a+b),
+  d INT GENERATED ALWAYS AS(a+b+b)
+)ENGINE=INNODB;
+
+INSERT INTO t (a,b) VALUES (0, 0), (1, NULL), (NULL, 2), (NULL, NULL);
+
+SELECT * FROM t;
+
+ALTER TABLE t DROP COLUMN c, ADD INDEX vidx(d), ALGORITHM=INPLACE;
+
+SELECT d FROM t;
+
+SELECT * FROM t;
+
+DROP TABLE t;
+
+--echo #
+--echo # Bug #22162200 MEMORY LEAK IN HA_INNOPART_SHARE
+--echo # ::SET_V_TEMPL PARTITIONED ON VIRTUAL COLUMN
+--echo #
+create table t (
+  c tinyint,
+  d longblob generated always as (c) virtual
+) engine=innodb partition by key (c) partitions 2;
+
+select d in(select d from t)from t group by d;
+drop table t;
+
+--echo #
+--echo # BUG#23052231 - ASSERTION FAILURE: ROW0MERGE.CC:2100:ADD_AUTOINC
+--echo # < DICT_TABLE_GET_N_USER_COLS
+--echo #
+CREATE TABLE `t` (
+  `a` int(11) NOT NULL,
+  `d` int(11) NOT NULL,
+  `b` varchar(198) NOT NULL,
+  `c` char(177) DEFAULT NULL,
+  `vadcol` int(11) GENERATED ALWAYS AS ((`a` + length(`d`))) STORED,
+  `vbcol` char(2) GENERATED ALWAYS AS (substr(`b`,2,2)) VIRTUAL,
+  `vbidxcol` char(3) GENERATED ALWAYS AS (substr(`b`,1,3)) VIRTUAL,
+  PRIMARY KEY (`b`(10),`a`,`d`),
+  KEY `d` (`d`),
+  KEY `a` (`a`),
+  KEY `c_renamed` (`c`(99),`b`(35)),
+  KEY `b` (`b`(5),`c`(10),`a`),
+  KEY `vbidxcol` (`vbidxcol`),
+  KEY `a_2` (`a`,`vbidxcol`),
+  KEY `vbidxcol_2` (`vbidxcol`,`d`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+INSERT INTO t values (11, 1, "11", "aa", default, default, default);
+
+ALTER TABLE t ADD COLUMN nc01128 BIGINT  AUTO_INCREMENT NOT NULL, ADD KEY auto_nc01128(nc01128);
+
+DROP TABLE t;
+
+--echo #
+--echo #Bug #22965271 NEEDS REBUILD FOR COLUMN LENGTH CHANGE THAT IS
+--echo #PART OF VIRTUAL INDEX.
+--echo #
+
+CREATE TABLE t1(
+a VARCHAR(45) CHARACTER SET LATIN1,
+b VARCHAR(115) CHARACTER SET UTF8 GENERATED ALWAYS AS ('f1') VIRTUAL,
+UNIQUE KEY (b,a))ENGINE=INNODB;
+INSERT INTO t1(a) VALUES ('');
+ALTER TABLE t1 CHANGE COLUMN a a VARCHAR(85);
+SELECT * FROM t1;
+DROP TABLE t1;