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

Distinct on a fulltext indexed column not working

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.0, 10.1, 10.2, 10.3, 10.4, 10.4.10
    • Fix Version/s: N/A
    • Component/s: Full-text Search
    • Labels:
      None

      Description

      we want to return all unique values from a column (using distinct), the column has a fulltext index defined.
      In this case a simple select does not return any values, removing the DISTINCT or the fulltext index solves the problem.

      MariaDB [regressiondb]> SELECT DISTINCT tagResult AS name FROM regressRun_tagResult WHERE tagResult LIKE 'esm_202-start_18677%';
      Empty set (0.00 sec)
       
      MariaDB [regressiondb]> SELECT tagResult AS name FROM regressRun_tagResult WHERE tagResult LIKE 'esm_202-start_18677%' ORDER BY tagResult;
      +----------------------------------------------+
      | name                                         |
      +----------------------------------------------+
      | esm_202-start_18677#date=2019/12/18_16:48:24 |
      +----------------------------------------------+
      1 row in set (0.15 sec)
       
      MariaDB [regressiondb]> show create table regressRun_tagResult;
      +----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table                | Create Table                                                                                                                                                                                                                                                                               |
      +----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | regressRun_tagResult | CREATE TABLE `regressRun_tagResult` (
        `regressRun_id` int(11) unsigned NOT NULL,
        `tagResult` varchar(255) COLLATE latin1_bin NOT NULL,
        PRIMARY KEY (`regressRun_id`,`tagResult`),
        FULLTEXT KEY `tagResult` (`tagResult`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin |
      +----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [regressiondb]> alter table regressRun_tagResult drop index tagResult;
      Query OK, 0 rows affected (3.98 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [regressiondb]> SELECT DISTINCT tagResult AS name FROM regressRun_tagResult WHERE tagResult LIKE 'esm_202-start_18677%';
      +----------------------------------------------+
      | name                                         |
      +----------------------------------------------+
      | esm_202-start_18677#date=2019/12/18_16:48:24 |
      +----------------------------------------------+
      1 row in set (0.11 sec)
       
      MariaDB [regressiondb]> SELECT tagResult AS name FROM regressRun_tagResult WHERE tagResult LIKE 'esm_202-start_18677%' ORDER BY tagResult;
      +----------------------------------------------+
      | name                                         |
      +----------------------------------------------+
      | esm_202-start_18677#date=2019/12/18_16:48:24 |
      +----------------------------------------------+
      1 row in set (0.16 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              Maikel Punie Maikel Punie
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: