Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2
3 #
4 # Tests for optimizations for InnoDB fulltext search (WL#6043)
5 #
6
7 CREATE TABLE wp(
8 FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
9 title VARCHAR(255) NOT NULL DEFAULT '',
10 text MEDIUMTEXT NOT NULL,
11 dummy INTEGER,
12 PRIMARY KEY (FTS_DOC_ID),
13 UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
14 FULLTEXT KEY idx (title,text)
15 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
16
17 INSERT INTO wp (title, text) VALUES
18 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
19 ('How To Use MySQL Well','After you went through a ...'),
20 ('Optimizing MySQL','In this tutorial we will show ...'),
21 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
22 ('MySQL vs. YourSQL','In the following database to database comparison ...'),
23 ('MySQL Security','When configured properly, MySQL ...');
24
25 CREATE TABLE t1 (i INTEGER);
26 INSERT INTO t1 SELECT FTS_DOC_ID FROM wp;
27
28 SET STATEMENT use_stat_tables=never FOR
29 ANALYZE TABLE t1;
30 SET STATEMENT use_stat_tables=never FOR
31 ANALYZE TABLE wp;
32
33 --disable_cursor_protocol
34 --disable_ps2_protocol
35
36 #
37 # Show results of MATCH expressions for reference
38 #
39 SELECT FTS_DOC_ID, title, MATCH(title, text) AGAINST ('database') AS score1,
40 MATCH(title, text) AGAINST ('mysql') AS score2
41 FROM wp;
42
43 #
44 # Test that filesort is not used if ordering on same match expression
45 # as where clause
46 #
47 --echo No sorting for this query
48 FLUSH STATUS;
49
50 SELECT title, MATCH(title, text) AGAINST ('database') AS score
51 FROM wp
52 WHERE MATCH(title, text) AGAINST ('database')
53 ORDER BY score DESC;
54
55 SHOW SESSION STATUS LIKE 'Sort%';
56
57 --echo No sorting for this query even if MATCH is part of an expression
58 FLUSH STATUS;
59
60 SELECT title, MATCH(title, text) AGAINST ('database') AS score
61 FROM wp
62 WHERE MATCH(title, text) AGAINST ('database') > 0.1
63 ORDER BY score DESC;
64
65 SHOW SESSION STATUS LIKE 'Sort%';
66
67 --echo No sorting even if there are several MATCH expressions as long as the
68 --echo right one is used in ORDER BY
69 FLUSH STATUS;
70
71 SELECT title, MATCH(title, text) AGAINST ('database') AS score1,
72 MATCH(title, text) AGAINST ('mysql') AS score2
73 FROM wp
74 WHERE MATCH(title, text) AGAINST ('database')
75 ORDER BY score1 DESC;
76
77 SHOW SESSION STATUS LIKE 'Sort%';
78
79 --echo No Sorting since FT table is first table in query
80 FLUSH STATUS;
81
82 SELECT title, MATCH(title, text) AGAINST ('database') AS score
83 FROM wp, t1
84 WHERE MATCH(title, text) AGAINST ('database') AND FTS_DOC_ID = t1.i
85 ORDER BY score DESC;
86
87 SHOW SESSION STATUS LIKE 'Sort_rows%';
88
89 --echo Sorting since there is no WHERE clause
90 FLUSH STATUS;
91
92 --sorted_result
93 SELECT MATCH(title, text) AGAINST ('database'), title AS score
94 FROM wp
95 ORDER BY score DESC;
96
97 SHOW SESSION STATUS LIKE 'Sort_rows%';
98
99 --echo Sorting since ordering on multiple columns
100 FLUSH STATUS;
101
102 SELECT title, MATCH(title, text) AGAINST ('database') AS score
103 FROM wp
104 WHERE MATCH(title, text) AGAINST ('database')
105 ORDER BY score DESC, FTS_DOC_ID;
106
107 SHOW SESSION STATUS LIKE 'Sort_rows%';
108
109 --echo Sorting since ordering is not descending
110 FLUSH STATUS;
111
112 SELECT title, MATCH(title, text) AGAINST ('database') AS score
113 FROM wp
114 WHERE MATCH(title, text) AGAINST ('database')
115 ORDER BY score ASC;
116
117 SHOW SESSION STATUS LIKE 'Sort_rows%';
118
119 --echo Sorting because one is ordering on a different MATCH expression
120 FLUSH STATUS;
121
122 SELECT title, MATCH(title, text) AGAINST ('mysql') AS score
123 FROM wp
124 WHERE MATCH(title, text) AGAINST ('database')
125 ORDER BY score DESC;
126
127 SHOW SESSION STATUS LIKE 'Sort_rows%';
128
129 #
130 # Tests for ORDER BY/LIMIT optimzation
131 #
132 --echo No sorting for this query
133 FLUSH STATUS;
134
135 SELECT title, MATCH(title, text) AGAINST ('database') AS score
136 FROM wp
137 ORDER BY score DESC LIMIT 2;
138
139 SHOW SESSION STATUS LIKE 'Sort%';
140
141 --echo Revert to table scan and sorting for this query since not
142 --echo enough matching rows to satisfy LIMIT clause
143 FLUSH STATUS;
144
145 SELECT title, MATCH(title, text) AGAINST ('database') AS score
146 FROM wp
147 ORDER BY score DESC LIMIT 2;
148
149 SHOW SESSION STATUS LIKE 'Handler_read%';
150 SHOW SESSION STATUS LIKE 'Sort_rows%';
151
152 --echo Sorting since no LIMIT clause
153 FLUSH STATUS;
154
155 --sorted_result
156 SELECT MATCH(title, text) AGAINST ('database') AS score, title
157 FROM wp
158 ORDER BY score DESC;
159
160 SHOW SESSION STATUS LIKE 'Sort_rows%';
161
162 --echo Sorting since there is a WHERE clause
163 FLUSH STATUS;
164
165 SELECT title, MATCH(title, text) AGAINST ('database') AS score
166 FROM wp
167 WHERE dummy IS NULL
168 ORDER BY score DESC LIMIT 2;
169
170 SHOW SESSION STATUS LIKE 'Sort_rows%';
171
172 --echo Sorting since ordering is not on a simple MATCH expressions
173 FLUSH STATUS;
174
175 SELECT title, (MATCH(title, text) AGAINST ('database')) * 100 AS score
176 FROM wp
177 ORDER BY score DESC LIMIT 2;
178
179 SHOW SESSION STATUS LIKE 'Sort_rows%';
180
181 #
182 # Test that there is no row accesses if all necessary information is
183 # available in FTS result
184 #
185 --echo No ordinary handler accesses when only accessing FTS_DOC_ID and MATCH
186 FLUSH STATUS;
187
188 SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
189 FROM wp
190 WHERE MATCH(title, text) AGAINST ('database');
191
192 SHOW SESSION STATUS LIKE 'Handler_read%';
193
194 --echo Still no handler accesses when adding FTS_DOC_ID to WHERE clause
195 FLUSH STATUS;
196
197 SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
198 FROM wp
199 WHERE MATCH(title, text) AGAINST ('database') AND FTS_DOC_ID > 2;
200
201 SHOW SESSION STATUS LIKE 'Handler_read%';
202
203 --echo Still no handler accesses when ordering by MATCH expression
204 FLUSH STATUS;
205
206 SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
207 FROM wp
208 WHERE MATCH(title, text) AGAINST ('database')
209 ORDER BY score;
210
211 SHOW SESSION STATUS LIKE 'Handler_read%';
212
213 --echo Optimization is disabled when ordering on FTS_DOC_ID
214 FLUSH STATUS;
215
216 SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
217 FROM wp
218 WHERE MATCH(title, text) AGAINST ('database')
219 ORDER BY 1 DESC;
220
221 SHOW SESSION STATUS LIKE 'Handler_read%';
222
223 --echo Optimization also work with several MATCH expressions
224 FLUSH STATUS;
225
226 SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score1,
227 MATCH(title, text) AGAINST ('mysql') AS score2
228 FROM wp
229 WHERE MATCH(title, text) AGAINST ('database');
230
231 SHOW SESSION STATUS LIKE 'Handler_read%';
232
233 --echo Optimization does not apply if sorting on a different MATCH expressions
234 --echo from the one used to access the
235 FLUSH STATUS;
236
237 SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score1,
238 MATCH(title, text) AGAINST ('mysql') AS score2
239 FROM wp
240 WHERE MATCH(title, text) AGAINST ('database')
241 ORDER BY score2 DESC;
242
243 SHOW SESSION STATUS LIKE 'Handler_read%';
244
245 FLUSH STATUS;
246
247 --echo Optimization does not apply for GROUP BY
248 SET @save_mode = @@sql_mode;
249 SET sql_mode = (select replace(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
250 SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('database') AS score
251 FROM wp
252 WHERE MATCH(title, text) AGAINST ('database')
253 GROUP BY score;
254
255 SHOW SESSION STATUS LIKE 'Handler_read%';
256 SET sql_mode = @save_mode;
257
258 #
259 # Putting all three optimizations together
260 #
261 --echo No sorting and no table access with LIMIT clause and only information
262 --echo from FTS result
263 FLUSH STATUS;
264
265 SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
266 FROM wp
267 ORDER BY score DESC LIMIT 2;
268
269 SHOW STATUS LIKE 'Handler_read%';
270 SHOW SESSION STATUS LIKE 'Sort%';
271
272 #
273 # Count optimization
274 #
275 let $query =
276 SELECT COUNT(*)
277 FROM wp
278 WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
279
280 --echo If count optimization applies, EXPLAIN shows
281 --echo "Select tables optimized away."
282 eval EXPLAIN $query;
283 FLUSH STATUS;
284 eval $query;
285 --echo Verify that there was no table access
286 SHOW STATUS LIKE 'Handler_read%';
287
288 let $query =
289 SELECT COUNT(title)
290 FROM wp
291 WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
292
293 --echo Optimization applies also to COUNT(expr) as long as expr is not nullable
294 eval EXPLAIN $query;
295 eval $query;
296
297 let $query =
298 SELECT count(*)
299 FROM wp, t1
300 WHERE MATCH(title, text) AGAINST ('database');
301
302 --echo Optimization does not apply if not a single table query.
303 eval EXPLAIN $query;
304 eval $query;
305
306 let $query =
307 SELECT COUNT(title)
308 FROM wp
309 WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE) > 0;
310
311 --echo Optimization does not apply if MATCH is part of an expression
312 eval EXPLAIN $query;
313 eval $query;
314
315 let $query =
316 SELECT COUNT(title)
317 FROM wp
318 WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE) > 0;
319
320 --echo Optimization does not apply if MATCH is part of an expression
321 eval EXPLAIN $query;
322 eval $query;
323
324 let $query =
325 SELECT COUNT(dummy)
326 FROM wp
327 WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
328
329 --echo Optimization does not apply if COUNT expression is nullable
330 eval EXPLAIN $query;
331 eval $query;
332
333 #
334 # Verify that the queries optimized for InnoDB works with QUERY EXPANSION
335 #
336
337 # Query will also avoid sorting when query expansion is used
338 FLUSH STATUS;
339 --sorted_result
340 SELECT MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score,
341 title
342 FROM wp
343 WHERE MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION)
344 ORDER BY score DESC;
345 SHOW SESSION STATUS LIKE 'Sort%';
346
347 # Check ORDER BY/LIMIT query with no WHERE clause
348 FLUSH STATUS;
349 SELECT title,
350 MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score
351 FROM wp
352 ORDER BY score DESC LIMIT 2;
353 SHOW SESSION STATUS LIKE 'Sort%';
354
355 # Check query where FTS result is "covering"
356 FLUSH STATUS;
357 SELECT FTS_DOC_ID docid,
358 MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score
359 FROM wp
360 WHERE MATCH(title, text) AGAINST ('database');
361 SHOW SESSION STATUS LIKE 'Handler_read%';
362
363 # Check the combination of all three
364 FLUSH STATUS;
365 SELECT FTS_DOC_ID docid,
366 MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score
367 FROM wp
368 ORDER BY score DESC LIMIT 2;
369 SHOW STATUS LIKE 'Handler_read%';
370 SHOW SESSION STATUS LIKE 'Sort%';
371
372 # Check the count optimization
373 let $query =
374 SELECT COUNT(*)
375 FROM wp
376 WHERE MATCH(title,text) AGAINST ('database' WITH QUERY EXPANSION);
377 eval EXPLAIN $query;
378 FLUSH STATUS;
379 eval $query;
380 SHOW STATUS LIKE 'Handler_read%';
381
382 #
383 # Verify that the queries optimized for InnoDB works with BOOLEAN MODE
384 #
385
386 # Query will also avoid sorting when Boolean mode is used
387 FLUSH STATUS;
388 --sorted_result
389 SELECT MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) score,
390 title
391 FROM wp
392 WHERE MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE)
393 ORDER BY score DESC;
394 SHOW SESSION STATUS LIKE 'Sort%';
395
396 # Check ORDER BY/LIMIT query with no WHERE clause
397 FLUSH STATUS;
398 --sorted_result
399 SELECT MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) score,
400 title
401 FROM wp
402 ORDER BY score DESC;
403 SHOW SESSION STATUS LIKE 'Sort%';
404
405 # Check query where FTS result is "covering"
406 FLUSH STATUS;
407 SELECT FTS_DOC_ID docid,
408 MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
409 FROM wp
410 WHERE MATCH(title, text) AGAINST ('+MySQL -database');
411 SHOW SESSION STATUS LIKE 'Handler_read%';
412
413 # Check the combination of all three
414 FLUSH STATUS;
415 SELECT FTS_DOC_ID docid,
416 MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
417 FROM wp
418 ORDER BY score DESC LIMIT 1;
419 SHOW STATUS LIKE 'Handler_read%';
420 SHOW SESSION STATUS LIKE 'Sort%';
421
422 # Check the count optimization
423 let $query =
424 SELECT COUNT(*)
425 FROM wp
426 WHERE MATCH(title,text) AGAINST ('+MySQL -database' IN BOOLEAN MODE);
427 eval EXPLAIN $query;
428 FLUSH STATUS;
429 eval $query;
430 SHOW STATUS LIKE 'Handler_read%';
431
432
433 #
434 # Verify that the queries optimized for InnoDB works with
435 # BOOLEAN proximity search
436 #
437
438 # Query will also avoid sorting when Boolean mode is used
439 FLUSH STATUS;
440 SELECT title,
441 MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score
442 FROM wp
443 WHERE MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE)
444 ORDER BY score DESC;
445 SHOW SESSION STATUS LIKE 'Sort%';
446
447 # Check ORDER BY/LIMIT query with no WHERE clause
448 FLUSH STATUS;
449 SELECT title,
450 MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score
451 FROM wp
452 ORDER BY score DESC LIMIT 1;
453 SHOW SESSION STATUS LIKE 'Sort%';
454
455 # Check query where FTS result is "covering"
456 FLUSH STATUS;
457 SELECT FTS_DOC_ID docid,
458 MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score
459 FROM wp
460 WHERE MATCH(title, text) AGAINST ('"MySQL database"@5');
461 SHOW SESSION STATUS LIKE 'Handler_read%';
462
463 # Check the combination of all three
464 FLUSH STATUS;
465 SELECT FTS_DOC_ID docid,
466 MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score
467 FROM wp
468 ORDER BY score DESC LIMIT 1;
469 SHOW STATUS LIKE 'Handler_read%';
470 SHOW SESSION STATUS LIKE 'Sort%';
471
472 # Check the count optimization
473 let $query =
474 SELECT COUNT(*)
475 FROM wp
476 WHERE MATCH(title,text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE);
477 eval EXPLAIN $query;
478 FLUSH STATUS;
479 eval $query;
480 SHOW STATUS LIKE 'Handler_read%';
481
482 #
483 # Check that nothing goes wrong when combining different modes
484 #
485 SELECT title,
486 MATCH(title, text) AGAINST ('database') AS score
487 FROM wp
488 WHERE MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION)
489 ORDER BY score DESC, title ASC;
490
491 SELECT title,
492 MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
493 FROM wp
494 WHERE MATCH(title, text) AGAINST ('MySQL database' WITH QUERY EXPANSION)
495 ORDER BY score DESC, title ASC;
496
497 SELECT title,
498 MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
499 FROM wp
500 WHERE MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE)
501 ORDER BY score DESC, title ASC;
502
503
504 #
505 # Verify that the queries optimized for InnoDB still works with MyISAM
506 #
507 ALTER TABLE wp ENGINE=myisam;
508
509 # Check avoid sorting query
510 FLUSH STATUS;
511 SELECT title, MATCH(title, text) AGAINST ('database') AS score
512 FROM wp
513 WHERE MATCH(title, text) AGAINST ('database')
514 ORDER BY score DESC;
515 SHOW SESSION STATUS LIKE 'Sort%';
516
517 # Check ORDER BY/LIMIT query with no WHERE clause
518 FLUSH STATUS;
519 SELECT title, MATCH(title, text) AGAINST ('database') AS score
520 FROM wp
521 ORDER BY score DESC LIMIT 2;
522 SHOW SESSION STATUS LIKE 'Sort%';
523
524 # Check query where FTS result is "covering"
525 FLUSH STATUS;
526 SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
527 FROM wp
528 WHERE MATCH(title, text) AGAINST ('database');
529 SHOW SESSION STATUS LIKE 'Handler_read%';
530
531 # Check the combination of all three
532 FLUSH STATUS;
533 SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
534 FROM wp
535 ORDER BY score DESC LIMIT 2;
536 SHOW STATUS LIKE 'Handler_read%';
537 SHOW SESSION STATUS LIKE 'Sort%';
538
539 # Check the count optimization
540 let $query =
541 SELECT COUNT(*)
542 FROM wp
543 WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
544 eval EXPLAIN $query;
545 FLUSH STATUS;
546 eval $query;
547 SHOW STATUS LIKE 'Handler_read%';
548
549
550 DROP TABLE wp, t1;
551
552 --enable_ps2_protocol
553 --enable_cursor_protocol
554
555 # Tests for FT hints.
556
557 CREATE TABLE t1
558 (
559 FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
560 title VARCHAR(255) DEFAULT '',
561 text MEDIUMTEXT ,
562 PRIMARY KEY (FTS_DOC_ID),
563 UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
564 FULLTEXT KEY ft_idx (title,text)
565 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
566
567
568 INSERT INTO t1 (title, text) VALUES
569 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
570 ('How To Use MySQL Well','After you went through a ...'),
571 ('Optimizing MySQL','In this tutorial we will show ...'),
572 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
573 ('MySQL vs. YourSQL database','In the following database to database comparison ...'),
574 ('MySQL Security','When configured properly, MySQL ...'),
575 ('InnoDB', 'InnoDB is a transaction-safe (ACID compliant) storage engine'),
576 ('MySQL is a database management system', 'A database is a structured collection of data...'),
577 ('MySQL databases are relational', 'A relational database stores data in separate tables rather than putting all the data in one big storeroom...'),
578 ('MySQL software is Open Source', 'Open Source means that it is possible for anyone to use and modify the software...'),
579 ('The MySQL Database Server is very fast, reliable, scalable, and easy to use', 'MySQL Server can run comfortably on a desktop or laptop...'),
580 ('MySQL Server works in client/server or embedded systems', 'The MySQL Database Software is a client/server system...'),
581 ('MyISAM', 'MyISAM is based on the older (and no longer available) ISAM storage engine but has many useful extensions'),
582 ('A large amount of contributed MySQL software is available', 'MySQL Server has a practical set of features developed in close cooperation with our users'),
583 (NULL,NULL);
584
585 -- disable_result_log
586 ANALYZE TABLE t1;
587 -- enable_result_log
588
589 --echo # No ranking
590
591 EXPLAIN
592 SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE);
593 SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE);
594
595 # Atm opt_sum_query does not support COUNT optimization if
596 # ORDER BY is present. TODO: fix it.
597 EXPLAIN
598 SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('data*' IN BOOLEAN MODE) ORDER BY title LIMIT 3;
599 SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('data*' IN BOOLEAN MODE) ORDER BY title LIMIT 3;
600
601 EXPLAIN
602 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);
603 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);
604
605 EXPLAIN
606 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION);
607 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION);
608
609 EXPLAIN
610 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE);
611 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE);
612
613 # check case with 'for' stopword
614 EXPLAIN SELECT FTS_DOC_ID FROM t1
615 WHERE MATCH(title, text) AGAINST ('+for' IN BOOLEAN MODE);
616 SELECT FTS_DOC_ID FROM t1
617 WHERE MATCH(title, text) AGAINST ('+for' IN BOOLEAN MODE);
618
619
620 --echo # No sorting by rank
621
622 EXPLAIN SELECT FTS_DOC_ID, TITLE FROM t1
623 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
624 ORDER BY title;
625 SELECT FTS_DOC_ID, TITLE FROM t1
626 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
627 ORDER BY title;
628
629 EXPLAIN SELECT FTS_DOC_ID FROM t1
630 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);
631 SELECT FTS_DOC_ID FROM t1
632 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);
633
634 EXPLAIN
635 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title;
636 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title;
637
638 EXPLAIN
639 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title;
640 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title;
641
642 --echo # LIMIT optimization
643
644 EXPLAIN SELECT FTS_DOC_ID, TITLE FROM t1
645 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
646 LIMIT 3;
647 SELECT FTS_DOC_ID, TITLE FROM t1
648 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
649 LIMIT 3;
650
651 EXPLAIN SELECT FTS_DOC_ID FROM t1
652 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
653 LIMIT 3;
654 SELECT FTS_DOC_ID FROM t1
655 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
656 LIMIT 3;
657
658 EXPLAIN SELECT FTS_DOC_ID FROM t1
659 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
660 ORDER BY title
661 LIMIT 3;
662 SELECT FTS_DOC_ID FROM t1
663 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
664 ORDER BY title
665 LIMIT 3;
666
667
668 EXPLAIN
669 SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title LIMIT 1;
670 SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title LIMIT 1;
671
672 EXPLAIN
673 SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title LIMIT 1;
674 SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title LIMIT 1;
675
676 EXPLAIN
677 SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank
678 FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
679 ORDER BY rank, FTS_DOC_ID
680 LIMIT 3;
681 SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank
682 FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
683 ORDER BY rank, FTS_DOC_ID
684 LIMIT 3;
685
686 EXPLAIN
687 SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank
688 FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
689 ORDER BY rank DESC, FTS_DOC_ID ASC
690 LIMIT 3;
691 SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank
692 FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
693 ORDER BY rank DESC, FTS_DOC_ID ASC
694 LIMIT 3;
695
696 EXPLAIN SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
697 FROM t1
698 ORDER BY rank DESC
699 LIMIT 2;
700 SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
701 FROM t1
702 ORDER BY rank DESC
703 LIMIT 2;
704
705 EXPLAIN SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank
706 FROM t1
707 ORDER BY rank DESC
708 LIMIT 3;
709 SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank
710 FROM t1
711 ORDER BY rank DESC
712 LIMIT 3;
713
714 EXPLAIN SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
715 FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE)
716 ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) DESC,
717 FTS_DOC_ID ASC;
718 SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
719 FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE)
720 ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) DESC,
721 FTS_DOC_ID ASC;
722
723 EXPLAIN SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
724 FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) and FTS_DOC_ID > 1
725 ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) DESC
726 LIMIT 2;
727 SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
728 FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) and FTS_DOC_ID > 1
729 ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) DESC
730 LIMIT 2;
731
732
733 EXPLAIN
734 SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank
735 FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
736 ORDER BY rank
737 LIMIT 1;
738 SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank
739 FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
740 ORDER BY rank
741 LIMIT 1;
742
743 EXPLAIN
744 SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank
745 FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
746 ORDER BY rank DESC
747 LIMIT 1;
748 SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank
749 FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
750 ORDER BY rank DESC
751 LIMIT 1;
752
753
754 EXPLAIN
755 SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank
756 FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
757 ORDER BY MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
758 LIMIT 1;
759 SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank
760 FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
761 ORDER BY MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) DESC
762 LIMIT 1;
763
764 --echo # WHERE optimization on MATCH > 'some_rank'
765
766 EXPLAIN SELECT FTS_DOC_ID FROM t1
767 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) > 0.1;
768
769 SELECT FTS_DOC_ID FROM t1
770 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) > 0.1;
771
772
773 --echo # additional test for correct behaviour
774
775 EXPLAIN SELECT * FROM t1 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC LIMIT 10;
776
777 SELECT FTS_DOC_ID FROM t1
778 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) AND
779 MATCH (title, text) AGAINST ('mysql' IN NATURAL LANGUAGE MODE)
780 LIMIT 6;
781
782 --echo # test OR condition
783
784 SELECT FTS_DOC_ID
785 FROM t1
786 WHERE MATCH(title, text) AGAINST ('database')
787 OR MATCH(title, text) AGAINST ('mysql')
788 ORDER BY MATCH(title, text) AGAINST ('database') DESC, FTS_DOC_ID ASC;
789
790 EXPLAIN SELECT FTS_DOC_ID
791 FROM t1
792 WHERE MATCH(title, text) AGAINST ('database')
793 OR MATCH(title, text) AGAINST ('mysql')
794 ORDER BY MATCH(title, text) AGAINST ('database') DESC, FTS_DOC_ID ASC;
795
796 --echo # MATCH and GROUP BY, DISTINCT
797
798 SET sql_mode = (select replace(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
799
800 EXPLAIN SELECT FTS_DOC_ID FROM t1
801 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
802 GROUP BY FTS_DOC_ID
803 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
804 LIMIT 3;
805 SELECT FTS_DOC_ID FROM t1
806 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
807 GROUP BY FTS_DOC_ID
808 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
809 LIMIT 3;
810
811 EXPLAIN SELECT FTS_DOC_ID FROM t1
812 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
813 GROUP BY title
814 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
815 LIMIT 3;
816
817 SELECT FTS_DOC_ID FROM t1
818 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
819 GROUP BY title
820 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
821 LIMIT 3;
822
823 EXPLAIN SELECT MAX(FTS_DOC_ID) FROM t1
824 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
825 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
826 LIMIT 3;
827 SELECT MAX(FTS_DOC_ID) FROM t1
828 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
829 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
830 LIMIT 3;
831
832 EXPLAIN SELECT DISTINCT(title) FROM t1
833 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
834 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
835 LIMIT 3;
836 SELECT DISTINCT(title) FROM t1
837 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
838 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
839 LIMIT 3;
840
841 EXPLAIN SELECT DISTINCT(FTS_DOC_ID) FROM t1
842 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
843 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
844 LIMIT 3;
845 SELECT DISTINCT(FTS_DOC_ID) FROM t1
846 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
847 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
848 LIMIT 3;
849
850 SET sql_mode = @save_mode;
851
852 --echo # FTS index access
853
854 SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
855 FROM t1
856 ORDER BY rank DESC
857 LIMIT 2;
858 EXPLAIN SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
859 FROM t1
860 ORDER BY rank DESC
861 LIMIT 2;
862
863 SELECT a.FTS_DOC_ID, b.FTS_DOC_ID
864 FROM t1 a, t1 b
865 WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and
866 MATCH(b.title, b.text) AGAINST ('+mysql' IN BOOLEAN MODE) and
867 a.FTS_DOC_ID = b.FTS_DOC_ID;
868 EXPLAIN SELECT a.FTS_DOC_ID, b.FTS_DOC_ID
869 FROM t1 a, t1 b
870 WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and
871 MATCH(b.title, b.text) AGAINST ('+mysql' IN BOOLEAN MODE) and
872 a.FTS_DOC_ID = b.FTS_DOC_ID;
873
874 SELECT a.FTS_DOC_ID, MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE),
875 b.FTS_DOC_ID, MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE)
876 FROM t1 a, t1 b
877 WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and
878 MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE);
879
880 EXPLAIN SELECT a.FTS_DOC_ID, MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE),
881 b.FTS_DOC_ID, MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE)
882 FROM t1 a, t1 b
883 WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and
884 MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE);
885
886 # Index only access by non-FTS index
887
888 EXPLAIN SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ("data*" IN BOOLEAN MODE) * 100;
889 SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ("data*" IN BOOLEAN MODE) * 100
890 FROM t1 WHERE MATCH(title, text) AGAINST ("data*" IN BOOLEAN MODE) * 100;
891 # Run query returning null record
892 SELECT * FROM t1 WHERE title IS NULL AND text IS NULL;
893
894 # More testing of index only access by non-FTS index
895
896 CREATE TABLE t2 SELECT FTS_DOC_ID as doc_id, title, text FROM t1;
897 ALTER TABLE t2 ADD PRIMARY KEY (doc_id);
898 ALTER TABLE t2 ADD FULLTEXT KEY ft_idx (title,text);
899
900 -- disable_result_log
901 ANALYZE TABLE t2;
902 -- enable_result_log
903
904 # No index access
905 EXPLAIN SELECT DOC_ID FROM t2 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;
906 SELECT DOC_ID FROM t2 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;
907 # Index access
908 EXPLAIN SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;
909 SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;
910
911 DROP TABLE t1, t2;
912
913 --echo "Check hints with uft8 charset for 2 cases"
914 set names utf8;
915 EVAL CREATE TABLE t1 (
916 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
917 title VARCHAR(200),
918 text TEXT
919 ) CHARACTER SET = utf8, ENGINE=InnoDB;
920
921 INSERT INTO t1 (title, text) VALUES
922 ('Я могу есть стекло', 'оно мне не вредит'),
923 ('Мога да ям стъкло', 'то не ми вреди'),
924 ('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'),
925 ('Příliš žluťoučký kůň', 'úpěl ďábelské kódy'),
926 ('Sævör grét', 'áðan því úlpan var ónýt'),
927 ('うゐのおくやま','けふこえて'),
928 ('いろはにほへど ちりぬる','あさきゆめみじ ゑひもせず');
929 CREATE FULLTEXT INDEX idx on t1 (title, text);
930
931 --echo # No ranking
932
933 EXPLAIN
934 SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('вредит' IN NATURAL LANGUAGE MODE);
935 SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('вредит' IN NATURAL LANGUAGE MODE);
936
937 EXPLAIN
938 SELECT * FROM t1 WHERE MATCH(title, text) AGAINST ("оно" WITH QUERY EXPANSION);
939 SELECT * FROM t1 WHERE MATCH(title, text) AGAINST ("оно" WITH QUERY EXPANSION);
940 --echo # No sorting by rank
941 EXPLAIN SELECT FTS_DOC_ID FROM t1
942 WHERE MATCH(title, text) AGAINST ('+(Мога τίποτα)' IN BOOLEAN MODE);
943 SELECT FTS_DOC_ID FROM t1
944 WHERE MATCH(title, text) AGAINST ('+(Мога τίποτα)' IN BOOLEAN MODE);
945 DROP TABLE t1;
946
947
948 --echo #
949 --echo # Bug #18924341 CRASH IN TEST_IF_SKIP_SORT_ORDER, GROUP BY MATCH AGAINST DESC
950 --echo #
951
952 CREATE TABLE t1 (f1 CHAR(1), FULLTEXT KEY (f1));
953 SELECT 1 FROM t1 NATURAL JOIN t1 a GROUP BY MATCH(t1.f1) AGAINST ("1") DESC;
954 DROP TABLE t1;
955
956
957 --echo #
958 --echo # Bug#20261601 ASSERTION FAILED: !FIRST_QEP_TAB->TABLE()->NO_KEYREAD
959 --echo #
960
961 CREATE TABLE t1(a INT PRIMARY KEY);
962 INSERT INTO t1 VALUES(1),(2);
963 --error ER_FT_MATCHING_KEY_NOT_FOUND
964 SELECT (SELECT MATCH(`a`)AGAINST('1') FROM t1) FROM t1;
965 SELECT 1, a IN (SELECT a FROM t1) FROM t1;
966 DROP TABLE t1;
967
968 --echo #
969 --echo # Bug#20442572 ASSERTION `!FIRST_QEP_TAB->TABLE()->NO_KEYREAD' FAILED.
970 --echo # Bug#75688 Assertion `!first_qep_tab->table()->no_keyread' failed.
971 --echo #
972
973 CREATE TABLE t1(a INT,b POINT NOT NULL,KEY(a));
974
975 HANDLER t1 OPEN;
976 #--error ER_FT_MATCHING_KEY_NOT_FOUND
977 select * from t1 where MATCH a,b AGAINST('"Now sUPPort"' IN BOOLEAN MODE);
978 prepare stmt1 from "truncate t1";
979 SELECT a IN(SELECT a FROM t1)FROM t1;
980
981 deallocate prepare stmt1;
982 DROP TABLE t1;
983
984 --echo #
985 --echo # Bug #20685427 INVALID WRITE OF FREED MEMORY IN ITEM_FUNC_MATCH::CLEANUP
986 --echo #
987
988 CREATE TABLE t1(a TEXT CHARSET LATIN1, FULLTEXT KEY(a)) ENGINE=INNODB;
989 --error ER_FT_MATCHING_KEY_NOT_FOUND
990 SELECT MATCH(a) AGAINST ('') FROM (SELECT a FROM t1 LIMIT 1) q;
991 DROP TABLE t1;
992
993 --echo #
994 --echo # Bug#21140067 EXPLAIN .. MATCH AGAINST: ASSERTION FAILED: TO <= END
995 --echo #
996
997 CREATE TABLE t1(f1 CHAR(1) CHARSET latin1, FULLTEXT(f1)) ENGINE=INNODB;
998 EXPLAIN SELECT 1 FROM t1 WHERE 1.238585e+308 <= MATCH(f1) AGAINST ('1' IN BOOLEAN MODE);
999
1000 EXPLAIN FORMAT = JSON SELECT 1 FROM t1 WHERE 1.238585e+308 <= MATCH(f1) AGAINST ('1' IN BOOLEAN MODE);
1001
1002 DROP TABLE t1;
1003
1004 --echo #
1005 --echo # Bug#21140088 MATCH AGAINST: ASSERTION FAILED: !TABLE || (!TABLE->READ_SET || BITMAP_IS_SET
1006 --echo #
1007
1008 SET sql_mode='';
1009 CREATE TABLE t1(a INT) ENGINE=INNODB;
1010 CREATE TABLE t2(b TEXT CHARSET LATIN1, FULLTEXT(b), PRIMARY KEY(b(10))) ENGINE=INNODB;
1011 INSERT INTO t2 VALUES ('a'),('b');
1012 --error ER_WRONG_ARGUMENTS
1013 SELECT NOT EXISTS (SELECT MATCH(b) AGAINST ('1') FROM t1) FROM t2 GROUP BY "a";
1014 DROP TABLE t1, t2;
1015
1016 CREATE TABLE t1(a INT) ENGINE=MyISAM;
1017 CREATE TABLE t2(b TEXT CHARSET LATIN1, FULLTEXT(b), PRIMARY KEY(b(10))) ENGINE=MyISAM;
1018 INSERT INTO t2 VALUES ('a'),('b');
1019 #--error ER_WRONG_ARGUMENTS
1020 SELECT NOT EXISTS (SELECT MATCH(b) AGAINST ('1' in BOOLEAN MODE) FROM t1) FROM t2 GROUP BY "a";
1021 DROP TABLE t1, t2;
1022
1023 SET sql_mode=default;
1024
1025 --echo #
1026 --echo # Bug#21140039 ASSERTION FAILED: !FIRST_QEP_TAB->TABLE()->NO_KEYREAD MATCH AGAINST.....
1027 --echo #
1028
1029 CREATE TABLE t1
1030 (
1031 a INT,
1032 b INT,
1033 c CHAR(1) CHARSET latin1,
1034 PRIMARY KEY (b,a),
1035 FULLTEXT KEY (c)
1036 ) ENGINE=INNODB;
1037 SELECT "a" NOT IN(SELECT b FROM t1 WHERE MATCH(c) AGAINST ('a' IN BOOLEAN MODE));
1038 DROP TABLE t1;
1039
1040 --echo #
1041 --echo # Bug#21300774 ASSERT `!INIT_FTFUNCS(THD, SELECT_LEX)` IN JOIN::RESET AT SQL/SQL_SELECT.CC:874
1042 --echo #
1043
1044 CREATE TABLE t1 (f1 INT);
1045 INSERT INTO t1 VALUES (1);
1046 INSERT INTO t1 VALUES (2);
1047 CREATE TABLE t2 (ft TEXT, FULLTEXT KEY ft(ft));
1048 INSERT INTO t2 VALUES ('abc');
1049 INSERT INTO t2 VALUES ('def');
1050
1051 --error ER_SUBQUERY_NO_1_ROW
1052 UPDATE t1 SET f1 =
1053 (SELECT t1.f1 FROM t2 WHERE NOT TRUE AND
1054 MATCH (ft) AGAINST ((SELECT 'xyz' FROM t2)));
1055
1056 DROP TABLE t1, t2;
1057
1058 --echo #
1059 --echo # Bug#22679209: FULL-TEXT QUERIES WITH ADDITIONAL SECONDARY INDEX
1060 --echo # GIVES NULL OR ZERO ROWS
1061 --echo #
1062
1063 CREATE TABLE t1 (
1064 f1 INTEGER,
1065 title varchar(255),
1066 body mediumtext,
1067 KEY f1 (f1),
1068 FULLTEXT KEY title (title),
1069 FULLTEXT KEY body (body)
1070 ) ENGINE=InnoDB;
1071
1072 INSERT INTO t1 VALUES
1073 (1, 'Insert into table', 'insert into table select from'),
1074 (1, 'Delete from table', 'insert into table select from'),
1075 (1, 'Update', 'perform update'),
1076 (2, 'Insert into table', 'insert into table select from'),
1077 ( 2, 'Delete from table', 'some body text here'),
1078 ( 2, 'Update', 'perform update'),
1079 ( 3, 'Insert into table', 'insert into table select from'),
1080 ( 3, 'Delete from table', 'some body text here');
1081
1082 SELECT f1 FROM t1 WHERE f1=1 AND
1083 (MATCH (title) AGAINST ('table' IN BOOLEAN MODE) OR
1084 MATCH (body) AGAINST ('table' IN BOOLEAN MODE));
1085
1086 DROP TABLE t1;
1087
1088 --echo # End of test for Bug#22679209