MDEV-41759 MyISAM and Aria: an excluded phrase in boolean mode excludes all rows that contain its words, in any order - #5976

• The Jira issue number for this PR is: MDEV-41759

Description

During the index search, _ftb_climb_the_tree() took a phrase as matched as soon as all its words were in the row. Word order and adjacency were only checked by ft_boolean_find_relevance(), which ft_boolean_read_next() calls for the rows that the index search accepted. A row rejected by an excluded phrase never gets there, so '+xxxx -"alpha beta"' also rejected rows with "beta alpha" or "alpha gamma beta".

Before:

CREATE TABLE t (id INT PRIMARY KEY, c TEXT, FULLTEXT(c)) ENGINE=MyISAM;
INSERT INTO t VALUES (1, 'xxxx alpha beta'), (2, 'xxxx beta alpha'),
  (3, 'xxxx alpha gamma beta'), (4, 'xxxx');
SELECT id FROM t WHERE MATCH(c) AGAINST('+xxxx -"alpha beta"' IN BOOLEAN MODE);
id
4

After: 2, 3, 4. InnoDB returns the same, and so do MyISAM and Aria with IGNORE INDEX (c).

Fix: in the index search, a phrase that can exclude the row, directly or through an enclosing excluded group, is checked in the row before its match is passed up. The check is skipped when a group on the path to the root has already rejected the current row. Other phrases are not checked during the index search, as before. Errors from reading the row are now returned instead of always HA_ERR_OUT_OF_MEM. Aria's ftb_parse_query_internal() now initializes the FT_WORD, as MDEV-33478 did for MyISAM: maria_ft_get_word() leaves it unset for a leading operator such as +", and MemorySanitizer reported the uninitialized arguments of mysql_add_word() for the new test.

Side effects: with an excluded phrase, the index search reads each row that contains all words of the phrase, also when the rest of the query rejects that row later. Queries without required words or with truncation can therefore do additional random row reads.

Note for reviewers: other wrong results with an exclusion inside a group, for example '+xxxx +(-alpha xxxx)' with IGNORE INDEX, are the same before and after this patch: MDEV-41771.

Found while working on MDEV-10267 (ngram parser), which turns each word of a boolean mode query into a phrase of its n-grams.

Release Notes

A boolean mode fulltext search with an excluded phrase (-"...") on a MyISAM or Aria table no longer excludes rows that contain the words of the phrase in another order or not next to each other.

How can this PR be tested?

New test main.fulltext_exclude_phrase for MyISAM and Aria, with an InnoDB reference: word order and adjacency, exclusions inside groups, prefixes, stopwords in a phrase, several indexed columns, and the test cases of MySQL bugs #84036 and #31755. Without the fix, '+xxxx -"alpha beta"' returns only row 4 for MyISAM and Aria.

The maria and innodb_fts suites and the main fulltext and MyISAM tests pass on 11.4 (debug build). On main with all our fixes and the ngram parser of MDEV-10267, the innodb_fts, maria and plugins suites and the main fulltext, plugin and alter tests pass.

Basing the PR against the correct MariaDB version

• This is a new feature and the PR is based against the latest MariaDB development branch.
• This is a bug fix and the PR is based against the earliest maintained branch in which the bug can be reproduced. (10.11 has the same code, but its first GA release is more than three years old. We can rebase onto 10.11 if wanted.)

PR quality check

• I checked the CODING_STANDARDS.md file and my PR conforms to this where appropriate.
• For any trivial modifications to the PR, I am ok with the reviewer making the changes themselves.

🤖 Generated with Claude Code