Debian Codegraph @

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

raw
1 # This is the basic function tests for innodb FTS
2
3
4 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* user stopword table not_defined does not exist.");
5 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* user stopword table test/user_stopword_session does not exist.");
6
7 select * from information_schema.innodb_ft_default_stopword;
8
9 # Create FTS table
10 CREATE TABLE articles (
11 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
12 title VARCHAR(200),
13 body TEXT,
14 FULLTEXT (title,body)
15 ) ENGINE=InnoDB;
16
17 # Insert six rows
18 INSERT INTO articles (title,body) VALUES
19 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
20 ('How To Use MySQL Well','After you went through a ...'),
21 ('Optimizing MySQL','In this tutorial we will show ...'),
22 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
23 ('MySQL vs. YourSQL','In the following database comparison ...'),
24 ('MySQL Security','When configured properly, MySQL ...');
25
26 # "the" is in the default stopword, it would not be selected
27 SELECT * FROM articles WHERE MATCH (title,body)
28 AGAINST ('the' IN NATURAL LANGUAGE MODE);
29
30 let $innodb_ft_server_stopword_table_orig=`select @@innodb_ft_server_stopword_table`;
31 let $innodb_ft_enable_stopword_orig=`select @@innodb_ft_enable_stopword`;
32 let $innodb_ft_user_stopword_table_orig=`select @@innodb_ft_user_stopword_table`;
33
34 select @@innodb_ft_server_stopword_table;
35 select @@innodb_ft_enable_stopword;
36 select @@innodb_ft_user_stopword_table;
37
38 # Provide user defined stopword table, if not (correctly) defined,
39 # it will be rejected
40 --error 1231
41 set global innodb_ft_server_stopword_table = "not_defined";
42
43 # Define a correct formated user stopword table
44 create table user_stopword(value varchar(30)) engine = innodb;
45
46 # The set operation should be successful
47 set global innodb_ft_server_stopword_table = "test/user_stopword";
48
49 drop index title on articles;
50
51 create fulltext index idx on articles(title, body);
52
53 # Now we should be able to find "the"
54 SELECT * FROM articles WHERE MATCH (title,body)
55 AGAINST ('the' IN NATURAL LANGUAGE MODE);
56
57 # Nothing inserted into the default stopword, so essentially
58 # nothing get screened. The new stopword could only be
59 # effective for table created thereafter
60 CREATE TABLE articles_2 (
61 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
62 title VARCHAR(200),
63 body TEXT,
64 FULLTEXT (title,body)
65 ) ENGINE=InnoDB;
66
67 INSERT INTO articles_2 (title, body)
68 VALUES ('test for stopwords','this is it...');
69
70 # Now we can find record with "this"
71 SELECT * FROM articles_2 WHERE MATCH (title,body)
72 AGAINST ('this' IN NATURAL LANGUAGE MODE);
73
74 # Ok, let's instantiate some value into user supplied stop word
75 # table
76 insert into user_stopword values("this");
77
78 # Ok, let's repeat with the new table again.
79 CREATE TABLE articles_3 (
80 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
81 title VARCHAR(200),
82 body TEXT,
83 FULLTEXT (title,body)
84 ) ENGINE=InnoDB;
85
86 INSERT INTO articles_3 (title, body)
87 VALUES ('test for stopwords','this is it...');
88
89 # Now we should NOT find record with "this"
90 SELECT * FROM articles_3 WHERE MATCH (title,body)
91 AGAINST ('this' IN NATURAL LANGUAGE MODE);
92
93 # Test session level stopword control "innodb_user_stopword_table"
94 create table user_stopword_session(value varchar(30)) engine = innodb;
95
96 insert into user_stopword_session values("session");
97
98 set session innodb_ft_user_stopword_table="test/user_stopword_session";
99
100 CREATE TABLE articles_4 (
101 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
102 title VARCHAR(200),
103 body TEXT,
104 FULLTEXT (title,body)
105 ) ENGINE=InnoDB;
106
107 INSERT INTO articles_4 (title, body)
108 VALUES ('test for session stopwords','this should also be excluded...');
109
110 # "session" is excluded
111 SELECT * FROM articles_4 WHERE MATCH (title,body)
112 AGAINST ('session' IN NATURAL LANGUAGE MODE);
113
114 # But we can find record with "this"
115 SELECT * FROM articles_4 WHERE MATCH (title,body)
116 AGAINST ('this' IN NATURAL LANGUAGE MODE);
117
118 --connect (con1,localhost,root,,)
119 CREATE TABLE articles_5 (
120 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
121 title VARCHAR(200),
122 body TEXT,
123 FULLTEXT (title,body)
124 ) ENGINE=InnoDB;
125
126 INSERT INTO articles_5 (title, body)
127 VALUES ('test for session stopwords','this should also be excluded...');
128
129 # "session" should be found since the stopword table is session specific
130 SELECT * FROM articles_5 WHERE MATCH (title,body)
131 AGAINST ('session' IN NATURAL LANGUAGE MODE);
132
133 --connection default
134 drop table articles;
135 drop table articles_2;
136 drop table articles_3;
137 drop table articles_4;
138 drop table articles_5;
139 drop table user_stopword;
140 drop table user_stopword_session;
141
142 eval SET GLOBAL innodb_ft_enable_stopword=$innodb_ft_enable_stopword_orig;
143 eval SET GLOBAL innodb_ft_server_stopword_table=default;
144
145 #---------------------------------------------------------------------------------------
146 # Behavior :
147 # The stopword is loaded into memory at
148 # 1) create fulltext index time,
149 # 2) boot server,
150 # 3) first time FTs is used
151 # So if you already created a FTS index, and then turn off stopword
152 # or change stopword table content it won't affect the FTS
153 # that already created since the stopword list are already loaded.
154 # It will only affect the new FTS index created after you changed
155 # the settings.
156
157 # Create FTS table
158 CREATE TABLE articles (
159 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
160 title VARCHAR(200),
161 body TEXT,
162 FULLTEXT `idx` (title,body)
163 ) ENGINE=InnoDB;
164
165 SHOW CREATE TABLE articles;
166
167 # Insert six rows
168 INSERT INTO articles (title,body) VALUES
169 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
170 ('when To Use MySQL Well','After that you went through a ...'),
171 ('where will Optimizing MySQL','In what tutorial we will show ...'),
172 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
173 ('MySQL vs. YourSQL','In the following database comparison ...'),
174 ('MySQL Security','When configured properly, MySQL ...');
175
176 # Case : server_stopword=default
177 # Try to Search default stopword from innodb, "where", "will", "what"
178 # and "when" are all stopwords
179 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will");
180 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when");
181 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION);
182 # boolean No result expected
183 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE);
184 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE);
185 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE);
186 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE);
187 # no result expected
188 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE);
189 # no result expected
190 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE);
191
192 INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words');
193 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
194 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE);
195 # Not going to update as where condition can not find record
196 UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not'
197 WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
198 # Update the record
199 UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not'
200 WHERE id = 7;
201 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
202 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
203 # Delete will not work as where condition do not return
204 DELETE FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
205 SELECT * FROM articles WHERE id = 7;
206 DELETE FROM articles WHERE id = 7;
207
208
209
210 # Case : Turn OFF stopword list variable and search stopword on OLD index.
211 # disable stopword list
212 #SET global innodb_ft_server_stopword_table = "";
213 SET SESSION innodb_ft_enable_stopword = 0;
214 select @@innodb_ft_enable_stopword;
215 #SET global innodb_ft_user_stopword_table = "";
216
217 # search default stopword with innodb_ft_enable_stopword is OFF.
218 # No records expected even though we turned OFF stopwod filtering
219 # (refer Behavior (at the top of the test) for explanation )
220 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will");
221 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when");
222 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION);
223 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE);
224 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE);
225 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE);
226 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE);
227 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE);
228 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE);
229
230 INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words');
231 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
232 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE);
233 # Not going to update as where condition can not find record
234 UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not'
235 WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
236 # Update the record
237 UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not'
238 WHERE id = 8;
239 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
240 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
241 SELECT * FROM articles WHERE id = 8;
242 # Delete will not work as where condition do not return
243 DELETE FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
244 SELECT * FROM articles WHERE id = 8;
245 DELETE FROM articles WHERE id = 8;
246
247 # Case : Turn OFF stopword list variable and search stopword on NEW index.
248 # Drop index
249 ALTER TABLE articles DROP INDEX idx;
250 SHOW CREATE TABLE articles;
251
252 # Create the FTS index Using Alter Table.
253 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
254
255 ANALYZE TABLE articles;
256
257 # search default stopword with innodb_ft_enable_stopword is OFF.
258 # All records expected as stopwod filtering is OFF and we created
259 # new FTS index.
260 # (refer Behavior (at the top of the test) for explanation )
261 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will");
262 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when");
263 --sorted_result
264 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION);
265 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE);
266 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE);
267 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE);
268 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE);
269 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE);
270 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE);
271
272 INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words');
273 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
274 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE);
275 # Update will succeed.
276 UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not'
277 WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
278
279 SELECT COUNT(*),max(id) FROM articles;
280 # Update the record - uncommet on fix
281 #UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not'
282 #WHERE id = 9;
283 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
284 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
285 # Delete will succeed.
286 DELETE FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
287 SELECT * FROM articles WHERE id = 9;
288
289
290 DROP TABLE articles;
291
292 eval SET SESSION innodb_ft_enable_stopword=$innodb_ft_enable_stopword_orig;
293 #eval SET GLOBAL innodb_ft_server_stopword_table=$innodb_ft_server_stopword_table_orig;
294 eval SET GLOBAL innodb_ft_server_stopword_table=default;
295 #eval SET GLOBAL innodb_ft_user_stopword_table=$innodb_ft_user_stopword_table_orig;
296 eval SET SESSION innodb_ft_user_stopword_table=default;
297
298 #---------------------------------------------------------------------------------------
299
300 select @@innodb_ft_server_stopword_table;
301 select @@innodb_ft_enable_stopword;
302 select @@innodb_ft_user_stopword_table;
303
304 # Create FTS table
305 CREATE TABLE articles (
306 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
307 title VARCHAR(200),
308 body TEXT,
309 FULLTEXT `idx` (title,body)
310 ) ENGINE=InnoDB;
311
312 # Insert six rows
313 INSERT INTO articles (title,body) VALUES
314 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
315 ('when To Use MySQL Well','After that you went through a ...'),
316 ('where will Optimizing MySQL','In what tutorial we will show ...'),
317 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
318 ('MySQL vs. YourSQL','In the following database comparison ...'),
319 ('MySQL Security','When configured properly, MySQL ...');
320
321 # No records expeced for select
322 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
323 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
324 # Define a correct formated user stopword table
325 create table user_stopword(value varchar(30)) engine = innodb;
326 # The set operation should be successful
327 set session innodb_ft_user_stopword_table = "test/user_stopword";
328 # Define a correct formated server stopword table
329 create table server_stopword(value varchar(30)) engine = innodb;
330 # The set operation should be successful
331 set global innodb_ft_server_stopword_table = "test/server_stopword";
332 # Add values into user supplied stop word table
333 insert into user_stopword values("this"),("will"),("the");
334
335 # Drop existing index and create the FTS index Using Alter Table.
336 ALTER TABLE articles DROP INDEX idx;
337 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
338 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
339 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
340
341 # Add values into server supplied stop word table
342 insert into server_stopword values("what"),("where");
343 # Follwoing should return result as server stopword list was empty at create index time
344 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
345 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
346
347 # Delete stopword from user list
348 DELETE FROM user_stopword;
349 # Drop existing index and create the FTS index Using Alter Table.
350 ALTER TABLE articles DROP INDEX idx;
351 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
352 # Follwoing should return result even though to server stopword list
353 # conatin these words. Session level stopword list takes priority
354 # Here user_stopword is set using innodb_ft_user_stopword_table
355 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
356 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
357
358 # Follwoing should return result as user stopword list was empty at create index time
359 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
360 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
361
362 # Add values into user supplied stop word table
363 insert into user_stopword values("this"),("will"),("the");
364
365 # Drop existing index and create the FTS index Using Alter Table.
366 ALTER TABLE articles DROP INDEX idx;
367 SET SESSION innodb_ft_enable_stopword = 0;
368 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
369 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
370 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
371 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
372 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
373
374 # Session level stopword list takes priority
375 SET SESSION innodb_ft_enable_stopword = 1;
376 ALTER TABLE articles DROP INDEX idx;
377 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
378 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
379 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
380 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
381 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
382
383 # Make user stopword list deafult so as to server stopword list takes priority
384 SET SESSION innodb_ft_enable_stopword = 1;
385 SET SESSION innodb_ft_user_stopword_table = default;
386 ALTER TABLE articles DROP INDEX idx;
387 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
388 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
389 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
390 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
391 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
392
393
394 DROP TABLE articles,user_stopword,server_stopword;
395
396 # Restore Defaults
397 eval SET SESSION innodb_ft_enable_stopword=$innodb_ft_enable_stopword_orig;
398 eval SET GLOBAL innodb_ft_server_stopword_table=default;
399 eval SET SESSION innodb_ft_user_stopword_table=default;
400 select @@innodb_ft_server_stopword_table;
401 select @@innodb_ft_enable_stopword;
402 select @@innodb_ft_user_stopword_table;
403
404 #---------------------------------------------------------------------------------------
405 # Create FTS table
406 CREATE TABLE articles (
407 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
408 title VARCHAR(200),
409 body TEXT,
410 FULLTEXT `idx` (title,body)
411 ) ENGINE=InnoDB;
412
413 SHOW CREATE TABLE articles;
414
415 # Insert six rows
416 INSERT INTO articles (title,body) VALUES
417 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
418 ('when To Use MySQL Well','After that you went through a ...'),
419 ('where will Optimizing MySQL','In what tutorial we will show ...'),
420 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
421 ('MySQL vs. YourSQL','In the following database comparison ...'),
422 ('MySQL Security','When configured properly, MySQL ...');
423
424 # No records expeced for select
425 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
426 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
427 # Define a correct formated user stopword table
428 create table user_stopword(value varchar(30)) engine = innodb;
429 # The set operation should be successful
430 set session innodb_ft_user_stopword_table = "test/user_stopword";
431 insert into user_stopword values("mysqld"),("DBMS");
432 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
433 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
434 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+DBMS +mysql" IN BOOLEAN MODE);
435 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld');
436
437
438 # Drop existing index and create the FTS index Using Alter Table.
439 # user stopword list will take effect.
440 ALTER TABLE articles DROP INDEX idx;
441 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
442 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
443 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
444 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+DBMS +mysql" IN BOOLEAN MODE);
445 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld');
446
447 # set user stopword list empty
448 set session innodb_ft_user_stopword_table = default;
449 # Define a correct formated user stopword table
450 create table server_stopword(value varchar(30)) engine = innodb;
451 # The set operation should be successful
452 set global innodb_ft_server_stopword_table = "test/server_stopword";
453 insert into server_stopword values("root"),("properly");
454 ALTER TABLE articles DROP INDEX idx;
455 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
456 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
457 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
458 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+root +mysql" IN BOOLEAN MODE);
459 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('properly');
460
461
462 # set user stopword list empty
463 set session innodb_ft_user_stopword_table = "test/user_stopword";
464 # The set operation should be successful
465 set global innodb_ft_server_stopword_table = "test/server_stopword";
466 # user stopword list take effect as its session level
467 # Result expected for select
468 ALTER TABLE articles DROP INDEX idx;
469 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
470 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
471 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
472 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+root +mysql" IN BOOLEAN MODE);
473 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('properly');
474
475 # set user stopword list
476 set session innodb_ft_user_stopword_table = "test/user_stopword";
477 DELETE FROM user_stopword;
478 # The set operation should be successful
479 set global innodb_ft_server_stopword_table = "test/server_stopword";
480 DELETE FROM server_stopword;
481 # user stopword list take affect as its session level
482 ALTER TABLE articles DROP INDEX idx;
483 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
484 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE);
485 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what');
486 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+root +mysql" IN BOOLEAN MODE);
487 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('properly');
488 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+DBMS +mysql" IN BOOLEAN MODE);
489 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld');
490
491 DROP TABLE articles,user_stopword,server_stopword;
492
493 # Restore Values
494 eval SET SESSION innodb_ft_enable_stopword=$innodb_ft_enable_stopword_orig;
495 eval SET GLOBAL innodb_ft_server_stopword_table=default;
496 eval SET SESSION innodb_ft_user_stopword_table=default;
497
498
499 #------------------------------------------------------------------------------
500 # FTS stopword list test - check varaibles across sessions
501
502 # Create FTS table
503 CREATE TABLE articles (
504 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
505 title VARCHAR(200),
506 body TEXT,
507 FULLTEXT `idx` (title,body)
508 ) ENGINE=InnoDB;
509
510 SHOW CREATE TABLE articles;
511
512 # Insert six rows
513 INSERT INTO articles (title,body) VALUES
514 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
515 ('when To Use MySQL Well','After that you went through a ...'),
516 ('where will Optimizing MySQL','In what tutorial we will show ...'),
517 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
518 ('MySQL vs. YourSQL','In the following database comparison ...'),
519 ('MySQL Security','When configured properly, MySQL ...');
520
521 # session varaible innodb_ft_enable_stopword=0 will take effect for new FTS index
522 SET SESSION innodb_ft_enable_stopword = 0;
523 select @@innodb_ft_enable_stopword;
524
525 ALTER TABLE articles DROP INDEX idx;
526 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
527
528
529 --echo "In connection 1"
530 --connection con1
531 select @@innodb_ft_enable_stopword;
532
533 ANALYZE TABLE articles;
534
535 # result expected as index created before setting innodb_ft_enable_stopword varaible off
536 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will");
537 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when");
538 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION);
539 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE);
540 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE);
541 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE);
542 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE);
543 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE);
544 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE);
545
546 SET SESSION innodb_ft_enable_stopword = 1;
547 select @@innodb_ft_enable_stopword;
548 ALTER TABLE articles DROP INDEX idx;
549 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
550 # no result expected turned innodb_ft_enable_stopword is ON
551 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will");
552 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when");
553 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION);
554 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE);
555 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE);
556 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE);
557 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE);
558 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE);
559 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE);
560
561
562 --echo "In connection default"
563 --connection default
564 select @@innodb_ft_enable_stopword;
565 # no result expected as word not indexed from connection 1
566 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will");
567 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when");
568 SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION);
569 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE);
570 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE);
571 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE);
572 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE);
573 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE);
574 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE);
575
576 INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words');
577 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
578 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE);
579
580 SET SESSION innodb_ft_enable_stopword = 1;
581 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE);
582 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE);
583
584
585 --echo "In connection 1"
586 --connection con1
587 SET SESSION innodb_ft_enable_stopword = 1;
588 # Define a correct formated user stopword table
589 create table user_stopword(value varchar(30)) engine = innodb;
590 # The set operation should be successful
591 set session innodb_ft_user_stopword_table = "test/user_stopword";
592 # Add values into user supplied stop word table
593 insert into user_stopword values("this"),("will"),("the");
594 # Drop existing index and create the FTS index Using Alter Table.
595 ALTER TABLE articles DROP INDEX idx;
596 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
597 # no result expected as innodb_ft_user_stopword_table filter it
598 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
599 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
600
601
602 --echo "In connection default"
603 --connection default
604 # no result expected as innodb_ft_user_stopword_table filter it from connection1
605 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE);
606 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will');
607 select @@innodb_ft_user_stopword_table;
608 # Define a correct formated user stopword table
609 create table user_stopword_1(value varchar(30)) engine = innodb;
610 # The set operation should be successful
611 set session innodb_ft_user_stopword_table = "test/user_stopword_1";
612 insert into user_stopword_1 values("when");
613 SET SESSION innodb_ft_enable_stopword = 1;
614 # result expected
615 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE);
616 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('when');
617 # Drop existing index and create the FTS index Using Alter Table.
618 ALTER TABLE articles DROP INDEX idx;
619 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
620 # no result expected
621 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE);
622 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('when');
623
624 --echo "In connection 1"
625 --connection con1
626 SET SESSION innodb_ft_enable_stopword = 1;
627 SET SESSION innodb_ft_user_stopword_table=default;
628 select @@innodb_ft_user_stopword_table;
629 select @@innodb_ft_server_stopword_table;
630 # Define a correct formated server stopword table
631 create table server_stopword(value varchar(30)) engine = innodb;
632 # The set operation should be successful
633 SET GLOBAL innodb_ft_server_stopword_table = "test/server_stopword";
634 select @@innodb_ft_server_stopword_table;
635 insert into server_stopword values("when"),("the");
636 # Drop existing index and create the FTS index Using Alter Table.
637 ALTER TABLE articles DROP INDEX idx;
638 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
639 # no result expected
640 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE);
641 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('the');
642
643 disconnect con1;
644 --source include/wait_until_disconnected.inc
645
646 --echo "In connection default"
647 --connection default
648 SET SESSION innodb_ft_enable_stopword = 1;
649 SET SESSION innodb_ft_user_stopword_table=default;
650 select @@innodb_ft_server_stopword_table;
651 # result expected
652 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+will +where" IN BOOLEAN MODE);
653 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('where');
654 insert into server_stopword values("where"),("will");
655 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+will +where" IN BOOLEAN MODE);
656 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('where');
657 ALTER TABLE articles DROP INDEX idx;
658 ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body);
659 # no result expected
660 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE);
661 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('the');
662 SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+will +where" IN BOOLEAN MODE);
663 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('where');
664
665
666 DROP TABLE articles,user_stopword,user_stopword_1,server_stopword;
667
668 # Restore Values
669 eval SET SESSION innodb_ft_enable_stopword=$innodb_ft_enable_stopword_orig;
670 eval SET GLOBAL innodb_ft_server_stopword_table=default;
671 eval SET SESSION innodb_ft_user_stopword_table=default;