Description:
When a `MATCH(col) AGAINST(...)` fulltext search predicate is used directly as (or as part of) the top-level `WHERE` clause, it is correctly evaluated as a fulltext boolean/relevance predicate and filters rows as expected. However, when the exact same `MATCH() AGAINST()` expression is wrapped inside a `CASE WHEN ... THEN ... ELSE ... END` expression where both the `THEN` and `ELSE` branches are identical (making the `CASE` a logical no-op), and the query also uses `DISTINCT`, the optimizer selects an execution plan that scans the table via a secondary (non-FULLTEXT) index and evaluates `MATCH() AGAINST()` as a filter condition on each row. In this plan, `MATCH() AGAINST()` appears to return incorrect relevance scores (nonzero for rows that do not match the search term), causing the query to return all rows instead of only the matching ones.
Since `CASE WHEN <any condition> THEN X ELSE X END` is logically equivalent to `X` for any value of the condition, the two queries below should always return identical result sets, but they do not.
How to repeat:
```
CREATE TABLE t1 (message CHAR(20), FULLTEXT INDEX ft(message));
INSERT INTO t1 (message) VALUES
('Testing'), ('table'), ('testbug'),
('steve'), ('is'), ('cool'), ('steve is cool');
ALTER TABLE t1 ADD INDEX m (message);
-- Correct: returns 2 rows
SELECT message FROM t1 WHERE MATCH(message) AGAINST('steve');
-- Incorrect: returns 7 rows (all rows), should also return 2
SELECT DISTINCT message FROM t1
WHERE CASE WHEN TRUE
THEN MATCH(message) AGAINST('steve')
ELSE MATCH(message) AGAINST('steve')
END;
```
Notably, removing either `DISTINCT` or the secondary index `m` on `message` causes the query to return the correct 2 rows. Both conditions must be present to reproduce the bug.
`EXPLAIN` for the failing query shows the plan uses the secondary index `m` for scanning/grouping rather than the FULLTEXT index, and evaluates `MATCH() AGAINST()` as a post-scan `Filter`:
```
-> Group (no aggregates) (cost=2.56 rows=2.65)
-> Filter: (0 <> (case when (...) then (match t1.message against ('steve')) else (match t1.message against ('steve')) end)) (cost=0.95 rows=7)
-> Index scan on t1 using m (cost=0.95 rows=7)
```
Description: When a `MATCH(col) AGAINST(...)` fulltext search predicate is used directly as (or as part of) the top-level `WHERE` clause, it is correctly evaluated as a fulltext boolean/relevance predicate and filters rows as expected. However, when the exact same `MATCH() AGAINST()` expression is wrapped inside a `CASE WHEN ... THEN ... ELSE ... END` expression where both the `THEN` and `ELSE` branches are identical (making the `CASE` a logical no-op), and the query also uses `DISTINCT`, the optimizer selects an execution plan that scans the table via a secondary (non-FULLTEXT) index and evaluates `MATCH() AGAINST()` as a filter condition on each row. In this plan, `MATCH() AGAINST()` appears to return incorrect relevance scores (nonzero for rows that do not match the search term), causing the query to return all rows instead of only the matching ones. Since `CASE WHEN <any condition> THEN X ELSE X END` is logically equivalent to `X` for any value of the condition, the two queries below should always return identical result sets, but they do not. How to repeat: ``` CREATE TABLE t1 (message CHAR(20), FULLTEXT INDEX ft(message)); INSERT INTO t1 (message) VALUES ('Testing'), ('table'), ('testbug'), ('steve'), ('is'), ('cool'), ('steve is cool'); ALTER TABLE t1 ADD INDEX m (message); -- Correct: returns 2 rows SELECT message FROM t1 WHERE MATCH(message) AGAINST('steve'); -- Incorrect: returns 7 rows (all rows), should also return 2 SELECT DISTINCT message FROM t1 WHERE CASE WHEN TRUE THEN MATCH(message) AGAINST('steve') ELSE MATCH(message) AGAINST('steve') END; ``` Notably, removing either `DISTINCT` or the secondary index `m` on `message` causes the query to return the correct 2 rows. Both conditions must be present to reproduce the bug. `EXPLAIN` for the failing query shows the plan uses the secondary index `m` for scanning/grouping rather than the FULLTEXT index, and evaluates `MATCH() AGAINST()` as a post-scan `Filter`: ``` -> Group (no aggregates) (cost=2.56 rows=2.65) -> Filter: (0 <> (case when (...) then (match t1.message against ('steve')) else (match t1.message against ('steve')) end)) (cost=0.95 rows=7) -> Index scan on t1 using m (cost=0.95 rows=7) ```