Debian Codegraph @

mysql-test/suite/innodb_fts/r/plugin_1.result

raw
1 INSTALL PLUGIN simple_parser SONAME 'mypluglib.so';
2 # Test Part 2: Create Index Test(CREATE TABLE WITH FULLTEXT INDEX)
3 CREATE TABLE articles (
4 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
5 title VARCHAR(200),
6 body TEXT,
7 body1 TEXT,
8 body2 TEXT,
9 body3 TEXT,
10 FULLTEXT (title, body) WITH PARSER simple_parser,
11 FULLTEXT (title, body1) WITH PARSER simple_parser,
12 FULLTEXT (body3)
13 ) ENGINE=InnoDB;
14 INSERT INTO articles (title, body,body1,body2,body3) VALUES
15 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...','row1col4','row1col5','row1col5'),
16 ('How To Use MySQL Well','After you went through a ...','row2col4','row2col5','row2col5'),
17 ('Optimizing MySQL','In this tutorial we will show ...','row3col4','row3col5','row3col5 for'),
18 ('1001 MySQL Tricks','How to use full-text search engine','row4col4','row4col5','row4col5'),
19 ('Go MySQL Tricks','How to use full text search engine','row5col4 this','row5col5','row5col5');
20 ALTER TABLE articles ADD FULLTEXT INDEX (body1,body2) WITH PARSER simple_parser;
21 ALTER TABLE articles ADD FULLTEXT INDEX (body2,body3);
22 ALTER TABLE articles ADD FULLTEXT INDEX (body) WITH PARSER simple_parser;
23 ANALYZE TABLE articles;
24 Table Op Msg_type Msg_text
25 test.articles analyze status OK
26 SELECT
27 PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_TYPE,
28 PLUGIN_LIBRARY
29 FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%simple_parser%';
30 PLUGIN_NAME PLUGIN_STATUS PLUGIN_TYPE PLUGIN_LIBRARY
31 simple_parser ACTIVE FTPARSER mypluglib.so
32 INSERT INTO articles (title, body,body1,body2,body3) VALUES (NULL,NULL,NULL,NULL,NULL);
33 SELECT * FROM articles WHERE
34 MATCH(title, body) AGAINST('mysql');
35 id title body body1 body2 body3
36 1 MySQL Tutorial DBMS stands for MySQL DataBase ... row1col4 row1col5 row1col5
37 2 How To Use MySQL Well After you went through a ... row2col4 row2col5 row2col5
38 3 Optimizing MySQL In this tutorial we will show ... row3col4 row3col5 row3col5 for
39 4 1001 MySQL Tricks How to use full-text search engine row4col4 row4col5 row4col5
40 5 Go MySQL Tricks How to use full text search engine row5col4 this row5col5 row5col5
41 SELECT * FROM articles WHERE
42 MATCH(title, body) AGAINST('dbms' WITH QUERY EXPANSION);
43 id title body body1 body2 body3
44 1 MySQL Tutorial DBMS stands for MySQL DataBase ... row1col4 row1col5 row1col5
45 2 How To Use MySQL Well After you went through a ... row2col4 row2col5 row2col5
46 3 Optimizing MySQL In this tutorial we will show ... row3col4 row3col5 row3col5 for
47 4 1001 MySQL Tricks How to use full-text search engine row4col4 row4col5 row4col5
48 5 Go MySQL Tricks How to use full text search engine row5col4 this row5col5 row5col5
49 SELECT * FROM articles WHERE
50 MATCH(title, body1) AGAINST('row1col4');
51 id title body body1 body2 body3
52 1 MySQL Tutorial DBMS stands for MySQL DataBase ... row1col4 row1col5 row1col5
53 SELECT * FROM articles WHERE
54 MATCH(title, body) AGAINST('row5col4' WITH QUERY EXPANSION);
55 id title body body1 body2 body3
56 SELECT * FROM articles WHERE
57 MATCH(body1, body2) AGAINST('row3col4');
58 id title body body1 body2 body3
59 3 Optimizing MySQL In this tutorial we will show ... row3col4 row3col5 row3col5 for
60 SELECT * FROM articles WHERE
61 MATCH(body) AGAINST('well' WITH QUERY EXPANSION);
62 id title body body1 body2 body3
63 SELECT * FROM articles WHERE
64 MATCH(title, body) AGAINST('will go');
65 id title body body1 body2 body3
66 SELECT * FROM articles WHERE
67 MATCH(title, body) AGAINST('this');
68 id title body body1 body2 body3
69 SELECT * FROM articles WHERE
70 MATCH(title, body) AGAINST('for');
71 id title body body1 body2 body3
72 SELECT * FROM articles WHERE
73 MATCH(body1, body2) AGAINST('this');
74 id title body body1 body2 body3
75 SELECT * FROM articles WHERE
76 MATCH(body) AGAINST('this');
77 id title body body1 body2 body3
78 # Test plugin parser tokenizer difference
79 SELECT * FROM articles WHERE
80 MATCH(title, body) AGAINST('full-text');
81 id title body body1 body2 body3
82 4 1001 MySQL Tricks How to use full-text search engine row4col4 row4col5 row4col5
83 SELECT * FROM articles WHERE
84 MATCH(title, body) AGAINST('full text');
85 id title body body1 body2 body3
86 5 Go MySQL Tricks How to use full text search engine row5col4 this row5col5 row5col5
87 SELECT * FROM articles WHERE
88 MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE);
89 id title body body1 body2 body3
90 SELECT * FROM articles WHERE
91 MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE);
92 id title body body1 body2 body3
93 START TRANSACTION;
94 INSERT INTO articles (title, body,body1,body2,body3) VALUES
95 ('latest Tutorial','transactional properties tests ...','row6col4','row6col5','row6col5'),
96 ('certification guide','all mysql exams tests ...','row7col4','row7col5','row7col5');
97 SELECT * FROM articles WHERE
98 MATCH(title, body) AGAINST('mysql');
99 id title body body1 body2 body3
100 1 MySQL Tutorial DBMS stands for MySQL DataBase ... row1col4 row1col5 row1col5
101 2 How To Use MySQL Well After you went through a ... row2col4 row2col5 row2col5
102 3 Optimizing MySQL In this tutorial we will show ... row3col4 row3col5 row3col5 for
103 4 1001 MySQL Tricks How to use full-text search engine row4col4 row4col5 row4col5
104 5 Go MySQL Tricks How to use full text search engine row5col4 this row5col5 row5col5
105 SELECT * FROM articles WHERE
106 MATCH(title, body) AGAINST('Tutorial');
107 id title body body1 body2 body3
108 1 MySQL Tutorial DBMS stands for MySQL DataBase ... row1col4 row1col5 row1col5
109 3 Optimizing MySQL In this tutorial we will show ... row3col4 row3col5 row3col5 for
110 SELECT * FROM articles WHERE
111 MATCH(title, body) AGAINST('guide');
112 id title body body1 body2 body3
113 SELECT * FROM articles WHERE
114 MATCH(title, body) AGAINST('tests');
115 id title body body1 body2 body3
116 SELECT * FROM articles WHERE
117 MATCH(title, body1) AGAINST('row6col4');
118 id title body body1 body2 body3
119 SELECT * FROM articles WHERE
120 MATCH(body1,body2) AGAINST('row7col5');
121 id title body body1 body2 body3
122 SELECT * FROM articles WHERE id > 5;
123 id title body body1 body2 body3
124 6 NULL NULL NULL NULL NULL
125 7 latest Tutorial transactional properties tests ... row6col4 row6col5 row6col5
126 8 certification guide all mysql exams tests ... row7col4 row7col5 row7col5
127 ROLLBACK;
128 SELECT * FROM articles WHERE
129 MATCH(title, body) AGAINST('mysql');
130 id title body body1 body2 body3
131 1 MySQL Tutorial DBMS stands for MySQL DataBase ... row1col4 row1col5 row1col5
132 2 How To Use MySQL Well After you went through a ... row2col4 row2col5 row2col5
133 3 Optimizing MySQL In this tutorial we will show ... row3col4 row3col5 row3col5 for
134 4 1001 MySQL Tricks How to use full-text search engine row4col4 row4col5 row4col5
135 5 Go MySQL Tricks How to use full text search engine row5col4 this row5col5 row5col5
136 SELECT * FROM articles WHERE
137 MATCH(title, body) AGAINST('Tutorial');
138 id title body body1 body2 body3
139 1 MySQL Tutorial DBMS stands for MySQL DataBase ... row1col4 row1col5 row1col5
140 3 Optimizing MySQL In this tutorial we will show ... row3col4 row3col5 row3col5 for
141 SELECT * FROM articles WHERE
142 MATCH(title, body) AGAINST('guide');
143 id title body body1 body2 body3
144 SELECT * FROM articles WHERE
145 MATCH(title, body) AGAINST('tests');
146 id title body body1 body2 body3
147 SELECT * FROM articles WHERE
148 MATCH(title, body1) AGAINST('row6col4');
149 id title body body1 body2 body3
150 SELECT * FROM articles WHERE
151 MATCH(body1,body2) AGAINST('row7col5');
152 id title body body1 body2 body3
153 START TRANSACTION;
154 INSERT INTO articles (title, body,body1,body2,body3) VALUES
155 ('latest Tutorial','transactional properties tests ...','row6col4','row6col5','row6col5');
156 COMMIT;
157 SELECT * FROM articles WHERE
158 MATCH(title, body) AGAINST('mysql');
159 id title body body1 body2 body3
160 1 MySQL Tutorial DBMS stands for MySQL DataBase ... row1col4 row1col5 row1col5
161 2 How To Use MySQL Well After you went through a ... row2col4 row2col5 row2col5
162 3 Optimizing MySQL In this tutorial we will show ... row3col4 row3col5 row3col5 for
163 4 1001 MySQL Tricks How to use full-text search engine row4col4 row4col5 row4col5
164 5 Go MySQL Tricks How to use full text search engine row5col4 this row5col5 row5col5
165 SELECT * FROM articles WHERE
166 MATCH(title, body) AGAINST('Tutorial');
167 id title body body1 body2 body3
168 1 MySQL Tutorial DBMS stands for MySQL DataBase ... row1col4 row1col5 row1col5
169 3 Optimizing MySQL In this tutorial we will show ... row3col4 row3col5 row3col5 for
170 9 latest Tutorial transactional properties tests ... row6col4 row6col5 row6col5
171 SELECT * FROM articles WHERE
172 MATCH(title, body) AGAINST('guide');
173 id title body body1 body2 body3
174 SELECT * FROM articles WHERE
175 MATCH(title, body) AGAINST('tests');
176 id title body body1 body2 body3
177 9 latest Tutorial transactional properties tests ... row6col4 row6col5 row6col5
178 SELECT * FROM articles WHERE
179 MATCH(body) AGAINST('tests');
180 id title body body1 body2 body3
181 9 latest Tutorial transactional properties tests ... row6col4 row6col5 row6col5
182 SELECT * FROM articles WHERE
183 MATCH(title, body1) AGAINST('row6col4');
184 id title body body1 body2 body3
185 9 latest Tutorial transactional properties tests ... row6col4 row6col5 row6col5
186 SELECT * FROM articles WHERE
187 MATCH(body1,body2) AGAINST('row7col5');
188 id title body body1 body2 body3
189 SELECT COUNT(*) FROM articles;
190 COUNT(*)
191 7
192 ALTER TABLE articles ADD COLUMN col_test VARCHAR(100) DEFAULT 'check add drop column';
193 SELECT COUNT(*) FROM articles WHERE col_test = 'check add drop column';
194 COUNT(*)
195 7
196 ALTER TABLE articles ADD FULLTEXT INDEX `idx_add` (col_test) WITH PARSER simple_parser;
197 INSERT INTO articles (title, body,body1,body2,body3,col_test) VALUES
198 ('value','value','value','value','value','value');
199 SELECT COUNT(*) AS score FROM articles WHERE
200 MATCH(col_test) AGAINST('check');
201 score
202 7
203 SELECT COUNT(*) AS score FROM articles WHERE
204 MATCH(col_test) AGAINST('value');
205 score
206 1
207 ALTER TABLE articles DROP COLUMN col_test;
208 SELECT * FROM articles WHERE
209 MATCH(title, body1) AGAINST('row6col4');
210 id title body body1 body2 body3
211 9 latest Tutorial transactional properties tests ... row6col4 row6col5 row6col5
212 SELECT * FROM articles WHERE
213 MATCH(body1,body2) AGAINST('row7col5');
214 id title body body1 body2 body3
215 DROP TABLE articles;
216 SET NAMES UTF8;
217 Warnings:
218 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
219 DROP TABLE IF EXISTS t1;
220 CREATE TABLE t1 (
221 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
222 a VARCHAR(200),
223 b TEXT,
224 FULLTEXT (b) WITH PARSER simple_parser
225 ) CHARACTER SET = UTF8,ENGINE = InnoDB;
226 Warnings:
227 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
228 INSERT INTO t1 (a,b) VALUES
229 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
230 ('when To Use MySQL Well','After that you went through a ...'),
231 ('where will Optimizing MySQL','what In this tutorial we will show ...'),
232 ('Я могу есть стекло', 'оно мне Mне вредит'),
233 ('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'),
234 ('Sævör grét', 'áðan því úlpan var ónýt');
235 CREATE FULLTEXT INDEX idx on t1 (a,b) WITH PARSER simple_parser;
236 INSERT INTO t1 (a,b) VALUES
237 ('adding record using session 1','for verifying multiple concurrent transactions'),
238 ('Мога да ям стъкло', 'то Mне ми вреди');
239 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+Mне' IN BOOLEAN MODE);
240 id a b
241 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('Mне');
242 id a b
243 4 Я могу есть стекло оно мне Mне вредит
244 8 Мога да ям стъкло то Mне ми вреди
245 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('могу');
246 id a b
247 4 Я могу есть стекло оно мне Mне вредит
248 SELECT * FROM t1 WHERE MATCH (b) AGAINST ('áðan');
249 id a b
250 6 Sævör grét áðan því úlpan var ónýt
251 SELECT * FROM t1 WHERE MATCH (b) AGAINST ('áðan') OR MATCH (a,b) AGAINST ('Mне могу');
252 id a b
253 4 Я могу есть стекло оно мне Mне вредит
254 6 Sævör grét áðan því úlpan var ónýt
255 8 Мога да ям стъкло то Mне ми вреди
256 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('стъкло');
257 id a b
258 8 Мога да ям стъкло то Mне ми вреди
259 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"dbms stands"@05' IN BOOLEAN MODE);
260 id a b
261 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('for');
262 id a b
263 DROP TABLE t1;
264 SELECT @@innodb_ft_min_token_size,@@innodb_ft_max_token_size;
265 @@innodb_ft_min_token_size @@innodb_ft_max_token_size
266 3 84
267 CREATE TABLE t1 (
268 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
269 a VARCHAR(200),
270 b TEXT,
271 FULLTEXT (b) WITH PARSER simple_parser
272 ) CHARACTER SET = UTF8,ENGINE = InnoDB;
273 Warnings:
274 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
275 INSERT INTO t1 (a,b) VALUES
276 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
277 ('when To Use MySQL Well','After that you went through a ...'),
278 ('where will Optimizing MySQL','what In this tutorial we will show ...'),
279 ('Я могу есть стекло', 'оно мне Mне вредит'),
280 ('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'),
281 ('Sævör grét', 'áðan því úlpan var ónýt');
282 CREATE FULLTEXT INDEX idx on t1 (a,b) WITH PARSER simple_parser;
283 INSERT INTO t1 (a,b) VALUES
284 ('adding record using session 1','for verifying multiple concurrent transactions'),
285 ('Мога да ям стъкло', 'то Mне ми вреди'),(NULL,NULL);
286 # restart:--innodb_ft_min_token_size=5 --innodb_ft_max_token_size=10
287 SELECT @@innodb_ft_min_token_size,@@innodb_ft_max_token_size;
288 @@innodb_ft_min_token_size @@innodb_ft_max_token_size
289 5 10
290 ALTER TABLE t1 DROP INDEX idx;
291 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+MySQL' IN BOOLEAN MODE);
292 ERROR HY000: Can't find FULLTEXT index matching the column list
293 CREATE FULLTEXT INDEX idx on t1 (a,b) WITH PARSER simple_parser;
294 ALTER TABLE t1 DROP INDEX idx;
295 CREATE FULLTEXT INDEX idx on t1 (a,b);
296 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('+MySQL' IN BOOLEAN MODE);
297 id a b
298 1 MySQL from Tutorial DBMS stands for DataBase ...
299 2 when To Use MySQL Well After that you went through a ...
300 3 where will Optimizing MySQL what In this tutorial we will show ...
301 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('MySQL');
302 id a b
303 1 MySQL from Tutorial DBMS stands for DataBase ...
304 2 when To Use MySQL Well After that you went through a ...
305 3 where will Optimizing MySQL what In this tutorial we will show ...
306 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('dbms');
307 id a b
308 SELECT * FROM t1 WHERE MATCH (b) AGAINST ('show');
309 id a b
310 3 where will Optimizing MySQL what In this tutorial we will show ...
311 SELECT * FROM t1 WHERE MATCH (b) AGAINST ('dbms') AND MATCH (a,b) AGAINST ('transactions');
312 id a b
313 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('transactions');
314 id a b
315 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"dbms stands"@05' IN BOOLEAN MODE);
316 id a b
317 1 MySQL from Tutorial DBMS stands for DataBase ...
318 SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('NULL' IN BOOLEAN MODE);
319 id a b
320 SELECT * FROM t1 WHERE b IS NULL or a IS NULL;
321 id a b
322 9 NULL NULL
323 CREATE TABLE t1_join AS SELECT * FROM t1;
324 CREATE FULLTEXT INDEX idx on t1_join (a,b) WITH PARSER simple_parser;
325 Warnings:
326 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
327 SELECT t1.id,t1_join.id FROM t1,t1_join WHERE MATCH (t1.a,t1.b) AGAINST ('Tutorial') AND t1.id = t1_join.id ;
328 id id
329 1 1
330 3 3
331 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('NULL');
332 ERROR HY000: Can't find FULLTEXT index matching the column list
333 DROP TABLE t1;
334 DROP TABLE t1_join;
335 CREATE TABLE t1 (
336 a VARCHAR(200),
337 FULLTEXT (a) WITH PARSER ngram
338 )ENGINE = InnoDB;
339 SELECT * FROM t1 WHERE MATCH(a) AGAINST("')'@7" IN BOOLEAN MODE);
340 a
341 DROP TABLE t1;
342 UNINSTALL PLUGIN simple_parser;
343 # restart