Debian Codegraph @

mysql-test/suite/innodb_fts/r/fulltext2.result

raw
1 call mtr.add_suppression("Trying to free a missing index of table 'test/t1'");
2 call mtr.add_suppression("Failed to create index with index id .* of table 'test/t1'");
3 DROP TABLE IF EXISTS t1;
4 CREATE TABLE t1 (
5 i int(10) unsigned not null auto_increment primary key,
6 a varchar(255) not null,
7 FULLTEXT KEY (a)
8 ) ENGINE = INNODB;
9 Warnings:
10 Warning 1681 Integer display width is deprecated and will be removed in a future release.
11 check table t1;
12 Table Op Msg_type Msg_text
13 test.t1 check status OK
14 select count(*) from t1 where match a against ('aaaxxx');
15 count(*)
16 260
17 select count(*) from t1 where match a against ('aaayyy');
18 count(*)
19 250
20 select count(*) from t1 where match a against ('aaazzz');
21 count(*)
22 255
23 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
24 count(*)
25 260
26 select count(*) from t1 where match a against ('aaayyy' in boolean mode);
27 count(*)
28 250
29 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
30 count(*)
31 255
32 select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz');
33 count(*)
34 765
35 select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz' in boolean mode);
36 count(*)
37 765
38 select count(*) from t1 where match a against ('aaax*' in boolean mode);
39 count(*)
40 260
41 select count(*) from t1 where match a against ('aaay*' in boolean mode);
42 count(*)
43 250
44 select count(*) from t1 where match a against ('aaa*' in boolean mode);
45 count(*)
46 765
47 insert t1 (a) values ('aaaxxx'),('aaayyy');
48 insert t1 (a) values ('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz');
49 select count(*) from t1 where match a against ('aaaxxx');
50 count(*)
51 261
52 select count(*) from t1 where match a against ('aaayyy');
53 count(*)
54 251
55 select count(*) from t1 where match a against ('aaazzz');
56 count(*)
57 260
58 insert t1 (a) values ('aaaxxx 000000');
59 select count(*) from t1 where match a against ('000000');
60 count(*)
61 1
62 delete from t1 where match a against ('000000');
63 select count(*) from t1 where match a against ('000000');
64 count(*)
65 0
66 select count(*) from t1 where match a against ('aaaxxx');
67 count(*)
68 261
69 delete from t1 where match a against ('aaazzz');
70 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
71 count(*)
72 261
73 select count(*) from t1 where match a against ('aaayyy' in boolean mode);
74 count(*)
75 251
76 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
77 count(*)
78 0
79 select count(*) from t1 where a = 'aaaxxx';
80 count(*)
81 261
82 select count(*) from t1 where a = 'aaayyy';
83 count(*)
84 251
85 select count(*) from t1 where a = 'aaazzz';
86 count(*)
87 0
88 insert t1 (a) values ('aaaxxx 000000');
89 select count(*) from t1 where match a against ('000000');
90 count(*)
91 1
92 update t1 set a='aaazzz' where match a against ('000000');
93 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
94 count(*)
95 261
96 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
97 count(*)
98 1
99 update t1 set a='aaazzz' where a = 'aaaxxx';
100 update t1 set a='aaaxxx' where a = 'aaayyy';
101 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
102 count(*)
103 251
104 select count(*) from t1 where match a against ('aaayyy' in boolean mode);
105 count(*)
106 0
107 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
108 count(*)
109 262
110 drop table t1;
111 CREATE TABLE t1 (
112 i int(10) unsigned not null auto_increment primary key,
113 a varchar(255) not null,
114 FULLTEXT KEY (a)
115 ) ENGINE = INNODB;
116 Warnings:
117 Warning 1681 Integer display width is deprecated and will be removed in a future release.
118 select count(*) from t1 where match a against ('aaaxxx');
119 count(*)
120 260
121 select count(*) from t1 where match a against ('aaayyy');
122 count(*)
123 250
124 select count(*) from t1 where match a against ('aaazzz');
125 count(*)
126 255
127 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
128 count(*)
129 260
130 select count(*) from t1 where match a against ('aaayyy' in boolean mode);
131 count(*)
132 250
133 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
134 count(*)
135 255
136 select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz');
137 count(*)
138 765
139 select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz' in boolean mode);
140 count(*)
141 765
142 select count(*) from t1 where match a against ('aaax*' in boolean mode);
143 count(*)
144 260
145 select count(*) from t1 where match a against ('aaay*' in boolean mode);
146 count(*)
147 250
148 select count(*) from t1 where match a against ('aaa*' in boolean mode);
149 count(*)
150 765
151 insert t1 (a) values ('aaaxxx'),('aaayyy');
152 insert t1 (a) values ('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz');
153 select count(*) from t1 where match a against ('aaaxxx');
154 count(*)
155 261
156 select count(*) from t1 where match a against ('aaayyy');
157 count(*)
158 251
159 select count(*) from t1 where match a against ('aaazzz');
160 count(*)
161 260
162 insert t1 (a) values ('aaaxxx 000000');
163 select count(*) from t1 where match a against ('000000');
164 count(*)
165 1
166 delete from t1 where match a against ('000000');
167 select count(*) from t1 where match a against ('000000');
168 count(*)
169 0
170 select count(*) from t1 where match a against ('aaaxxx');
171 count(*)
172 261
173 delete from t1 where match a against ('aaazzz');
174 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
175 count(*)
176 261
177 select count(*) from t1 where match a against ('aaayyy' in boolean mode);
178 count(*)
179 251
180 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
181 count(*)
182 0
183 select count(*) from t1 where a = 'aaaxxx';
184 count(*)
185 261
186 select count(*) from t1 where a = 'aaayyy';
187 count(*)
188 251
189 select count(*) from t1 where a = 'aaazzz';
190 count(*)
191 0
192 insert t1 (a) values ('aaaxxx 000000');
193 select count(*) from t1 where match a against ('000000');
194 count(*)
195 1
196 update t1 set a='aaazzz' where match a against ('000000');
197 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
198 count(*)
199 261
200 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
201 count(*)
202 1
203 update t1 set a='aaazzz' where a = 'aaaxxx';
204 update t1 set a='aaaxxx' where a = 'aaayyy';
205 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
206 count(*)
207 251
208 select count(*) from t1 where match a against ('aaayyy' in boolean mode);
209 count(*)
210 0
211 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
212 count(*)
213 262
214 drop table t1;
215 SET NAMES utf8mb4;
216 create table t1(a text,fulltext(a)) ENGINE = INNODB collate=utf8_swedish_ci;
217 Warnings:
218 Warning 3778 'utf8mb3_swedish_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
219 insert into t1 values('test test '),('test'),('test'),('test'),
220 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
221 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
222 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
223 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
224 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
225 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
226 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
227 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
228 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
229 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
230 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
231 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
232 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
233 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
234 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test');
235 delete from t1 limit 1;
236 truncate table t1;
237 insert into t1 values('ab c d');
238 update t1 set a='ab c d';
239 select * from t1 where match a against('ab c' in boolean mode);
240 a
241 select * from t1 where match a against('ab c' in boolean mode);
242 a
243 drop table t1;
244 set names latin1;
245 SET NAMES utf8mb4;
246 CREATE TABLE t1(a VARCHAR(255), FULLTEXT(a)) ENGINE = INNODB DEFAULT CHARSET=utf8;
247 Warnings:
248 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
249 INSERT INTO t1 VALUES('„MySQL“');
250 SELECT a FROM t1 WHERE MATCH a AGAINST('“MySQL„' IN BOOLEAN MODE);
251 a
252 „MySQL“
253 DROP TABLE t1;
254 SET NAMES latin1;
255 CREATE TABLE t1 (
256 FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
257 id int(10) not null ,
258 first_name varchar(50) NOT NULL,
259 last_name varchar(50) NOT NULL,
260 PRIMARY KEY (FTS_DOC_ID),
261 UNIQUE KEY idx_1 (first_name, last_name),
262 FULLTEXT KEY `idx_2` (first_name)
263 ) ENGINE=InnoDB;
264 Warnings:
265 Warning 1681 Integer display width is deprecated and will be removed in a future release.
266 Warning 1681 Integer display width is deprecated and will be removed in a future release.
267 INSERT INTO t1 (id, first_name, last_name) VALUES
268 (10, 'Bart', 'Simpson'),
269 (11, 'Homer', 'Simpson'),
270 (12, 'Marge', 'Simpson'),
271 (13, 'Lisa', 'Simpson'),
272 (14, 'Maggie', 'Simpson'),
273 (15, 'Ned', 'Flanders'),
274 (16, 'Nelson', 'Muntz');
275 analyze table t1;
276 Table Op Msg_type Msg_text
277 test.t1 analyze status OK
278 SELECT fts_doc_id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN BOOLEAN MODE) AS score FROM t1;
279 fts_doc_id first_name last_name score
280 1 Bart Simpson 0
281 2 Homer Simpson 0.7141907215118408
282 3 Marge Simpson 0
283 4 Lisa Simpson 0
284 5 Maggie Simpson 0
285 6 Ned Flanders 0
286 7 Nelson Muntz 0
287 DROP TABLE t1;