Missing row with MRR and an indexed virtual column after ALTER TABLE on an unrelated MEMORY table

A SELECT from an InnoDB table with an indexed VIRTUAL generated column loses a matching row when an always-true expression is added to ORDER BY. The reduced reproducer first inserts one row into an unrelated MEMORY table and executes ALTER TABLE on that table.

The first sort key is the unique primary key, and the additional sort expression is true for every row. Both queries should therefore return the same rows in the same order.

Steps to reproduce

SET optimizer_switch = 'mrr=on,mrr_cost_based=off';

CREATE TABLE t1 (

  pk INT AUTO_INCREMENT PRIMARY KEY,

  col_int_nokey INT NULL,

  col_int_key INT GENERATED ALWAYS AS (col_int_nokey) VIRTUAL,

  INDEX (col_int_key)

);

INSERT INTO t1 (col_int_nokey)

VALUES (0), (5), (4), (3), (7), (42), (5), (0), (3);

SET optimizer_switch = 'index_condition_pushdown=off';

CREATE TABLE h (a INT) ENGINE=MEMORY;

INSERT INTO h VALUES (1);

ALTER TABLE h ADD c INT;

SELECT DISTINCT *

FROM t1

WHERE col_int_key IN (3, 4)

  AND col_int_key <= 83

ORDER BY 1;

SELECT DISTINCT *

FROM t1

WHERE col_int_key IN (3, 4)

  AND col_int_key <= 83

ORDER BY 1,

  ((t1.col_int_nokey IS NULL)

   OR NOT (t1.col_int_nokey IS NULL)

   OR (t1.col_int_nokey IS NULL) IS NULL);

Actual result

The first SELECT returns three rows:

+----+---------------+-------------+

| pk | col_int_nokey | col_int_key |

+----+---------------+-------------+

|  3 |             4 |           4 |

|  4 |             3 |           3 |

|  9 |             3 |           3 |

+----+---------------+-------------+

The second SELECT returns only two rows:

+----+---------------+-------------+

| pk | col_int_nokey | col_int_key |

+----+---------------+-------------+

|  4 |             3 |           3 |

|  9 |             3 |           3 |

+----+---------------+-------------+

The row (3, 4, 4) satisfies the WHERE condition but is missing from the second result.

Expected result

Both SELECT statements should return the same three rows, ordered by pk.

Additional observations

• The reproducer was verified in a fresh MariaDB 11.8.9 server instance using the native mariadb client.
• In this reduced test case, omitting the CREATE TABLE h / INSERT INTO h / ALTER TABLE h sequence makes the two SELECT statements return the same three rows.
• An empty MEMORY table followed by ALTER TABLE did not trigger the discrepancy in the tested reduction. Inserting one row before ALTER TABLE was sufficient.
• Table h is not referenced by either SELECT. Why its ALTER TABLE operation affects the later SELECT has not been determined.