Debian Codegraph @

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

raw
1 #
2 # Fulltext configurable parameters
3 #
4 --disable_warnings
5 drop table if exists t1;
6 --enable_warnings
7
8 --echo #
9 --echo # Bug#56814 Explain + subselect + fulltext crashes server
10 --echo #
11
12 CREATE TABLE t1(f1 VARCHAR(6) NOT NULL,FULLTEXT KEY(f1),UNIQUE(f1)) ENGINE = InnoDB;
13 INSERT INTO t1 VALUES ('test');
14 EXPLAIN SELECT 1 FROM t1
15 WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST (""))
16 WHERE t1.f1 GROUP BY t1.f1));
17
18 PREPARE stmt FROM
19 'EXPLAIN SELECT 1 FROM t1
20 WHERE 1 > ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a
21 ON (MATCH(t1.f1) AGAINST (""))
22 WHERE t1.f1 GROUP BY t1.f1))';
23
24 EXECUTE stmt;
25 EXECUTE stmt;
26
27 DEALLOCATE PREPARE stmt;
28
29 PREPARE stmt FROM
30 'EXPLAIN SELECT 1 FROM t1
31 WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a
32 ON (MATCH(t1.f1) AGAINST (""))
33 WHERE t1.f1 GROUP BY t1.f1))';
34
35 EXECUTE stmt;
36 EXECUTE stmt;
37
38 DEALLOCATE PREPARE stmt;
39
40 DROP TABLE t1;
41
42 #try to crash gcc 2.96
43 --disable_warnings
44 drop table if exists t1;
45 --enable_warnings
46
47 CREATE TABLE t1 (
48 kodoboru varchar(10) default NULL,
49 obor tinytext,
50 aobor tinytext,
51 UNIQUE INDEX kodoboru (kodoboru),
52 FULLTEXT KEY obor (obor),
53 FULLTEXT KEY aobor (aobor)
54 ) ENGINE = InnoDB;
55
56 drop table t1;
57
58 CREATE TABLE t1 (
59 kodoboru varchar(10) default NULL,
60 obor tinytext,
61 aobor tinytext,
62 UNIQUE INDEX kodoboru (kodoboru),
63 FULLTEXT KEY obor (obor)
64 ) ENGINE = InnoDB;
65 INSERT INTO t1 VALUES ('0101000000','aaa','AAA');
66 INSERT INTO t1 VALUES ('0102000000','bbb','BBB');
67 INSERT INTO t1 VALUES ('0103000000','ccc','CCC');
68 INSERT INTO t1 VALUES ('0104000000','xxx','XXX');
69
70 select * from t1;
71 drop table t1;
72
73 # End of 4.1 tests
74
75
76 #
77 # Bug#20503: Server crash due to the ORDER clause isn't taken into account
78 # while space allocation
79 #
80 create table t1 (c1 varchar(1), c2 int, c3 int, c4 int, c5 int, c6 int,
81 c7 int, c8 int, c9 int, fulltext key (`c1`)) ENGINE = InnoDB;
82 select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8
83 from t1 where c9=1 order by c2, c2;
84 drop table t1;
85
86
87 #
88 # VIEW with full text
89 #
90 CREATE TABLE t1 (c1 int not null auto_increment primary key, c2 varchar(20), fulltext(c2)) ENGINE = InnoDB;
91 insert into t1 (c2) VALUES ('real Beer'),('Water'),('Kossu'),('Coca-Cola'),('Vodka'),('Wine'),('almost real Beer');
92 select * from t1 WHERE match (c2) against ('Beer');
93 CREATE VIEW v1 AS SELECT * from t1 WHERE match (c2) against ('Beer');
94 select * from v1;
95 drop view v1;
96 drop table t1;
97
98
99 # Test case for bug 6447
100 create table t1 (mytext text, FULLTEXT (mytext)) ENGINE = InnoDB;
101 insert t1 values ('aaabbb');
102
103 # INNODB_FTS: These variables are not support in InnoDB
104 check table t1;
105 # set @my_key_cache_block_size= @@global.key_cache_block_size;
106 # set GLOBAL key_cache_block_size=2048;
107 check table t1;
108 drop table t1;
109 # Restore the changed variable value
110 #set global key_cache_block_size= @my_key_cache_block_size;
111
112
113
114 #
115 # BUG#12075 - FULLTEXT non-functional for big5 strings
116 #
117 # INNODB_FTS: Not yet support big5
118 #CREATE TABLE t1 (a CHAR(50) CHARACTER SET big5 NOT NULL, FULLTEXT(a)) ENGINE = InnoDB;
119 #INSERT INTO t1 VALUES(0xA741ADCCA66EB6DC20A7DAADCCABDCA66E);
120 #SELECT HEX(a) FROM t1 WHERE MATCH(a) AGAINST (0xA741ADCCA66EB6DC IN BOOLEAN MODE);
121 #DROP TABLE t1;
122
123 #
124
125 create table t1 (a varchar(10), fulltext key(a)) ENGINE = InnoDB;
126 insert into t1 values ('a');
127 select hex(concat(match (a) against ('a'))) from t1;
128 create table t2 ENGINE = InnoDB as select concat(match (a) against ('a')) as a from t1;
129 show create table t2;
130 drop table t1, t2;
131
132
133 #
134 # BUG#31159 - fulltext search on ucs2 column crashes server
135 #
136 CREATE TABLE t1(a TEXT CHARSET ucs2 COLLATE ucs2_unicode_ci) ENGINE = InnoDB;
137 INSERT INTO t1 VALUES('abcd');
138
139 # INNODB_FTS: Please Note this table do not have FTS. InnoDB return 1214 error
140 --error ER_FT_MATCHING_KEY_NOT_FOUND
141 SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abcd' IN BOOLEAN MODE);
142 DROP TABLE t1;
143
144
145 #
146 # Some other simple tests with the current character set
147 #
148 create table t1 (a varchar(10), key(a), fulltext (a)) ENGINE = InnoDB;
149 insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
150 -- disable_result_log
151 ANALYZE TABLE t1;
152 -- enable_result_log
153 select * from t1 where a like "abc%";
154 select * from t1 where a like "test%";
155 select * from t1 where a like "te_t";
156 select * from t1 where match a against ("te*" in boolean mode)+0;
157 drop table t1;
158
159
160
161 --echo #
162 --echo # Bug #49734: Crash on EXPLAIN UNION ... ORDER BY
163 --echo # <any non-const-function>
164 --echo #
165
166 CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a)) ENGINE = InnoDB;
167 INSERT INTO t1 VALUES (1),(2);
168 CREATE TABLE t2 (b INT) ENGINE = InnoDB;
169 INSERT INTO t2 VALUES (1),(2);
170
171 --echo # Should not crash
172 EXPLAIN
173 SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12;
174
175 --echo # Should not crash
176 SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12;
177
178
179 --echo # Should not crash
180 --error ER_TABLE_CANT_HANDLE_FT
181 EXPLAIN
182 SELECT * FROM t1 UNION SELECT * FROM t1
183 ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
184
185 --echo # Should not crash
186 --error ER_TABLE_CANT_HANDLE_FT
187 SELECT * FROM t1 UNION SELECT * FROM t1
188 ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
189
190 # FIXME: Valgrind in MySQL code _MI_WRITE_BLOB_RECORD, bug #13389854
191 #--echo # Should not crash
192 #(SELECT * FROM t1) UNION (SELECT * FROM t1)
193 # ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
194
195
196 --echo # Should not crash
197 EXPLAIN
198 SELECT * FROM t1 UNION SELECT * FROM t1
199 ORDER BY (SELECT a FROM t2 WHERE b = 12);
200
201 --echo # Should not crash
202 --disable_result_log
203 SELECT * FROM t1 UNION SELECT * FROM t1
204 ORDER BY (SELECT a FROM t2 WHERE b = 12);
205 --enable_result_log
206
207 --echo # Should not crash
208 SELECT * FROM t2 UNION SELECT * FROM t2
209 ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE));
210
211 DROP TABLE t1,t2;
212
213
214 --echo End of 5.1 tests
215
216 --echo #
217 --echo # Bug#32979815: WL#14422: ASSERTION `!(USED_TABS & (~READ_TABLES & ~FILTER_FOR_TABLE))' FAILED
218 --echo #
219
220 CREATE TABLE t (x VARCHAR(10), FULLTEXT (x));
221
222 SELECT 1 FROM t
223 WHERE x IN
224 (SELECT MATCH (t2.x) AGAINST ('abc')
225 FROM t AS t1 JOIN t AS t2
226 ON t1.x = t2.x
227 GROUP BY t2.x
228 HAVING t2.x = '');
229
230 DROP TABLE t;
231
232 --echo #
233 --echo # Bug#32980875: WL#14422: ASSERTION `FALSE' FAILED|SQL/BASIC_ROW_ITERATORS.H
234 --echo #
235
236 CREATE TABLE t (a VARCHAR(10), FULLTEXT (a));
237 INSERT INTO t VALUES ('a'), ('b'), ('c');
238 ANALYZE TABLE t;
239
240 PREPARE prep_stmt FROM '
241
242 SELECT DISTINCT
243 MATCH (t1.a) AGAINST(''x''),
244 MIN(t2.a),
245 t1.a
246 FROM t AS t1 JOIN t AS t2 ON t1.a = t2.a
247 GROUP BY MATCH (t1.a) AGAINST(''x''), t1.a
248
249 ';
250
251 # The second execution used to fail with the hypergraph optimizer.
252 --sorted_result
253 EXECUTE prep_stmt;
254 --sorted_result
255 EXECUTE prep_stmt;
256
257 DEALLOCATE PREPARE prep_stmt;
258 DROP TABLE t;
259
260 --echo #
261 --echo # Bug#32990422: WL#14422: RESULT MISMATCH WITH FTS FUNCTION
262 --echo #
263
264 CREATE TABLE t (i INTEGER, x VARCHAR(10), FULLTEXT (x));
265 INSERT INTO t VALUES (0, 'a'), (1, 'b'), (0, 'c');
266 --sorted_result
267 SELECT i = 0 AS a, MATCH(x) AGAINST('d') AS b FROM t GROUP BY a, b;
268 DROP TABLE t;
269
270 --echo #
271 --echo # Bug#32996515: WL#14422: RESULT MISMATCH WITH DISTINCT AND GROUP BY ON FTS FUNCTION
272 --echo #
273
274 CREATE TABLE t (a INTEGER, b VARCHAR(10), FULLTEXT(b));
275 INSERT INTO t VALUES (1, 'a'), (2, 'b');
276 --sorted_result
277 SELECT DISTINCT MATCH (b) AGAINST ('x'), a
278 FROM t GROUP BY MATCH (b) AGAINST ('x'), a;
279 DROP TABLE t;
280
281 --echo #
282 --echo # Bug#34782389: Assertion `item->hidden == hidden' failed.
283 --echo #
284
285 CREATE TABLE t (a VARCHAR(10), FULLTEXT(a));
286 INSERT INTO t VALUES ('abc'), ('xyz');
287 SELECT 0 IN (SELECT MATCH (a) AGAINST ('abc') FROM t);
288 DROP TABLE t;