Debian Codegraph @

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

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