[#MDEV-14231] MATCH() AGAINST( IN BOOLEAN MODE), results mismatch

drop table if exists t1;

CREATE TABLE t1 (

id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

a VARCHAR(200),

b TEXT

) ENGINE = InnoDB STATS_PERSISTENT=0;

INSERT INTO t1 (a,b) VALUES

        ('MySQL Tutorial','DBMS stands for DataBase ...')  ,

        ('How To Use MySQL Well','After you went through a ...'),

        ('Optimizing MySQL','In this tutorial we will show ...');

ALTER TABLE t1 ADD FULLTEXT INDEX idx_1 (a);

ALTER TABLE t1 ADD FULLTEXT INDEX idx_2 (b);

SHOW CREATE TABLE t1;

START TRANSACTION;

INSERT INTO t1 (a,b) VALUES

        ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),

        ('MySQL vs. YourSQL','In the following database comparison ...'),

        ('MySQL Security','When configured properly, MySQL ...');

ROLLBACK;

select *, MATCH(a) AGAINST("Optimizing MySQL" IN BOOLEAN MODE) as x from t1;

MariaDB [test]> select *, MATCH(a) AGAINST("Optimizing MySQL" IN BOOLEAN MODE) as x from t1;

+----+-----------------------+-----------------------------------+----------------------------+

| id | a                     | b                                 | x                          |

+----+-----------------------+-----------------------------------+----------------------------+

|  1 | MySQL Tutorial        | DBMS stands for DataBase ...      | 0.000000001885928302414186 |

|  2 | How To Use MySQL Well | After you went through a ...      | 0.000000001885928302414186 |

|  3 | Optimizing MySQL      | In this tutorial we will show ... |        0.22764469683170319 |

+----+-----------------------+-----------------------------------+----------------------------+

3 rows in set (0.00 sec)

mysql> select *, MATCH(a) AGAINST("Optimizing MySQL" IN BOOLEAN MODE) as x from t1;

+----+-----------------------+-----------------------------------+--------------------+

| id | a                     | b                                 | x                  |

+----+-----------------------+-----------------------------------+--------------------+

|  1 | MySQL Tutorial        | DBMS stands for DataBase ...      | 0.0906190574169159 |

|  2 | How To Use MySQL Well | After you went through a ...      | 0.0906190574169159 |

|  3 | Optimizing MySQL      | In this tutorial we will show ... | 0.6961383819580078 |

+----+-----------------------+-----------------------------------+--------------------+

3 rows in set (0,00 sec)

CURRENT_TEST: innodb_fts.plugin_debug

--- /home/alice/aliska/10.2/mysql-test/suite/innodb_fts/r/plugin_debug.result	2017-11-02 13:01:39.827614000 +0100

+++ /home/alice/aliska/10.2/mysql-test/suite/innodb_fts/r/plugin_debug.reject	2017-11-02 15:33:28.110118532 +0100

@@ -1,4 +1,4 @@

-INSTALL PLUGIN simple_parser SONAME 'mypluglib.so';

+INSTALL PLUGIN simple_parser SONAME 'mypluglib';

 # Test Part 2: Create Index Test(CREATE TABLE WITH FULLTEXT INDEX)

 CREATE TABLE articles (

 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

@@ -131,7 +131,6 @@

 SELECT * FROM articles WHERE

 MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE);

 id	title	body

-1	MySQL Tutorial	DBMS stands for MySQL DataBase ...

 DROP TABLE articles;

 # Test Part 5: Row Merge Create Index Test with Default/Internal Parser

 CREATE TABLE articles (

@@ -173,7 +172,6 @@

 SELECT * FROM articles WHERE

 MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE);

 id	title	body

-1	MySQL Tutorial	DBMS stands for MySQL DataBase ...

 DROP TABLE articles;

 # Test Part 6: Test Query Parser with Default/Internal Parser

 SET GLOBAL innodb_ft_enable_diag_print = 1;

@@ -232,15 +230,12 @@

 SELECT * FROM articles WHERE

 MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE);

 id	title	body

-1	MySQL Tutorial	DBMS stands for MySQL DataBase ...

 SELECT * FROM articles WHERE

 MATCH(title, body) AGAINST('"full text search"' IN BOOLEAN MODE);

 id	title	body

-5	1001 MySQL Tricks	How to use full-text search engine

 SELECT * FROM articles WHERE

 MATCH(title, body) AGAINST('"full text search engine"' IN BOOLEAN MODE);

 id	title	body

-5	1001 MySQL Tricks	How to use full-text search engine

 SELECT * FROM articles WHERE

 MATCH(title, body) AGAINST('+"dbms stands" -good' IN BOOLEAN MODE);

 id	title	body

@@ -257,7 +252,6 @@

 SELECT * FROM articles WHERE

 MATCH(title, body) AGAINST('+("dbms stands" "full text") -good' IN BOOLEAN MODE);

 id	title	body

-5	1001 MySQL Tricks	How to use full-text search engine

 1	MySQL Tutorial	DBMS stands for MySQL DataBase ...

 SELECT * FROM articles WHERE

 MATCH(title, body) AGAINST('("msyql database")@3' IN BOOLEAN MODE);

@@ -298,7 +292,6 @@

 SELECT * FROM articles WHERE

 MATCH(title, body) AGAINST('"计算机 数据库"' IN BOOLEAN MODE);

 id	title	body

-3	要 想 将 数据 添加 到 数据库	或 访问、处理 计算机 数据库 中 保存 的 数据

 DROP TABLE articles;

 SET GLOBAL innodb_ft_enable_diag_print = 0;

 SET SESSION DEBUG_DBUG="-d,fts_instrument_use_default_parser";

Merge InnoDB test cases from MySQL 5.7 (MDEV-13626) [MDEV-14231] MATCH() AGAINST( IN BOOLEAN MODE), results mismatch Created: 2017-10-31 Updated: 2023-11-08
Status:	Open
Project:	MariaDB Server
Component/s:	Full-text Search, Storage Engine - InnoDB, Tests
Affects Version/s:	10.2, 10.3, 10.4
Fix Version/s:	10.4, 10.5, 10.6