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.
