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

wrong relevance ranking for full-text searches

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.2
    • Fix Version/s: 10.2
    • Component/s: Full-text Search
    • Labels:
      None

      Description

      innodb_fts.fulltext2.test (from mysql 5.7)

      CREATE TABLE t1 (
        FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        id int(10) not null ,
        first_name varchar(50) NOT NULL,
        last_name varchar(50) NOT NULL,
        PRIMARY KEY (FTS_DOC_ID),
        UNIQUE KEY idx_1 (first_name, last_name),
        FULLTEXT KEY `idx_2` (first_name)
      ) ENGINE=InnoDB;
       
      INSERT INTO t1 (id, first_name, last_name) VALUES
      (10, 'Bart', 'Simpson'),
      (11, 'Homer', 'Simpson'),
      (12, 'Marge', 'Simpson'),
      (13, 'Lisa', 'Simpson'),
      (14, 'Maggie', 'Simpson'),
      (15, 'Ned', 'Flanders'),
      (16, 'Nelson', 'Muntz');
       
      analyze table t1;
      SELECT fts_doc_id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN BOOLEAN MODE) AS score FROM t1;
      

      in MariaDB 10.2.10

      MariaDB [test]> SELECT fts_doc_id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN BOOLEAN MODE) AS score FROM t1;
      +------------+------------+-----------+-------+
      | fts_doc_id | first_name | last_name | score |
      +------------+------------+-----------+-------+
      |          1 | Bart       | Simpson   |     0 |
      |          2 | Homer      | Simpson   |     0 |
      |          4 | Lisa       | Simpson   |     0 |
      |          5 | Maggie     | Simpson   |     0 |
      |          3 | Marge      | Simpson   |     0 |
      |          6 | Ned        | Flanders  |     0 |
      |          7 | Nelson     | Muntz     |     0 |
      +------------+------------+-----------+-------+
      7 rows in set (0.00 sec)
      

      in mysql 5.7

      mysql> SELECT fts_doc_id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN BOOLEAN MODE) AS score FROM t1;
      +------------+------------+-----------+--------------------+
      | fts_doc_id | first_name | last_name | score              |
      +------------+------------+-----------+--------------------+
      |          1 | Bart       | Simpson   |                  0 |
      |          2 | Homer      | Simpson   | 0.7141907215118408 |
      |          4 | Lisa       | Simpson   |                  0 |
      |          5 | Maggie     | Simpson   |                  0 |
      |          3 | Marge      | Simpson   |                  0 |
      |          6 | Ned        | Flanders  |                  0 |
      |          7 | Nelson     | Muntz     |                  0 |
      +------------+------------+-----------+--------------------+
      7 rows in set (0,05 sec)
      

      also without primary key, result is different in mysql/ please check that it is correct.

      MariaDB [test]> CREATE TABLE t1 (
          ->   FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL,
          ->   id int(10) not null ,
          ->   first_name varchar(50) NOT NULL,
          ->   last_name varchar(50) NOT NULL,
          ->   UNIQUE KEY idx_1 (first_name, last_name),
          ->   FULLTEXT KEY `idx_2` (first_name)
          -> ) ENGINE=InnoDB;
      Query OK, 0 rows affected (1.74 sec)
       
      MariaDB [test]> INSERT INTO t1 VALUES
          -> (1,10, 'Bart', 'Simpson'),
          -> (2,11, 'Homer', 'Simpson'),
          -> (3,12, 'Marge', 'Simpson'),
          -> (4,13, 'Lisa', 'Simpson'),
          -> (5,14, 'Maggie', 'Simpson'),
          -> (6,15, 'Ned', 'Flanders'),
          -> (7,16, 'Nelson', 'Muntz');
      Query OK, 7 rows affected (0.04 sec)
      Records: 7  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> SELECT fts_doc_id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN BOOLEAN MODE) AS score FROM t1;
      +------------+------------+-----------+--------------------+
      | fts_doc_id | first_name | last_name | score              |
      +------------+------------+-----------+--------------------+
      |          1 | Bart       | Simpson   |                  0 |
      |          2 | Homer      | Simpson   | 0.8155715465545654 |
      |          4 | Lisa       | Simpson   |                  0 |
      |          5 | Maggie     | Simpson   |                  0 |
      |          3 | Marge      | Simpson   |                  0 |
      |          6 | Ned        | Flanders  |                  0 |
      |          7 | Nelson     | Muntz     |                  0 |
      +------------+------------+-----------+--------------------+
      7 rows in set (0.00 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              alice Alice Sherepa
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.