Wrong results from filesort over indexed virtual columns during a concurrent uncommitted update

Reproduce

--source include/have_innodb.inc

--echo #

--echo # MDEV-41365 Wrong results from filesort over indexed virtual columns

--echo # during a concurrent uncommitted update

--echo #

create table t (

  pk int primary key,

  f int,

  v1 int as (f & 1) virtual,

  v2 int as (f & 2) virtual,

  key (v1)

) engine=innodb;

insert into t (pk, f) values (1, 0);

explain select * from t force index (v1) where v1 = 0 and v2 = 0 order by f, pk;

select * from t force index (v1) where v1 = 0 and v2 = 0 order by f, pk;

connect con1, localhost, root;

begin;

update t set f = 1 where pk = 1;

connection default;

--echo # No filesort: correct

select * from t force index (v1) where v1 = 0 and v2 = 0;

--echo # Filesort: must return pk=1, returns nothing

select * from t force index (v1) where v1 = 0 and v2 = 0 order by f, pk;

connection con1;

rollback;

disconnect con1;

connection default;

drop table t;

Result

No rows returned.

 # Filesort: must return pk=1, returns nothing

 select * from t force index (v1) where v1 = 0 and v2 = 0 order by f, pk;

 pk     f       v1      v2

Expected

Row pk=1 returned.

 # Filesort: must return pk=1, returns nothing

 select * from t force index (v1) where v1 = 0 and v2 = 0 order by f, pk;

 pk     f       v1      v2

 1      0       0       0

More info

A `SELECT` that reads InnoDB rows through a secondary index on a `VIRTUAL` generated column and sorts them with filesort returns a wrong result while another connection holds an uncommitted change to a row in the scanned index range. It can return rows that do not satisfy its `WHERE` clause, or omit rows that do. Before the other transaction starts and after it rolls back, the same query returns the correct result.

The failure is deterministic. It reproduces on every release line tested, from 10.2.44 to 13.0.2, at both `REPEATABLE READ` and `READ COMMITTED`.

How to reproduce:

Requirements: `bash` and Docker (the script uses the official `mariadb` images). Alternatively, you can use an existing server, reached through the `mariadb` command-line client.

Use the repro.sh as follows:

./repro.sh # mariadb:10.6.22
./repro.sh mariadb:11.8 # 11.8

ISOLATION=READ-COMMITTED ./repro.sh # isolation level of the reading connection

MARIADB_CLIENT="mariadb -h 127.0.0.1 -P 3306 -u root -psecret" ./repro.sh --server

The script creates the tables in `sql/schema.sql`, about 40,000 rows each, in database
`repro_vcol`. For each query it compares three results:

1. the query run with no other activity;
2. the query run while a second connection has executed the writer's `BEGIN; UPDATE ...` and not
yet committed (the script waits until the transaction appears in `INFORMATION_SCHEMA.INNODB_TRX`);
3. the query run after that connection has been killed and its transaction rolled back.

The update is never committed, so the reader must see the same rows in all three runs. The script exits with 0 if they differed.

Please see attached file.

This is causing issues in production hence the criticality.