MySQL Bugs: #120527: FULLTEXT index scan with ORDER BY and numeric WHERE filter returns empty result set

Bug #120527	FULLTEXT index scan with ORDER BY and numeric WHERE filter returns empty result set
Submitted:	22 May 11:47	Modified:	27 May 11:56
Reporter:	Li Zeyan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	9.6.0	OS:	Linux
Assigned to:		CPU Architecture:	Any

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.

EXPLAIN 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(500));
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 (empty set)
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)
SELECT COUNT(*) AS cnt FROM t FORCE INDEX(ft) WHERE c = 0.35277035960136804;

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).

Hi Li Zeyan,

We are unable to repeat the problem on MySQL-9.7. For the query with force index, this is the result we are seeing.

mysql> SELECT * FROM t FORCE INDEX(ft) WHERE c = 0.35277035960136804 ORDER BY c;
+---------------------+
| c                   |
+---------------------+
| 0.35277035960136804 |
+---------------------+
1 row in set (0.001 sec)

mysql> explain SELECT * FROM t FORCE INDEX(ft) WHERE c = 0.35277035960136804 ORDER BY c\G
*************************** 1. row ***************************
EXPLAIN: -> Sort: t.c  (cost=0.6 rows=1)
    -> Filter: (t.c = 0.35277035960136804)  (cost=0.6 rows=1)
        -> Table scan on t  (cost=0.6 rows=1)

1 row in set, 1 warning (0.001 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                              |
+---------+------+--------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use ref access on index 'ft' due to type or collation conversion on field 'c' |
+---------+------+--------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

Apologies, the original "How to repeat" script has a mistake in the CREATE TABLE statement that prevents the bug from reproducing. 

The bug only manifests when the column is defined as NOT NULL. The original submission used `c VARCHAR(500)` (nullable), which does NOT trigger the bug. The column must be `NOT NULL`.

Here is the corrected reproduction script:

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;