Debian Codegraph @

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

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