318 lines
11 KiB
Plaintext
318 lines
11 KiB
Plaintext
--source include/have_simple_parser.inc
|
||
--source include/no_valgrind_without_big.inc
|
||
|
||
#-----------------------------------------------------------------------------
|
||
# wl6943 : Support external parser
|
||
# Adding FTS check with "simple_praser" (external parser plugin)
|
||
# - Table with external parser + default parser
|
||
# - FTS index with single and two columns
|
||
# - external parser index using alter table
|
||
# - check with transactional statement
|
||
# - Check utf8 charset table
|
||
# - alter table commands Add/drop fulltext index and column
|
||
# - NULL values in table
|
||
# - FTS query with join condition
|
||
# - Invalid query
|
||
#-----------------------------------------------------------------------------
|
||
|
||
# Install fts parser plugin
|
||
--replace_regex /\.dll/.so/
|
||
eval INSTALL PLUGIN simple_parser SONAME '$SIMPLE_PARSER';
|
||
|
||
|
||
-- echo # Test Part 2: Create Index Test(CREATE TABLE WITH FULLTEXT INDEX)
|
||
CREATE TABLE articles (
|
||
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
||
title VARCHAR(200),
|
||
body TEXT,
|
||
body1 TEXT,
|
||
body2 TEXT,
|
||
body3 TEXT,
|
||
FULLTEXT (title, body) WITH PARSER simple_parser,
|
||
FULLTEXT (title, body1) WITH PARSER simple_parser,
|
||
FULLTEXT (body3)
|
||
) ENGINE=InnoDB;
|
||
|
||
INSERT INTO articles (title, body,body1,body2,body3) VALUES
|
||
('MySQL Tutorial','DBMS stands for MySQL DataBase ...','row1col4','row1col5','row1col5'),
|
||
('How To Use MySQL Well','After you went through a ...','row2col4','row2col5','row2col5'),
|
||
('Optimizing MySQL','In this tutorial we will show ...','row3col4','row3col5','row3col5 for'),
|
||
('1001 MySQL Tricks','How to use full-text search engine','row4col4','row4col5','row4col5'),
|
||
('Go MySQL Tricks','How to use full text search engine','row5col4 this','row5col5','row5col5');
|
||
|
||
ALTER TABLE articles ADD FULLTEXT INDEX (body1,body2) WITH PARSER simple_parser;
|
||
ALTER TABLE articles ADD FULLTEXT INDEX (body2,body3);
|
||
ALTER TABLE articles ADD FULLTEXT INDEX (body) WITH PARSER simple_parser;
|
||
|
||
ANALYZE TABLE articles;
|
||
|
||
--replace_regex /\.dll/.so/
|
||
SELECT
|
||
PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE,
|
||
PLUGIN_LIBRARY
|
||
FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%simple_parser%';
|
||
|
||
|
||
INSERT INTO articles (title, body,body1,body2,body3) VALUES (NULL,NULL,NULL,NULL,NULL);
|
||
|
||
# Simple term search
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('mysql');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('dbms' WITH QUERY EXPANSION);
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body1) AGAINST('row1col4');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('row5col4' WITH QUERY EXPANSION);
|
||
SELECT * FROM articles WHERE
|
||
MATCH(body1, body2) AGAINST('row3col4');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(body) AGAINST('well' WITH QUERY EXPANSION);
|
||
|
||
# Test stopword and word len less than fts_min_token_size
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('will go');
|
||
#stopword
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('this');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('for');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(body1, body2) AGAINST('this');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(body) AGAINST('this');
|
||
|
||
|
||
-- echo # Test plugin parser tokenizer difference
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('full-text');
|
||
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('full text');
|
||
|
||
# No result here, we get '"mysql' 'database"' by simple parser
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE);
|
||
|
||
|
||
# Phrase search
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE);
|
||
|
||
|
||
# use transaction
|
||
START TRANSACTION;
|
||
INSERT INTO articles (title, body,body1,body2,body3) VALUES
|
||
('latest Tutorial','transactional properties tests ...','row6col4','row6col5','row6col5'),
|
||
('certification guide','all mysql exams tests ...','row7col4','row7col5','row7col5');
|
||
# FTS do not parse those uncommitted rows, only one row should show up
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('mysql');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('Tutorial');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('guide');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('tests');
|
||
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body1) AGAINST('row6col4');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(body1,body2) AGAINST('row7col5');
|
||
# uncommitted records can be seen with non fts queries
|
||
SELECT * FROM articles WHERE id > 5;
|
||
|
||
ROLLBACK;
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('mysql');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('Tutorial');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('guide');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('tests');
|
||
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body1) AGAINST('row6col4');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(body1,body2) AGAINST('row7col5');
|
||
|
||
START TRANSACTION;
|
||
INSERT INTO articles (title, body,body1,body2,body3) VALUES
|
||
('latest Tutorial','transactional properties tests ...','row6col4','row6col5','row6col5');
|
||
COMMIT;
|
||
# FTS only parse committed rows, now rows should show up
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('mysql');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('Tutorial');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('guide');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body) AGAINST('tests');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(body) AGAINST('tests');
|
||
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body1) AGAINST('row6col4');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(body1,body2) AGAINST('row7col5');
|
||
|
||
# Alter table Add/drop column
|
||
SELECT COUNT(*) FROM articles;
|
||
ALTER TABLE articles ADD COLUMN col_test VARCHAR(100) DEFAULT 'check add drop column';
|
||
SELECT COUNT(*) FROM articles WHERE col_test = 'check add drop column';
|
||
ALTER TABLE articles ADD FULLTEXT INDEX `idx_add` (col_test) WITH PARSER simple_parser;
|
||
INSERT INTO articles (title, body,body1,body2,body3,col_test) VALUES
|
||
('value','value','value','value','value','value');
|
||
SELECT COUNT(*) AS score FROM articles WHERE
|
||
MATCH(col_test) AGAINST('check');
|
||
SELECT COUNT(*) AS score FROM articles WHERE
|
||
MATCH(col_test) AGAINST('value');
|
||
|
||
ALTER TABLE articles DROP COLUMN col_test;
|
||
|
||
SELECT * FROM articles WHERE
|
||
MATCH(title, body1) AGAINST('row6col4');
|
||
SELECT * FROM articles WHERE
|
||
MATCH(body1,body2) AGAINST('row7col5');
|
||
|
||
DROP TABLE articles;
|
||
# Get warning here
|
||
|
||
|
||
#set names utf8;
|
||
SET NAMES UTF8;
|
||
|
||
|
||
--disable_warnings
|
||
DROP TABLE IF EXISTS t1;
|
||
--enable_warnings
|
||
|
||
# Create FTS table
|
||
EVAL CREATE TABLE t1 (
|
||
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
||
a VARCHAR(200),
|
||
b TEXT,
|
||
FULLTEXT (b) WITH PARSER simple_parser
|
||
) CHARACTER SET = UTF8,ENGINE = InnoDB;
|
||
|
||
|
||
# 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 ...'),
|
||
('Я могу есть стекло', 'оно мне Mне вредит'),
|
||
('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'),
|
||
('Sævör grét', 'áðan því úlpan var ónýt');
|
||
|
||
CREATE FULLTEXT INDEX idx on t1 (a,b) WITH PARSER simple_parser;
|
||
INSERT INTO t1 (a,b) VALUES
|
||
('adding record using session 1','for verifying multiple concurrent transactions'),
|
||
('Мога да ям стъкло', 'то Mне ми вреди');
|
||
|
||
# no record expected - records not seen in boolean for simple_parser
|
||
SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
|
||
# 2 record expected
|
||
SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('Mне');
|
||
# 1 record expected
|
||
SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('могу');
|
||
SELECT * FROM t1 WHERE MATCH (b) AGAINST ('áðan');
|
||
# 3 record expected
|
||
SELECT * FROM t1 WHERE MATCH (b) AGAINST ('áðan') OR MATCH (a,b) AGAINST ('Mне могу');
|
||
# 1 record expected
|
||
SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стъкло');
|
||
# 1 record expected
|
||
SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"dbms stands"@05' IN BOOLEAN MODE);
|
||
# no records for stopword
|
||
SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('for');
|
||
|
||
DROP TABLE t1;
|
||
|
||
# Set fts_min_token_size, max_token_size values
|
||
# Write file to make mysql-test-run.pl start up the server again
|
||
SELECT @@innodb_ft_min_token_size,@@innodb_ft_max_token_size;
|
||
|
||
EVAL CREATE TABLE t1 (
|
||
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
||
a VARCHAR(200),
|
||
b TEXT,
|
||
FULLTEXT (b) WITH PARSER simple_parser
|
||
) CHARACTER SET = UTF8,ENGINE = InnoDB;
|
||
|
||
|
||
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 ...'),
|
||
('Я могу есть стекло', 'оно мне Mне вредит'),
|
||
('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'),
|
||
('Sævör grét', 'áðan því úlpan var ónýt');
|
||
|
||
CREATE FULLTEXT INDEX idx on t1 (a,b) WITH PARSER simple_parser;
|
||
INSERT INTO t1 (a,b) VALUES
|
||
('adding record using session 1','for verifying multiple concurrent transactions'),
|
||
('Мога да ям стъкло', 'то Mне ми вреди'),(NULL,NULL);
|
||
|
||
let $restart_parameters = restart:--innodb_ft_min_token_size=5 --innodb_ft_max_token_size=10;
|
||
--source include/restart_mysqld.inc
|
||
|
||
SELECT @@innodb_ft_min_token_size,@@innodb_ft_max_token_size;
|
||
|
||
ALTER TABLE t1 DROP INDEX idx;
|
||
|
||
# invalid query
|
||
--error 1191
|
||
SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+MySQL' IN BOOLEAN MODE);
|
||
|
||
CREATE FULLTEXT INDEX idx on t1 (a,b) WITH PARSER simple_parser;
|
||
|
||
|
||
ALTER TABLE t1 DROP INDEX idx;
|
||
CREATE FULLTEXT INDEX idx on t1 (a,b);
|
||
|
||
# no record expected - records not seen in boolean for simple_parser
|
||
SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+MySQL' IN BOOLEAN MODE);
|
||
# # # 2 record expected
|
||
SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('MySQL');
|
||
# no record expected as length < 5
|
||
SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('dbms');
|
||
# 1 record expected as index on b built with min size 3
|
||
SELECT * FROM t1 WHERE MATCH (b) AGAINST ('show');
|
||
# 1 record expected
|
||
SELECT * FROM t1 WHERE MATCH (b) AGAINST ('dbms') AND MATCH (a,b) AGAINST ('transactions');
|
||
# # # 1 record expected
|
||
SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('transactions');
|
||
SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"dbms stands"@05' IN BOOLEAN MODE);
|
||
# No result expected for query with null
|
||
SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('NULL' IN BOOLEAN MODE);
|
||
SELECT * FROM t1 WHERE b IS NULL or a IS NULL;
|
||
|
||
|
||
# join tables
|
||
CREATE TABLE t1_join AS SELECT * FROM t1;
|
||
CREATE FULLTEXT INDEX idx on t1_join (a,b) WITH PARSER simple_parser;
|
||
SELECT t1.id,t1_join.id FROM t1,t1_join WHERE MATCH (t1.a,t1.b) AGAINST ('Tutorial') AND t1.id = t1_join.id ;
|
||
|
||
--error ER_FT_MATCHING_KEY_NOT_FOUND
|
||
SELECT * FROM t1 WHERE MATCH (a) AGAINST ('NULL');
|
||
|
||
DROP TABLE t1;
|
||
DROP TABLE t1_join;
|
||
|
||
#
|
||
# BUG#21638891 NGRAM PARSER: FAILING ASSERTION: STATE->DEPTH > 0
|
||
#
|
||
|
||
EVAL CREATE TABLE t1 (
|
||
a VARCHAR(200),
|
||
FULLTEXT (a) WITH PARSER ngram
|
||
)ENGINE = InnoDB;
|
||
|
||
SELECT * FROM t1 WHERE MATCH(a) AGAINST("')'@7" IN BOOLEAN MODE);
|
||
|
||
DROP TABLE t1;
|
||
|
||
UNINSTALL PLUGIN simple_parser;
|