Debian Codegraph @

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

raw
1 CREATE TABLE t1 (
2 i int(10) unsigned not null auto_increment primary key,
3 a varchar(255) not null,
4 FULLTEXT KEY (a)
5 ) ENGINE = INNODB;
6 check table t1;
7 Table Op Msg_type Msg_text
8 test.t1 check status OK
9 select count(*) from t1 where match a against ('aaaxxx');
10 count(*)
11 260
12 select count(*) from t1 where match a against ('aaayyy');
13 count(*)
14 250
15 select count(*) from t1 where match a against ('aaazzz');
16 count(*)
17 255
18 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
19 count(*)
20 260
21 select count(*) from t1 where match a against ('aaayyy' in boolean mode);
22 count(*)
23 250
24 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
25 count(*)
26 255
27 select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz');
28 count(*)
29 765
30 select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz' in boolean mode);
31 count(*)
32 765
33 select count(*) from t1 where match a against ('aaax*' in boolean mode);
34 count(*)
35 260
36 select count(*) from t1 where match a against ('aaay*' in boolean mode);
37 count(*)
38 250
39 select count(*) from t1 where match a against ('aaa*' in boolean mode);
40 count(*)
41 765
42 insert t1 (a) values ('aaaxxx'),('aaayyy');
43 insert t1 (a) values ('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz');
44 select count(*) from t1 where match a against ('aaaxxx');
45 count(*)
46 261
47 select count(*) from t1 where match a against ('aaayyy');
48 count(*)
49 251
50 select count(*) from t1 where match a against ('aaazzz');
51 count(*)
52 260
53 insert t1 (a) values ('aaaxxx 000000');
54 select count(*) from t1 where match a against ('000000');
55 count(*)
56 1
57 delete from t1 where match a against ('000000');
58 select count(*) from t1 where match a against ('000000');
59 count(*)
60 0
61 select count(*) from t1 where match a against ('aaaxxx');
62 count(*)
63 261
64 delete from t1 where match a against ('aaazzz');
65 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
66 count(*)
67 261
68 select count(*) from t1 where match a against ('aaayyy' in boolean mode);
69 count(*)
70 251
71 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
72 count(*)
73 0
74 select count(*) from t1 where a = 'aaaxxx';
75 count(*)
76 261
77 select count(*) from t1 where a = 'aaayyy';
78 count(*)
79 251
80 select count(*) from t1 where a = 'aaazzz';
81 count(*)
82 0
83 insert t1 (a) values ('aaaxxx 000000');
84 select count(*) from t1 where match a against ('000000');
85 count(*)
86 1
87 update t1 set a='aaazzz' where match a against ('000000');
88 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
89 count(*)
90 261
91 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
92 count(*)
93 1
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 count(*)
98 251
99 select count(*) from t1 where match a against ('aaayyy' in boolean mode);
100 count(*)
101 0
102 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
103 count(*)
104 262
105 drop table t1;
106 CREATE TABLE t1 (
107 i int(10) unsigned not null auto_increment primary key,
108 a varchar(255) not null,
109 FULLTEXT KEY (a)
110 ) ENGINE = INNODB;
111 select count(*) from t1 where match a against ('aaaxxx');
112 count(*)
113 260
114 select count(*) from t1 where match a against ('aaayyy');
115 count(*)
116 250
117 select count(*) from t1 where match a against ('aaazzz');
118 count(*)
119 255
120 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
121 count(*)
122 260
123 select count(*) from t1 where match a against ('aaayyy' in boolean mode);
124 count(*)
125 250
126 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
127 count(*)
128 255
129 select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz');
130 count(*)
131 765
132 select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz' in boolean mode);
133 count(*)
134 765
135 select count(*) from t1 where match a against ('aaax*' in boolean mode);
136 count(*)
137 260
138 select count(*) from t1 where match a against ('aaay*' in boolean mode);
139 count(*)
140 250
141 select count(*) from t1 where match a against ('aaa*' in boolean mode);
142 count(*)
143 765
144 insert t1 (a) values ('aaaxxx'),('aaayyy');
145 insert t1 (a) values ('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz');
146 select count(*) from t1 where match a against ('aaaxxx');
147 count(*)
148 261
149 select count(*) from t1 where match a against ('aaayyy');
150 count(*)
151 251
152 select count(*) from t1 where match a against ('aaazzz');
153 count(*)
154 260
155 insert t1 (a) values ('aaaxxx 000000');
156 select count(*) from t1 where match a against ('000000');
157 count(*)
158 1
159 delete from t1 where match a against ('000000');
160 select count(*) from t1 where match a against ('000000');
161 count(*)
162 0
163 select count(*) from t1 where match a against ('aaaxxx');
164 count(*)
165 261
166 delete from t1 where match a against ('aaazzz');
167 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
168 count(*)
169 261
170 select count(*) from t1 where match a against ('aaayyy' in boolean mode);
171 count(*)
172 251
173 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
174 count(*)
175 0
176 select count(*) from t1 where a = 'aaaxxx';
177 count(*)
178 261
179 select count(*) from t1 where a = 'aaayyy';
180 count(*)
181 251
182 select count(*) from t1 where a = 'aaazzz';
183 count(*)
184 0
185 insert t1 (a) values ('aaaxxx 000000');
186 select count(*) from t1 where match a against ('000000');
187 count(*)
188 1
189 update t1 set a='aaazzz' where match a against ('000000');
190 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
191 count(*)
192 261
193 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
194 count(*)
195 1
196 update t1 set a='aaazzz' where a = 'aaaxxx';
197 update t1 set a='aaaxxx' where a = 'aaayyy';
198 select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
199 count(*)
200 251
201 select count(*) from t1 where match a against ('aaayyy' in boolean mode);
202 count(*)
203 0
204 select count(*) from t1 where match a against ('aaazzz' in boolean mode);
205 count(*)
206 262
207 drop table t1;
208 set names utf8;
209 create table t1(a text,fulltext(a)) ENGINE = INNODB collate=utf8_swedish_ci;
210 insert into t1 values('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 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
215 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
216 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
217 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
218 ('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
219 ('test'),('test'),('test'),('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 delete from t1 limit 1;
227 truncate table t1;
228 insert into t1 values('ab c d');
229 update t1 set a='ab c d';
230 select * from t1 where match a against('ab c' in boolean mode);
231 a
232 select * from t1 where match a against('ab c' in boolean mode);
233 a
234 drop table t1;
235 set names latin1;
236 SET NAMES utf8;
237 CREATE TABLE t1(a VARCHAR(255), FULLTEXT(a)) ENGINE = INNODB DEFAULT CHARSET=utf8;
238 INSERT INTO t1 VALUES('„MySQL“');
239 SELECT a FROM t1 WHERE MATCH a AGAINST('“MySQL„' IN BOOLEAN MODE);
240 a
241 „MySQL“
242 DROP TABLE t1;
243 SET NAMES latin1;
244 CREATE TABLE t1 (
245 FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
246 id int(10) not null ,
247 first_name varchar(50) NOT NULL,
248 last_name varchar(50) NOT NULL,
249 PRIMARY KEY (FTS_DOC_ID),
250 UNIQUE KEY idx_1 (first_name, last_name),
251 FULLTEXT KEY `idx_2` (first_name)
252 ) ENGINE=InnoDB;
253 INSERT INTO t1 (id, first_name, last_name) VALUES
254 (10, 'Bart', 'Simpson'),
255 (11, 'Homer', 'Simpson'),
256 (12, 'Marge', 'Simpson'),
257 (13, 'Lisa', 'Simpson'),
258 (14, 'Maggie', 'Simpson'),
259 (15, 'Ned', 'Flanders'),
260 (16, 'Nelson', 'Muntz');
261 analyze table t1;
262 Table Op Msg_type Msg_text
263 test.t1 analyze status Engine-independent statistics collected
264 test.t1 analyze status OK
265 SELECT fts_doc_id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN BOOLEAN MODE) AS score FROM t1;
266 fts_doc_id first_name last_name score
267 1 Bart Simpson 0
268 2 Homer Simpson 0.7141907215118408
269 4 Lisa Simpson 0
270 5 Maggie Simpson 0
271 3 Marge Simpson 0
272 6 Ned Flanders 0
273 7 Nelson Muntz 0
274 DROP TABLE t1;
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 ALTER TABLE t1 ADD FULLTEXT(b);
280 ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index
281 DROP TABLE t1;
282 #
283 # MDEV-29169 Using MATCH returns NULL for Virtual Column
284 #
285 CREATE TABLE t (a TEXT DEFAULT NULL,
286 b TEXT AS (a),
287 c TEXT AS (concat(a, '1')),
288 d int AS (111) VIRTUAL,
289 FULLTEXT KEY `a` (`a`)
290 ) ENGINE=InnoDB;
291 INSERT INTO t (a) VALUES ('test');
292 SELECT * FROM t;
293 a b c d
294 test test test1 111
295 SELECT * FROM t WHERE MATCH(a) AGAINST('test');
296 a b c d
297 test test test1 111
298 DROP TABLE t;