diff options
Diffstat (limited to 'mysql-test/suite/innodb_fts/t')
| -rw-r--r-- | mysql-test/suite/innodb_fts/t/crash_recovery.test | 2 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_fts/t/foreign_key_check.test | 41 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_fts/t/foreign_key_update.test | 34 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_fts/t/index_table.opt | 2 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_fts/t/index_table.test | 121 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_fts/t/innodb_fts_proximity.test | 2 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_fts/t/limit_union.test | 143 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_fts/t/misc.test | 1511 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_fts/t/misc_1.test | 894 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_fts/t/opt.opt | 1 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_fts/t/opt.test | 1086 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_fts/t/phrase.opt | 2 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_fts/t/phrase.test | 39 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_fts/t/result_cache_limit.test | 52 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_fts/t/savepoint.test | 475 | ||||
| -rw-r--r-- | mysql-test/suite/innodb_fts/t/subexpr.test | 58 |
16 files changed, 4462 insertions, 1 deletions
diff --git a/mysql-test/suite/innodb_fts/t/crash_recovery.test b/mysql-test/suite/innodb_fts/t/crash_recovery.test index dd8a07f8..ac3f0385 100644 --- a/mysql-test/suite/innodb_fts/t/crash_recovery.test +++ b/mysql-test/suite/innodb_fts/t/crash_recovery.test @@ -113,7 +113,7 @@ disconnect ddl3; # Wait for purge, so that any #sql-ib.ibd files from the previous kill # will be deleted. -source ../../innodb/include/wait_all_purged.inc; +SET GLOBAL innodb_max_purge_lag_wait=0; CHECK TABLE t1,t2,t3; DROP TABLE t1,t2,t3; diff --git a/mysql-test/suite/innodb_fts/t/foreign_key_check.test b/mysql-test/suite/innodb_fts/t/foreign_key_check.test new file mode 100644 index 00000000..24421dcb --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/foreign_key_check.test @@ -0,0 +1,41 @@ +# +# BUG#22094601 CREATE TABLE WITH FULLTEXT AND CONSTRAINT FAILS WHEN FOREIGN_KEY_CHECKS IS 0 +# + +--source include/have_innodb.inc + +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 ( + id INT NOT NULL, + title TEXT, + PRIMARY KEY (id), + FULLTEXT KEY (title), + FOREIGN KEY (id) REFERENCES t2 (id) +) ENGINE=InnoDB; + +CREATE TABLE t1 ( + id INT NOT NULL, + title TEXT, + PRIMARY KEY (id) +) ENGINE=InnoDB; + +--error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ADD FULLTEXT KEY (title), ADD FOREIGN KEY (id) REFERENCES t2 (id); + +SET FOREIGN_KEY_CHECKS = 0; + +ALTER TABLE t1 ADD FULLTEXT KEY (title), ADD FOREIGN KEY (id) REFERENCES t2 (id); + +DROP TABLE t1; + +CREATE TABLE t1 ( + id INT NOT NULL, + title TEXT, + PRIMARY KEY (id), + FULLTEXT KEY (title), + FOREIGN KEY (id) REFERENCES t2 (id) +) ENGINE=InnoDB; + +DROP TABLE t1; + +SET FOREIGN_KEY_CHECKS = 1; diff --git a/mysql-test/suite/innodb_fts/t/foreign_key_update.test b/mysql-test/suite/innodb_fts/t/foreign_key_update.test new file mode 100644 index 00000000..1f74e640 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/foreign_key_update.test @@ -0,0 +1,34 @@ +# +# BUG#21111301 INNODB FTS: ERROR DUPLICATE ENTRY IN FTS_DOC_ID_INDEX ON UPDATE CASCADE +# + +-- source include/have_innodb.inc + +CREATE TABLE t1 ( + a varchar(40), + KEY a(a) +) ENGINE=InnoDB; + +CREATE TABLE t1_fk ( + a varchar(40), + KEY a(a), + FULLTEXT KEY (a), + CONSTRAINT fk FOREIGN KEY (a) REFERENCES t1 (a) ON UPDATE CASCADE +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES('mysql'); + +INSERT INTO t1_fk VALUES('mysql'); +INSERT INTO t1_fk VALUES('mysql'); + +SELECT * FROM t1_fk; +SELECT * FROM t1_fk WHERE MATCH(a) AGAINST('mysql'); + +UPDATE t1 SET a = 'database' WHERE a = 'mysql'; + +SELECT * FROM t1_fk; +SELECT * FROM t1_fk WHERE MATCH(a) AGAINST('mysql'); +SELECT * FROM t1_fk WHERE MATCH(a) AGAINST('database'); + +DROP TABLE t1_fk; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb_fts/t/index_table.opt b/mysql-test/suite/innodb_fts/t/index_table.opt new file mode 100644 index 00000000..7724f976 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/index_table.opt @@ -0,0 +1,2 @@ +--innodb-ft-index-cache +--innodb-ft-index-table diff --git a/mysql-test/suite/innodb_fts/t/index_table.test b/mysql-test/suite/innodb_fts/t/index_table.test new file mode 100644 index 00000000..4b484877 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/index_table.test @@ -0,0 +1,121 @@ +# This is the basic function tests for INNODB_FT_INDEX_TABLE +# and INNODB_FT_INDEX_TABLE in INFORMATION_SCHEMA. + +-- source include/have_innodb.inc +-- source include/have_debug.inc + +SET @optimize=@@GLOBAL.INNODB_OPTIMIZE_FULLTEXT_ONLY; +SET GLOBAL INNODB_OPTIMIZE_FULLTEXT_ONLY=1; + +# Test Case 1: Test Result Cache Limit +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + content TEXT + ) ENGINE= InnoDB; + +CREATE FULLTEXT INDEX idx ON articles (title, content); + +INSERT INTO articles (title, content) VALUES + ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'), + ('How To Use MySQL Well','After you went through a ...'), + ('Optimizing MySQL','In this tutorial we will show ...'), + ('1001 MySQL Tricks','How to use full-text search engine'), + ('Go MySQL Tricks','How to use full text search engine'); + +SET @aux=@@GLOBAL.innodb_ft_aux_table; +SET GLOBAL innodb_ft_aux_table='test/articles'; + +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; + +OPTIMIZE TABLE articles; + +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; + +SET @save_dbug=@@debug_dbug; +SET debug_dbug='+d,fts_instrument_result_cache_limit'; + +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; + +SET debug_dbug=@save_dbug; + +DROP TABLE articles; + +SET GLOBAL innodb_ft_result_cache_limit=default; + +# Test Case 2: Test Multiple Indexes +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + content TEXT + ) ENGINE= InnoDB; + +CREATE FULLTEXT INDEX idx_t ON articles (title); + +CREATE FULLTEXT INDEX idx_c ON articles (content); + +INSERT INTO articles (title, content) VALUES + ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'), + ('How To Use MySQL Well','After you went through a ...'), + ('Optimizing MySQL','In this tutorial we will show ...'), + ('1001 MySQL Tricks','How to use full-text search engine'), + ('Go MySQL Tricks','How to use full text search engine'); + +# test.articles had been dropped, so this result will be empty +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +SET GLOBAL innodb_ft_aux_table='test/articles'; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; + +OPTIMIZE TABLE articles; + +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; + +DROP TABLE articles; + +SET NAMES utf8; + +# Test Case 3: Test UFT8 Charset +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; + +CREATE FULLTEXT INDEX idx ON articles (title); + +INSERT INTO articles (title) VALUES + ('相亲相爱'),('怜香惜爱'),('充满可爱'),('爱恨交织'); + +SET GLOBAL innodb_ft_aux_table="test/articles"; + +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; + +OPTIMIZE TABLE articles; + +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; + +DROP TABLE articles; + +# Test Case 4: Test GB2312 Charset +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; + +CREATE FULLTEXT INDEX idx ON articles (title); + +INSERT INTO articles (title) VALUES + ('相亲相爱'),('怜香惜爱'),('充满可爱'),('爱恨交织'); + +SET GLOBAL innodb_ft_aux_table="test/articles"; + +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; + +OPTIMIZE TABLE articles; + +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; + +DROP TABLE articles; + +# Restore global variables +SET GLOBAL innodb_ft_aux_table=@aux; +SET GLOBAL INNODB_OPTIMIZE_FULLTEXT_ONLY=@optimize; diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_proximity.test b/mysql-test/suite/innodb_fts/t/innodb_fts_proximity.test index e3d8eb0c..edf7ab21 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_proximity.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_proximity.test @@ -163,6 +163,8 @@ DROP TABLE t1; set global innodb_file_per_table=1; +set names utf8; +--character_set utf8 # Test fts with externally stored long column CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, diff --git a/mysql-test/suite/innodb_fts/t/limit_union.test b/mysql-test/suite/innodb_fts/t/limit_union.test new file mode 100644 index 00000000..71c9af69 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/limit_union.test @@ -0,0 +1,143 @@ +--source include/have_debug.inc +--source include/have_innodb.inc + +--echo # Bug #22709692 FTS QUERY EXCEEDS RESULT CACHE LIMIT + +CREATE TABLE articles ( +id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, +title VARCHAR(200), +body TEXT, +FULLTEXT (title,body), +FULLTEXT (body))ENGINE=InnoDB; + +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 ...'); + +SET @default_cache_size = @@GLOBAL.query_cache_size; +SET GLOBAL query_cache_size=0; + +--echo # Query involves Ranking +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('MySQL' IN NATURAL LANGUAGE MODE) LIMIT 1; + +--echo # Without optimization +SET @save_dbug = @@debug_dbug; +SET debug_dbug = '+d,fts_union_limit_off'; +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('MySQL' IN NATURAL LANGUAGE MODE) LIMIT 1; +SET debug_dbug = @save_dbug; + +--echo # Query involves No Ranking and fts_union operations +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('MySQL' IN BOOLEAN MODE) limit 1; + +--echo # Without optimization +SET debug_dbug = '+d,fts_union_limit_off'; +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('MySQL' IN BOOLEAN MODE) limit 1; + +SET debug_dbug = @save_dbug; + +--echo # Query involves No ranking and fts_union, fts_ignore +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('MySQL -YourSQL' IN BOOLEAN MODE) limit 1; + +--echo # Without optimization +SET debug_dbug = '+d,fts_union_limit_off'; +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('MySQL -YourSQL' IN BOOLEAN MODE) limit 1; +SET debug_dbug = @save_dbug; + +--echo # Query with fts_intersect +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('MySQL +YourSQL' IN BOOLEAN MODE) limit 1; + +--echo # Without optimization +SET debug_dbug = '+d,fts_union_limit_off'; +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('MySQL +YourSQL' IN BOOLEAN MODE) limit 1; +SET debug_dbug = @save_dbug; + +# insert record with @ character which is used in proximity search +INSERT INTO articles (title,body) VALUES + ('MySQL Tutorial','request doc@oraclehelp.com ...'), + ('MySQL Tutorial','request support@oraclehelp.com ...'), + ('Trial version','query performace @1255 minute on 2.1Hz + Memory 2GB...'), + ('when To Use MySQL Well','for free faq mail@xyz.com ...'); + +--echo # Query with @distance +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('"MySQL request"@3' IN BOOLEAN MODE) limit 1; + +--echo # Without optimization +SET debug_dbug = '+d,fts_union_limit_off'; +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('"MySQL request"@3' IN BOOLEAN MODE) limit 1; +SET debug_dbug = @save_dbug; + +--echo # Query with subexpression +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('+MySQL +(-support +doc)' IN BOOLEAN MODE) limit 1; + +--echo # Without optimization +SET debug_dbug = '+d,fts_union_limit_off'; +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('+MySQL +(-support +doc)' IN BOOLEAN MODE) limit 1; +SET debug_dbug = @save_dbug; + +--echo # limit num1 OFFSET num2 +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('MySQL' in boolean mode) limit 4 offset 2; + +--echo # Without optimization +SET debug_dbug = '+d,fts_union_limit_off'; +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('MySQL' in boolean mode) limit 4 offset 2; +SET debug_dbug = @save_dbug; + +--echo # wild card search +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('ru*' IN BOOLEAN MODE) limit 1; + +--echo # Without optimization +SET debug_dbug = '+d,fts_union_limit_off'; +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('ru*' IN BOOLEAN MODE) limit 1; +SET debug_dbug = @save_dbug; + +--echo # phrase search +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('"request support"' IN BOOLEAN MODE) limit 1; + +--echo # Without optimization +SET debug_dbug = '+d,fts_union_limit_off'; +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('"request support"' IN BOOLEAN MODE) limit 1; +SET debug_dbug = @save_dbug; + +DROP TABLE articles; +SET GLOBAL query_cache_size = @default_cache_size; diff --git a/mysql-test/suite/innodb_fts/t/misc.test b/mysql-test/suite/innodb_fts/t/misc.test new file mode 100644 index 00000000..f897cf12 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/misc.test @@ -0,0 +1,1511 @@ +--source include/have_innodb.inc +--source include/no_valgrind_without_big.inc + +#------------------------------------------------------------------------------ +# Test With alter/create/drop index +#------------------------------------------------------------------------------ + +# Create FTS table +CREATE TABLE t1 ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + a VARCHAR(200), + b TEXT + ) ENGINE = InnoDB; + +# Insert rows +INSERT INTO t1 (a,b) 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 ...'); + +# Create the FTS index Using Alter Table +ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b); +EVAL SHOW CREATE TABLE t1; + +# Insert rows +INSERT INTO t1 (a,b) VALUES + ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), + ('MySQL vs. YourSQL','In the following database comparison ...'), + ('MySQL Security','When configured properly, MySQL ...'); + +# Select word "tutorial" in the table +SELECT id FROM t1 WHERE MATCH (a,b) + AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); + +# boolean mode +select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE); +select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE); +select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE); +select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE); +select id, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1; +select id, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1; +select id from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE); +select id from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE); +select id from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE); + +# query expansion +select id from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION); +select id from t1 where MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION); +select id from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY EXPANSION); + +# Drop index +ALTER TABLE t1 DROP INDEX idx; + +# Create the FTS index again +CREATE FULLTEXT INDEX idx on t1 (a,b); + +# Select word "tutorial" in the table +SELECT id FROM t1 WHERE MATCH (a,b) + AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); + +# boolean mode +select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE); +select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE); +select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE); +select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE); +select id, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1; +select id, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1; +select id from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE); +select id from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE); +select id from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE); + +# query expansion +select id from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION); +select id from t1 where MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION); +select id from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY EXPANSION); + +# insert for proximity search +INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...'); +# Insert into table with similar word of different distances +INSERT INTO t1 (a,b) VALUES + ('test proximity search, test, proximity and phrase', + 'search, with proximity innodb'); + +INSERT INTO t1 (a,b) VALUES + ('test proximity fts search, test, proximity and phrase', + 'search, with proximity innodb'); + +INSERT INTO t1 (a,b) VALUES + ('test more proximity fts search, test, more proximity and phrase', + 'search, with proximity innodb'); + +# This should only return the first document +SELECT id FROM t1 + WHERE MATCH (a,b) + AGAINST ('"proximity search"@2' IN BOOLEAN MODE); + +# This would return no document +SELECT id FROM t1 + WHERE MATCH (a,b) + AGAINST ('"proximity search"@1' IN BOOLEAN MODE); + +# This give you all three documents +SELECT id FROM t1 + WHERE MATCH (a,b) + AGAINST ('"proximity search"@3' IN BOOLEAN MODE); + +# Similar boundary testing for the words +SELECT id FROM t1 + WHERE MATCH (a,b) + AGAINST ('"test proximity"@3' IN BOOLEAN MODE); + +# Test with more word The last document will return, please notice there +# is no ordering requirement for proximity search. +SELECT id FROM t1 + WHERE MATCH (a,b) + AGAINST ('"more test proximity"@3' IN BOOLEAN MODE); + +SELECT id FROM t1 + WHERE MATCH (a,b) + AGAINST ('"more test proximity"@2' IN BOOLEAN MODE); + +# The phrase search will not require exact word ordering +SELECT id FROM t1 + WHERE MATCH (a,b) + AGAINST ('"more fts proximity"@02' IN BOOLEAN MODE); + +DROP TABLE t1; + + + +#------------------------------------------------------------------------------ +# Test with FTS condition in subquery +#------------------------------------------------------------------------------ + +# 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 Tutorial','DBMS stands for DataBase ...') , + ('How To Use MySQL Well','After you went through a ...'), + ('Optimizing MySQL','In this tutorial we will show ...'); + +# Insert rows +INSERT INTO t1 (a,b) VALUES + ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), + ('MySQL vs. YourSQL','In the following database comparison ...'), + ('MySQL Security','When configured properly, MySQL ...'); + + +# Select word "tutorial" in the table +SELECT id FROM t1 WHERE MATCH (a,b) +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); + +# Select word "tutorial" in the table +SELECT id FROM t1 WHERE id = (SELECT MAX(id) FROM t1 WHERE MATCH (a,b) +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)); + +# Select word "tutorial" in the table +SELECT id FROM t1 WHERE id = (SELECT MIN(id) FROM t1 WHERE MATCH (a,b) +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)); + +# Select word "tutorial" in the table +SELECT id FROM t1 WHERE id = (SELECT MIN(id) FROM t1 WHERE MATCH (a,b) +AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)) OR id = 3 ; + + +# Select word "tutorial" in the table - innodb crash +SELECT id FROM t1 WHERE CONCAT(t1.a,t1.b) IN ( +SELECT CONCAT(a,b) FROM t1 AS t2 WHERE +MATCH (t2.a,t2.b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) +) OR t1.id = 3 ; + + +# Select word "tutorial" in the table - innodb crash +SELECT id FROM t1 WHERE CONCAT(t1.a,t1.b) IN ( +SELECT CONCAT(a,b) FROM t1 AS t2 +WHERE MATCH (t2.a,t2.b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) +AND t2.id != 3) ; + +# Select word "tutorial" in the table +SELECT id FROM t1 WHERE id IN (SELECT MIN(id) FROM t1 WHERE +MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)) OR id = 3 ; + +# Select word except "tutorial" in the table +SELECT id FROM t1 WHERE id NOT IN (SELECT MIN(id) FROM t1 +WHERE MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)) ; + + +# Select word "tutorial" in the table +SELECT id FROM t1 WHERE EXISTS (SELECT t2.id FROM t1 AS t2 WHERE +MATCH (t2.a,t2.b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) +AND t1.id = t2.id) ; + + +# Select not word like "tutorial" using subquery +SELECT id FROM t1 WHERE NOT EXISTS (SELECT t2.id FROM t1 AS t2 WHERE +MATCH (t2.a,t2.b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) +AND t1.id = t2.id) ; + +DROP TABLE t1; + +# boolean search +CREATE TABLE t1 ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + a VARCHAR(200), + b TEXT , + FULLTEXT (a,b) + ) ENGINE = InnoDB; + +INSERT INTO t1(a,b) VALUES('MySQL has now support', 'for full-text search'), +('Full-text indexes', 'are called collections'), +('Only MyISAM tables','support collections'), +('Function MATCH ... AGAINST()','is used to do a search'), +('Full-text search in MySQL', 'implements vector space model'); + +# Select word "tutorial" in the table +SELECT id FROM t1 WHERE t1.id = (SELECT MAX(t2.id) FROM t1 AS t2 WHERE + MATCH(t2.a,t2.b) AGAINST("+support +collections" IN BOOLEAN MODE)); +SELECT id FROM t1 WHERE t1.id != (SELECT MIN(t2.id) FROM t1 AS t2 WHERE + MATCH(t2.a,t2.b) AGAINST("+search" IN BOOLEAN MODE)); + +SELECT id FROM t1 WHERE t1.id IN (SELECT t2.id FROM t1 AS t2 WHERE +MATCH (t2.a,t2.b) AGAINST ("+call* +coll*" IN BOOLEAN MODE)); + +SELECT id FROM t1 WHERE EXISTS (SELECT id FROM t1 AS t2 WHERE +MATCH t2.a,t2.b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE) AND t2.id=t1.id); + + +#query expansion search +# result differ for query expansion search even wo subquery +#SELECT id FROM t1 WHERE t1.id = ( SELECT MAX(t2.id) FROM t1 AS t2 WHERE +#MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION)); +#SELECT id FROM t1 WHERE t1.id IN ( SELECT t2.id FROM t1 AS t2 WHERE +#MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION)); +#SELECT id FROM t1 WHERE ( SELECT COUNT(*) FROM t1 AS t2 WHERE +#MATCH(t2.a,t2.b) AGAINST ("indexes collections" WITH QUERY EXPANSION)) >= 1 +#AND t1.id <=3 ; + +# proximity search +# insert for proximity search +INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...'); +# Insert into table with similar word of different distances +INSERT INTO t1 (a,b) VALUES + ('test proximity search, test, proximity and phrase', + 'search, with proximity innodb'); + +INSERT INTO t1 (a,b) VALUES + ('test proximity fts search, test, proximity and phrase', + 'search, with proximity innodb'); + +INSERT INTO t1 (a,b) VALUES + ('test more proximity fts search, test, more proximity and phrase', + 'search, with proximity innodb'); + + +SELECT id FROM t1 WHERE t1.id = (SELECT MAX(t2.id) FROM t1 AS t2 WHERE +MATCH(t2.a,t2.b) AGAINST ('"proximity search"@2' IN BOOLEAN MODE)); +SELECT id FROM t1 WHERE t1.id > (SELECT MIN(t2.id) FROM t1 AS t2 WHERE +MATCH(t2.a,t2.b) AGAINST ('"proximity search"@2' IN BOOLEAN MODE)); + +SELECT id FROM t1 WHERE t1.id IN (SELECT t2.id FROM t1 AS t2 WHERE +MATCH (t2.a,t2.b) AGAINST ('"proximity search"@2' IN BOOLEAN MODE)); + +SELECT id FROM t1 WHERE EXISTS (SELECT id FROM t1 AS t2 WHERE +MATCH t2.a,t2.b AGAINST ('"proximity search"@2' IN BOOLEAN MODE) +AND t2.id=t1.id); + +SELECT id FROM t1 WHERE EXISTS (SELECT id FROM t1 AS t2 WHERE +MATCH t2.a,t2.b AGAINST ('"more test proximity"@3' IN BOOLEAN MODE) +AND t2.id=t1.id); + +SELECT id FROM t1 WHERE EXISTS (SELECT id FROM t1 AS t2 WHERE +MATCH t2.a,t2.b AGAINST ('"more test proximity"@2' IN BOOLEAN MODE) +AND t2.id=t1.id); + + +#------------------------------------------------------------------------------ +# create table AS SELECT from fts indexed table +#------------------------------------------------------------------------------ +CREATE TABLE t2 ENGINE = InnoDB AS SELECT id FROM t1 WHERE +MATCH a,b AGAINST ('support') ; +SHOW CREATE TABLE t2; +SELECT id FROM t2; +DROP TABLE t2; + +CREATE TABLE t2 ENGINE = InnoDB AS SELECT id FROM t1 WHERE +MATCH a,b AGAINST("+support +collections" IN BOOLEAN MODE); +SHOW CREATE TABLE t2; +SELECT id FROM t2; +DROP TABLE t2; + +CREATE TABLE t2 ENGINE = InnoDB AS SELECT id FROM t1 WHERE +MATCH a,b AGAINST ('"proximity search"@10' IN BOOLEAN MODE); +SHOW CREATE TABLE t2; +SELECT id FROM t2; +DROP TABLE t2; + +DROP TABLE t1; + + +#------------------------------------------------------------------------------ +# Verift FTS with NULL records +#------------------------------------------------------------------------------ +# Create FTS table +EVAL 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 ...'); + +let $counter = 50; +--disable_query_log +WHILE ($counter > 0) { + INSERT INTO t1 (a,b) VALUES (NULL,NULL); + dec $counter; +} +--enable_query_log +INSERT INTO t1 (a,b) VALUES +('when To Use MySQL Well','After that you went through a ...'); + +let $counter = 50; +--disable_query_log +WHILE ($counter > 0) { + INSERT INTO t1 (a,b) VALUES (NULL,NULL); + dec $counter; +} +--enable_query_log +INSERT INTO t1 (a,b) VALUES +('where will Optimizing MySQL','what In this tutorial we will show ...'); + +INSERT INTO t1 (a,b) VALUES +('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), +('MySQL vs. YourSQL','In the following database comparison ...'), +('MySQL Security','When configured properly, MySQL null...'); + +SELECT COUNT(*) FROM t1; +SELECT COUNT(*) FROM t1 WHERE a IS NULL; +SELECT COUNT(*) FROM t1 WHERE b IS NOT NULL; + +SELECT id FROM t1 + WHERE MATCH (a,b) + AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); + +SELECT id FROM t1 + WHERE MATCH (a,b) + AGAINST (NULL IN NATURAL LANGUAGE MODE); +SELECT id FROM t1 + WHERE MATCH (a,b) + AGAINST (NULL WITH QUERY EXPANSION); +SELECT id FROM t1 + WHERE MATCH (a,b) + AGAINST ('null' IN NATURAL LANGUAGE MODE); +# Boolean search +# Select rows contain "MySQL" but not "YourSQL" +SELECT id FROM t1 WHERE MATCH (a,b) +AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE); +SELECT id FROM t1 WHERE MATCH (a,b) +AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE) AND (a IS NOT NULL OR b IS NOT NULL); +SELECT id FROM t1 WHERE MATCH (a,b) +AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE) AND (a IS NULL AND b IS NOT NULL); + +# Select rows contain at least one of the two words +SELECT id FROM t1 WHERE MATCH (a,b) +AGAINST ('DBMS Security' IN BOOLEAN MODE); + +# Test query expansion +SELECT COUNT(*) FROM t1 +WHERE MATCH (a,b) +AGAINST ('database' WITH QUERY EXPANSION); + +# proximity +SELECT id FROM t1 +WHERE MATCH (a,b) +AGAINST ('"following database"@10' IN BOOLEAN MODE); + + +DROP TABLE t1; + + + +#------------------------------------------------------------------------------ +# More FTS test from peter's testing +#------------------------------------------------------------------------------ +--disable_warnings +drop table if exists t50; +--enable_warnings + +set names utf8; + + +--echo "----------Test1---------" +# Create FTS table +create table t50 (s1 varchar(60) character set utf8 collate utf8_bin) engine = innodb; +create fulltext index i on t50 (s1); +# INNODB_FTS: Assert - fixed +# Assert : InnoDB: Failing assertion: rbt_validate(result_doc->tokens) +insert into t50 values ('ABCDE'),('FGHIJ'),('KLMNO'),('VÐƷWİ'); +# it was giving empty result set instead of one record +select * from t50 where match(s1) against ('VÐƷWİ'); +drop table t50; + + +--echo "----------Test2---------" +create table t50 (s1 int unsigned primary key auto_increment, s2 +varchar(60) character set utf8) engine = innodb; +create fulltext index i on t50 (s2); +insert into t50 (s2) values ('FGHIJ'),('KLMNO'),('VÐƷWİ'),('ABCDE'); +# INNODB_FTS: RESULT DIFF +# Order by does not sort result. +# Optimizer's Evgeny is investigate a similar issue. InnoDB FTS is used only +# for FT search, and should not be used as regular index for such order by query. +# Correct the result file when fixed. +select * from t50 order by s2; +drop table t50; + + +--echo "----------Test3---------" +create table t50 (id int unsigned primary key auto_increment, s2 +varchar(60) character set utf8) engine = innodb; +create fulltext index i on t50 (s2); +insert into t50 (s2) values ('FGHIJ'),('KLMNO'),('VÐƷWİ'),('ABCDE'); +set @@autocommit=0; +update t50 set s2 = lower(s2); +update t50 set s2 = upper(s2); +commit; +select * from t50 where match(s2) against ('VÐƷWİ FGHIJ KLMNO ABCDE' in boolean mode); +select * from t50; +drop table t50; +set @@autocommit=1; + +--echo "----------Test4---------" +create table t50 (id int unsigned primary key auto_increment, s2 +varchar(60) character set utf8) engine = innodb; +create fulltext index i on t50 (s2); +insert into t50 (s2) values ('FGHIJ'),('KLMNO'),('VÐƷWİ'),('ABCD*'); +select * from t50 where match(s2) against ('abcd*' in natural language +mode); +# INNODB_FTS: RESULT DIFF(Expected). InnoDB do not index "*", so +# word "ABCD" indexed, instead of "ABCD*" +select * from t50 where match(s2) against ('abcd*' in boolean mode); +drop table t50; + + +--echo "----------Test5---------" +create table t50 (s1 int, s2 varchar(200), fulltext key(s2)) engine = innodb; +set @@autocommit=0; +insert into t50 values (1,'Sunshine'),(2,'Lollipops'); +select * from t50 where match(s2) against('Rainbows'); +rollback; +select * from t50; +drop table t50; +set @@autocommit=1; + +--echo "----------Test6---------" +CREATE TABLE t1 ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + a VARCHAR(200), + b TEXT + ) ENGINE = InnoDB; + +# Insert rows +INSERT INTO t1 (a,b) VALUES + ('aab` MySQL Tutorial','DBMS stands for DataBase ...') , + ('aas How To Use MySQL Well','After you went through a ...'), + ('aac Optimizing MySQL','In this tutorial we will show ...'); +# Insert rows +INSERT INTO t1 (a,b) VALUES + ('aac 1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), + ('aab MySQL vs. YourSQL','In the following database comparison ...'), + ('aaa MySQL Security','When configured properly, MySQL ...'); +# Create the FTS index Using Alter Table +ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b); + +-- disable_query_log +-- disable_result_log +ANALYZE TABLE t1; +-- enable_result_log +-- enable_query_log + +SELECT * FROM t1 ORDER BY MATCH(a,b) AGAINST ('aac') DESC; +SELECT * FROM t1 ORDER BY MATCH(a,b) AGAINST ('aab') DESC; + +--echo "----------Test7---------" +select * from t1 where match(a,b) against ('aaa') +union select * from t1 where match(a,b) against ('aab') +union select * from t1 where match(a,b) against ('aac'); + +select * from t1 where match(a,b) against ('aaa') + or match(a,b) against ('aab') + or match(a,b) against ('aac'); + +DROP TABLE t1; + +--echo "----------Test8---------" +# Create FTS table +CREATE TABLE t1 ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + a VARCHAR(200), + b TEXT + ) ENGINE = InnoDB; + +# Insert rows +INSERT INTO t1 (a,b) VALUES + ('MySQL Tutorial','DBMS stands for DataBase ... abcd') , + ('How To Use MySQL Well','After you went through a q ...abdd'), + ('Optimizing MySQL','In this tutorial we will show ...abed'); + +# Create the FTS index Using Alter Table +ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b); +EVAL SHOW CREATE TABLE t1; + +# Insert rows +INSERT INTO t1 (a,b) VALUES + ('1001 MySQL Tricks','1. Never run mysqld as root. 2. q ...'), + ('MySQL vs. YourSQL use','In the following database comparison ...'), + ('MySQL Security','When run configured properly, MySQL ...'); + +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('run'); +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('use'); +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('went'); +# rows should be matched as 'q' is single char its not indexed +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('run') AND NOT MATCH(a,b) AGAINST ('q'); +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('use') AND NOT MATCH(a,b) AGAINST ('q'); +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('went') AND NOT MATCH(a,b) AGAINST ('q'); + +--echo "----------Test9---------" +CREATE TABLE t2 AS SELECT * FROM t1; +ALTER TABLE t2 ENGINE=MYISAM; +CREATE FULLTEXT INDEX i ON t2 (a,b); +SET @x = (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('run')); +SET @x = @x + (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('use')); +SET @x = @x + (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('went')); +SET @x = @x + (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('run')); +SET @x2 = (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('run')); +SET @x2 = @x2 + (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('use')); +SET @x2 = @x2 + (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('went')); +SET @x2 = @x2 + (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('run')); +# Innodb returns value for x which is correct +SELECT @x, @x2; + + +DROP TABLE t2; + +--echo "----------Test10---------" +CREATE TABLE t2 AS SELECT * FROM t1; +ALTER TABLE t2 ENGINE=MYISAM; +CREATE FULLTEXT INDEX i ON t2 (a,b); +SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('abc*' IN BOOLEAN MODE); +SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('abc*' IN BOOLEAN MODE); + +DROP TABLE t2; + + +--echo "----------Test11---------" +CREATE TABLE t2 AS SELECT * FROM t1; +ALTER TABLE t2 ENGINE = MYISAM; +CREATE FULLTEXT INDEX i ON t2 (a,b); +ALTER TABLE t2 ENGINE=InnoDB; +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('run'); +SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('abc*' IN BOOLEAN MODE); +DROP TABLE t2,t1; + + +--echo "----------Test13---------" +set names utf8; + +CREATE TABLE t1 (s1 INT, s2 VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8_SPANISH_CI) ENGINE = InnoDB; +CREATE FULLTEXT INDEX i ON t1 (s2); +INSERT INTO t1 VALUES (1,'aaCen'),(2,'aaCha'),(3,'aaCio'),(4,'aaçen'),(5,'aaçha'),(6,'aaçio'); +SELECT * FROM t1 WHERE MATCH(s2) AGAINST ('aach*' IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(s2) AGAINST ('aaC*' IN BOOLEAN MODE); +DROP TABLE t1; + +--echo "----------Test14---------" +CREATE TABLE t1(s1 INT , s2 VARCHAR(100) CHARACTER SET sjis) ENGINE = InnoDB; +CREATE FULLTEXT INDEX i ON t1 (s2); +INSERT INTO t1 VALUES (1,'ペペペ'),(2,'テテテ'),(3,'ルルル'),(4,'グググ'); +# Innodb Asset : file ha_innodb.cc line 4557 +#SELECT * FROM t1 WHERE MATCH(s2) AGAINST ('テテ*' IN BOOLEAN MODE); +DROP TABLE t1; + + +--echo "----------Test15---------" +CREATE TABLE t1 (s1 VARCHAR (60) CHARACTER SET UTF8 COLLATE UTF8_UNICODE_520_CI) ENGINE = MyISAM; +CREATE FULLTEXT INDEX i ON t1 (s1); +INSERT INTO t1 VALUES +('a'),('b'),('c'),('d'),('ŁŁŁŁ'),('LLLL'),(NULL),('ŁŁŁŁ ŁŁŁŁ'),('LLLLLLLL'); +SELECT * FROM t1 WHERE MATCH(s1) AGAINST ('LLLL' COLLATE UTF8_UNICODE_520_CI); +DROP TABLE if EXISTS t2; +CREATE TABLE t2 (s1 VARCHAR(60) CHARACTER SET UTF8 COLLATE UTF8_POLISH_CI) ENGINE = InnoDB; +CREATE FULLTEXT INDEX i ON t2 ( s1); +INSERT INTO t2 VALUES +('a'),('b'),('c'),('d'),('ŁŁŁŁ'),('LLLL'),(NULL),('ŁŁŁŁ ŁŁŁŁ'),('LLLLLLLL'); +SELECT * FROM t2 WHERE MATCH(s1) AGAINST ('LLLL' COLLATE UTF8_UNICODE_520_CI); +--disable_warnings +DROP TABLE t1,t2; +--enable_warnings + +--echo "----------Test16---------" +CREATE TABLE t1 (s1 INT, s2 VARCHAR(50) CHARACTER SET UTF8) ENGINE = InnoDB; +CREATE FULLTEXT INDEX i ON t1(s2); +INSERT INTO t1 VALUES (2, 'ğė Daśi p '); +SELECT * FROM t1 WHERE MATCH(s2) AGAINST ('+p +"ğė Daśi*"' IN BOOLEAN MODE); +DROP TABLE t1; + + +--echo "----------Test19---------" +#19 Failure with Boolean quoted search +CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF8) ENGINE = InnoDB; +INSERT INTO t1 VALUES (1,'İóëɠ'); +CREATE FULLTEXT INDEX i ON t1 (char_column); +SELECT * FROM t1 WHERE MATCH(char_column) AGAINST ('"İóëɠ"' IN BOOLEAN MODE); +DROP TABLE t1; + +--echo "----------Test20---------" +#20 Crash with utf32 and boolean mode. +CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF32, char_column2 VARCHAR(60) character set utf8) ENGINE = InnoDB; +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; + +--echo "----------Test22---------" +# case 22 +CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF8) ENGINE = InnoDB; +INSERT INTO t1 VALUES (1,'aaa'),(2,'bbb'),(3,'ccc'); +CREATE FULLTEXT INDEX i ON t1 (char_column); +HANDLER t1 OPEN; +--error ER_KEY_DOESNT_SUPPORT +HANDLER t1 READ i = ('aaa'); +DROP TABLE t1; +#23. Duplicate key error when there are no unique indexes (procedure test) +#24 Failure after cascading update - already have tests + +--echo "----------Test25---------" +#25 Failure with Croatian boolean truncated search. +CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF8 COLLATE UTF8_CROATIAN_CI) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,'LJin'),(2,'ljin'),(3,'lmin'),(4,'LJLJLJLJLJ'); +CREATE FULLTEXT INDEX i ON t1 (char_column); +#inndob:error incorrect result correct it after fix +SELECT count(*) FROM t1 WHERE MATCH (char_column) AGAINST ('lj*' IN BOOLEAN MODE); +DROP TABLE t1; + +#26. Index error when run procedure call from multiple clients + +--echo "----------Test27---------" +#27 Crash after server restart +CREATE TABLE t1 (id INT,char_column VARCHAR(60)) ENGINE=InnoDB; +SET @@autocommit=0; +CREATE FULLTEXT INDEX i ON t1 (char_column); +INSERT INTO t1 values (1,'aaa'); +echo "restart server..." +# Restart the server +--source include/restart_mysqld.inc +DELETE FROM t1 WHERE MATCH(char_column) AGAINST ('bbb'); +SET @@autocommit=1; +DROP TABLE t1; + +--echo "----------Test28---------" +drop table if exists `fts_test`; +create table `fts_test`(`a` text,fulltext key(`a`))engine=innodb; +set session autocommit=0; +insert into `fts_test` values (''); +savepoint `b`; +savepoint `b`; +set session autocommit=1; +DROP TABLE fts_test; + +# Continue test savepoint related operations. With a commit after +# multiple rollback to savepoints +--echo "----------Test29---------" +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT (title,body) + ) ENGINE=InnoDB; + +INSERT INTO articles (title,body) VALUES + ('MySQL Tutorial','DBMS stands for DataBase ...'); + + +start transaction; + +INSERT INTO articles (title,body) VALUES +('How To Use MySQL Well','After you went through a ...'); + +savepoint `a1`; + +INSERT INTO articles (title,body) VALUES +('Optimizing MySQL','In this tutorial we will show ...'); + +savepoint `a2`; + +INSERT INTO articles (title,body) VALUES +('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'); + +savepoint `a3`; + +INSERT INTO articles (title,body) VALUES +('MySQL vs. YourSQL','In the following database comparison ...'); + +savepoint `a4`; + +# FTS do not parse those uncommitted rows, only one row should show up +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('Database' IN NATURAL LANGUAGE MODE); + +rollback to savepoint a3; + +# The last inserted row should not be there +select title, body from articles; + +INSERT INTO articles (title,body) VALUES +('MySQL Security','When configured properly, MySQL ...'); + +savepoint `a5`; + +select title, body from articles; + +rollback to savepoint a2; + +select title, body from articles; + +commit; + +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('Database' IN NATURAL LANGUAGE MODE); + +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); + +DROP TABLE articles; + +# Continue test savepoint related operations. With a rollback after +# multiple rollback to savepoints +--echo "----------Test30---------" +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT (title,body) + ) ENGINE=InnoDB; + +INSERT INTO articles (title,body) VALUES + ('MySQL Tutorial','DBMS stands for DataBase ...'); + +start transaction; + +INSERT INTO articles (title,body) VALUES +('How To Use MySQL Well','After you went through a ...'); + +savepoint `a1`; + +INSERT INTO articles (title,body) VALUES +('Optimizing MySQL','In this tutorial we will show ...'); + +savepoint `a2`; + +INSERT INTO articles (title,body) VALUES +('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'); + +savepoint `a3`; + +INSERT INTO articles (title,body) VALUES +('MySQL vs. YourSQL','In the following database comparison ...'); + +savepoint `a4`; + +# FTS do not parse those uncommitted rows, only one row should show up +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('Database' IN NATURAL LANGUAGE MODE); + +rollback to savepoint a3; + +# The last inserted row should not be there +select title, body from articles; + +INSERT INTO articles (title,body) VALUES +('MySQL Security','When configured properly, MySQL ...'); + +savepoint `a5`; + +select title, body from articles; + +rollback to savepoint a2; + +select title, body from articles; + +rollback; + +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('Database' IN NATURAL LANGUAGE MODE); + +SELECT * FROM articles + WHERE MATCH (title,body) + AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); + +DROP TABLE articles; + +# Test for Bug #13907075 - DIFFERENT RESULTS FOR DIFFERENT TERM ORDER +# WITH INNODB BOOLEAN FULLTEXT SEARCH. The FTS_IGNORE ("-") operation +# is orderless +# 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 ...'); + +-- disable_result_log +ANALYZE TABLE articles; +-- enable_result_log + +SELECT *, MATCH(title, body) AGAINST ('-database +MySQL' IN BOOLEAN MODE) AS score from articles; + +SELECT *, MATCH(title, body) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score FROM articles; + +# With subquery +SELECT * FROM articles where MATCH(title, body) AGAINST ('MySQL - (database - tutorial)' IN BOOLEAN MODE); + +SELECT * FROM articles where MATCH(title, body) AGAINST ('MySQL - (- tutorial database)' IN BOOLEAN MODE); + +# More complex query +SELECT * FROM articles where MATCH(title, body) AGAINST ('MySQL - (- tutorial database) -Tricks' IN BOOLEAN MODE); + +SELECT * FROM articles where MATCH(title, body) AGAINST ('-Tricks MySQL - (- tutorial database)' IN BOOLEAN MODE); + +DROP TABLE articles; + +# Test for Bug 13940669 - 64901: INNODB: ASSERTION FAILURE IN +# THREAD 34387022112 IN FILE REM0CMP.CC LINE 5 + +drop table if exists t1; + +create table t1 (FTS_DOC_ID bigint unsigned auto_increment not null primary key, +title varchar(200),body text,fulltext(title,body)) engine=innodb; + +insert into t1 set body='test'; + +select * from t1 where match(title,body) against('%test'); + +select * from t1 where match(title,body) against('%'); + +select * from t1 where match(title,body) against('%%%%'); + +drop table t1; + +# Test for Bug 13881758 - 64745: CREATE FULLTEXT INDEX CAUSES CRASH +# Create a database with empty space in its name +CREATE DATABASE `benu database`; + +USE `benu database`; + +# Create FTS table +CREATE TABLE t1 ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + a VARCHAR(200), + b TEXT + ) ENGINE = InnoDB; + +# Insert rows +INSERT INTO t1 (a,b) 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 ...'); + +# Create the FTS index Using Alter Table +ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b); +EVAL SHOW CREATE TABLE t1; + +# Insert rows +INSERT INTO t1 (a,b) VALUES + ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), + ('MySQL vs. YourSQL','In the following database comparison ...'), + ('MySQL Security','When configured properly, MySQL ...'); + +# Select word "tutorial" in the table +SELECT id FROM t1 WHERE MATCH (a,b) + AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); + +# boolean mode +select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE); +select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE); +select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE); +select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE); +select id, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1; +select id, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1; +select id from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE); +select id from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE); +select id from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE); + +DROP DATABASE `benu database`; + +USE test; + +# Test for Bug #14101706 - CRASH WITH DDL IN ROW_MERGE_BUILD_INDEXES +# WHEN FULLTEXT INDEXES EXIST + +CREATE TABLE `t21` (`a` text, `b` int not null, +fulltext key (`a`), fulltext key (`a`) +) ENGINE=INNODB DEFAULT CHARSET=LATIN1; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE `t21` ADD UNIQUE INDEX (`b`), ALGORITHM=INPLACE; +ALTER TABLE `t21` ADD UNIQUE INDEX (`b`); + +DROP TABLE t21; + +CREATE TABLE `t21` (`a` text, `b` int not null, +fulltext key (`a`)) ENGINE=INNODB DEFAULT CHARSET=LATIN1; + +ALTER TABLE `t21` ADD UNIQUE INDEX (`b`); + +DROP TABLE t21; + +# Test primary index rebuild +CREATE TABLE t1 ( + id INT NOT NULL, + a VARCHAR(200), + b TEXT + ) ENGINE = InnoDB; + +# Insert rows +INSERT INTO t1 VALUES + (1, 'MySQL Tutorial','DBMS stands for DataBase ...') , + (2, 'How To Use MySQL Well','After you went through a ...'), + (3, 'Optimizing MySQL','In this tutorial we will show ...'); + +ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b); + +ALTER TABLE t1 ADD UNIQUE INDEX (`id`); + +# Select word "tutorial" in the table +SELECT id FROM t1 WHERE MATCH (a,b) + AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); + +# boolean mode +select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE); +select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE); +select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE); +select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE); +select id, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1; + +DROP TABLE t1; + +# Test create the FTS and primary index in the same clause +CREATE TABLE t1 ( + id INT NOT NULL, + a VARCHAR(200), + b TEXT + ) ENGINE = InnoDB; + +# Insert rows +INSERT INTO t1 VALUES + (1, 'MySQL Tutorial','DBMS stands for DataBase ...') , + (2, 'How To Use MySQL Well','After you went through a ...'), + (3, 'Optimizing MySQL','In this tutorial we will show ...'); + +ALTER TABLE t1 ADD UNIQUE INDEX (`id`), ADD FULLTEXT INDEX idx (a,b); + +# Select word "tutorial" in the table +SELECT id FROM t1 WHERE MATCH (a,b) + AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); + +# boolean mode +select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE); +select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE); +select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE); +select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE); + +DROP TABLE t1; + +# Create FTS table with FTS_DOC_ID already existed +CREATE TABLE t1 ( + FTS_DOC_ID BIGINT UNSIGNED NOT NULL, + a VARCHAR(200), + b TEXT + ) ENGINE = InnoDB; + +# Insert rows +INSERT INTO t1 VALUES + (1, 'MySQL Tutorial','DBMS stands for DataBase ...') , + (2, 'How To Use MySQL Well','After you went through a ...'), + (3, 'Optimizing MySQL','In this tutorial we will show ...'); + +ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b); + +ALTER TABLE t1 ADD UNIQUE INDEX (`FTS_DOC_ID`); + +# Select word "tutorial" in the table +SELECT FTS_DOC_ID FROM t1 WHERE MATCH (a,b) + AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); + +# boolean mode +select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE); +select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE); +select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE); +select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE); +select FTS_DOC_ID, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1; + +DROP TABLE t1; + +# Create FTS table with FTS_DOC_ID and FTS_DOC_ID_INDEX +CREATE TABLE t1 ( + FTS_DOC_ID BIGINT UNSIGNED NOT NULL, + a VARCHAR(200), + b TEXT + ) ENGINE = InnoDB; + +# Insert rows +INSERT INTO t1 VALUES + (1, 'MySQL Tutorial','DBMS stands for DataBase ...') , + (2, 'How To Use MySQL Well','After you went through a ...'), + (3, 'Optimizing MySQL','In this tutorial we will show ...'); + +ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b), ADD UNIQUE INDEX FTS_DOC_ID_INDEX (FTS_DOC_ID); + +# Select word "tutorial" in the table +SELECT FTS_DOC_ID FROM t1 WHERE MATCH (a,b) + AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); + +# boolean mode +select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE); +select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE); +select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE); +select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE); +select FTS_DOC_ID, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1; + +DROP TABLE t1; + +# Test for bug #14079609 - FTS: CRASH IN FTS_TRX_TABLE_CMP WITH SAVEPOINTS, XA + +CREATE TABLE t2 (`b` char(2),fulltext(`b`)) ENGINE=INNODB +DEFAULT CHARSET=LATIN1; + +CREATE TABLE t3 LIKE t2; + +INSERT INTO `t2` VALUES(); + +COMMIT WORK AND CHAIN; + +INSERT INTO `t3` VALUES (); +UPDATE `t2` SET `b` = 'a'; + +SAVEPOINT BATCH1; + +DROP TABLE t2; +DROP TABLE t3; + +# Create FTS table +CREATE TABLE t1 ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + a VARCHAR(200), + b TEXT + ) ENGINE = InnoDB; + +# Insert rows +INSERT INTO t1 (a,b) 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 ...'); + +# Create the FTS index Using Alter Table +ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b); + +COMMIT WORK AND CHAIN; + +INSERT INTO t1 (a,b) VALUES + ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), + ('MySQL vs. YourSQL','In the following database comparison ...'), + ('MySQL Security','When configured properly, MySQL ...'); + +SAVEPOINT BATCH1; + +SELECT id FROM t1 WHERE MATCH (a,b) + AGAINST ('MySQL' IN NATURAL LANGUAGE MODE); + +INSERT INTO t1 (a,b) VALUES + ('1002 MySQL Tricks','1. Never run mysqld as root. 2. ...'), + ('MySQL vs. YourSQL','In the following database comparison ...'), + ('MySQL Security','When configured properly, MySQL ...'); + + +ROLLBACK TO SAVEPOINT BATCH1; + +COMMIT; + +SELECT id FROM t1 WHERE MATCH (a,b) + AGAINST ('MySQL' IN NATURAL LANGUAGE MODE); + +DROP TABLE t1; + +# Test for Bug 14588091 - FTS: BUFFER OVERFLOW IN FTS_AST_CREATE_NODE_TEXT +CREATE TABLE `t` (`a` char(20) character set utf8 default null, +fulltext key (`a`)) ENGINE=INNODB; +INSERT INTO `t` VALUES ('a'); +INSERT INTO `t` VALUES ('aaa'); + +# 0x22 is the '"', 0xdd is not encoded in utf8 +SELECT MATCH(`a`) AGAINST (0x22dd22) FROM `t`; +SELECT MATCH(`a`) AGAINST (0x2222) FROM `t`; +SELECT MATCH(`a`) AGAINST (0x22) FROM `t`; + +# this should show one match +SELECT MATCH(`a`) AGAINST (0x2261616122) FROM `t`; + +# again 0xdd should be ignored +SELECT MATCH(`a`) AGAINST (0x2261dd6122) FROM `t`; + +SELECT MATCH(`a`) AGAINST (0x2261dd612222226122) FROM `t`; + +DROP TABLE t; + +# InnoDB FTS does not support index scan from handler +CREATE TABLE t(a CHAR(1),FULLTEXT KEY(a)) ENGINE=INNODB; +HANDLER t OPEN; +HANDLER t READ a NEXT; +HANDLER t READ a PREV; +DROP TABLE t; + +CREATE TABLE `%`(a TEXT, FULLTEXT INDEX(a)) ENGINE=INNODB; +CREATE TABLE `A B`(a TEXT, FULLTEXT INDEX(a)) ENGINE=INNODB; +DROP TABLE `%`; +DROP TABLE `A B`; + +CREATE TABLE `t-26`(a VARCHAR(10),FULLTEXT KEY(a)) ENGINE=INNODB; +INSERT INTO `t-26` VALUES('117'); +DROP TABLE `t-26`; + +# Test on phrase search with stopwords contained in the search string +CREATE TABLE `t1` ( + `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + `content` TEXT NOT NULL, + PRIMARY KEY (`id`), + FULLTEXT INDEX `IDX_CONTEXT_FULLTEXT`(`content`) +) +ENGINE = InnoDB; + +insert into t1 (content) +values +('This is a story which has has a complicated phrase structure here in the +middle'), +('This is a story which doesn''t have that text'), +('This is a story that has complicated the phrase structure'); + +select * from t1 +where match(content) against('"complicated phrase structure"' in boolean +mode); + +# Test single phrase search with "+" symbol, one row should be returned +select * from t1 +where match(content) against('+"complicated phrase structure"' in boolean +mode); + +# Test phrase search with stopwords in between, one row should be returned +select * from t1 +where match(content) against('"complicated the phrase structure"' in boolean +mode); + +# Test phrase search with multiple "+" symbols +select * from t1 where match(content) against('+"this is a story which" +"complicated the phrase structure"' in boolean mode); + +# Test phrase search with leading word is a stopword, such stopword would be +# ignored +select * from t1 where match(content) against('"the complicated the phrase structure"' in boolean mode); + +# Test phrase search with non-matching stopword in between, no row should be +# returned +select * from t1 where match(content) against('"complicated a phrase structure"' in boolean mode); + +DROP TABLE t1; + +CREATE TABLE my (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, +c VARCHAR(32), FULLTEXT(c)) ENGINE = INNODB; + +INSERT INTO my (c) VALUES ('green-iguana'); + +SELECT * FROM my WHERE MATCH(c) AGAINST ('green-iguana'); + +DROP TABLE my; + +CREATE TABLE ift ( + `a` int(11) NOT NULL, + `b` text, + PRIMARY KEY (`a`), + FULLTEXT KEY `b` (`b`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO ift values (1, "skip"); +INSERT INTO ift values (2, "skip and networking"); +INSERT INTO ift values (3, "--skip-networking"); +INSERT INTO ift values (4, "-donot--skip-networking"); + +SELECT * FROM ift WHERE MATCH (b) AGAINST ('--skip-networking'); +SELECT * FROM ift WHERE MATCH (b) AGAINST ('skip-networking'); +SELECT * FROM ift WHERE MATCH (b) AGAINST ('----'); +SELECT * FROM ift WHERE MATCH (b) AGAINST ('-donot--skip-networking'); + +DROP TABLE ift; + +# Test special cases of wildword. +# 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 ...'), + ('( that''s me )','When configured properly, MySQL ...'); + +SELECT * FROM articles WHERE MATCH (title,body) + AGAINST ('( yours''s* )' IN BOOLEAN MODE); + +SELECT * FROM articles WHERE MATCH (title,body) + AGAINST ('s*' IN BOOLEAN MODE); + +SELECT * FROM articles WHERE MATCH (title,body) + AGAINST ('stands\'] | * | show[@database' IN NATURAL LANGUAGE MODE); + +DROP TABLE articles; + +# Test for BUG#16429688 - FTS: SYNTAX ERROR, UNEXPECTED '*', EXPECTING $END +CREATE TABLE t1(a TEXT CHARACTER SET LATIN1, FULLTEXT INDEX(a)) ENGINE=INNODB; + +--error ER_PARSE_ERROR +SELECT * FROM t1 WHERE MATCH(a) AGAINST("*"); + +DROP TABLE t1; + +# Test for BUG#16516193 - LITERAL PHRASES CANNOT BE COMBINED WITH + OR - OPERATOR +# Create FTS table +CREATE TABLE t1 ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + a VARCHAR(200), + FULLTEXT (a) + ) ENGINE= InnoDB; + +# Insert rows +INSERT INTO t1 (a) VALUES + ('Do you know MySQL is a good database'), + ('How to build a good database'), + ('Do you know'), + ('Do you know MySQL'), + ('How to use MySQL'), + ('Do you feel good'), + ('MySQL is good'), + ('MySQL is good to know'), + ('What is database'); + +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know mysql"' IN BOOLEAN MODE); + +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+("know mysql")' IN BOOLEAN MODE); + +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('("know mysql" good)' IN BOOLEAN MODE); + +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+("know mysql" good)' IN BOOLEAN MODE); + +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('(good "know mysql")' IN BOOLEAN MODE); + +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+(good "know mysql")' IN BOOLEAN MODE); + +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+("know mysql" "good database")' IN BOOLEAN MODE); + +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know mysql" +"good database"' IN BOOLEAN MODE); + +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know database"@4' IN BOOLEAN MODE); + +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know database"@8' IN BOOLEAN MODE); + +# Drop table +DROP TABLE t1; + +# Test for BUG#16885178 - INNODB FULLTEXT PHRASE SEARCH VALGRIND ERROR +CREATE TABLE t1 ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + a VARCHAR(200), + FULLTEXT (a) + ) ENGINE= InnoDB; + +# Insert a special row +INSERT INTO t1 (a) VALUES + ('know mysql good database'); + +# This phrase search fails in valgrind test before the fix. +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"good database"' IN BOOLEAN MODE); + +DROP TABLE t1; + +# Test single term ranking +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT (title,body) + ) ENGINE=InnoDB; + +# Repeatedly insert/delete records, the ranking should be the same for +# each of them +INSERT INTO articles (title,body) VALUES ('Test Article','blah blah +blah'),("Matt's Noise",'this is noisy'),('February Weather','It was terrible +this year.'),('Peter Pan','Tis a kids story.'),('Test1','nada'),('Database +database database','foo database database database'),('Database article +title','body with lots of words.'),('myfulltext database', 'my test fulltext +database'); + +SELECT id, title, body FROM articles ORDER BY MATCH (title,body) AGAINST ('database' IN BOOLEAN MODE) DESC; +DELETE from articles WHERE title like "myfulltext database"; + +INSERT INTO articles (title,body) VALUES ('myfulltext database', 'my test fulltext database'); + +SELECT id, title, body FROM articles ORDER BY MATCH (title,body) AGAINST ('database' IN BOOLEAN MODE) DESC; +DELETE from articles WHERE title like "myfulltext database"; + +INSERT INTO articles (title,body) VALUES ('myfulltext database', 'my test fulltext database'); + +SELECT id, title, body FROM articles ORDER BY MATCH (title,body) AGAINST ('database' IN BOOLEAN MODE) DESC; + +DROP TABLE articles; + +# Test for BUG 18277305 - FTS: FAILING ASSERTION: PTR[1] == '\"' +# IN FTS_AST_CREATE_NODE_TEXT +CREATE TABLE t1( + a TEXT CHARSET ujis COLLATE ujis_japanese_ci, + b TEXT CHARSET utf8mb4 COLLATE utf8mb4_turkish_ci, + c TEXT CHARSET eucjpms COLLATE eucjpms_bin, + d TEXT CHARSET utf8mb4, + FULLTEXT INDEX(a), + FULLTEXT INDEX(b), + FULLTEXT INDEX(c), + FULLTEXT INDEX(d) +) ENGINE = InnoDB; + +INSERT INTO t1 VALUES + ('myisam', 'myisam', 'myisam', 'myisam'), + ('innodb', 'innodb', 'innodb', 'innodb'), + ('innodb myisam', 'innodb myisam', 'innodb myisam', 'innodb myisam'), + ('memory', 'memory', 'memory', 'memory'), + ('archive', 'archive', 'archive', 'archive'), + ('federated', 'federated', 'federated', 'federated'), + ('storage engine innodb', 'storage engine innodb', 'storage engine innodb', 'storage engine innodb'), + ('storage engine myisam', 'storage engine myisam', 'storage engine myisam', 'storage engine myisam'), + ('innobase', 'innobase', 'innobase', 'innobase'), + ('myisam innodb', 'myisam innodb', 'myisam innodb', 'myisam innodb'), + ('innodb myisam engines', 'innodb myisam engines', 'innodb myisam engines', 'innodb myisam engines'); + +# Test the ujis_japanese_ci +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00)); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', ' ', '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00, '"', 0x00, '"', 0x00)); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', '&', 0x00, '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, '&', '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', '%', '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00, '"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', 0x00, 0x00, 0x00, '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00, '"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00)); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00)); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00, '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', '(', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', ')')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', 'm', 'y', 'i', 's', 'a', 'm')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', 'm', 'y', 'i', 's', 'a', 'm', '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00, '"','@', '2') IN BOOLEAN MODE); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', '$', 'm', 'y', 'i', 's', 'a', 'm', '"','@', '4') IN BOOLEAN MODE); +--error ER_PARSE_ERROR +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00, '"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', '$', 's', 't', 'o', 'r', 'a', 'g', 'e', '"','@', '4', 0x00) IN BOOLEAN MODE); + + +# Test the utf8mb4_turkish_ci +SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"')); +SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT(0x00)); +SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', '"')); +SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', ' ', '"')); +SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', 0x00, '"')); +SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT(0x00, '"', 0x00, '"', 0x00)); +SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', '&', 0x00, '"')); +SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', 0x00, '&', '"')); +SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', '%', '"')); +SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '"')); +SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '"')); +SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT(0x00, '"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', 0x00, 0x00, 0x00, '"')); +SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT(0x00, '"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00)); +SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00, '"','@', '2') IN BOOLEAN MODE); +SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', '$', 'm', 'y', 'i', 's', 'a', 'm', '"','@', '4') IN BOOLEAN MODE); +--error ER_PARSE_ERROR +SELECT b FROM t1 WHERE MATCH (b) AGAINST (CONCAT(0x00, '"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', '$', 's', 't', 'o', 'r', 'a', 'g', 'e', '"','@', '4', 0x00) IN BOOLEAN MODE); + +# Test the eucjpms_bin +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"')); +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT(0x00)); +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', '"')); +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', ' ', '"')); +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 0x00, '"')); +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT(0x00, '"', 0x00, '"', 0x00)); +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', '&', 0x00, '"')); +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 0x00, '&', '"')); +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', '%', '"')); +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '"')); +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '"')); +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT(0x00, '"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', 0x00, 0x00, 0x00, '"')); +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT(0x00, '"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00)); +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00)); +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00, '"')); +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', '(', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', ')')); +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', 'm', 'y', 'i', 's', 'a', 'm')); +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', 'm', 'y', 'i', 's', 'a', 'm', '"')); +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00, '"','@', '2') IN BOOLEAN MODE); +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', '$', 'm', 'y', 'i', 's', 'a', 'm', '"','@', '4') IN BOOLEAN MODE); +--error ER_PARSE_ERROR +SELECT c FROM t1 WHERE MATCH (c) AGAINST (CONCAT(0x00, '"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', '$', 's', 't', 'o', 'r', 'a', 'g', 'e', '"','@', '4', 0x00) IN BOOLEAN MODE); + +ALTER TABLE t1 ENGINE = MyISAM; + +# Test the query against myisam to verify +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00)); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', ' ', '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00, '"', 0x00, '"', 0x00)); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', '&', 0x00, '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, '&', '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', '%', '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00, '"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', 0x00, 0x00, 0x00, '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00, '"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', 0x00, '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00)); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00)); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00, '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', '(', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', ')')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', 'm', 'y', 'i', 's', 'a', 'm')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', 'm', 'y', 'i', 's', 'a', 'm', '"')); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 0x00, 'i', 'n', 'n', 'o', 'd', 'b', '@', '$', 'm', 'y', 'i', 's', 'a', 'm', '%', 0x00, 0x00, '"','@', '2') IN BOOLEAN MODE); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT('"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', '$', 'm', 'y', 'i', 's', 'a', 'm', '"','@', '4') IN BOOLEAN MODE); +SELECT a FROM t1 WHERE MATCH (a) AGAINST (CONCAT(0x00, '"', 'i', 'n', 'n', 'o', 'd', 'b', ' ', '$', 's', 't', 'o', 'r', 'a', 'g', 'e', '"','@', '4', 0x00) IN BOOLEAN MODE); + +DROP TABLE t1; + +# Test for BUG#18229097 - FTS: DID NOT FIND WORD 0 IN DOC 39161 FOR QUERY EXPANSION SEARCH. +CREATE TABLE t1 ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + a VARCHAR(200), + FULLTEXT (a) +) ENGINE= InnoDB; + +INSERT INTO t1 (a) VALUES + ('know database'),('good database'), ('gmail email'), ('ghome windows'); + +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('g *' IN NATURAL LANGUAGE MODE); + +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('g *' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION); + +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('g * k *' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION); + +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('g * k * d *' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION); + +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('g * go *' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION); + +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('g * good' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION); + +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('gm * go *' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION); + +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('good *' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION); + +SELECT * FROM t1 WHERE MATCH (a) AGAINST ('g* database' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION); + +DROP TABLE t1; diff --git a/mysql-test/suite/innodb_fts/t/misc_1.test b/mysql-test/suite/innodb_fts/t/misc_1.test new file mode 100644 index 00000000..45eaf447 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/misc_1.test @@ -0,0 +1,894 @@ +--source include/have_innodb.inc +--source include/innodb_page_size_small.inc +--source include/no_valgrind_without_big.inc + +#------------------------------------------------------------------------------ +# FTS with FK and update cascade +#------------------------------------------------------------------------------- +set names utf8; + +call mtr.add_suppression("\\[Warning\\] InnoDB: A new Doc ID must be supplied while updating FTS indexed columns."); +call mtr.add_suppression("\\[Warning\\] InnoDB: FTS Doc ID must be larger than [0-9]+ for table `test`.`t1`"); + +# Create FTS table +CREATE TABLE t1 ( + id1 INT , + a1 VARCHAR(200) , + b1 TEXT , + FULLTEXT KEY (a1,b1), PRIMARY KEY (a1, id1) + ) CHARACTER SET = utf8 , ENGINE = InnoDB; + +CREATE TABLE t2 ( + id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + a2 VARCHAR(200), + b2 TEXT , + FOREIGN KEY (a2) REFERENCES t1(a1) ON UPDATE CASCADE, + FULLTEXT KEY (b2,a2) + ) CHARACTER SET = utf8 ,ENGINE = InnoDB; + +# Insert rows +INSERT INTO t1 (id1,a1,b1) VALUES + (1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , + (2,'How To Use MySQL Well','After you went through a ...'), + (3,'Optimizing MySQL','In this tutorial we will show ...'); + +# Insert rows +INSERT INTO t1 (id1,a1,b1) VALUES + (4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), + (5,'MySQL vs. YourSQL','In the following database comparison ...'), + (6,'MySQL Security','When configured properly, MySQL ...'); + +# Insert rows in t2 fk table +INSERT INTO t2 (a2,b2) VALUES + ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , + ('How To Use MySQL Well','After you went through a ...'), + ('Optimizing MySQL','In this tutorial we will show ...'); + +# Insert rows t2 fk table +INSERT INTO t2 (a2,b2) VALUES + ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), + ('MySQL vs. YourSQL','In the following database comparison ...'), + ('MySQL Security','When configured properly, MySQL ...'); + +# error on violating fk constraint +--error 1452 +INSERT INTO t2 (a2,b2) VALUES + ('MySQL Tricks','1. Never run mysqld as root. 2. ...'); + +# error on delete from parent table +--error 1451 +DELETE FROM t1; + +ANALYZE TABLE t1; +ANALYZE TABLE t2; + +SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ORDER BY id1; +SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ORDER BY id2; + +SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id1; +SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id2; + +SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id1; +SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id2; + + +SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ; +SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ; + +set global innodb_optimize_fulltext_only=1; +optimize table t1; +set global innodb_optimize_fulltext_only=0; +# Updating parent table hence child table should get updated due to 'update cascade' clause +UPDATE t1 SET a1 = "changing column - on update cascade" , b1 = "to check foreign constraint" WHERE +MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; + +# no records expected +SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; +# InnoDB:Error child table shows records which is incorrect - UPADTE on Fix +SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; + +# it shows updated record +SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('+update +cascade' IN BOOLEAN MODE) ORDER BY id1; +# InnoDB:Error child table does not show the expected record +SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('+update +cascade' IN BOOLEAN MODE) ORDER BY id2; +SELECT id2 FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%' ORDER BY id2; + +DROP TABLE t2 , t1; + +# on update cascade +create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB; +create table t2 (s1 int, s2 varchar(200), + fulltext key(s2), + foreign key (s1,s2) references t1 (s1,s2) on update cascade) ENGINE = InnoDB; +insert into t1 values (1,'Sunshine'),(2,'Lollipops'); +insert into t2 values (1,'Sunshine'),(2,'Lollipops'); +update t1 set s2 = 'Rainbows' where s2 <> 'Sunshine'; +commit; +select * from t2 where match(s2) against ('Lollipops'); +DROP TABLE t2 , t1; + +# on delete cascade +create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB; +create table t2 (s1 int, s2 varchar(200), + fulltext key(s2), + foreign key (s1,s2) references t1 (s1,s2) on delete cascade) ENGINE = InnoDB; +insert into t1 values (1,'Sunshine'),(2,'Lollipops'); +insert into t2 values (1,'Sunshine'),(2,'Lollipops'); +delete from t1 where s2 <> 'Sunshine'; +select * from t2 where match(s2) against ('Lollipops'); +DROP TABLE t2 , t1; + +# on delete set NULL +create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB; +create table t2 (s1 int, s2 varchar(200), + fulltext key(s2), + foreign key (s1,s2) references t1 (s1,s2) on delete set null) ENGINE = InnoDB; +insert into t1 values (1,'Sunshine'),(2,'Lollipops'); +insert into t2 values (1,'Sunshine'),(2,'Lollipops'); +delete from t1 where s2 <> 'Sunshine'; +select * from t2 where match(s2) against ('Lollipops'); +DROP TABLE t2 , t1; + + +# on update set NULL +create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB; +create table t2 (s1 int, s2 varchar(200), + fulltext key(s2), + foreign key (s1,s2) references t1 (s1,s2) on update set null) ENGINE = InnoDB; +insert into t1 values (1,'Sunshine'),(2,'Lollipops'); +insert into t2 values (1,'Sunshine'),(2,'Lollipops'); +update t1 set s2 = 'Rainbows' where s2 <> 'Sunshine'; +commit; +select * from t2 where match(s2) against ('Lollipops'); +DROP TABLE t2 , t1; + +# When Doc ID is involved +create table t1 (s1 bigint unsigned not null, s2 varchar(200), + primary key (s1,s2)) ENGINE = InnoDB; +create table t2 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL, s2 varchar(200), + foreign key (FTS_DOC_ID) references t1 (s1) + on update cascade) ENGINE = InnoDB; + +create fulltext index idx on t2(s2); + +show create table t2; + +insert into t1 values (1,'Sunshine'),(2,'Lollipops'); +insert into t2 values (1,'Sunshine'),(2,'Lollipops'); + +update t1 set s1 = 3 where s1=1; + +select * from t2 where match(s2) against ('sunshine'); + +# FTS Doc ID cannot be reused +--error 1451 +update t1 set s1 = 1 where s1=3; + +DROP TABLE t2 , t1; + +#------------------------------------------------------------------------------ +# FTS with FK and delete casecade +#------------------------------------------------------------------------------ + +# Create FTS table +CREATE TABLE t1 ( + id1 INT , + a1 VARCHAR(200) PRIMARY KEY, + b1 TEXT character set utf8 , + FULLTEXT KEY (a1,b1) + ) CHARACTER SET = utf8 ,ENGINE = InnoDB; + +CREATE TABLE t2 ( + id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + a2 VARCHAR(200), + b2 TEXT character set utf8 , + FOREIGN KEY (a2) REFERENCES t1(a1) ON DELETE CASCADE, + FULLTEXT KEY (b2,a2) + ) CHARACTER SET = utf8 ,ENGINE = InnoDB; + +# Insert rows +INSERT INTO t1 (id1,a1,b1) VALUES + (1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , + (2,'How To Use MySQL Well','After you went through a ...'), + (3,'Optimizing MySQL','In this tutorial we will show ...'), + (4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), + (5,'MySQL vs. YourSQL','In the following database comparison ...'), + (6,'MySQL Security','When configured properly, MySQL ...'); + +# Insert rows in t2 +INSERT INTO t2 (a2,b2) VALUES + ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , + ('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 ...'); + +# delete records from parent +DELETE FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; + +# no records expected +SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; +SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; + +SELECT * FROM t1 WHERE a1 LIKE '%tutorial%'; +SELECT * FROM t2 WHERE a2 LIKE '%tutorial%'; + +DROP TABLE t2 , t1; + +#------------------------------------------------------------------------------ +# FTS with FK+transactions and UPDATE casecade with transaction +#------------------------------------------------------------------------------- + +call mtr.add_suppression("\\[ERROR\\] InnoDB: FTS Doc ID must be larger than 3 for table `test`.`t2`"); + +# Create FTS table +CREATE TABLE t1 ( + id1 INT , + a1 VARCHAR(200) , + b1 TEXT , + FULLTEXT KEY (a1,b1), PRIMARY KEY(a1, id1) + ) CHARACTER SET = utf8 , ENGINE = InnoDB; + +CREATE TABLE t2 ( + id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + a2 VARCHAR(200), + b2 TEXT , + FOREIGN KEY (a2) REFERENCES t1(a1) ON UPDATE CASCADE, + FULLTEXT KEY (b2,a2) + ) CHARACTER SET = utf8 ,ENGINE = InnoDB; + +# Insert rows +INSERT INTO t1 (id1,a1,b1) VALUES + (1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , + (2,'How To Use MySQL Well','After you went through a ...'), + (3,'Optimizing MySQL','In this tutorial we will show ...'); + +# Insert rows in t2 fk table +INSERT INTO t2 (a2,b2) VALUES + ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , + ('How To Use MySQL Well','After you went through a ...'), + ('Optimizing MySQL','In this tutorial we will show ...'); + +START TRANSACTION; +# Insert rows +INSERT INTO t1 (id1,a1,b1) VALUES + (4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), + (5,'MySQL vs. YourSQL','In the following database comparison ...'), + (6,'MySQL Security','When configured properly, MySQL ...'); + +# Insert rows t2 fk table +INSERT INTO t2 (a2,b2) VALUES + ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), + ('MySQL vs. YourSQL','In the following database comparison ...'), + ('MySQL Security','When configured properly, MySQL ...'); + +# error on violating fk constraint +--error 1452 +INSERT INTO t2 (a2,b2) VALUES + ('MySQL Tricks','1. Never run mysqld as root. 2. ...'); + +# error on DELETE FROM parent table +--error 1451 +DELETE FROM t1; + +# records expected +SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ORDER BY id1; +SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ORDER BY id2; +SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id1; +SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id2; +SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id1; +SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id2; +SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ; +SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ; + +# no records as data not COMMITted. +SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('root') ; +SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('root') ; +SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('mysqld (+root)' IN BOOLEAN MODE) ; +SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('mysqld (-root)' IN BOOLEAN MODE) ; +SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('root' WITH QUERY EXPANSION) ; +SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('root' WITH QUERY EXPANSION) ; +SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('"database comparison"@02' IN BOOLEAN MODE) ; +SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('"database comparison"@02' IN BOOLEAN MODE) ; + +SELECT * FROM t1 ORDER BY id1; +SELECT * FROM t2 ORDER BY id2; + +COMMIT; + +START TRANSACTION; +# Updating parent table hence child table should get updated due to 'UPDATE cascade' clause +UPDATE t1 SET a1 = "changing column - on UPDATE cascade" , b1 = "to check foreign constraint" WHERE +MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; +COMMIT; + +# no records expected +SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; +SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ; + +# it shows updated record +SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ORDER BY id1; +SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ORDER BY id2; +SELECT * FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%' ORDER BY id2; + +DROP TABLE t2 , t1; + + +# FTS with FK+transactions - UPDATE cascade +CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; +CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), + FULLTEXT KEY(s2), + FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE cascade) ENGINE = InnoDB; +START TRANSACTION; +INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); +INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); +UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine'; +COMMIT; +SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); +DROP TABLE t2 , t1; + +# FTS with FK+transactions - on DELETE cascade +CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; +CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), + FULLTEXT KEY(s2), + FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE cascade) ENGINE = InnoDB; +START TRANSACTION; +INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); +INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); +DELETE FROM t1 WHERE s2 <> 'Sunshine'; +COMMIT; +SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); +DROP TABLE t2 , t1; + +# FTS with FK+transactions - DELETE SET NULL +CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; +CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), + FULLTEXT KEY(s2), + FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE SET NULL) ENGINE = InnoDB; +START TRANSACTION; +INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); +INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); +DELETE FROM t1 WHERE s2 <> 'Sunshine'; +COMMIT; +SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); +DROP TABLE t2 , t1; + + +# FTS with FK+transactions - UPDATE SET NULL +CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; +CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), + FULLTEXT KEY(s2), + FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE SET NULL) ENGINE = InnoDB; +START TRANSACTION; +INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); +INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); +UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine'; +COMMIT; +SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); +DROP TABLE t2 , t1; + + +#----------------------------------------------------------------------------- + +# FTS with FK+transactions - UPDATE cascade +CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; +CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), + FULLTEXT KEY(s2), + FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE cascade) ENGINE = InnoDB; +START TRANSACTION; +INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); +INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); +UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine'; +ROLLBACK; +SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); +DROP TABLE t2 , t1; + +# FTS with FK+transactions - DELETE cascade +CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; +CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), + FULLTEXT KEY(s2), + FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE cascade) ENGINE = InnoDB; +START TRANSACTION; +INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); +INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); +DELETE FROM t1 WHERE s2 <> 'Sunshine'; +ROLLBACK; +SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); +DROP TABLE t2 , t1; + +# FTS with FK+transactions - DELETE SET NULL +CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; +CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), + FULLTEXT KEY(s2), + FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE SET NULL) ENGINE = InnoDB; +START TRANSACTION; +INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); +INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); +DELETE FROM t1 WHERE s2 <> 'Sunshine'; +ROLLBACK; +SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); +DROP TABLE t2 , t1; + + +# FTS with FK+transactions - UPDATE SET NULL +CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB; +CREATE TABLE t2 (s1 INT, s2 VARCHAR(200), + FULLTEXT KEY(s2), + FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE SET NULL) ENGINE = InnoDB; +START TRANSACTION; +INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops'); +INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops'); +UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine'; +ROLLBACK; +SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); +DROP TABLE t2 , t1; + + +#------------------------------------------------------------------------------ +# FTS index with compressed row format +#------------------------------------------------------------------------------ + +# Save innodb variables +let $innodb_file_per_table_orig=`select @@innodb_file_per_table`; + +set global innodb_file_per_table=1; + +# Create FTS table +CREATE TABLE t1 ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + a VARCHAR(200), + b TEXT + ) CHARACTER SET = utf8, ROW_FORMAT=COMPRESSED, ENGINE = InnoDB; + +# Insert rows +INSERT INTO t1 (a,b) VALUES + ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') , + ('How To Use MySQL Well','After you went through a ...'), + ('Optimizing MySQL','In this tutorial we will show ...'); + +# Create the FTS index Using Alter Table +ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b); +EVAL SHOW CREATE TABLE t1; + +# Check whether individual space id created for AUX tables +SELECT count(*) FROM information_schema.innodb_sys_tables WHERE name LIKE "%FTS_%" AND space !=0; + +# Insert rows +INSERT INTO t1 (a,b) VALUES + ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), + ('MySQL vs. YourSQL','In the following database comparison ...'), + ('MySQL Security','When configured properly, MySQL ...'); + +-- disable_result_log +ANALYZE TABLE t1; +-- enable_result_log + +# Select word "tutorial" in the table +SELECT * FROM t1 WHERE MATCH (a,b) + AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id; + +# boolean mode +select * from t1 where MATCH(a,b) AGAINST("+tutorial +VÐƷWİ" IN BOOLEAN MODE); +--error ER_PARSE_ERROR +select * from t1 where MATCH(a,b) AGAINST("+-VÐƷWİ" IN BOOLEAN MODE); +select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE); +select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id; +select *, MATCH(a,b) AGAINST("mysql stands" IN BOOLEAN MODE) as x from t1 ORDER BY id; +select * from t1 where MATCH a,b AGAINST ("+database* +VÐƷW*" IN BOOLEAN MODE); +select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE); + +# query expansion +select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id; + +# Drop index +ALTER TABLE t1 DROP INDEX idx; + +# Create the FTS index again +CREATE FULLTEXT INDEX idx on t1 (a,b); + +-- disable_query_log +-- disable_result_log +ANALYZE TABLE t1; +-- enable_result_log +-- enable_query_log + +# Select word "tutorial" in the table +SELECT * FROM t1 WHERE MATCH (a,b) + AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id; + +# boolean mode +select * from t1 where MATCH(a,b) AGAINST("+tutorial +VÐƷWİ" IN BOOLEAN MODE); +select * from t1 where MATCH(a,b) AGAINST("+dbms" IN BOOLEAN MODE); +select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE); +select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id; +select *, MATCH(a,b) AGAINST("mysql VÐƷWİ" IN BOOLEAN MODE) as x from t1 ORDER BY id; +# Innodb:Assert eval0eval.c line 148 +#select * from t1 where MATCH a,b AGAINST ("+database* +VÐƷWİ*" IN BOOLEAN MODE); +select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE); + +# query expansion +select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id; + + +# insert for proximity search +INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...'); +# Insert into table with similar word of different distances +INSERT INTO t1 (a,b) VALUES + ('test proximity search, test, proximity and phrase', + 'search, with proximity innodb'); + +INSERT INTO t1 (a,b) VALUES + ('test proximity fts search, test, proximity and phrase', + 'search, with proximity innodb'); + +INSERT INTO t1 (a,b) VALUES + ('test more proximity fts search, test, more proximity and phrase', + 'search, with proximity innodb'); + +# This should only return the first document +SELECT * FROM t1 + WHERE MATCH (a,b) + AGAINST ('"proximity search"@2' IN BOOLEAN MODE); + +# This would return no document +SELECT * FROM t1 + WHERE MATCH (a,b) + AGAINST ('"proximity search"@1' IN BOOLEAN MODE); + +# This give you all three documents +SELECT * FROM t1 + WHERE MATCH (a,b) + AGAINST ('"proximity search"@3' IN BOOLEAN MODE) ORDER BY id; + +# Similar boundary testing for the words +SELECT * FROM t1 + WHERE MATCH (a,b) + AGAINST ('"test proximity"@5' IN BOOLEAN MODE) ORDER BY id; + +# Test with more word The last document will return, please notice there +# is no ordering requirement for proximity search. +SELECT * FROM t1 + WHERE MATCH (a,b) + AGAINST ('"more test proximity"@2' IN BOOLEAN MODE); + +SELECT * FROM t1 + WHERE MATCH (a,b) + AGAINST ('"more test proximity"@3' IN BOOLEAN MODE); + +# The phrase search will not require exact word ordering +SELECT * FROM t1 + WHERE MATCH (a,b) + AGAINST ('"more fts proximity"@03' IN BOOLEAN MODE); + + +UPDATE t1 SET a = UPPER(a) , b = UPPER(b) ; +UPDATE t1 SET a = UPPER(a) , b = LOWER(b) ; + +select * from t1 where MATCH(a,b) AGAINST("+tutorial +dbms" IN BOOLEAN MODE); +select * from t1 where MATCH(a,b) AGAINST("+VÐƷWİ" IN BOOLEAN MODE); + +SELECT * FROM t1 WHERE MATCH (a,b) + AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id; + +DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); +DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('"proximity search"@14' IN BOOLEAN MODE); + + +SELECT * FROM t1 WHERE MATCH (a,b) + AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); + +SELECT * FROM t1 ORDER BY id; + +DROP TABLE t1; +eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; + +#------------------------------------------------------------------------------ +# FTS index with utf8 character testcase +#------------------------------------------------------------------------------ + +# Create FTS table +EVAL CREATE TABLE t1 ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + a VARCHAR(200), + b TEXT + ) CHARACTER SET = utf8, ENGINE=InnoDB; + + +# Insert rows from different languages +INSERT INTO t1 (a,b) VALUES +('Я могу есть стекло', 'оно мне не вредит'), +('Мога да ям стъкло', 'то не ми вреди'), +('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'), +('Příliš žluťoučký kůň', 'úpěl ďábelské kódy'), +('Sævör grét', 'áðan því úlpan var ónýt'), +('うゐのおくやま','けふこえて'), +('いろはにほへど ちりぬる','あさきゆめみじ ゑひもせず'); + +# insert english text +INSERT INTO t1 (a,b) VALUES + ('MySQL Tutorial','request docteam@oraclehelp.com ...') , + ('Trial version','query performace @1255 minute on 2.1Hz Memory 2GB...') , + ('when To Use MySQL Well','for free faq mail@xyz.com ...'); + +# Create the FTS index again +CREATE FULLTEXT INDEX idx on t1 (a,b); + +# FTS Queries +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("вредит χωρὶς") ORDER BY id; +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("оно" WITH QUERY EXPANSION); + +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE) ORDER BY id; +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+γυαλιὰ +tutorial" IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+tutorial +(Мога τίποτα)" IN BOOLEAN MODE); + +# Innodb:error - no result returned (update result of query once fixed) (innodb limit , does not understand character boundry for japanses like charcter set) +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず"); +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("ちりぬる" WITH QUERY EXPANSION); + +# Innodb:error - no result returned (update result of query once fixed) (innodb limit , does not understand character boundry for japanses like charcter set) +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("+あさきゆめみじ +ゑひもせず" IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("うゐのおく*" IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE); + +SELECT * FROM t1 + WHERE MATCH (a,b) + AGAINST ('"γυαλιὰ χωρὶς"@2' IN BOOLEAN MODE); + +SELECT * FROM t1 + WHERE MATCH (a,b) + AGAINST ('"query performace"@02' IN BOOLEAN MODE); + +SELECT * FROM t1 + WHERE MATCH (a,b) + AGAINST ('"πάθω τίποτα"@2' IN BOOLEAN MODE); + +SELECT * FROM t1 + WHERE MATCH (a,b) + AGAINST ('"あさきゆめみじ ゑひもせず"@1' IN BOOLEAN MODE); + +SELECT * FROM t1 + WHERE MATCH (a,b) + AGAINST ('"あさきゆめみじ ゑひもせず"@2' IN BOOLEAN MODE); + +ALTER TABLE t1 DROP INDEX idx; +# Create the FTS index again +CREATE FULLTEXT INDEX idx on t1 (a,b); + +# Innodb:error - no result returned (update result of query once fixed) (innodb limit , does not understand character boundry for japanses like charcter set) +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず"); +# Update fails because where condition do not succeed which is incorrect (update result of query once fixed) +UPDATE t1 SET a = "Pchnąć w tę łódź jeża" , b = "lub osiem skrzyń fig" WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず"); +UPDATE t1 SET a = "В чащах юга жил-был цитрус? Да", b = "но фальшивый экземпляр! ёъ" WHERE MATCH(a,b) AGAINST ("вред*" IN BOOLEAN MODE); +DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE); + +# Innodb error - no result returned +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず"); +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("łódź osiem"); +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("фальшив*" IN BOOLEAN MODE) ORDER BY id; +SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE); + +SELECT * FROM t1 + WHERE MATCH (a,b) + AGAINST ('"łódź jeża"@2' IN BOOLEAN MODE); + +SELECT * FROM t1 ORDER BY id; +DROP TABLE t1; + +# This is to test the update operation on FTS indexed and non-indexed +# column +CREATE TABLE t1(ID INT PRIMARY KEY, + no_fts_field VARCHAR(10), + fts_field VARCHAR(10), + FULLTEXT INDEX f(fts_field)) ENGINE=INNODB; + +INSERT INTO t1 VALUES (1, 'AAA', 'BBB'); + +SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB"); + +# Update FULLTEXT indexed column, Doc ID will be updated +UPDATE t1 SET fts_field='anychange' where id = 1; + +SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange"); + +# Update non-FULLTEXT indexed column, Doc ID stay to be the same +UPDATE t1 SET no_fts_field='anychange' where id = 1; + +SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange"); + +# Update both FULLTEXT indexed and non-indexed column, Doc ID will be updated +UPDATE t1 SET no_fts_field='anychange', fts_field='other' where id = 1; + +SELECT * FROM t1 WHERE MATCH(fts_field) against("other"); + +SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB"); + +# FTS index dropped, the DOC_ID column is kept, however, the ID will not +# change +DROP INDEX f on t1; + +UPDATE t1 SET fts_field='anychange' where id = 1; + +UPDATE t1 SET no_fts_field='anychange' where id = 1; + +UPDATE t1 SET no_fts_field='anychange', fts_field='other' where id = 1; + +CREATE FULLTEXT INDEX f ON t1(FTS_FIELD); + +SELECT * FROM t1 WHERE MATCH(fts_field) against("other"); + +DROP TABLE t1; + +# Test on user supplied 'FTS_DOC_ID' +CREATE TABLE t1(`FTS_DOC_ID` serial, + no_fts_field VARCHAR(10), + fts_field VARCHAR(10), + FULLTEXT INDEX f(fts_field)) ENGINE=INNODB; + +INSERT INTO t1 VALUES (1, 'AAA', 'BBB'); + +# Doc ID must be updated as well (HA_FTS_INVALID_DOCID). +--error 182 +UPDATE t1 SET fts_field='anychange' where FTS_DOC_ID = 1; + +UPDATE t1 SET fts_field='anychange', FTS_DOC_ID = 2 where FTS_DOC_ID = 1; + +SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange"); + +# "BBB" should be marked as deleted. +SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB"); + +UPDATE t1 SET no_fts_field='anychange' where FTS_DOC_ID = 2; + +SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange"); + +# "HA_FTS_INVALID_DOCID" +--error 182 +UPDATE t1 SET no_fts_field='anychange', fts_field='other' where FTS_DOC_ID = 2; + +SELECT * FROM t1 WHERE MATCH(fts_field) against("other"); + +# Doc ID must be monotonically increase (HA_FTS_INVALID_DOCID) +--error 182 +UPDATE t1 SET FTS_DOC_ID = 1 where FTS_DOC_ID = 2; + +DROP INDEX f ON t1; + +# After FULLTEXT index dropped, we can update the fields freely +UPDATE t1 SET fts_field='newchange' where FTS_DOC_ID = 2; + +UPDATE t1 SET no_fts_field='anychange' where FTS_DOC_ID = 2; + +SELECT * FROM t1; + +DROP TABLE t1; + +CREATE TABLE t1(ID INT PRIMARY KEY, + no_fts_field VARCHAR(10), + fts_field VARCHAR(10), + FULLTEXT INDEX f(fts_field), index k(fts_field)) ENGINE=INNODB; + +CREATE TABLE t2(ID INT PRIMARY KEY, + no_fts_field VARCHAR(10), + fts_field VARCHAR(10), + FULLTEXT INDEX f(fts_field), + INDEX k2(fts_field), + FOREIGN KEY(fts_field) REFERENCES + t1(fts_field) ON UPDATE CASCADE) ENGINE=INNODB; + +INSERT INTO t1 VALUES (1, 'AAA', 'BBB'); + +INSERT INTO t2 VALUES (1, 'AAA', 'BBB'); + +update t1 set fts_field='newchange' where id =1; + +SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB"); +SELECT * FROM t2 WHERE MATCH(fts_field) against("BBB"); +SELECT * FROM t1 WHERE MATCH(fts_field) against("newchange"); +SELECT * FROM t2 WHERE MATCH(fts_field) against("newchange"); + +DROP TABLE t2; + +DROP TABLE t1; + +# Testcases adopted from innodb_multi_update.test + +CREATE TABLE t1(id INT PRIMARY KEY, + fts_field VARCHAR(10), + FULLTEXT INDEX f(fts_field)) ENGINE=INNODB; + + +CREATE TABLE t2(id INT PRIMARY KEY, + fts_field VARCHAR(10), + FULLTEXT INDEX f(fts_field)) ENGINE=INNODB; + +INSERT INTO t1 values (1,'100'),(2,'200'),(3,'300'),(4,'400'),(5,'500'),(6,'600'), (7,'700'),(8,'800'),(9,'900'),(10,'1000'),(11,'1100'),(12,'1200'); +INSERT INTO t2 values (1,'100'),(2,'200'),(3,'300'),(4,'400'),(5,'500'),(6,'600'), (7,'700'),(8,'800'); + +UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'foo'); + +UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'foo') WHERE t1.fts_field = "100foo"; + +# Update two tables in the same statement +UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'xoo'), t2.fts_field = CONCAT(t1.fts_field, 'xoo') where t1.fts_field=CONCAT(t2.fts_field, 'foo'); + +# Following selects shows whether the correct Doc ID are updated + +# This row should present in table t1 +SELECT * FROM t1 WHERE MATCH(fts_field) against("100foofoo"); + +# Following rows should be dropped +SELECT * FROM t1 WHERE MATCH(fts_field) against("100foo"); +SELECT * FROM t1 WHERE MATCH(fts_field) against("100"); + +# This row should present in table t2 +SELECT * FROM t2 WHERE MATCH(fts_field) against("400fooxoo"); +SELECT * FROM t2 WHERE MATCH(fts_field) against("100"); + +# Follow rows should be marked as dropped +SELECT * FROM t2 WHERE MATCH(fts_field) against("200"); +SELECT * FROM t2 WHERE MATCH(fts_field) against("400"); + + +DROP TABLE t1; + +DROP TABLE t2; + + +--echo +--echo BUG#13701973/64274: MYSQL THREAD WAS SUSPENDED WHEN EXECUTE UPDATE QUERY +--echo +# FTS setup did not track which tables it had already looked at to see whether +# they need initialization. Hilarity ensued when hitting circular dependencies. + +SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; + +CREATE TABLE t1 ( + t1_id INT(10) UNSIGNED NOT NULL, + t2_id INT(10) UNSIGNED DEFAULT NULL, + PRIMARY KEY (t1_id), + FOREIGN KEY (t2_id) REFERENCES t2 (t2_id) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB; + +CREATE TABLE t2 ( + t1_id INT(10) UNSIGNED NOT NULL, + t2_id INT(10) UNSIGNED NOT NULL, + t3_id INT(10) UNSIGNED NOT NULL, + t4_id INT(10) UNSIGNED NOT NULL, + PRIMARY KEY (t2_id), + FOREIGN KEY (t1_id) REFERENCES t1 (t1_id), + FOREIGN KEY (t3_id) REFERENCES t3 (t3_id) + ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY (t4_id) REFERENCES t4 (t4_id) +) ENGINE=InnoDB; + +CREATE TABLE t3 ( + t3_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, + payload char(3), + PRIMARY KEY (t3_id) +) ENGINE=InnoDB; + +INSERT INTO t3 VALUES (1, '100'); + +CREATE TABLE t4 ( + t2_id INT(10) UNSIGNED DEFAULT NULL, + t4_id INT(10) UNSIGNED NOT NULL, + PRIMARY KEY (t4_id), + FOREIGN KEY (t2_id) REFERENCES t2 (t2_id) + ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB; + +SET FOREIGN_KEY_CHECKS=1; + +UPDATE t3 SET payload='101' WHERE t3_id=1; + +SET FOREIGN_KEY_CHECKS=0; + +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; + +SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; diff --git a/mysql-test/suite/innodb_fts/t/opt.opt b/mysql-test/suite/innodb_fts/t/opt.opt new file mode 100644 index 00000000..d7d47164 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/opt.opt @@ -0,0 +1 @@ +--query_cache_type=0 diff --git a/mysql-test/suite/innodb_fts/t/opt.test b/mysql-test/suite/innodb_fts/t/opt.test new file mode 100644 index 00000000..19dfdcad --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/opt.test @@ -0,0 +1,1086 @@ +--source include/have_innodb.inc + +# +# Tests for optimizations for InnoDB fulltext search (WL#6043) +# + +CREATE TABLE wp( + FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + title VARCHAR(255) NOT NULL DEFAULT '', + text MEDIUMTEXT NOT NULL, + dummy INTEGER, + PRIMARY KEY (FTS_DOC_ID), + UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID), + FULLTEXT KEY idx (title,text) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO wp (title, text) VALUES + ('MySQL Tutorial','DBMS stands for MySQL 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 to database comparison ...'), + ('MySQL Security','When configured properly, MySQL ...'); + +CREATE TABLE t1 (i INTEGER); +INSERT INTO t1 SELECT FTS_DOC_ID FROM wp; + +SET STATEMENT use_stat_tables=never FOR +ANALYZE TABLE t1; +SET STATEMENT use_stat_tables=never FOR +ANALYZE TABLE wp; + +--disable_ps2_protocol + +# +# Show results of MATCH expressions for reference +# +SELECT FTS_DOC_ID, title, MATCH(title, text) AGAINST ('database') AS score1, + MATCH(title, text) AGAINST ('mysql') AS score2 +FROM wp; + +# +# Test that filesort is not used if ordering on same match expression +# as where clause +# +--echo No sorting for this query +FLUSH STATUS; + +SELECT title, MATCH(title, text) AGAINST ('database') AS score +FROM wp +WHERE MATCH(title, text) AGAINST ('database') +ORDER BY score DESC; + +SHOW SESSION STATUS LIKE 'Sort%'; + +--echo No sorting for this query even if MATCH is part of an expression +FLUSH STATUS; + +SELECT title, MATCH(title, text) AGAINST ('database') AS score +FROM wp +WHERE MATCH(title, text) AGAINST ('database') > 0.1 +ORDER BY score DESC; + +SHOW SESSION STATUS LIKE 'Sort%'; + +--echo No sorting even if there are several MATCH expressions as long as the +--echo right one is used in ORDER BY +FLUSH STATUS; + +SELECT title, MATCH(title, text) AGAINST ('database') AS score1, + MATCH(title, text) AGAINST ('mysql') AS score2 +FROM wp +WHERE MATCH(title, text) AGAINST ('database') +ORDER BY score1 DESC; + +SHOW SESSION STATUS LIKE 'Sort%'; + +--echo No Sorting since FT table is first table in query +FLUSH STATUS; + +SELECT title, MATCH(title, text) AGAINST ('database') AS score +FROM wp, t1 +WHERE MATCH(title, text) AGAINST ('database') AND FTS_DOC_ID = t1.i +ORDER BY score DESC; + +SHOW SESSION STATUS LIKE 'Sort_rows%'; + +--echo Sorting since there is no WHERE clause +FLUSH STATUS; + +--sorted_result +SELECT MATCH(title, text) AGAINST ('database'), title AS score +FROM wp +ORDER BY score DESC; + +SHOW SESSION STATUS LIKE 'Sort_rows%'; + +--echo Sorting since ordering on multiple columns +FLUSH STATUS; + +SELECT title, MATCH(title, text) AGAINST ('database') AS score +FROM wp +WHERE MATCH(title, text) AGAINST ('database') +ORDER BY score DESC, FTS_DOC_ID; + +SHOW SESSION STATUS LIKE 'Sort_rows%'; + +--echo Sorting since ordering is not descending +FLUSH STATUS; + +SELECT title, MATCH(title, text) AGAINST ('database') AS score +FROM wp +WHERE MATCH(title, text) AGAINST ('database') +ORDER BY score ASC; + +SHOW SESSION STATUS LIKE 'Sort_rows%'; + +--echo Sorting because one is ordering on a different MATCH expression +FLUSH STATUS; + +SELECT title, MATCH(title, text) AGAINST ('mysql') AS score +FROM wp +WHERE MATCH(title, text) AGAINST ('database') +ORDER BY score DESC; + +SHOW SESSION STATUS LIKE 'Sort_rows%'; + +# +# Tests for ORDER BY/LIMIT optimzation +# +--echo No sorting for this query +FLUSH STATUS; + +SELECT title, MATCH(title, text) AGAINST ('database') AS score +FROM wp +ORDER BY score DESC LIMIT 2; + +SHOW SESSION STATUS LIKE 'Sort%'; + +--echo Revert to table scan and sorting for this query since not +--echo enough matching rows to satisfy LIMIT clause +FLUSH STATUS; + +SELECT title, MATCH(title, text) AGAINST ('database') AS score +FROM wp +ORDER BY score DESC LIMIT 2; + +SHOW SESSION STATUS LIKE 'Handler_read%'; +SHOW SESSION STATUS LIKE 'Sort_rows%'; + +--echo Sorting since no LIMIT clause +FLUSH STATUS; + +--sorted_result +SELECT MATCH(title, text) AGAINST ('database') AS score, title +FROM wp +ORDER BY score DESC; + +SHOW SESSION STATUS LIKE 'Sort_rows%'; + +--echo Sorting since there is a WHERE clause +FLUSH STATUS; + +SELECT title, MATCH(title, text) AGAINST ('database') AS score +FROM wp +WHERE dummy IS NULL +ORDER BY score DESC LIMIT 2; + +SHOW SESSION STATUS LIKE 'Sort_rows%'; + +--echo Sorting since ordering is not on a simple MATCH expressions +FLUSH STATUS; + +SELECT title, (MATCH(title, text) AGAINST ('database')) * 100 AS score +FROM wp +ORDER BY score DESC LIMIT 2; + +SHOW SESSION STATUS LIKE 'Sort_rows%'; + +# +# Test that there is no row accesses if all necessary information is +# available in FTS result +# +--echo No ordinary handler accesses when only accessing FTS_DOC_ID and MATCH +FLUSH STATUS; + +SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score +FROM wp +WHERE MATCH(title, text) AGAINST ('database'); + +SHOW SESSION STATUS LIKE 'Handler_read%'; + +--echo Still no handler accesses when adding FTS_DOC_ID to WHERE clause +FLUSH STATUS; + +SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score +FROM wp +WHERE MATCH(title, text) AGAINST ('database') AND FTS_DOC_ID > 2; + +SHOW SESSION STATUS LIKE 'Handler_read%'; + +--echo Still no handler accesses when ordering by MATCH expression +FLUSH STATUS; + +SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score +FROM wp +WHERE MATCH(title, text) AGAINST ('database') +ORDER BY score; + +SHOW SESSION STATUS LIKE 'Handler_read%'; + +--echo Optimization is disabled when ordering on FTS_DOC_ID +FLUSH STATUS; + +SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score +FROM wp +WHERE MATCH(title, text) AGAINST ('database') +ORDER BY 1 DESC; + +SHOW SESSION STATUS LIKE 'Handler_read%'; + +--echo Optimization also work with several MATCH expressions +FLUSH STATUS; + +SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score1, + MATCH(title, text) AGAINST ('mysql') AS score2 +FROM wp +WHERE MATCH(title, text) AGAINST ('database'); + +SHOW SESSION STATUS LIKE 'Handler_read%'; + +--echo Optimization does not apply if sorting on a different MATCH expressions +--echo from the one used to access the +FLUSH STATUS; + +SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score1, + MATCH(title, text) AGAINST ('mysql') AS score2 +FROM wp +WHERE MATCH(title, text) AGAINST ('database') +ORDER BY score2 DESC; + +SHOW SESSION STATUS LIKE 'Handler_read%'; + +FLUSH STATUS; + +--echo Optimization does not apply for GROUP BY +SET @save_mode = @@sql_mode; +SET sql_mode = (select replace(@@sql_mode,'ONLY_FULL_GROUP_BY','')); +SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('database') AS score +FROM wp +WHERE MATCH(title, text) AGAINST ('database') +GROUP BY score; + +SHOW SESSION STATUS LIKE 'Handler_read%'; +SET sql_mode = @save_mode; + +# +# Putting all three optimizations together +# +--echo No sorting and no table access with LIMIT clause and only information +--echo from FTS result +FLUSH STATUS; + +SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score +FROM wp +ORDER BY score DESC LIMIT 2; + +SHOW STATUS LIKE 'Handler_read%'; +SHOW SESSION STATUS LIKE 'Sort%'; + +# +# Count optimization +# +let $query = +SELECT COUNT(*) +FROM wp +WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE); + +--echo If count optimization applies, EXPLAIN shows +--echo "Select tables optimized away." +eval EXPLAIN $query; +FLUSH STATUS; +eval $query; +--echo Verify that there was no table access +SHOW STATUS LIKE 'Handler_read%'; + +let $query = +SELECT COUNT(title) +FROM wp +WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE); + +--echo Optimization applies also to COUNT(expr) as long as expr is not nullable +eval EXPLAIN $query; +eval $query; + +let $query = +SELECT count(*) +FROM wp, t1 +WHERE MATCH(title, text) AGAINST ('database'); + +--echo Optimization does not apply if not a single table query. +eval EXPLAIN $query; +eval $query; + +let $query = +SELECT COUNT(title) +FROM wp +WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE) > 0; + +--echo Optimization does not apply if MATCH is part of an expression +eval EXPLAIN $query; +eval $query; + +let $query = +SELECT COUNT(title) +FROM wp +WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE) > 0; + +--echo Optimization does not apply if MATCH is part of an expression +eval EXPLAIN $query; +eval $query; + +let $query = +SELECT COUNT(dummy) +FROM wp +WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE); + +--echo Optimization does not apply if COUNT expression is nullable +eval EXPLAIN $query; +eval $query; + +# +# Verify that the queries optimized for InnoDB works with QUERY EXPANSION +# + +# Query will also avoid sorting when query expansion is used +FLUSH STATUS; +--sorted_result +SELECT MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score, +title +FROM wp +WHERE MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) +ORDER BY score DESC; +SHOW SESSION STATUS LIKE 'Sort%'; + +# Check ORDER BY/LIMIT query with no WHERE clause +FLUSH STATUS; +SELECT title, + MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score +FROM wp +ORDER BY score DESC LIMIT 2; +SHOW SESSION STATUS LIKE 'Sort%'; + +# Check query where FTS result is "covering" +FLUSH STATUS; +SELECT FTS_DOC_ID docid, + MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score +FROM wp +WHERE MATCH(title, text) AGAINST ('database'); +SHOW SESSION STATUS LIKE 'Handler_read%'; + +# Check the combination of all three +FLUSH STATUS; +SELECT FTS_DOC_ID docid, + MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score +FROM wp +ORDER BY score DESC LIMIT 2; +SHOW STATUS LIKE 'Handler_read%'; +SHOW SESSION STATUS LIKE 'Sort%'; + +# Check the count optimization +let $query = +SELECT COUNT(*) +FROM wp +WHERE MATCH(title,text) AGAINST ('database' WITH QUERY EXPANSION); +eval EXPLAIN $query; +FLUSH STATUS; +eval $query; +SHOW STATUS LIKE 'Handler_read%'; + +# +# Verify that the queries optimized for InnoDB works with BOOLEAN MODE +# + +# Query will also avoid sorting when Boolean mode is used +FLUSH STATUS; +--sorted_result +SELECT MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) score, +title +FROM wp +WHERE MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) +ORDER BY score DESC; +SHOW SESSION STATUS LIKE 'Sort%'; + +# Check ORDER BY/LIMIT query with no WHERE clause +FLUSH STATUS; +--sorted_result +SELECT MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) score, +title +FROM wp +ORDER BY score DESC; +SHOW SESSION STATUS LIKE 'Sort%'; + +# Check query where FTS result is "covering" +FLUSH STATUS; +SELECT FTS_DOC_ID docid, + MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score +FROM wp +WHERE MATCH(title, text) AGAINST ('+MySQL -database'); +SHOW SESSION STATUS LIKE 'Handler_read%'; + +# Check the combination of all three +FLUSH STATUS; +SELECT FTS_DOC_ID docid, + MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score +FROM wp +ORDER BY score DESC LIMIT 1; +SHOW STATUS LIKE 'Handler_read%'; +SHOW SESSION STATUS LIKE 'Sort%'; + +# Check the count optimization +let $query = +SELECT COUNT(*) +FROM wp +WHERE MATCH(title,text) AGAINST ('+MySQL -database' IN BOOLEAN MODE); +eval EXPLAIN $query; +FLUSH STATUS; +eval $query; +SHOW STATUS LIKE 'Handler_read%'; + + +# +# Verify that the queries optimized for InnoDB works with +# BOOLEAN proximity search +# + +# Query will also avoid sorting when Boolean mode is used +FLUSH STATUS; +SELECT title, + MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score +FROM wp +WHERE MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) +ORDER BY score DESC; +SHOW SESSION STATUS LIKE 'Sort%'; + +# Check ORDER BY/LIMIT query with no WHERE clause +FLUSH STATUS; +SELECT title, + MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score +FROM wp +ORDER BY score DESC LIMIT 1; +SHOW SESSION STATUS LIKE 'Sort%'; + +# Check query where FTS result is "covering" +FLUSH STATUS; +SELECT FTS_DOC_ID docid, + MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score +FROM wp +WHERE MATCH(title, text) AGAINST ('"MySQL database"@5'); +SHOW SESSION STATUS LIKE 'Handler_read%'; + +# Check the combination of all three +FLUSH STATUS; +SELECT FTS_DOC_ID docid, + MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score +FROM wp +ORDER BY score DESC LIMIT 1; +SHOW STATUS LIKE 'Handler_read%'; +SHOW SESSION STATUS LIKE 'Sort%'; + +# Check the count optimization +let $query = +SELECT COUNT(*) +FROM wp +WHERE MATCH(title,text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE); +eval EXPLAIN $query; +FLUSH STATUS; +eval $query; +SHOW STATUS LIKE 'Handler_read%'; + +# +# Check that nothing goes wrong when combining different modes +# +SELECT title, + MATCH(title, text) AGAINST ('database') AS score +FROM wp +WHERE MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) +ORDER BY score DESC, title ASC; + +SELECT title, + MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score +FROM wp +WHERE MATCH(title, text) AGAINST ('MySQL database' WITH QUERY EXPANSION) +ORDER BY score DESC, title ASC; + +SELECT title, + MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score +FROM wp +WHERE MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) +ORDER BY score DESC, title ASC; + + +# +# Verify that the queries optimized for InnoDB still works with MyISAM +# +ALTER TABLE wp ENGINE=myisam; + +# Check avoid sorting query +FLUSH STATUS; +SELECT title, MATCH(title, text) AGAINST ('database') AS score +FROM wp +WHERE MATCH(title, text) AGAINST ('database') +ORDER BY score DESC; +SHOW SESSION STATUS LIKE 'Sort%'; + +# Check ORDER BY/LIMIT query with no WHERE clause +FLUSH STATUS; +SELECT title, MATCH(title, text) AGAINST ('database') AS score +FROM wp +ORDER BY score DESC LIMIT 2; +SHOW SESSION STATUS LIKE 'Sort%'; + +# Check query where FTS result is "covering" +FLUSH STATUS; +SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score +FROM wp +WHERE MATCH(title, text) AGAINST ('database'); +SHOW SESSION STATUS LIKE 'Handler_read%'; + +# Check the combination of all three +FLUSH STATUS; +SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score +FROM wp +ORDER BY score DESC LIMIT 2; +SHOW STATUS LIKE 'Handler_read%'; +SHOW SESSION STATUS LIKE 'Sort%'; + +# Check the count optimization +let $query = +SELECT COUNT(*) +FROM wp +WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE); +eval EXPLAIN $query; +FLUSH STATUS; +eval $query; +SHOW STATUS LIKE 'Handler_read%'; + + +DROP TABLE wp, t1; + +--enable_ps2_protocol + +# Tests for FT hints. + +CREATE TABLE t1 +( + FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + title VARCHAR(255) DEFAULT '', + text MEDIUMTEXT , + PRIMARY KEY (FTS_DOC_ID), + UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID), + FULLTEXT KEY ft_idx (title,text) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + + +INSERT INTO t1 (title, text) VALUES + ('MySQL Tutorial','DBMS stands for MySQL 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 database','In the following database to database comparison ...'), + ('MySQL Security','When configured properly, MySQL ...'), + ('InnoDB', 'InnoDB is a transaction-safe (ACID compliant) storage engine'), + ('MySQL is a database management system', 'A database is a structured collection of data...'), + ('MySQL databases are relational', 'A relational database stores data in separate tables rather than putting all the data in one big storeroom...'), + ('MySQL software is Open Source', 'Open Source means that it is possible for anyone to use and modify the software...'), + ('The MySQL Database Server is very fast, reliable, scalable, and easy to use', 'MySQL Server can run comfortably on a desktop or laptop...'), + ('MySQL Server works in client/server or embedded systems', 'The MySQL Database Software is a client/server system...'), + ('MyISAM', 'MyISAM is based on the older (and no longer available) ISAM storage engine but has many useful extensions'), + ('A large amount of contributed MySQL software is available', 'MySQL Server has a practical set of features developed in close cooperation with our users'), + (NULL,NULL); + +-- disable_result_log +ANALYZE TABLE t1; +-- enable_result_log + +--echo # No ranking + +EXPLAIN +SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE); +SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE); + +# Atm opt_sum_query does not support COUNT optimization if +# ORDER BY is present. TODO: fix it. +EXPLAIN +SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('data*' IN BOOLEAN MODE) ORDER BY title LIMIT 3; +SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('data*' IN BOOLEAN MODE) ORDER BY title LIMIT 3; + +EXPLAIN +SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE); +SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE); + +EXPLAIN +SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION); +SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION); + +EXPLAIN +SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE); +SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE); + +# check case with 'for' stopword +EXPLAIN SELECT FTS_DOC_ID FROM t1 +WHERE MATCH(title, text) AGAINST ('+for' IN BOOLEAN MODE); +SELECT FTS_DOC_ID FROM t1 +WHERE MATCH(title, text) AGAINST ('+for' IN BOOLEAN MODE); + + +--echo # No sorting by rank + +EXPLAIN SELECT FTS_DOC_ID, TITLE FROM t1 +WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) +ORDER BY title; +SELECT FTS_DOC_ID, TITLE FROM t1 +WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) +ORDER BY title; + +EXPLAIN SELECT FTS_DOC_ID FROM t1 +WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE); +SELECT FTS_DOC_ID FROM t1 +WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE); + +EXPLAIN +SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title; +SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title; + +EXPLAIN +SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title; +SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title; + +--echo # LIMIT optimization + +EXPLAIN SELECT FTS_DOC_ID, TITLE FROM t1 +WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) +LIMIT 3; +SELECT FTS_DOC_ID, TITLE FROM t1 +WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) +LIMIT 3; + +EXPLAIN SELECT FTS_DOC_ID FROM t1 +WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE) +LIMIT 3; +SELECT FTS_DOC_ID FROM t1 +WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE) +LIMIT 3; + +EXPLAIN SELECT FTS_DOC_ID FROM t1 +WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE) +ORDER BY title +LIMIT 3; +SELECT FTS_DOC_ID FROM t1 +WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE) +ORDER BY title +LIMIT 3; + + +EXPLAIN +SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title LIMIT 1; +SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title LIMIT 1; + +EXPLAIN +SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title LIMIT 1; +SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title LIMIT 1; + +EXPLAIN +SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank +FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) +ORDER BY rank, FTS_DOC_ID +LIMIT 3; +SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank +FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) +ORDER BY rank, FTS_DOC_ID +LIMIT 3; + +EXPLAIN +SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank +FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) +ORDER BY rank DESC, FTS_DOC_ID ASC +LIMIT 3; +SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank +FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) +ORDER BY rank DESC, FTS_DOC_ID ASC +LIMIT 3; + +EXPLAIN SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank +FROM t1 +ORDER BY rank DESC +LIMIT 2; +SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank +FROM t1 +ORDER BY rank DESC +LIMIT 2; + +EXPLAIN SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank +FROM t1 +ORDER BY rank DESC +LIMIT 3; +SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank +FROM t1 +ORDER BY rank DESC +LIMIT 3; + +EXPLAIN SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank +FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) +ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) DESC, +FTS_DOC_ID ASC; +SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank +FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) +ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) DESC, +FTS_DOC_ID ASC; + +EXPLAIN SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank +FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) and FTS_DOC_ID > 1 +ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) DESC +LIMIT 2; +SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank +FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) and FTS_DOC_ID > 1 +ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) DESC +LIMIT 2; + + +EXPLAIN +SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank +FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) +ORDER BY rank +LIMIT 1; +SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank +FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) +ORDER BY rank +LIMIT 1; + +EXPLAIN +SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank +FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) +ORDER BY rank DESC +LIMIT 1; +SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank +FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) +ORDER BY rank DESC +LIMIT 1; + + +EXPLAIN +SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank +FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) +ORDER BY MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) +LIMIT 1; +SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank +FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) +ORDER BY MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) DESC +LIMIT 1; + +--echo # WHERE optimization on MATCH > 'some_rank' + +EXPLAIN SELECT FTS_DOC_ID FROM t1 +WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) > 0.1; + +SELECT FTS_DOC_ID FROM t1 +WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) > 0.1; + + +--echo # additional test for correct behaviour + +EXPLAIN SELECT * FROM t1 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC LIMIT 10; + +SELECT FTS_DOC_ID FROM t1 +WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) AND +MATCH (title, text) AGAINST ('mysql' IN NATURAL LANGUAGE MODE) +LIMIT 6; + +--echo # test OR condition + +SELECT FTS_DOC_ID +FROM t1 +WHERE MATCH(title, text) AGAINST ('database') + OR MATCH(title, text) AGAINST ('mysql') +ORDER BY MATCH(title, text) AGAINST ('database') DESC, FTS_DOC_ID ASC; + +EXPLAIN SELECT FTS_DOC_ID +FROM t1 +WHERE MATCH(title, text) AGAINST ('database') + OR MATCH(title, text) AGAINST ('mysql') +ORDER BY MATCH(title, text) AGAINST ('database') DESC, FTS_DOC_ID ASC; + +--echo # MATCH and GROUP BY, DISTINCT + +SET sql_mode = (select replace(@@sql_mode,'ONLY_FULL_GROUP_BY','')); + +EXPLAIN SELECT FTS_DOC_ID FROM t1 +WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) +GROUP BY FTS_DOC_ID +ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC +LIMIT 3; +SELECT FTS_DOC_ID FROM t1 +WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) +GROUP BY FTS_DOC_ID +ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC +LIMIT 3; + +EXPLAIN SELECT FTS_DOC_ID FROM t1 +WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) +GROUP BY title +ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC +LIMIT 3; + +SELECT FTS_DOC_ID FROM t1 +WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) +GROUP BY title +ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC +LIMIT 3; + +EXPLAIN SELECT MAX(FTS_DOC_ID) FROM t1 +WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) +ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC +LIMIT 3; +SELECT MAX(FTS_DOC_ID) FROM t1 +WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) +ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC +LIMIT 3; + +EXPLAIN SELECT DISTINCT(title) FROM t1 +WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) +ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC +LIMIT 3; +SELECT DISTINCT(title) FROM t1 +WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) +ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC +LIMIT 3; + +EXPLAIN SELECT DISTINCT(FTS_DOC_ID) FROM t1 +WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) +ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC +LIMIT 3; +SELECT DISTINCT(FTS_DOC_ID) FROM t1 +WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) +ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC +LIMIT 3; + +SET sql_mode = @save_mode; + +--echo # FTS index access + +SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank +FROM t1 +ORDER BY rank DESC +LIMIT 2; +EXPLAIN SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank +FROM t1 +ORDER BY rank DESC +LIMIT 2; + +SELECT a.FTS_DOC_ID, b.FTS_DOC_ID +FROM t1 a, t1 b +WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and + MATCH(b.title, b.text) AGAINST ('+mysql' IN BOOLEAN MODE) and + a.FTS_DOC_ID = b.FTS_DOC_ID; +EXPLAIN SELECT a.FTS_DOC_ID, b.FTS_DOC_ID +FROM t1 a, t1 b +WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and + MATCH(b.title, b.text) AGAINST ('+mysql' IN BOOLEAN MODE) and + a.FTS_DOC_ID = b.FTS_DOC_ID; + +SELECT a.FTS_DOC_ID, MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE), + b.FTS_DOC_ID, MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE) +FROM t1 a, t1 b +WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and + MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE); + +EXPLAIN SELECT a.FTS_DOC_ID, MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE), + b.FTS_DOC_ID, MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE) +FROM t1 a, t1 b +WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and + MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE); + +# Index only access by non-FTS index + +EXPLAIN SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ("data*" IN BOOLEAN MODE) * 100; +SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ("data*" IN BOOLEAN MODE) * 100 +FROM t1 WHERE MATCH(title, text) AGAINST ("data*" IN BOOLEAN MODE) * 100; +# Run query returning null record +SELECT * FROM t1 WHERE title IS NULL AND text IS NULL; + +# More testing of index only access by non-FTS index + +CREATE TABLE t2 SELECT FTS_DOC_ID as doc_id, title, text FROM t1; +ALTER TABLE t2 ADD PRIMARY KEY (doc_id); +ALTER TABLE t2 ADD FULLTEXT KEY ft_idx (title,text); + +-- disable_result_log +ANALYZE TABLE t2; +-- enable_result_log + +# No index access +EXPLAIN SELECT DOC_ID FROM t2 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100; +SELECT DOC_ID FROM t2 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100; +# Index access +EXPLAIN SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100; +SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100; + +DROP TABLE t1, t2; + +--echo "Check hints with uft8 charset for 2 cases" +set names utf8; +EVAL CREATE TABLE t1 ( + FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + text TEXT + ) CHARACTER SET = utf8, ENGINE=InnoDB; + +INSERT INTO t1 (title, text) VALUES +('Я могу есть стекло', 'оно мне не вредит'), +('Мога да ям стъкло', 'то не ми вреди'), +('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'), +('Příliš žluťoučký kůň', 'úpěl ďábelské kódy'), +('Sævör grét', 'áðan því úlpan var ónýt'), +('うゐのおくやま','けふこえて'), +('いろはにほへど ちりぬる','あさきゆめみじ ゑひもせず'); +CREATE FULLTEXT INDEX idx on t1 (title, text); + +--echo # No ranking + +EXPLAIN +SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('вредит' IN NATURAL LANGUAGE MODE); +SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('вредит' IN NATURAL LANGUAGE MODE); + +EXPLAIN +SELECT * FROM t1 WHERE MATCH(title, text) AGAINST ("оно" WITH QUERY EXPANSION); +SELECT * FROM t1 WHERE MATCH(title, text) AGAINST ("оно" WITH QUERY EXPANSION); +--echo # No sorting by rank +EXPLAIN SELECT FTS_DOC_ID FROM t1 +WHERE MATCH(title, text) AGAINST ('+(Мога τίποτα)' IN BOOLEAN MODE); +SELECT FTS_DOC_ID FROM t1 +WHERE MATCH(title, text) AGAINST ('+(Мога τίποτα)' IN BOOLEAN MODE); +DROP TABLE t1; + + +--echo # +--echo # Bug #18924341 CRASH IN TEST_IF_SKIP_SORT_ORDER, GROUP BY MATCH AGAINST DESC +--echo # + +CREATE TABLE t1 (f1 CHAR(1), FULLTEXT KEY (f1)); +SELECT 1 FROM t1 NATURAL JOIN t1 a GROUP BY MATCH(t1.f1) AGAINST ("1") DESC; +DROP TABLE t1; + + +--echo # +--echo # Bug#20261601 ASSERTION FAILED: !FIRST_QEP_TAB->TABLE()->NO_KEYREAD +--echo # + +CREATE TABLE t1(a INT PRIMARY KEY); +INSERT INTO t1 VALUES(1),(2); +--error ER_FT_MATCHING_KEY_NOT_FOUND +SELECT (SELECT MATCH(`a`)AGAINST('1') FROM t1) FROM t1; +SELECT 1, a IN (SELECT a FROM t1) FROM t1; +DROP TABLE t1; + +--echo # +--echo # Bug#20442572 ASSERTION `!FIRST_QEP_TAB->TABLE()->NO_KEYREAD' FAILED. +--echo # Bug#75688 Assertion `!first_qep_tab->table()->no_keyread' failed. +--echo # + +CREATE TABLE t1(a INT,b POINT NOT NULL,KEY(a)); + +HANDLER t1 OPEN; +#--error ER_FT_MATCHING_KEY_NOT_FOUND +select * from t1 where MATCH a,b AGAINST('"Now sUPPort"' IN BOOLEAN MODE); +prepare stmt1 from "truncate t1"; +SELECT a IN(SELECT a FROM t1)FROM t1; + +deallocate prepare stmt1; +DROP TABLE t1; + +--echo # +--echo # Bug #20685427 INVALID WRITE OF FREED MEMORY IN ITEM_FUNC_MATCH::CLEANUP +--echo # + +CREATE TABLE t1(a TEXT CHARSET LATIN1, FULLTEXT KEY(a)) ENGINE=INNODB; +--error ER_FT_MATCHING_KEY_NOT_FOUND +SELECT MATCH(a) AGAINST ('') FROM (SELECT a FROM t1 LIMIT 1) q; +DROP TABLE t1; + +--echo # +--echo # Bug#21140067 EXPLAIN .. MATCH AGAINST: ASSERTION FAILED: TO <= END +--echo # + +CREATE TABLE t1(f1 CHAR(1) CHARSET latin1, FULLTEXT(f1)) ENGINE=INNODB; +EXPLAIN SELECT 1 FROM t1 WHERE 1.238585e+308 <= MATCH(f1) AGAINST ('1' IN BOOLEAN MODE); + +EXPLAIN FORMAT = JSON SELECT 1 FROM t1 WHERE 1.238585e+308 <= MATCH(f1) AGAINST ('1' IN BOOLEAN MODE); + +DROP TABLE t1; + +--echo # +--echo # Bug#21140088 MATCH AGAINST: ASSERTION FAILED: !TABLE || (!TABLE->READ_SET || BITMAP_IS_SET +--echo # + +SET sql_mode=''; +CREATE TABLE t1(a INT) ENGINE=INNODB; +CREATE TABLE t2(b TEXT CHARSET LATIN1, FULLTEXT(b), PRIMARY KEY(b(10))) ENGINE=INNODB; +INSERT INTO t2 VALUES ('a'),('b'); +--error ER_WRONG_ARGUMENTS +SELECT NOT EXISTS (SELECT MATCH(b) AGAINST ('1') FROM t1) FROM t2 GROUP BY "a"; +DROP TABLE t1, t2; + +CREATE TABLE t1(a INT) ENGINE=MyISAM; +CREATE TABLE t2(b TEXT CHARSET LATIN1, FULLTEXT(b), PRIMARY KEY(b(10))) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('a'),('b'); +#--error ER_WRONG_ARGUMENTS +SELECT NOT EXISTS (SELECT MATCH(b) AGAINST ('1' in BOOLEAN MODE) FROM t1) FROM t2 GROUP BY "a"; +DROP TABLE t1, t2; + +SET sql_mode=default; + +--echo # +--echo # Bug#21140039 ASSERTION FAILED: !FIRST_QEP_TAB->TABLE()->NO_KEYREAD MATCH AGAINST..... +--echo # + +CREATE TABLE t1 +( + a INT, + b INT, + c CHAR(1) CHARSET latin1, + PRIMARY KEY (b,a), + FULLTEXT KEY (c) +) ENGINE=INNODB; +SELECT "a" NOT IN(SELECT b FROM t1 WHERE MATCH(c) AGAINST ('a' IN BOOLEAN MODE)); +DROP TABLE t1; + +--echo # +--echo # Bug#21300774 ASSERT `!INIT_FTFUNCS(THD, SELECT_LEX)` IN JOIN::RESET AT SQL/SQL_SELECT.CC:874 +--echo # + +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (2); +CREATE TABLE t2 (ft TEXT, FULLTEXT KEY ft(ft)); +INSERT INTO t2 VALUES ('abc'); +INSERT INTO t2 VALUES ('def'); + +--error ER_SUBQUERY_NO_1_ROW +UPDATE t1 SET f1 = +(SELECT t1.f1 FROM t2 WHERE NOT TRUE AND + MATCH (ft) AGAINST ((SELECT 'xyz' FROM t2))); + +DROP TABLE t1, t2; + +--echo # +--echo # Bug#22679209: FULL-TEXT QUERIES WITH ADDITIONAL SECONDARY INDEX +--echo # GIVES NULL OR ZERO ROWS +--echo # + +CREATE TABLE t1 ( +f1 INTEGER, +title varchar(255), +body mediumtext, +KEY f1 (f1), +FULLTEXT KEY title (title), +FULLTEXT KEY body (body) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES +(1, 'Insert into table', 'insert into table select from'), +(1, 'Delete from table', 'insert into table select from'), +(1, 'Update', 'perform update'), +(2, 'Insert into table', 'insert into table select from'), +( 2, 'Delete from table', 'some body text here'), +( 2, 'Update', 'perform update'), +( 3, 'Insert into table', 'insert into table select from'), +( 3, 'Delete from table', 'some body text here'); + +SELECT f1 FROM t1 WHERE f1=1 AND +(MATCH (title) AGAINST ('table' IN BOOLEAN MODE) OR +MATCH (body) AGAINST ('table' IN BOOLEAN MODE)); + +DROP TABLE t1; + +--echo # End of test for Bug#22679209 diff --git a/mysql-test/suite/innodb_fts/t/phrase.opt b/mysql-test/suite/innodb_fts/t/phrase.opt new file mode 100644 index 00000000..7724f976 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/phrase.opt @@ -0,0 +1,2 @@ +--innodb-ft-index-cache +--innodb-ft-index-table diff --git a/mysql-test/suite/innodb_fts/t/phrase.test b/mysql-test/suite/innodb_fts/t/phrase.test new file mode 100644 index 00000000..fb0d29f3 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/phrase.test @@ -0,0 +1,39 @@ +-- source include/have_innodb.inc + +# +# BUG#20465273 - FULLTEXT SEARCH BEHAVIOUR WITH MYISAM VS. INNODB (WRONG RESULT WITH INNODB) +# + +CREATE TABLE articles ( + id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + title VARCHAR(200), + body TEXT, + FULLTEXT (title,body) +) ENGINE=InnoDB; + +INSERT INTO articles (title,body) VALUES + (NULL, 'mysql good database'), + (NULL, ' mysql good database'), + ('', 'mysql good database'), + ('', ' mysql good database'), + (' ', 'mysql good database'), + ('mysql', 'good database'), + ('mysql ', 'good database'), + ('mysql', ' good database'), + ('mysql good database', ''), + ('mysql good database', NULL); + + +SET GLOBAL innodb_ft_aux_table="test/articles"; +SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +SET GLOBAL innodb_ft_aux_table=default; + +SELECT * FROM articles; + +SELECT * FROM articles WHERE MATCH(title, body) + AGAINST('"mysql good database"' IN BOOLEAN MODE); + +SELECT * FROM articles WHERE MATCH(title, body) + AGAINST('("mysql good database")' IN BOOLEAN MODE); + +DROP TABLE articles; diff --git a/mysql-test/suite/innodb_fts/t/result_cache_limit.test b/mysql-test/suite/innodb_fts/t/result_cache_limit.test new file mode 100644 index 00000000..b19907a5 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/result_cache_limit.test @@ -0,0 +1,52 @@ +# 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 @save_limit=@@GLOBAL.innodb_ft_result_cache_limit; +SET @save_dbug=@@debug_dbug; +SET 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 debug_dbug=@save_dbug; + +DROP TABLE t1; +SET GLOBAL innodb_ft_result_cache_limit=@save_limit; diff --git a/mysql-test/suite/innodb_fts/t/savepoint.test b/mysql-test/suite/innodb_fts/t/savepoint.test new file mode 100644 index 00000000..09ccb383 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/savepoint.test @@ -0,0 +1,475 @@ +# This is the basic function tests for innodb FTS savepoint + +-- source include/have_innodb.inc + + +CREATE TABLE articles ( + id INT UNSIGNED NOT NULL PRIMARY KEY, + title VARCHAR(200), + FULLTEXT (title) + ) ENGINE= InnoDB; + +# Test Part 1: ROLLBACK TO SAVEPOINT +# Test rollback to savepoint 1(S1,RB1) +TRUNCATE TABLE articles; + +INSERT INTO articles(id, title) VALUES(1, 'mysql'); + +BEGIN; + +INSERT INTO articles(id, title) VALUES(2, 'mysql'); + +SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(3, 'mysql'); + +ROLLBACK TO SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(4, 'mysql'); + +COMMIT; + +INSERT INTO articles(id, title) VALUES(5, 'mysql'); + +SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql'); + +# Test rollback to savepoint 2(S1,RB1,S2,RB2) +TRUNCATE TABLE articles; + +INSERT INTO articles(id, title) VALUES(1, 'mysql'); + +BEGIN; + +INSERT INTO articles(id, title) VALUES(2, 'mysql'); + +SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(3, 'mysql'); + +ROLLBACK TO SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(4, 'mysql'); + +SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(5, 'mysql'); + +ROLLBACK TO SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(6, 'mysql'); + +COMMIT; + +INSERT INTO articles(id, title) VALUES(7, 'mysql'); + +SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql'); + +# Test rollback to savepoint 3(S1,S2,RB1) +TRUNCATE TABLE articles; + +INSERT INTO articles(id, title) VALUES(1, 'mysql'); + +BEGIN; + +INSERT INTO articles(id, title) VALUES(2, 'mysql'); + +SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(3, 'mysql'); + +SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(4, 'mysql'); + +ROLLBACK TO SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(5, 'mysql'); + +COMMIT; + +INSERT INTO articles(id, title) VALUES(6, 'mysql'); + +SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql'); + +# Test rollback to savepoint 4(S1,S2,RB2,RB1) +TRUNCATE TABLE articles; + +INSERT INTO articles(id, title) VALUES(1, 'mysql'); + +BEGIN; + +INSERT INTO articles(id, title) VALUES(2, 'mysql'); + +SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(3, 'mysql'); + +SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(4, 'mysql'); + +ROLLBACK TO SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(5, 'mysql'); + +ROLLBACK TO SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(6, 'mysql'); + +COMMIT; + +INSERT INTO articles(id, title) VALUES(7, 'mysql'); + +SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql'); + +# Test Part 2: RELEASE SAVEPOINT +# Test release savepoint 1(S1,RL1) +TRUNCATE TABLE articles; + +INSERT INTO articles(id, title) VALUES(1, 'mysql'); + +BEGIN; + +INSERT INTO articles(id, title) VALUES(2, 'mysql'); + +SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(3, 'mysql'); + +RELEASE SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(4, 'mysql'); + +COMMIT; + +INSERT INTO articles(id, title) VALUES(5, 'mysql'); + +SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql'); + +# Test release savepoint 2(S1,RL1,S2,RL2) +TRUNCATE TABLE articles; + +INSERT INTO articles(id, title) VALUES(1, 'mysql'); + +BEGIN; + +INSERT INTO articles(id, title) VALUES(2, 'mysql'); + +SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(3, 'mysql'); + +RELEASE SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(4, 'mysql'); + +SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(5, 'mysql'); + +RELEASE SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(6, 'mysql'); + +COMMIT; + +INSERT INTO articles(id, title) VALUES(7, 'mysql'); + +SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql'); + +# Test release savepoint 3(S1,S2,RL1) +TRUNCATE TABLE articles; + +INSERT INTO articles(id, title) VALUES(1, 'mysql'); + +BEGIN; + +INSERT INTO articles(id, title) VALUES(2, 'mysql'); + +SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(3, 'mysql'); + +SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(4, 'mysql'); + +RELEASE SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(5, 'mysql'); + +COMMIT; + +INSERT INTO articles(id, title) VALUES(6, 'mysql'); + +SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql'); + +# Test release savepoint 4(S1,S2,RL2,RL1) +TRUNCATE TABLE articles; + +INSERT INTO articles(id, title) VALUES(1, 'mysql'); + +BEGIN; + +INSERT INTO articles(id, title) VALUES(2, 'mysql'); + +SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(3, 'mysql'); + +SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(4, 'mysql'); + +RELEASE SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(5, 'mysql'); + +RELEASE SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(6, 'mysql'); + +COMMIT; + +INSERT INTO articles(id, title) VALUES(7, 'mysql'); + +SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql'); + +# Test Part 3: RELEASE & ROLLBACK TO SAVEPOINT +# Test release & rollback to savepoint 1(S1,RB1,S2,RL2) +TRUNCATE TABLE articles; + +INSERT INTO articles(id, title) VALUES(1, 'mysql'); + +BEGIN; + +INSERT INTO articles(id, title) VALUES(2, 'mysql'); + +SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(3, 'mysql'); + +ROLLBACK TO SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(4, 'mysql'); + +SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(5, 'mysql'); + +RELEASE SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(6, 'mysql'); + +COMMIT; + +INSERT INTO articles(id, title) VALUES(7, 'mysql'); + +SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql'); + +# Test release & rollback to savepoint 2(S1,RL1,S2,RB2) +TRUNCATE TABLE articles; + +INSERT INTO articles(id, title) VALUES(1, 'mysql'); + +BEGIN; + +INSERT INTO articles(id, title) VALUES(2, 'mysql'); + +SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(3, 'mysql'); + +RELEASE SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(4, 'mysql'); + +SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(5, 'mysql'); + +ROLLBACK TO SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(6, 'mysql'); + +COMMIT; + +INSERT INTO articles(id, title) VALUES(7, 'mysql'); + +SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql'); + +# Test release & rollback to savepoint 3(S1,S2,RL2,RB1) +TRUNCATE TABLE articles; + +INSERT INTO articles(id, title) VALUES(1, 'mysql'); + +BEGIN; + +INSERT INTO articles(id, title) VALUES(2, 'mysql'); + +SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(3, 'mysql'); + +SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(4, 'mysql'); + +RELEASE SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(5, 'mysql'); + +ROLLBACK TO SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(6, 'mysql'); + +COMMIT; + +INSERT INTO articles(id, title) VALUES(7, 'mysql'); + +SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql'); + +# Test release & rollback to savepoint 4(S1,S2,RB2,RL1) +TRUNCATE TABLE articles; + +INSERT INTO articles(id, title) VALUES(1, 'mysql'); + +BEGIN; + +INSERT INTO articles(id, title) VALUES(2, 'mysql'); + +SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(3, 'mysql'); + +SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(4, 'mysql'); + +ROLLBACK TO SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(5, 'mysql'); + +RELEASE SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(6, 'mysql'); + +COMMIT; + +INSERT INTO articles(id, title) VALUES(7, 'mysql'); + +SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql'); + +# Test Part 4: ROLLBACK & SAVEPOINT +# Test rollback 1 +TRUNCATE TABLE articles; + +INSERT INTO articles(id, title) VALUES(1, 'mysql'); + +BEGIN; + +INSERT INTO articles(id, title) VALUES(2, 'mysql'); + +ROLLBACK; + +INSERT INTO articles(id, title) VALUES(3, 'mysql'); + +SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql'); + +# Test rollback 2(S1) +TRUNCATE TABLE articles; + +INSERT INTO articles(id, title) VALUES(1, 'mysql'); + +BEGIN; + +INSERT INTO articles(id, title) VALUES(2, 'mysql'); + +SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(3, 'mysql'); + +ROLLBACK; + +INSERT INTO articles(id, title) VALUES(4, 'mysql'); + +SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql'); + +# Test rollback 3(S1,RL1) +TRUNCATE TABLE articles; + +INSERT INTO articles(id, title) VALUES(1, 'mysql'); + +BEGIN; + +INSERT INTO articles(id, title) VALUES(2, 'mysql'); + +SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(3, 'mysql'); + +RELEASE SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(4, 'mysql'); + +ROLLBACK; + +INSERT INTO articles(id, title) VALUES(5, 'mysql'); + +SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql'); + +# Test rollback 4(S1,RB1) +TRUNCATE TABLE articles; + +INSERT INTO articles(id, title) VALUES(1, 'mysql'); + +BEGIN; + +INSERT INTO articles(id, title) VALUES(2, 'mysql'); + +SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(3, 'mysql'); + +ROLLBACK TO SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(4, 'mysql'); + +ROLLBACK; + +INSERT INTO articles(id, title) VALUES(5, 'mysql'); + +SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql'); + +# Test rollback 5(S1,S2,RB2,RL1) +TRUNCATE TABLE articles; + +INSERT INTO articles(id, title) VALUES(1, 'mysql'); + +BEGIN; + +INSERT INTO articles(id, title) VALUES(2, 'mysql'); + +SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(3, 'mysql'); + +SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(4, 'mysql'); + +ROLLBACK TO SAVEPOINT sp2; + +INSERT INTO articles(id, title) VALUES(5, 'mysql'); + +RELEASE SAVEPOINT sp1; + +INSERT INTO articles(id, title) VALUES(6, 'mysql'); + +ROLLBACK; + +INSERT INTO articles(id, title) VALUES(7, 'mysql'); + +SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql'); + +DROP TABLE articles; diff --git a/mysql-test/suite/innodb_fts/t/subexpr.test b/mysql-test/suite/innodb_fts/t/subexpr.test new file mode 100644 index 00000000..63294066 --- /dev/null +++ b/mysql-test/suite/innodb_fts/t/subexpr.test @@ -0,0 +1,58 @@ +--source include/have_innodb.inc + +--echo # +--echo # Bug #20028323 INNODB FULLTEXT BOOLEAN SEARCH INCORRECTLY HANDLES +--echo # PARENTHESES +--echo # + +CREATE TABLE t1 ( + f1 INT NOT NULL AUTO_INCREMENT, + f2 TEXT NOT NULL, + PRIMARY KEY (f1), + FULLTEXT (f2) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +INSERT INTO t1 (f2) VALUES +('Pumpkin soup with cheese bread'), +('Yellow chicken curry'), +('Fresh green vegetables with garlic'); + +SELECT * FROM t1 WHERE MATCH(f2) AGAINST('+pumpkin' IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(f2) AGAINST('+cheese' IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(f2) AGAINST('+(pumpkin cheese)' IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(f2) + AGAINST('+pumpkin +(souffle)' IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(f2) + AGAINST('+pumpkin +(souffle tart)' IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(f2) + AGAINST('+pumpkin +(>souffle <tart)' IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(f2) + AGAINST('+pumpkin +(souffle tart)' IN BOOLEAN MODE); + +INSERT INTO t1 (f2) VALUES +('This row contains only souffle'), +('This row contains only tart'), +('This row contains only pumpkin'), +('This row contains only cheese'), +('This row contains pumpkin and souffle'), +('This row contains pumpkin and tart'), +('This row contains pumpkin and cheese'), +('This row contains both souffle and tart'), +('This row contains both souffle and cheese'), +('This row contains both tart and cheese'), +('This row contains all three souffle, pumpkin and tart'), +('This row contains all four cheese, souffle, pumpkin and tart'); + +SELECT * FROM t1 WHERE MATCH(f2) AGAINST('+pumpkin' IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(f2) AGAINST('+cheese' IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(f2) AGAINST('+(pumpkin cheese)' IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(f2) + AGAINST('+pumpkin +(souffle)' IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(f2) + AGAINST('+pumpkin +(souffle tart)' IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(f2) + AGAINST('+pumpkin +(>souffle <tart)' IN BOOLEAN MODE); +SELECT * FROM t1 WHERE MATCH(f2) + AGAINST('+pumpkin +(souffle tart)' IN BOOLEAN MODE); + +DROP TABLE t1; |