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

Having Unique index interference with MATCH from a FULLTEXT

    XMLWordPrintable

Details

    Description

      We waned to use the score of the MATCH function on a FULLTEXT index to show relevance of the result.
      For one of the column/tables we didn't get a score event with a full match.
      After further investigation we discovered that if we have an UNIQUE INDEX on the same column on an InnoDB table the MATCH stops working.
      Here is a simple script to reproduce (also attached)

      CREATE TABLE IF NOT EXISTS ft_test(copy VARCHAR(255),FULLTEXT(copy));
       
      INSERT INTO ft_test(copy) VALUES ('test');
       
      SELECT copy,MATCH(copy) AGAINST('test' IN BOOLEAN MODE) rel FROM ft_test;
       
      ALTER TABLE ft_test ADD UNIQUE INDEX ft_test_unq (copy) USING BTREE;
       
      SELECT copy,MATCH(copy) AGAINST('test' IN BOOLEAN MODE) rel FROM ft_test;
      

      The first select would produce correct result as:

      +------+----------------------------+
      | copy | rel                        |
      +------+----------------------------+
      | test | 0.000000001885928302414186 |
      +------+----------------------------+
      
      

      Where as the second would return:

      +------+------+
      | copy | rel  |
      +------+------+
      | test |    0 |
      +------+------+
      

      Attachments

        Issue Links

          Activity

            People

              thiru Thirunarayanan Balathandayuthapani
              lukav Anton Avramov
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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