Debian Codegraph @

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

raw
1
2 --disable_warnings
3 DROP TABLE IF EXISTS t1,t2,t3;
4 --enable_warnings
5
6 CREATE TABLE t1 (
7 a INT AUTO_INCREMENT PRIMARY KEY,
8 message CHAR(20),
9 FULLTEXT(message)
10 ) ENGINE = InnoDB comment = 'original testcase by [email protected]';
11 INSERT INTO t1 (message) VALUES ("Testing"),("table"),("testbug"),
12 ("steve"),("is"),("cool"),("steve is cool");
13 -- disable_result_log
14 ANALYZE TABLE t1;
15 -- enable_result_log
16 # basic MATCH
17 SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve');
18 SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve');
19 SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
20 SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
21
22 # MATCH + ORDER BY (with ft-ranges)
23 SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY a;
24 SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY a;
25
26 # MATCH + ORDER BY (with normal ranges) + UNIQUE
27 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;
28 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;
29
30 # MATCH + ORDER BY + UNIQUE (const_table)
31 SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve') ORDER BY 1;
32 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;
33
34 # ORDER BY MATCH
35 # INNODB_FTS: INVESITGATE
36 SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) as rel FROM t1 ORDER BY rel;
37 SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel;
38
39 #
40 # BUG#6635 - test_if_skip_sort_order() thought it can skip filesort
41 # for fulltext searches too
42 #
43 alter table t1 add key m (message);
44 -- disable_result_log
45 ANALYZE TABLE t1;
46 -- enable_result_log
47 explain SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message;
48 SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message desc;
49
50 drop table t1;
51
52 #
53 # reused boolean scan bug
54 #
55 CREATE TABLE t1 (
56 a INT AUTO_INCREMENT PRIMARY KEY,
57 message CHAR(20),
58 FULLTEXT(message)
59 ) ENGINE = InnoDB;
60 INSERT INTO t1 (message) VALUES ("testbug"),("testbug foobar");
61 -- disable_result_log
62 ANALYZE TABLE t1;
63 -- enable_result_log
64 SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1;
65 SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel,a;
66 drop table t1;
67
68 # BUG#11869
69 CREATE TABLE t1 (
70 id int(11) NOT NULL auto_increment,
71 thread int(11) NOT NULL default '0',
72 beitrag longtext NOT NULL,
73 PRIMARY KEY (id),
74 KEY thread (thread),
75 FULLTEXT KEY beitrag (beitrag)
76 ) ENGINE =InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7923 ;
77
78 CREATE TABLE t2 (
79 id int(11) NOT NULL auto_increment,
80 text varchar(100) NOT NULL default '',
81 PRIMARY KEY (id),
82 KEY text (text)
83 ) ENGINE = InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=63 ;
84
85 CREATE TABLE t3 (
86 id int(11) NOT NULL auto_increment,
87 forum int(11) NOT NULL default '0',
88 betreff varchar(70) NOT NULL default '',
89 PRIMARY KEY (id),
90 KEY forum (forum),
91 FULLTEXT KEY betreff (betreff)
92 ) ENGINE = InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=996 ;
93
94 --error ER_TABLENAME_NOT_ALLOWED_HERE
95 select a.text, b.id, b.betreff
96 from
97 t2 a inner join t3 b on a.id = b.forum inner join
98 t1 c on b.id = c.thread
99 where
100 match(b.betreff) against ('+abc' in boolean mode)
101 group by a.text, b.id, b.betreff
102 union
103 select a.text, b.id, b.betreff
104 from
105 t2 a inner join t3 b on a.id = b.forum inner join
106 t1 c on b.id = c.thread
107 where
108 match(c.beitrag) against ('+abc' in boolean mode)
109 group by
110 a.text, b.id, b.betreff
111 order by
112 match(b.betreff) against ('+abc' in boolean mode) desc;
113
114 --error ER_TABLENAME_NOT_ALLOWED_HERE
115 select a.text, b.id, b.betreff
116 from
117 t2 a inner join t3 b on a.id = b.forum inner join
118 t1 c on b.id = c.thread
119 where
120 match(b.betreff) against ('+abc' in boolean mode)
121 union
122 select a.text, b.id, b.betreff
123 from
124 t2 a inner join t3 b on a.id = b.forum inner join
125 t1 c on b.id = c.thread
126 where
127 match(c.beitrag) against ('+abc' in boolean mode)
128 order by
129 match(b.betreff) against ('+abc' in boolean mode) desc;
130
131 --error ER_TABLE_CANT_HANDLE_FT
132 select a.text, b.id, b.betreff
133 from
134 t2 a inner join t3 b on a.id = b.forum inner join
135 t1 c on b.id = c.thread
136 where
137 match(b.betreff) against ('+abc' in boolean mode)
138 union
139 select a.text, b.id, b.betreff
140 from
141 t2 a inner join t3 b on a.id = b.forum inner join
142 t1 c on b.id = c.thread
143 where
144 match(c.beitrag) against ('+abc' in boolean mode)
145 order by
146 match(betreff) against ('+abc' in boolean mode) desc;
147
148 # BUG#11869 part2: used table type doesn't support FULLTEXT indexes error
149 --error ER_TABLE_CANT_HANDLE_FT
150 (select b.id, b.betreff from t3 b) union
151 (select b.id, b.betreff from t3 b)
152 order by match(betreff) against ('+abc' in boolean mode) desc;
153
154 --error ER_TABLE_CANT_HANDLE_FT
155 (select b.id, b.betreff from t3 b) union
156 (select b.id, b.betreff from t3 b)
157 order by match(betreff) against ('+abc') desc;
158
159 select distinct b.id, b.betreff from t3 b
160 order by match(betreff) against ('+abc' in boolean mode) desc;
161
162 --source include/turn_off_only_full_group_by.inc
163 select b.id, b.betreff from t3 b group by b.id+1
164 order by match(betreff) against ('+abc' in boolean mode) desc;
165 --source include/restore_sql_mode_after_turn_off_only_full_group_by.inc
166
167 drop table t1,t2,t3;
168
169 # End of 4.1 tests
170
171 --echo #
172 --echo # Bug#28940361 QUERY AGAINST FULL TEXT INDEX WITH ORDER BY SILENTLY FAILS
173 --echo #
174
175 # Restart the server with a small sort buffer
176 --let $restart_parameters = restart: --sort-buffer-size=32768
177 --source include/restart_mysqld.inc
178
179 --echo # Test with explicit FTS index
180 --disable_warnings
181 CREATE TABLE t_fts (
182 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
183 c0000 longtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
184 FULLTEXT KEY i0000 (c0000)
185 );
186 --enable_warnings
187
188 INSERT INTO t_fts(c0000) VALUES
189 ('1537001596'), ('1537001596'), ('1537000966'), ('1537000966'), ('1537001075'),
190 ('1537001075'), ('1537000596'), ('1537000596'), ('1537000633'), ('1537000633'),
191 ('1537001651'), ('1537001651'), ('1537000604'), ('1537000604'), ('1537000064'),
192 ('1537000064'), ('1536999311'), ('1536999311'), ('1537001654'), ('1537001654'),
193 ('1537000338'), ('1537000338'), ('1537000463'), ('1537000463'), ('1537001269'),
194 ('1537001269'), ('1537001271'), ('1537001271'), ('1537001129'), ('1537001129'),
195 ('1537001706'), ('1537001706'), ('1537001455'), ('1537001455'), ('1536999158'),
196 ('1536999158'), ('1536999904'), ('1536999904'), ('1537001553'), ('1537001553'),
197 ('1537002066'), ('1537002066'), ('1537001451'), ('1537001451'), ('1537000484'),
198 ('1537000484'), ('1537001365'), ('1537001365'), ('1536999200'), ('1536999200'),
199 ('1537001994'), ('1537001994'), ('1536999319'), ('1536999319'), ('1537001730'),
200 ('1537001730'), ('1537001144'), ('1537001144'), ('1537001152'), ('1537001152'),
201 ('1537001376'), ('1537001376'), ('1537001692'), ('1537001692'), ('1537002082'),
202 ('1537002082'), ('1537000926'), ('1537000926'), ('1537000606'), ('1537000606');
203
204 SELECT c0000 , c0000 FROM t_fts WHERE
205 MATCH( c0000 ) AGAINST ( '15370*' IN BOOLEAN MODE) ORDER BY c0000;
206 SELECT c0000 , c0000 FROM t_fts WHERE
207 MATCH( c0000 ) AGAINST ( '1537000*' IN BOOLEAN MODE) ORDER BY c0000;
208
209 DROP TABLE t_fts;
210
211 --echo # Test with implicit FTS index
212 --disable_warnings
213 CREATE TABLE t_nofts (
214 c0000 longtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
215 FULLTEXT KEY i0000 (c0000)
216 );
217 --enable_warnings
218
219 INSERT INTO t_nofts(c0000) VALUES
220 ('1537001596'), ('1537001596'), ('1537000966'), ('1537000966'), ('1537001075'),
221 ('1537001075'), ('1537000596'), ('1537000596'), ('1537000633'), ('1537000633'),
222 ('1537001651'), ('1537001651'), ('1537000604'), ('1537000604'), ('1537000064'),
223 ('1537000064'), ('1536999311'), ('1536999311'), ('1537001654'), ('1537001654'),
224 ('1537000338'), ('1537000338'), ('1537000463'), ('1537000463'), ('1537001269'),
225 ('1537001269'), ('1537001271'), ('1537001271'), ('1537001129'), ('1537001129'),
226 ('1537001706'), ('1537001706'), ('1537001455'), ('1537001455'), ('1536999158'),
227 ('1536999158'), ('1536999904'), ('1536999904'), ('1537001553'), ('1537001553'),
228 ('1537002066'), ('1537002066'), ('1537001451'), ('1537001451'), ('1537000484'),
229 ('1537000484'), ('1537001365'), ('1537001365'), ('1536999200'), ('1536999200'),
230 ('1537001994'), ('1537001994'), ('1536999319'), ('1536999319'), ('1537001730'),
231 ('1537001730'), ('1537001144'), ('1537001144'), ('1537001152'), ('1537001152'),
232 ('1537001376'), ('1537001376'), ('1537001692'), ('1537001692'), ('1537002082'),
233 ('1537002082'), ('1537000926'), ('1537000926'), ('1537000606'), ('1537000606');
234
235 SELECT c0000 , c0000 FROM t_nofts WHERE
236 MATCH( c0000 ) AGAINST ( '15370*' IN BOOLEAN MODE) ORDER BY c0000;
237 SELECT c0000 , c0000 FROM t_nofts WHERE
238 MATCH( c0000 ) AGAINST ( '1537000*' IN BOOLEAN MODE) ORDER BY c0000;
239
240 DROP TABLE t_nofts;
241
242 # Restart server with default settings
243 --let $restart_parameters = restart:
244 --source include/restart_mysqld.inc