--source include/have_debug.inc --echo # 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 ...'); --echo # Query involves Ranking --skip_if_hypergraph # Non-deterministic result (LIMIT without ORDER BY). # The old optimizer chooses an ordered index scan here, # and returns the highest-ranking match. (For legacy # reasons: The documentation used to promise ordering # if the WHERE clause contained a natural langugage # query. The promise wasn't always kept.) The hypergraph # optimizer does not request an ordered scan. SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL' IN NATURAL LANGUAGE MODE) LIMIT 1; --echo # Without optimization # Disabling the fts_union_limit optimization means that InnoDB ignores # the no_ranking and limit hints, and returns the rows in order. So we # get the highest-ranking document. SET DEBUG = '+d,fts_union_limit_off'; SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL' IN NATURAL LANGUAGE MODE) LIMIT 1; SET DEBUG = '-d,fts_union_limit_off'; --echo # Query involves No Ranking and fts_union operations # The optimizer gives a hint that ranking of the results is not # necessary, and a random matching document, not necessarily the # highest-ranking one, is returned. SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL' IN BOOLEAN MODE) limit 1; --echo # Without optimization # The no_ranking hint is ignored when the optimization is disabled, so # InnoDB performs ranking of the results and returns the # highest-ranking one. SET DEBUG = '+d,fts_union_limit_off'; SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL' IN BOOLEAN MODE) limit 1; SET DEBUG = '-d,fts_union_limit_off'; --echo # Query involves No ranking and fts_union, fts_ignore SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL -YourSQL' IN BOOLEAN MODE) limit 1; --echo # Without optimization SET DEBUG = '+d,fts_union_limit_off'; SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL -YourSQL' IN BOOLEAN MODE) limit 1; SET DEBUG = '-d,fts_union_limit_off'; --echo # Query with fts_intersect SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL +YourSQL' IN BOOLEAN MODE) limit 1; --echo # Without optimization SET DEBUG = '+d,fts_union_limit_off'; SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL +YourSQL' IN BOOLEAN MODE) limit 1; SET DEBUG = '-d,fts_union_limit_off'; # insert record with @ character which is used in proximity search 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 ...'); --echo # Query with @distance SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"MySQL request"@3' IN BOOLEAN MODE) limit 1; --echo # Without optimization SET DEBUG = '+d,fts_union_limit_off'; SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"MySQL request"@3' IN BOOLEAN MODE) limit 1; SET DEBUG = '-d,fts_union_limit_off'; --echo # Query with subexpression SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL +(-support +doc)' IN BOOLEAN MODE) limit 1; --echo # Without optimization SET DEBUG = '+d,fts_union_limit_off'; SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL +(-support +doc)' IN BOOLEAN MODE) limit 1; SET DEBUG = '-d,fts_union_limit_off'; --echo # limit num1 OFFSET num2 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL' in boolean mode) limit 4 offset 2; --echo # Without optimization SET DEBUG = '+d,fts_union_limit_off'; SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL' in boolean mode) limit 4 offset 2; SET DEBUG = '-d,fts_union_limit_off'; --echo # wild card search SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('ru*' IN BOOLEAN MODE) limit 1; --echo # Without optimization SET DEBUG = '+d,fts_union_limit_off'; SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('ru*' IN BOOLEAN MODE) limit 1; SET DEBUG = '-d,fts_union_limit_off'; --echo # phrase search SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"request support"' IN BOOLEAN MODE) limit 1; --echo # Without optimization SET DEBUG = '+d,fts_union_limit_off'; SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"request support"' IN BOOLEAN MODE) limit 1; SET DEBUG = '-d,fts_union_limit_off'; DROP TABLE articles; --echo # --echo # Bug #31228694 FTS QUERY WITH LIMIT CRASH --echo # let $restart_parameters = restart: --ngram_token_size=1; --source include/restart_mysqld.inc select @@ngram_token_size; CREATE TABLE t1 ( id INT(10) unsigned NOT NULL AUTO_INCREMENT, name VARCHAR(45) NOT NULL, PRIMARY KEY (id) USING BTREE, FULLTEXT KEY search_name (name) WITH PARSER ngram ) ENGINE=InnoDB; INSERT INTO t1(name) VALUES('x'), ('mx'), ('mx'), ('mx'), ('mx'); SELECT * FROM t1 WHERE MATCH (`name`) AGAINST ('mx*' IN BOOLEAN MODE) LIMIT 3; DROP TABLE t1; # restore config let $restart_parameters = restart: ; --source include/restart_mysqld.inc