Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9232

Fulltext index on a given column causes empty set for 'distinct' query

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              thiru Thirunarayanan Balathandayuthapani
              Reporter:
              stuntman Joao Osorio
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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