Debian Codegraph @

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

raw
1 --source include/have_debug.inc
2 --source include/have_innodb.inc
3
4 --echo # Bug #22709692 FTS QUERY EXCEEDS RESULT CACHE LIMIT
5
6 CREATE TABLE articles (
7 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
8 title VARCHAR(200),
9 body TEXT,
10 FULLTEXT (title,body),
11 FULLTEXT (body))ENGINE=InnoDB;
12
13 INSERT INTO articles (title,body) VALUES
14 ('MySQL Tutorial','DBMS stands for DataBase ...'),
15 ('How To Use MySQL Well','After you went through a ...'),
16 ('Optimizing MySQL','In this tutorial we will show ...'),
17 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
18 ('MySQL vs. YourSQL','In the following database comparison ...'),
19 ('MySQL Security','When configured properly, MySQL ...');
20
21 SET @default_cache_size = @@GLOBAL.query_cache_size;
22 SET GLOBAL query_cache_size=0;
23
24 --echo # Query involves Ranking
25 SELECT * FROM articles
26 WHERE MATCH (title,body)
27 AGAINST ('MySQL' IN NATURAL LANGUAGE MODE) LIMIT 1;
28
29 --echo # Without optimization
30 SET @save_dbug = @@debug_dbug;
31 SET debug_dbug = '+d,fts_union_limit_off';
32 SELECT * FROM articles
33 WHERE MATCH (title,body)
34 AGAINST ('MySQL' IN NATURAL LANGUAGE MODE) LIMIT 1;
35 SET debug_dbug = @save_dbug;
36
37 --echo # Query involves No Ranking and fts_union operations
38 SELECT * FROM articles
39 WHERE MATCH (title,body)
40 AGAINST ('MySQL' IN BOOLEAN MODE) limit 1;
41
42 --echo # Without optimization
43 SET debug_dbug = '+d,fts_union_limit_off';
44 SELECT * FROM articles
45 WHERE MATCH (title,body)
46 AGAINST ('MySQL' IN BOOLEAN MODE) limit 1;
47
48 SET debug_dbug = @save_dbug;
49
50 --echo # Query involves No ranking and fts_union, fts_ignore
51 SELECT * FROM articles
52 WHERE MATCH (title,body)
53 AGAINST ('MySQL -YourSQL' IN BOOLEAN MODE) limit 1;
54
55 --echo # Without optimization
56 SET debug_dbug = '+d,fts_union_limit_off';
57 SELECT * FROM articles
58 WHERE MATCH (title,body)
59 AGAINST ('MySQL -YourSQL' IN BOOLEAN MODE) limit 1;
60 SET debug_dbug = @save_dbug;
61
62 --echo # Query with fts_intersect
63 SELECT * FROM articles
64 WHERE MATCH (title,body)
65 AGAINST ('MySQL +YourSQL' IN BOOLEAN MODE) limit 1;
66
67 --echo # Without optimization
68 SET debug_dbug = '+d,fts_union_limit_off';
69 SELECT * FROM articles
70 WHERE MATCH (title,body)
71 AGAINST ('MySQL +YourSQL' IN BOOLEAN MODE) limit 1;
72 SET debug_dbug = @save_dbug;
73
74 # insert record with @ character which is used in proximity search
75 INSERT INTO articles (title,body) VALUES
76 ('MySQL Tutorial','request [email protected] ...'),
77 ('MySQL Tutorial','request [email protected] ...'),
78 ('Trial version','query performace @1255 minute on 2.1Hz
79 Memory 2GB...'),
80 ('when To Use MySQL Well','for free faq [email protected] ...');
81
82 --echo # Query with @distance
83 SELECT * FROM articles
84 WHERE MATCH (title,body)
85 AGAINST ('"MySQL request"@3' IN BOOLEAN MODE) limit 1;
86
87 --echo # Without optimization
88 SET debug_dbug = '+d,fts_union_limit_off';
89 SELECT * FROM articles
90 WHERE MATCH (title,body)
91 AGAINST ('"MySQL request"@3' IN BOOLEAN MODE) limit 1;
92 SET debug_dbug = @save_dbug;
93
94 --echo # Query with subexpression
95 SELECT * FROM articles
96 WHERE MATCH (title,body)
97 AGAINST ('+MySQL +(-support +doc)' IN BOOLEAN MODE) limit 1;
98
99 --echo # Without optimization
100 SET debug_dbug = '+d,fts_union_limit_off';
101 SELECT * FROM articles
102 WHERE MATCH (title,body)
103 AGAINST ('+MySQL +(-support +doc)' IN BOOLEAN MODE) limit 1;
104 SET debug_dbug = @save_dbug;
105
106 --echo # limit num1 OFFSET num2
107 SELECT * FROM articles
108 WHERE MATCH (title,body)
109 AGAINST ('MySQL' in boolean mode) limit 4 offset 2;
110
111 --echo # Without optimization
112 SET debug_dbug = '+d,fts_union_limit_off';
113 SELECT * FROM articles
114 WHERE MATCH (title,body)
115 AGAINST ('MySQL' in boolean mode) limit 4 offset 2;
116 SET debug_dbug = @save_dbug;
117
118 --echo # wild card search
119 SELECT * FROM articles
120 WHERE MATCH (title,body)
121 AGAINST ('ru*' IN BOOLEAN MODE) limit 1;
122
123 --echo # Without optimization
124 SET debug_dbug = '+d,fts_union_limit_off';
125 SELECT * FROM articles
126 WHERE MATCH (title,body)
127 AGAINST ('ru*' IN BOOLEAN MODE) limit 1;
128 SET debug_dbug = @save_dbug;
129
130 --echo # phrase search
131 SELECT * FROM articles
132 WHERE MATCH (title,body)
133 AGAINST ('"request support"' IN BOOLEAN MODE) limit 1;
134
135 --echo # Without optimization
136 SET debug_dbug = '+d,fts_union_limit_off';
137 SELECT * FROM articles
138 WHERE MATCH (title,body)
139 AGAINST ('"request support"' IN BOOLEAN MODE) limit 1;
140 SET debug_dbug = @save_dbug;
141
142 DROP TABLE articles;
143 SET GLOBAL query_cache_size = @default_cache_size;