diff options
Diffstat (limited to 'mysql-test/suite/innodb_fts/t/innodb-fts-stopword.test')
| -rw-r--r-- | mysql-test/suite/innodb_fts/t/innodb-fts-stopword.test | 670 |
1 files changed, 670 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb_fts/t/innodb-fts-stopword.test b/mysql-test/suite/innodb_fts/t/innodb-fts-stopword.test new file mode 100644 index 00000000000..5c2d383d101 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/innodb-fts-stopword.test @@ -0,0 +1,670 @@ +# This is the basic function tests for innodb FTS + +-- source include/have_innodb.inc + + +select * from information_schema.innodb_ft_default_stopword; + +# Create FTS table +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT (title,body) + ) ENGINE=InnoDB; + +# Insert six rows +INSERT INTO articles (title,body) VALUES + ('MySQL Tutorial','DBMS stands for DataBase ...') , + ('How To Use MySQL Well','After you went through a ...'), + ('Optimizing MySQL','In this tutorial we will show ...'), + ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), + ('MySQL vs. YourSQL','In the following database comparison ...'), + ('MySQL Security','When configured properly, MySQL ...'); + +# "the" is in the default stopword, it would not be selected +SELECT * FROM articles WHERE MATCH (title,body) + AGAINST ('the' IN NATURAL LANGUAGE MODE); + +let $innodb_ft_server_stopword_table_orig=`select @@innodb_ft_server_stopword_table`; +let $innodb_ft_enable_stopword_orig=`select @@innodb_ft_enable_stopword`; +let $innodb_ft_user_stopword_table_orig=`select @@innodb_ft_user_stopword_table`; + +select @@innodb_ft_server_stopword_table; +select @@innodb_ft_enable_stopword; +select @@innodb_ft_user_stopword_table; + +# Provide user defined stopword table, if not (correctly) defined, +# it will be rejected +--error 1231 +set global innodb_ft_server_stopword_table = "not_defined"; + +# Define a correct formated user stopword table +create table user_stopword(value varchar(30)) engine = innodb; + +# The set operation should be successful +set global innodb_ft_server_stopword_table = "test/user_stopword"; + +drop index title on articles; + +create fulltext index idx on articles(title, body); + +# Now we should be able to find "the" +SELECT * FROM articles WHERE MATCH (title,body) + AGAINST ('the' IN NATURAL LANGUAGE MODE); + +# Nothing inserted into the default stopword, so essentially +# nothing get screened. The new stopword could only be +# effective for table created thereafter +CREATE TABLE articles_2 ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT (title,body) + ) ENGINE=InnoDB; + +INSERT INTO articles_2 (title, body) + VALUES ('test for stopwords','this is it...'); + +# Now we can find record with "this" +SELECT * FROM articles_2 WHERE MATCH (title,body) + AGAINST ('this' IN NATURAL LANGUAGE MODE); + +# Ok, let's instantiate some value into user supplied stop word +# table +insert into user_stopword values("this"); + +# Ok, let's repeat with the new table again. +CREATE TABLE articles_3 ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT (title,body) + ) ENGINE=InnoDB; + +INSERT INTO articles_3 (title, body) + VALUES ('test for stopwords','this is it...'); + +# Now we should NOT find record with "this" +SELECT * FROM articles_3 WHERE MATCH (title,body) + AGAINST ('this' IN NATURAL LANGUAGE MODE); + +# Test session level stopword control "innodb_user_stopword_table" +create table user_stopword_session(value varchar(30)) engine = innodb; + +insert into user_stopword_session values("session"); + +set session innodb_ft_user_stopword_table="test/user_stopword_session"; + +CREATE TABLE articles_4 ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT (title,body) + ) ENGINE=InnoDB; + +INSERT INTO articles_4 (title, body) + VALUES ('test for session stopwords','this should also be excluded...'); + +# "session" is excluded +SELECT * FROM articles_4 WHERE MATCH (title,body) + AGAINST ('session' IN NATURAL LANGUAGE MODE); + +# But we can find record with "this" +SELECT * FROM articles_4 WHERE MATCH (title,body) + AGAINST ('this' IN NATURAL LANGUAGE MODE); + +--connect (con1,localhost,root,,) +CREATE TABLE articles_5 ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT (title,body) + ) ENGINE=InnoDB; + +INSERT INTO articles_5 (title, body) + VALUES ('test for session stopwords','this should also be excluded...'); + +# "session" should be found since the stopword table is session specific +SELECT * FROM articles_5 WHERE MATCH (title,body) + AGAINST ('session' IN NATURAL LANGUAGE MODE); + +--connection default +drop table articles; +drop table articles_2; +drop table articles_3; +drop table articles_4; +drop table articles_5; +drop table user_stopword; +drop table user_stopword_session; + +eval SET GLOBAL innodb_ft_enable_stopword=$innodb_ft_enable_stopword_orig; +eval SET GLOBAL innodb_ft_server_stopword_table=default; + +#--------------------------------------------------------------------------------------- +# Behavior : +# The stopword is loaded into memory at +# 1) create fulltext index time, +# 2) boot server, +# 3) first time FTs is used +# So if you already created a FTS index, and then turn off stopword +# or change stopword table content it won't affect the FTS +# that already created since the stopword list are already loaded. +# It will only affect the new FTS index created after you changed +# the settings. + +# Create FTS table +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT `idx` (title,body) + ) ENGINE=InnoDB; + +SHOW CREATE TABLE articles; + +# Insert six rows +INSERT INTO articles (title,body) VALUES + ('MySQL from Tutorial','DBMS stands for DataBase ...') , + ('when To Use MySQL Well','After that you went through a ...'), + ('where will Optimizing MySQL','In what tutorial we will show ...'), + ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), + ('MySQL vs. YourSQL','In the following database comparison ...'), + ('MySQL Security','When configured properly, MySQL ...'); + +# Case : server_stopword=default +# Try to Search default stopword from innodb, "where", "will", "what" +# and "when" are all stopwords +SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will"); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when"); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION); +# boolean No result expected +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE); +# no result expected +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE); +# no result expected +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); + +INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words'); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE); +# Not going to update as where condition can not find record +UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not' +WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); +# Update the record +UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not' +WHERE id = 7; +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); +# Delete will not work as where condition do not return +DELETE FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE id = 7; +DELETE FROM articles WHERE id = 7; + + + +# Case : Turn OFF stopword list variable and search stopword on OLD index. +# disable stopword list +#SET global innodb_ft_server_stopword_table = ""; +SET SESSION innodb_ft_enable_stopword = 0; +select @@innodb_ft_enable_stopword; +#SET global innodb_ft_user_stopword_table = ""; + +# search default stopword with innodb_ft_enable_stopword is OFF. +# No records expected even though we turned OFF stopwod filtering +# (refer Behavior (at the top of the test) for explanation ) +SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will"); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when"); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); + +INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words'); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE); +# Not going to update as where condition can not find record +UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not' +WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); +# Update the record +UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not' +WHERE id = 8; +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); +SELECT * FROM articles WHERE id = 8; +# Delete will not work as where condition do not return +DELETE FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE id = 8; +DELETE FROM articles WHERE id = 8; + +# Case : Turn OFF stopword list variable and search stopword on NEW index. +# Drop index +ALTER TABLE articles DROP INDEX idx; +SHOW CREATE TABLE articles; + +# Create the FTS index Using Alter Table. +ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); + +ANALYZE TABLE articles; + +# search default stopword with innodb_ft_enable_stopword is OFF. +# All records expected as stopwod filtering is OFF and we created +# new FTS index. +# (refer Behavior (at the top of the test) for explanation ) +SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will"); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when"); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); + +INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words'); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE); +# Update will succeed. +UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not' +WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); + +SELECT COUNT(*),max(id) FROM articles; +# Update the record - uncommet on fix +#UPDATE articles SET title = "update the record" , body = 'to see will is indexed or not' +#WHERE id = 9; +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); +# Delete will succeed. +DELETE FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE id = 9; + + +DROP TABLE articles; + +eval SET SESSION innodb_ft_enable_stopword=$innodb_ft_enable_stopword_orig; +#eval SET GLOBAL innodb_ft_server_stopword_table=$innodb_ft_server_stopword_table_orig; +eval SET GLOBAL innodb_ft_server_stopword_table=default; +#eval SET GLOBAL innodb_ft_user_stopword_table=$innodb_ft_user_stopword_table_orig; +eval SET SESSION innodb_ft_user_stopword_table=default; + +#--------------------------------------------------------------------------------------- + +select @@innodb_ft_server_stopword_table; +select @@innodb_ft_enable_stopword; +select @@innodb_ft_user_stopword_table; + +# Create FTS table +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT `idx` (title,body) + ) ENGINE=InnoDB; + +# Insert six rows +INSERT INTO articles (title,body) VALUES + ('MySQL from Tutorial','DBMS stands for DataBase ...') , + ('when To Use MySQL Well','After that you went through a ...'), + ('where will Optimizing MySQL','In what tutorial we will show ...'), + ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), + ('MySQL vs. YourSQL','In the following database comparison ...'), + ('MySQL Security','When configured properly, MySQL ...'); + +# No records expeced for select +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); +# Define a correct formated user stopword table +create table user_stopword(value varchar(30)) engine = innodb; +# The set operation should be successful +set session innodb_ft_user_stopword_table = "test/user_stopword"; +# Define a correct formated server stopword table +create table server_stopword(value varchar(30)) engine = innodb; +# The set operation should be successful +set global innodb_ft_server_stopword_table = "test/server_stopword"; +# Add values into user supplied stop word table +insert into user_stopword values("this"),("will"),("the"); + +# Drop existing index and create the FTS index Using Alter Table. +ALTER TABLE articles DROP INDEX idx; +ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); + +# Add values into server supplied stop word table +insert into server_stopword values("what"),("where"); +# Follwoing should return result as server stopword list was empty at create index time +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); + +# Delete stopword from user list +DELETE FROM user_stopword; +# Drop existing index and create the FTS index Using Alter Table. +ALTER TABLE articles DROP INDEX idx; +ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); +# Follwoing should return result even though to server stopword list +# conatin these words. Session level stopword list takes priority +# Here user_stopword is set using innodb_ft_user_stopword_table +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); + +# Follwoing should return result as user stopword list was empty at create index time +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); + +# Add values into user supplied stop word table +insert into user_stopword values("this"),("will"),("the"); + +# Drop existing index and create the FTS index Using Alter Table. +ALTER TABLE articles DROP INDEX idx; +SET SESSION innodb_ft_enable_stopword = 0; +ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); + +# Session level stopword list takes priority +SET SESSION innodb_ft_enable_stopword = 1; +ALTER TABLE articles DROP INDEX idx; +ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); + +# Make user stopword list deafult so as to server stopword list takes priority +SET SESSION innodb_ft_enable_stopword = 1; +SET SESSION innodb_ft_user_stopword_table = default; +ALTER TABLE articles DROP INDEX idx; +ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); + + +DROP TABLE articles,user_stopword,server_stopword; + +# Restore Defaults +eval SET SESSION innodb_ft_enable_stopword=$innodb_ft_enable_stopword_orig; +eval SET GLOBAL innodb_ft_server_stopword_table=default; +eval SET SESSION innodb_ft_user_stopword_table=default; +select @@innodb_ft_server_stopword_table; +select @@innodb_ft_enable_stopword; +select @@innodb_ft_user_stopword_table; + +#--------------------------------------------------------------------------------------- +# Create FTS table +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT `idx` (title,body) + ) ENGINE=InnoDB; + +SHOW CREATE TABLE articles; + +# Insert six rows +INSERT INTO articles (title,body) VALUES + ('MySQL from Tutorial','DBMS stands for DataBase ...') , + ('when To Use MySQL Well','After that you went through a ...'), + ('where will Optimizing MySQL','In what tutorial we will show ...'), + ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), + ('MySQL vs. YourSQL','In the following database comparison ...'), + ('MySQL Security','When configured properly, MySQL ...'); + +# No records expeced for select +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); +# Define a correct formated user stopword table +create table user_stopword(value varchar(30)) engine = innodb; +# The set operation should be successful +set session innodb_ft_user_stopword_table = "test/user_stopword"; +insert into user_stopword values("mysqld"),("DBMS"); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+DBMS +mysql" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld'); + + +# Drop existing index and create the FTS index Using Alter Table. +# user stopword list will take effect. +ALTER TABLE articles DROP INDEX idx; +ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+DBMS +mysql" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld'); + +# set user stopword list empty +set session innodb_ft_user_stopword_table = default; +# Define a correct formated user stopword table +create table server_stopword(value varchar(30)) engine = innodb; +# The set operation should be successful +set global innodb_ft_server_stopword_table = "test/server_stopword"; +insert into server_stopword values("root"),("properly"); +ALTER TABLE articles DROP INDEX idx; +ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+root +mysql" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('properly'); + + +# set user stopword list empty +set session innodb_ft_user_stopword_table = "test/user_stopword"; +# The set operation should be successful +set global innodb_ft_server_stopword_table = "test/server_stopword"; +# user stopword list take effect as its session level +# Result expected for select +ALTER TABLE articles DROP INDEX idx; +ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+root +mysql" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('properly'); + +# set user stopword list +set session innodb_ft_user_stopword_table = "test/user_stopword"; +DELETE FROM user_stopword; +# The set operation should be successful +set global innodb_ft_server_stopword_table = "test/server_stopword"; +DELETE FROM server_stopword; +# user stopword list take affect as its session level +ALTER TABLE articles DROP INDEX idx; +ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+wha* +where" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('what'); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+root +mysql" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('properly'); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+DBMS +mysql" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysqld'); + +DROP TABLE articles,user_stopword,server_stopword; + +# Restore Values +eval SET SESSION innodb_ft_enable_stopword=$innodb_ft_enable_stopword_orig; +eval SET GLOBAL innodb_ft_server_stopword_table=default; +eval SET SESSION innodb_ft_user_stopword_table=default; + + +#------------------------------------------------------------------------------ +# FTS stopword list test - check varaibles across sessions + +# Create FTS table +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT `idx` (title,body) + ) ENGINE=InnoDB; + +SHOW CREATE TABLE articles; + +# Insert six rows +INSERT INTO articles (title,body) VALUES + ('MySQL from Tutorial','DBMS stands for DataBase ...') , + ('when To Use MySQL Well','After that you went through a ...'), + ('where will Optimizing MySQL','In what tutorial we will show ...'), + ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), + ('MySQL vs. YourSQL','In the following database comparison ...'), + ('MySQL Security','When configured properly, MySQL ...'); + +# session varaible innodb_ft_enable_stopword=0 will take effect for new FTS index +SET SESSION innodb_ft_enable_stopword = 0; +select @@innodb_ft_enable_stopword; + +ALTER TABLE articles DROP INDEX idx; +ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); + + +--echo "In connection 1" +--connection con1 +select @@innodb_ft_enable_stopword; + +ANALYZE TABLE articles; + +# result expected as index created before setting innodb_ft_enable_stopword varaible off +SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will"); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when"); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); + +SET SESSION innodb_ft_enable_stopword = 1; +select @@innodb_ft_enable_stopword; +ALTER TABLE articles DROP INDEX idx; +ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); +# no result expected turned innodb_ft_enable_stopword is ON +SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will"); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when"); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); + + +--echo "In connection default" +--connection default +select @@innodb_ft_enable_stopword; +# no result expected as word not indexed from connection 1 +SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("where will"); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("when"); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST ("what" WITH QUERY EXPANSION); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("whe*" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+what +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+from" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+where +(show what)" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@6' IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"where will"@9' IN BOOLEAN MODE); + +INSERT INTO articles(title,body) values ('the record will' , 'not index the , will words'); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE); + +SET SESSION innodb_ft_enable_stopword = 1; +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+the +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"the will"@11' IN BOOLEAN MODE); + + +--echo "In connection 1" +--connection con1 +SET SESSION innodb_ft_enable_stopword = 1; +# Define a correct formated user stopword table +create table user_stopword(value varchar(30)) engine = innodb; +# The set operation should be successful +set session innodb_ft_user_stopword_table = "test/user_stopword"; +# Add values into user supplied stop word table +insert into user_stopword values("this"),("will"),("the"); +# Drop existing index and create the FTS index Using Alter Table. +ALTER TABLE articles DROP INDEX idx; +ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); +# no result expected as innodb_ft_user_stopword_table filter it +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); + + +--echo "In connection default" +--connection default +# no result expected as innodb_ft_user_stopword_table filter it from connection1 +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+show +will" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('will'); +select @@innodb_ft_user_stopword_table; +# Define a correct formated user stopword table +create table user_stopword_1(value varchar(30)) engine = innodb; +# The set operation should be successful +set session innodb_ft_user_stopword_table = "test/user_stopword_1"; +insert into user_stopword_1 values("when"); +SET SESSION innodb_ft_enable_stopword = 1; +# result expected +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('when'); +# Drop existing index and create the FTS index Using Alter Table. +ALTER TABLE articles DROP INDEX idx; +ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); +# no result expected +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('when'); + +--echo "In connection 1" +--connection con1 +SET SESSION innodb_ft_enable_stopword = 1; +SET SESSION innodb_ft_user_stopword_table=default; +select @@innodb_ft_user_stopword_table; +select @@innodb_ft_server_stopword_table; +# Define a correct formated server stopword table +create table server_stopword(value varchar(30)) engine = innodb; +# The set operation should be successful +SET GLOBAL innodb_ft_server_stopword_table = "test/server_stopword"; +select @@innodb_ft_server_stopword_table; +insert into server_stopword values("when"),("the"); +# Drop existing index and create the FTS index Using Alter Table. +ALTER TABLE articles DROP INDEX idx; +ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); +# no result expected +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('the'); + +disconnect con1; +--source include/wait_until_disconnected.inc + +--echo "In connection default" +--connection default +SET SESSION innodb_ft_enable_stopword = 1; +SET SESSION innodb_ft_user_stopword_table=default; +select @@innodb_ft_server_stopword_table; +# result expected +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+will +where" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('where'); +insert into server_stopword values("where"),("will"); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+will +where" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('where'); +ALTER TABLE articles DROP INDEX idx; +ALTER TABLE articles ADD FULLTEXT INDEX idx (title,body); +# no result expected +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+when" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('the'); +SELECT * FROM articles WHERE MATCH(title,body) AGAINST("+will +where" IN BOOLEAN MODE); +SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('where'); + + +DROP TABLE articles,user_stopword,user_stopword_1,server_stopword; + +# Restore Values +eval SET SESSION innodb_ft_enable_stopword=$innodb_ft_enable_stopword_orig; +eval SET GLOBAL innodb_ft_server_stopword_table=default; +eval SET SESSION innodb_ft_user_stopword_table=default; + |