Until MDEV-22361 moves the FOREIGN KEY processing to the SQL layer, InnoDB must be able to evaluate indexed virtual column values when a CASCADE or SET NULL operation would change the values of base columns.
It seems that the system table SYS_VIRTUAL is mostly needed for the FOREIGN KEY processing.
ALTER TABLE…ADD INDEX or CREATE INDEX on virtual columns must be able to evaluate the column values. The calling thread is necessarily holding MDL, and the THD and both old and new TABLE are available.
When an INSERT (or UPDATE of a PRIMARY KEY) replaces a delete-marked record, that record must either have been deleted by the current transaction, or a DELETE must have been executed by a previously committed transaction. Either way, the delete-marked record may be accessible to old MVCC read views. The TRX_UNDO_UPD_DEL_REC must store both the old and new values of all indexed columns. The information is strictly needed on ROLLBACK if the current transaction executed both the DELETE and the INSERT (or UPDATE).
On a positive side, INSERT into tables that may contain indexed virtual columns can only be invoked via the handler interface by the SQL layer while holding MDL, and the THD and TABLE are available for evaluating the columns in the deleted record.
The evaluation interface should be simplified to the following: InnoDB would fill in TABLE::record and ask the SQL layer to evaluate all indexed virtual columns.
InnoDB should include virtual columns in dict_table_t::cols and in the ‘table row’ tuple in the same order as TABLE_SHARE, and dict_table_t::v_cols and dtuple_t::v_fields would be removed.
The ALTER TABLE code should be cleaned up and unnecessary restrictions removed. An example is named in MDEV-17468.
The undo log record encoding for indexed virtual columns is should be fixed to encode the entire PAGE_INDEX_ID and not only the least significant 32 bits.