A FULLTEXT index added later misses rows when the first one was added to an empty table

CREATE TABLE t (id INT PRIMARY KEY, a VARCHAR(200), b VARCHAR(200)) ENGINE=InnoDB;

ALTER TABLE t ADD FULLTEXT INDEX fa (a);

INSERT INTO t VALUES (1, 'spoken', 'United Kingdom'),

  (2, 'Anglo-Saxon kingdoms', 'United Kingdom'),

  (3, 'roots from Latin', 'lingua franca'), (4, 'other', 'text');

ALTER TABLE t ADD FULLTEXT INDEX fab (a, b);

SELECT id FROM t WHERE MATCH (a, b) AGAINST ('+kingdom' IN BOOLEAN MODE);

returns no rows; expected 1 and 2. It works when the server is restarted before the second ALTER TABLE, with ALGORITHM=COPY (for either ALTER TABLE), with a user-defined FTS_DOC_ID column, or when the table was not empty when the first FULLTEXT index was added.

With innodb_ft_aux_table='test/t', INNODB_FT_INDEX_TABLE shows that fab got document IDs 6..9 while the rows (and fa) have FTS_DOC_ID 1..4.

When ALTER TABLE adds the first FULLTEXT index, prepare_inplace_alter_table_dict() sets DICT_TF2_FTS_ADD_DOC_ID on the rebuilt table, so that row_merge_build_indexes() assigns new FTS_DOC_ID values; row_merge_build_indexes() clears the flag when it is done. Since MDEV-23805, inplace_alter_table() skips that phase when the table is empty, so the flag stays set on the dict_table_t. The next ADD FULLTEXT INDEX without a rebuild then assigns new document IDs instead of reading FTS_DOC_ID (after a restart, the table is loaded without the flag).

We found it while porting MySQL's ngram tests for MDEV-10267 (innodb_fts.ngram_1 adds a built-in FULLTEXT index to a table that already has ngram indexes). We have a fix with a test in innodb_fts.innodb-fts-ddl (clear the flag when the phase is skipped) and can open a PR against 11.4.