Bug #79497 Fulltext index on a given column causes empty set for 'distinct' query
Submitted: 2 Dec 2015 16:41 Modified: 2 Dec 2015 17:49
Reporter: SQL Stunts Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S2 (Serious)
Version:5.7, 5.6.29, 5.7.11, 5.7.9 OS:Ubuntu (14.04)
Assigned to: CPU Architecture:Any
Tags: distinct, emptyset

[2 Dec 2015 16:41] SQL Stunts
Description:
After creating a full text targeting a column on a table, a "select distinct" query to retrieve the range of values returns an empty set.

If you use the count operator, it returns the real number of distinct occurences.

If you apply a function, like "UCASE", it returns the correct record set.

How to repeat:
Install the sample employee database:
https://dev.mysql.com/doc/employee/en/employees-installation.html

Query the titles table with the following query:
mysql> select distinct title from titles;
+--------------------+
| title              |
+--------------------+
| Senior Engineer    |
| Staff              |
| Engineer           |
| Senior Staff       |
| Assistant Engineer |
| Technique Leader   |
| Manager            |
+--------------------+
7 rows in set (0.38 sec)

Then create a fulltext index on the titles table, over the title column:
mysql> alter table titles add fulltext index `title` (`title`);
Query OK, 0 rows affected (14.65 sec)
Records: 0  Duplicates: 0  Warnings: 0

Issue the same query again:
mysql> select distinct title from titles;
Empty set (0.00 sec)

Repeat the query, but with COUNT:
mysql> select count(distinct title) from titles;
+-----------------------+
| count(distinct title) |
+-----------------------+
|                     7 |
+-----------------------+
1 row in set (0.24 sec)

Repeat the query with UCASE:
mysql> select distinct(ucase(title)) from titles;
+--------------------+
| (ucase(title))     |
+--------------------+
| SENIOR ENGINEER    |
| STAFF              |
| ENGINEER           |
| SENIOR STAFF       |
| ASSISTANT ENGINEER |
| TECHNIQUE LEADER   |
| MANAGER            |
+--------------------+
7 rows in set (0.44 sec)

So, how come the first query returns an empty set, when there are 7 records to display.

If you drop the FT index, the query returns the correct result set.
[2 Dec 2015 17:49] Umesh Shastry
Hello!

Thank you for the report.
Observed that 5.6.29, 5.7.9/11 are affected.

Thanks,
Umesh