diff options
Diffstat (limited to 'mysql-test')
19 files changed, 945 insertions, 50 deletions
diff --git a/mysql-test/r/alter_table_autoinc-5574.result b/mysql-test/r/alter_table_autoinc-5574.result new file mode 100644 index 00000000000..9476313c773 --- /dev/null +++ b/mysql-test/r/alter_table_autoinc-5574.result @@ -0,0 +1,11 @@ +create table t1(a int(10)unsigned not null auto_increment primary key, +b varchar(255) not null) engine=innodb default charset=utf8; +insert into t1 values(1,'aaa'),(2,'bbb'); +alter table t1 auto_increment=1; +insert into t1 values(NULL, 'ccc'); +select * from t1; +a b +1 aaa +2 bbb +3 ccc +drop table t1; diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index 7d255e794c0..bc961ba1e46 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -1597,10 +1597,6 @@ select distinct concat(a, b) from t1; concat(a, b) 11113333 drop table t1; -CREATE TABLE t1 ( a char(10) ) ENGINE=InnoDB; -SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE); -ERROR HY000: The table does not have FULLTEXT index to support this query -DROP TABLE t1; CREATE TABLE t1 (a_id tinyint(4) NOT NULL default '0', PRIMARY KEY (a_id)) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO t1 VALUES (1),(2),(3); CREATE TABLE t2 (b_id tinyint(4) NOT NULL default '0',b_a tinyint(4) NOT NULL default '0', PRIMARY KEY (b_id), KEY (b_a), diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test index e2056d66855..157e2480740 100644 --- a/mysql-test/suite/innodb/t/innodb.test +++ b/mysql-test/suite/innodb/t/innodb.test @@ -1245,16 +1245,6 @@ select distinct concat(a, b) from t1; drop table t1; # -# BUG#7709 test case - Boolean fulltext query against unsupported -# engines does not fail -# - -CREATE TABLE t1 ( a char(10) ) ENGINE=InnoDB; ---error 1764 -SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE); -DROP TABLE t1; - -# # check null values #1 # diff --git a/mysql-test/suite/innodb_fts/r/fulltext.result b/mysql-test/suite/innodb_fts/r/fulltext.result index f3c913110d2..e665fd1370f 100644 --- a/mysql-test/suite/innodb_fts/r/fulltext.result +++ b/mysql-test/suite/innodb_fts/r/fulltext.result @@ -337,9 +337,9 @@ insert into t2 values (1, 1, 'xxfoo'); insert into t2 values (2, 1, 'xxbar'); insert into t2 values (3, 1, 'xxbuz'); select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in boolean mode); -ERROR HY000: The table does not have FULLTEXT index to support this query +ERROR HY000: Incorrect arguments to MATCH select * from t2 where match name against ('*a*b*c*d*e*f*' in boolean mode); -ERROR HY000: The table does not have FULLTEXT index to support this query +ERROR HY000: Can't find FULLTEXT index matching the column list drop table t1,t2; create table t1 (a text, fulltext key (a)) ENGINE = InnoDB; insert into t1 select "xxxx yyyy zzzz"; @@ -479,16 +479,13 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 fulltext a a 0 1 Using where EXPLAIN SELECT * FROM t1 IGNORE INDEX(a) WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL b NULL NULL NULL 8 Using where +ERROR HY000: Can't find FULLTEXT index matching the column list EXPLAIN SELECT * FROM t1 USE INDEX(b) WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL b NULL NULL NULL 8 Using where +ERROR HY000: Can't find FULLTEXT index matching the column list EXPLAIN SELECT * FROM t1 FORCE INDEX(b) WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref b b 5 const 5 Using where +ERROR HY000: Can't find FULLTEXT index matching the column list DROP TABLE t1; CREATE TABLE t1(a CHAR(10), fulltext(a)) ENGINE = InnoDB; INSERT INTO t1 VALUES('aaa15'); @@ -562,14 +559,12 @@ WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) ); count(*) 1 -# should return 0 SELECT count(*) FROM t1 WHERE not exists( SELECT 1 FROM t2 IGNORE INDEX (b2), t3 WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) ); -count(*) -0 +ERROR HY000: Can't find FULLTEXT index matching the column list DROP TABLE t1,t2,t3; CREATE TABLE t1 (a VARCHAR(4), FULLTEXT(a)) ENGINE = InnoDB; INSERT INTO t1 VALUES diff --git a/mysql-test/suite/innodb_fts/r/fulltext_left_join.result b/mysql-test/suite/innodb_fts/r/fulltext_left_join.result index f40d22caa92..fc8d29bbef2 100644 --- a/mysql-test/suite/innodb_fts/r/fulltext_left_join.result +++ b/mysql-test/suite/innodb_fts/r/fulltext_left_join.result @@ -66,7 +66,7 @@ create table t2 (m_id int not null, f char(200), key (m_id), fulltext (f)) engin insert into t2 values (1, 'bword'), (3, 'aword'), (5, ''); ANALYZE TABLE t2; select * from t1 left join t2 on m_id = id where match(d, e, f) against ('+aword +bword' in boolean mode); -id d e m_id f +ERROR HY000: Incorrect arguments to MATCH drop table t1,t2; CREATE TABLE t1 ( id int(10) NOT NULL auto_increment, @@ -89,9 +89,7 @@ ANALYZE TABLE t2; SELECT t1.*, MATCH(t1.name) AGAINST('string') AS relevance FROM t1 LEFT JOIN t2 ON t1.link = t2.id WHERE MATCH(t1.name, t2.name) AGAINST('string' IN BOOLEAN MODE); -id link name relevance -1 1 string 0.000000001885928302414186 -2 0 string 0.000000001885928302414186 +ERROR HY000: Incorrect arguments to MATCH DROP TABLE t1,t2; CREATE TABLE t1 (a INT) ENGINE = InnoDB; CREATE TABLE t2 (b INT, c TEXT, KEY(b), FULLTEXT(c)) ENGINE = InnoDB; diff --git a/mysql-test/suite/innodb_fts/r/fulltext_misc.result b/mysql-test/suite/innodb_fts/r/fulltext_misc.result index 2e803d1f815..7c342475eeb 100644 --- a/mysql-test/suite/innodb_fts/r/fulltext_misc.result +++ b/mysql-test/suite/innodb_fts/r/fulltext_misc.result @@ -115,7 +115,7 @@ drop table t1, t2; CREATE TABLE t1(a TEXT CHARSET ucs2 COLLATE ucs2_unicode_ci) ENGINE = InnoDB; INSERT INTO t1 VALUES('abcd'); SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abcd' IN BOOLEAN MODE); -ERROR HY000: The table does not have FULLTEXT index to support this query +ERROR HY000: Can't find FULLTEXT index matching the column list DROP TABLE t1; create table t1 (a varchar(10), key(a), fulltext (a)) ENGINE = InnoDB; insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test"); diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result b/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result index 3f22e79a384..f6be36a24ef 100644 --- a/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result +++ b/mysql-test/suite/innodb_fts/r/innodb_fts_misc.result @@ -710,8 +710,7 @@ CREATE FULLTEXT INDEX i ON t1 (char_column2); Warnings: Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID SELECT * FROM t1 WHERE MATCH(char_column) AGAINST ('abc*' IN BOOLEAN MODE); -id char_column char_column2 -NULL abcde abcde +ERROR HY000: Can't find FULLTEXT index matching the column list DROP TABLE t1; "----------Test22---------" CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF8) ENGINE = InnoDB; diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_plugin.result b/mysql-test/suite/innodb_fts/r/innodb_fts_plugin.result new file mode 100644 index 00000000000..569de081762 --- /dev/null +++ b/mysql-test/suite/innodb_fts/r/innodb_fts_plugin.result @@ -0,0 +1,29 @@ +INSTALL PLUGIN simple_parser SONAME 'mypluglib'; +CREATE TABLE articles ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +body TEXT, +FULLTEXT (title) WITH PARSER simple_parser +) ENGINE=MyISAM; +ALTER TABLE articles ENGINE=InnoDB; +ERROR HY000: Cannot CREATE FULLTEXT INDEX WITH PARSER on InnoDB table +DROP TABLE articles; +CREATE TABLE articles ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +body TEXT, +FULLTEXT (title) WITH PARSER simple_parser +) ENGINE=InnoDB; +ERROR HY000: Cannot CREATE FULLTEXT INDEX WITH PARSER on InnoDB table +CREATE TABLE articles ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +body TEXT, +FULLTEXT (title) +) ENGINE=InnoDB; +ALTER TABLE articles ADD FULLTEXT INDEX (body) WITH PARSER simple_parser; +ERROR HY000: Cannot CREATE FULLTEXT INDEX WITH PARSER on InnoDB table +CREATE FULLTEXT INDEX ft_index ON articles(body) WITH PARSER simple_parser; +ERROR HY000: Cannot CREATE FULLTEXT INDEX WITH PARSER on InnoDB table +DROP TABLE articles; +UNINSTALL PLUGIN simple_parser; diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_result_cache_limit.result b/mysql-test/suite/innodb_fts/r/innodb_fts_result_cache_limit.result new file mode 100644 index 00000000000..b4fe5154cc3 --- /dev/null +++ b/mysql-test/suite/innodb_fts/r/innodb_fts_result_cache_limit.result @@ -0,0 +1,31 @@ +CREATE TABLE t1 ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +a VARCHAR(200), +b TEXT +) ENGINE= InnoDB; +CREATE FULLTEXT INDEX idx on t1 (a,b); +Warnings: +Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID +INSERT INTO t1 (a,b) VALUES +('MySQL from Tutorial','DBMS stands for DataBase ...') , +('when To Use MySQL Well','After that you went through a ...'), +('where will Optimizing MySQL','what In this tutorial we will show ...'), +('MySQL from Tutorial','DBMS stands for DataBase ...') , +('when To Use MySQL Well','After that you went through a ...'), +('where will Optimizing MySQL','what In this tutorial we will show ...'), +('MySQL from Tutorial','DBMS stands for DataBase ...') , +('when To Use MySQL Well','After that you went through a ...'), +('where will Optimizing MySQL','what In this tutorial we will show ...'); +SET SESSION debug_dbug="+d,fts_instrument_result_cache_limit"; +SELECT COUNT(*) FROM t1 WHERE MATCH (a,b) AGAINST ('mysql' IN BOOLEAN MODE); +COUNT(*) +9 +SELECT COUNT(*) FROM t1 WHERE MATCH (a,b) AGAINST ('mysql' WITH QUERY EXPANSION); +ERROR HY000: Table handler out of memory +SELECT COUNT(*) FROM t1 WHERE MATCH (a,b) AGAINST ('"mysql database"' IN BOOLEAN MODE); +ERROR HY000: Table handler out of memory +SELECT COUNT(*) FROM t1 WHERE MATCH (a,b) AGAINST ('"mysql database" @ 5' IN BOOLEAN MODE); +ERROR HY000: Table handler out of memory +SET SESSION debug_dbug="-d,fts_instrument_result_cache_limit"; +DROP TABLE t1; +SET GLOBAL innodb_ft_result_cache_limit=default; diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_stopword_charset.result b/mysql-test/suite/innodb_fts/r/innodb_fts_stopword_charset.result new file mode 100644 index 00000000000..5f8d5e37680 --- /dev/null +++ b/mysql-test/suite/innodb_fts/r/innodb_fts_stopword_charset.result @@ -0,0 +1,321 @@ +SELECT @@innodb_ft_server_stopword_table; +@@innodb_ft_server_stopword_table +NULL +SELECT @@innodb_ft_enable_stopword; +@@innodb_ft_enable_stopword +1 +SELECT @@innodb_ft_user_stopword_table; +@@innodb_ft_user_stopword_table +NULL +SET NAMES utf8; +# Test 1 : default latin1_swedish_ci +CREATE TABLE articles ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200) +) ENGINE=InnoDB; +INSERT INTO articles (title) VALUES +('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), +('lṓve'),('LṒVE'); +CREATE FULLTEXT INDEX ft_idx ON articles(title); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('lòve' IN NATURAL LANGUAGE MODE); +id title +1 love +2 LOVE +3 lòve +4 LÒVE +CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB; +INSERT INTO user_stopword VALUES('lòve'); +SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword'; +DROP INDEX ft_idx ON articles; +CREATE FULLTEXT INDEX ft_idx ON articles(title); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('lòve' IN NATURAL LANGUAGE MODE); +id title +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('love' IN NATURAL LANGUAGE MODE); +id title +DROP TABLE articles; +DROP TABLE user_stopword; +# Test 2 : latin1_general_ci +CREATE TABLE articles ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200) +) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci; +INSERT INTO articles (title) VALUES +('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), +('lṓve'),('LṒVE'); +CREATE FULLTEXT INDEX ft_idx ON articles(title); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('lòve' IN NATURAL LANGUAGE MODE); +id title +3 lòve +4 LÒVE +CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB +DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci; +INSERT INTO user_stopword VALUES('lòve'); +SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword'; +DROP INDEX ft_idx ON articles; +CREATE FULLTEXT INDEX ft_idx ON articles(title); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('lòve' IN NATURAL LANGUAGE MODE); +id title +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('love' IN NATURAL LANGUAGE MODE); +id title +1 love +2 LOVE +DROP TABLE articles; +DROP TABLE user_stopword; +# Test 3 : latin1_spanish_ci +CREATE TABLE articles ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200) +) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_spanish_ci; +INSERT INTO articles (title) VALUES +('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), +('lṓve'),('LṒVE'); +CREATE FULLTEXT INDEX ft_idx ON articles(title); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('lòve' IN NATURAL LANGUAGE MODE); +id title +1 love +2 LOVE +3 lòve +4 LÒVE +5 löve +6 LÖVE +7 løve +8 LØVE +CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB +DEFAULT CHARACTER SET latin1 COLLATE latin1_spanish_ci; +INSERT INTO user_stopword VALUES('lòve'); +SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword'; +DROP INDEX ft_idx ON articles; +CREATE FULLTEXT INDEX ft_idx ON articles(title); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('lòve' IN NATURAL LANGUAGE MODE); +id title +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('love' IN NATURAL LANGUAGE MODE); +id title +DROP TABLE articles; +DROP TABLE user_stopword; +# Test 4 : utf8_general_ci +CREATE TABLE articles ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200) +) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; +INSERT INTO articles (title) VALUES +('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), +('lṓve'),('LṒVE'); +CREATE FULLTEXT INDEX ft_idx ON articles(title); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('lòve' IN NATURAL LANGUAGE MODE); +id title +1 love +2 LOVE +3 lòve +4 LÒVE +5 löve +6 LÖVE +9 lṓve +10 LṒVE +CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB +DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; +INSERT INTO user_stopword VALUES('lòve'); +SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword'; +DROP INDEX ft_idx ON articles; +CREATE FULLTEXT INDEX ft_idx ON articles(title); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('lòve' IN NATURAL LANGUAGE MODE); +id title +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('love' IN NATURAL LANGUAGE MODE); +id title +DROP TABLE articles; +DROP TABLE user_stopword; +# Test 5 : utf8_unicode_ci +CREATE TABLE articles ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200) +) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_swedish_ci; +INSERT INTO articles (title) VALUES +('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), +('lṓve'),('LṒVE'); +CREATE FULLTEXT INDEX ft_idx ON articles(title); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('lòve' IN NATURAL LANGUAGE MODE); +id title +1 love +2 LOVE +3 lòve +4 LÒVE +9 lṓve +10 LṒVE +CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB +DEFAULT CHARACTER SET utf8 COLLATE utf8_swedish_ci; +INSERT INTO user_stopword VALUES('lòve'); +SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword'; +DROP INDEX ft_idx ON articles; +CREATE FULLTEXT INDEX ft_idx ON articles(title); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('lòve' IN NATURAL LANGUAGE MODE); +id title +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('love' IN NATURAL LANGUAGE MODE); +id title +DROP TABLE articles; +DROP TABLE user_stopword; +# Test 6 : utf8_unicode_ci +CREATE TABLE articles ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200) +) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; +INSERT INTO articles (title) VALUES +('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), +('lṓve'),('LṒVE'); +CREATE FULLTEXT INDEX ft_idx ON articles(title); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('lòve' IN NATURAL LANGUAGE MODE); +id title +1 love +2 LOVE +3 lòve +4 LÒVE +5 löve +6 LÖVE +9 lṓve +10 LṒVE +CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB +DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; +INSERT INTO user_stopword VALUES('lòve'); +SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword'; +DROP INDEX ft_idx ON articles; +CREATE FULLTEXT INDEX ft_idx ON articles(title); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('lòve' IN NATURAL LANGUAGE MODE); +id title +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('love' IN NATURAL LANGUAGE MODE); +id title +DROP TABLE articles; +DROP TABLE user_stopword; +# Test 7 : gb2312_chinese_ci +CREATE TABLE articles ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200) +) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci; +INSERT INTO articles (title) VALUES +('相亲相爱'),('怜香惜爱'),('充满可爱'),('爱恨交织'); +CREATE FULLTEXT INDEX ft_idx ON articles(title); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('相亲相爱' IN NATURAL LANGUAGE MODE); +id title +1 相亲相爱 +CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB +DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci; +INSERT INTO user_stopword VALUES('相亲相爱'); +SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword'; +DROP INDEX ft_idx ON articles; +CREATE FULLTEXT INDEX ft_idx ON articles(title); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('相亲相爱' IN NATURAL LANGUAGE MODE); +id title +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('怜香惜爱' IN NATURAL LANGUAGE MODE); +id title +2 怜香惜爱 +DROP TABLE articles; +DROP TABLE user_stopword; +# Test 8 : test shutdown to check if stopword still works +CREATE TABLE articles ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200) +) ENGINE=InnoDB; +INSERT INTO articles (title) VALUES +('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), +('lṓve'),('LṒVE'); +CREATE FULLTEXT INDEX ft_idx ON articles(title); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('lòve' IN NATURAL LANGUAGE MODE); +id title +1 love +2 LOVE +3 lòve +4 LÒVE +CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB; +INSERT INTO user_stopword VALUES('lòve'); +SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword'; +DROP INDEX ft_idx ON articles; +CREATE FULLTEXT INDEX ft_idx ON articles(title); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('lòve' IN NATURAL LANGUAGE MODE); +id title +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('love' IN NATURAL LANGUAGE MODE); +id title +# Shutdown and restart mysqld +SET NAMES utf8; +INSERT INTO articles (title) VALUES +('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), +('lṓve'),('LṒVE'); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('lòve' IN NATURAL LANGUAGE MODE); +id title +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('love' IN NATURAL LANGUAGE MODE); +id title +DROP TABLE articles; +DROP TABLE user_stopword; +# Test 9 : drop user stopwrod table,test shutdown to check if it works +CREATE TABLE articles ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200) +) ENGINE=InnoDB; +INSERT INTO articles (title) VALUES +('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), +('lṓve'),('LṒVE'); +CREATE FULLTEXT INDEX ft_idx ON articles(title); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('lòve' IN NATURAL LANGUAGE MODE); +id title +1 love +2 LOVE +3 lòve +4 LÒVE +CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB; +INSERT INTO user_stopword VALUES('lòve'); +SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword'; +DROP INDEX ft_idx ON articles; +CREATE FULLTEXT INDEX ft_idx ON articles(title); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('lòve' IN NATURAL LANGUAGE MODE); +id title +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('love' IN NATURAL LANGUAGE MODE); +id title +DROP TABLE user_stopword; +# Shutdown and restart mysqld +SET NAMES utf8; +INSERT INTO articles (title) VALUES +('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), +('lṓve'),('LṒVE'); +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('lòve' IN NATURAL LANGUAGE MODE); +id title +11 love +12 LOVE +13 lòve +14 LÒVE +SELECT * FROM articles WHERE MATCH (title) +AGAINST ('love' IN NATURAL LANGUAGE MODE); +id title +11 love +12 LOVE +13 lòve +14 LÒVE +DROP TABLE articles; +SET SESSION innodb_ft_enable_stopword=1; +SET GLOBAL innodb_ft_server_stopword_table=default; +SET SESSION innodb_ft_user_stopword_table=default; diff --git a/mysql-test/suite/innodb_fts/t/fulltext.test b/mysql-test/suite/innodb_fts/t/fulltext.test index d75a650ca4d..90d5d5c71e0 100644 --- a/mysql-test/suite/innodb_fts/t/fulltext.test +++ b/mysql-test/suite/innodb_fts/t/fulltext.test @@ -4,11 +4,6 @@ --source include/have_innodb.inc -if (`select plugin_auth_version <= "5.6.10" from information_schema.plugins where plugin_name='innodb'`) -{ - --skip Not fixed in InnoDB 5.6.10 or earlier -} - --disable_warnings drop table if exists t1,t2,t3; --enable_warnings @@ -281,17 +276,17 @@ create table t2 (t2_id int(11) primary key, t1_id int(11), name varchar(32)) ENG insert into t2 values (1, 1, 'xxfoo'); insert into t2 values (2, 1, 'xxbar'); insert into t2 values (3, 1, 'xxbuz'); -# INNODB_FTS: Note there is no fulltext index on table. InnoDB do not support -# Fulltext search in such case, will return 1739 ---error ER_TABLE_HAS_NO_FT +# INNODB_FTS: InnoDB do not support MATCH expressions with arguments from +# different tables +--error ER_WRONG_ARGUMENTS select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in boolean mode); # # Bug #7858: bug with many short (< ft_min_word_len) words in boolean search # # INNODB_FTS: Note there is no fulltext index on table. InnoDB do not support -# Fulltext search in such case, will return 1739 ---error ER_TABLE_HAS_NO_FT +# Fulltext search in such case +--error ER_FT_MATCHING_KEY_NOT_FOUND select * from t2 where match name against ('*a*b*c*d*e*f*' in boolean mode); drop table t1,t2; @@ -490,12 +485,15 @@ WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; EXPLAIN SELECT * FROM t1 FORCE INDEX(a) WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; +--error ER_FT_MATCHING_KEY_NOT_FOUND EXPLAIN SELECT * FROM t1 IGNORE INDEX(a) WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; +--error ER_FT_MATCHING_KEY_NOT_FOUND EXPLAIN SELECT * FROM t1 USE INDEX(b) WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; +--error ER_FT_MATCHING_KEY_NOT_FOUND EXPLAIN SELECT * FROM t1 FORCE INDEX(b) WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; @@ -592,7 +590,7 @@ SELECT count(*) FROM t1 WHERE WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) ); ---echo # should return 0 +--error ER_FT_MATCHING_KEY_NOT_FOUND SELECT count(*) FROM t1 WHERE not exists( SELECT 1 FROM t2 IGNORE INDEX (b2), t3 diff --git a/mysql-test/suite/innodb_fts/t/fulltext_left_join.test b/mysql-test/suite/innodb_fts/t/fulltext_left_join.test index 54cec263bfa..23bbd5ddc10 100644 --- a/mysql-test/suite/innodb_fts/t/fulltext_left_join.test +++ b/mysql-test/suite/innodb_fts/t/fulltext_left_join.test @@ -77,6 +77,7 @@ insert into t2 values (1, 'bword'), (3, 'aword'), (5, ''); -- disable_result_log ANALYZE TABLE t2; -- enable_result_log +--error ER_WRONG_ARGUMENTS select * from t1 left join t2 on m_id = id where match(d, e, f) against ('+aword +bword' in boolean mode); drop table t1,t2; @@ -107,6 +108,7 @@ ANALYZE TABLE t1; ANALYZE TABLE t2; -- enable_result_log +--error ER_WRONG_ARGUMENTS SELECT t1.*, MATCH(t1.name) AGAINST('string') AS relevance FROM t1 LEFT JOIN t2 ON t1.link = t2.id WHERE MATCH(t1.name, t2.name) AGAINST('string' IN BOOLEAN MODE); diff --git a/mysql-test/suite/innodb_fts/t/fulltext_misc.test b/mysql-test/suite/innodb_fts/t/fulltext_misc.test index 18fbd7112fd..424cfda2f95 100644 --- a/mysql-test/suite/innodb_fts/t/fulltext_misc.test +++ b/mysql-test/suite/innodb_fts/t/fulltext_misc.test @@ -139,7 +139,7 @@ CREATE TABLE t1(a TEXT CHARSET ucs2 COLLATE ucs2_unicode_ci) ENGINE = InnoDB; INSERT INTO t1 VALUES('abcd'); # INNODB_FTS: Please Note this table do not have FTS. InnoDB return 1214 error ---error ER_TABLE_HAS_NO_FT +--error ER_FT_MATCHING_KEY_NOT_FOUND SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abcd' IN BOOLEAN MODE); DROP TABLE t1; diff --git a/mysql-test/suite/innodb_fts/t/fulltext_order_by.test b/mysql-test/suite/innodb_fts/t/fulltext_order_by.test index 5ea5c89a49c..b8f64a2fe78 100644 --- a/mysql-test/suite/innodb_fts/t/fulltext_order_by.test +++ b/mysql-test/suite/innodb_fts/t/fulltext_order_by.test @@ -152,7 +152,7 @@ order by (select b.id, b.betreff from t3 b) order by match(betreff) against ('+abc' in boolean mode) desc; ---error 1191 +--error ER_FT_MATCHING_KEY_NOT_FOUND (select b.id, b.betreff from t3 b) union (select b.id, b.betreff from t3 b) order by match(betreff) against ('+abc') desc; diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test b/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test index 934d52f764f..68ca8974512 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_misc.test @@ -9,11 +9,6 @@ let collation=UTF8_UNICODE_CI; drop table if exists t1; --enable_warnings -if (`select plugin_auth_version <= "5.6.10" from information_schema.plugins where plugin_name='innodb'`) -{ - --skip Not fixed in InnoDB 5.6.10 or earlier -} - # Create FTS table CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, @@ -643,6 +638,7 @@ CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF32, char_col INSERT INTO t1 (char_column) VALUES ('abcde'),('fghij'),('klmno'),('qrstu'); UPDATE t1 SET char_column2 = char_column; CREATE FULLTEXT INDEX i ON t1 (char_column2); +--error ER_FT_MATCHING_KEY_NOT_FOUND SELECT * FROM t1 WHERE MATCH(char_column) AGAINST ('abc*' IN BOOLEAN MODE); DROP TABLE t1; diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_plugin.test b/mysql-test/suite/innodb_fts/t/innodb_fts_plugin.test new file mode 100644 index 00000000000..e800faed0f5 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_plugin.test @@ -0,0 +1,45 @@ +--source include/have_simple_parser.inc +--source include/have_innodb.inc + +# Install fts parser plugin +INSTALL PLUGIN simple_parser SONAME 'mypluglib'; + +# Create a myisam table and alter it to innodb table +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT (title) WITH PARSER simple_parser + ) ENGINE=MyISAM; +--error ER_INNODB_NO_FT_USES_PARSER +ALTER TABLE articles ENGINE=InnoDB; + +DROP TABLE articles; + +# Create a table having a full text index with parser +--error ER_INNODB_NO_FT_USES_PARSER +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT (title) WITH PARSER simple_parser + ) ENGINE=InnoDB; + +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT (title) + ) ENGINE=InnoDB; + +# Alter table to add a full text index with parser +--error ER_INNODB_NO_FT_USES_PARSER +ALTER TABLE articles ADD FULLTEXT INDEX (body) WITH PARSER simple_parser; + +# Create a full text index with parser +--error ER_INNODB_NO_FT_USES_PARSER +CREATE FULLTEXT INDEX ft_index ON articles(body) WITH PARSER simple_parser; + +DROP TABLE articles; +# Uninstall plugin +UNINSTALL PLUGIN simple_parser; diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_result_cache_limit.test b/mysql-test/suite/innodb_fts/t/innodb_fts_result_cache_limit.test new file mode 100644 index 00000000000..dc55712b47c --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_result_cache_limit.test @@ -0,0 +1,51 @@ +# This is a basic test for innodb fts result cache limit. + +-- source include/have_innodb.inc + +# Must have debug code to use SET SESSION debug +--source include/have_debug.inc + +# Create FTS table +CREATE TABLE t1 ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + a VARCHAR(200), + b TEXT + ) ENGINE= InnoDB; + +# Create the FTS index again +CREATE FULLTEXT INDEX idx on t1 (a,b); + +# Insert rows +INSERT INTO t1 (a,b) VALUES + ('MySQL from Tutorial','DBMS stands for DataBase ...') , + ('when To Use MySQL Well','After that you went through a ...'), + ('where will Optimizing MySQL','what In this tutorial we will show ...'), + ('MySQL from Tutorial','DBMS stands for DataBase ...') , + ('when To Use MySQL Well','After that you went through a ...'), + ('where will Optimizing MySQL','what In this tutorial we will show ...'), + ('MySQL from Tutorial','DBMS stands for DataBase ...') , + ('when To Use MySQL Well','After that you went through a ...'), + ('where will Optimizing MySQL','what In this tutorial we will show ...'); + +SET SESSION debug_dbug="+d,fts_instrument_result_cache_limit"; + +# Simple term search +SELECT COUNT(*) FROM t1 WHERE MATCH (a,b) AGAINST ('mysql' IN BOOLEAN MODE); + +# Query expansion +--error 128 +SELECT COUNT(*) FROM t1 WHERE MATCH (a,b) AGAINST ('mysql' WITH QUERY EXPANSION); + +# Simple phrase search +--error 128 +SELECT COUNT(*) FROM t1 WHERE MATCH (a,b) AGAINST ('"mysql database"' IN BOOLEAN MODE); + +# Simple proximity search +--error 128 +SELECT COUNT(*) FROM t1 WHERE MATCH (a,b) AGAINST ('"mysql database" @ 5' IN BOOLEAN MODE); + +SET SESSION debug_dbug="-d,fts_instrument_result_cache_limit"; + +DROP TABLE t1; + +SET GLOBAL innodb_ft_result_cache_limit=default; diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_stopword_charset.test b/mysql-test/suite/innodb_fts/t/innodb_fts_stopword_charset.test new file mode 100644 index 00000000000..cb49ca0e39f --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_stopword_charset.test @@ -0,0 +1,421 @@ +# This is the basic function tests for innodb FTS stopword charset + +-- source include/have_innodb.inc + +# Valgrind would complain about memory leaks when we crash on purpose. +--source include/not_valgrind.inc +# Embedded server does not support crashing +--source include/not_embedded.inc +# Avoid CrashReporter popup on Mac +--source include/not_crashrep.inc + +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; + +SET NAMES utf8; + +-- echo # Test 1 : default latin1_swedish_ci +# Create FTS table with default charset latin1_swedish_ci +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200) + ) ENGINE=InnoDB; + +--disable_warnings +INSERT INTO articles (title) VALUES + ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), + ('lṓve'),('LṒVE'); + +# Build full text index with default stopword +CREATE FULLTEXT INDEX ft_idx ON articles(title); +--enable_warnings + +# We can find 'lòve' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('lòve' IN NATURAL LANGUAGE MODE); + +# Define a user stopword table and set to it +CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB; +INSERT INTO user_stopword VALUES('lòve'); +SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword'; + +# Rebuild the full text index with user stopword +DROP INDEX ft_idx ON articles; +CREATE FULLTEXT INDEX ft_idx ON articles(title); + +# Now we will not find 'lòve' and check result with 'love' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('lòve' IN NATURAL LANGUAGE MODE); + +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('love' IN NATURAL LANGUAGE MODE); + +DROP TABLE articles; +DROP TABLE user_stopword; + +-- echo # Test 2 : latin1_general_ci +# Create FTS table with default charset latin1_swedish_ci +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200) + ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci; + +--disable_warnings +INSERT INTO articles (title) VALUES + ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), + ('lṓve'),('LṒVE'); + +# Build full text index with default stopword +CREATE FULLTEXT INDEX ft_idx ON articles(title); +--enable_warnings + +# We can find 'lòve' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('lòve' IN NATURAL LANGUAGE MODE); + +# Define a user stopword table and set to it +CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB + DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci; +INSERT INTO user_stopword VALUES('lòve'); +SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword'; + +# Rebuild the full text index with user stopword +DROP INDEX ft_idx ON articles; +CREATE FULLTEXT INDEX ft_idx ON articles(title); + +# Now we will not find 'lòve' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('lòve' IN NATURAL LANGUAGE MODE); + +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('love' IN NATURAL LANGUAGE MODE); + +DROP TABLE articles; +DROP TABLE user_stopword; + +-- echo # Test 3 : latin1_spanish_ci +# Create FTS table with default charset latin1_swedish_ci +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200) + ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_spanish_ci; + +--disable_warnings +INSERT INTO articles (title) VALUES + ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), + ('lṓve'),('LṒVE'); + +# Build full text index with default stopword +CREATE FULLTEXT INDEX ft_idx ON articles(title); +--enable_warnings + +# We can find 'lòve' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('lòve' IN NATURAL LANGUAGE MODE); + +# Define a user stopword table and set to it +CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB + DEFAULT CHARACTER SET latin1 COLLATE latin1_spanish_ci; +INSERT INTO user_stopword VALUES('lòve'); +SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword'; + +# Rebuild the full text index with user stopword +DROP INDEX ft_idx ON articles; +CREATE FULLTEXT INDEX ft_idx ON articles(title); + +# Now we will not find 'lòve' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('lòve' IN NATURAL LANGUAGE MODE); + +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('love' IN NATURAL LANGUAGE MODE); + +DROP TABLE articles; +DROP TABLE user_stopword; + +-- echo # Test 4 : utf8_general_ci +# Create FTS table with default charset utf8_general_ci +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200) + ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; + +--disable_warnings +INSERT INTO articles (title) VALUES + ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), + ('lṓve'),('LṒVE'); + +# Build full text index with default stopword +CREATE FULLTEXT INDEX ft_idx ON articles(title); +--enable_warnings + +# We can find 'lòve' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('lòve' IN NATURAL LANGUAGE MODE); + +# Define a user stopword table and set to it +CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB + DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; +INSERT INTO user_stopword VALUES('lòve'); +SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword'; + +# Rebuild the full text index with user stopword +DROP INDEX ft_idx ON articles; +CREATE FULLTEXT INDEX ft_idx ON articles(title); + +# Now we will not find 'lòve' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('lòve' IN NATURAL LANGUAGE MODE); + +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('love' IN NATURAL LANGUAGE MODE); + +DROP TABLE articles; +DROP TABLE user_stopword; + +-- echo # Test 5 : utf8_unicode_ci +# Create FTS table with default charset utf8_swedish_ci +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200) + ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_swedish_ci; + +--disable_warnings +INSERT INTO articles (title) VALUES + ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), + ('lṓve'),('LṒVE'); + +# Build full text index with default stopword +CREATE FULLTEXT INDEX ft_idx ON articles(title); +--enable_warnings + +# We can find 'lòve' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('lòve' IN NATURAL LANGUAGE MODE); + +# Define a user stopword table and set to it +CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB + DEFAULT CHARACTER SET utf8 COLLATE utf8_swedish_ci; +INSERT INTO user_stopword VALUES('lòve'); +SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword'; + +# Rebuild the full text index with user stopword +DROP INDEX ft_idx ON articles; +CREATE FULLTEXT INDEX ft_idx ON articles(title); + +# Now we will not find 'lòve' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('lòve' IN NATURAL LANGUAGE MODE); + +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('love' IN NATURAL LANGUAGE MODE); + +DROP TABLE articles; +DROP TABLE user_stopword; + +-- echo # Test 6 : utf8_unicode_ci +# Create FTS table with default charset utf8_unicode_ci +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200) + ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; + +--disable_warnings +INSERT INTO articles (title) VALUES + ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), + ('lṓve'),('LṒVE'); + +# Build full text index with default stopword +CREATE FULLTEXT INDEX ft_idx ON articles(title); +--enable_warnings + +# We can find 'lòve' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('lòve' IN NATURAL LANGUAGE MODE); + +# Define a user stopword table and set to it +CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB + DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; +INSERT INTO user_stopword VALUES('lòve'); +SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword'; + +# Rebuild the full text index with user stopword +DROP INDEX ft_idx ON articles; +CREATE FULLTEXT INDEX ft_idx ON articles(title); + +# Now we will not find 'lòve' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('lòve' IN NATURAL LANGUAGE MODE); + +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('love' IN NATURAL LANGUAGE MODE); + +DROP TABLE articles; +DROP TABLE user_stopword; + +-- echo # Test 7 : gb2312_chinese_ci +# Create FTS table with default charset gb2312_chinese_ci +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200) + ) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci; + +--disable_warnings +INSERT INTO articles (title) VALUES + ('相亲相爱'),('怜香惜爱'),('充满可爱'),('爱恨交织'); + +# Build full text index with default stopword +CREATE FULLTEXT INDEX ft_idx ON articles(title); +--enable_warnings + +# We can find '相亲相爱' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('相亲相爱' IN NATURAL LANGUAGE MODE); + +# Define a user stopword table and set to it +CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB + DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci; +INSERT INTO user_stopword VALUES('相亲相爱'); +SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword'; + +# Rebuild the full text index with user stopword +DROP INDEX ft_idx ON articles; +CREATE FULLTEXT INDEX ft_idx ON articles(title); + +# Now we will not find '相亲相爱' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('相亲相爱' IN NATURAL LANGUAGE MODE); + +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('怜香惜爱' IN NATURAL LANGUAGE MODE); + +DROP TABLE articles; +DROP TABLE user_stopword; + +-- echo # Test 8 : test shutdown to check if stopword still works +# Create FTS table with default charset latin1_swedish_ci +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200) + ) ENGINE=InnoDB; + +--disable_warnings +INSERT INTO articles (title) VALUES + ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), + ('lṓve'),('LṒVE'); + +# Build full text index with default stopword +CREATE FULLTEXT INDEX ft_idx ON articles(title); +--enable_warnings + +# We can find 'lòve' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('lòve' IN NATURAL LANGUAGE MODE); + +# Define a user stopword table and set to it +CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB; +INSERT INTO user_stopword VALUES('lòve'); +SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword'; + +# Rebuild the full text index with user stopword +DROP INDEX ft_idx ON articles; +CREATE FULLTEXT INDEX ft_idx ON articles(title); + +# Now we will not find 'lòve' and check result with 'love' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('lòve' IN NATURAL LANGUAGE MODE); + +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('love' IN NATURAL LANGUAGE MODE); + +--echo # Shutdown and restart mysqld +--source include/restart_mysqld.inc + +SET NAMES utf8; + +# Insert rows to check if it uses user stopword +--disable_warnings +INSERT INTO articles (title) VALUES + ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), + ('lṓve'),('LṒVE'); +--enable_warnings + +# Now we will not find 'lòve' and check result with 'love' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('lòve' IN NATURAL LANGUAGE MODE); + +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('love' IN NATURAL LANGUAGE MODE); + +DROP TABLE articles; +DROP TABLE user_stopword; + +-- echo # Test 9 : drop user stopwrod table,test shutdown to check if it works +# Create FTS table with default charset latin1_swedish_ci +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200) + ) ENGINE=InnoDB; + +--disable_warnings +INSERT INTO articles (title) VALUES + ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), + ('lṓve'),('LṒVE'); + +# Build full text index with default stopword +CREATE FULLTEXT INDEX ft_idx ON articles(title); +--enable_warnings + +# We can find 'lòve' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('lòve' IN NATURAL LANGUAGE MODE); + +# Define a user stopword table and set to it +CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB; +INSERT INTO user_stopword VALUES('lòve'); +SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword'; + +# Rebuild the full text index with user stopword +DROP INDEX ft_idx ON articles; +CREATE FULLTEXT INDEX ft_idx ON articles(title); + +# Now we will not find 'lòve' and check result with 'love' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('lòve' IN NATURAL LANGUAGE MODE); + +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('love' IN NATURAL LANGUAGE MODE); + +# Drop user stopword table +DROP TABLE user_stopword; + +--echo # Shutdown and restart mysqld +--source include/restart_mysqld.inc + +SET NAMES utf8; + +# Insert rows to check if it uses user stopword +--disable_warnings +INSERT INTO articles (title) VALUES + ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'), + ('lṓve'),('LṒVE'); +--enable_warnings + +# Now we will not find 'lòve' and check result with 'love' +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('lòve' IN NATURAL LANGUAGE MODE); + +SELECT * FROM articles WHERE MATCH (title) + AGAINST ('love' IN NATURAL LANGUAGE MODE); + +DROP TABLE articles; + +# 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; diff --git a/mysql-test/t/alter_table_autoinc-5574.test b/mysql-test/t/alter_table_autoinc-5574.test new file mode 100644 index 00000000000..95c2b8d81bb --- /dev/null +++ b/mysql-test/t/alter_table_autoinc-5574.test @@ -0,0 +1,12 @@ +# +# MDEV-5574 Set AUTO_INCREMENT below max value of column +# +--source include/have_innodb.inc +create table t1(a int(10)unsigned not null auto_increment primary key, +b varchar(255) not null) engine=innodb default charset=utf8; +insert into t1 values(1,'aaa'),(2,'bbb'); +alter table t1 auto_increment=1; +insert into t1 values(NULL, 'ccc'); +select * from t1; +drop table t1; + |