1 files changed, 89 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-virtual-columns2.result b/mysql-test/suite/innodb/r/innodb-virtual-columns2.result
new file mode 100644
index 00000000..57a2fe64
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb-virtual-columns2.result
@@ -0,0 +1,89 @@
+CREATE TABLE t1 ( 
+y YEAR NULL,
+i SMALLINT NULL,
+c VARCHAR(8) NOT NULL DEFAULT '',
+vi SMALLINT AS (i) VIRTUAL,
+INDEX(y)
+) ENGINE=InnoDB;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `y` year(4) DEFAULT NULL,
+  `i` smallint(6) DEFAULT NULL,
+  `c` varchar(8) NOT NULL DEFAULT '',
+  `vi` smallint(6) GENERATED ALWAYS AS (`i`) VIRTUAL,
+  KEY `y` (`y`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
+y	i	c	vi
+INSERT INTO t1 (i) VALUES (1),(2);
+SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
+y	i	c	vi
+DROP TABLE t1;
+CREATE TABLE t1 ( 
+y YEAR NULL,
+i SMALLINT NULL,
+vi SMALLINT AS (i) VIRTUAL,
+INDEX(y)
+) ENGINE=InnoDB;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `y` year(4) DEFAULT NULL,
+  `i` smallint(6) DEFAULT NULL,
+  `vi` smallint(6) GENERATED ALWAYS AS (`i`) VIRTUAL,
+  KEY `y` (`y`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
+y	i	vi
+INSERT INTO t1 (i) VALUES (1),(2);
+SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
+y	i	vi
+DROP TABLE t1;
+CREATE TABLE t1 ( 
+y YEAR NULL,
+i SMALLINT NULL,
+b BLOB NULL,
+vi SMALLINT AS (i) VIRTUAL,
+INDEX(y)
+) ENGINE=InnoDB;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `y` year(4) DEFAULT NULL,
+  `i` smallint(6) DEFAULT NULL,
+  `b` blob DEFAULT NULL,
+  `vi` smallint(6) GENERATED ALWAYS AS (`i`) VIRTUAL,
+  KEY `y` (`y`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
+y	i	b	vi
+INSERT INTO t1 (i) VALUES (1),(2);
+SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
+y	i	b	vi
+DROP TABLE t1;
+#
+# MDEV-23632 ALTER TABLE...ADD KEY creates corrupted index on virtual column
+#
+CREATE TABLE t1(a INT PRIMARY KEY, b INT, g INT GENERATED ALWAYS AS(b)VIRTUAL) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,1,default);
+ALTER TABLE t1 ADD COLUMN c INT;
+ALTER TABLE t1 ADD KEY(g);
+CHECK TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+SELECT g FROM t1 FORCE INDEX (g);
+g
+1
+DROP TABLE t1;
+CREATE TABLE t1(a INT, b INT, g INT GENERATED ALWAYS AS(b)VIRTUAL) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,1,default);
+ALTER TABLE t1 ADD COLUMN c INT PRIMARY KEY;
+ALTER TABLE t1 ADD KEY(g);
+CHECK TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+SELECT g FROM t1 FORCE INDEX (g);
+g
+1
+DROP TABLE t1;