Phrase search misses rows of a FULLTEXT index built over rows with an empty string in an earlier indexed column

When a FULLTEXT index on several columns is built over existing rows (ALTER TABLE ... ADD FULLTEXT, CREATE FULLTEXT INDEX, or a rebuild such as ALTER TABLE ... FORCE or OPTIMIZE TABLE), a phrase search does not find rows in which an earlier column of the index holds an empty string. A NULL in that column works, and ALGORITHM=COPY is not affected. The same happens to rows that were only in the FTS cache when the server was killed, once they are added to the cache again after the restart.

CREATE TABLE t (id INT PRIMARY KEY, a VARCHAR(20), c TEXT) ENGINE=InnoDB;

INSERT INTO t VALUES (1, '', 'hello world'), (2, NULL, 'hello world'),

                     (3, 'x', 'hello world');

ALTER TABLE t ADD FULLTEXT(a, c);

INSERT INTO t VALUES (4, '', 'hello world');

SELECT id FROM t WHERE MATCH(a, c) AGAINST('"hello world"' IN BOOLEAN MODE);

Expected: 1, 2, 3, 4. Actual on 11.4 and main: 2, 3, 4. Row 4, which was added after the index existed, is found; row 1 is not. '+hello +world' finds all four rows. If the index exists before the rows are inserted, row 1 is found until the table is rebuilt (ALTER TABLE t FORCE, OPTIMIZE TABLE t).

When the index is built, row_merge_fts_doc_tokenize() moves the start of the next field by the length of the field plus one byte (t_ctx->init_pos += doc->text.f_len + 1), also for an empty field. fts_init_recover_doc() (fts_init_recover_all_docs() since MDEV-28730) does the same on recovery. Rows added to an existing index (fts_fetch_doc_from_rec(), fts_fetch_doc_from_tuple()) and phrase search (fts_query_fetch_document()) skip an empty field, as they skip a NULL. The skip in fts_fetch_doc_from_rec() came with MDEV-7523 (MySQL Bug #75755), which fixed the same symptom for newly inserted rows only. In INNODB_FT_INDEX_TABLE, "hello" of row 1 is at position 1, that of row 2 at 0. Phrase search starts to match at the stored position, one byte into the word, and does not find the phrase.

Recovery:

CREATE TABLE t1 (id INT PRIMARY KEY, a TEXT, c TEXT, FULLTEXT(a, c)) ENGINE=InnoDB;

INSERT INTO t1 VALUES (1, '', 'hello world'), (2, NULL, 'hello world'),

  (3, 'x', 'hello world'), (4, REPEAT('long ', 4000), 'hello world');

-- kill the server while the rows are only in the FTS cache, start it again

-- (mtr: let $shutdown_timeout= 0; --source include/restart_mysqld.inc)

SELECT id FROM t1 WHERE MATCH(a, c) AGAINST('"hello world"' IN BOOLEAN MODE);

This returns 2, 3, 4 on 11.4 and 2, 3 on main (1, 2, 3, 4 before the kill). On main, fts_init_recover_all_docs() also moves the position by the length of the locally stored part of an externally stored field (field_len from rec_get_nth_field()) instead of the length of its text, so row 4 is missed as well: in INNODB_FT_INDEX_CACHE, "hello" of row 4 is at position 21 instead of 20001.

We found this with a random test for the ngram parser proposed in MDEV-10267, where it also happens. A fix with tests is ready (for main also the externally stored field). Indexes built before the fix need a rebuild.