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

          Activity

            People

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