Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.8, 12.3, 13.1, 12.2.2
-
None
Description
On a RANGE-partitioned InnoDB table (monthly partitions on TO_DAYS(created_at), 86 partitions) with a FULLTEXT index on (subject, snippet) and a secondary index (counterparty, created_at), a prepared statement filtering counterparty LIKE ? bound to an exact value (no wildcard) returns rows whose counterparty does not match, but only after a FULLTEXT MATCH ... AGAINST ... IN BOOLEAN MODE ... ORDER BY ... LIMIT query was executed on the same connection. Disabling index_condition_pushdown makes the result correct.
Schema (simplified)
CREATE TABLE message ( |
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, |
created_at DATETIME NOT NULL, |
direction ENUM('in','out') NOT NULL, |
counterparty VARCHAR(255) NOT NULL, |
subject VARCHAR(998) NULL, |
snippet VARCHAR(200) NULL, |
PRIMARY KEY (id, created_at), |
KEY ix_counterparty (counterparty, created_at), |
FULLTEXT KEY ft_subject_snippet (subject, snippet) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
PARTITION BY RANGE (TO_DAYS(created_at)) ( |
PARTITION p_pre2024 VALUES LESS THAN (TO_DAYS('2024-01-01')), |
PARTITION p2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')), |
-- ... monthly partitions up to 2030-12 ... |
PARTITION pmax VALUES LESS THAN MAXVALUE |
);
|
Data: ~143 000 rows; in the tested month ~20 000 rows contain the word "doprava" in subject/snippet.
Steps (single session)
SET @w1='2026-08-01 00:00:00', @w2='2026-08-31 23:59:59'; |
SET @addr='someone@example.com'; -- a value with exactly 2 rows in the window |
SET @ft='+doprava*'; |
PREPARE adr FROM "SELECT COUNT(*), SUM(counterparty NOT LIKE ?) FROM ( |
SELECT id, counterparty FROM message m
|
WHERE m.direction='out' AND m.counterparty LIKE ? AND m.created_at BETWEEN ? AND ?
|
ORDER BY m.created_at, m.id LIMIT 51) t"; |
PREPARE ft FROM "SELECT COUNT(*) FROM ( |
SELECT id FROM message m
|
WHERE m.direction='out' AND MATCH(m.subject, m.snippet) AGAINST (? IN BOOLEAN MODE)
|
AND m.created_at BETWEEN ? AND ?
|
ORDER BY m.created_at, m.id LIMIT 51) t"; |
|
|
EXECUTE adr USING @addr, @addr, @w1, @w2; -- 2, 0 (correct) |
EXECUTE ft USING @ft, @w1, @w2; -- 51 |
EXECUTE adr USING @addr, @addr, @w1, @w2; -- 51, 51 (WRONG: 51 rows, none matching) |
Expected: 2, 0 for every EXECUTE adr.
Actual: after the FULLTEXT query, 51, 51.
Observations
- With SET SESSION optimizer_switch='index_condition_pushdown=off'; at the start of the session, the third EXECUTE adr returns 2, 0 (also after repeating the FULLTEXT query).
- Two EXECUTE adr without the FULLTEXT query in between both return 2, 0.
- A prefix value ('someone@%') was not affected in our test; the exact value was.
- Not yet isolated: whether the prepared-statement path, the exact-value LIKE or the ascending order are required. A first text-protocol attempt with DESC order and a prefix value did not reproduce, but it differed in all three.
Workaround
SET SESSION optimizer_switch='index_condition_pushdown=off' on every connection that may run both kinds of queries.