Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5.17, 10.6.7, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
-
Ubuntu 22.04, Debian Buster
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 |
|
+------+------+
|