Debian Codegraph @

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

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