Debian Codegraph @

mysql-test/suite/innodb_fts/t/plugin_1.test

raw
1 --source include/have_simple_parser.inc
2 --source include/no_valgrind_without_big.inc
3
4 #-----------------------------------------------------------------------------
5 # wl6943 : Support external parser
6 # Adding FTS check with "simple_praser" (external parser plugin)
7 # - Table with external parser + default parser
8 # - FTS index with single and two columns
9 # - external parser index using alter table
10 # - check with transactional statement
11 # - Check utf8 charset table
12 # - alter table commands Add/drop fulltext index and column
13 # - NULL values in table
14 # - FTS query with join condition
15 # - Invalid query
16 #-----------------------------------------------------------------------------
17
18 # Install fts parser plugin
19 --replace_regex /\.dll/.so/
20 eval INSTALL PLUGIN simple_parser SONAME '$SIMPLE_PARSER';
21
22
23 -- echo # Test Part 2: Create Index Test(CREATE TABLE WITH FULLTEXT INDEX)
24 CREATE TABLE articles (
25 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
26 title VARCHAR(200),
27 body TEXT,
28 body1 TEXT,
29 body2 TEXT,
30 body3 TEXT,
31 FULLTEXT (title, body) WITH PARSER simple_parser,
32 FULLTEXT (title, body1) WITH PARSER simple_parser,
33 FULLTEXT (body3)
34 ) ENGINE=InnoDB;
35
36 INSERT INTO articles (title, body,body1,body2,body3) VALUES
37 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...','row1col4','row1col5','row1col5'),
38 ('How To Use MySQL Well','After you went through a ...','row2col4','row2col5','row2col5'),
39 ('Optimizing MySQL','In this tutorial we will show ...','row3col4','row3col5','row3col5 for'),
40 ('1001 MySQL Tricks','How to use full-text search engine','row4col4','row4col5','row4col5'),
41 ('Go MySQL Tricks','How to use full text search engine','row5col4 this','row5col5','row5col5');
42
43 ALTER TABLE articles ADD FULLTEXT INDEX (body1,body2) WITH PARSER simple_parser;
44 ALTER TABLE articles ADD FULLTEXT INDEX (body2,body3);
45 ALTER TABLE articles ADD FULLTEXT INDEX (body) WITH PARSER simple_parser;
46
47 ANALYZE TABLE articles;
48
49 --replace_regex /\.dll/.so/
50 SELECT
51 PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE,
52 PLUGIN_LIBRARY
53 FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%simple_parser%';
54
55
56 INSERT INTO articles (title, body,body1,body2,body3) VALUES (NULL,NULL,NULL,NULL,NULL);
57
58 # Simple term search
59 SELECT * FROM articles WHERE
60 MATCH(title, body) AGAINST('mysql');
61 --sorted_result
62 SELECT * FROM articles WHERE
63 MATCH(title, body) AGAINST('dbms' WITH QUERY EXPANSION);
64 SELECT * FROM articles WHERE
65 MATCH(title, body1) AGAINST('row1col4');
66 SELECT * FROM articles WHERE
67 MATCH(title, body) AGAINST('row5col4' WITH QUERY EXPANSION);
68 SELECT * FROM articles WHERE
69 MATCH(body1, body2) AGAINST('row3col4');
70 SELECT * FROM articles WHERE
71 MATCH(body) AGAINST('well' WITH QUERY EXPANSION);
72
73 # Test stopword and word len less than fts_min_token_size
74 SELECT * FROM articles WHERE
75 MATCH(title, body) AGAINST('will go');
76 #stopword
77 SELECT * FROM articles WHERE
78 MATCH(title, body) AGAINST('this');
79 SELECT * FROM articles WHERE
80 MATCH(title, body) AGAINST('for');
81 SELECT * FROM articles WHERE
82 MATCH(body1, body2) AGAINST('this');
83 SELECT * FROM articles WHERE
84 MATCH(body) AGAINST('this');
85
86
87 -- echo # Test plugin parser tokenizer difference
88 SELECT * FROM articles WHERE
89 MATCH(title, body) AGAINST('full-text');
90
91 SELECT * FROM articles WHERE
92 MATCH(title, body) AGAINST('full text');
93
94 # No result here, we get '"mysql' 'database"' by simple parser
95 SELECT * FROM articles WHERE
96 MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE);
97
98
99 # Phrase search
100 SELECT * FROM articles WHERE
101 MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE);
102
103
104 # use transaction
105 START TRANSACTION;
106 INSERT INTO articles (title, body,body1,body2,body3) VALUES
107 ('latest Tutorial','transactional properties tests ...','row6col4','row6col5','row6col5'),
108 ('certification guide','all mysql exams tests ...','row7col4','row7col5','row7col5');
109 # FTS do not parse those uncommitted rows, only one row should show up
110 SELECT * FROM articles WHERE
111 MATCH(title, body) AGAINST('mysql');
112 SELECT * FROM articles WHERE
113 MATCH(title, body) AGAINST('Tutorial');
114 SELECT * FROM articles WHERE
115 MATCH(title, body) AGAINST('guide');
116 SELECT * FROM articles WHERE
117 MATCH(title, body) AGAINST('tests');
118
119 SELECT * FROM articles WHERE
120 MATCH(title, body1) AGAINST('row6col4');
121 SELECT * FROM articles WHERE
122 MATCH(body1,body2) AGAINST('row7col5');
123 # uncommitted records can be seen with non fts queries
124 SELECT * FROM articles WHERE id > 5;
125
126 ROLLBACK;
127 SELECT * FROM articles WHERE
128 MATCH(title, body) AGAINST('mysql');
129 SELECT * FROM articles WHERE
130 MATCH(title, body) AGAINST('Tutorial');
131 SELECT * FROM articles WHERE
132 MATCH(title, body) AGAINST('guide');
133 SELECT * FROM articles WHERE
134 MATCH(title, body) AGAINST('tests');
135
136 SELECT * FROM articles WHERE
137 MATCH(title, body1) AGAINST('row6col4');
138 SELECT * FROM articles WHERE
139 MATCH(body1,body2) AGAINST('row7col5');
140
141 START TRANSACTION;
142 INSERT INTO articles (title, body,body1,body2,body3) VALUES
143 ('latest Tutorial','transactional properties tests ...','row6col4','row6col5','row6col5');
144 COMMIT;
145 # FTS only parse committed rows, now rows should show up
146 SELECT * FROM articles WHERE
147 MATCH(title, body) AGAINST('mysql');
148 SELECT * FROM articles WHERE
149 MATCH(title, body) AGAINST('Tutorial');
150 SELECT * FROM articles WHERE
151 MATCH(title, body) AGAINST('guide');
152 SELECT * FROM articles WHERE
153 MATCH(title, body) AGAINST('tests');
154 SELECT * FROM articles WHERE
155 MATCH(body) AGAINST('tests');
156
157 SELECT * FROM articles WHERE
158 MATCH(title, body1) AGAINST('row6col4');
159 SELECT * FROM articles WHERE
160 MATCH(body1,body2) AGAINST('row7col5');
161
162 # Alter table Add/drop column
163 SELECT COUNT(*) FROM articles;
164 ALTER TABLE articles ADD COLUMN col_test VARCHAR(100) DEFAULT 'check add drop column';
165 SELECT COUNT(*) FROM articles WHERE col_test = 'check add drop column';
166 ALTER TABLE articles ADD FULLTEXT INDEX `idx_add` (col_test) WITH PARSER simple_parser;
167 INSERT INTO articles (title, body,body1,body2,body3,col_test) VALUES
168 ('value','value','value','value','value','value');
169 SELECT COUNT(*) AS score FROM articles WHERE
170 MATCH(col_test) AGAINST('check');
171 SELECT COUNT(*) AS score FROM articles WHERE
172 MATCH(col_test) AGAINST('value');
173
174 ALTER TABLE articles DROP COLUMN col_test;
175
176 SELECT * FROM articles WHERE
177 MATCH(title, body1) AGAINST('row6col4');
178 SELECT * FROM articles WHERE
179 MATCH(body1,body2) AGAINST('row7col5');
180
181 DROP TABLE articles;
182 # Get warning here
183
184
185 #set names utf8;
186 SET NAMES UTF8;
187
188
189 --disable_warnings
190 DROP TABLE IF EXISTS t1;
191 --enable_warnings
192
193 # Create FTS table
194 EVAL CREATE TABLE t1 (
195 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
196 a VARCHAR(200),
197 b TEXT,
198 FULLTEXT (b) WITH PARSER simple_parser
199 ) CHARACTER SET = UTF8,ENGINE = InnoDB;
200
201
202 # Insert rows
203 INSERT INTO t1 (a,b) VALUES
204 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
205 ('when To Use MySQL Well','After that you went through a ...'),
206 ('where will Optimizing MySQL','what In this tutorial we will show ...'),
207 ('Я могу есть стекло', 'оно мне Mне вредит'),
208 ('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'),
209 ('Sævör grét', 'áðan því úlpan var ónýt');
210
211 CREATE FULLTEXT INDEX idx on t1 (a,b) WITH PARSER simple_parser;
212 INSERT INTO t1 (a,b) VALUES
213 ('adding record using session 1','for verifying multiple concurrent transactions'),
214 ('Мога да ям стъкло', 'то Mне ми вреди');
215
216 # no record expected - records not seen in boolean for simple_parser
217 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
218 # 2 record expected
219 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('Mне');
220 # 1 record expected
221 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('могу');
222 SELECT * FROM t1 WHERE MATCH (b) AGAINST ('áðan');
223 # 3 record expected
224 SELECT * FROM t1 WHERE MATCH (b) AGAINST ('áðan') OR MATCH (a,b) AGAINST ('Mне могу');
225 # 1 record expected
226 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стъкло');
227 # 1 record expected
228 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"dbms stands"@05' IN BOOLEAN MODE);
229 # no records for stopword
230 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('for');
231
232 DROP TABLE t1;
233
234 # Set fts_min_token_size, max_token_size values
235 # Write file to make mysql-test-run.pl start up the server again
236 SELECT @@innodb_ft_min_token_size,@@innodb_ft_max_token_size;
237
238 EVAL CREATE TABLE t1 (
239 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
240 a VARCHAR(200),
241 b TEXT,
242 FULLTEXT (b) WITH PARSER simple_parser
243 ) CHARACTER SET = UTF8,ENGINE = InnoDB;
244
245
246 INSERT INTO t1 (a,b) VALUES
247 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
248 ('when To Use MySQL Well','After that you went through a ...'),
249 ('where will Optimizing MySQL','what In this tutorial we will show ...'),
250 ('Я могу есть стекло', 'оно мне Mне вредит'),
251 ('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'),
252 ('Sævör grét', 'áðan því úlpan var ónýt');
253
254 CREATE FULLTEXT INDEX idx on t1 (a,b) WITH PARSER simple_parser;
255 INSERT INTO t1 (a,b) VALUES
256 ('adding record using session 1','for verifying multiple concurrent transactions'),
257 ('Мога да ям стъкло', 'то Mне ми вреди'),(NULL,NULL);
258
259 let $restart_parameters = restart:--innodb_ft_min_token_size=5 --innodb_ft_max_token_size=10;
260 --source include/restart_mysqld.inc
261
262 SELECT @@innodb_ft_min_token_size,@@innodb_ft_max_token_size;
263
264 ALTER TABLE t1 DROP INDEX idx;
265
266 # invalid query
267 --error 1191
268 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+MySQL' IN BOOLEAN MODE);
269
270 CREATE FULLTEXT INDEX idx on t1 (a,b) WITH PARSER simple_parser;
271
272
273 ALTER TABLE t1 DROP INDEX idx;
274 CREATE FULLTEXT INDEX idx on t1 (a,b);
275
276 # no record expected - records not seen in boolean for simple_parser
277 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+MySQL' IN BOOLEAN MODE);
278 # # # 2 record expected
279 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('MySQL');
280 # no record expected as length < 5
281 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('dbms');
282 # 1 record expected as index on b built with min size 3
283 SELECT * FROM t1 WHERE MATCH (b) AGAINST ('show');
284 # 1 record expected
285 SELECT * FROM t1 WHERE MATCH (b) AGAINST ('dbms') AND MATCH (a,b) AGAINST ('transactions');
286 # # # 1 record expected
287 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('transactions');
288 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"dbms stands"@05' IN BOOLEAN MODE);
289 # No result expected for query with null
290 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('NULL' IN BOOLEAN MODE);
291 SELECT * FROM t1 WHERE b IS NULL or a IS NULL;
292
293
294 # join tables
295 CREATE TABLE t1_join AS SELECT * FROM t1;
296 CREATE FULLTEXT INDEX idx on t1_join (a,b) WITH PARSER simple_parser;
297 SELECT t1.id,t1_join.id FROM t1,t1_join WHERE MATCH (t1.a,t1.b) AGAINST ('Tutorial') AND t1.id = t1_join.id ;
298
299 --error ER_FT_MATCHING_KEY_NOT_FOUND
300 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('NULL');
301
302 DROP TABLE t1;
303 DROP TABLE t1_join;
304
305 #
306 # BUG#21638891 NGRAM PARSER: FAILING ASSERTION: STATE->DEPTH > 0
307 #
308
309 EVAL CREATE TABLE t1 (
310 a VARCHAR(200),
311 FULLTEXT (a) WITH PARSER ngram
312 )ENGINE = InnoDB;
313
314 SELECT * FROM t1 WHERE MATCH(a) AGAINST("')'@7" IN BOOLEAN MODE);
315
316 DROP TABLE t1;
317
318 UNINSTALL PLUGIN simple_parser;
319
320 --let $restart_parameters=
321 --source include/restart_mysqld.inc