Love to code, although it bugs me.

MySQL Bug #79497: Full text indexes and aggregate functions

No comments
Playing around with the Employees sample database and full text search on MySQL, I found a weird bug. 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.
So if you initially perform the following query, the outcome comes with 7 rows:
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)
If you 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)

So, apparently our records are gone? Repeat the query, but with COUNT:
mysql> select count(distinct title) from titles;
+-----------------------+
| count(distinct title) |
+-----------------------+
| 7 |
+-----------------------+
1 row in set (0.24 sec)
The records are not gone, they justo won't appear with the DISTINCT aggregate function. If you drop the FT index, the query returns the correct result set.
If dropping the index is not an option, you can workaround this bug, repeating 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)
This bug is already reported at MySQL Bugs. It's #79497. It appears to be an upstream issue, reproducible on current 5.6 and 5.7.


No comments :

Post a Comment