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 type possible_keys key key_len ref rows Extra
11 1 PRIMARY t1 ALL NULL NULL NULL NULL 1
12 2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where
13 2 SUBQUERY a ALL NULL NULL NULL NULL 1
14 PREPARE stmt FROM
15 'EXPLAIN SELECT 1 FROM t1
16 WHERE 1 > ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a
17 ON (MATCH(t1.f1) AGAINST (""))
18 WHERE t1.f1 GROUP BY t1.f1))';
19 EXECUTE stmt;
20 id select_type table type possible_keys key key_len ref rows Extra
21 1 PRIMARY t1 ALL NULL NULL NULL NULL 1
22 2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where
23 2 SUBQUERY a ALL NULL NULL NULL NULL 1
24 EXECUTE stmt;
25 id select_type table type possible_keys key key_len ref rows Extra
26 1 PRIMARY t1 ALL NULL NULL NULL NULL 1
27 2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where
28 2 SUBQUERY a ALL NULL NULL NULL NULL 1
29 DEALLOCATE PREPARE stmt;
30 PREPARE stmt FROM
31 'EXPLAIN SELECT 1 FROM t1
32 WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a
33 ON (MATCH(t1.f1) AGAINST (""))
34 WHERE t1.f1 GROUP BY t1.f1))';
35 EXECUTE stmt;
36 id select_type table type possible_keys key key_len ref rows Extra
37 1 PRIMARY t1 ALL NULL NULL NULL NULL 1
38 2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where
39 2 SUBQUERY a ALL NULL NULL NULL NULL 1
40 EXECUTE stmt;
41 id select_type table type possible_keys key key_len ref rows Extra
42 1 PRIMARY t1 ALL NULL NULL NULL NULL 1
43 2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where
44 2 SUBQUERY a ALL NULL NULL NULL NULL 1
45 DEALLOCATE PREPARE stmt;
46 DROP TABLE t1;
47 drop table if exists t1;
48 CREATE TABLE t1 (
49 kodoboru varchar(10) default NULL,
50 obor tinytext,
51 aobor tinytext,
52 UNIQUE INDEX kodoboru (kodoboru),
53 FULLTEXT KEY obor (obor),
54 FULLTEXT KEY aobor (aobor)
55 ) ENGINE = InnoDB;
56 drop table t1;
57 CREATE TABLE t1 (
58 kodoboru varchar(10) default NULL,
59 obor tinytext,
60 aobor tinytext,
61 UNIQUE INDEX kodoboru (kodoboru),
62 FULLTEXT KEY obor (obor)
63 ) ENGINE = InnoDB;
64 INSERT INTO t1 VALUES ('0101000000','aaa','AAA');
65 INSERT INTO t1 VALUES ('0102000000','bbb','BBB');
66 INSERT INTO t1 VALUES ('0103000000','ccc','CCC');
67 INSERT INTO t1 VALUES ('0104000000','xxx','XXX');
68 select * from t1;
69 kodoboru obor aobor
70 0101000000 aaa AAA
71 0102000000 bbb BBB
72 0103000000 ccc CCC
73 0104000000 xxx XXX
74 drop table t1;
75 create table t1 (c1 varchar(1), c2 int, c3 int, c4 int, c5 int, c6 int,
76 c7 int, c8 int, c9 int, fulltext key (`c1`)) ENGINE = InnoDB;
77 select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8
78 from t1 where c9=1 order by c2, c2;
79 match (`c1`) against ('z') c2 c3 c4 c5 c6 c7 c8
80 drop table t1;
81 CREATE TABLE t1 (c1 int not null auto_increment primary key, c2 varchar(20), fulltext(c2)) ENGINE = InnoDB;
82 insert into t1 (c2) VALUES ('real Beer'),('Water'),('Kossu'),('Coca-Cola'),('Vodka'),('Wine'),('almost real Beer');
83 select * from t1 WHERE match (c2) against ('Beer');
84 c1 c2
85 1 real Beer
86 7 almost real Beer
87 CREATE VIEW v1 AS SELECT * from t1 WHERE match (c2) against ('Beer');
88 select * from v1;
89 c1 c2
90 1 real Beer
91 7 almost real Beer
92 drop view v1;
93 drop table t1;
94 create table t1 (mytext text, FULLTEXT (mytext)) ENGINE = InnoDB;
95 insert t1 values ('aaabbb');
96 check table t1;
97 Table Op Msg_type Msg_text
98 test.t1 check status OK
99 check table t1;
100 Table Op Msg_type Msg_text
101 test.t1 check status OK
102 drop table t1;
103 create table t1 (a varchar(10), fulltext key(a)) ENGINE = InnoDB;
104 insert into t1 values ('a');
105 select hex(concat(match (a) against ('a'))) from t1;
106 hex(concat(match (a) against ('a')))
107 30
108 create table t2 ENGINE = InnoDB as select concat(match (a) against ('a')) as a from t1;
109 show create table t2;
110 Table Create Table
111 t2 CREATE TABLE `t2` (
112 `a` varchar(23) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL
113 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
114 drop table t1, t2;
115 CREATE TABLE t1(a TEXT CHARSET ucs2 COLLATE ucs2_unicode_ci) ENGINE = InnoDB;
116 INSERT INTO t1 VALUES('abcd');
117 SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abcd' IN BOOLEAN MODE);
118 ERROR HY000: Can't find FULLTEXT index matching the column list
119 DROP TABLE t1;
120 create table t1 (a varchar(10), key(a), fulltext (a)) ENGINE = InnoDB;
121 insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
122 select * from t1 where a like "abc%";
123 a
124 abc
125 abcd
126 select * from t1 where a like "test%";
127 a
128 test
129 select * from t1 where a like "te_t";
130 a
131 test
132 select * from t1 where match a against ("te*" in boolean mode);
133 a
134 test
135 drop table t1;
136 #
137 # Bug #49734: Crash on EXPLAIN EXTENDED UNION ... ORDER BY
138 # <any non-const-function>
139 #
140 CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a)) ENGINE = InnoDB;
141 INSERT INTO t1 VALUES (1),(2);
142 CREATE TABLE t2 (b INT) ENGINE = InnoDB;
143 INSERT INTO t2 VALUES (1),(2);
144 # Should not crash
145 EXPLAIN EXTENDED
146 SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12;
147 id select_type table type possible_keys key key_len ref rows filtered Extra
148 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
149 2 UNION t1 ALL NULL NULL NULL NULL 2 100.00
150 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort
151 Warnings:
152 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
153 # Should not crash
154 SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12;
155 a
156 1
157 2
158 # Should not crash
159 EXPLAIN EXTENDED
160 SELECT * FROM t1 UNION SELECT * FROM t1
161 ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
162 id select_type table type possible_keys key key_len ref rows filtered Extra
163 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
164 2 UNION t1 ALL NULL NULL NULL NULL 2 100.00
165 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort
166 Warnings:
167 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 (match `a` against ('+abc' in boolean mode))
168 # Should not crash
169 SELECT * FROM t1 UNION SELECT * FROM t1
170 ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE);
171 a
172 1
173 2
174 # Should not crash
175 EXPLAIN EXTENDED
176 SELECT * FROM t1 UNION SELECT * FROM t1
177 ORDER BY (SELECT a FROM t2 WHERE b = 12);
178 id select_type table type possible_keys key key_len ref rows filtered Extra
179 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
180 2 UNION t1 ALL NULL NULL NULL NULL 2 100.00
181 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort
182 3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
183 Warnings:
184 Note 1276 Field or reference 'a' of SELECT #3 was resolved in SELECT #-1
185 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 <expr_cache><`a`>((/* select#3 */ select `a` from `test`.`t2` where `test`.`t2`.`b` = 12))
186 # Should not crash
187 SELECT * FROM t1 UNION SELECT * FROM t1
188 ORDER BY (SELECT a FROM t2 WHERE b = 12);
189 # Should not crash
190 SELECT * FROM t2 UNION SELECT * FROM t2
191 ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE));
192 b
193 1
194 2
195 DROP TABLE t1,t2;
196 End of 5.1 tests