Bug #76038 WRONG RELEVANCE RANKING FOR FULL TEXT SEARCHES WHEN FTS_DOC_ID IS PRIMARY KEY
Submitted: 25 Feb 2015 5:34 Modified: 1 Jun 2015 13:56
Reporter: Chaithra Marsur Gopala Reddy Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.6 OS:Any
Assigned to: CPU Architecture:Any

[25 Feb 2015 5:34] Chaithra Marsur Gopala Reddy 
Description:
An InnoDB full text search produces the wrong relevancy ranking
in case when FTS_DOC_ID is user created column and is made the primary key.

How to repeat:
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 fts_idx (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');

explain SELECT first_name, last_name, MATCH(first_name) AGAINST('Homer' IN BOOLEAN MODE) AS score FROM t1;
SELECT fts_doc_id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN BOOLEAN MODE) AS score FROM t1;

DROP TABLE t1;
[1 Jun 2015 13:56] Daniel Price 
Posted by developer:
 
Fixed as of the upcoming 5.6.26, 5.7.8, 5.8.0 releases, and here's the changelog entry:

Defining a user-created "FTS_DOC_ID" column as a primary key produced
incorrect full-text search relevancy rankings.
[16 Jun 2015 12:58] Daniel Price 
Posted by developer:
 
Removed 5.6.26 changelog entry. Thiru reverted 5.6.26 patch for Bug#20597981 and requested that 5.6.26 changelog entry be removed.