[#MDEV-9232] Fulltext index on a given column causes empty set for 'distinct' query

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.

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.

[MDEV-9232] Fulltext index on a given column causes empty set for 'distinct' query Created: 2015-12-02 Updated: 2023-04-27
Status:	Confirmed
Project:	MariaDB Server
Component/s:	Full-text Search, Optimizer, Storage Engine - InnoDB, Storage Engine - XtraDB
Affects Version/s:	10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s:	10.4