Debian Codegraph @

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

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