Debian Codegraph @

mysql-test/suite/innodb_fts/t/limit_union.test

raw
1 --source include/have_debug.inc
2
3 --echo # Bug #22709692 FTS QUERY EXCEEDS RESULT CACHE LIMIT
4
5 CREATE TABLE articles (
6 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
7 title VARCHAR(200),
8 body TEXT,
9 FULLTEXT (title,body),
10 FULLTEXT (body))ENGINE=InnoDB;
11
12 INSERT INTO articles (title,body) VALUES
13 ('MySQL Tutorial','DBMS stands for DataBase ...'),
14 ('How To Use MySQL Well','After you went through a ...'),
15 ('Optimizing MySQL','In this tutorial we will show ...'),
16 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
17 ('MySQL vs. YourSQL','In the following database comparison ...'),
18 ('MySQL Security','When configured properly, MySQL ...');
19
20 --echo # Query involves Ranking
21 --skip_if_hypergraph # Non-deterministic result (LIMIT without ORDER BY).
22 # The old optimizer chooses an ordered index scan here,
23 # and returns the highest-ranking match. (For legacy
24 # reasons: The documentation used to promise ordering
25 # if the WHERE clause contained a natural langugage
26 # query. The promise wasn't always kept.) The hypergraph
27 # optimizer does not request an ordered scan.
28 SELECT * FROM articles
29 WHERE MATCH (title,body)
30 AGAINST ('MySQL' IN NATURAL LANGUAGE MODE) LIMIT 1;
31
32 --echo # Without optimization
33 # Disabling the fts_union_limit optimization means that InnoDB ignores
34 # the no_ranking and limit hints, and returns the rows in order. So we
35 # get the highest-ranking document.
36 SET DEBUG = '+d,fts_union_limit_off';
37 SELECT * FROM articles
38 WHERE MATCH (title,body)
39 AGAINST ('MySQL' IN NATURAL LANGUAGE MODE) LIMIT 1;
40 SET DEBUG = '-d,fts_union_limit_off';
41
42 --echo # Query involves No Ranking and fts_union operations
43 # The optimizer gives a hint that ranking of the results is not
44 # necessary, and a random matching document, not necessarily the
45 # highest-ranking one, is returned.
46 SELECT * FROM articles
47 WHERE MATCH (title,body)
48 AGAINST ('MySQL' IN BOOLEAN MODE) limit 1;
49
50 --echo # Without optimization
51 # The no_ranking hint is ignored when the optimization is disabled, so
52 # InnoDB performs ranking of the results and returns the
53 # highest-ranking one.
54 SET DEBUG = '+d,fts_union_limit_off';
55 SELECT * FROM articles
56 WHERE MATCH (title,body)
57 AGAINST ('MySQL' IN BOOLEAN MODE) limit 1;
58
59 SET DEBUG = '-d,fts_union_limit_off';
60
61 --echo # Query involves No ranking and fts_union, fts_ignore
62 SELECT * FROM articles
63 WHERE MATCH (title,body)
64 AGAINST ('MySQL -YourSQL' IN BOOLEAN MODE) limit 1;
65
66 --echo # Without optimization
67 SET DEBUG = '+d,fts_union_limit_off';
68 SELECT * FROM articles
69 WHERE MATCH (title,body)
70 AGAINST ('MySQL -YourSQL' IN BOOLEAN MODE) limit 1;
71 SET DEBUG = '-d,fts_union_limit_off';
72
73 --echo # Query with fts_intersect
74 SELECT * FROM articles
75 WHERE MATCH (title,body)
76 AGAINST ('MySQL +YourSQL' IN BOOLEAN MODE) limit 1;
77
78 --echo # Without optimization
79 SET DEBUG = '+d,fts_union_limit_off';
80 SELECT * FROM articles
81 WHERE MATCH (title,body)
82 AGAINST ('MySQL +YourSQL' IN BOOLEAN MODE) limit 1;
83 SET DEBUG = '-d,fts_union_limit_off';
84
85 # insert record with @ character which is used in proximity search
86 INSERT INTO articles (title,body) VALUES
87 ('MySQL Tutorial','request [email protected] ...'),
88 ('MySQL Tutorial','request [email protected] ...'),
89 ('Trial version','query performace @1255 minute on 2.1Hz
90 Memory 2GB...'),
91 ('when To Use MySQL Well','for free faq [email protected] ...');
92
93 --echo # Query with @distance
94 SELECT * FROM articles
95 WHERE MATCH (title,body)
96 AGAINST ('"MySQL request"@3' IN BOOLEAN MODE) limit 1;
97
98 --echo # Without optimization
99 SET DEBUG = '+d,fts_union_limit_off';
100 SELECT * FROM articles
101 WHERE MATCH (title,body)
102 AGAINST ('"MySQL request"@3' IN BOOLEAN MODE) limit 1;
103 SET DEBUG = '-d,fts_union_limit_off';
104
105 --echo # Query with subexpression
106 SELECT * FROM articles
107 WHERE MATCH (title,body)
108 AGAINST ('+MySQL +(-support +doc)' IN BOOLEAN MODE) limit 1;
109
110 --echo # Without optimization
111 SET DEBUG = '+d,fts_union_limit_off';
112 SELECT * FROM articles
113 WHERE MATCH (title,body)
114 AGAINST ('+MySQL +(-support +doc)' IN BOOLEAN MODE) limit 1;
115 SET DEBUG = '-d,fts_union_limit_off';
116
117 --echo # limit num1 OFFSET num2
118 SELECT * FROM articles
119 WHERE MATCH (title,body)
120 AGAINST ('MySQL' in boolean mode) limit 4 offset 2;
121
122 --echo # Without optimization
123 SET DEBUG = '+d,fts_union_limit_off';
124 SELECT * FROM articles
125 WHERE MATCH (title,body)
126 AGAINST ('MySQL' in boolean mode) limit 4 offset 2;
127 SET DEBUG = '-d,fts_union_limit_off';
128
129 --echo # wild card search
130 SELECT * FROM articles
131 WHERE MATCH (title,body)
132 AGAINST ('ru*' IN BOOLEAN MODE) limit 1;
133
134 --echo # Without optimization
135 SET DEBUG = '+d,fts_union_limit_off';
136 SELECT * FROM articles
137 WHERE MATCH (title,body)
138 AGAINST ('ru*' IN BOOLEAN MODE) limit 1;
139 SET DEBUG = '-d,fts_union_limit_off';
140
141 --echo # phrase search
142 SELECT * FROM articles
143 WHERE MATCH (title,body)
144 AGAINST ('"request support"' IN BOOLEAN MODE) limit 1;
145
146 --echo # Without optimization
147 SET DEBUG = '+d,fts_union_limit_off';
148 SELECT * FROM articles
149 WHERE MATCH (title,body)
150 AGAINST ('"request support"' IN BOOLEAN MODE) limit 1;
151 SET DEBUG = '-d,fts_union_limit_off';
152
153 DROP TABLE articles;
154
155 --echo #
156 --echo # Bug #31228694 FTS QUERY WITH LIMIT CRASH
157 --echo #
158
159 let $restart_parameters = restart: --ngram_token_size=1;
160 --source include/restart_mysqld.inc
161 select @@ngram_token_size;
162
163 CREATE TABLE t1 (
164 id INT(10) unsigned NOT NULL AUTO_INCREMENT,
165 name VARCHAR(45) NOT NULL,
166 PRIMARY KEY (id) USING BTREE,
167 FULLTEXT KEY search_name (name) WITH PARSER ngram
168 ) ENGINE=InnoDB;
169
170 INSERT INTO t1(name) VALUES('x'), ('mx'), ('mx'), ('mx'), ('mx');
171
172 SELECT * FROM t1 WHERE MATCH (`name`) AGAINST ('mx*' IN BOOLEAN MODE) LIMIT 3;
173 DROP TABLE t1;
174
175 # restore config
176 let $restart_parameters = restart: ;
177 --source include/restart_mysqld.inc