Debian Codegraph @

mysql-test/suite/innodb_fts/t/basic.inc

raw
1 if ($basic_stage == create_table)
2 {
3 # Create FTS table
4 --error ER_NO_INDEX_ON_TEMPORARY
5 CREATE TEMPORARY TABLE articles (
6 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
7 title VARCHAR(200),
8 body TEXT,
9 FULLTEXT (title,body)
10 ) ENGINE=InnoDB;
11
12 --disable_query_log
13 eval CREATE TABLE articles (
14 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
15 title VARCHAR(200),
16 body TEXT,
17 FULLTEXT (title,body)
18 )$create_options ENGINE=InnoDB;
19 --enable_query_log
20 }
21
22 if ($basic_stage == insert_1)
23 {
24 # Insert six rows
25 INSERT INTO articles (title,body) VALUES
26 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
27 ('How To Use MySQL Well','After you went through a ...'),
28 ('Optimizing MySQL','In this tutorial we will show ...'),
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
34 if ($basic_stage == select_1)
35 {
36 # Look for 'Database' in table article
37 SELECT * FROM articles
38 WHERE MATCH (title,body)
39 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
40
41 SELECT COUNT(*) FROM articles
42 WHERE MATCH (title,body)
43 AGAINST ('database' IN NATURAL LANGUAGE MODE);
44
45 SELECT * FROM articles
46 WHERE MATCH (title, body)
47 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
48
49
50 SELECT COUNT(IF(MATCH (title,body)
51 AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
52 AS count FROM articles;
53
54 # Select Relevance Ranking
55 SELECT id, body, MATCH (title,body)
56 AGAINST ('Database' IN NATURAL LANGUAGE MODE) AS score
57 FROM articles;
58
59 # 'MySQL' treated as stopword (stopword functionality not yet supported)
60 SELECT * FROM articles
61 WHERE MATCH (title,body)
62 AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);
63
64 # Boolean search
65 # Select rows contain "MySQL" but not "YourSQL"
66 SELECT * FROM articles WHERE MATCH (title,body)
67 AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
68
69 # Select rows contain at least one of the two words
70 SELECT * FROM articles WHERE MATCH (title,body)
71 AGAINST ('DBMS Security' IN BOOLEAN MODE);
72
73 # Select rows contain both "MySQL" and "YourSQL"
74 SELECT * FROM articles WHERE MATCH (title,body)
75 AGAINST ('+MySQL +YourSQL' IN BOOLEAN MODE);
76
77 # Select rows contain "MySQL" but rank rows with "YourSQL" higher
78 SELECT * FROM articles WHERE MATCH (title,body)
79 AGAINST ('+MySQL YourSQL' IN BOOLEAN MODE);
80
81 # Test negation operator. Select rows contain MySQL,
82 # if the row contains "YourSQL", rank it lower
83 SELECT * FROM articles WHERE MATCH (title,body)
84 AGAINST ('+MySQL ~YourSQL' IN BOOLEAN MODE);
85
86 # Test wild card search operator
87 # Notice row with "the" will not get fetched due to
88 # stopword filtering
89 SELECT * FROM articles WHERE MATCH (title,body)
90 AGAINST ('t*' IN BOOLEAN MODE);
91
92 # Test wild card search, notice row 6 with 2 "MySQL" rank first
93 SELECT * FROM articles WHERE MATCH (title,body)
94 AGAINST ('MY*' IN BOOLEAN MODE);
95
96 # Another wild card search
97 SELECT * FROM articles WHERE MATCH (title,body)
98 AGAINST ('ru*' IN BOOLEAN MODE);
99
100 # Test ">" and "<" Operator, the ">" operator increases
101 # the word relevance rank and the "<" operator decreases it
102 # Following test puts rows with "Well" on top and rows
103 # with "stands" at the bottom
104 SELECT * FROM articles WHERE MATCH (title,body)
105 AGAINST ('+ MySQL >Well < stands' IN BOOLEAN MODE);
106
107 # Test sub-expression boolean search. Find rows contain
108 # "MySQL" but not "Well" or "stands".
109 SELECT * FROM articles WHERE MATCH (title,body)
110 AGAINST ('+ MySQL - (Well stands)' IN BOOLEAN MODE);
111
112 --error 128
113 SELECT * FROM articles WHERE MATCH (title,body) AGAINST
114 ('(((((((((((((((((((((((((((((((((Security)))))))))))))))))))))))))))))))))'
115 IN BOOLEAN MODE);
116 SELECT * FROM articles WHERE MATCH (title,body) AGAINST
117 ('((((((((((((((((((((((((((((((((Security))))))))))))))))))))))))))))))))'
118 IN BOOLEAN MODE);
119 SELECT * FROM articles WHERE MATCH (title,body) AGAINST
120 ('(((((((((((((((((((((((((((((((vs))))))))))))))))))))))))))))))),(((to)))'
121 IN BOOLEAN MODE);
122
123 --error ER_PARSE_ERROR
124 SELECT * FROM articles WHERE MATCH (title,body) AGAINST
125 ('((((((((((((((((((((((((((((((((Security)))))))))))))))))))))))))))))))'
126 IN BOOLEAN MODE);
127 --error ER_PARSE_ERROR
128 SELECT * FROM articles WHERE MATCH (title,body) AGAINST
129 ('(((((((((((((((((((((((((((((((((Security))))))))))))))))))))))))))))))))'
130 IN BOOLEAN MODE);
131
132 # Test sub-expression boolean search. Find rows contain
133 # "MySQL" and "Well" or "MySQL" and "stands". But rank the
134 # doc with "Well" higher, and doc with "stands" lower.
135 SELECT * FROM articles WHERE MATCH (title,body)
136 AGAINST ('+ MySQL + (>Well < stands)' IN BOOLEAN MODE);
137
138 # Test nested sub-expression.
139 SELECT * FROM articles WHERE MATCH (title,body)
140 AGAINST ('YourSQL + (+MySQL - (Tricks Security))' IN BOOLEAN MODE);
141
142 # Find rows with "MySQL" but not "Tricks", "Security" nor "YourSQL"
143 SELECT * FROM articles WHERE MATCH (title,body)
144 AGAINST ('(+MySQL - (Tricks Security)) - YourSQL' IN BOOLEAN MODE);
145
146 # Test non-word delimiter combined with negate "-" operator
147 # This should return the same result as 'mysql - (Security DBMS)'
148 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysql - Security&DBMS' IN BOOLEAN MODE);
149 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysql - (Security DBMS)' IN BOOLEAN MODE);
150
151 # Again, the operator sequence should not matter
152 SELECT * FROM articles WHERE MATCH (title,body) AGAINST (' - Security&DBMS + YourSQL' IN BOOLEAN MODE);
153
154 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+YourSQL - Security&DBMS' IN BOOLEAN MODE);
155
156 # Test query expansion
157 SELECT COUNT(*) FROM articles
158 WHERE MATCH (title,body)
159 AGAINST ('database' WITH QUERY EXPANSION);
160 }
161
162 if ($basic_stage == insert_2)
163 {
164 INSERT INTO articles (title,body) VALUES
165 ('test query expansion','for database ...');
166 }
167
168 if ($basic_stage == select_2)
169 {
170 # This query will return result containing word "database" as
171 # the query expand from "test" to words in document just
172 # inserted above
173 SELECT * FROM articles
174 WHERE MATCH (title,body)
175 AGAINST ('test' WITH QUERY EXPANSION);
176
177 # This is to test the proximity search, search two word
178 # "following" and "comparison" within 19 character space
179 SELECT * FROM articles
180 WHERE MATCH (title,body)
181 AGAINST ('"following comparison"@3' IN BOOLEAN MODE);
182
183 # This is to test the proximity search, search two word
184 # "following" and "comparison" within 19 character space
185 # This search should come with no return result
186 SELECT * FROM articles
187 WHERE MATCH (title,body)
188 AGAINST ('"following comparison"@2' IN BOOLEAN MODE);
189
190 # This is to test the phrase search, searching phrase
191 # "following database"
192 SELECT * FROM articles
193 WHERE MATCH (title,body)
194 AGAINST ('"following database"' IN BOOLEAN MODE);
195 }
196
197 if ($basic_stage == insert_3)
198 {
199 # Insert into table with similar word of different distances
200 INSERT INTO articles (title,body) VALUES
201 ('test proximity search, test, proximity and phrase',
202 'search, with proximity innodb');
203
204 INSERT INTO articles (title,body) VALUES
205 ('test my proximity fts new search, test, proximity and phrase',
206 'search, with proximity innodb');
207
208 INSERT INTO articles (title,body) VALUES
209 ('test more of proximity fts search, test, more proximity and phrase',
210 'search, with proximity innodb');
211 }
212
213 if ($basic_stage == select_3)
214 {
215 # This should only return the first document
216 SELECT * FROM articles
217 WHERE MATCH (title,body)
218 AGAINST ('"proximity search"@3' IN BOOLEAN MODE);
219
220 # This would return no document
221 SELECT * FROM articles
222 WHERE MATCH (title,body)
223 AGAINST ('"proximity search"@2' IN BOOLEAN MODE);
224
225 # This give you all three documents
226 SELECT * FROM articles
227 WHERE MATCH (title,body)
228 AGAINST ('"proximity search"@5' IN BOOLEAN MODE);
229
230 # Similar boundary testing for the words
231 SELECT * FROM articles
232 WHERE MATCH (title,body)
233 AGAINST ('"test proximity"@5' IN BOOLEAN MODE);
234
235 # No document will be returned
236 SELECT * FROM articles
237 WHERE MATCH (title,body)
238 AGAINST ('"test proximity"@1' IN BOOLEAN MODE);
239
240 # All three documents will be returned
241 SELECT * FROM articles
242 WHERE MATCH (title,body)
243 AGAINST ('"test proximity"@4' IN BOOLEAN MODE);
244
245 # Only two document will be returned.
246 SELECT * FROM articles
247 WHERE MATCH (title,body)
248 AGAINST ('"test proximity"@3' IN BOOLEAN MODE);
249
250 # Test with more word The last document will return, please notice there
251 # is no ordering requirement for proximity search.
252 SELECT * FROM articles
253 WHERE MATCH (title,body)
254 AGAINST ('"more test proximity"@4' IN BOOLEAN MODE);
255
256 SELECT * FROM articles
257 WHERE MATCH (title,body)
258 AGAINST ('"more test proximity"@3' IN BOOLEAN MODE);
259
260 # The phrase search will not require exact word ordering
261 SELECT * FROM articles
262 WHERE MATCH (title,body)
263 AGAINST ('"more test proximity"' IN BOOLEAN MODE);
264 }