Debian Codegraph @

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

raw
1 # This test for FTS index with big records
2 # case a) more words in single record
3 # b) more words across records
4
5 --source include/have_innodb.inc
6 --source include/maybe_versioning.inc
7
8 --disable_warnings
9 DROP TABLE IF EXISTS t1;
10 --enable_warnings
11
12 # Create FTS table
13 EVAL CREATE TABLE t1 (
14 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
15 a TEXT,
16 b TEXT
17 ) ENGINE = InnoDB;
18
19 CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
20
21 let $counter = 1;
22 --disable_query_log
23
24 # Generate input file using perl
25 perl;
26 use strict;
27 my $fname= "$ENV{'MYSQLTEST_VARDIR'}/tmp/fts_input_data1.txt";
28 open FH,">$fname";
29 my $record_counter = 1;
30 while ($record_counter < 50) {
31 my $word_counter = 1;
32 my ($col1,$col2);
33 while ($word_counter < 51) {
34 $col1 = $col1. "row".$record_counter."col1"."word".$word_counter." ";
35 $col2 = $col2. "row".$record_counter."col2"."word".$word_counter." ";
36 $word_counter++;
37 }
38 print FH "$col1,$col2\n";
39 $record_counter++;
40 }
41 close FH;
42 EOF
43
44 EVAL LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/fts_input_data1.txt' INTO
45 TABLE t1 FIELDS TERMINATED BY ',' (a,b);
46 --enable_query_log
47 --echo "Loading data using LOAD DATA Command , File <MYSQLTEST_VARDIR>/tmp/fts_input_data1.txt"
48
49 # Create the FTS index again
50 CREATE FULLTEXT INDEX idx on t1 (a,b);
51
52 SELECT COUNT(*) FROM t1;
53
54 # Select word "tutorial" in the table
55 SELECT FTS_DOC_ID FROM t1 WHERE MATCH (a,b)
56 AGAINST ('row35col2word49' IN NATURAL LANGUAGE MODE);
57
58 # boolean mode
59 SELECT COUNT(*) from t1
60 WHERE MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
61 SELECT COUNT(*) from t1
62 WHERE MATCH(a,b) AGAINST("+row5col2word49" IN BOOLEAN MODE);
63 SELECT COUNT(*) from t1
64 WHERE MATCH(a,b) AGAINST("+row35col2word49 +(row35col1word49 row35col2word40)" IN BOOLEAN MODE);
65 SELECT COUNT(*) from t1
66 WHERE MATCH(a,b) AGAINST("+row35col2word49 -(row45col2word49)" IN BOOLEAN MODE);
67 SELECT COUNT(*) from t1
68 WHERE MATCH(a,b) AGAINST("row5col2word49 row5col2word40" IN BOOLEAN MODE);
69 SELECT COUNT(*) from t1
70 WHERE MATCH a,b AGAINST ("+row5col2word* +row5col1word49*" IN BOOLEAN MODE);
71 SELECT COUNT(*) from t1
72 WHERE MATCH a,b AGAINST ('"row35col2word49"' IN BOOLEAN MODE);
73 SELECT COUNT(*) from t1
74 WHERE MATCH a,b AGAINST ('"ROW35col2WORD49"' IN BOOLEAN MODE);
75
76 # query expansion
77 SELECT COUNT(*) from t1
78 WHERE MATCH(a,b) AGAINST ("row5col2word49" WITH QUERY EXPANSION);
79
80 SELECT FTS_DOC_ID FROM t1
81 WHERE MATCH (a,b)
82 AGAINST ('"row5col2word48 row5col2word49"@2' IN BOOLEAN MODE);
83
84 SELECT * FROM t1
85 WHERE MATCH (a,b)
86 AGAINST ('"row5col2word48 row5col2word49"@1' IN BOOLEAN MODE);
87
88 UPDATE t1 SET a = "using update" , b = "changing fulltext index record", FTS_DOC_ID = FTS_DOC_ID + 10000
89 WHERE MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
90
91 SELECT a,b FROM t1 WHERE MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
92 SELECT a,b FROM t1 WHERE MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
93 SELECT a,b FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
94
95 DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
96 SELECT a,b FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
97
98 --remove_file '$MYSQLTEST_VARDIR/tmp/fts_input_data1.txt';
99 DROP TABLE t1;
100
101 #--------------------------------------------------------------------------------------------
102 # Create FTS table
103 EVAL CREATE TABLE t1 (
104 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
105 a TEXT,
106 b TEXT
107 ) ENGINE = InnoDB;
108
109 CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
110
111 let $counter = 1;
112 --disable_query_log
113
114 # Generate input file using perl
115 perl;
116 use strict;
117 my $fname= "$ENV{'MYSQLTEST_VARDIR'}/tmp/fts_input_data2.txt";
118 open FH,">$fname";
119 my $record_counter = 1;
120 while ($record_counter < 101) {
121 my $word_counter = 1;
122 my ($col1,$col2);
123 while ($word_counter < 50) {
124 $col1 = $col1. "row".$record_counter."col1"."word".$word_counter." ";
125 $col2 = $col2. "row".$record_counter."col2"."word".$word_counter." ";
126 $word_counter++;
127 }
128 print FH "$col1,$col2\n";
129 $record_counter++;
130 }
131 close FH;
132 EOF
133
134 EVAL LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/fts_input_data2.txt'
135 INTO TABLE t1 FIELDS TERMINATED BY ',' (a,b);
136 --enable_query_log
137 --echo "Loading data using LOAD DATA Command , File <MYSQLTEST_VARDIR>/tmp/fts_input_data2.txt"
138
139 # Create the FTS index again
140 CREATE FULLTEXT INDEX idx on t1 (a,b);
141
142 SELECT COUNT(*) FROM t1;
143
144 SELECT FTS_DOC_ID from t1 WHERE b like '%row300col2word30%';
145
146 SELECT FTS_DOC_ID FROM t1 WHERE MATCH (a,b)
147 AGAINST ('row35col2word49' IN NATURAL LANGUAGE MODE);
148
149 # boolean mode
150 SELECT COUNT(*) from t1
151 WHERE MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
152 SELECT COUNT(*) from t1
153 WHERE MATCH(a,b) AGAINST("+row5col2word49" IN BOOLEAN MODE);
154 SELECT COUNT(*) from t1
155 WHERE MATCH(a,b) AGAINST("+row35col2word49 +(row35col1word49 row35col2word40)" IN BOOLEAN MODE);
156 SELECT COUNT(*) from t1
157 WHERE MATCH(a,b) AGAINST("+row35col2word49 -(row45col2word49)" IN BOOLEAN MODE);
158 SELECT COUNT(*) from t1
159 WHERE MATCH(a,b) AGAINST("row5col2word49 row5col2word40" IN BOOLEAN MODE);
160 SELECT COUNT(*) from t1
161 WHERE MATCH a,b AGAINST ("+row5col2word* +row5col1word49*" IN BOOLEAN MODE);
162 SELECT COUNT(*) from t1
163 WHERE MATCH a,b AGAINST ('"row35col2word49"' IN BOOLEAN MODE);
164 SELECT COUNT(*) from t1
165 WHERE MATCH a,b AGAINST ('"ROW35col2WORD49"' IN BOOLEAN MODE);
166
167 # query expansion
168 SELECT COUNT(*) from t1 WHERE MATCH(a,b) AGAINST ("row5col2word49" WITH QUERY EXPANSION);
169
170 SELECT FTS_DOC_ID FROM t1
171 WHERE MATCH (a,b)
172 AGAINST ('"row5col2word48 row5col2word49"@2' IN BOOLEAN MODE);
173
174 SELECT * FROM t1
175 WHERE MATCH (a,b)
176 AGAINST ('"row5col2word48 row5col2word49"@1' IN BOOLEAN MODE);
177
178 UPDATE t1 SET a = "using update" , b = "changing fulltext index record", FTS_DOC_ID = FTS_DOC_ID + 10000
179 WHERE MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
180
181 SELECT a,b FROM t1
182 WHERE MATCH(a,b) AGAINST("+row5col2word49 +row5col1word49" IN BOOLEAN MODE);
183 SELECT a,b FROM t1
184 WHERE MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
185 SELECT a,b FROM t1
186 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
187
188 DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
189 SELECT a,b FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
190
191 ALTER TABLE t1 DROP INDEX idx;
192 # Create the FTS index again
193 CREATE FULLTEXT INDEX idx on t1 (a,b);
194 UPDATE t1 SET a = NULL , b = NULL, FTS_DOC_ID= 6000 + FTS_DOC_ID;
195
196 SELECT COUNT(*) FROM t1 WHERE a IS NULL AND b IS NULL;
197 ALTER TABLE t1 DROP INDEX idx;
198 SELECT COUNT(*) FROM t1 WHERE a IS NULL AND b IS NULL;
199
200 --remove_file '$MYSQLTEST_VARDIR/tmp/fts_input_data2.txt';
201 DROP TABLE t1;
202
203 #--------------------------------------------------------------------------------------------
204 # Create FTS table
205 EVAL CREATE TABLE t1 (
206 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
207 a TEXT,
208 b TEXT
209 ) ENGINE = InnoDB;
210
211 CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
212
213 let $counter = 1;
214 --disable_query_log
215
216 # Generate input file using perl
217 perl;
218 use strict;
219 my $fname= "$ENV{'MYSQLTEST_VARDIR'}/tmp/fts_input_data3.txt";
220 open FH,">$fname";
221 my $record_counter = 1;
222 while ($record_counter < 101) {
223 my $word_counter = 1;
224 my ($col1,$col2);
225 while ($word_counter < 50) {
226 $col1 = $col1. "samerowword" ." ";
227 $col2 = $col2. "samerowword" ." ";
228 $word_counter++;
229 }
230 print FH "$col1,$col2\n";
231 $record_counter++;
232 }
233 close FH;
234 EOF
235
236 EVAL LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/fts_input_data3.txt'
237 INTO TABLE t1 FIELDS TERMINATED BY ',' (a,b);
238 --enable_query_log
239 --echo "Loading data using LOAD DATA Command , File <MYSQLTEST_VARDIR>/tmp/fts_input_data3.txt"
240
241 # Create the FTS index again
242 CREATE FULLTEXT INDEX idx on t1 (a,b);
243
244 SELECT COUNT(*) FROM t1;
245
246 SELECT COUNT(*) from t1 WHERE b like '%samerowword%';
247
248 SELECT COUNT(*) FROM t1 WHERE MATCH (a,b)
249 AGAINST ('samerowword' IN NATURAL LANGUAGE MODE);
250
251 # boolean mode
252 SELECT COUNT(*) from t1
253 WHERE MATCH(a,b) AGAINST("+samerowword +samerowword" IN BOOLEAN MODE);
254 SELECT COUNT(*) from t1
255 WHERE MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
256 SELECT COUNT(*) from t1
257 WHERE MATCH(a,b) AGAINST("+samerowword -(row45col2word49)" IN BOOLEAN MODE);
258 SELECT COUNT(*) from t1
259 WHERE MATCH a,b AGAINST ("+sameroww" IN BOOLEAN MODE);
260
261 # query expansion
262 SELECT COUNT(*) from t1
263 WHERE MATCH(a,b) AGAINST ("samerowword" WITH QUERY EXPANSION);
264
265 UPDATE t1 SET a = "using update" , b = "changing fulltext index record",
266 FTS_DOC_ID = FTS_DOC_ID + 10000
267 WHERE MATCH(a,b) AGAINST("+samerowword +samerowword" IN BOOLEAN MODE);
268
269 SELECT COUNT(*) from t1
270 WHERE MATCH(a,b) AGAINST("+samerowword +samerowword" IN BOOLEAN MODE);
271 SELECT COUNT(*) from t1
272 WHERE MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
273 SELECT COUNT(*) from t1
274 WHERE MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
275 SELECT COUNT(*) from t1
276 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
277
278 DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
279
280 SELECT COUNT(*) from t1
281 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
282
283 ALTER TABLE t1 DROP INDEX idx;
284 # Create the FTS index again
285 CREATE FULLTEXT INDEX idx on t1 (a,b);
286 UPDATE t1 SET a = NULL , b = NULL ;
287
288 SELECT COUNT(*) FROM t1 WHERE a IS NULL AND b IS NULL;
289 ALTER TABLE t1 DROP INDEX idx;
290 SELECT COUNT(*) FROM t1 WHERE a IS NULL AND b IS NULL;
291
292 --remove_file '$MYSQLTEST_VARDIR/tmp/fts_input_data3.txt';
293 DROP TABLE t1;
294
295 #--------------------------------------------------------------------------------------------
296 # Create FTS with same word and numbers
297 EVAL CREATE TABLE t1 (
298 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
299 a TEXT,
300 b TEXT
301 ) ENGINE = InnoDB;
302
303 CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
304
305
306 let $counter = 1;
307 --disable_query_log
308
309 # Generate input file using perl
310 perl;
311 use strict;
312 my $fname= "$ENV{'MYSQLTEST_VARDIR'}/tmp/fts_input_data4.txt";
313 open FH,">$fname";
314 my $record_counter = 1;
315 while ($record_counter < 101) {
316 my $word_counter = 1001;
317 my ($col1,$col2);
318 while ($word_counter < 1101) {
319 $col1 = $col1. "samerowword" ." ";
320 $col2 = $col2. "$word_counter" ." ";
321 $word_counter++;
322 }
323 print FH "$col1,$col2\n";
324 $record_counter++;
325 }
326 close FH;
327 EOF
328
329 EVAL LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/fts_input_data4.txt'
330 INTO TABLE t1 FIELDS TERMINATED BY ',' (a,b);
331 --enable_query_log
332 --echo "Loading data using LOAD DATA Command , File <MYSQLTEST_VARDIR>/tmp/fts_input_data4.txt"
333
334 # Create the FTS index again
335 CREATE FULLTEXT INDEX idx on t1 (a,b);
336
337 SELECT COUNT(*) FROM t1;
338
339 SELECT COUNT(*) from t1 WHERE a like '%samerowword%';
340
341 SELECT COUNT(*) FROM t1 WHERE MATCH (a,b)
342 AGAINST ('samerowword' IN NATURAL LANGUAGE MODE);
343
344 # boolean mode
345 SELECT COUNT(*) from t1 WHERE MATCH(a,b) AGAINST("+samerowword +1050" IN BOOLEAN MODE);
346 SELECT COUNT(*) from t1 WHERE MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
347 SELECT COUNT(*) from t1 WHERE MATCH(a,b) AGAINST("+samerowword -(1050)" IN BOOLEAN MODE);
348 SELECT COUNT(*) from t1 WHERE MATCH a,b AGAINST ("+2001" IN BOOLEAN MODE);
349
350 # query expansion
351 SELECT COUNT(*) from t1 WHERE MATCH(a,b) AGAINST ("samerowword" WITH QUERY EXPANSION);
352
353 UPDATE t1 SET a = "using update" , b = "changing fulltext index record",
354 FTS_DOC_ID = FTS_DOC_ID + 10000
355 WHERE MATCH(a,b) AGAINST("+samerowword +1050" IN BOOLEAN MODE);
356
357 SELECT COUNT(*) from t1
358 WHERE MATCH(a,b) AGAINST("+samerowword +1050" IN BOOLEAN MODE);
359 SELECT COUNT(*) from t1
360 WHERE MATCH(a,b) AGAINST("+samerowword" IN BOOLEAN MODE);
361 SELECT COUNT(*) from t1
362 WHERE MATCH(a,b) AGAINST("changing fulltext" IN BOOLEAN MODE);
363 SELECT COUNT(*) from t1
364 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
365
366 DELETE FROM t1
367 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
368 SELECT COUNT(*) from t1
369 WHERE MATCH(a,b) AGAINST("+chang* +fulltext" IN BOOLEAN MODE);
370
371 ALTER TABLE t1 DROP INDEX idx;
372 # Create the FTS index again
373 CREATE FULLTEXT INDEX idx on t1 (a,b);
374 UPDATE t1 SET a = NULL , b = NULL ;
375
376 SELECT COUNT(*) FROM t1 WHERE a IS NULL AND b IS NULL;
377 ALTER TABLE t1 DROP INDEX idx;
378 SELECT COUNT(*) FROM t1 WHERE a IS NULL AND b IS NULL;
379
380 --remove_file '$MYSQLTEST_VARDIR/tmp/fts_input_data4.txt';
381 DROP TABLE t1;
382