mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-02-06 05:42:19 +01:00
Code Issues Projects Releases Packages Wiki Activity Actions
mariadb/mysql-test/suite/innodb_fts/r/index_table.result
Marko Mäkelä 228b7e4db5 MDEV-13626 Merge InnoDB test cases from MySQL 5.7 
This imports and adapts a number of MySQL 5.7 test cases that are
applicable to MariaDB.

Some tests for old bug fixes are not that relevant because the code
has been refactored since then (especially starting with
MariaDB Server 10.6), and the tests would not reproduce the
original bug if the fix was reverted.

In the test innodb_fts.opt, there are many duplicate MATCH ranks, which
would make the results nondeterministic. The test was stabilized by
changing some LIMIT clauses or by adding sorted_result in those cases
where the purpose of a test was to show that no sorting took place
in the server.

In the test innodb_fts.phrase, MySQL 5.7 would generate FTS_DOC_ID that
are 1 larger than in MariaDB. In innodb_fts.index_table the difference is 2.
This is because in MariaDB, fts_get_next_doc_id() post-increments
cache->next_doc_id, while MySQL 5.7 pre-increments it.

Reviewed by: Thirunarayanan Balathandayuthapani
2023-11-08 12:17:14 +02:00

265 lines
6.8 KiB
Text
Raw Permalink Blame History

SET @optimize=@@GLOBAL.INNODB_OPTIMIZE_FULLTEXT_ONLY;
SET GLOBAL INNODB_OPTIMIZE_FULLTEXT_ONLY=1;
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
content TEXT
) ENGINE= InnoDB;
CREATE FULLTEXT INDEX idx ON articles (title, content);
INSERT INTO articles (title, content) VALUES
('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','How to use full-text search engine'),
('Go MySQL Tricks','How to use full text search engine');
SET @aux=@@GLOBAL.innodb_ft_aux_table;
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 4 4 1 4 0
after 2 2 1 2 22
database 1 1 1 1 37
dbms 1 1 1 1 15
engine 4 5 2 4 46
engine 4 5 2 5 44
full 4 5 2 4 29
full 4 5 2 5 27
mysql 1 5 5 1 0
mysql 1 5 5 1 31
mysql 1 5 5 2 11
mysql 1 5 5 3 11
mysql 1 5 5 4 5
mysql 1 5 5 5 3
optimizing 3 3 1 3 0
search 4 5 2 4 39
search 4 5 2 5 37
show 3 3 1 3 42
stands 1 1 1 1 20
text 4 5 2 4 34
text 4 5 2 5 32
through 2 2 1 2 37
tricks 4 5 2 4 11
tricks 4 5 2 5 9
tutorial 1 3 2 1 6
tutorial 1 3 2 3 25
use 2 5 3 2 7
use 2 5 3 4 25
use 2 5 3 5 23
well 2 2 1 2 17
went 2 2 1 2 32
you 2 2 1 2 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 4 4 1 4 0
after 2 2 1 2 22
database 1 1 1 1 37
dbms 1 1 1 1 15
engine 4 5 2 4 46
engine 4 5 2 5 44
full 4 5 2 4 29
full 4 5 2 5 27
mysql 1 5 5 1 0
mysql 1 5 5 1 31
mysql 1 5 5 2 11
mysql 1 5 5 3 11
mysql 1 5 5 4 5
mysql 1 5 5 5 3
optimizing 3 3 1 3 0
search 4 5 2 4 39
search 4 5 2 5 37
show 3 3 1 3 42
stands 1 1 1 1 20
text 4 5 2 4 34
text 4 5 2 5 32
through 2 2 1 2 37
tricks 4 5 2 4 11
tricks 4 5 2 5 9
tutorial 1 3 2 1 6
tutorial 1 3 2 3 25
use 2 5 3 2 7
use 2 5 3 4 25
use 2 5 3 5 23
well 2 2 1 2 17
went 2 2 1 2 32
you 2 2 1 2 28
SET @save_dbug=@@debug_dbug;
SET 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 4 4 1 4 0
after 2 2 1 2 22
database 1 1 1 1 37
dbms 1 1 1 1 15
engine 4 5 2 4 46
engine 4 5 2 5 44
full 4 5 2 4 29
full 4 5 2 5 27
mysql 1 5 5 1 0
mysql 1 5 5 1 31
mysql 1 5 5 2 11
mysql 1 5 5 3 11
mysql 1 5 5 4 5
mysql 1 5 5 5 3
optimizing 3 3 1 3 0
search 4 5 2 4 39
search 4 5 2 5 37
show 3 3 1 3 42
stands 1 1 1 1 20
text 4 5 2 4 34
text 4 5 2 5 32
through 2 2 1 2 37
tricks 4 5 2 4 11
tricks 4 5 2 5 9
tutorial 1 3 2 1 6
tutorial 1 3 2 3 25
use 2 5 3 2 7
use 2 5 3 4 25
use 2 5 3 5 23
well 2 2 1 2 17
went 2 2 1 2 32
you 2 2 1 2 28
SET debug_dbug=@save_dbug;
DROP TABLE articles;
SET GLOBAL innodb_ft_result_cache_limit=default;
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
content TEXT
) ENGINE= InnoDB;
CREATE FULLTEXT INDEX idx_t ON articles (title);
CREATE FULLTEXT INDEX idx_c ON articles (content);
INSERT INTO articles (title, content) VALUES
('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','How to use full-text search engine'),
('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
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 4 4 1 4 0
mysql 1 5 5 1 0
mysql 1 5 5 2 11
mysql 1 5 5 3 11
mysql 1 5 5 4 5
mysql 1 5 5 5 3
optimizing 3 3 1 3 0
tricks 4 5 2 4 11
tricks 4 5 2 5 9
tutorial 1 1 1 1 6
use 2 2 1 2 7
well 2 2 1 2 17
after 2 2 1 2 0
database 1 1 1 1 22
dbms 1 1 1 1 0
engine 4 5 2 4 28
engine 4 5 2 5 28
full 4 5 2 4 11
full 4 5 2 5 11
mysql 1 1 1 1 16
search 4 5 2 4 21
search 4 5 2 5 21
show 3 3 1 3 25
stands 1 1 1 1 5
text 4 5 2 4 16
text 4 5 2 5 16
through 2 2 1 2 15
tutorial 3 3 1 3 8
use 4 5 2 4 7
use 4 5 2 5 7
went 2 2 1 2 10
you 2 2 1 2 6
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 4 4 1 4 0
mysql 1 5 5 1 0
mysql 1 5 5 2 11
mysql 1 5 5 3 11
mysql 1 5 5 4 5
mysql 1 5 5 5 3
optimizing 3 3 1 3 0
tricks 4 5 2 4 11
tricks 4 5 2 5 9
tutorial 1 1 1 1 6
use 2 2 1 2 7
well 2 2 1 2 17
after 2 2 1 2 0
database 1 1 1 1 22
dbms 1 1 1 1 0
engine 4 5 2 4 28
engine 4 5 2 5 28
full 4 5 2 4 11
full 4 5 2 5 11
mysql 1 1 1 1 16
search 4 5 2 4 21
search 4 5 2 5 21
show 3 3 1 3 25
stands 1 1 1 1 5
text 4 5 2 4 16
text 4 5 2 5 16
through 2 2 1 2 15
tutorial 3 3 1 3 8
use 4 5 2 4 7
use 4 5 2 5 7
went 2 2 1 2 10
you 2 2 1 2 6
DROP TABLE articles;
SET NAMES utf8;
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE FULLTEXT INDEX idx ON articles (title);
INSERT INTO articles (title) VALUES
('相亲相爱'),('怜香惜爱'),('充满可爱'),('爱恨交织');
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
充满可爱 3 3 1 3 0
怜香惜爱 2 2 1 2 0
爱恨交织 4 4 1 4 0
相亲相爱 1 1 1 1 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
充满可爱 3 3 1 3 0
怜香惜爱 2 2 1 2 0
爱恨交织 4 4 1 4 0
相亲相爱 1 1 1 1 0
DROP TABLE articles;
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200)
) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
CREATE FULLTEXT INDEX idx ON articles (title);
INSERT INTO articles (title) VALUES
('相亲相爱'),('怜香惜爱'),('充满可爱'),('爱恨交织');
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
爱恨交织 4 4 1 4 0
充满可爱 3 3 1 3 0
怜香惜爱 2 2 1 2 0
相亲相爱 1 1 1 1 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
怜香惜爱 2 2 1 2 0
充满可爱 3 3 1 3 0
相亲相爱 1 1 1 1 0
爱恨交织 4 4 1 4 0
DROP TABLE articles;
SET GLOBAL innodb_ft_aux_table=@aux;
SET GLOBAL INNODB_OPTIMIZE_FULLTEXT_ONLY=@optimize;
Reference in a new issue View git blame Copy permalink