1--disable_warnings 2DROP TABLE IF EXISTS t1,t2,t3; 3--enable_warnings 4 5CREATE TABLE t1 ( 6 a INT AUTO_INCREMENT PRIMARY KEY, 7 message CHAR(20), 8 FULLTEXT(message) 9) comment = 'original testcase by sroussey@network54.com'; 10INSERT INTO t1 (message) VALUES ("Testing"),("table"),("testbug"), 11 ("steve"),("is"),("cool"),("steve is cool"); 12# basic MATCH 13SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve'); 14SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve'); 15SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE); 16SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE); 17 18# MATCH + ORDER BY (with ft-ranges) 19SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY a; 20SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY a; 21 22# MATCH + ORDER BY (with normal ranges) + UNIQUE 23SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE a in (2,7,4) and MATCH (message) AGAINST ('steve') ORDER BY a DESC; 24SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE a in (2,7,4) and MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY a DESC; 25 26# MATCH + ORDER BY + UNIQUE (const_table) 27SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve') ORDER BY 1; 28SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY 1; 29 30# ORDER BY MATCH 31SELECT IF(a=7,'steve-is-cool',IF(a=4,'cool', 'other')), FORMAT(MATCH (message) AGAINST ('steve'),6) as rel FROM t1 ORDER BY rel; 32SELECT IF(a=7,'match',IF(a=4,'match', 'no-match')), MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel; 33 34# 35# BUG#6635 - test_if_skip_sort_order() thought it can skip filesort 36# for fulltext searches too 37# 38alter table t1 add key m (message); 39explain SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message; 40SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message desc; 41 42drop table t1; 43 44# 45# reused boolean scan bug 46# 47CREATE TABLE t1 ( 48 a INT AUTO_INCREMENT PRIMARY KEY, 49 message CHAR(20), 50 FULLTEXT(message) 51); 52INSERT INTO t1 (message) VALUES ("testbug"),("testbug foobar"); 53SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1; 54SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel,a; 55drop table t1; 56 57# BUG#11869 58CREATE TABLE t1 ( 59 id int(11) NOT NULL auto_increment, 60 thread int(11) NOT NULL default '0', 61 beitrag longtext NOT NULL, 62 PRIMARY KEY (id), 63 KEY thread (thread), 64 FULLTEXT KEY beitrag (beitrag) 65) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7923 ; 66 67CREATE TABLE t2 ( 68 id int(11) NOT NULL auto_increment, 69 text varchar(100) NOT NULL default '', 70 PRIMARY KEY (id), 71 KEY text (text) 72) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=63 ; 73 74CREATE TABLE t3 ( 75 id int(11) NOT NULL auto_increment, 76 forum int(11) NOT NULL default '0', 77 betreff varchar(70) NOT NULL default '', 78 PRIMARY KEY (id), 79 KEY forum (forum), 80 FULLTEXT KEY betreff (betreff) 81) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=996 ; 82 83--error ER_TABLENAME_NOT_ALLOWED_HERE 84select a.text, b.id, b.betreff 85from 86 t2 a inner join t3 b on a.id = b.forum inner join 87 t1 c on b.id = c.thread 88where 89 match(b.betreff) against ('+abc' in boolean mode) 90group by a.text, b.id, b.betreff 91union 92select a.text, b.id, b.betreff 93from 94 t2 a inner join t3 b on a.id = b.forum inner join 95 t1 c on b.id = c.thread 96where 97 match(c.beitrag) against ('+abc' in boolean mode) 98group by 99 a.text, b.id, b.betreff 100order by 101 match(b.betreff) against ('+abc' in boolean mode) desc; 102 103--error ER_TABLENAME_NOT_ALLOWED_HERE 104select a.text, b.id, b.betreff 105from 106 t2 a inner join t3 b on a.id = b.forum inner join 107 t1 c on b.id = c.thread 108where 109 match(b.betreff) against ('+abc' in boolean mode) 110union 111select a.text, b.id, b.betreff 112from 113 t2 a inner join t3 b on a.id = b.forum inner join 114 t1 c on b.id = c.thread 115where 116 match(c.beitrag) against ('+abc' in boolean mode) 117order by 118 match(b.betreff) against ('+abc' in boolean mode) desc; 119 120select a.text, b.id, b.betreff 121from 122 t2 a inner join t3 b on a.id = b.forum inner join 123 t1 c on b.id = c.thread 124where 125 match(b.betreff) against ('+abc' in boolean mode) 126union 127select a.text, b.id, b.betreff 128from 129 t2 a inner join t3 b on a.id = b.forum inner join 130 t1 c on b.id = c.thread 131where 132 match(c.beitrag) against ('+abc' in boolean mode) 133order by 134 match(betreff) against ('+abc' in boolean mode) desc; 135 136# BUG#11869 part2: used table type doesn't support FULLTEXT indexes error 137(select b.id, b.betreff from t3 b) union 138(select b.id, b.betreff from t3 b) 139order by match(betreff) against ('+abc' in boolean mode) desc; 140 141--error 1191 142(select b.id, b.betreff from t3 b) union 143(select b.id, b.betreff from t3 b) 144order by match(betreff) against ('+abc') desc; 145 146select distinct b.id, b.betreff from t3 b 147order by match(betreff) against ('+abc' in boolean mode) desc; 148 149select b.id, b.betreff from t3 b group by b.id+1 150order by match(betreff) against ('+abc' in boolean mode) desc; 151 152drop table t1,t2,t3; 153 154# End of 4.1 tests 155