MDEV-23632 ALTER TABLE...ADD KEY creates corrupted index on virtual column

mysql_col_offset was not updated after the new column has been added by an
INSTANT ALTER TABLE -- table data dictionary had been remaining the same.

When the virtual column is added or removed, table was usually evicted and
then reopened, which triggered vcol info rebuild on the next open.

However this also should be done when the usual column is added or removed:
mariadb always stores virtual field at the end of maria record,
so the shift should always happen.

Fix:
expand the eviction condition to the case when usual fields are
added/removed

Note:
this should happen only in the case of !new_clustered:
* When new_clustered is true, a new data dictionary is created, and vcol
metadata is rebuilt in `alter_rebuild_apply_log()`
* We can't do it in `new_clustered` case, because the old table is not yet
subctituted correctly

3 files changed, 49 insertions, 1 deletions

diff --git a/mysql-test/suite/innodb/r/innodb-virtual-columns2.result b/mysql-test/suite/innodb/r/innodb-virtual-columns2.result
index 3574ba72849..99a1c610bd3 100644
--- a/mysql-test/suite/innodb/r/innodb-virtual-columns2.result
+++ b/mysql-test/suite/innodb/r/innodb-virtual-columns2.result
@@ -62,3 +62,28 @@ 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;
diff --git a/mysql-test/suite/innodb/t/innodb-virtual-columns2.test b/mysql-test/suite/innodb/t/innodb-virtual-columns2.test
index 474a6354576..13ecffcc896 100644
--- a/mysql-test/suite/innodb/t/innodb-virtual-columns2.test
+++ b/mysql-test/suite/innodb/t/innodb-virtual-columns2.test
@@ -52,3 +52,23 @@ SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
 INSERT INTO t1 (i) VALUES (1),(2);
 SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE;
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-23632 ALTER TABLE...ADD KEY creates corrupted index on virtual column
+--echo #
+
+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;
+SELECT g FROM t1 FORCE INDEX (g);
+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; # Triggers `new_clustered`
+ALTER TABLE t1 ADD KEY(g);
+CHECK TABLE t1;
+SELECT g FROM t1 FORCE INDEX (g);
+DROP TABLE t1;
diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
index a479a96ce61..a1c11247e66 100644
--- a/storage/innobase/handler/handler0alter.cc
+++ b/storage/innobase/handler/handler0alter.cc
@@ -9899,7 +9899,10 @@ foreign_fail:
 		}
 	}
 
-	if (ctx0->num_to_drop_vcol || ctx0->num_to_add_vcol) {
+	if (ctx0->num_to_drop_vcol || ctx0->num_to_add_vcol
+	    || (ctx0->new_table->n_v_cols && !new_clustered
+		&& (ha_alter_info->alter_info->drop_list.elements
+		    || ha_alter_info->alter_info->create_list.elements))) {
 		DBUG_ASSERT(ctx0->old_table->get_ref_count() == 1);
 		trx_commit_for_mysql(m_prebuilt->trx);