Debian Codegraph @

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

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