Bug #80298 Full-Text queries with additional secondary index gives NULL or Zero rows

Description:
Certain Full-Text queries are failing when an additional secondary index is used.

How to repeat:
I'll attach SQL below to this bugs.

1. Create test table/data

mysql> CREATE TABLE `ft_max_test` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `doc_id` int(10) unsigned NOT NULL DEFAULT '0',
    ->   `title` varchar(255) NOT NULL DEFAULT 'Untitled',
    ->   `body` mediumtext,
    ->   PRIMARY KEY (`id`),  
    ->   KEY `doc_id` (`doc_id`),  
    ->   FULLTEXT KEY `title` (`title`),  
    ->   FULLTEXT KEY `body` (`body`) 
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO ft_max_test VALUES
    ->   (1,  1, 'Insert into table', 'insert into table select from'),
    ->   (2,  1, 'Delete from table', 'some body text here'),
    ->   (3,  1, 'Update',            'perform update'),
    ->   (4,  2, 'Insert into table', 'insert into table select from'),
    ->   (5,  2, 'Delete from table', 'some body text here'),
    ->   (6,  2, 'Update',            'perform update'),
    ->   (7,  3, 'Insert into table', 'insert into table select from'),
    ->   (8,  3, 'Delete from table', 'some body text here'),
    ->   (9,  3, 'Update',            'perform update'),
    ->   (10, 4, 'Insert into table', 'insert into table select from'),
    ->   (11, 4, 'Delete from table', 'some body text here'),
    ->   (12, 4, 'Update',            'perform update');
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

2. Perform FT search getting max relevance for title and body (without using doc_id index)

mysql> SELECT
    ->     MAX(MATCH (title) AGAINST ('table' IN BOOLEAN MODE)) AS rel1,
    ->     MAX(MATCH (body) AGAINST ('table' IN BOOLEAN MODE)) AS rel2
    -> FROM
    ->     ft_max_test
    -> WHERE
    ->     MATCH (title) AGAINST ('table' IN BOOLEAN MODE) OR
    ->     MATCH (body) AGAINST ('table' IN BOOLEAN MODE);
+----------------------+---------------------+
| rel1                 | rel2                |
+----------------------+---------------------+
| 0.031008131802082062 | 0.22764469683170319 |
+----------------------+---------------------+
1 row in set (0.01 sec)

3. Examine title and body relevance values for rows matched

mysql> SELECT
    ->     id,
    ->     doc_id,
    ->     MATCH (title) AGAINST ('table' IN BOOLEAN MODE) AS rel1,
    ->     MATCH (body) AGAINST ('table' IN BOOLEAN MODE) AS rel2
    -> FROM
    ->     ft_max_test
    -> WHERE
    ->     MATCH (title) AGAINST ('table' IN BOOLEAN MODE) OR
    ->     MATCH (body) AGAINST ('table' IN BOOLEAN MODE);
+----+--------+----------------------+---------------------+
| id | doc_id | rel1                 | rel2                |
+----+--------+----------------------+---------------------+
|  1 |      1 | 0.031008131802082062 | 0.22764469683170319 |
|  2 |      1 | 0.031008131802082062 |                   0 |
|  4 |      2 | 0.031008131802082062 | 0.22764469683170319 |
|  5 |      2 | 0.031008131802082062 |                   0 |
|  7 |      3 | 0.031008131802082062 | 0.22764469683170319 |
|  8 |      3 | 0.031008131802082062 |                   0 |
| 10 |      4 | 0.031008131802082062 | 0.22764469683170319 |
| 11 |      4 | 0.031008131802082062 |                   0 |
+----+--------+----------------------+---------------------+
8 rows in set (0.00 sec)

4. Perform FT search getting max relevance for title and body using doc_id index (Doesn't Work)

mysql> SELECT
    ->     MAX(MATCH (title) AGAINST ('table' IN BOOLEAN MODE)) AS rel1,
    ->     MAX(MATCH (body) AGAINST ('table' IN BOOLEAN MODE)) AS rel2
    -> FROM
    ->     ft_max_test
    -> WHERE
    ->     doc_id = 1 AND
    ->     (MATCH (title) AGAINST ('table' IN BOOLEAN MODE) OR
    ->     MATCH (body) AGAINST ('table' IN BOOLEAN MODE));
+------+------+
| rel1 | rel2 |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

5. Run EXPLAIN to confirm doc_id index was used

mysql> EXPLAIN SELECT
    ->     MAX(MATCH (title) AGAINST ('table' IN BOOLEAN MODE)) AS rel1,
    ->     MAX(MATCH (body) AGAINST ('table' IN BOOLEAN MODE)) AS rel2
    -> FROM
    ->     ft_max_test
    -> WHERE
    ->     doc_id = 1 AND
    ->     (MATCH (title) AGAINST ('table' IN BOOLEAN MODE) OR
    ->     MATCH (body) AGAINST ('table' IN BOOLEAN MODE));
+----+-------------+-------------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | ft_max_test | NULL       | ref  | doc_id        | doc_id | 4       | const |    3 |    20.99 | Using where |
+----+-------------+-------------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

6. Try to get title and body relevance values for rows matched

mysql> SELECT
    ->     id,
    ->     doc_id,
    ->     MATCH (title) AGAINST ('table' IN BOOLEAN MODE) AS rel1,
    ->     MATCH (body) AGAINST ('table' IN BOOLEAN MODE) AS rel2
    -> FROM
    ->     ft_max_test
    -> WHERE
    ->     doc_id = 1 AND
    ->     (MATCH (title) AGAINST ('table' IN BOOLEAN MODE) OR
    ->     MATCH (body) AGAINST ('table' IN BOOLEAN MODE));
Empty set (0.00 sec)

7. Try 4 and 6 above again ignoring the doc_id index ... Works!

mysql> SELECT
    ->     MAX(MATCH (title) AGAINST ('table' IN BOOLEAN MODE)) AS rel1,
    ->     MAX(MATCH (body) AGAINST ('table' IN BOOLEAN MODE)) AS rel2
    -> FROM
    ->     ft_max_test
    -> IGNORE
    ->     index(doc_id)
    -> WHERE
    ->     doc_id = 1 AND
    ->     (MATCH (title) AGAINST ('table' IN BOOLEAN MODE) OR
    ->     MATCH (body) AGAINST ('table' IN BOOLEAN MODE));
+----------------------+---------------------+
| rel1                 | rel2                |
+----------------------+---------------------+
| 0.031008131802082062 | 0.22764469683170319 |
+----------------------+---------------------+
1 row in set (0.01 sec)

mysql> SELECT
    ->     id,
    ->     doc_id,
    ->     MATCH (title) AGAINST ('table' IN BOOLEAN MODE) AS rel1,
    ->     MATCH (body) AGAINST ('table' IN BOOLEAN MODE) AS rel2
    -> FROM
    ->     ft_max_test
    -> IGNORE
    ->     index(doc_id)
    -> WHERE
    ->     doc_id = 1 AND
    ->     (MATCH (title) AGAINST ('table' IN BOOLEAN MODE) OR
    ->     MATCH (body) AGAINST ('table' IN BOOLEAN MODE));
+----+--------+----------------------+---------------------+
| id | doc_id | rel1                 | rel2                |
+----+--------+----------------------+---------------------+
|  1 |      1 | 0.031008131802082062 | 0.22764469683170319 |
|  2 |      1 | 0.031008131802082062 |                   0 |
+----+--------+----------------------+---------------------+
2 rows in set (0.00 sec)

Suggested fix:
May be related to bug #80244.

Test Case

Attachment: test_case.sql (application/octet-stream, text), 6.09 KiB.

Hello Ray,

Thank you for the report and test case.
Observed this with 5.7.11 build.

Thanks,
Umesh

Posted by developer:
 
Fixed as of the upcoming 5.7.14 release, and here's the changelog entry:

The optimizer failed to mark the columns required for MATCH function
evaluation when the secondary index used for the full-text query was not
chosen by the optimizer. 

Thank you for the bug report.