Debian Codegraph @

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

raw
1 drop table if exists t1;
2 #
3 # Bug#56814 Explain + subselect + fulltext crashes server
4 #
5 CREATE TABLE t1(f1 VARCHAR(6) NOT NULL,FULLTEXT KEY(f1),UNIQUE(f1)) ENGINE = InnoDB;
6 INSERT INTO t1 VALUES ('test');
7 EXPLAIN SELECT 1 FROM t1
8 WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST (""))
9 WHERE t1.f1 GROUP BY t1.f1));
10 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
11 1 PRIMARY t1 NULL index NULL f1_2 26 NULL 1 100.00 Using index
12 2 SUBQUERY t1 NULL fulltext f1 f1 0 const 1 100.00 Using where; Ft_hints: no_ranking
13 2 SUBQUERY a NULL index NULL f1_2 26 NULL 1 100.00 Using index
14 Warnings:
15 Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where true
16 PREPARE stmt FROM
17 'EXPLAIN SELECT 1 FROM t1
18 WHERE 1 > ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a
19 ON (MATCH(t1.f1) AGAINST (""))
20 WHERE t1.f1 GROUP BY t1.f1))';
21 EXECUTE stmt;
22 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
23 1 PRIMARY t1 NULL index NULL f1_2 26 NULL 1 100.00 Using index
24 2 SUBQUERY a NULL index NULL f1_2 26 NULL 1 100.00 Using index
25 2 SUBQUERY t1 NULL fulltext f1 f1 0 const 1 100.00 Using where; Ft_hints: no_ranking
26 Warnings:
27 Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where true
28 EXECUTE stmt;
29 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
30 1 PRIMARY t1 NULL index NULL f1_2 26 NULL 1 100.00 Using index
31 2 SUBQUERY a NULL index NULL f1_2 26 NULL 1 100.00 Using index
32 2 SUBQUERY t1 NULL fulltext f1 f1 0 const 1 100.00 Using where; Ft_hints: no_ranking
33 Warnings:
34 Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where true
35 DEALLOCATE PREPARE stmt;
36 PREPARE stmt FROM
37 'EXPLAIN SELECT 1 FROM t1
38 WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a
39 ON (MATCH(t1.f1) AGAINST (""))
40 WHERE t1.f1 GROUP BY t1.f1))';
41 EXECUTE stmt;
42 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
43 1 PRIMARY t1 NULL index NULL f1_2 26 NULL 1 100.00 Using index
44 2 SUBQUERY t1 NULL fulltext f1 f1 0 const 1 100.00 Using where; Ft_hints: no_ranking
45 2 SUBQUERY a NULL index NULL f1_2 26 NULL 1 100.00 Using index
46 Warnings:
47 Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where true
48 EXECUTE stmt;
49 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
50 1 PRIMARY t1 NULL index NULL f1_2 26 NULL 1 100.00 Using index
51 2 SUBQUERY t1 NULL fulltext f1 f1 0 const 1 100.00 Using where; Ft_hints: no_ranking
52 2 SUBQUERY a NULL index NULL f1_2 26 NULL 1 100.00 Using index
53 Warnings:
54 Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where true
55 DEALLOCATE PREPARE stmt;
56 DROP TABLE t1;
57 drop table if exists t1;
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 FULLTEXT KEY aobor (aobor)
65 ) ENGINE = InnoDB;
66 drop table t1;
67 CREATE TABLE t1 (
68 kodoboru varchar(10) default NULL,
69 obor tinytext,
70 aobor tinytext,
71 UNIQUE INDEX kodoboru (kodoboru),
72 FULLTEXT KEY obor (obor)
73 ) ENGINE = InnoDB;
74 INSERT INTO t1 VALUES ('0101000000','aaa','AAA');
75 INSERT INTO t1 VALUES ('0102000000','bbb','BBB');
76 INSERT INTO t1 VALUES ('0103000000','ccc','CCC');
77 INSERT INTO t1 VALUES ('0104000000','xxx','XXX');
78 select * from t1;
79 kodoboru obor aobor
80 0101000000 aaa AAA
81 0102000000 bbb BBB
82 0103000000 ccc CCC
83 0104000000 xxx XXX
84 drop table t1;
85 create table t1 (c1 varchar(1), c2 int, c3 int, c4 int, c5 int, c6 int,
86 c7 int, c8 int, c9 int, fulltext key (`c1`)) ENGINE = InnoDB;
87 select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8
88 from t1 where c9=1 order by c2, c2;
89 match (`c1`) against ('z') c2 c3 c4 c5 c6 c7 c8
90 drop table t1;
91 CREATE TABLE t1 (c1 int not null auto_increment primary key, c2 varchar(20), fulltext(c2)) ENGINE = InnoDB;
92 insert into t1 (c2) VALUES ('real Beer'),('Water'),('Kossu'),('Coca-Cola'),('Vodka'),('Wine'),('almost real Beer');
93 select * from t1 WHERE match (c2) against ('Beer');
94 c1 c2
95 1 real Beer
96 7 almost real Beer
97 CREATE VIEW v1 AS SELECT * from t1 WHERE match (c2) against ('Beer');
98 select * from v1;
99 c1 c2
100 1 real Beer
101 7 almost real Beer
102 drop view v1;
103 drop table t1;
104 create table t1 (mytext text, FULLTEXT (mytext)) ENGINE = InnoDB;
105 insert t1 values ('aaabbb');
106 check table t1;
107 Table Op Msg_type Msg_text
108 test.t1 check status OK
109 check table t1;
110 Table Op Msg_type Msg_text
111 test.t1 check status OK
112 drop table t1;
113 create table t1 (a varchar(10), fulltext key(a)) ENGINE = InnoDB;
114 insert into t1 values ('a');
115 select hex(concat(match (a) against ('a'))) from t1;
116 hex(concat(match (a) against ('a')))
117 30
118 create table t2 ENGINE = InnoDB as select concat(match (a) against ('a')) as a from t1;
119 show create table t2;
120 Table Create Table
121 t2 CREATE TABLE `t2` (
122 `a` varchar(22) DEFAULT NULL
123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
124 drop table t1, t2;
125 CREATE TABLE t1(a TEXT CHARSET ucs2 COLLATE ucs2_unicode_ci) ENGINE = InnoDB;
126 Warnings:
127 Warning 1287 'ucs2' is deprecated and will be removed in a future release. Please use utf8mb4 instead
128 Warning 4079 'ucs2_unicode_ci' is a collation of the deprecated character set ucs2. Please consider using utf8mb4 with an appropriate collation instead.
129 INSERT INTO t1 VALUES('abcd');
130 SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abcd' IN BOOLEAN MODE);
131 ERROR HY000: Can't find FULLTEXT index matching the column list
132 DROP TABLE t1;
133 create table t1 (a varchar(10), key(a), fulltext (a)) ENGINE = InnoDB;
134 insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
135 ANALYZE TABLE t1;
136 select * from t1 where a like "abc%";
137 a
138 abc
139 abcd
140 select * from t1 where a like "test%";
141 a
142 test
143 select * from t1 where a like "te_t";
144 a
145 test
146 select * from t1 where match a against ("te*" in boolean mode)+0;
147 a
148 test
149 drop table t1;
150 #
151 # Bug #49734: Crash on EXPLAIN UNION ... ORDER BY
152 # <any non-const-function>
153 #
154 CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a)) ENGINE = InnoDB;
155 INSERT INTO t1 VALUES (1),(2);
156 CREATE TABLE t2 (b INT) ENGINE = InnoDB;
157 INSERT INTO t2 VALUES (1),(2);
158 # Should not crash
159 EXPLAIN
160 SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12;
161 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
162 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
163 2 UNION t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
164 3 UNION RESULT <union1,2> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary; Using filesort
165 Warnings:
166 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` union /* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by (`a` + 12)
167 # Should not crash
168 SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12;
169 a
170 1
171 2
172 # Should not crash
173 EXPLAIN
174 SELECT * FROM t1 UNION SELECT * FROM t1
175 ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
176 ERROR HY000: The used table type doesn't support FULLTEXT indexes
177 # Should not crash
178 SELECT * FROM t1 UNION SELECT * FROM t1
179 ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
180 ERROR HY000: The used table type doesn't support FULLTEXT indexes
181 # Should not crash
182 EXPLAIN
183 SELECT * FROM t1 UNION SELECT * FROM t1
184 ORDER BY (SELECT a FROM t2 WHERE b = 12);
185 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
186 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
187 2 UNION t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
188 4 UNION RESULT <union1,2> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary; Using filesort
189 5 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
190 Warnings:
191 Note 1276 Field or reference 'a' of SELECT #5 was resolved in SELECT #1
192 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` union /* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by (/* select#5 */ select `a` from `test`.`t2` where (`test`.`t2`.`b` = 12))
193 # Should not crash
194 SELECT * FROM t1 UNION SELECT * FROM t1
195 ORDER BY (SELECT a FROM t2 WHERE b = 12);
196 # Should not crash
197 SELECT * FROM t2 UNION SELECT * FROM t2
198 ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE));
199 b
200 1
201 2
202 DROP TABLE t1,t2;
203 End of 5.1 tests
204 #
205 # Bug#32979815: WL#14422: ASSERTION `!(USED_TABS & (~READ_TABLES & ~FILTER_FOR_TABLE))' FAILED
206 #
207 CREATE TABLE t (x VARCHAR(10), FULLTEXT (x));
208 SELECT 1 FROM t
209 WHERE x IN
210 (SELECT MATCH (t2.x) AGAINST ('abc')
211 FROM t AS t1 JOIN t AS t2
212 ON t1.x = t2.x
213 GROUP BY t2.x
214 HAVING t2.x = '');
215 1
216 DROP TABLE t;
217 #
218 # Bug#32980875: WL#14422: ASSERTION `FALSE' FAILED|SQL/BASIC_ROW_ITERATORS.H
219 #
220 CREATE TABLE t (a VARCHAR(10), FULLTEXT (a));
221 INSERT INTO t VALUES ('a'), ('b'), ('c');
222 ANALYZE TABLE t;
223 Table Op Msg_type Msg_text
224 test.t analyze status OK
225 PREPARE prep_stmt FROM '
226
227 SELECT DISTINCT
228 MATCH (t1.a) AGAINST(''x''),
229 MIN(t2.a),
230 t1.a
231 FROM t AS t1 JOIN t AS t2 ON t1.a = t2.a
232 GROUP BY MATCH (t1.a) AGAINST(''x''), t1.a
233
234 ';
235 EXECUTE prep_stmt;
236 MATCH (t1.a) AGAINST('x') MIN(t2.a) a
237 0 a a
238 0 b b
239 0 c c
240 EXECUTE prep_stmt;
241 MATCH (t1.a) AGAINST('x') MIN(t2.a) a
242 0 a a
243 0 b b
244 0 c c
245 DEALLOCATE PREPARE prep_stmt;
246 DROP TABLE t;
247 #
248 # Bug#32990422: WL#14422: RESULT MISMATCH WITH FTS FUNCTION
249 #
250 CREATE TABLE t (i INTEGER, x VARCHAR(10), FULLTEXT (x));
251 INSERT INTO t VALUES (0, 'a'), (1, 'b'), (0, 'c');
252 SELECT i = 0 AS a, MATCH(x) AGAINST('d') AS b FROM t GROUP BY a, b;
253 a b
254 0 0
255 1 0
256 DROP TABLE t;
257 #
258 # Bug#32996515: WL#14422: RESULT MISMATCH WITH DISTINCT AND GROUP BY ON FTS FUNCTION
259 #
260 CREATE TABLE t (a INTEGER, b VARCHAR(10), FULLTEXT(b));
261 INSERT INTO t VALUES (1, 'a'), (2, 'b');
262 SELECT DISTINCT MATCH (b) AGAINST ('x'), a
263 FROM t GROUP BY MATCH (b) AGAINST ('x'), a;
264 MATCH (b) AGAINST ('x') a
265 0 1
266 0 2
267 DROP TABLE t;
268 #
269 # Bug#34782389: Assertion `item->hidden == hidden' failed.
270 #
271 CREATE TABLE t (a VARCHAR(10), FULLTEXT(a));
272 INSERT INTO t VALUES ('abc'), ('xyz');
273 SELECT 0 IN (SELECT MATCH (a) AGAINST ('abc') FROM t);
274 0 IN (SELECT MATCH (a) AGAINST ('abc') FROM t)
275 1
276 DROP TABLE t;