wrong relevance ranking for full-text searches

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;

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)

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)

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)