mirror of
https://github.com/MariaDB/server.git
synced 2025-02-04 21:02:17 +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
157 lines
5.4 KiB
Text
157 lines
5.4 KiB
Text
# Bug #22709692 FTS QUERY EXCEEDS RESULT CACHE LIMIT
|
|
CREATE TABLE articles (
|
|
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
|
title VARCHAR(200),
|
|
body TEXT,
|
|
FULLTEXT (title,body),
|
|
FULLTEXT (body))ENGINE=InnoDB;
|
|
INSERT INTO articles (title,body) 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 ...'),
|
|
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
|
|
('MySQL vs. YourSQL','In the following database comparison ...'),
|
|
('MySQL Security','When configured properly, MySQL ...');
|
|
SET @default_cache_size = @@GLOBAL.query_cache_size;
|
|
SET GLOBAL query_cache_size=0;
|
|
# Query involves Ranking
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('MySQL' IN NATURAL LANGUAGE MODE) LIMIT 1;
|
|
id title body
|
|
6 MySQL Security When configured properly, MySQL ...
|
|
# Without optimization
|
|
SET @save_dbug = @@debug_dbug;
|
|
SET debug_dbug = '+d,fts_union_limit_off';
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('MySQL' IN NATURAL LANGUAGE MODE) LIMIT 1;
|
|
id title body
|
|
6 MySQL Security When configured properly, MySQL ...
|
|
SET debug_dbug = @save_dbug;
|
|
# Query involves No Ranking and fts_union operations
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('MySQL' IN BOOLEAN MODE) limit 1;
|
|
id title body
|
|
6 MySQL Security When configured properly, MySQL ...
|
|
# Without optimization
|
|
SET debug_dbug = '+d,fts_union_limit_off';
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('MySQL' IN BOOLEAN MODE) limit 1;
|
|
id title body
|
|
6 MySQL Security When configured properly, MySQL ...
|
|
SET debug_dbug = @save_dbug;
|
|
# Query involves No ranking and fts_union, fts_ignore
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('MySQL -YourSQL' IN BOOLEAN MODE) limit 1;
|
|
id title body
|
|
6 MySQL Security When configured properly, MySQL ...
|
|
# Without optimization
|
|
SET debug_dbug = '+d,fts_union_limit_off';
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('MySQL -YourSQL' IN BOOLEAN MODE) limit 1;
|
|
id title body
|
|
6 MySQL Security When configured properly, MySQL ...
|
|
SET debug_dbug = @save_dbug;
|
|
# Query with fts_intersect
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('MySQL +YourSQL' IN BOOLEAN MODE) limit 1;
|
|
id title body
|
|
5 MySQL vs. YourSQL In the following database comparison ...
|
|
# Without optimization
|
|
SET debug_dbug = '+d,fts_union_limit_off';
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('MySQL +YourSQL' IN BOOLEAN MODE) limit 1;
|
|
id title body
|
|
5 MySQL vs. YourSQL In the following database comparison ...
|
|
SET debug_dbug = @save_dbug;
|
|
INSERT INTO articles (title,body) VALUES
|
|
('MySQL Tutorial','request doc@oraclehelp.com ...'),
|
|
('MySQL Tutorial','request support@oraclehelp.com ...'),
|
|
('Trial version','query performace @1255 minute on 2.1Hz
|
|
Memory 2GB...'),
|
|
('when To Use MySQL Well','for free faq mail@xyz.com ...');
|
|
# Query with @distance
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('"MySQL request"@3' IN BOOLEAN MODE) limit 1;
|
|
id title body
|
|
7 MySQL Tutorial request doc@oraclehelp.com ...
|
|
# Without optimization
|
|
SET debug_dbug = '+d,fts_union_limit_off';
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('"MySQL request"@3' IN BOOLEAN MODE) limit 1;
|
|
id title body
|
|
7 MySQL Tutorial request doc@oraclehelp.com ...
|
|
SET debug_dbug = @save_dbug;
|
|
# Query with subexpression
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('+MySQL +(-support +doc)' IN BOOLEAN MODE) limit 1;
|
|
id title body
|
|
7 MySQL Tutorial request doc@oraclehelp.com ...
|
|
# Without optimization
|
|
SET debug_dbug = '+d,fts_union_limit_off';
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('+MySQL +(-support +doc)' IN BOOLEAN MODE) limit 1;
|
|
id title body
|
|
7 MySQL Tutorial request doc@oraclehelp.com ...
|
|
SET debug_dbug = @save_dbug;
|
|
# limit num1 OFFSET num2
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('MySQL' in boolean mode) limit 4 offset 2;
|
|
id title body
|
|
2 How To Use MySQL Well After you went through a ...
|
|
3 Optimizing MySQL In this tutorial we will show ...
|
|
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
|
|
5 MySQL vs. YourSQL In the following database comparison ...
|
|
# Without optimization
|
|
SET debug_dbug = '+d,fts_union_limit_off';
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('MySQL' in boolean mode) limit 4 offset 2;
|
|
id title body
|
|
2 How To Use MySQL Well After you went through a ...
|
|
3 Optimizing MySQL In this tutorial we will show ...
|
|
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
|
|
5 MySQL vs. YourSQL In the following database comparison ...
|
|
SET debug_dbug = @save_dbug;
|
|
# wild card search
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('ru*' IN BOOLEAN MODE) limit 1;
|
|
id title body
|
|
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
|
|
# Without optimization
|
|
SET debug_dbug = '+d,fts_union_limit_off';
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('ru*' IN BOOLEAN MODE) limit 1;
|
|
id title body
|
|
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
|
|
SET debug_dbug = @save_dbug;
|
|
# phrase search
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('"request support"' IN BOOLEAN MODE) limit 1;
|
|
id title body
|
|
8 MySQL Tutorial request support@oraclehelp.com ...
|
|
# Without optimization
|
|
SET debug_dbug = '+d,fts_union_limit_off';
|
|
SELECT * FROM articles
|
|
WHERE MATCH (title,body)
|
|
AGAINST ('"request support"' IN BOOLEAN MODE) limit 1;
|
|
id title body
|
|
8 MySQL Tutorial request support@oraclehelp.com ...
|
|
SET debug_dbug = @save_dbug;
|
|
DROP TABLE articles;
|
|
SET GLOBAL query_cache_size = @default_cache_size;
|