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

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.4(EOL)
Component/s: Full-text Search, Optimizer, Storage Engine - InnoDB, Storage Engine - XtraDB
Labels:
- upstream
Environment:
Ubuntu Server 14.04 64bit

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.

is duplicated by

MDEV-21426 Distinct on a fulltext indexed column not working

relates to

MDEV-24211 FULLTEXT search does not honor case sensitive _bin collations

links to

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server