Debian Codegraph @

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

raw
1
2 # This is the DDL function tests for innodb FTS
3 # Functional testing with FTS proximity search using '@'
4 # and try search default words
5
6 --disable_warnings
7 drop table if exists t1;
8 --enable_warnings
9
10 --disable_query_log
11 let $innodb_file_per_table_orig = `select @@innodb_file_per_table`;
12 --enable_query_log
13
14 # Create FTS table
15 CREATE TABLE t1 (
16 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
17 a VARCHAR(200),
18 b TEXT
19 ) ENGINE= InnoDB;
20
21 # Create the FTS index again
22 CREATE FULLTEXT INDEX idx on t1 (a,b);
23
24 # Insert rows
25 INSERT INTO t1 (a,b) VALUES
26 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
27 ('when To Use MySQL Well','After that you went through a ...'),
28 ('where will Optimizing MySQL','what In this tutorial we will show ...');
29
30 # Try to Search default stopword from innodb, "where", "will", "what"
31 # and "when" are all stopwords
32 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("where will");
33 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("when");
34 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("what" WITH QUERY EXPANSION);
35
36 # boolean No result expected
37 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("whe*" IN BOOLEAN MODE);
38 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+what +will" IN BOOLEAN MODE);
39 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+from" IN BOOLEAN MODE);
40 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+where +(show what)" IN BOOLEAN MODE);
41
42 # no result expected. Words are filtered out as stopwords
43 SELECT * FROM t1
44 WHERE MATCH (a,b)
45 AGAINST ('"where will"@6' IN BOOLEAN MODE);
46
47 # no result expected
48 SELECT * FROM t1
49 WHERE MATCH (a,b)
50 AGAINST ('"where will"@9' IN BOOLEAN MODE);
51
52 # insert record with @ character which is used in proximity search
53 INSERT INTO t1 (a,b) VALUES
54 ('MySQL Tutorial','request [email protected] ...') ,
55 ('Trial version','query performace @1255 minute on 2.1Hz Memory 2GB...') ,
56 ('when To Use MySQL Well','for free faq [email protected] ...');
57 # proximity search with @ charcter
58
59 # We don't need more than one word in proximity search. Single word
60 # treated as single word search
61 SELECT * FROM t1
62 WHERE MATCH (a,b)
63 AGAINST ('"request"@10' IN BOOLEAN MODE);
64
65 # If the distance is 0, it is treated as "phrase search"
66 SELECT * FROM t1
67 WHERE MATCH (a,b)
68 AGAINST ('"Trial version"@0' IN BOOLEAN MODE);
69
70 # @ is word separator
71 SELECT * FROM t1
72 WHERE MATCH (a,b)
73 AGAINST ('"request [email protected]"@10' IN BOOLEAN MODE);
74
75 # This should not return any document
76 SELECT * FROM t1
77 WHERE MATCH (a,b)
78 AGAINST ('"1255 minute"@1' IN BOOLEAN MODE);
79
80 # This should return the first document. That is "1255" and "minutes" are
81 # in a two-word range (adjacent)
82 SELECT * FROM t1
83 WHERE MATCH (a,b)
84 AGAINST ('"1255 minute"@2' IN BOOLEAN MODE);
85
86 SELECT * FROM t1
87 WHERE MATCH (a,b)
88 AGAINST ('"1255"@10' IN BOOLEAN MODE);
89
90 SELECT * FROM t1
91 WHERE MATCH (a,b)
92 AGAINST ('1255' WITH QUERY EXPANSION);
93
94 SELECT * FROM t1
95 WHERE MATCH (a,b)
96 AGAINST ('"request docteam"@2' IN BOOLEAN MODE);
97
98 SELECT * FROM t1
99 WHERE MATCH (a,b)
100 AGAINST ('"1255 minute"' IN BOOLEAN MODE);
101
102 SELECT * FROM t1
103 WHERE MATCH (a,b)
104 AGAINST ('request [email protected]');
105
106 # Test across fields search
107 SELECT * FROM t1
108 WHERE MATCH (a,b)
109 AGAINST ('"MySQL request"@3' IN BOOLEAN MODE);
110
111 # Two words are in 10 words range
112 SELECT * FROM t1
113 WHERE MATCH (a,b)
114 AGAINST ('"Trial memory"@10' IN BOOLEAN MODE);
115
116 SELECT * FROM t1
117 WHERE MATCH (a,b)
118 AGAINST ('"Trial memory"@9' IN BOOLEAN MODE);
119
120 DROP TABLE t1;
121
122 # test on utf8 encoded proximity search
123 CREATE TABLE t1 (
124 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
125 a VARCHAR(200),
126 b TEXT
127 ) CHARACTER SET = UTF8, ENGINE= InnoDB;
128
129 INSERT INTO t1 (a,b) VALUES
130 ('MySQL from Tutorial','DBMS stands for DataBase ...') ,
131 ('when To Use MySQL Well','After that you went through a ...'),
132 ('where will Optimizing MySQL','what In this tutorial we will show ...');
133
134 CREATE FULLTEXT INDEX idx on t1 (a,b);
135
136 INSERT INTO t1 (a,b) VALUES
137 ('MySQL Tutorial','request [email protected] ...') ,
138 ('Trial version','query performace @1255 minute on 2.1Hz Memory 2GB...'),
139 ('when To Use MySQL Well','for free faq [email protected] ...');
140
141 # Should have 2 rows. Note proximity search does require words in order
142 SELECT * FROM t1
143 WHERE MATCH (a,b)
144 AGAINST ('"mysql use"@2' IN BOOLEAN MODE);
145
146 # Should return 0 row
147 SELECT * FROM t1
148 WHERE MATCH (a,b)
149 AGAINST ('"mysql use"@1' IN BOOLEAN MODE);
150
151 INSERT INTO t1 (a,b) VALUES ('XYZ, long blob', repeat("a", 9000));
152
153 INSERT IGNORE INTO t1 (a,b) VALUES (repeat("b", 9000), 'XYZ, long blob');
154
155 # 2 rows match
156 SELECT count(*) FROM t1
157 WHERE MATCH (a,b)
158 AGAINST ('"xyz blob"@3' IN BOOLEAN MODE);
159
160 DROP TABLE t1;
161
162 set global innodb_file_per_table=1;
163
164 # Test fts with externally stored long column
165 CREATE TABLE t1 (
166 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
167 a TEXT,
168 b TEXT,
169 c TEXT
170 ) CHARACTER SET = UTF8, ROW_FORMAT=DYNAMIC, ENGINE= InnoDB;
171
172 INSERT INTO t1 (a,b,c) VALUES (repeat("b", 19000), 'XYZ, long text', 'very long blob');
173 INSERT INTO t1 (a,b,c) VALUES (repeat("b", 19000), 'XYZ, very little long blob very much blob', 'very long blob');
174
175 # Note 租车 is count as one word
176 INSERT INTO t1 (a,b,c) VALUES (repeat("b", 19000),"very 租车 供 blob","new 供需分析information");
177 CREATE FULLTEXT INDEX idx on t1 (a,b,c);
178
179 INSERT INTO t1 (a,b,c) VALUES (repeat("x", 19000), 'new, long text', 'very new blob');
180 INSERT INTO t1 (a,b,c) VALUES ('interesting, long text', repeat("x", 19000), 'very very good new blob');
181
182 # 3 rows should match
183 SELECT count(*) FROM t1
184 WHERE MATCH (a,b,c)
185 AGAINST ('"very blob"@3' IN BOOLEAN MODE);
186
187 SELECT count(*) FROM t1
188 WHERE MATCH (a,b,c)
189 AGAINST ('"very long blob"@0' IN BOOLEAN MODE);
190
191 # 4 rows should match
192 SELECT count(*) FROM t1
193 WHERE MATCH (a,b,c)
194 AGAINST ('"very blob"@4' IN BOOLEAN MODE);
195
196 # 1 row should match
197 SELECT count(*) FROM t1
198 WHERE MATCH (a,b,c)
199 AGAINST ('"interesting blob"@9' IN BOOLEAN MODE);
200
201 # should have 3 rows
202 SELECT COUNT(*) FROM t1
203 WHERE MATCH (a,b,c)
204 AGAINST ('"interesting blob"@9 "very long blob"@0' IN BOOLEAN MODE);
205
206 # should have 3 rows
207 SELECT COUNT(*) FROM t1
208 WHERE MATCH (a,b,c)
209 AGAINST ('"very blob"@4 - "interesting blob"@9' IN BOOLEAN MODE);
210
211 DROP TABLE t1;
212
213 CREATE TABLE t1 (
214 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
215 a VARCHAR(200),
216 b TEXT
217 ) CHARACTER SET = UTF8, ENGINE= InnoDB;
218
219 # Space and special characters are not counted as word
220 INSERT INTO t1 (a,b) VALUES
221 ('MySQL from Tutorial','DBMS stands for + DataBase ...');
222
223 CREATE FULLTEXT INDEX idx on t1 (a,b);
224
225 SELECT * FROM t1
226 WHERE MATCH (a,b)
227 AGAINST ('"stands database"@3' IN BOOLEAN MODE);
228
229 DROP TABLE t1;
230
231 # Test fts with externally stored long column
232 CREATE TABLE t1 (
233 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
234 a TEXT,
235 b TEXT,
236 c TEXT
237 ) CHARACTER SET = UTF8, ROW_FORMAT=DYNAMIC, ENGINE= InnoDB;
238
239 INSERT INTO t1 (a,b,c) VALUES (repeat("b", 19000), 'XYZ, long text', 'very long blob');
240 INSERT INTO t1 (a,b,c) VALUES ('XYZ, 租车 very little long blob very much blob', repeat("b", 19000), 'very long but smaller blob');
241
242 CREATE FULLTEXT INDEX idx on t1 (a,b,c);
243
244 DELETE FROM t1;
245
246 INSERT INTO t1 (a,b,c) VALUES (repeat("b", 19000), 'XYZ, long text', 'very long blob');
247 INSERT INTO t1 (a,b,c) VALUES ('XYZ, 租车 very little long blob is a very much longer blob', repeat("b", 19000), 'this is very long but smaller blob');
248
249 SELECT count(*) FROM t1
250 WHERE MATCH (a,b,c)
251 AGAINST ('"very blob"@4' IN BOOLEAN MODE);
252
253 SELECT count(*) FROM t1
254 WHERE MATCH (a,b,c)
255 AGAINST ('"very blob"@3' IN BOOLEAN MODE);
256
257 DROP TABLE t1;
258
259 eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig;