Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.6.7, 10.5.17, 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL)
-
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 |
|
+------+------+
|
Attachments
Issue Links
- duplicates
-
-
- Closed
-
Thank you for the report!
I repeated as described on 10.3-10.10.
As a temporary workaround one could use index hints and after that further select returned correct result.
MariaDB [test]> CREATE or replace TABLE ft_test(copy VARCHAR(255),FULLTEXT(copy), UNIQUE INDEX ft_test_unq (copy) USING BTREE) engine=innodb;Query OK, 0 rows affected (0,159 sec)MariaDB [test]> INSERT INTO ft_test(copy) VALUES ('test');Query OK, 1 row affected (0,009 sec)MariaDB [test]> SELECT sql_no_cache copy,MATCH(copy) AGAINST('test' IN BOOLEAN MODE) rel FROM ft_test;+------+------+| copy | rel |+------+------+| test | 0 |+------+------+1 row in set (0,001 sec)MariaDB [test]> show create table ft_test;+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| ft_test | CREATE TABLE `ft_test` (`copy` varchar(255) DEFAULT NULL,UNIQUE KEY `ft_test_unq` (`copy`) USING BTREE,FULLTEXT KEY `copy` (`copy`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0,001 sec)MariaDB [test]> SELECT sql_no_cache copy,MATCH(copy) AGAINST('test' IN BOOLEAN MODE) rel FROM ft_test ignore index (ft_test_unq);+------+----------------------------+| copy | rel |+------+----------------------------+| test | 0.000000001885928302414186 |+------+----------------------------+1 row in set (0,001 sec)MariaDB [test]> SELECT sql_no_cache copy,MATCH(copy) AGAINST('test' IN BOOLEAN MODE) rel FROM ft_test;+------+----------------------------+| copy | rel |+------+----------------------------+| test | 0.000000001885928302414186 |+------+----------------------------+1 row in set (0,001 sec)