mirror of
https://github.com/MariaDB/server.git
synced 2025-02-06 05:42:19 +01:00
228b7e4db5
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
121 lines
3.5 KiB
Text
121 lines
3.5 KiB
Text
# This is the basic function tests for INNODB_FT_INDEX_TABLE
|
|
# and INNODB_FT_INDEX_TABLE in INFORMATION_SCHEMA.
|
|
|
|
-- source include/have_innodb.inc
|
|
-- source include/have_debug.inc
|
|
|
|
SET @optimize=@@GLOBAL.INNODB_OPTIMIZE_FULLTEXT_ONLY;
|
|
SET GLOBAL INNODB_OPTIMIZE_FULLTEXT_ONLY=1;
|
|
|
|
# Test Case 1: Test Result Cache Limit
|
|
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;
|
|
|
|
OPTIMIZE TABLE articles;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
|
|
|
|
SET @save_dbug=@@debug_dbug;
|
|
SET debug_dbug='+d,fts_instrument_result_cache_limit';
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
|
|
|
|
SET debug_dbug=@save_dbug;
|
|
|
|
DROP TABLE articles;
|
|
|
|
SET GLOBAL innodb_ft_result_cache_limit=default;
|
|
|
|
# Test Case 2: Test Multiple Indexes
|
|
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');
|
|
|
|
# test.articles had been dropped, so this result will be empty
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
|
|
SET GLOBAL innodb_ft_aux_table='test/articles';
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
|
|
|
|
OPTIMIZE TABLE articles;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
|
|
|
|
DROP TABLE articles;
|
|
|
|
SET NAMES utf8;
|
|
|
|
# Test Case 3: Test UFT8 Charset
|
|
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;
|
|
|
|
OPTIMIZE TABLE articles;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
|
|
|
|
DROP TABLE articles;
|
|
|
|
# Test Case 4: Test GB2312 Charset
|
|
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;
|
|
|
|
OPTIMIZE TABLE articles;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
|
|
|
|
DROP TABLE articles;
|
|
|
|
# Restore global variables
|
|
SET GLOBAL innodb_ft_aux_table=@aux;
|
|
SET GLOBAL INNODB_OPTIMIZE_FULLTEXT_ONLY=@optimize;
|