1drop table if exists t1; 2# 3# Bug#56814 Explain + subselect + fulltext crashes server 4# 5CREATE TABLE t1(f1 VARCHAR(6) NOT NULL,FULLTEXT KEY(f1),UNIQUE(f1)) ENGINE = InnoDB; 6INSERT INTO t1 VALUES ('test'); 7EXPLAIN SELECT 1 FROM t1 8WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) 9WHERE t1.f1 GROUP BY t1.f1)); 10id select_type table type possible_keys key key_len ref rows Extra 111 PRIMARY t1 index NULL f1_2 8 NULL 1 Using index 122 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where 132 SUBQUERY a index NULL f1_2 8 NULL 1 Using index 14PREPARE 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))'; 19EXECUTE stmt; 20id select_type table type possible_keys key key_len ref rows Extra 211 PRIMARY t1 index NULL f1_2 8 NULL 1 Using index 222 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where 232 SUBQUERY a index NULL f1_2 8 NULL 1 Using index 24EXECUTE stmt; 25id select_type table type possible_keys key key_len ref rows Extra 261 PRIMARY t1 index NULL f1_2 8 NULL 1 Using index 272 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where 282 SUBQUERY a index NULL f1_2 8 NULL 1 Using index 29DEALLOCATE PREPARE stmt; 30PREPARE 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))'; 35EXECUTE stmt; 36id select_type table type possible_keys key key_len ref rows Extra 371 PRIMARY t1 index NULL f1_2 8 NULL 1 Using index 382 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where 392 SUBQUERY a index NULL f1_2 8 NULL 1 Using index 40EXECUTE stmt; 41id select_type table type possible_keys key key_len ref rows Extra 421 PRIMARY t1 index NULL f1_2 8 NULL 1 Using index 432 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where 442 SUBQUERY a index NULL f1_2 8 NULL 1 Using index 45DEALLOCATE PREPARE stmt; 46DROP TABLE t1; 47drop table if exists t1; 48CREATE TABLE t1 ( 49kodoboru varchar(10) default NULL, 50obor tinytext, 51aobor tinytext, 52UNIQUE INDEX kodoboru (kodoboru), 53FULLTEXT KEY obor (obor), 54FULLTEXT KEY aobor (aobor) 55) ENGINE = InnoDB; 56drop table t1; 57CREATE TABLE t1 ( 58kodoboru varchar(10) default NULL, 59obor tinytext, 60aobor tinytext, 61UNIQUE INDEX kodoboru (kodoboru), 62FULLTEXT KEY obor (obor) 63) ENGINE = InnoDB; 64INSERT INTO t1 VALUES ('0101000000','aaa','AAA'); 65INSERT INTO t1 VALUES ('0102000000','bbb','BBB'); 66INSERT INTO t1 VALUES ('0103000000','ccc','CCC'); 67INSERT INTO t1 VALUES ('0104000000','xxx','XXX'); 68select * from t1; 69kodoboru obor aobor 700101000000 aaa AAA 710102000000 bbb BBB 720103000000 ccc CCC 730104000000 xxx XXX 74drop table t1; 75create table t1 (c1 varchar(1), c2 int, c3 int, c4 int, c5 int, c6 int, 76c7 int, c8 int, c9 int, fulltext key (`c1`)) ENGINE = InnoDB; 77select distinct match (`c1`) against ('z') , c2, c3, c4,c5, c6,c7, c8 78from t1 where c9=1 order by c2, c2; 79match (`c1`) against ('z') c2 c3 c4 c5 c6 c7 c8 80drop table t1; 81CREATE TABLE t1 (c1 int not null auto_increment primary key, c2 varchar(20), fulltext(c2)) ENGINE = InnoDB; 82insert into t1 (c2) VALUES ('real Beer'),('Water'),('Kossu'),('Coca-Cola'),('Vodka'),('Wine'),('almost real Beer'); 83select * from t1 WHERE match (c2) against ('Beer'); 84c1 c2 851 real Beer 867 almost real Beer 87CREATE VIEW v1 AS SELECT * from t1 WHERE match (c2) against ('Beer'); 88select * from v1; 89c1 c2 901 real Beer 917 almost real Beer 92drop view v1; 93drop table t1; 94create table t1 (mytext text, FULLTEXT (mytext)) ENGINE = InnoDB; 95insert t1 values ('aaabbb'); 96check table t1; 97Table Op Msg_type Msg_text 98test.t1 check status OK 99check table t1; 100Table Op Msg_type Msg_text 101test.t1 check status OK 102drop table t1; 103create table t1 (a varchar(10), fulltext key(a)) ENGINE = InnoDB; 104insert into t1 values ('a'); 105select hex(concat(match (a) against ('a'))) from t1; 106hex(concat(match (a) against ('a'))) 10730 108create table t2 ENGINE = InnoDB as select concat(match (a) against ('a')) as a from t1; 109show create table t2; 110Table Create Table 111t2 CREATE TABLE `t2` ( 112 `a` varchar(23) DEFAULT NULL 113) ENGINE=InnoDB DEFAULT CHARSET=latin1 114drop table t1, t2; 115CREATE TABLE t1(a TEXT CHARSET ucs2 COLLATE ucs2_unicode_ci) ENGINE = InnoDB; 116INSERT INTO t1 VALUES('abcd'); 117SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abcd' IN BOOLEAN MODE); 118ERROR HY000: Can't find FULLTEXT index matching the column list 119DROP TABLE t1; 120create table t1 (a varchar(10), key(a), fulltext (a)) ENGINE = InnoDB; 121insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test"); 122select * from t1 where a like "abc%"; 123a 124abc 125abcd 126select * from t1 where a like "test%"; 127a 128test 129select * from t1 where a like "te_t"; 130a 131test 132select * from t1 where match a against ("te*" in boolean mode)+0; 133a 134drop table t1; 135# 136# Bug #49734: Crash on EXPLAIN EXTENDED UNION ... ORDER BY 137# <any non-const-function> 138# 139CREATE TABLE t1 (a VARCHAR(10), FULLTEXT KEY a (a)) ENGINE = InnoDB; 140INSERT INTO t1 VALUES (1),(2); 141CREATE TABLE t2 (b INT) ENGINE = InnoDB; 142INSERT INTO t2 VALUES (1),(2); 143# Should not crash 144EXPLAIN EXTENDED 145SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12; 146id select_type table type possible_keys key key_len ref rows filtered Extra 1471 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 1482 UNION t1 ALL NULL NULL NULL NULL 2 100.00 149NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort 150Warnings: 151Note 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 152# Should not crash 153SELECT * FROM t1 UNION SELECT * FROM t1 ORDER BY a + 12; 154a 1551 1562 157# Should not crash 158EXPLAIN EXTENDED 159SELECT * FROM t1 UNION SELECT * FROM t1 160ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE); 161id select_type table type possible_keys key key_len ref rows filtered Extra 1621 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 1632 UNION t1 ALL NULL NULL NULL NULL 2 100.00 164NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort 165Warnings: 166Note 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)) 167# Should not crash 168SELECT * FROM t1 UNION SELECT * FROM t1 169ORDER BY MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE); 170a 1711 1722 173# Should not crash 174EXPLAIN EXTENDED 175SELECT * FROM t1 UNION SELECT * FROM t1 176ORDER BY (SELECT a FROM t2 WHERE b = 12); 177id select_type table type possible_keys key key_len ref rows filtered Extra 1781 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 1792 UNION t1 ALL NULL NULL NULL NULL 2 100.00 180NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort 1813 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where 182Warnings: 183Note 1276 Field or reference 'a' of SELECT #3 was resolved in SELECT #-1 184Note 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)) 185# Should not crash 186SELECT * FROM t1 UNION SELECT * FROM t1 187ORDER BY (SELECT a FROM t2 WHERE b = 12); 188# Should not crash 189SELECT * FROM t2 UNION SELECT * FROM t2 190ORDER BY (SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abc' IN BOOLEAN MODE)); 191b 1921 1932 194DROP TABLE t1,t2; 195End of 5.1 tests 196