Debian Codegraph @

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

raw
1 #
2 # Fulltext configurable parameters
3 #
4 --source include/have_innodb.inc
5
6 --disable_warnings
7 drop table if exists t1;
8 --enable_warnings
9
10 --echo #
11 --echo # Bug#56814 Explain + subselect + fulltext crashes server
12 --echo #
13
14 CREATE TABLE t1(f1 VARCHAR(6) NOT NULL,FULLTEXT KEY(f1),UNIQUE(f1)) ENGINE = InnoDB;
15 INSERT INTO t1 VALUES ('test');
16 EXPLAIN SELECT 1 FROM t1
17 WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST (""))
18 WHERE t1.f1 GROUP BY t1.f1));
19
20 PREPARE stmt FROM
21 'EXPLAIN SELECT 1 FROM t1
22 WHERE 1 > ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a
23 ON (MATCH(t1.f1) AGAINST (""))
24 WHERE t1.f1 GROUP BY t1.f1))';
25
26 EXECUTE stmt;
27 EXECUTE stmt;
28
29 DEALLOCATE PREPARE stmt;
30
31 PREPARE stmt FROM
32 'EXPLAIN SELECT 1 FROM t1
33 WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a
34 ON (MATCH(t1.f1) AGAINST (""))
35 WHERE t1.f1 GROUP BY t1.f1))';
36
37 EXECUTE stmt;
38 EXECUTE stmt;
39
40 DEALLOCATE PREPARE stmt;
41
42 DROP TABLE t1;
43
44 #try to crash gcc 2.96
45 --disable_warnings
46 drop table if exists t1;
47 --enable_warnings
48
49 CREATE TABLE t1 (
50 kodoboru varchar(10) default NULL,
51 obor tinytext,
52 aobor tinytext,
53 UNIQUE INDEX kodoboru (kodoboru),
54 FULLTEXT KEY obor (obor),
55 FULLTEXT KEY aobor (aobor)
56 ) ENGINE = InnoDB;
57
58 drop table t1;
59
60 CREATE TABLE t1 (
61 kodoboru varchar(10) default NULL,
62 obor tinytext,
63 aobor tinytext,
64 UNIQUE INDEX kodoboru (kodoboru),
65 FULLTEXT KEY obor (obor)
66 ) ENGINE = InnoDB;
67 INSERT INTO t1 VALUES ('0101000000','aaa','AAA');
68 INSERT INTO t1 VALUES ('0102000000','bbb','BBB');
69 INSERT INTO t1 VALUES ('0103000000','ccc','CCC');
70 INSERT INTO t1 VALUES ('0104000000','xxx','XXX');
71
72 select * from t1;
73 drop table t1;
74
75 # End of 4.1 tests
76
77
78 #
79 # Bug#20503: Server crash due to the ORDER clause isn't taken into account
80 # while space allocation
81 #
82 create table t1 (c1 varchar(1), c2 int, c3 int, c4 int, c5 int, c6 int,
83 c7 int, c8 int, c9 int, fulltext key (`c1`)) ENGINE = InnoDB;
84 select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8
85 from t1 where c9=1 order by c2, c2;
86 drop table t1;
87
88
89 #
90 # VIEW with full text
91 #
92 CREATE TABLE t1 (c1 int not null auto_increment primary key, c2 varchar(20), fulltext(c2)) ENGINE = InnoDB;
93 insert into t1 (c2) VALUES ('real Beer'),('Water'),('Kossu'),('Coca-Cola'),('Vodka'),('Wine'),('almost real Beer');
94 select * from t1 WHERE match (c2) against ('Beer');
95 CREATE VIEW v1 AS SELECT * from t1 WHERE match (c2) against ('Beer');
96 select * from v1;
97 drop view v1;
98 drop table t1;
99
100
101 # Test case for bug 6447
102 create table t1 (mytext text, FULLTEXT (mytext)) ENGINE = InnoDB;
103 insert t1 values ('aaabbb');
104
105 # INNODB_FTS: These variables are not support in InnoDB
106 check table t1;
107 # set @my_key_cache_block_size= @@global.key_cache_block_size;
108 # set GLOBAL key_cache_block_size=2048;
109 check table t1;
110 drop table t1;
111 # Restore the changed variable value
112 #set global key_cache_block_size= @my_key_cache_block_size;
113
114
115
116 #
117 # BUG#12075 - FULLTEXT non-functional for big5 strings
118 #
119 # INNODB_FTS: Not yet support big5
120 #CREATE TABLE t1 (a CHAR(50) CHARACTER SET big5 NOT NULL, FULLTEXT(a)) ENGINE = InnoDB;
121 #INSERT INTO t1 VALUES(0xA741ADCCA66EB6DC20A7DAADCCABDCA66E);
122 #SELECT HEX(a) FROM t1 WHERE MATCH(a) AGAINST (0xA741ADCCA66EB6DC IN BOOLEAN MODE);
123 #DROP TABLE t1;
124
125 #
126
127 create table t1 (a varchar(10), fulltext key(a)) ENGINE = InnoDB;
128 insert into t1 values ('a');
129 select hex(concat(match (a) against ('a'))) from t1;
130 create table t2 ENGINE = InnoDB as select concat(match (a) against ('a')) as a from t1;
131 show create table t2;
132 drop table t1, t2;
133
134
135 #
136 # BUG#31159 - fulltext search on ucs2 column crashes server
137 #
138 CREATE TABLE t1(a TEXT CHARSET ucs2 COLLATE ucs2_unicode_ci) ENGINE = InnoDB;
139 INSERT INTO t1 VALUES('abcd');
140
141 # INNODB_FTS: Please Note this table do not have FTS. InnoDB return 1214 error
142 --error ER_FT_MATCHING_KEY_NOT_FOUND
143 SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abcd' IN BOOLEAN MODE);
144 DROP TABLE t1;
145
146
147 #
148 # Some other simple tests with the current character set
149 #
150 create table t1 (a varchar(10), key(a), fulltext (a)) ENGINE = InnoDB;
151 insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
152 select * from t1 where a like "abc%";
153 select * from t1 where a like "test%";
154 select * from t1 where a like "te_t";
155 select * from t1 where match a against ("te*" in boolean mode);
156 drop table t1;
157
158
159
160 --echo #
161 --echo # Bug #49734: Crash on EXPLAIN EXTENDED UNION ... ORDER BY
162 --echo # <any non-const-function>
163 --echo #
164
165 CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a)) ENGINE = InnoDB;
166 INSERT INTO t1 VALUES (1),(2);
167 CREATE TABLE t2 (b INT) ENGINE = InnoDB;
168 INSERT INTO t2 VALUES (1),(2);
169
170 --echo # Should not crash
171 EXPLAIN EXTENDED
172 SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12;
173
174 --echo # Should not crash
175 SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12;
176
177
178 --echo # Should not crash
179 EXPLAIN EXTENDED
180 SELECT * FROM t1 UNION SELECT * FROM t1
181 ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
182
183 --echo # Should not crash
184 --sorted_result
185 SELECT * FROM t1 UNION SELECT * FROM t1
186 ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
187
188 # FIXME: Valgrind in MySQL code _MI_WRITE_BLOB_RECORD, bug #13389854
189 #--echo # Should not crash
190 #(SELECT * FROM t1) UNION (SELECT * FROM t1)
191 # ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
192
193
194 --echo # Should not crash
195 EXPLAIN EXTENDED
196 SELECT * FROM t1 UNION SELECT * FROM t1
197 ORDER BY (SELECT a FROM t2 WHERE b = 12);
198
199 --echo # Should not crash
200 --disable_result_log
201 SELECT * FROM t1 UNION SELECT * FROM t1
202 ORDER BY (SELECT a FROM t2 WHERE b = 12);
203 --enable_result_log
204
205 --echo # Should not crash
206 SELECT * FROM t2 UNION SELECT * FROM t2
207 ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE));
208
209 DROP TABLE t1,t2;
210
211
212 --echo End of 5.1 tests
213