Debian Codegraph @

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

raw
1 DROP TABLE IF EXISTS t1,t2,t3;
2 CREATE TABLE t1 (
3 a INT AUTO_INCREMENT PRIMARY KEY,
4 message CHAR(20),
5 FULLTEXT(message)
6 ) ENGINE = InnoDB comment = 'original testcase by [email protected]';
7 INSERT INTO t1 (message) VALUES ("Testing"),("table"),("testbug"),
8 ("steve"),("is"),("cool"),("steve is cool");
9 ANALYZE TABLE t1;
10 SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve');
11 a FORMAT(MATCH (message) AGAINST ('steve'),6)
12 4 0.296010
13 7 0.296010
14 SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve');
15 a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
16 4 0.2960100471973419
17 7 0.2960100471973419
18 SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
19 a FORMAT(MATCH (message) AGAINST ('steve'),6)
20 4 0.296010
21 7 0.296010
22 SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
23 a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
24 4 0.2960100471973419
25 7 0.2960100471973419
26 SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY a;
27 a FORMAT(MATCH (message) AGAINST ('steve'),6)
28 4 0.296010
29 7 0.296010
30 SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY a;
31 a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
32 4 0.2960100471973419
33 7 0.2960100471973419
34 SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE a in (2,7,4) and MATCH (message) AGAINST ('steve') ORDER BY a DESC;
35 a FORMAT(MATCH (message) AGAINST ('steve'),6)
36 7 0.296010
37 4 0.296010
38 SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE a in (2,7,4) and MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY a DESC;
39 a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
40 7 0.2960100471973419
41 4 0.2960100471973419
42 SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve') ORDER BY 1;
43 a FORMAT(MATCH (message) AGAINST ('steve'),6)
44 7 0.296010
45 SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY 1;
46 a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
47 7 0.2960100471973419
48 SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) as rel FROM t1 ORDER BY rel;
49 a rel
50 1 0.000000
51 2 0.000000
52 3 0.000000
53 5 0.000000
54 6 0.000000
55 4 0.296010
56 7 0.296010
57 SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel;
58 a rel
59 1 0
60 2 0
61 3 0
62 5 0
63 6 0
64 4 0.2960100471973419
65 7 0.2960100471973419
66 alter table t1 add key m (message);
67 ANALYZE TABLE t1;
68 explain SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message;
69 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
70 1 SIMPLE t1 NULL fulltext message message 0 const 1 100.00 Using where; Ft_hints: no_ranking; Using filesort
71 Warnings:
72 Note 1003 /* select#1 */ select `test`.`t1`.`message` AS `message` from `test`.`t1` where (match `test`.`t1`.`message` against ('steve')) order by `test`.`t1`.`message`
73 SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message desc;
74 message
75 steve is cool
76 steve
77 drop table t1;
78 CREATE TABLE t1 (
79 a INT AUTO_INCREMENT PRIMARY KEY,
80 message CHAR(20),
81 FULLTEXT(message)
82 ) ENGINE = InnoDB;
83 INSERT INTO t1 (message) VALUES ("testbug"),("testbug foobar");
84 ANALYZE TABLE t1;
85 SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1;
86 a rel
87 1 0.000000001885928302414186
88 2 0.0906190574169159
89 SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel,a;
90 a rel
91 1 0.000000001885928302414186
92 2 0.0906190574169159
93 drop table t1;
94 CREATE TABLE t1 (
95 id int(11) NOT NULL auto_increment,
96 thread int(11) NOT NULL default '0',
97 beitrag longtext NOT NULL,
98 PRIMARY KEY (id),
99 KEY thread (thread),
100 FULLTEXT KEY beitrag (beitrag)
101 ) ENGINE =InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7923 ;
102 Warnings:
103 Warning 1681 Integer display width is deprecated and will be removed in a future release.
104 Warning 1681 Integer display width is deprecated and will be removed in a future release.
105 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.
106 CREATE TABLE t2 (
107 id int(11) NOT NULL auto_increment,
108 text varchar(100) NOT NULL default '',
109 PRIMARY KEY (id),
110 KEY text (text)
111 ) ENGINE = InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=63 ;
112 Warnings:
113 Warning 1681 Integer display width is deprecated and will be removed in a future release.
114 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.
115 CREATE TABLE t3 (
116 id int(11) NOT NULL auto_increment,
117 forum int(11) NOT NULL default '0',
118 betreff varchar(70) NOT NULL default '',
119 PRIMARY KEY (id),
120 KEY forum (forum),
121 FULLTEXT KEY betreff (betreff)
122 ) ENGINE = InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=996 ;
123 Warnings:
124 Warning 1681 Integer display width is deprecated and will be removed in a future release.
125 Warning 1681 Integer display width is deprecated and will be removed in a future release.
126 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.
127 select a.text, b.id, b.betreff
128 from
129 t2 a inner join t3 b on a.id = b.forum inner join
130 t1 c on b.id = c.thread
131 where
132 match(b.betreff) against ('+abc' in boolean mode)
133 group by a.text, b.id, b.betreff
134 union
135 select a.text, b.id, b.betreff
136 from
137 t2 a inner join t3 b on a.id = b.forum inner join
138 t1 c on b.id = c.thread
139 where
140 match(c.beitrag) against ('+abc' in boolean mode)
141 group by
142 a.text, b.id, b.betreff
143 order by
144 match(b.betreff) against ('+abc' in boolean mode) desc;
145 ERROR 42000: Table 'b' from one of the SELECTs cannot be used in global ORDER clause
146 select a.text, b.id, b.betreff
147 from
148 t2 a inner join t3 b on a.id = b.forum inner join
149 t1 c on b.id = c.thread
150 where
151 match(b.betreff) against ('+abc' in boolean mode)
152 union
153 select a.text, b.id, b.betreff
154 from
155 t2 a inner join t3 b on a.id = b.forum inner join
156 t1 c on b.id = c.thread
157 where
158 match(c.beitrag) against ('+abc' in boolean mode)
159 order by
160 match(b.betreff) against ('+abc' in boolean mode) desc;
161 ERROR 42000: Table 'b' from one of the SELECTs cannot be used in global ORDER clause
162 select a.text, b.id, b.betreff
163 from
164 t2 a inner join t3 b on a.id = b.forum inner join
165 t1 c on b.id = c.thread
166 where
167 match(b.betreff) against ('+abc' in boolean mode)
168 union
169 select a.text, b.id, b.betreff
170 from
171 t2 a inner join t3 b on a.id = b.forum inner join
172 t1 c on b.id = c.thread
173 where
174 match(c.beitrag) against ('+abc' in boolean mode)
175 order by
176 match(betreff) against ('+abc' in boolean mode) desc;
177 ERROR HY000: The used table type doesn't support FULLTEXT indexes
178 (select b.id, b.betreff from t3 b) union
179 (select b.id, b.betreff from t3 b)
180 order by match(betreff) against ('+abc' in boolean mode) desc;
181 ERROR HY000: The used table type doesn't support FULLTEXT indexes
182 (select b.id, b.betreff from t3 b) union
183 (select b.id, b.betreff from t3 b)
184 order by match(betreff) against ('+abc') desc;
185 ERROR HY000: The used table type doesn't support FULLTEXT indexes
186 select distinct b.id, b.betreff from t3 b
187 order by match(betreff) against ('+abc' in boolean mode) desc;
188 id betreff
189 select b.id, b.betreff from t3 b group by b.id+1
190 order by match(betreff) against ('+abc' in boolean mode) desc;
191 id betreff
192 drop table t1,t2,t3;
193 #
194 # Bug#28940361 QUERY AGAINST FULL TEXT INDEX WITH ORDER BY SILENTLY FAILS
195 #
196 # restart: --sort-buffer-size=32768
197 # Test with explicit FTS index
198 CREATE TABLE t_fts (
199 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
200 c0000 longtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
201 FULLTEXT KEY i0000 (c0000)
202 );
203 INSERT INTO t_fts(c0000) VALUES
204 ('1537001596'), ('1537001596'), ('1537000966'), ('1537000966'), ('1537001075'),
205 ('1537001075'), ('1537000596'), ('1537000596'), ('1537000633'), ('1537000633'),
206 ('1537001651'), ('1537001651'), ('1537000604'), ('1537000604'), ('1537000064'),
207 ('1537000064'), ('1536999311'), ('1536999311'), ('1537001654'), ('1537001654'),
208 ('1537000338'), ('1537000338'), ('1537000463'), ('1537000463'), ('1537001269'),
209 ('1537001269'), ('1537001271'), ('1537001271'), ('1537001129'), ('1537001129'),
210 ('1537001706'), ('1537001706'), ('1537001455'), ('1537001455'), ('1536999158'),
211 ('1536999158'), ('1536999904'), ('1536999904'), ('1537001553'), ('1537001553'),
212 ('1537002066'), ('1537002066'), ('1537001451'), ('1537001451'), ('1537000484'),
213 ('1537000484'), ('1537001365'), ('1537001365'), ('1536999200'), ('1536999200'),
214 ('1537001994'), ('1537001994'), ('1536999319'), ('1536999319'), ('1537001730'),
215 ('1537001730'), ('1537001144'), ('1537001144'), ('1537001152'), ('1537001152'),
216 ('1537001376'), ('1537001376'), ('1537001692'), ('1537001692'), ('1537002082'),
217 ('1537002082'), ('1537000926'), ('1537000926'), ('1537000606'), ('1537000606');
218 SELECT c0000 , c0000 FROM t_fts WHERE
219 MATCH( c0000 ) AGAINST ( '15370*' IN BOOLEAN MODE) ORDER BY c0000;
220 c0000 c0000
221 1537000064 1537000064
222 1537000064 1537000064
223 1537000338 1537000338
224 1537000338 1537000338
225 1537000463 1537000463
226 1537000463 1537000463
227 1537000484 1537000484
228 1537000484 1537000484
229 1537000596 1537000596
230 1537000596 1537000596
231 1537000604 1537000604
232 1537000604 1537000604
233 1537000606 1537000606
234 1537000606 1537000606
235 1537000633 1537000633
236 1537000633 1537000633
237 1537000926 1537000926
238 1537000926 1537000926
239 1537000966 1537000966
240 1537000966 1537000966
241 1537001075 1537001075
242 1537001075 1537001075
243 1537001129 1537001129
244 1537001129 1537001129
245 1537001144 1537001144
246 1537001144 1537001144
247 1537001152 1537001152
248 1537001152 1537001152
249 1537001269 1537001269
250 1537001269 1537001269
251 1537001271 1537001271
252 1537001271 1537001271
253 1537001365 1537001365
254 1537001365 1537001365
255 1537001376 1537001376
256 1537001376 1537001376
257 1537001451 1537001451
258 1537001451 1537001451
259 1537001455 1537001455
260 1537001455 1537001455
261 1537001553 1537001553
262 1537001553 1537001553
263 1537001596 1537001596
264 1537001596 1537001596
265 1537001651 1537001651
266 1537001651 1537001651
267 1537001654 1537001654
268 1537001654 1537001654
269 1537001692 1537001692
270 1537001692 1537001692
271 1537001706 1537001706
272 1537001706 1537001706
273 1537001730 1537001730
274 1537001730 1537001730
275 1537001994 1537001994
276 1537001994 1537001994
277 1537002066 1537002066
278 1537002066 1537002066
279 1537002082 1537002082
280 1537002082 1537002082
281 SELECT c0000 , c0000 FROM t_fts WHERE
282 MATCH( c0000 ) AGAINST ( '1537000*' IN BOOLEAN MODE) ORDER BY c0000;
283 c0000 c0000
284 1537000064 1537000064
285 1537000064 1537000064
286 1537000338 1537000338
287 1537000338 1537000338
288 1537000463 1537000463
289 1537000463 1537000463
290 1537000484 1537000484
291 1537000484 1537000484
292 1537000596 1537000596
293 1537000596 1537000596
294 1537000604 1537000604
295 1537000604 1537000604
296 1537000606 1537000606
297 1537000606 1537000606
298 1537000633 1537000633
299 1537000633 1537000633
300 1537000926 1537000926
301 1537000926 1537000926
302 1537000966 1537000966
303 1537000966 1537000966
304 DROP TABLE t_fts;
305 # Test with implicit FTS index
306 CREATE TABLE t_nofts (
307 c0000 longtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
308 FULLTEXT KEY i0000 (c0000)
309 );
310 INSERT INTO t_nofts(c0000) VALUES
311 ('1537001596'), ('1537001596'), ('1537000966'), ('1537000966'), ('1537001075'),
312 ('1537001075'), ('1537000596'), ('1537000596'), ('1537000633'), ('1537000633'),
313 ('1537001651'), ('1537001651'), ('1537000604'), ('1537000604'), ('1537000064'),
314 ('1537000064'), ('1536999311'), ('1536999311'), ('1537001654'), ('1537001654'),
315 ('1537000338'), ('1537000338'), ('1537000463'), ('1537000463'), ('1537001269'),
316 ('1537001269'), ('1537001271'), ('1537001271'), ('1537001129'), ('1537001129'),
317 ('1537001706'), ('1537001706'), ('1537001455'), ('1537001455'), ('1536999158'),
318 ('1536999158'), ('1536999904'), ('1536999904'), ('1537001553'), ('1537001553'),
319 ('1537002066'), ('1537002066'), ('1537001451'), ('1537001451'), ('1537000484'),
320 ('1537000484'), ('1537001365'), ('1537001365'), ('1536999200'), ('1536999200'),
321 ('1537001994'), ('1537001994'), ('1536999319'), ('1536999319'), ('1537001730'),
322 ('1537001730'), ('1537001144'), ('1537001144'), ('1537001152'), ('1537001152'),
323 ('1537001376'), ('1537001376'), ('1537001692'), ('1537001692'), ('1537002082'),
324 ('1537002082'), ('1537000926'), ('1537000926'), ('1537000606'), ('1537000606');
325 SELECT c0000 , c0000 FROM t_nofts WHERE
326 MATCH( c0000 ) AGAINST ( '15370*' IN BOOLEAN MODE) ORDER BY c0000;
327 c0000 c0000
328 1537000064 1537000064
329 1537000064 1537000064
330 1537000338 1537000338
331 1537000338 1537000338
332 1537000463 1537000463
333 1537000463 1537000463
334 1537000484 1537000484
335 1537000484 1537000484
336 1537000596 1537000596
337 1537000596 1537000596
338 1537000604 1537000604
339 1537000604 1537000604
340 1537000606 1537000606
341 1537000606 1537000606
342 1537000633 1537000633
343 1537000633 1537000633
344 1537000926 1537000926
345 1537000926 1537000926
346 1537000966 1537000966
347 1537000966 1537000966
348 1537001075 1537001075
349 1537001075 1537001075
350 1537001129 1537001129
351 1537001129 1537001129
352 1537001144 1537001144
353 1537001144 1537001144
354 1537001152 1537001152
355 1537001152 1537001152
356 1537001269 1537001269
357 1537001269 1537001269
358 1537001271 1537001271
359 1537001271 1537001271
360 1537001365 1537001365
361 1537001365 1537001365
362 1537001376 1537001376
363 1537001376 1537001376
364 1537001451 1537001451
365 1537001451 1537001451
366 1537001455 1537001455
367 1537001455 1537001455
368 1537001553 1537001553
369 1537001553 1537001553
370 1537001596 1537001596
371 1537001596 1537001596
372 1537001651 1537001651
373 1537001651 1537001651
374 1537001654 1537001654
375 1537001654 1537001654
376 1537001692 1537001692
377 1537001692 1537001692
378 1537001706 1537001706
379 1537001706 1537001706
380 1537001730 1537001730
381 1537001730 1537001730
382 1537001994 1537001994
383 1537001994 1537001994
384 1537002066 1537002066
385 1537002066 1537002066
386 1537002082 1537002082
387 1537002082 1537002082
388 SELECT c0000 , c0000 FROM t_nofts WHERE
389 MATCH( c0000 ) AGAINST ( '1537000*' IN BOOLEAN MODE) ORDER BY c0000;
390 c0000 c0000
391 1537000064 1537000064
392 1537000064 1537000064
393 1537000338 1537000338
394 1537000338 1537000338
395 1537000463 1537000463
396 1537000463 1537000463
397 1537000484 1537000484
398 1537000484 1537000484
399 1537000596 1537000596
400 1537000596 1537000596
401 1537000604 1537000604
402 1537000604 1537000604
403 1537000606 1537000606
404 1537000606 1537000606
405 1537000633 1537000633
406 1537000633 1537000633
407 1537000926 1537000926
408 1537000926 1537000926
409 1537000966 1537000966
410 1537000966 1537000966
411 DROP TABLE t_nofts;
412 # restart: