Description:
When a FULLTEXT index is forced on a VARCHAR column and the query contains both a numeric equality filter (implicit type coercion) and an ORDER BY clause, MySQL returns an empty result set instead of the matching row. COUNT(*) correctly reports the row exists, but SELECT * returns 0 rows. The bug only manifests when the optimizer uses a "Index scan on t using ft" plan (FULLTEXT index); the default plan using a BTREE/UNIQUE index returns correct results.
XPLAIN comparison:
- Buggy plan (FORCE INDEX(ft)):
-> Filter: (t.c = 0.35277035960136804) (cost=0.6 rows=1)
-> Index scan on t using ft (cost=0.6 rows=1)
- Correct plan (default, uses UNIQUE index):
-> Filter: (t.c = 0.35277035960136804) (cost=0.35 rows=1)
-> Covering index scan on t using u (cost=0.35 rows=1)
The FULLTEXT index scan path appears to incorrectly skip or discard rows during the scan when combined with ORDER BY and the type-coerced equality condition.
How to repeat:
DROP DATABASE IF EXISTS test_ft_orderby_bug;
CREATE DATABASE test_ft_orderby_bug;
USE test_ft_orderby_bug;
CREATE TABLE t (c VARCHAR(255) NOT NULL);
INSERT INTO t VALUES ('0.35277035960136804');
CREATE FULLTEXT INDEX ft ON t (c);
CREATE UNIQUE INDEX u ON t (c);
ANALYZE TABLE t;
-- BUG: returns 0 rows (should be 1)
SELECT * FROM t FORCE INDEX(ft) WHERE c = 0.35277035960136804 ORDER BY c;
-- CORRECT: returns 1 row
SELECT * FROM t WHERE c = 0.35277035960136804 ORDER BY c;
-- PROOF the row exists: COUNT(*) = 1 even with FORCE FULLTEXT + ORDER BY
SELECT COUNT(*) FROM t FORCE INDEX(ft) WHERE c = 0.35277035960136804 ORDER BY c;
Using FORCE INDEX on FULLTEXT index:
Empty set (0.001 sec) <- WRONG! missing 1 row
Using default plan:
+---------------------+
| c |
+---------------------+
| 0.35277035960136804 | <- CORRECT
+---------------------+
1 row in set (0.003 sec)
Using COUNT(*) and FORCE INDEX on FULLTEXT index (as proof):
+-----+
| cnt |
+-----+
| 1 | <- CORRECT
+-----+
1 row in set (0.001 sec)
Notes:
- The bug requires all three conditions simultaneously: FULLTEXT index scan + ORDER BY + numeric literal in WHERE (type coercion from DOUBLE to VARCHAR).
- Removing ORDER BY makes the query return the correct result.
- Removing FORCE INDEX and letting the optimizer choose the UNIQUE BTREE index also returns the correct result.
- Using a string literal (WHERE c = '0.35277035960136804') instead of a numeric literal also returns the correct result, confirming the issue is related to type coercion in the FULLTEXT index scan path combined with ORDER BY.
Suggested fix:
The FULLTEXT index scan iterator appears to mishandle the combination of ORDER BY and type-coerced equality filtering. The optimizer or execution layer should correctly apply the filter after the full-text index scan, similar to how it works without ORDER BY. Investigate the interaction between ha_innobase::read_range_first/next and the type coercion logic when a FULLTEXT index is used as a non-full-text access path (i.e., not a MATCH...AGAINST query, but a regular range/ref scan on a FULLTEXT index).
Description: When a FULLTEXT index is forced on a VARCHAR column and the query contains both a numeric equality filter (implicit type coercion) and an ORDER BY clause, MySQL returns an empty result set instead of the matching row. COUNT(*) correctly reports the row exists, but SELECT * returns 0 rows. The bug only manifests when the optimizer uses a "Index scan on t using ft" plan (FULLTEXT index); the default plan using a BTREE/UNIQUE index returns correct results. XPLAIN comparison: - Buggy plan (FORCE INDEX(ft)): -> Filter: (t.c = 0.35277035960136804) (cost=0.6 rows=1) -> Index scan on t using ft (cost=0.6 rows=1) - Correct plan (default, uses UNIQUE index): -> Filter: (t.c = 0.35277035960136804) (cost=0.35 rows=1) -> Covering index scan on t using u (cost=0.35 rows=1) The FULLTEXT index scan path appears to incorrectly skip or discard rows during the scan when combined with ORDER BY and the type-coerced equality condition. How to repeat: DROP DATABASE IF EXISTS test_ft_orderby_bug; CREATE DATABASE test_ft_orderby_bug; USE test_ft_orderby_bug; CREATE TABLE t (c VARCHAR(255) NOT NULL); INSERT INTO t VALUES ('0.35277035960136804'); CREATE FULLTEXT INDEX ft ON t (c); CREATE UNIQUE INDEX u ON t (c); ANALYZE TABLE t; -- BUG: returns 0 rows (should be 1) SELECT * FROM t FORCE INDEX(ft) WHERE c = 0.35277035960136804 ORDER BY c; -- CORRECT: returns 1 row SELECT * FROM t WHERE c = 0.35277035960136804 ORDER BY c; -- PROOF the row exists: COUNT(*) = 1 even with FORCE FULLTEXT + ORDER BY SELECT COUNT(*) FROM t FORCE INDEX(ft) WHERE c = 0.35277035960136804 ORDER BY c; Using FORCE INDEX on FULLTEXT index: Empty set (0.001 sec) <- WRONG! missing 1 row Using default plan: +---------------------+ | c | +---------------------+ | 0.35277035960136804 | <- CORRECT +---------------------+ 1 row in set (0.003 sec) Using COUNT(*) and FORCE INDEX on FULLTEXT index (as proof): +-----+ | cnt | +-----+ | 1 | <- CORRECT +-----+ 1 row in set (0.001 sec) Notes: - The bug requires all three conditions simultaneously: FULLTEXT index scan + ORDER BY + numeric literal in WHERE (type coercion from DOUBLE to VARCHAR). - Removing ORDER BY makes the query return the correct result. - Removing FORCE INDEX and letting the optimizer choose the UNIQUE BTREE index also returns the correct result. - Using a string literal (WHERE c = '0.35277035960136804') instead of a numeric literal also returns the correct result, confirming the issue is related to type coercion in the FULLTEXT index scan path combined with ORDER BY. Suggested fix: The FULLTEXT index scan iterator appears to mishandle the combination of ORDER BY and type-coerced equality filtering. The optimizer or execution layer should correctly apply the filter after the full-text index scan, similar to how it works without ORDER BY. Investigate the interaction between ha_innobase::read_range_first/next and the type coercion logic when a FULLTEXT index is used as a non-full-text access path (i.e., not a MATCH...AGAINST query, but a regular range/ref scan on a FULLTEXT index).