Debian Codegraph @

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

raw
1 # This is the basic function tests for innodb FTS stopword charset
2
3 -- source include/have_innodb.inc
4
5 # Embedded server tests do not support restarting
6 --source include/not_embedded.inc
7 --source include/maybe_versioning.inc
8
9 SELECT @@innodb_ft_server_stopword_table;
10 SELECT @@innodb_ft_enable_stopword;
11 SELECT @@innodb_ft_user_stopword_table;
12
13 SET NAMES utf8;
14
15 -- echo # Test 1 : default latin1_swedish_ci
16 # Create FTS table with default charset latin1_swedish_ci
17 CREATE TABLE articles (
18 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
19 title VARCHAR(200)
20 ) ENGINE=InnoDB CHARSET=latin1;
21
22 --disable_warnings
23 INSERT IGNORE INTO articles (title) VALUES
24 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
25 ('lṓve'),('LṒVE');
26
27 # Build full text index with default stopword
28 CREATE FULLTEXT INDEX ft_idx ON articles(title);
29 --enable_warnings
30
31 # We can find 'lòve'
32 SELECT * FROM articles WHERE MATCH (title)
33 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
34
35 # Define a user stopword table and set to it
36 CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB CHARSET=latin1;
37 INSERT INTO user_stopword VALUES('lòve');
38 SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
39
40 # Rebuild the full text index with user stopword
41 DROP INDEX ft_idx ON articles;
42 CREATE FULLTEXT INDEX ft_idx ON articles(title);
43
44 # Now we will not find 'lòve' and check result with 'love'
45 SELECT * FROM articles WHERE MATCH (title)
46 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
47
48 SELECT * FROM articles WHERE MATCH (title)
49 AGAINST ('love' IN NATURAL LANGUAGE MODE);
50
51 DROP TABLE articles;
52 DROP TABLE user_stopword;
53
54 -- echo # Test 2 : latin1_general_ci
55 # Create FTS table with default charset latin1_swedish_ci
56 CREATE TABLE articles (
57 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
58 title VARCHAR(200)
59 ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;
60
61 --disable_warnings
62 INSERT IGNORE INTO articles (title) VALUES
63 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
64 ('lṓve'),('LṒVE');
65
66 # Build full text index with default stopword
67 CREATE FULLTEXT INDEX ft_idx ON articles(title);
68 --enable_warnings
69
70 # We can find 'lòve'
71 SELECT * FROM articles WHERE MATCH (title)
72 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
73
74 # Define a user stopword table and set to it
75 CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
76 DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;
77 INSERT INTO user_stopword VALUES('lòve');
78 SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
79
80 # Rebuild the full text index with user stopword
81 DROP INDEX ft_idx ON articles;
82 CREATE FULLTEXT INDEX ft_idx ON articles(title);
83
84 # Now we will not find 'lòve'
85 SELECT * FROM articles WHERE MATCH (title)
86 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
87
88 SELECT * FROM articles WHERE MATCH (title)
89 AGAINST ('love' IN NATURAL LANGUAGE MODE);
90
91 DROP TABLE articles;
92 DROP TABLE user_stopword;
93
94 -- echo # Test 3 : latin1_spanish_ci
95 # Create FTS table with default charset latin1_swedish_ci
96 CREATE TABLE articles (
97 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
98 title VARCHAR(200)
99 ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_spanish_ci;
100
101 --disable_warnings
102 INSERT IGNORE INTO articles (title) VALUES
103 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
104 ('lṓve'),('LṒVE');
105
106 # Build full text index with default stopword
107 CREATE FULLTEXT INDEX ft_idx ON articles(title);
108 --enable_warnings
109
110 # We can find 'lòve'
111 SELECT * FROM articles WHERE MATCH (title)
112 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
113
114 # Define a user stopword table and set to it
115 CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
116 DEFAULT CHARACTER SET latin1 COLLATE latin1_spanish_ci;
117 INSERT INTO user_stopword VALUES('lòve');
118 SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
119
120 # Rebuild the full text index with user stopword
121 DROP INDEX ft_idx ON articles;
122 CREATE FULLTEXT INDEX ft_idx ON articles(title);
123
124 # Now we will not find 'lòve'
125 SELECT * FROM articles WHERE MATCH (title)
126 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
127
128 SELECT * FROM articles WHERE MATCH (title)
129 AGAINST ('love' IN NATURAL LANGUAGE MODE);
130
131 DROP TABLE articles;
132 DROP TABLE user_stopword;
133
134 -- echo # Test 4 : utf8_general_ci
135 # Create FTS table with default charset utf8_general_ci
136 CREATE TABLE articles (
137 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
138 title VARCHAR(200)
139 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
140
141 --disable_warnings
142 INSERT INTO articles (title) VALUES
143 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
144 ('lṓve'),('LṒVE');
145
146 # Build full text index with default stopword
147 CREATE FULLTEXT INDEX ft_idx ON articles(title);
148 --enable_warnings
149
150 # We can find 'lòve'
151 SELECT * FROM articles WHERE MATCH (title)
152 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
153
154 # Define a user stopword table and set to it
155 CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
156 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
157 INSERT INTO user_stopword VALUES('lòve');
158 SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
159
160 # Rebuild the full text index with user stopword
161 DROP INDEX ft_idx ON articles;
162 CREATE FULLTEXT INDEX ft_idx ON articles(title);
163
164 # Now we will not find 'lòve'
165 SELECT * FROM articles WHERE MATCH (title)
166 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
167
168 SELECT * FROM articles WHERE MATCH (title)
169 AGAINST ('love' IN NATURAL LANGUAGE MODE);
170
171 DROP TABLE articles;
172 DROP TABLE user_stopword;
173
174 -- echo # Test 5 : utf8_unicode_ci
175 # Create FTS table with default charset utf8_swedish_ci
176 CREATE TABLE articles (
177 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
178 title VARCHAR(200)
179 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_swedish_ci;
180
181 --disable_warnings
182 INSERT INTO articles (title) VALUES
183 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
184 ('lṓve'),('LṒVE');
185
186 # Build full text index with default stopword
187 CREATE FULLTEXT INDEX ft_idx ON articles(title);
188 --enable_warnings
189
190 # We can find 'lòve'
191 SELECT * FROM articles WHERE MATCH (title)
192 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
193
194 # Define a user stopword table and set to it
195 CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
196 DEFAULT CHARACTER SET utf8 COLLATE utf8_swedish_ci;
197 INSERT INTO user_stopword VALUES('lòve');
198 SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
199
200 # Rebuild the full text index with user stopword
201 DROP INDEX ft_idx ON articles;
202 CREATE FULLTEXT INDEX ft_idx ON articles(title);
203
204 # Now we will not find 'lòve'
205 SELECT * FROM articles WHERE MATCH (title)
206 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
207
208 SELECT * FROM articles WHERE MATCH (title)
209 AGAINST ('love' IN NATURAL LANGUAGE MODE);
210
211 DROP TABLE articles;
212 DROP TABLE user_stopword;
213
214 -- echo # Test 6 : utf8_unicode_ci
215 # Create FTS table with default charset utf8_unicode_ci
216 CREATE TABLE articles (
217 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
218 title VARCHAR(200)
219 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
220
221 --disable_warnings
222 INSERT INTO articles (title) VALUES
223 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
224 ('lṓve'),('LṒVE');
225
226 # Build full text index with default stopword
227 CREATE FULLTEXT INDEX ft_idx ON articles(title);
228 --enable_warnings
229
230 # We can find 'lòve'
231 SELECT * FROM articles WHERE MATCH (title)
232 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
233
234 # Define a user stopword table and set to it
235 CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
236 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
237 INSERT INTO user_stopword VALUES('lòve');
238 SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
239
240 # Rebuild the full text index with user stopword
241 DROP INDEX ft_idx ON articles;
242 CREATE FULLTEXT INDEX ft_idx ON articles(title);
243
244 # Now we will not find 'lòve'
245 SELECT * FROM articles WHERE MATCH (title)
246 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
247
248 SELECT * FROM articles WHERE MATCH (title)
249 AGAINST ('love' IN NATURAL LANGUAGE MODE);
250
251 DROP TABLE articles;
252 DROP TABLE user_stopword;
253
254 -- echo # Test 7 : gb2312_chinese_ci
255 # Create FTS table with default charset gb2312_chinese_ci
256 CREATE TABLE articles (
257 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
258 title VARCHAR(200)
259 ) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
260
261 --disable_warnings
262 INSERT INTO articles (title) VALUES
263 ('相亲相爱'),('怜香惜爱'),('充满可爱'),('爱恨交织');
264
265 # Build full text index with default stopword
266 CREATE FULLTEXT INDEX ft_idx ON articles(title);
267 --enable_warnings
268
269 # We can find '相亲相爱'
270 SELECT * FROM articles WHERE MATCH (title)
271 AGAINST ('相亲相爱' IN NATURAL LANGUAGE MODE);
272
273 # Define a user stopword table and set to it
274 CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
275 DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
276 INSERT INTO user_stopword VALUES('相亲相爱');
277 SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
278
279 # Rebuild the full text index with user stopword
280 DROP INDEX ft_idx ON articles;
281 CREATE FULLTEXT INDEX ft_idx ON articles(title);
282
283 # Now we will not find '相亲相爱'
284 SELECT * FROM articles WHERE MATCH (title)
285 AGAINST ('相亲相爱' IN NATURAL LANGUAGE MODE);
286
287 SELECT * FROM articles WHERE MATCH (title)
288 AGAINST ('怜香惜爱' IN NATURAL LANGUAGE MODE);
289
290 DROP TABLE articles;
291 DROP TABLE user_stopword;
292
293 -- echo # Test 8 : test shutdown to check if stopword still works
294 # Create FTS table with default charset latin1_swedish_ci
295 CREATE TABLE articles (
296 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
297 title VARCHAR(200)
298 ) ENGINE=InnoDB CHARSET=latin1;
299
300 --disable_warnings
301 INSERT IGNORE INTO articles (title) VALUES
302 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
303 ('lṓve'),('LṒVE');
304
305 # Build full text index with default stopword
306 CREATE FULLTEXT INDEX ft_idx ON articles(title);
307 --enable_warnings
308
309 # We can find 'lòve'
310 SELECT * FROM articles WHERE MATCH (title)
311 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
312
313 # Define a user stopword table and set to it
314 CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB CHARSET=latin1;
315 INSERT INTO user_stopword VALUES('lòve');
316 SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
317
318 # Rebuild the full text index with user stopword
319 DROP INDEX ft_idx ON articles;
320 CREATE FULLTEXT INDEX ft_idx ON articles(title);
321
322 # Now we will not find 'lòve' and check result with 'love'
323 SELECT * FROM articles WHERE MATCH (title)
324 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
325
326 SELECT * FROM articles WHERE MATCH (title)
327 AGAINST ('love' IN NATURAL LANGUAGE MODE);
328
329 --echo # Shutdown and restart mysqld
330 --source include/restart_mysqld.inc
331
332 SET NAMES utf8;
333
334 # Insert rows to check if it uses user stopword
335 --disable_warnings
336 INSERT IGNORE INTO articles (title) VALUES
337 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
338 ('lṓve'),('LṒVE');
339 --enable_warnings
340
341 # Now we will not find 'lòve' and check result with 'love'
342 SELECT * FROM articles WHERE MATCH (title)
343 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
344
345 SELECT * FROM articles WHERE MATCH (title)
346 AGAINST ('love' IN NATURAL LANGUAGE MODE);
347
348 DROP TABLE articles;
349 DROP TABLE user_stopword;
350
351 -- echo # Test 9 : drop user stopwrod table,test shutdown to check if it works
352 # Create FTS table with default charset latin1_swedish_ci
353 CREATE TABLE articles (
354 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
355 title VARCHAR(200)
356 ) ENGINE=InnoDB CHARSET=latin1;
357
358 --disable_warnings
359 INSERT IGNORE INTO articles (title) VALUES
360 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
361 ('lṓve'),('LṒVE');
362
363 # Build full text index with default stopword
364 CREATE FULLTEXT INDEX ft_idx ON articles(title);
365 --enable_warnings
366
367 # We can find 'lòve'
368 SELECT * FROM articles WHERE MATCH (title)
369 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
370
371 # Define a user stopword table and set to it
372 CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB CHARSET=latin1;
373 INSERT INTO user_stopword VALUES('lòve');
374 SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
375
376 # Rebuild the full text index with user stopword
377 DROP INDEX ft_idx ON articles;
378 CREATE FULLTEXT INDEX ft_idx ON articles(title);
379
380 # Now we will not find 'lòve' and check result with 'love'
381 SELECT * FROM articles WHERE MATCH (title)
382 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
383
384 SELECT * FROM articles WHERE MATCH (title)
385 AGAINST ('love' IN NATURAL LANGUAGE MODE);
386
387 # Drop user stopword table
388 DROP TABLE user_stopword;
389
390 --echo # Shutdown and restart mysqld
391 --source include/restart_mysqld.inc
392
393 SET NAMES utf8;
394
395 # Insert rows to check if it uses user stopword
396 --disable_warnings
397 INSERT IGNORE INTO articles (title) VALUES
398 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
399 ('lṓve'),('LṒVE');
400 --enable_warnings
401
402 # Now we will not find 'lòve' and check result with 'love'
403 SELECT * FROM articles WHERE MATCH (title)
404 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
405
406 SELECT * FROM articles WHERE MATCH (title)
407 AGAINST ('love' IN NATURAL LANGUAGE MODE);
408
409 DROP TABLE articles;