Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
Description
results are different in mysql/mariadb, need check which ones are correct
testcase
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; |
in MariaDb 10.2
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 5.7
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)
|
Attachments
Issue Links
- is part of
-
MDEV-13626 Merge InnoDB test cases from MySQL 5.7
-
- Closed
-
- relates to
-
-
- Open
-
Activity
Similar to MDEV-14252, I think that this needs to be analyzed by thiru.
multiple_index.test - behaves correctly in MariaDB. In mysql, statistics n_rows value is 6 (wrong one). So it leads to different rank value.
To prove that the behaviour of test case changes after analyze table.
In 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
|
|
|
ANALYZE TABLE t1;
|
Table Op Msg_type Msg_text
|
test.t1 analyze status OK
|
|
|
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
|
plugin_debug test case needs code changes in mariadb.
also in plugin_debug test https://github.com/MariaDB/server/blob/mdev-13626/mysql-test/suite/innodb_fts/t/plugin_debug.test
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 WHEREMATCH(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 ParserCREATE TABLE articles (@@ -173,7 +172,6 @@SELECT * FROM articles WHEREMATCH(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 ParserSET GLOBAL innodb_ft_enable_diag_print = 1;@@ -232,15 +230,12 @@SELECT * FROM articles WHEREMATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE);id title body-1 MySQL Tutorial DBMS stands for MySQL DataBase ...SELECT * FROM articles WHEREMATCH(title, body) AGAINST('"full text search"' IN BOOLEAN MODE);id title body-5 1001 MySQL Tricks How to use full-text search engineSELECT * FROM articles WHEREMATCH(title, body) AGAINST('"full text search engine"' IN BOOLEAN MODE);id title body-5 1001 MySQL Tricks How to use full-text search engineSELECT * FROM articles WHEREMATCH(title, body) AGAINST('+"dbms stands" -good' IN BOOLEAN MODE);id title body@@ -257,7 +252,6 @@SELECT * FROM articles WHEREMATCH(title, body) AGAINST('+("dbms stands" "full text") -good' IN BOOLEAN MODE);id title body-5 1001 MySQL Tricks How to use full-text search engine1 MySQL Tutorial DBMS stands for MySQL DataBase ...SELECT * FROM articles WHEREMATCH(title, body) AGAINST('("msyql database")@3' IN BOOLEAN MODE);@@ -298,7 +292,6 @@SELECT * FROM articles WHEREMATCH(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";