When MySQL 5.7 introduced indexed virtual columns in InnoDB, it added many limitations around ALTER TABLE…ALGORITHM=INPLACE. One of them is that altering or adding virtual columns is not supported when the same ALTER TABLE statement is also adding, dropping or reordering stored columns. MariaDB 10.2 inherited these limitations in
MDEV-5800 and related work.
MDEV-15562 the right thing would be to allow any combination of ADD/DROP/reorder of virtual and stored columns. (Only the ADD of virtual persistent aka generated stored columns cannot possibly be instantaneous.)
I ran into trouble with the following in the test gcol.innodb_virtual_basic, around line 541:
The problem is that instant ADD of persistent columns (
MDEV-11369) would modify the data dictionary cache already before commit, while adding virtual columns uses a different approach, deferring modifications of the table to the commit time.
In this case, we should be able to avoid a table rebuild, and instantly add both columns, then generate the values for the stored column.
Furthermore, there is the function check_v_col_in_order() whose sole purpose is to refuse operations that would change the order of virtual columns. This function should be removed and the code be fixed.
Last but not least, the function ha_innobase::commit_inplace_alter_table() is evicting and reloading the table definition if any virtual columns were added or dropped. This code does not appear to work for partitioned tables (or it is only operating on the first partition or subpartition of the table). It is causing an expensive operation of emptying the adaptive hash index. The purpose of the exercise seems to be to invoke dict_load_column_low(), which is the only place where num_base for virtual columns is assigned to nonzero. It would be better to update this field directly in the data dictionary cache.