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

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

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

            Thanks for the report.

            It appears to be an upstream issue, reproducible on current 5.6 and 5.7. In such cases we normally also report bugs at bugs.mysql.com to inform MySQL. Are you willing to do it, or should we do it on your behalf?

            It's possible that the bug has already been filed there, but things like that are not easy to find. I tried and couldn't.

            elenst Elena Stepanova added a comment - Thanks for the report. It appears to be an upstream issue, reproducible on current 5.6 and 5.7. In such cases we normally also report bugs at bugs.mysql.com to inform MySQL. Are you willing to do it, or should we do it on your behalf? It's possible that the bug has already been filed there, but things like that are not easy to find. I tried and couldn't.
            stuntman Joao Osorio added a comment -

            Hi Elena,

            I already filed this on MySQL also:
            https://bugs.mysql.com/79497

            Kind regards,
            Joao.

            stuntman Joao Osorio added a comment - Hi Elena, I already filed this on MySQL also: https://bugs.mysql.com/79497 Kind regards, Joao.
            alice Alice Sherepa added a comment -

            still reproducible with Mysql (8.0.18)

            alice Alice Sherepa added a comment - still reproducible with Mysql (8.0.18)

            People

              thiru Thirunarayanan Balathandayuthapani
              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.