MyISAM and Aria: wrong results for boolean mode searches with an exclusion inside a group

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

INSERT INTO t1 VALUES (1, 'xxxx alpha'), (2, 'xxxx'), (3, 'xxxx aaaa bbbb'),

  (4, 'xxxx bbbb aaaa'), (5, 'xxxx aaaa bbbb cccc'), (6, 'xxxx aaaa');

SELECT id FROM t1 WHERE MATCH(c) AGAINST('+xxxx +(-alpha xxxx)' IN BOOLEAN MODE);

SELECT id FROM t1 IGNORE INDEX(c)

  WHERE MATCH(c) AGAINST('+xxxx +(-alpha xxxx)' IN BOOLEAN MODE);

Expected: 2, 3, 4, 5, 6 for both. The first query, which uses the fulltext index, returns these rows. The second one returns 1, 2, 3, 4, 5, 6, although row 1 contains alpha, so the group (-alpha xxxx) does not match it.

More queries on the same table. "index" is the search through the fulltext index, "IGNORE INDEX" the same query with IGNORE INDEX(c), where ft_boolean_find_relevance() evaluates each row. For a query with a phrase, ft_boolean_read_next() also checks each row found through the index with ft_boolean_find_relevance(). "expected" is what InnoDB returns for the same table. Aria returns the same as MyISAM.

                                             main                      11.4 + patch for MDEV-41759

query                          expected      index      IGNORE INDEX   index       IGNORE INDEX

'+xxxx +(-alpha xxxx)'         2,3,4,5,6     2,3,4,5,6  1,2,3,4,5,6    2,3,4,5,6   1,2,3,4,5,6

'+xxxx +(-"aaaa bbbb" xxxx)'   1,2,4,6       1,2,3,6    1,2,3,4,5,6    1,2,3,4,6   1,2,3,4,5,6

'+xxxx +(+aaaa -"aaaa bbbb")'  4,6           6          3,4,5,6        4,6         3,4,5,6

'+xxxx -(+"aaaa bbbb" -cccc)'  1,2,4,5,6     1,2,6      1,2,4,6        1,2,4,6     1,2,4,6

'+xxxx -(+xxxx -"aaaa bbbb")'  3,5           (none)     (none)         (none)      (none)

The missing row 4 ('xxxx bbbb aaaa') in the index column on main is MDEV-41759: an excluded phrase is taken as matched when its words are in the row in any order. The patch for MDEV-41759 fixes only that row; all other differences from the expected rows remain.

The result depends on the order of the words in the row:

CREATE TABLE t2 (id INT PRIMARY KEY, c TEXT, FULLTEXT(c)) ENGINE=MyISAM;

INSERT INTO t2 VALUES (1, 'xxxx alpha'), (2, 'alpha xxxx'),

  (3, 'xxxx aaaa bbbb'), (4, 'aaaa bbbb xxxx');

'+xxxx +(alpha xxxx)' with IGNORE INDEX returns 1, 3, 4 instead of 3, 4. With and without IGNORE INDEX, '+xxxx +("aaaa bbbb" xxxx)' returns 1, 2, 3 instead of 1, 2, and '+xxxx -(+xxxx -"aaaa bbbb")' returns 4 instead of 3, 4. In each case the row in which the excluded word or phrase comes after xxxx (row 1 for alpha, row 3 for "aaaa bbbb") is wrong, and the row in which it comes before xxxx (row 2, row 4) is right. This is the same on main and on 11.4 with the patch for MDEV-41759, and for Aria.

We noticed this while working on MDEV-41759. We did not check whether MySQL has bug reports for exclusions inside groups.