Debian Codegraph @

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

raw
1 # This is the basic function tests for innodb FTS
2
3
4 # Create FTS table
5 CREATE 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 # Insert six rows
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 -- disable_result_log
22 ANALYZE TABLE articles;
23 -- enable_result_log
24
25 # Look for 'Database' in table article
26 SELECT * FROM articles
27 WHERE MATCH (title,body)
28 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
29
30 SELECT COUNT(*) FROM articles
31 WHERE MATCH (title,body)
32 AGAINST ('database' IN NATURAL LANGUAGE MODE);
33
34 SELECT * FROM articles
35 WHERE MATCH (title, body)
36 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
37
38
39 SELECT COUNT(IF(MATCH (title,body)
40 AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
41 AS count FROM articles;
42
43 # Select Relevance Ranking
44 SELECT id, body, MATCH (title,body)
45 AGAINST ('Database' IN NATURAL LANGUAGE MODE) AS score
46 FROM articles;
47
48 # 'MySQL' treated as stopword (stopword functionality not yet supported)
49 --sorted_result
50 SELECT * FROM articles
51 WHERE MATCH (title,body)
52 AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);
53
54 # Boolean search
55 # Select rows contain "MySQL" but not "YourSQL"
56 --sorted_result
57 SELECT * FROM articles WHERE MATCH (title,body)
58 AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
59
60 # Select rows contain at least one of the two words
61 SELECT * FROM articles WHERE MATCH (title,body)
62 AGAINST ('DBMS Security' IN BOOLEAN MODE);
63
64 # Select rows contain both "MySQL" and "YourSQL"
65 SELECT * FROM articles WHERE MATCH (title,body)
66 AGAINST ('+MySQL +YourSQL' IN BOOLEAN MODE);
67
68 # Select rows contain "MySQL" but rank rows with "YourSQL" higher
69 --sorted_result
70 SELECT * FROM articles WHERE MATCH (title,body)
71 AGAINST ('+MySQL YourSQL' IN BOOLEAN MODE);
72
73 # Test negation operator. Select rows contain MySQL,
74 # if the row contains "YourSQL", rank it lower
75 --sorted_result
76 SELECT * FROM articles WHERE MATCH (title,body)
77 AGAINST ('+MySQL ~YourSQL' IN BOOLEAN MODE);
78
79 # Test wild card search operator
80 # Notice row with "the" will not get fetched due to
81 # stopword filtering
82 SELECT * FROM articles WHERE MATCH (title,body)
83 AGAINST ('t*' IN BOOLEAN MODE);
84
85 # Test wild card search, notice row 6 with 2 "MySQL" rank first
86 --sorted_result
87 SELECT * FROM articles WHERE MATCH (title,body)
88 AGAINST ('MY*' IN BOOLEAN MODE);
89
90 # Another wild card search
91 SELECT * FROM articles WHERE MATCH (title,body)
92 AGAINST ('ru*' IN BOOLEAN MODE);
93
94 # Test ">" and "<" Operator, the ">" operator increases
95 # the word relevance rank and the "<" operator decreases it
96 # Following test puts rows with "Well" on top and rows
97 # with "stands" at the bottom
98 --sorted_result
99 SELECT * FROM articles WHERE MATCH (title,body)
100 AGAINST ('+ MySQL >Well < stands' IN BOOLEAN MODE);
101
102 # Test sub-expression boolean search. Find rows contain
103 # "MySQL" but not "Well" or "stands".
104 --sorted_result
105 SELECT * FROM articles WHERE MATCH (title,body)
106 AGAINST ('+ MySQL - (Well stands)' IN BOOLEAN MODE);
107
108 # Test sub-expression boolean search. Find rows contain
109 # "MySQL" and "Well" or "MySQL" and "stands". But rank the
110 # doc with "Well" higher, and doc with "stands" lower.
111 --sorted_result
112 SELECT * FROM articles WHERE MATCH (title,body)
113 AGAINST ('+ MySQL + (>Well < stands)' IN BOOLEAN MODE);
114
115 # Test nested sub-expression.
116 --sorted_result
117 SELECT * FROM articles WHERE MATCH (title,body)
118 AGAINST ('YourSQL + (+MySQL - (Tricks Security))' IN BOOLEAN MODE);
119
120 # Find rows with "MySQL" but not "Tricks", "Security" nor "YourSQL"
121 SELECT * FROM articles WHERE MATCH (title,body)
122 AGAINST ('(+MySQL - (Tricks Security)) - YourSQL' IN BOOLEAN MODE);
123
124 # Test non-word delimiter combined with negate "-" operator
125 # This should return the same result as 'mysql - (Security DBMS)'
126 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysql - Security&DBMS' IN BOOLEAN MODE);
127 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysql - (Security DBMS)' IN BOOLEAN MODE);
128
129 # Again, the operator sequence should not matter
130 SELECT * FROM articles WHERE MATCH (title,body) AGAINST (' - Security&DBMS + YourSQL' IN BOOLEAN MODE);
131
132 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+YourSQL - Security&DBMS' IN BOOLEAN MODE);
133
134 # Test query expansion
135 SELECT COUNT(*) FROM articles
136 WHERE MATCH (title,body)
137 AGAINST ('database' WITH QUERY EXPANSION);
138
139 INSERT INTO articles (title,body) VALUES
140 ('test query expansion','for database ...');
141
142 # This query will return result containing word "database" as
143 # the query expand from "test" to words in document just
144 # inserted above
145 --sorted_result
146 SELECT * FROM articles
147 WHERE MATCH (title,body)
148 AGAINST ('test' WITH QUERY EXPANSION);
149
150 # This is to test the proximity search, search two word
151 # "following" and "comparison" within 19 character space
152 SELECT * FROM articles
153 WHERE MATCH (title,body)
154 AGAINST ('"following comparison"@3' IN BOOLEAN MODE);
155
156 # This is to test the proximity search, search two word
157 # "following" and "comparison" within 19 character space
158 # This search should come with no return result
159 SELECT * FROM articles
160 WHERE MATCH (title,body)
161 AGAINST ('"following comparison"@2' IN BOOLEAN MODE);
162
163 # This is to test the phrase search, searching phrase
164 # "following database"
165 SELECT * FROM articles
166 WHERE MATCH (title,body)
167 AGAINST ('"following database"' IN BOOLEAN MODE);
168
169 # Insert into table with similar word of different distances
170 INSERT INTO articles (title,body) VALUES
171 ('test proximity search, test, proximity and phrase',
172 'search, with proximity innodb');
173
174 INSERT INTO articles (title,body) VALUES
175 ('test my proximity fts new search, test, proximity and phrase',
176 'search, with proximity innodb');
177
178 INSERT INTO articles (title,body) VALUES
179 ('test more of proximity fts search, test, more proximity and phrase',
180 'search, with proximity innodb');
181
182 # This should only return the first document
183 SELECT * FROM articles
184 WHERE MATCH (title,body)
185 AGAINST ('"proximity search"@3' IN BOOLEAN MODE);
186
187 # This would return no document
188 SELECT * FROM articles
189 WHERE MATCH (title,body)
190 AGAINST ('"proximity search"@2' IN BOOLEAN MODE);
191
192 # This give you all three documents
193 SELECT * FROM articles
194 WHERE MATCH (title,body)
195 AGAINST ('"proximity search"@5' IN BOOLEAN MODE);
196
197 # Similar boundary testing for the words
198 SELECT * FROM articles
199 WHERE MATCH (title,body)
200 AGAINST ('"test proximity"@5' IN BOOLEAN MODE);
201
202 # No document will be returned
203 SELECT * FROM articles
204 WHERE MATCH (title,body)
205 AGAINST ('"test proximity"@1' IN BOOLEAN MODE);
206
207 # All three documents will be returned
208 SELECT * FROM articles
209 WHERE MATCH (title,body)
210 AGAINST ('"test proximity"@4' IN BOOLEAN MODE);
211
212 # Only two document will be returned.
213 SELECT * FROM articles
214 WHERE MATCH (title,body)
215 AGAINST ('"test proximity"@3' IN BOOLEAN MODE);
216
217 # Test with more word The last document will return, please notice there
218 # is no ordering requirement for proximity search.
219 SELECT * FROM articles
220 WHERE MATCH (title,body)
221 AGAINST ('"more test proximity"@4' IN BOOLEAN MODE);
222
223 SELECT * FROM articles
224 WHERE MATCH (title,body)
225 AGAINST ('"more test proximity"@3' IN BOOLEAN MODE);
226
227 # The phrase search will not require exact word ordering
228 SELECT * FROM articles
229 WHERE MATCH (title,body)
230 AGAINST ('"more test proximity"' IN BOOLEAN MODE);
231
232 drop table articles;
233
234 --echo #
235 --echo # Bug #22679185 INVALID INNODB FTS DOC ID DURING INSERT
236 --echo #
237
238 create table t1 (f1 int not null primary key, f2 varchar(100),
239 FTS_DOC_ID bigint(20) unsigned not null,
240 unique key `FTS_DOC_ID_INDEX` (`FTS_DOC_ID`),
241 fulltext key (f2))engine=innodb;
242
243 insert into t1 values(1, "This is the first record", 20000);
244 insert into t1 values(2, "This is the second record", 40000);
245 select FTS_DOC_ID from t1;
246 drop table t1;
247
248
249 create table t1 (f1 int not null primary key, f2 varchar(100),
250 FTS_DOC_ID bigint(20) unsigned not null auto_increment,
251 unique key `FTS_DOC_ID_INDEX` (`FTS_DOC_ID`),
252 fulltext key (f2))engine=innodb;
253
254 set auto_increment_increment = 65535;
255 insert into t1(f1, f2) values(1, "This is the first record");
256 insert into t1(f1, f2) values(2, "This is the second record");
257 insert into t1(f1, f2) values(3, "This is the third record");
258 select FTS_DOC_ID from t1;
259 drop table t1;
260
261 create table t1( a text character set utf8mb4 collate utf8mb4_czech_ci not null, fulltext key(a))engine=innodb;
262 insert into t1 values('');
263 select match(a)against(0xf3) from t1;
264 drop table t1;
265
266 call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Doc ID 20030101000000 is too big. Its difference with largest used Doc ID 0 cannot exceed or equal to 65535");
267 CREATE TABLE t1 (FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
268 title VARCHAR(200), FULLTEXT(title)) ENGINE=InnoDB;
269 --error 182
270 INSERT INTO t1 VALUES (NULL, NULL), (20030101000000, 20030102000000);
271 DROP TABLE t1;