Bug #120539 Duplicate FULLTEXT index on a column returns wrong MATCH...AGAINST results when combined with a STORED generated column

• Submitted: 24 May 15:26
• Reporter: Li Zeyan
• Status: Open
• Category: MySQL Server: FULLTEXT search
• Severity: S2 (Serious)
• Version: 9.6.0
• OS: Linux
• CPU Architecture: Any

[24 May 15:26] Li Zeyan

Description:
When a table has a FULLTEXT index on a VARCHAR column, adding a STORED generated column (via ALTER TABLE) and a functional index, then creating a SECOND FULLTEXT index on the same column, corrupts the fulltext search state. After the second FULLTEXT index is created, MATCH(c5) AGAINST (... IN BOOLEAN MODE) queries return 0 rows instead of the correct non-zero count. The underlying data is intact -- a LIKE query on the same column and same pattern returns the correct result.

Specifically, the following sequence triggers the bug:

  1. Create a table with an INT column (c0), an INT column (c4), and a
     VARCHAR(255) column (c5).
  2. Insert one row where c5 = '1828091703'.
  3. Create a first FULLTEXT index on c5 -- queries work correctly at
     this point.
  4. ALTER TABLE to add a STORED generated column defined as
     IFNULL(c0, 0) + 1.
  5. Create a functional index on the expression (c4 + 1).
  6. Create a SECOND FULLTEXT index on c5 -- this corrupts fulltext
     search.

After step 6, the query:

  SELECT COUNT(*) FROM idx_t0
    WHERE MATCH(c5) AGAINST('1828091703*' IN BOOLEAN MODE);

returns 0 instead of 1. The control query using LIKE still returns 1:

  SELECT COUNT(*) FROM idx_t0 WHERE c5 LIKE '1828091703%';

The bug requires all three preconditions to be present simultaneously:
(a) an existing FULLTEXT index on the column,
(b) a STORED generated column on the table, and
(c) a functional index on the table.
Removing any one of these conditions prevents the bug from manifesting.

How to repeat:
DROP DATABASE IF EXISTS test_ft_min;
CREATE DATABASE test_ft_min;
USE test_ft_min;

-- Step 1: Create table with 1 row
CREATE TABLE idx_t0 (
    c0 INT NOT NULL,
    c4 INT NOT NULL,
    c5 VARCHAR(255) NOT NULL
);
INSERT INTO idx_t0 VALUES (286586734, 1717882338, '1828091703');

-- Step 2: Create FIRST fulltext index (works correctly)
CREATE FULLTEXT INDEX ft_0_c5 ON idx_t0 (c5);

-- Step 3: Add STORED generated column (triggers bug condition 1)
ALTER TABLE idx_t0 ADD COLUMN gen_col BIGINT GENERATED ALWAYS AS (IFNULL(c0, 0) + 1) STORED;

-- Step 4: Create functional index (triggers bug condition 2)
CREATE INDEX func_idx ON idx_t0 ((c4 + 1));

-- Step 5: Create SECOND fulltext index on same column (triggers the bug!)
CREATE FULLTEXT INDEX ft_idx_3122 ON idx_t0 (c5);

-- Verify: both queries should return 1
SELECT 'FT BUG (should be 1)' AS test, COUNT(*) AS result
    FROM idx_t0 WHERE MATCH(c5) AGAINST('1828091703*' IN BOOLEAN MODE);

SELECT 'LIKE ctrl (should be 1)' AS test, COUNT(*) AS result
    FROM idx_t0 WHERE c5 LIKE '1828091703%';

SHOW INDEX FROM idx_t0 WHERE Index_type = 'FULLTEXT';

FULLTEXT result:
+----------------------+--------+
| test                 | result |
+----------------------+--------+
| FT BUG (should be 1) |      0 |  <- WRONG! Missing 1 row!
+----------------------+--------+
1 row in set (0.001 sec)

LIKE control result:
+-------------------------+--------+
| test                    | result |
+-------------------------+--------+
| LIKE ctrl (should be 1) |      1 | <- CORRECT
+-------------------------+--------+
1 row in set (0.001 sec)

Expected output for the MATCH query: COUNT(*) = 1
Actual output: COUNT(*) = 0

Suggested fix:
The second CREATE FULLTEXT INDEX statement appears to corrupt the
internal fulltext index state (possibly the FT index cache or token
mapping) when the table contains both a STORED generated column and
a functional index. The InnoDB fulltext index creation/rebuild logic
should be reviewed for the code path that handles tables with generated
columns and functional indexes.