Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2 --source include/have_debug.inc
3 --source include/have_simple_parser.inc
4
5 # Install fts parser plugin
6 INSTALL PLUGIN simple_parser SONAME 'mypluglib';
7
8 -- echo # Test Part 2: Create Index Test(CREATE TABLE WITH FULLTEXT INDEX)
9 CREATE TABLE articles (
10 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
11 title VARCHAR(200),
12 body TEXT,
13 FULLTEXT (title, body) WITH PARSER simple_parser
14 ) ENGINE=InnoDB;
15
16 INSERT INTO articles (title, body) VALUES
17 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
18 ('How To Use MySQL Well','After you went through a ...'),
19 ('Optimizing MySQL','In this tutorial we will show ...'),
20 ('1001 MySQL Tricks','How to use full-text search engine'),
21 ('Go MySQL Tricks','How to use full text search engine');
22
23 # Simple term search
24 SELECT * FROM articles WHERE
25 MATCH(title, body) AGAINST('mysql');
26
27 # Test stopword and word len less than fts_min_token_size
28 SELECT * FROM articles WHERE
29 MATCH(title, body) AGAINST('will go');
30
31 -- echo # Test plugin parser tokenizer difference
32 SELECT * FROM articles WHERE
33 MATCH(title, body) AGAINST('full-text');
34
35 SELECT * FROM articles WHERE
36 MATCH(title, body) AGAINST('full text');
37
38 # No result here, we get '"mysql' 'database"' by simple parser
39 SELECT * FROM articles WHERE
40 MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE);
41
42 DROP TABLE articles;
43
44 -- echo # Test Part 3: Row Merge Create Index Test(ALTER TABLE ADD FULLTEXT INDEX)
45 CREATE TABLE articles (
46 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
47 title VARCHAR(200),
48 body TEXT
49 ) ENGINE=InnoDB;
50
51 INSERT INTO articles (title, body) VALUES
52 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
53 ('How To Use MySQL Well','After you went through a ...'),
54 ('Optimizing MySQL','In this tutorial we will show ...'),
55 ('1001 MySQL Tricks','How to use full-text search engine'),
56 ('Go MySQL Tricks','How to use full text search engine');
57
58 # Create fulltext index
59 ALTER TABLE articles ADD FULLTEXT INDEX (title, body) WITH PARSER simple_parser;
60
61 # Simple term search
62 SELECT * FROM articles WHERE
63 MATCH(title, body) AGAINST('mysql');
64
65 # Test stopword and word len less than fts_min_token_size
66 SELECT * FROM articles WHERE
67 MATCH(title, body) AGAINST('will go');
68
69 -- echo # Test plugin parser tokenizer difference
70 SELECT * FROM articles WHERE
71 MATCH(title, body) AGAINST('full-text');
72
73 SELECT * FROM articles WHERE
74 MATCH(title, body) AGAINST('full text');
75
76 # Test query expansion
77 SELECT * FROM articles WHERE
78 MATCH(title, body) AGAINST('full-text' WITH QUERY EXPANSION);
79
80 SELECT * FROM articles WHERE
81 MATCH(title, body) AGAINST('full text' WITH QUERY EXPANSION);
82
83 # No result here, we get '"mysql' 'database"' by simple parser
84 SELECT * FROM articles WHERE
85 MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE);
86
87 DROP TABLE articles;
88 -- echo # Test Part 3 END
89
90 # Use default parser
91 SET SESSION debug="+d,fts_instrument_use_default_parser";
92
93 -- echo # Test Part 4: Create Index Test with Default/Internal Parser
94 CREATE TABLE articles (
95 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
96 title VARCHAR(200),
97 body TEXT,
98 FULLTEXT (title, body) WITH PARSER simple_parser
99 ) ENGINE=InnoDB;
100
101 INSERT INTO articles (title, body) VALUES
102 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
103 ('How To Use MySQL Well','After you went through a ...'),
104 ('Optimizing MySQL','In this tutorial we will show ...'),
105 ('1001 MySQL Tricks','How to use full-text search engine'),
106 ('Go MySQL Tricks','How to use full text search engine');
107
108 # Simple term search
109 SELECT * FROM articles WHERE
110 MATCH(title, body) AGAINST('mysql');
111
112 # Test stopword and word len less than fts_min_token_size
113 SELECT * FROM articles WHERE
114 MATCH(title, body) AGAINST('will go');
115
116 -- echo # Test plugin parser tokenizer difference
117 SELECT * FROM articles WHERE
118 MATCH(title, body) AGAINST('full-text');
119
120 SELECT * FROM articles WHERE
121 MATCH(title, body) AGAINST('full text');
122
123 # Phrase search
124 --echo # simple parser st_mysql_ftparser_boolean_info doesn't
125 --echo # store position of every word in boolean metadata.
126 --echo # This leads to empty result
127 SELECT * FROM articles WHERE
128 MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE);
129
130 DROP TABLE articles;
131
132 # Disable query parse using plugin parser
133 -- echo # Test Part 5: Row Merge Create Index Test with Default/Internal Parser
134 CREATE TABLE articles (
135 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
136 title VARCHAR(200),
137 body TEXT
138 ) ENGINE=InnoDB;
139
140 INSERT INTO articles (title, body) VALUES
141 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
142 ('How To Use MySQL Well','After you went through a ...'),
143 ('Optimizing MySQL','In this tutorial we will show ...'),
144 ('1001 MySQL Tricks','How to use full-text search engine'),
145 ('Go MySQL Tricks','How to use full text search engine');
146
147 # Create fulltext index
148 ALTER TABLE articles ADD FULLTEXT INDEX (title, body) WITH PARSER simple_parser;
149
150 # Simple term search
151 SELECT * FROM articles WHERE
152 MATCH(title, body) AGAINST('mysql');
153
154 # Test stopword and word len less than fts_min_token_size
155 SELECT * FROM articles WHERE
156 MATCH(title, body) AGAINST('will go');
157
158 -- echo # Test plugin parser tokenizer difference
159 SELECT * FROM articles WHERE
160 MATCH(title, body) AGAINST('full-text');
161
162 SELECT * FROM articles WHERE
163 MATCH(title, body) AGAINST('full text');
164
165 # Phrase search
166 --echo # simple parser st_mysql_ftparser_boolean_info doesn't
167 --echo # store position of every word in boolean metadata.
168 --echo # This leads to empty result
169 SELECT * FROM articles WHERE
170 MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE);
171
172 DROP TABLE articles;
173
174 -- echo # Test Part 6: Test Query Parser with Default/Internal Parser
175 let $innodb_ft_enable_diag_print_orig = `SELECT @@innodb_ft_enable_diag_print`;
176 # Enable diag print to print query parse tree
177 SET GLOBAL innodb_ft_enable_diag_print = 1;
178
179 CREATE TABLE articles (
180 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
181 title VARCHAR(200),
182 body TEXT,
183 FULLTEXT (title, body) WITH PARSER simple_parser
184 ) ENGINE=InnoDB;
185
186 INSERT INTO articles (title, body) VALUES
187 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
188 ('MySQL Tutorial','DBMS stands for MySQL good one DataBase ...'),
189 ('How To Use MySQL Well','After you went through a ...'),
190 ('Optimizing MySQL','In this tutorial we will show ...'),
191 ('1001 MySQL Tricks','How to use full-text search engine');
192
193 SELECT * FROM articles WHERE
194 MATCH(title, body) AGAINST('+mysql -database' IN BOOLEAN MODE);
195
196 SELECT * FROM articles WHERE
197 MATCH(title, body) AGAINST('>mysql <database ~search' IN BOOLEAN MODE);
198
199 SELECT * FROM articles WHERE
200 MATCH(title, body) AGAINST('+(mysql database) -engine' IN BOOLEAN MODE);
201
202 # The will be no result because of parsing error.
203 SELECT * FROM articles WHERE
204 MATCH(title, body) AGAINST('+(mysql database -engine' IN BOOLEAN MODE);
205
206 # Test phrase match
207 SELECT * FROM articles WHERE
208 MATCH(title, body) AGAINST('"mysql"' IN BOOLEAN MODE);
209
210 # Test stopword or word less than fts_min_token_size
211 SELECT * FROM articles WHERE
212 MATCH(title, body) AGAINST('"for mysql"' IN BOOLEAN MODE);
213
214 --echo # simple parser st_mysql_ftparser_boolean_info doesn't
215 --echo # store position of every word in boolean metadata.
216 --echo # This leads to empty result for the next 3 queries
217 SELECT * FROM articles WHERE
218 MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE);
219
220 SELECT * FROM articles WHERE
221 MATCH(title, body) AGAINST('"full text search"' IN BOOLEAN MODE);
222
223 SELECT * FROM articles WHERE
224 MATCH(title, body) AGAINST('"full text search engine"' IN BOOLEAN MODE);
225
226 SELECT * FROM articles WHERE
227 MATCH(title, body) AGAINST('+"dbms stands" -good' IN BOOLEAN MODE);
228
229 SELECT * FROM articles WHERE
230 MATCH(title, body) AGAINST('+("dbms stands") -good' IN BOOLEAN MODE);
231
232 SELECT * FROM articles WHERE
233 MATCH(title, body) AGAINST('+("dbms stands" search) -good' IN BOOLEAN MODE);
234
235 SELECT * FROM articles WHERE
236 MATCH(title, body) AGAINST('+("dbms stands" "full text") -good' IN BOOLEAN MODE);
237
238 # Test proximity match
239 # It will be treated as phrase match
240 SELECT * FROM articles WHERE
241 MATCH(title, body) AGAINST('("msyql database")@3' IN BOOLEAN MODE);
242
243 DROP TABLE articles;
244
245 SET NAMES utf8;
246
247 -- echo # Test Part 7: Test Different Charset
248 CREATE TABLE articles (
249 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
250 title VARCHAR(200),
251 body TEXT,
252 FULLTEXT (title, body) WITH PARSER simple_parser
253 ) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
254
255 INSERT INTO articles (title, body) VALUES
256 ('数据库 是 数据 的 结构化 集合','它 可以 是 任何 东西'),
257 ('从 简单 的 购物清单 到 画展','或 企业网络 中 的 海量信息'),
258 ('要 想 将 数据 添加 到 数据库','或 访问、处理 计算机 数据库 中 保存 的 数据'),
259 ('需要 使用 数据库 管理系统','计算机 是 处理 大量 数据 的 理想 工具');
260
261 SELECT * FROM articles WHERE
262 MATCH(title, body) AGAINST('数据库');
263
264 # Test word len less than fts_min_token_size
265 SELECT * FROM articles WHERE
266 MATCH(title, body) AGAINST('数据');
267
268 # Test wildcard
269 SELECT * FROM articles WHERE
270 MATCH(title, body) AGAINST('数据*');
271
272 # Test operator
273 SELECT * FROM articles WHERE
274 MATCH(title, body) AGAINST('+数据库 -计算机');
275
276 # Test phrase search
277 --echo # simple parser st_mysql_ftparser_boolean_info doesn't
278 --echo # store position of every word in boolean metadata.
279 --echo # This leads to empty result
280 SELECT * FROM articles WHERE
281 MATCH(title, body) AGAINST('"计算机 数据库"' IN BOOLEAN MODE);
282
283 DROP TABLE articles;
284
285 eval SET GLOBAL innodb_ft_enable_diag_print = $innodb_ft_enable_diag_print_orig;
286
287 # Disable use default parser
288 SET SESSION debug="-d,fts_instrument_use_default_parser";
289
290 -- echo # Test Part 9:# Abort the operation in dict_create_index_step
291 # by setting return status of dict_create_index_tree_step() to DB_OUT_OF_MEMORY
292 # # The newly create dict_index_t should be removed from fts cache
293
294 CREATE TABLE articles (
295 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
296 title VARCHAR(200),
297 body TEXT,
298 FULLTEXT (title, body) WITH PARSER simple_parser
299 ) ENGINE=InnoDB;
300 #
301 SET SESSION debug="+d,ib_dict_create_index_tree_fail";
302 --error ER_OUT_OF_RESOURCES
303 CREATE FULLTEXT INDEX idx ON articles(body);
304 SET SESSION debug="-d,ib_dict_create_index_tree_fail";
305 #
306 DROP TABLE articles;
307
308 UNINSTALL PLUGIN simple_parser;