Debian Codegraph @

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

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