Merge InnoDB test cases from MySQL 5.7 (MDEV-13626)

[MDEV-14232] innodb_fts.index_table test failed, result mismatch Created: 2017-10-31  Updated: 2023-11-08  Resolved: 2023-11-08

Status: Closed
Project: MariaDB Server
Component/s: Full-text Search, Tests
Affects Version/s: 10.2
Fix Version/s: 10.4.33, 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3, 11.3.2

Type: Technical task Priority: Minor
Reporter: Alice Sherepa Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None


 Description   

https://github.com/MariaDB/server/tree/bb-10.2-alice/mysql-test/suite/innodb_fts/t/index_table.test
results from mysql 5.7 differ from MariaDB, needs a check which of them are correct

CURRENT_TEST: innodb_fts.index_table
 
--- /home/alice/aliska/10.2/mysql-test/suite/innodb_fts/r/index_table.result	2017-06-22 16:13:19.000000000 +0200
 
+++ /home/alice/aliska/10.2/mysql-test/suite/innodb_fts/r/index_table.reject	2017-10-31 13:38:33.669007403 +0100
 
@@ -16,111 +16,111 @@
 
 SET GLOBAL innodb_ft_aux_table="test/articles";
 
 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
 
 WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
 
-1001	6	6	1	6	0
 
-after	4	4	1	4	22
 
-database	3	3	1	3	37
 
-dbms	3	3	1	3	15
 
-engine	6	7	2	6	46
 
-engine	6	7	2	7	44
 
-full	6	7	2	6	29
 
-full	6	7	2	7	27
 
-mysql	3	7	5	3	0
 
-mysql	3	7	5	3	31
 
-mysql	3	7	5	4	11
 
-mysql	3	7	5	5	11
 
-mysql	3	7	5	6	5
 
-mysql	3	7	5	7	3
 
-optimizing	5	5	1	5	0
 
-search	6	7	2	6	39
 
-search	6	7	2	7	37
 
-show	5	5	1	5	42
 
-stands	3	3	1	3	20
 
-text	6	7	2	6	34
 
-text	6	7	2	7	32
 
-through	4	4	1	4	37
 
-tricks	6	7	2	6	11
 
-tricks	6	7	2	7	9
 
-tutorial	3	5	2	3	6
 
-tutorial	3	5	2	5	25
 
-use	4	7	3	4	7
 
-use	4	7	3	6	25
 
-use	4	7	3	7	23
 
-well	4	4	1	4	17
 
-went	4	4	1	4	32
 
-you	4	4	1	4	28
 
+1001	5	5	1	5	0
 
+after	3	3	1	3	22
 
+database	2	2	1	2	37
 
+dbms	2	2	1	2	15
 
+engine	5	6	2	5	46
 
+engine	5	6	2	6	44
 
+full	5	6	2	5	29
 
+full	5	6	2	6	27
 
+mysql	2	6	5	2	0
 
+mysql	2	6	5	2	31
 
+mysql	2	6	5	3	11
 
+mysql	2	6	5	4	11
 
+mysql	2	6	5	5	5
 
+mysql	2	6	5	6	3
 
+optimizing	4	4	1	4	0
 
+search	5	6	2	5	39
 
+search	5	6	2	6	37
 
+show	4	4	1	4	42
 
+stands	2	2	1	2	20
 
+text	5	6	2	5	34
 
+text	5	6	2	6	32
 
+through	3	3	1	3	37
 
+tricks	5	6	2	5	11
 
+tricks	5	6	2	6	9
 
+tutorial	2	4	2	2	6
 
+tutorial	2	4	2	4	25
 
+use	3	6	3	3	7
 
+use	3	6	3	5	25
 
+use	3	6	3	6	23
 
+well	3	3	1	3	17
 
+went	3	3	1	3	32
 
+you	3	3	1	3	28
 
 OPTIMIZE TABLE articles;
 
 Table	Op	Msg_type	Msg_text
 
 test.articles	optimize	status	OK
 
 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
 
 WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
 
-1001	6	6	1	6	0
 
-after	4	4	1	4	22
 
-database	3	3	1	3	37
 
-dbms	3	3	1	3	15
 
-engine	6	7	2	6	46
 
-engine	6	7	2	7	44
 
-full	6	7	2	6	29
 
-full	6	7	2	7	27
 
-mysql	3	7	5	3	0
 
-mysql	3	7	5	3	31
 
-mysql	3	7	5	4	11
 
-mysql	3	7	5	5	11
 
-mysql	3	7	5	6	5
 
-mysql	3	7	5	7	3
 
-optimizing	5	5	1	5	0
 
-search	6	7	2	6	39
 
-search	6	7	2	7	37
 
-show	5	5	1	5	42
 
-stands	3	3	1	3	20
 
-text	6	7	2	6	34
 
-text	6	7	2	7	32
 
-through	4	4	1	4	37
 
-tricks	6	7	2	6	11
 
-tricks	6	7	2	7	9
 
-tutorial	3	5	2	3	6
 
-tutorial	3	5	2	5	25
 
-use	4	7	3	4	7
 
-use	4	7	3	6	25
 
-use	4	7	3	7	23
 
-well	4	4	1	4	17
 
-went	4	4	1	4	32
 
-you	4	4	1	4	28
 
-SET SESSION debug="+d,fts_instrument_result_cache_limit";
 
+1001	5	5	1	5	0
 
+after	3	3	1	3	22
 
+database	2	2	1	2	37
 
+dbms	2	2	1	2	15
 
+engine	5	6	2	5	46
 
+engine	5	6	2	6	44
 
+full	5	6	2	5	29
 
+full	5	6	2	6	27
 
+mysql	2	6	5	2	0
 
+mysql	2	6	5	2	31
 
+mysql	2	6	5	3	11
 
+mysql	2	6	5	4	11
 
+mysql	2	6	5	5	5
 
+mysql	2	6	5	6	3
 
+optimizing	4	4	1	4	0
 
+search	5	6	2	5	39
 
+search	5	6	2	6	37
 
+show	4	4	1	4	42
 
+stands	2	2	1	2	20
 
+text	5	6	2	5	34
 
+text	5	6	2	6	32
 
+through	3	3	1	3	37
 
+tricks	5	6	2	5	11
 
+tricks	5	6	2	6	9
 
+tutorial	2	4	2	2	6
 
+tutorial	2	4	2	4	25
 
+use	3	6	3	3	7
 
+use	3	6	3	5	25
 
+use	3	6	3	6	23
 
+well	3	3	1	3	17
 
+went	3	3	1	3	32
 
+you	3	3	1	3	28
 
+SET SESSION debug_dbug="+d,fts_instrument_result_cache_limit";
 
 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
 
 WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
 
-1001	6	6	1	6	0
 
-after	4	4	1	4	22
 
-database	3	3	1	3	37
 
-dbms	3	3	1	3	15
 
-engine	6	7	2	6	46
 
-engine	6	7	2	7	44
 
-full	6	7	2	6	29
 
-full	6	7	2	7	27
 
-mysql	3	7	5	3	0
 
-mysql	3	7	5	3	31
 
-mysql	3	7	5	4	11
 
-mysql	3	7	5	5	11
 
-mysql	3	7	5	6	5
 
-mysql	3	7	5	7	3
 
-optimizing	5	5	1	5	0
 
-search	6	7	2	6	39
 
-search	6	7	2	7	37
 
-show	5	5	1	5	42
 
-stands	3	3	1	3	20
 
-text	6	7	2	6	34
 
-text	6	7	2	7	32
 
-through	4	4	1	4	37
 
-tricks	6	7	2	6	11
 
-tricks	6	7	2	7	9
 
-tutorial	3	5	2	3	6
 
-tutorial	3	5	2	5	25
 
-use	4	7	3	4	7
 
-use	4	7	3	6	25
 
-use	4	7	3	7	23
 
-well	4	4	1	4	17
 
-went	4	4	1	4	32
 
-you	4	4	1	4	28
 
-SET SESSION debug="-d,fts_instrument_result_cache_limit";
 
+1001	5	5	1	5	0
 
+after	3	3	1	3	22
 
+database	2	2	1	2	37
 
+dbms	2	2	1	2	15
 
+engine	5	6	2	5	46
 
+engine	5	6	2	6	44
 
+full	5	6	2	5	29
 
+full	5	6	2	6	27
 
+mysql	2	6	5	2	0
 
+mysql	2	6	5	2	31
 
+mysql	2	6	5	3	11
 
+mysql	2	6	5	4	11
 
+mysql	2	6	5	5	5
 
+mysql	2	6	5	6	3
 
+optimizing	4	4	1	4	0
 
+search	5	6	2	5	39
 
+search	5	6	2	6	37
 
+show	4	4	1	4	42
 
+stands	2	2	1	2	20
 
+text	5	6	2	5	34
 
+text	5	6	2	6	32
 
+through	3	3	1	3	37
 
+tricks	5	6	2	5	11
 
+tricks	5	6	2	6	9
 
+tutorial	2	4	2	2	6
 
+tutorial	2	4	2	4	25
 
+use	3	6	3	3	7
 
+use	3	6	3	5	25
 
+use	3	6	3	6	23
 
+well	3	3	1	3	17
 
+went	3	3	1	3	32
 
+you	3	3	1	3	28
 
+SET SESSION debug_dbug="-d,fts_instrument_result_cache_limit";
 
 DROP TABLE articles;
 
 SET GLOBAL innodb_ft_result_cache_limit=default;
 
 CREATE TABLE articles (
 
@@ -140,76 +140,76 @@
 
 ('Go MySQL Tricks','How to use full text search engine');
 
 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
 
 WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
 
-1001	6	6	1	6	0
 
-mysql	3	7	5	3	0
 
-mysql	3	7	5	4	11
 
-mysql	3	7	5	5	11
 
-mysql	3	7	5	6	5
 
-mysql	3	7	5	7	3
 
-optimizing	5	5	1	5	0
 
-tricks	6	7	2	6	11
 
-tricks	6	7	2	7	9
 
-tutorial	3	3	1	3	6
 
-use	4	4	1	4	7
 
-well	4	4	1	4	17
 
-after	4	4	1	4	0
 
-database	3	3	1	3	22
 
-dbms	3	3	1	3	0
 
-engine	6	7	2	6	28
 
-engine	6	7	2	7	28
 
-full	6	7	2	6	11
 
-full	6	7	2	7	11
 
-mysql	3	3	1	3	16
 
-search	6	7	2	6	21
 
-search	6	7	2	7	21
 
-show	5	5	1	5	25
 
-stands	3	3	1	3	5
 
-text	6	7	2	6	16
 
-text	6	7	2	7	16
 
-through	4	4	1	4	15
 
-tutorial	5	5	1	5	8
 
-use	6	7	2	6	7
 
-use	6	7	2	7	7
 
-went	4	4	1	4	10
 
-you	4	4	1	4	6
 
+1001	5	5	1	5	0
 
+mysql	2	6	5	2	0
 
+mysql	2	6	5	3	11
 
+mysql	2	6	5	4	11
 
+mysql	2	6	5	5	5
 
+mysql	2	6	5	6	3
 
+optimizing	4	4	1	4	0
 
+tricks	5	6	2	5	11
 
+tricks	5	6	2	6	9
 
+tutorial	2	2	1	2	6
 
+use	3	3	1	3	7
 
+well	3	3	1	3	17
 
+after	3	3	1	3	0
 
+database	2	2	1	2	22
 
+dbms	2	2	1	2	0
 
+engine	5	6	2	5	28
 
+engine	5	6	2	6	28
 
+full	5	6	2	5	11
 
+full	5	6	2	6	11
 
+mysql	2	2	1	2	16
 
+search	5	6	2	5	21
 
+search	5	6	2	6	21
 
+show	4	4	1	4	25
 
+stands	2	2	1	2	5
 
+text	5	6	2	5	16
 
+text	5	6	2	6	16
 
+through	3	3	1	3	15
 
+tutorial	4	4	1	4	8
 
+use	5	6	2	5	7
 
+use	5	6	2	6	7
 
+went	3	3	1	3	10
 
+you	3	3	1	3	6
 
 SET GLOBAL innodb_ft_aux_table="test/articles";
 
 OPTIMIZE TABLE articles;
 
 Table	Op	Msg_type	Msg_text
 
 test.articles	optimize	status	OK
 
 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
 
 WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
 
-1001	6	6	1	6	0
 
-mysql	3	7	5	3	0
 
-mysql	3	7	5	4	11
 
-mysql	3	7	5	5	11
 
-mysql	3	7	5	6	5
 
-mysql	3	7	5	7	3
 
-optimizing	5	5	1	5	0
 
-tricks	6	7	2	6	11
 
-tricks	6	7	2	7	9
 
-tutorial	3	3	1	3	6
 
-use	4	4	1	4	7
 
-well	4	4	1	4	17
 
-after	4	4	1	4	0
 
-database	3	3	1	3	22
 
-dbms	3	3	1	3	0
 
-engine	6	7	2	6	28
 
-engine	6	7	2	7	28
 
-full	6	7	2	6	11
 
-full	6	7	2	7	11
 
-mysql	3	3	1	3	16
 
-search	6	7	2	6	21
 
-search	6	7	2	7	21
 
-show	5	5	1	5	25
 
-stands	3	3	1	3	5
 
-text	6	7	2	6	16
 
-text	6	7	2	7	16
 
-through	4	4	1	4	15
 
-tutorial	5	5	1	5	8
 
-use	6	7	2	6	7
 
-use	6	7	2	7	7
 
-went	4	4	1	4	10
 
-you	4	4	1	4	6
 
+1001	5	5	1	5	0
 
+mysql	2	6	5	2	0
 
+mysql	2	6	5	3	11
 
+mysql	2	6	5	4	11
 
+mysql	2	6	5	5	5
 
+mysql	2	6	5	6	3
 
+optimizing	4	4	1	4	0
 
+tricks	5	6	2	5	11
 
+tricks	5	6	2	6	9
 
+tutorial	2	2	1	2	6
 
+use	3	3	1	3	7
 
+well	3	3	1	3	17
 
+after	3	3	1	3	0
 
+database	2	2	1	2	22
 
+dbms	2	2	1	2	0
 
+engine	5	6	2	5	28
 
+engine	5	6	2	6	28
 
+full	5	6	2	5	11
 
+full	5	6	2	6	11
 
+mysql	2	2	1	2	16
 
+search	5	6	2	5	21
 
+search	5	6	2	6	21
 
+show	4	4	1	4	25
 
+stands	2	2	1	2	5
 
+text	5	6	2	5	16
 
+text	5	6	2	6	16
 
+through	3	3	1	3	15
 
+tutorial	4	4	1	4	8
 
+use	5	6	2	5	7
 
+use	5	6	2	6	7
 
+went	3	3	1	3	10
 
+you	3	3	1	3	6
 
 DROP TABLE articles;
 
 SET NAMES utf8;
 
 CREATE TABLE articles (
 
@@ -224,19 +224,19 @@
 
 SET GLOBAL innodb_ft_aux_table="test/articles";
 
 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
 
 WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
 
-充满可爱	5	5	1	5	0
 
-怜香惜爱	4	4	1	4	0
 
-爱恨交织	6	6	1	6	0
 
-相亲相爱	3	3	1	3	0
 
+充满可爱	4	4	1	4	0
 
+怜香惜爱	3	3	1	3	0
 
+爱恨交织	5	5	1	5	0
 
+相亲相爱	2	2	1	2	0
 
 OPTIMIZE TABLE articles;
 
 Table	Op	Msg_type	Msg_text
 
 test.articles	optimize	status	OK
 
 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
 
 WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
 
-充满可爱	5	5	1	5	0
 
-怜香惜爱	4	4	1	4	0
 
-爱恨交织	6	6	1	6	0
 
-相亲相爱	3	3	1	3	0
 
+充满可爱	4	4	1	4	0
 
+怜香惜爱	3	3	1	3	0
 
+爱恨交织	5	5	1	5	0
 
+相亲相爱	2	2	1	2	0
 
 DROP TABLE articles;
 
 CREATE TABLE articles (
 
 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
 
@@ -250,19 +250,19 @@
 
 SET GLOBAL innodb_ft_aux_table="test/articles";
 
 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
 
 WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
 
-爱恨交织	6	6	1	6	0
 
-充满可爱	5	5	1	5	0
 
-怜香惜爱	4	4	1	4	0
 
-相亲相爱	3	3	1	3	0
 
+爱恨交织	5	5	1	5	0
 
+充满可爱	4	4	1	4	0
 
+怜香惜爱	3	3	1	3	0
 
+相亲相爱	2	2	1	2	0
 
 OPTIMIZE TABLE articles;
 
 Table	Op	Msg_type	Msg_text
 
 test.articles	optimize	status	OK
 
 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
 
 WORD	FIRST_DOC_ID	LAST_DOC_ID	DOC_COUNT	DOC_ID	POSITION
 
-怜香惜爱	4	4	1	4	0
 
-充满可爱	5	5	1	5	0
 
-相亲相爱	3	3	1	3	0
 
-爱恨交织	6	6	1	6	0
 
+怜香惜爱	3	3	1	3	0
 
+充满可爱	4	4	1	4	0
 
+相亲相爱	2	2	1	2	0
 
+爱恨交织	5	5	1	5	0
 
 DROP TABLE articles;
 
 SET GLOBAL innodb_ft_aux_table=default;
 
 SET GLOBAL INNODB_OPTIMIZE_FULLTEXT_ONLY=default;
 
 
 
mysqltest: Result length mismatch



 Comments   
Comment by Marko Mäkelä [ 2023-11-07 ]

MySQL generates FTS_DOC_ID that are 1 more than those of MariaDB. According to thiru, this is because fts_get_next_doc_id() in MariaDB works differently. We had changed that in MDEV-12676.

When I adapted this test in MDEV-13626 for the current 10.4, another difference was that INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE in MariaDB would report an empty result if the table that was identified by innodb_ft_aux_table was dropped and re-created.

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