Debian Codegraph @

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

raw
1 #-----------------------------------------------------------------------------
2 # Test With alter/create/drop index
3 #-----------------------------------------------------------------------------
4
5 --source include/have_innodb.inc
6 --source include/no_valgrind_without_big.inc
7 --source include/default_charset.inc
8 let collation=UTF8_UNICODE_CI;
9 --source include/have_collation.inc
10
11 # Create FTS table
12 CREATE TABLE t1 (
13 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
14 a VARCHAR(200),
15 b TEXT
16 ) ENGINE = InnoDB;
17
18 # Insert rows
19 INSERT INTO t1 (a,b) VALUES
20 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
21 ('How To Use MySQL Well','After you went through a ...'),
22 ('Optimizing MySQL','In this tutorial we will show ...');
23
24 # Create the FTS index Using Alter Table
25 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
26 EVAL SHOW CREATE TABLE t1;
27
28 # Insert rows
29 INSERT INTO t1 (a,b) VALUES
30 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
31 ('MySQL vs. YourSQL','In the following database comparison ...'),
32 ('MySQL Security','When configured properly, MySQL ...');
33
34 # Select word "tutorial" in the table
35 SELECT id FROM t1 WHERE MATCH (a,b)
36 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
37
38 # boolean mode
39 select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
40 select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
41 select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
42 select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
43 select id, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
44 select id, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;
45 select id from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE);
46 select id from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE);
47 select id from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE);
48
49 # query expansion
50 select id from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION);
51 select id from t1 where MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION);
52 select id from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY EXPANSION);
53
54 # Drop index
55 ALTER TABLE t1 DROP INDEX idx;
56
57 # Create the FTS index again
58 CREATE FULLTEXT INDEX idx on t1 (a,b);
59
60 # Select word "tutorial" in the table
61 SELECT id FROM t1 WHERE MATCH (a,b)
62 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
63
64 # boolean mode
65 select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
66 select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
67 select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
68 select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
69 select id, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
70 select id, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;
71 select id from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE);
72 select id from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE);
73 select id from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE);
74
75 # query expansion
76 select id from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION);
77 select id from t1 where MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION);
78 select id from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY EXPANSION);
79
80 # insert for proximity search
81 INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...');
82 # Insert into table with similar word of different distances
83 INSERT INTO t1 (a,b) VALUES
84 ('test proximity search, test, proximity and phrase',
85 'search, with proximity innodb');
86
87 INSERT INTO t1 (a,b) VALUES
88 ('test proximity fts search, test, proximity and phrase',
89 'search, with proximity innodb');
90
91 INSERT INTO t1 (a,b) VALUES
92 ('test more proximity fts search, test, more proximity and phrase',
93 'search, with proximity innodb');
94
95 # This should only return the first document
96 SELECT id FROM t1
97 WHERE MATCH (a,b)
98 AGAINST ('"proximity search"@2' IN BOOLEAN MODE);
99
100 # This would return no document
101 SELECT id FROM t1
102 WHERE MATCH (a,b)
103 AGAINST ('"proximity search"@1' IN BOOLEAN MODE);
104
105 # This give you all three documents
106 SELECT id FROM t1
107 WHERE MATCH (a,b)
108 AGAINST ('"proximity search"@3' IN BOOLEAN MODE);
109
110 # Similar boundary testing for the words
111 SELECT id FROM t1
112 WHERE MATCH (a,b)
113 AGAINST ('"test proximity"@3' IN BOOLEAN MODE);
114
115 # Test with more word The last document will return, please notice there
116 # is no ordering requirement for proximity search.
117 SELECT id FROM t1
118 WHERE MATCH (a,b)
119 AGAINST ('"more test proximity"@3' IN BOOLEAN MODE);
120
121 SELECT id FROM t1
122 WHERE MATCH (a,b)
123 AGAINST ('"more test proximity"@2' IN BOOLEAN MODE);
124
125 # The phrase search will not require exact word ordering
126 SELECT id FROM t1
127 WHERE MATCH (a,b)
128 AGAINST ('"more fts proximity"@02' IN BOOLEAN MODE);
129
130 DROP TABLE t1;
131
132
133
134 #------------------------------------------------------------------------------
135 # Test with FTS condition in subquery
136 #------------------------------------------------------------------------------
137
138 # Create FTS table
139 CREATE TABLE t1 (
140 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
141 a VARCHAR(200),
142 b TEXT
143 ) ENGINE = InnoDB;
144
145 # Create the FTS index again
146 CREATE FULLTEXT INDEX idx on t1 (a,b);
147
148 # Insert rows
149 INSERT INTO t1 (a,b) VALUES
150 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
151 ('How To Use MySQL Well','After you went through a ...'),
152 ('Optimizing MySQL','In this tutorial we will show ...');
153
154 # Insert rows
155 INSERT INTO t1 (a,b) VALUES
156 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
157 ('MySQL vs. YourSQL','In the following database comparison ...'),
158 ('MySQL Security','When configured properly, MySQL ...');
159
160
161 # Select word "tutorial" in the table
162 SELECT id FROM t1 WHERE MATCH (a,b)
163 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
164
165 # Select word "tutorial" in the table
166 SELECT id FROM t1 WHERE id = (SELECT MAX(id) FROM t1 WHERE MATCH (a,b)
167 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE));
168
169 # Select word "tutorial" in the table
170 SELECT id FROM t1 WHERE id = (SELECT MIN(id) FROM t1 WHERE MATCH (a,b)
171 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE));
172
173 # Select word "tutorial" in the table
174 SELECT id FROM t1 WHERE id = (SELECT MIN(id) FROM t1 WHERE MATCH (a,b)
175 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)) OR id = 3 ;
176
177
178 # Select word "tutorial" in the table - innodb crash
179 SELECT id FROM t1 WHERE CONCAT(t1.a,t1.b) IN (
180 SELECT CONCAT(a,b) FROM t1 AS t2 WHERE
181 MATCH (t2.a,t2.b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)
182 ) OR t1.id = 3 ;
183
184
185 # Select word "tutorial" in the table - innodb crash
186 SELECT id FROM t1 WHERE CONCAT(t1.a,t1.b) IN (
187 SELECT CONCAT(a,b) FROM t1 AS t2
188 WHERE MATCH (t2.a,t2.b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)
189 AND t2.id != 3) ;
190
191 # Select word "tutorial" in the table
192 SELECT id FROM t1 WHERE id IN (SELECT MIN(id) FROM t1 WHERE
193 MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)) OR id = 3 ;
194
195 # Select word except "tutorial" in the table
196 SELECT id FROM t1 WHERE id NOT IN (SELECT MIN(id) FROM t1
197 WHERE MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)) ;
198
199
200 # Select word "tutorial" in the table
201 SELECT id FROM t1 WHERE EXISTS (SELECT t2.id FROM t1 AS t2 WHERE
202 MATCH (t2.a,t2.b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)
203 AND t1.id = t2.id) ;
204
205
206 # Select not word like "tutorial" using subquery
207 SELECT id FROM t1 WHERE NOT EXISTS (SELECT t2.id FROM t1 AS t2 WHERE
208 MATCH (t2.a,t2.b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)
209 AND t1.id = t2.id) ;
210
211 DROP TABLE t1;
212
213 # boolean search
214 CREATE TABLE t1 (
215 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
216 a VARCHAR(200),
217 b TEXT ,
218 FULLTEXT (a,b)
219 ) ENGINE = InnoDB;
220
221 INSERT INTO t1(a,b) VALUES('MySQL has now support', 'for full-text search'),
222 ('Full-text indexes', 'are called collections'),
223 ('Only MyISAM tables','support collections'),
224 ('Function MATCH ... AGAINST()','is used to do a search'),
225 ('Full-text search in MySQL', 'implements vector space model');
226
227 # Select word "tutorial" in the table
228 SELECT id FROM t1 WHERE t1.id = (SELECT MAX(t2.id) FROM t1 AS t2 WHERE
229 MATCH(t2.a,t2.b) AGAINST("+support +collections" IN BOOLEAN MODE));
230 SELECT id FROM t1 WHERE t1.id != (SELECT MIN(t2.id) FROM t1 AS t2 WHERE
231 MATCH(t2.a,t2.b) AGAINST("+search" IN BOOLEAN MODE));
232
233 SELECT id FROM t1 WHERE t1.id IN (SELECT t2.id FROM t1 AS t2 WHERE
234 MATCH (t2.a,t2.b) AGAINST ("+call* +coll*" IN BOOLEAN MODE));
235
236 SELECT id FROM t1 WHERE EXISTS (SELECT id FROM t1 AS t2 WHERE
237 MATCH t2.a,t2.b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE) AND t2.id=t1.id);
238
239
240 #query expansion search
241 # result differ for query expansion search even wo subquery
242 #SELECT id FROM t1 WHERE t1.id = ( SELECT MAX(t2.id) FROM t1 AS t2 WHERE
243 #MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION));
244 #SELECT id FROM t1 WHERE t1.id IN ( SELECT t2.id FROM t1 AS t2 WHERE
245 #MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION));
246 #SELECT id FROM t1 WHERE ( SELECT COUNT(*) FROM t1 AS t2 WHERE
247 #MATCH(t2.a,t2.b) AGAINST ("indexes collections" WITH QUERY EXPANSION)) >= 1
248 #AND t1.id <=3 ;
249
250 # proximity search
251 # insert for proximity search
252 INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...');
253 # Insert into table with similar word of different distances
254 INSERT INTO t1 (a,b) VALUES
255 ('test proximity search, test, proximity and phrase',
256 'search, with proximity innodb');
257
258 INSERT INTO t1 (a,b) VALUES
259 ('test proximity fts search, test, proximity and phrase',
260 'search, with proximity innodb');
261
262 INSERT INTO t1 (a,b) VALUES
263 ('test more proximity fts search, test, more proximity and phrase',
264 'search, with proximity innodb');
265
266
267 SELECT id FROM t1 WHERE t1.id = (SELECT MAX(t2.id) FROM t1 AS t2 WHERE
268 MATCH(t2.a,t2.b) AGAINST ('"proximity search"@2' IN BOOLEAN MODE));
269 SELECT id FROM t1 WHERE t1.id > (SELECT MIN(t2.id) FROM t1 AS t2 WHERE
270 MATCH(t2.a,t2.b) AGAINST ('"proximity search"@2' IN BOOLEAN MODE));
271
272 SELECT id FROM t1 WHERE t1.id IN (SELECT t2.id FROM t1 AS t2 WHERE
273 MATCH (t2.a,t2.b) AGAINST ('"proximity search"@2' IN BOOLEAN MODE));
274
275 SELECT id FROM t1 WHERE EXISTS (SELECT id FROM t1 AS t2 WHERE
276 MATCH t2.a,t2.b AGAINST ('"proximity search"@2' IN BOOLEAN MODE)
277 AND t2.id=t1.id);
278
279 SELECT id FROM t1 WHERE EXISTS (SELECT id FROM t1 AS t2 WHERE
280 MATCH t2.a,t2.b AGAINST ('"more test proximity"@3' IN BOOLEAN MODE)
281 AND t2.id=t1.id);
282
283 SELECT id FROM t1 WHERE EXISTS (SELECT id FROM t1 AS t2 WHERE
284 MATCH t2.a,t2.b AGAINST ('"more test proximity"@2' IN BOOLEAN MODE)
285 AND t2.id=t1.id);
286
287
288 #------------------------------------------------------------------------------
289 # create table AS SELECT from fts indexed table
290 #------------------------------------------------------------------------------
291 CREATE TABLE t2 ENGINE = InnoDB AS SELECT id FROM t1 WHERE
292 MATCH a,b AGAINST ('support') ;
293 SHOW CREATE TABLE t2;
294 SELECT id FROM t2;
295 DROP TABLE t2;
296
297 CREATE TABLE t2 ENGINE = InnoDB AS SELECT id FROM t1 WHERE
298 MATCH a,b AGAINST("+support +collections" IN BOOLEAN MODE);
299 SHOW CREATE TABLE t2;
300 SELECT id FROM t2;
301 DROP TABLE t2;
302
303 CREATE TABLE t2 ENGINE = InnoDB AS SELECT id FROM t1 WHERE
304 MATCH a,b AGAINST ('"proximity search"@10' IN BOOLEAN MODE);
305 SHOW CREATE TABLE t2;
306 SELECT id FROM t2;
307 DROP TABLE t2;
308
309 DROP TABLE t1;
310
311
312 #------------------------------------------------------------------------------
313 # Verift FTS with NULL records
314 #------------------------------------------------------------------------------
315 # Create FTS table
316 EVAL CREATE TABLE t1 (
317 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
318 a VARCHAR(200),
319 b TEXT
320 ) ENGINE = InnoDB;
321
322 # Create the FTS index again
323 CREATE FULLTEXT INDEX idx on t1 (a,b);
324
325
326 # Insert rows
327 INSERT INTO t1 (a,b) VALUES
328 ('MySQL from Tutorial','DBMS stands for DataBase ...');
329
330 let $counter = 50;
331 --disable_query_log
332 WHILE ($counter > 0) {
333 INSERT INTO t1 (a,b) VALUES (NULL,NULL);
334 dec $counter;
335 }
336 --enable_query_log
337 INSERT INTO t1 (a,b) VALUES
338 ('when To Use MySQL Well','After that you went through a ...');
339
340 let $counter = 50;
341 --disable_query_log
342 WHILE ($counter > 0) {
343 INSERT INTO t1 (a,b) VALUES (NULL,NULL);
344 dec $counter;
345 }
346 --enable_query_log
347 INSERT INTO t1 (a,b) VALUES
348 ('where will Optimizing MySQL','what In this tutorial we will show ...');
349
350 INSERT INTO t1 (a,b) VALUES
351 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
352 ('MySQL vs. YourSQL','In the following database comparison ...'),
353 ('MySQL Security','When configured properly, MySQL null...');
354
355 SELECT COUNT(*) FROM t1;
356 SELECT COUNT(*) FROM t1 WHERE a IS NULL;
357 SELECT COUNT(*) FROM t1 WHERE b IS NOT NULL;
358
359 SELECT id FROM t1
360 WHERE MATCH (a,b)
361 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
362
363 SELECT id FROM t1
364 WHERE MATCH (a,b)
365 AGAINST (NULL IN NATURAL LANGUAGE MODE);
366 SELECT id FROM t1
367 WHERE MATCH (a,b)
368 AGAINST (NULL WITH QUERY EXPANSION);
369 SELECT id FROM t1
370 WHERE MATCH (a,b)
371 AGAINST ('null' IN NATURAL LANGUAGE MODE);
372 # Boolean search
373 # Select rows contain "MySQL" but not "YourSQL"
374 SELECT id FROM t1 WHERE MATCH (a,b)
375 AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
376 SELECT id FROM t1 WHERE MATCH (a,b)
377 AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE) AND (a IS NOT NULL OR b IS NOT NULL);
378 SELECT id FROM t1 WHERE MATCH (a,b)
379 AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE) AND (a IS NULL AND b IS NOT NULL);
380
381 # Select rows contain at least one of the two words
382 SELECT id FROM t1 WHERE MATCH (a,b)
383 AGAINST ('DBMS Security' IN BOOLEAN MODE);
384
385 # Test query expansion
386 SELECT COUNT(*) FROM t1
387 WHERE MATCH (a,b)
388 AGAINST ('database' WITH QUERY EXPANSION);
389
390 # proximity
391 SELECT id FROM t1
392 WHERE MATCH (a,b)
393 AGAINST ('"following database"@10' IN BOOLEAN MODE);
394
395
396 DROP TABLE t1;
397
398
399
400 #------------------------------------------------------------------------------
401 # More FTS test from peter's testing
402 #------------------------------------------------------------------------------
403 set names utf8;
404
405
406 --echo "----------Test1---------"
407 # Create FTS table
408 create table t50 (s1 varchar(60) character set utf8 collate utf8_bin) engine = innodb;
409 create fulltext index i on t50 (s1);
410 # INNODB_FTS: Assert - fixed
411 # Assert : InnoDB: Failing assertion: rbt_validate(result_doc->tokens)
412 insert into t50 values ('ABCDE'),('FGHIJ'),('KLMNO'),('VÐƷWİ');
413 # it was giving empty result set instead of one record
414 select * from t50 where match(s1) against ('VÐƷWİ');
415 drop table t50;
416
417
418 --echo "----------Test2---------"
419 create table t50 (s1 int unsigned primary key auto_increment, s2
420 varchar(60) character set utf8) engine = innodb;
421 create fulltext index i on t50 (s2);
422 insert into t50 (s2) values ('FGHIJ'),('KLMNO'),('VÐƷWİ'),('ABCDE');
423 # INNODB_FTS: RESULT DIFF
424 # Order by does not sort result.
425 # Optimizer's Evgeny is investigate a similar issue. InnoDB FTS is used only
426 # for FT search, and should not be used as regular index for such order by query.
427 # Correct the result file when fixed.
428 select * from t50 order by s2;
429 drop table t50;
430
431
432 --echo "----------Test3---------"
433 create table t50 (id int unsigned primary key auto_increment, s2
434 varchar(60) character set utf8) engine = innodb;
435 create fulltext index i on t50 (s2);
436 insert into t50 (s2) values ('FGHIJ'),('KLMNO'),('VÐƷWİ'),('ABCDE');
437 set @@autocommit=0;
438 update t50 set s2 = lower(s2);
439 update t50 set s2 = upper(s2);
440 commit;
441 select * from t50 where match(s2) against ('VÐƷWİ FGHIJ KLMNO ABCDE' in boolean mode);
442 select * from t50;
443 drop table t50;
444 set @@autocommit=1;
445
446 --echo "----------Test4---------"
447 create table t50 (id int unsigned primary key auto_increment, s2
448 varchar(60) character set utf8) engine = innodb;
449 create fulltext index i on t50 (s2);
450 insert into t50 (s2) values ('FGHIJ'),('KLMNO'),('VÐƷWİ'),('ABCD*');
451 select * from t50 where match(s2) against ('abcd*' in natural language
452 mode);
453 # INNODB_FTS: RESULT DIFF(Expected). InnoDB do not index "*", so
454 # word "ABCD" indexed, instead of "ABCD*"
455 select * from t50 where match(s2) against ('abcd*' in boolean mode);
456 drop table t50;
457
458
459 --echo "----------Test5---------"
460 create table t50 (s1 int, s2 varchar(200), fulltext key(s2)) engine = innodb;
461 set @@autocommit=0;
462 insert into t50 values (1,'Sunshine'),(2,'Lollipops');
463 select * from t50 where match(s2) against('Rainbows');
464 rollback;
465 select * from t50;
466 drop table t50;
467 set @@autocommit=1;
468
469 --echo "----------Test6---------"
470 CREATE TABLE t1 (
471 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
472 a VARCHAR(200),
473 b TEXT
474 ) ENGINE = InnoDB;
475
476 # Insert rows
477 INSERT INTO t1 (a,b) VALUES
478 ('aab` MySQL Tutorial','DBMS stands for DataBase ...') ,
479 ('aas How To Use MySQL Well','After you went through a ...'),
480 ('aac Optimizing MySQL','In this tutorial we will show ...');
481 # Insert rows
482 INSERT INTO t1 (a,b) VALUES
483 ('aac 1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
484 ('aab MySQL vs. YourSQL','In the following database comparison ...'),
485 ('aaa MySQL Security','When configured properly, MySQL ...');
486 # Create the FTS index Using Alter Table
487 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
488
489 -- disable_query_log
490 -- disable_result_log
491 ANALYZE TABLE t1;
492 -- enable_result_log
493 -- enable_query_log
494
495 SELECT * FROM t1 ORDER BY MATCH(a,b) AGAINST ('aac') DESC;
496 SELECT * FROM t1 ORDER BY MATCH(a,b) AGAINST ('aab') DESC;
497
498 --echo "----------Test7---------"
499 select * from t1 where match(a,b) against ('aaa')
500 union select * from t1 where match(a,b) against ('aab')
501 union select * from t1 where match(a,b) against ('aac');
502
503 select * from t1 where match(a,b) against ('aaa')
504 or match(a,b) against ('aab')
505 or match(a,b) against ('aac');
506
507 DROP TABLE t1;
508
509 --echo "----------Test8---------"
510 # Create FTS table
511 CREATE TABLE t1 (
512 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
513 a VARCHAR(200),
514 b TEXT
515 ) ENGINE = InnoDB;
516
517 # Insert rows
518 INSERT INTO t1 (a,b) VALUES
519 ('MySQL Tutorial','DBMS stands for DataBase ... abcd') ,
520 ('How To Use MySQL Well','After you went through a q ...abdd'),
521 ('Optimizing MySQL','In this tutorial we will show ...abed');
522
523 # Create the FTS index Using Alter Table
524 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
525 EVAL SHOW CREATE TABLE t1;
526
527 # Insert rows
528 INSERT INTO t1 (a,b) VALUES
529 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. q ...'),
530 ('MySQL vs. YourSQL use','In the following database comparison ...'),
531 ('MySQL Security','When run configured properly, MySQL ...');
532
533 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('run');
534 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('use');
535 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('went');
536 # rows should be matched as 'q' is single char its not indexed
537 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('run') AND NOT MATCH(a,b) AGAINST ('q');
538 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('use') AND NOT MATCH(a,b) AGAINST ('q');
539 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('went') AND NOT MATCH(a,b) AGAINST ('q');
540
541 --echo "----------Test9---------"
542 CREATE TABLE t2 AS SELECT * FROM t1;
543 ALTER TABLE t2 ENGINE=MYISAM;
544 CREATE FULLTEXT INDEX i ON t2 (a,b);
545 SET @x = (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('run'));
546 SET @x = @x + (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('use'));
547 SET @x = @x + (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('went'));
548 SET @x = @x + (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('run'));
549 SET @x2 = (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('run'));
550 SET @x2 = @x2 + (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('use'));
551 SET @x2 = @x2 + (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('went'));
552 SET @x2 = @x2 + (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('run'));
553 # Innodb returns value for x which is correct
554 SELECT @x, @x2;
555
556
557 DROP TABLE t2;
558
559 --echo "----------Test10---------"
560 CREATE TABLE t2 AS SELECT * FROM t1;
561 ALTER TABLE t2 ENGINE=MYISAM;
562 CREATE FULLTEXT INDEX i ON t2 (a,b);
563 SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('abc*' IN BOOLEAN MODE);
564 SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('abc*' IN BOOLEAN MODE);
565
566 DROP TABLE t2;
567
568
569 --echo "----------Test11---------"
570 CREATE TABLE t2 AS SELECT * FROM t1;
571 ALTER TABLE t2 ENGINE = MYISAM;
572 CREATE FULLTEXT INDEX i ON t2 (a,b);
573 ALTER TABLE t2 ENGINE=InnoDB;
574 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('run');
575 SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('abc*' IN BOOLEAN MODE);
576 DROP TABLE t2,t1;
577
578
579 --echo "----------Test13---------"
580 set names utf8;
581
582 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8_SPANISH_CI) ENGINE = InnoDB;
583 CREATE FULLTEXT INDEX i ON t1 (s2);
584 INSERT INTO t1 VALUES (1,'aaCen'),(2,'aaCha'),(3,'aaCio'),(4,'aaçen'),(5,'aaçha'),(6,'aaçio');
585 SELECT * FROM t1 WHERE MATCH(s2) AGAINST ('aach*' IN BOOLEAN MODE);
586 SELECT * FROM t1 WHERE MATCH(s2) AGAINST ('aaC*' IN BOOLEAN MODE);
587 DROP TABLE t1;
588
589 --echo "----------Test14---------"
590 CREATE TABLE t1(s1 INT , s2 VARCHAR(100) CHARACTER SET sjis) ENGINE = InnoDB;
591 CREATE FULLTEXT INDEX i ON t1 (s2);
592 INSERT INTO t1 VALUES (1,'ペペペ'),(2,'テテテ'),(3,'ルルル'),(4,'グググ');
593 # Innodb Asset : file ha_innodb.cc line 4557
594 #SELECT * FROM t1 WHERE MATCH(s2) AGAINST ('テテ*' IN BOOLEAN MODE);
595 DROP TABLE t1;
596
597
598 --echo "----------Test15---------"
599 CREATE TABLE t1 (s1 VARCHAR (60) CHARACTER SET UTF8 COLLATE UTF8_UNICODE_520_CI) ENGINE = MyISAM;
600 CREATE FULLTEXT INDEX i ON t1 (s1);
601 INSERT INTO t1 VALUES
602 ('a'),('b'),('c'),('d'),('ŁŁŁŁ'),('LLLL'),(NULL),('ŁŁŁŁ ŁŁŁŁ'),('LLLLLLLL');
603 SELECT * FROM t1 WHERE MATCH(s1) AGAINST ('LLLL' COLLATE UTF8_UNICODE_520_CI);
604 CREATE TABLE t2 (s1 VARCHAR(60) CHARACTER SET UTF8 COLLATE UTF8_POLISH_CI) ENGINE = InnoDB;
605 CREATE FULLTEXT INDEX i ON t2 ( s1);
606 INSERT INTO t2 VALUES
607 ('a'),('b'),('c'),('d'),('ŁŁŁŁ'),('LLLL'),(NULL),('ŁŁŁŁ ŁŁŁŁ'),('LLLLLLLL');
608 SELECT * FROM t2 WHERE MATCH(s1) AGAINST ('LLLL' COLLATE UTF8_UNICODE_520_CI);
609 --disable_warnings
610 DROP TABLE t1,t2;
611 --enable_warnings
612
613 --echo "----------Test16---------"
614 CREATE TABLE t1 (s1 INT, s2 VARCHAR(50) CHARACTER SET UTF8) ENGINE = InnoDB;
615 CREATE FULLTEXT INDEX i ON t1(s2);
616 INSERT INTO t1 VALUES (2, 'ğė Daśi p ');
617 SELECT * FROM t1 WHERE MATCH(s2) AGAINST ('+p +"ğė Daśi*"' IN BOOLEAN MODE);
618 DROP TABLE t1;
619
620
621 --echo "----------Test19---------"
622 #19 Failure with Boolean quoted search
623 CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF8) ENGINE = InnoDB;
624 INSERT INTO t1 VALUES (1,'İóëɠ');
625 CREATE FULLTEXT INDEX i ON t1 (char_column);
626 SELECT * FROM t1 WHERE MATCH(char_column) AGAINST ('"İóëɠ"' IN BOOLEAN MODE);
627 DROP TABLE t1;
628
629 --echo "----------Test20---------"
630 #20 Crash with utf32 and boolean mode.
631 CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF32, char_column2 VARCHAR(60) character set utf8) ENGINE = InnoDB;
632 INSERT INTO t1 (char_column) VALUES ('abcde'),('fghij'),('klmno'),('qrstu');
633 UPDATE t1 SET char_column2 = char_column;
634 CREATE FULLTEXT INDEX i ON t1 (char_column2);
635 --error ER_FT_MATCHING_KEY_NOT_FOUND
636 SELECT * FROM t1 WHERE MATCH(char_column) AGAINST ('abc*' IN BOOLEAN MODE);
637 DROP TABLE t1;
638
639 --echo "----------Test22---------"
640 # case 22
641 CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF8) ENGINE = InnoDB;
642 INSERT INTO t1 VALUES (1,'aaa'),(2,'bbb'),(3,'ccc');
643 CREATE FULLTEXT INDEX i ON t1 (char_column);
644 HANDLER t1 OPEN;
645 --error ER_KEY_DOESNT_SUPPORT
646 HANDLER t1 READ i = ('aaa');
647 DROP TABLE t1;
648 #23. Duplicate key error when there are no unique indexes (procedure test)
649 #24 Failure after cascading update - already have tests
650
651 --echo "----------Test25---------"
652 #25 Failure with Croatian boolean truncated search.
653 CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF8 COLLATE UTF8_CROATIAN_CI) ENGINE=InnoDB;
654 INSERT INTO t1 VALUES (1,'LJin'),(2,'ljin'),(3,'lmin'),(4,'LJLJLJLJLJ');
655 CREATE FULLTEXT INDEX i ON t1 (char_column);
656 #inndob:error incorrect result correct it after fix
657 SELECT count(*) FROM t1 WHERE MATCH (char_column) AGAINST ('lj*' IN BOOLEAN MODE);
658 DROP TABLE t1;
659
660 #26. Index error when run procedure call from multiple clients
661
662 --echo "----------Test27---------"
663 #27 Crash after server restart
664 CREATE TABLE t1 (id INT,char_column VARCHAR(60));
665 CREATE TABLE t2 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, a TEXT)ENGINE=InnoDB;
666 ALTER TABLE t2 DROP a;
667 CREATE FULLTEXT INDEX i ON t1 (char_column);
668 INSERT INTO t1 values (1,'aaa');
669
670 CREATE TABLE mdev20987_1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=InnoDB;
671 CREATE TABLE mdev20987_2(f1 INT NOT NULL, f2 CHAR(100),
672 FULLTEXT(f2),
673 FOREIGN KEY(f1) REFERENCES mdev20987_1(f1))ENGINE=InnoDB;
674 INSERT INTO mdev20987_1 VALUES(1);
675 INSERT INTO mdev20987_2 VALUES(1, 'mariadb');
676
677 CREATE TABLE mdev22358 (a INT, b TEXT, FULLTEXT KEY ftidx (b)) ENGINE=InnoDB;
678 ALTER TABLE mdev22358 DROP KEY ftidx;
679 INSERT INTO mdev22358 (a) VALUES (2),(2);
680 --error ER_DUP_ENTRY
681 ALTER TABLE mdev22358 ADD UNIQUE KEY uidx (a), ADD FULLTEXT KEY ftidx (b);
682 --source include/restart_mysqld.inc
683 SHOW CREATE TABLE t2;
684 DELETE FROM t1 WHERE MATCH(char_column) AGAINST ('bbb');
685 DROP TABLE t1, t2, mdev20987_2, mdev20987_1, mdev22358;
686
687 --echo "----------Test28---------"
688 create table `fts_test`(`a` text,fulltext key(`a`))engine=innodb;
689 set session autocommit=0;
690 insert into `fts_test` values ('');
691 savepoint `b`;
692 savepoint `b`;
693 set session autocommit=1;
694 DROP TABLE fts_test;
695
696 # Continue test savepoint related operations. With a commit after
697 # multiple rollback to savepoints
698 --echo "----------Test29---------"
699 CREATE TABLE articles (
700 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
701 title VARCHAR(200),
702 body TEXT,
703 FULLTEXT (title,body)
704 ) ENGINE=InnoDB;
705
706 INSERT INTO articles (title,body) VALUES
707 ('MySQL Tutorial','DBMS stands for DataBase ...');
708
709
710 start transaction;
711
712 INSERT INTO articles (title,body) VALUES
713 ('How To Use MySQL Well','After you went through a ...');
714
715 savepoint `a1`;
716
717 INSERT INTO articles (title,body) VALUES
718 ('Optimizing MySQL','In this tutorial we will show ...');
719
720 savepoint `a2`;
721
722 INSERT INTO articles (title,body) VALUES
723 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...');
724
725 savepoint `a3`;
726
727 INSERT INTO articles (title,body) VALUES
728 ('MySQL vs. YourSQL','In the following database comparison ...');
729
730 savepoint `a4`;
731
732 # FTS do not parse those uncommitted rows, only one row should show up
733 SELECT * FROM articles
734 WHERE MATCH (title,body)
735 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
736
737 rollback to savepoint a3;
738
739 # The last inserted row should not be there
740 select title, body from articles;
741
742 INSERT INTO articles (title,body) VALUES
743 ('MySQL Security','When configured properly, MySQL ...');
744
745 savepoint `a5`;
746
747 select title, body from articles;
748
749 rollback to savepoint a2;
750
751 select title, body from articles;
752
753 commit;
754
755 SELECT * FROM articles
756 WHERE MATCH (title,body)
757 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
758
759 SELECT * FROM articles
760 WHERE MATCH (title,body)
761 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
762
763 DROP TABLE articles;
764
765 # Continue test savepoint related operations. With a rollback after
766 # multiple rollback to savepoints
767 --echo "----------Test30---------"
768 CREATE TABLE articles (
769 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
770 title VARCHAR(200),
771 body TEXT,
772 FULLTEXT (title,body)
773 ) ENGINE=InnoDB;
774
775 INSERT INTO articles (title,body) VALUES
776 ('MySQL Tutorial','DBMS stands for DataBase ...');
777
778 start transaction;
779
780 INSERT INTO articles (title,body) VALUES
781 ('How To Use MySQL Well','After you went through a ...');
782
783 savepoint `a1`;
784
785 INSERT INTO articles (title,body) VALUES
786 ('Optimizing MySQL','In this tutorial we will show ...');
787
788 savepoint `a2`;
789
790 INSERT INTO articles (title,body) VALUES
791 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...');
792
793 savepoint `a3`;
794
795 INSERT INTO articles (title,body) VALUES
796 ('MySQL vs. YourSQL','In the following database comparison ...');
797
798 savepoint `a4`;
799
800 # FTS do not parse those uncommitted rows, only one row should show up
801 SELECT * FROM articles
802 WHERE MATCH (title,body)
803 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
804
805 rollback to savepoint a3;
806
807 # The last inserted row should not be there
808 select title, body from articles;
809
810 INSERT INTO articles (title,body) VALUES
811 ('MySQL Security','When configured properly, MySQL ...');
812
813 savepoint `a5`;
814
815 select title, body from articles;
816
817 rollback to savepoint a2;
818
819 select title, body from articles;
820
821 rollback;
822
823 SELECT * FROM articles
824 WHERE MATCH (title,body)
825 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
826
827 SELECT * FROM articles
828 WHERE MATCH (title,body)
829 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
830
831 DROP TABLE articles;
832
833 # Test for Bug #13907075 - DIFFERENT RESULTS FOR DIFFERENT TERM ORDER
834 # WITH INNODB BOOLEAN FULLTEXT SEARCH. The FTS_IGNORE ("-") operation
835 # is orderless
836 # Create FTS table
837 CREATE TABLE articles (
838 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
839 title VARCHAR(200),
840 body TEXT,
841 FULLTEXT (title,body)
842 ) ENGINE=InnoDB;
843
844 # Insert six rows
845 INSERT INTO articles (title,body) VALUES
846 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
847 ('How To Use MySQL Well','After you went through a ...'),
848 ('Optimizing MySQL','In this tutorial we will show ...'),
849 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
850 ('MySQL vs. YourSQL','In the following database comparison ...'),
851 ('MySQL Security','When configured properly, MySQL ...');
852
853 -- disable_result_log
854 ANALYZE TABLE articles;
855 -- enable_result_log
856
857 SELECT *, MATCH(title, body) AGAINST ('-database +MySQL' IN BOOLEAN MODE) AS score from articles;
858
859 SELECT *, MATCH(title, body) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score FROM articles;
860
861 # With subquery
862 SELECT * FROM articles where MATCH(title, body) AGAINST ('MySQL - (database - tutorial)' IN BOOLEAN MODE);
863
864 SELECT * FROM articles where MATCH(title, body) AGAINST ('MySQL - (- tutorial database)' IN BOOLEAN MODE);
865
866 # More complex query
867 SELECT * FROM articles where MATCH(title, body) AGAINST ('MySQL - (- tutorial database) -Tricks' IN BOOLEAN MODE);
868
869 SELECT * FROM articles where MATCH(title, body) AGAINST ('-Tricks MySQL - (- tutorial database)' IN BOOLEAN MODE);
870
871 DROP TABLE articles;
872
873 # Test for Bug 13940669 - 64901: INNODB: ASSERTION FAILURE IN
874 # THREAD 34387022112 IN FILE REM0CMP.CC LINE 5
875
876 create table t1 (FTS_DOC_ID bigint unsigned auto_increment not null primary key,
877 title varchar(200),body text,fulltext(title,body)) engine=innodb;
878
879 insert into t1 set body='test';
880
881 select * from t1 where match(title,body) against('%test');
882
883 select * from t1 where match(title,body) against('%');
884
885 select * from t1 where match(title,body) against('%%%%');
886
887 drop table t1;
888
889 # Test for Bug 13881758 - 64745: CREATE FULLTEXT INDEX CAUSES CRASH
890 # Create a database with empty space in its name
891 CREATE DATABASE `benu database`;
892
893 USE `benu database`;
894
895 # Create FTS table
896 CREATE TABLE t1 (
897 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
898 a VARCHAR(200),
899 b TEXT
900 ) ENGINE = InnoDB;
901
902 # Insert rows
903 INSERT INTO t1 (a,b) VALUES
904 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
905 ('How To Use MySQL Well','After you went through a ...'),
906 ('Optimizing MySQL','In this tutorial we will show ...');
907
908 # Create the FTS index Using Alter Table
909 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
910 EVAL SHOW CREATE TABLE t1;
911
912 # Insert rows
913 INSERT INTO t1 (a,b) VALUES
914 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
915 ('MySQL vs. YourSQL','In the following database comparison ...'),
916 ('MySQL Security','When configured properly, MySQL ...');
917
918 # Select word "tutorial" in the table
919 SELECT id FROM t1 WHERE MATCH (a,b)
920 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
921
922 # boolean mode
923 select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
924 select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
925 select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
926 select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
927 select id, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
928 select id, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;
929 select id from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE);
930 select id from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE);
931 select id from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE);
932
933 DROP DATABASE `benu database`;
934
935 USE test;
936
937 # Test for Bug #14101706 - CRASH WITH DDL IN ROW_MERGE_BUILD_INDEXES
938 # WHEN FULLTEXT INDEXES EXIST
939
940 CREATE TABLE `t21` (`a` text, `b` int not null,
941 fulltext key (`a`), fulltext key (`a`)
942 ) ENGINE=INNODB DEFAULT CHARSET=LATIN1;
943
944 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
945 ALTER TABLE `t21` ADD UNIQUE INDEX (`b`), ALGORITHM=INPLACE;
946 ALTER TABLE `t21` ADD UNIQUE INDEX (`b`);
947
948 DROP TABLE t21;
949
950 CREATE TABLE `t21` (`a` text, `b` int not null,
951 fulltext key (`a`)) ENGINE=INNODB DEFAULT CHARSET=LATIN1;
952
953 ALTER TABLE `t21` ADD UNIQUE INDEX (`b`);
954
955 DROP TABLE t21;
956
957 # Test primary index rebuild
958 CREATE TABLE t1 (
959 id INT NOT NULL,
960 a VARCHAR(200),
961 b TEXT
962 ) ENGINE = InnoDB;
963
964 # Insert rows
965 INSERT INTO t1 VALUES
966 (1, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
967 (2, 'How To Use MySQL Well','After you went through a ...'),
968 (3, 'Optimizing MySQL','In this tutorial we will show ...');
969
970 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
971
972 ALTER TABLE t1 ADD UNIQUE INDEX (`id`);
973
974 # Select word "tutorial" in the table
975 SELECT id FROM t1 WHERE MATCH (a,b)
976 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
977
978 # boolean mode
979 select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
980 select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
981 select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
982 select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
983 select id, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
984
985 DROP TABLE t1;
986
987 # Test create the FTS and primary index in the same clause
988 CREATE TABLE t1 (
989 id INT NOT NULL,
990 a VARCHAR(200),
991 b TEXT
992 ) ENGINE = InnoDB;
993
994 # Insert rows
995 INSERT INTO t1 VALUES
996 (1, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
997 (2, 'How To Use MySQL Well','After you went through a ...'),
998 (3, 'Optimizing MySQL','In this tutorial we will show ...');
999
1000 ALTER TABLE t1 ADD UNIQUE INDEX (`id`), ADD FULLTEXT INDEX idx (a,b);
1001
1002 # Select word "tutorial" in the table
1003 SELECT id FROM t1 WHERE MATCH (a,b)
1004 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
1005
1006 # boolean mode
1007 select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
1008 select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
1009 select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
1010 select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
1011
1012 DROP TABLE t1;
1013
1014 # Create FTS table with FTS_DOC_ID already existed
1015 CREATE TABLE t1 (
1016 FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
1017 a VARCHAR(200),
1018 b TEXT
1019 ) ENGINE = InnoDB;
1020
1021 # Insert rows
1022 INSERT INTO t1 VALUES
1023 (1, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
1024 (2, 'How To Use MySQL Well','After you went through a ...'),
1025 (3, 'Optimizing MySQL','In this tutorial we will show ...');
1026
1027 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
1028
1029 ALTER TABLE t1 ADD UNIQUE INDEX (`FTS_DOC_ID`);
1030
1031 # Select word "tutorial" in the table
1032 SELECT FTS_DOC_ID FROM t1 WHERE MATCH (a,b)
1033 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
1034
1035 # boolean mode
1036 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
1037 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
1038 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
1039 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
1040 select FTS_DOC_ID, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
1041
1042 DROP TABLE t1;
1043
1044 # Create FTS table with FTS_DOC_ID and FTS_DOC_ID_INDEX
1045 CREATE TABLE t1 (
1046 FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
1047 a VARCHAR(200),
1048 b TEXT
1049 ) ENGINE = InnoDB;
1050
1051 # Insert rows
1052 INSERT INTO t1 VALUES
1053 (1, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
1054 (2, 'How To Use MySQL Well','After you went through a ...'),
1055 (3, 'Optimizing MySQL','In this tutorial we will show ...');
1056
1057 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b), ADD UNIQUE INDEX FTS_DOC_ID_INDEX (FTS_DOC_ID);
1058
1059 # Select word "tutorial" in the table
1060 SELECT FTS_DOC_ID FROM t1 WHERE MATCH (a,b)
1061 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
1062
1063 # boolean mode
1064 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
1065 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
1066 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
1067 select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
1068 select FTS_DOC_ID, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
1069
1070 DROP TABLE t1;
1071
1072 # Test for bug #14079609 - FTS: CRASH IN FTS_TRX_TABLE_CMP WITH SAVEPOINTS, XA
1073
1074 CREATE TABLE t2 (`b` char(2),fulltext(`b`)) ENGINE=INNODB
1075 DEFAULT CHARSET=LATIN1;
1076
1077 CREATE TABLE t3 LIKE t2;
1078
1079 INSERT INTO `t2` VALUES();
1080
1081 COMMIT WORK AND CHAIN;
1082
1083 INSERT INTO `t3` VALUES ();
1084 UPDATE `t2` SET `b` = 'a';
1085
1086 SAVEPOINT BATCH1;
1087
1088 DROP TABLE t2;
1089 DROP TABLE t3;
1090
1091 # Create FTS table
1092 CREATE TABLE t1 (
1093 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
1094 a VARCHAR(200),
1095 b TEXT
1096 ) ENGINE = InnoDB;
1097
1098 # Insert rows
1099 INSERT INTO t1 (a,b) VALUES
1100 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
1101 ('How To Use MySQL Well','After you went through a ...'),
1102 ('Optimizing MySQL','In this tutorial we will show ...');
1103
1104 # Create the FTS index Using Alter Table
1105 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
1106
1107 COMMIT WORK AND CHAIN;
1108
1109 INSERT INTO t1 (a,b) VALUES
1110 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
1111 ('MySQL vs. YourSQL','In the following database comparison ...'),
1112 ('MySQL Security','When configured properly, MySQL ...');
1113
1114 SAVEPOINT BATCH1;
1115
1116 SELECT id FROM t1 WHERE MATCH (a,b)
1117 AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);
1118
1119 INSERT INTO t1 (a,b) VALUES
1120 ('1002 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
1121 ('MySQL vs. YourSQL','In the following database comparison ...'),
1122 ('MySQL Security','When configured properly, MySQL ...');
1123
1124
1125 ROLLBACK TO SAVEPOINT BATCH1;
1126
1127 COMMIT;
1128
1129 SELECT id FROM t1 WHERE MATCH (a,b)
1130 AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);
1131
1132 DROP TABLE t1;
1133
1134 # Test for Bug 14588091 - FTS: BUFFER OVERFLOW IN FTS_AST_CREATE_NODE_TEXT
1135 CREATE TABLE `t` (`a` char(20) character set utf8 default null,
1136 fulltext key (`a`)) ENGINE=INNODB;
1137 INSERT INTO `t` VALUES ('a');
1138 INSERT INTO `t` VALUES ('aaa');
1139
1140 # 0x22 is the '"', 0xdd is not encoded in utf8
1141 SELECT MATCH(`a`) AGAINST (0x22dd22) FROM `t`;
1142 SELECT MATCH(`a`) AGAINST (0x2222) FROM `t`;
1143 SELECT MATCH(`a`) AGAINST (0x22) FROM `t`;
1144
1145 # this should show one match
1146 SELECT MATCH(`a`) AGAINST (0x2261616122) FROM `t`;
1147
1148 # again 0xdd should be ignored
1149 SELECT MATCH(`a`) AGAINST (0x2261dd6122) FROM `t`;
1150
1151 SELECT MATCH(`a`) AGAINST (0x2261dd612222226122) FROM `t`;
1152
1153 DROP TABLE t;
1154
1155 # InnoDB FTS does not support index scan from handler
1156 CREATE TABLE t(a CHAR(1),FULLTEXT KEY(a)) ENGINE=INNODB;
1157 HANDLER t OPEN;
1158 --error ER_KEY_DOESNT_SUPPORT
1159 HANDLER t READ a NEXT;
1160 --error ER_KEY_DOESNT_SUPPORT
1161 HANDLER t READ a PREV;
1162 DROP TABLE t;
1163
1164 CREATE TABLE `%`(a TEXT, FULLTEXT INDEX(a)) ENGINE=INNODB;
1165 CREATE TABLE `A B`(a TEXT, FULLTEXT INDEX(a)) ENGINE=INNODB;
1166 DROP TABLE `%`;
1167 DROP TABLE `A B`;
1168
1169 CREATE TABLE `t-26`(a VARCHAR(10),FULLTEXT KEY(a)) ENGINE=INNODB;
1170 INSERT INTO `t-26` VALUES('117');
1171 DROP TABLE `t-26`;
1172
1173 # Test on phrase search with stopwords contained in the search string
1174 CREATE TABLE `t1` (
1175 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
1176 `content` TEXT NOT NULL,
1177 PRIMARY KEY (`id`),
1178 FULLTEXT INDEX `IDX_CONTEXT_FULLTEXT`(`content`)
1179 )
1180 ENGINE = InnoDB;
1181
1182 insert into t1 (content)
1183 values
1184 ('This is a story which has has a complicated phrase structure here in the
1185 middle'),
1186 ('This is a story which doesn''t have that text'),
1187 ('This is a story that has complicated the phrase structure');
1188
1189 select * from t1
1190 where match(content) against('"complicated phrase structure"' in boolean
1191 mode);
1192
1193 # Test single phrase search with "+" symbol, one row should be returned
1194 select * from t1
1195 where match(content) against('+"complicated phrase structure"' in boolean
1196 mode);
1197
1198 # Test phrase search with stopwords in between, one row should be returned
1199 select * from t1
1200 where match(content) against('"complicated the phrase structure"' in boolean
1201 mode);
1202
1203 # Test phrase search with multiple "+" symbols
1204 select * from t1 where match(content) against('+"this is a story which" +"complicated the phrase structure"' in boolean mode);
1205
1206 # Test phrase search with leading word is a stopword, such stopword would be
1207 # ignored
1208 select * from t1 where match(content) against('"the complicated the phrase structure"' in boolean mode);
1209
1210 # Test phrase search with non-matching stopword in between, no row should be
1211 # returned
1212 select * from t1 where match(content) against('"complicated a phrase structure"' in boolean mode);
1213
1214 DROP TABLE t1;
1215
1216 CREATE TABLE my (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
1217 c VARCHAR(32), FULLTEXT(c)) ENGINE = INNODB;
1218
1219 INSERT INTO my (c) VALUES ('green-iguana');
1220
1221 SELECT * FROM my WHERE MATCH(c) AGAINST ('green-iguana');
1222
1223 DROP TABLE my;
1224
1225 CREATE TABLE ift (
1226 `a` int(11) NOT NULL,
1227 `b` text,
1228 PRIMARY KEY (`a`),
1229 FULLTEXT KEY `b` (`b`)
1230 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1231
1232 INSERT INTO ift values (1, "skip");
1233 INSERT INTO ift values (2, "skip and networking");
1234 INSERT INTO ift values (3, "--skip-networking");
1235 INSERT INTO ift values (4, "-donot--skip-networking");
1236
1237 SELECT * FROM ift WHERE MATCH (b) AGAINST ('--skip-networking');
1238 SELECT * FROM ift WHERE MATCH (b) AGAINST ('skip-networking');
1239 SELECT * FROM ift WHERE MATCH (b) AGAINST ('----');
1240 SELECT * FROM ift WHERE MATCH (b) AGAINST ('-donot--skip-networking');
1241
1242 DROP TABLE ift;
1243
1244 # Test special cases of wildword.
1245 # Create FTS table
1246 CREATE TABLE articles (
1247 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
1248 title VARCHAR(200),
1249 body TEXT,
1250 FULLTEXT (title,body)
1251 ) ENGINE=InnoDB;
1252
1253 # Insert six rows
1254 INSERT INTO articles (title,body) VALUES
1255 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
1256 ('How To Use MySQL Well','After you went through a ...'),
1257 ('Optimizing MySQL','In this tutorial we will show ...'),
1258 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
1259 ('MySQL vs. YourSQL','In the following database comparison ...'),
1260 ('( that''s me )','When configured properly, MySQL ...');
1261
1262 SELECT * FROM articles WHERE MATCH (title,body)
1263 AGAINST ('( yours''s* )' IN BOOLEAN MODE);
1264
1265 SELECT * FROM articles WHERE MATCH (title,body)
1266 AGAINST ('s*' IN BOOLEAN MODE);
1267
1268 SELECT * FROM articles WHERE MATCH (title,body)
1269 AGAINST ('stands\'] | * | show[@database' IN NATURAL LANGUAGE MODE);
1270
1271 DROP TABLE articles;
1272
1273 # Test for BUG#16429688 - FTS: SYNTAX ERROR, UNEXPECTED '*', EXPECTING $END
1274 CREATE TABLE t1(a TEXT CHARACTER SET LATIN1, FULLTEXT INDEX(a)) ENGINE=INNODB;
1275
1276 --error ER_PARSE_ERROR
1277 SELECT * FROM t1 WHERE MATCH(a) AGAINST("*");
1278
1279 DROP TABLE t1;
1280
1281 # Test for BUG#16516193 - LITERAL PHRASES CANNOT BE COMBINED WITH + OR - OPERATOR
1282 # Create FTS table
1283 CREATE TABLE t1 (
1284 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
1285 a VARCHAR(200),
1286 FULLTEXT (a)
1287 ) ENGINE= InnoDB;
1288
1289 # Insert rows
1290 INSERT INTO t1 (a) VALUES
1291 ('Do you know MySQL is a good database'),
1292 ('How to build a good database'),
1293 ('Do you know'),
1294 ('Do you know MySQL'),
1295 ('How to use MySQL'),
1296 ('Do you feel good'),
1297 ('MySQL is good'),
1298 ('MySQL is good to know'),
1299 ('What is database');
1300
1301 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know mysql"' IN BOOLEAN MODE);
1302
1303 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+("know mysql")' IN BOOLEAN MODE);
1304
1305 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('("know mysql" good)' IN BOOLEAN MODE);
1306
1307 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+("know mysql" good)' IN BOOLEAN MODE);
1308
1309 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('(good "know mysql")' IN BOOLEAN MODE);
1310
1311 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+(good "know mysql")' IN BOOLEAN MODE);
1312
1313 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+("know mysql" "good database")' IN BOOLEAN MODE);
1314
1315 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know mysql" +"good database"' IN BOOLEAN MODE);
1316
1317 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know database"@4' IN BOOLEAN MODE);
1318
1319 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know database"@8' IN BOOLEAN MODE);
1320
1321 # Drop table
1322 DROP TABLE t1;
1323
1324 # Test for BUG#16885178 - INNODB FULLTEXT PHRASE SEARCH VALGRIND ERROR
1325 CREATE TABLE t1 (
1326 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
1327 a VARCHAR(200),
1328 FULLTEXT (a)
1329 ) ENGINE= InnoDB;
1330
1331 # Insert a special row
1332 INSERT INTO t1 (a) VALUES
1333 ('know mysql good database');
1334
1335 # This phrase search fails in valgrind test before the fix.
1336 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"good database"' IN BOOLEAN MODE);
1337
1338 DROP TABLE t1;
1339
1340 # MDEV-19974 InnoDB: Cannot load compressed BLOB
1341 --disable_query_log
1342 SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed;
1343 SET GLOBAL innodb_read_only_compressed=OFF;
1344 --enable_query_log
1345
1346 CREATE TABLE t1(f1 TEXT, FULLTEXT KEY(f1))ENGINE=InnoDB;
1347 INSERT INTO t1 VALUES(repeat("this is the test case", 500));
1348 ALTER TABLE t1 KEY_BLOCK_SIZE=4;
1349 ALTER TABLE t1 KEY_BLOCK_SIZE=0;
1350 --disable_query_log
1351 SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed;
1352 --enable_query_log
1353 DROP TABLE t1;
1354