[MDEV-29778] Having Unique index interference with MATCH from a FULLTEXT Created: 2022-10-12  Updated: 2022-10-25  Resolved: 2022-10-21

Status: Closed
Project: MariaDB Server
Component/s: Full-text Search, Storage Engine - InnoDB
Affects Version/s: 10.5.17, 10.6.7, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.3.37, 10.4.27, 10.5.18, 10.6.11, 10.7.7, 10.8.6, 10.9.4, 10.10.2, 10.11.1

Type: Bug Priority: Critical
Reporter: Anton Avramov Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 1
Labels: innodb, optimizer, performance
Environment:

Ubuntu 22.04, Debian Buster

Attachments: File MDEV-full-text.sql    

 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 |
 
+------+------+



 Comments   
Comment by Alice Sherepa [ 2022-10-12 ]

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)

Comment by Marko Mäkelä [ 2022-10-20 ]

OK to push.

Generated at Thu Feb 08 10:11:13 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.