path: root/mysql-test/suite/innodb_fts/t/stopword.test

# This is the basic function tests for innodb FTS

-- source include/have_innodb.inc
-- let $modify_create_table= 1
-- source include/maybe_versioning.inc

SET @innodb_ft_server_stopword_table_orig=@@innodb_ft_server_stopword_table;
SET @innodb_ft_enable_stopword_orig=@@innodb_ft_enable_stopword;
SET @innodb_ft_user_stopword_table_orig=@@innodb_ft_user_stopword_table;

let $stopword_table= articles;
let $stopword_stage= create_table;
--source stopword.inc

# 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
eval CREATE TABLE articles_2 (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        body TEXT,
        FULLTEXT (title,body)
        )$create_options 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("the");
delete from user_stopword;
insert into user_stopword values("this");

# Ok, let's repeat with the new table again.
eval CREATE TABLE articles_3 (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        body TEXT,
        FULLTEXT (title,body)
        )$create_options 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"
eval create table user_stopword_session(value varchar(30))$create_options engine = innodb;

insert into user_stopword values("this");
delete from user_stopword;
insert into user_stopword_session values("session");

set session innodb_ft_user_stopword_table="test/user_stopword_session";

eval CREATE TABLE articles_4 (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        body TEXT,
        FULLTEXT (title,body)
        )$create_options 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,,)
eval CREATE TABLE articles_5 (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        body TEXT,
        FULLTEXT (title,body)
        )$create_options 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;

SET GLOBAL innodb_ft_enable_stopword=@innodb_ft_enable_stopword_orig;
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
eval CREATE TABLE articles (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        body TEXT,
        FULLTEXT `idx` (title,body)
        )$create_options 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 = NULL;
SET SESSION innodb_ft_enable_stopword = 0;
select @@innodb_ft_enable_stopword;
SET global innodb_ft_user_stopword_table = NULL;

# 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;

SET SESSION innodb_ft_enable_stopword=@innodb_ft_enable_stopword_orig;
SET GLOBAL innodb_ft_server_stopword_table=@innodb_ft_server_stopword_table_orig;
SET GLOBAL innodb_ft_user_stopword_table=@innodb_ft_user_stopword_table_orig;
SET SESSION innodb_ft_user_stopword_table=default;

# Create FTS table
eval CREATE TABLE articles (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        body TEXT,
        FULLTEXT `idx` (title,body)
        )$create_options 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 formatted user stopword table
eval create table user_stopword(value varchar(30))$create_options engine = innodb;
# The set operation should be successful
set session innodb_ft_user_stopword_table = "test/user_stopword";
# Define a correct formatted server stopword table
eval create table server_stopword(value varchar(30))$create_options 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("when"),("where");
delete from user_stopword;
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
SET innodb_ft_enable_stopword=@innodb_ft_enable_stopword_orig;
SET GLOBAL innodb_ft_server_stopword_table=default;
SET SESSION innodb_ft_user_stopword_table=default;

#---------------------------------------------------------------------------------------
# Create FTS table
eval CREATE TABLE articles (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        body TEXT,
        FULLTEXT `idx` (title,body)
        )$create_options 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 formatted user stopword table
eval create table user_stopword(value varchar(30))$create_options 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 formatted user stopword table
eval create table server_stopword(value varchar(30))$create_options 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
SET SESSION innodb_ft_enable_stopword=@innodb_ft_enable_stopword_orig;
SET GLOBAL innodb_ft_server_stopword_table=default;
SET SESSION innodb_ft_user_stopword_table=default;


#------------------------------------------------------------------------------
# FTS stopword list test - check varaibles across sessions

# Create FTS table
eval CREATE TABLE articles (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        body TEXT,
        FULLTEXT `idx` (title,body)
        )$create_options 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 formatted user stopword table
eval create table user_stopword(value varchar(30))$create_options 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 formatted user stopword table
eval create table user_stopword_1(value varchar(30))$create_options 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 formatted server stopword table
eval create table server_stopword(value varchar(30))$create_options 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
SET GLOBAL innodb_ft_user_stopword_table=@innodb_ft_user_stopword_table_orig;
SET GLOBAL innodb_ft_server_stopword_table=@innodb_ft_server_stopword_table_orig;