Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.0, 10.1, 10.2, 10.3, 10.4
-
Ubuntu Server 14.04 64bit
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.
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Stalled
-
- links to