Debian Codegraph @

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

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