Debian Codegraph @

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

raw
1 #
2 # test of new fulltext search features
3 #
4
5 #
6 # two-level tree
7 #
8
9 --source include/have_innodb.inc
10 --source include/maybe_versioning.inc
11
12 CREATE TABLE t1 (
13 i int(10) unsigned not null auto_increment primary key,
14 a varchar(255) not null,
15 FULLTEXT KEY (a)
16 ) ENGINE = INNODB;
17
18 # two-level entry, second-level tree with depth 2
19 --disable_query_log
20 let $1=260;
21 while ($1)
22 {
23 eval insert t1 (a) values ('aaaxxx');
24 dec $1;
25 }
26
27 # two-level entry, second-level tree has only one page
28 let $1=255;
29 while ($1)
30 {
31 eval insert t1 (a) values ('aaazzz');
32 dec $1;
33 }
34
35 # one-level entry (entries)
36 let $1=250;
37 while ($1)
38 {
39 eval insert t1 (a) values ('aaayyy');
40 dec $1;
41 }
42 --enable_query_log
43
44 # converting to two-level
45 # INNODB_FTS: Do not support repair
46 #repair table t1 quick;
47 check table t1;
48 #optimize table t1; # BUG#5327 - mi_sort_index() of 2-level tree
49 #check table t1;
50
51 select count(*) from t1 where match a against ('aaaxxx');
52 select count(*) from t1 where match a against ('aaayyy');
53 select count(*) from t1 where match a against ('aaazzz');
54 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
55 select count(*) from t1 where match a against ('aaayyy' in boolean mode);
56 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
57 select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz');
58 select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz' in boolean mode);
59
60 select count(*) from t1 where match a against ('aaax*' in boolean mode);
61 select count(*) from t1 where match a against ('aaay*' in boolean mode);
62 select count(*) from t1 where match a against ('aaa*' in boolean mode);
63
64 # mi_write:
65
66 insert t1 (a) values ('aaaxxx'),('aaayyy');
67 # call to enlarge_root() below
68 insert t1 (a) values ('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz');
69 select count(*) from t1 where match a against ('aaaxxx');
70 select count(*) from t1 where match a against ('aaayyy');
71 select count(*) from t1 where match a against ('aaazzz');
72
73 # mi_delete
74 insert t1 (a) values ('aaaxxx 000000');
75 select count(*) from t1 where match a against ('000000');
76 delete from t1 where match a against ('000000');
77 select count(*) from t1 where match a against ('000000');
78 select count(*) from t1 where match a against ('aaaxxx');
79 delete from t1 where match a against ('aaazzz');
80 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
81 select count(*) from t1 where match a against ('aaayyy' in boolean mode);
82 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
83 # double-check without index
84 select count(*) from t1 where a = 'aaaxxx';
85 select count(*) from t1 where a = 'aaayyy';
86 select count(*) from t1 where a = 'aaazzz';
87
88 # update
89 insert t1 (a) values ('aaaxxx 000000');
90 select count(*) from t1 where match a against ('000000');
91 update t1 set a='aaazzz' where match a against ('000000');
92 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
93 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
94 update t1 set a='aaazzz' where a = 'aaaxxx';
95 update t1 set a='aaaxxx' where a = 'aaayyy';
96 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
97 select count(*) from t1 where match a against ('aaayyy' in boolean mode);
98 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
99
100 drop table t1;
101
102 CREATE TABLE t1 (
103 i int(10) unsigned not null auto_increment primary key,
104 a varchar(255) not null,
105 FULLTEXT KEY (a)
106 ) ENGINE = INNODB;
107
108 #
109 # now same as about but w/o repair table
110 # 2-level tree created by mi_write
111 #
112
113 # two-level entry, second-level tree with depth 2
114 --disable_query_log
115 let $1=260;
116 while ($1)
117 {
118 eval insert t1 (a) values ('aaaxxx');
119 dec $1;
120 }
121 let $1=255;
122 while ($1)
123 {
124 eval insert t1 (a) values ('aaazzz');
125 dec $1;
126 }
127 let $1=250;
128 while ($1)
129 {
130 eval insert t1 (a) values ('aaayyy');
131 dec $1;
132 }
133 --enable_query_log
134
135 select count(*) from t1 where match a against ('aaaxxx');
136 select count(*) from t1 where match a against ('aaayyy');
137 select count(*) from t1 where match a against ('aaazzz');
138 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
139 select count(*) from t1 where match a against ('aaayyy' in boolean mode);
140 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
141 select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz');
142 select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz' in boolean mode);
143
144 select count(*) from t1 where match a against ('aaax*' in boolean mode);
145 select count(*) from t1 where match a against ('aaay*' in boolean mode);
146 select count(*) from t1 where match a against ('aaa*' in boolean mode);
147
148 # mi_write:
149
150 insert t1 (a) values ('aaaxxx'),('aaayyy');
151 insert t1 (a) values ('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz');
152 select count(*) from t1 where match a against ('aaaxxx');
153 select count(*) from t1 where match a against ('aaayyy');
154 select count(*) from t1 where match a against ('aaazzz');
155
156 # mi_delete
157 insert t1 (a) values ('aaaxxx 000000');
158 select count(*) from t1 where match a against ('000000');
159 delete from t1 where match a against ('000000');
160 select count(*) from t1 where match a against ('000000');
161 select count(*) from t1 where match a against ('aaaxxx');
162 delete from t1 where match a against ('aaazzz');
163 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
164 select count(*) from t1 where match a against ('aaayyy' in boolean mode);
165 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
166 # double-check without index
167 select count(*) from t1 where a = 'aaaxxx';
168 select count(*) from t1 where a = 'aaayyy';
169 select count(*) from t1 where a = 'aaazzz';
170
171 # update
172 insert t1 (a) values ('aaaxxx 000000');
173 select count(*) from t1 where match a against ('000000');
174 update t1 set a='aaazzz' where match a against ('000000');
175 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
176 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
177 update t1 set a='aaazzz' where a = 'aaaxxx';
178 update t1 set a='aaaxxx' where a = 'aaayyy';
179 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
180 select count(*) from t1 where match a against ('aaayyy' in boolean mode);
181 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
182 drop table t1;
183
184 #
185 # BUG#11336
186 #
187 # for uca collation isalnum and strnncollsp don't agree on whether
188 # 0xC2A0 is a space (strnncollsp is right, isalnum is wrong).
189 #
190 # they still don't, the bug was fixed by avoiding strnncollsp
191 #
192
193 set names utf8;
194 eval create table t1(a text,fulltext(a)) ENGINE = INNODB collate=utf8_swedish_ci;
195 insert into t1 values('test test '),('test'),('test'),('test'),
196 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
197 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
198 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
199 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
200 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
201 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
202 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
203 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
204 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
205 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
206 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
207 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
208 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
209 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
210 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test');
211 delete from t1 limit 1;
212
213 #
214 # BUG#16489: utf8 + fulltext leads to corrupt index file.
215 #
216 if ($MTR_COMBINATION_ORIG)
217 {
218 truncate table t1;
219 }
220 if (!$MTR_COMBINATION_ORIG)
221 {
222 --disable_query_log
223 delete from t1;
224 --enable_query_log
225 --echo truncate table t1;
226 }
227 insert into t1 values('ab c d');
228 update t1 set a='ab c d';
229 select * from t1 where match a against('ab c' in boolean mode);
230 select * from t1 where match a against('ab c' in boolean mode);
231 drop table t1;
232 set names latin1;
233
234 # End of 4.1 tests
235
236 #
237 # BUG#19580 - FULLTEXT search produces wrong results on UTF-8 columns
238 # INNODB_FTS: Investigate
239 SET NAMES utf8;
240 CREATE TABLE t1(a VARCHAR(255), FULLTEXT(a)) ENGINE = INNODB DEFAULT CHARSET=utf8;
241 INSERT INTO t1 VALUES('„MySQL“');
242 SELECT a FROM t1 WHERE MATCH a AGAINST('“MySQL„' IN BOOLEAN MODE);
243 DROP TABLE t1;
244 SET NAMES latin1;
245
246 #
247 # Bug #20597981 - WRONG RELEVANCE RANKING FOR FULL TEXT SEARCHES
248 # WHEN FTS_DOC_ID IS PRIMARY KEY
249 CREATE TABLE t1 (
250 FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
251 id int(10) not null ,
252 first_name varchar(50) NOT NULL,
253 last_name varchar(50) NOT NULL,
254 PRIMARY KEY (FTS_DOC_ID),
255 UNIQUE KEY idx_1 (first_name, last_name),
256 FULLTEXT KEY `idx_2` (first_name)
257 ) ENGINE=InnoDB;
258
259 INSERT INTO t1 (id, first_name, last_name) VALUES
260 (10, 'Bart', 'Simpson'),
261 (11, 'Homer', 'Simpson'),
262 (12, 'Marge', 'Simpson'),
263 (13, 'Lisa', 'Simpson'),
264 (14, 'Maggie', 'Simpson'),
265 (15, 'Ned', 'Flanders'),
266 (16, 'Nelson', 'Muntz');
267
268 analyze table t1;
269 SELECT fts_doc_id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN BOOLEAN MODE) AS score FROM t1;
270 DROP TABLE t1;
271
272 #
273 # MDEV-28912 NON-UNIQUE FTS_DOC_ID mistaken as FTS_DOC_ID_INDEX
274 #
275 CREATE TABLE t1(a INT, b TEXT, FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
276 KEY FTS_DOC_ID_INDEX(FTS_DOC_ID))ENGINE=InnoDB;
277 ALTER TABLE t1 ADD COLUMN c INT as (a) VIRTUAL;
278 ALTER TABLE t1 ADD d INT NULL;
279 --error ER_INNODB_FT_WRONG_DOCID_INDEX
280 ALTER TABLE t1 ADD FULLTEXT(b);
281 DROP TABLE t1;
282
283 --echo #
284 --echo # MDEV-29169 Using MATCH returns NULL for Virtual Column
285 --echo #
286 CREATE TABLE t (a TEXT DEFAULT NULL,
287 b TEXT AS (a),
288 c TEXT AS (concat(a, '1')),
289 d int AS (111) VIRTUAL,
290 FULLTEXT KEY `a` (`a`)
291 ) ENGINE=InnoDB;
292
293 INSERT INTO t (a) VALUES ('test');
294 SELECT * FROM t;
295 SELECT * FROM t WHERE MATCH(a) AGAINST('test');
296
297 DROP TABLE t;