1DROP TABLE IF EXISTS t1,t2,t3; 2CREATE TABLE t1 ( 3a INT AUTO_INCREMENT PRIMARY KEY, 4message CHAR(20), 5FULLTEXT(message) 6) comment = 'original testcase by sroussey@network54.com'; 7INSERT INTO t1 (message) VALUES ("Testing"),("table"),("testbug"), 8("steve"),("is"),("cool"),("steve is cool"); 9SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve'); 10a FORMAT(MATCH (message) AGAINST ('steve'),6) 114 0.905873 127 0.895690 13SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve'); 14a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) 154 1 167 1 17SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE); 18a FORMAT(MATCH (message) AGAINST ('steve'),6) 194 0.905873 207 0.895690 21SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE); 22a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) 234 1 247 1 25SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY a; 26a FORMAT(MATCH (message) AGAINST ('steve'),6) 274 0.905873 287 0.895690 29SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY a; 30a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) 314 1 327 1 33SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE a in (2,7,4) and MATCH (message) AGAINST ('steve') ORDER BY a DESC; 34a FORMAT(MATCH (message) AGAINST ('steve'),6) 357 0.895690 364 0.905873 37SELECT 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; 38a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) 397 1 404 1 41SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve') ORDER BY 1; 42a FORMAT(MATCH (message) AGAINST ('steve'),6) 437 0.895690 44SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY 1; 45a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) 467 1 47SELECT IF(a=7,'steve-is-cool',IF(a=4,'cool', 'other')), FORMAT(MATCH (message) AGAINST ('steve'),6) as rel FROM t1 ORDER BY rel; 48IF(a=7,'steve-is-cool',IF(a=4,'cool', 'other')) rel 49other 0.000000 50other 0.000000 51other 0.000000 52other 0.000000 53other 0.000000 54steve-is-cool 0.895690 55cool 0.905873 56SELECT IF(a=7,'match',IF(a=4,'match', 'no-match')), MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel; 57IF(a=7,'match',IF(a=4,'match', 'no-match')) rel 58no-match 0 59no-match 0 60no-match 0 61no-match 0 62no-match 0 63match 1 64match 1 65alter table t1 add key m (message); 66explain SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message; 67id select_type table type possible_keys key key_len ref rows Extra 681 SIMPLE t1 fulltext message message 0 1 Using where; Using filesort 69SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message desc; 70message 71steve is cool 72steve 73drop table t1; 74CREATE TABLE t1 ( 75a INT AUTO_INCREMENT PRIMARY KEY, 76message CHAR(20), 77FULLTEXT(message) 78); 79INSERT INTO t1 (message) VALUES ("testbug"),("testbug foobar"); 80SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1; 81a rel 821 1 832 2 84SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel,a; 85a rel 861 1 872 2 88drop table t1; 89CREATE TABLE t1 ( 90id int(11) NOT NULL auto_increment, 91thread int(11) NOT NULL default '0', 92beitrag longtext NOT NULL, 93PRIMARY KEY (id), 94KEY thread (thread), 95FULLTEXT KEY beitrag (beitrag) 96) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7923 ; 97CREATE TABLE t2 ( 98id int(11) NOT NULL auto_increment, 99text varchar(100) NOT NULL default '', 100PRIMARY KEY (id), 101KEY text (text) 102) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=63 ; 103CREATE TABLE t3 ( 104id int(11) NOT NULL auto_increment, 105forum int(11) NOT NULL default '0', 106betreff varchar(70) NOT NULL default '', 107PRIMARY KEY (id), 108KEY forum (forum), 109FULLTEXT KEY betreff (betreff) 110) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=996 ; 111select a.text, b.id, b.betreff 112from 113t2 a inner join t3 b on a.id = b.forum inner join 114t1 c on b.id = c.thread 115where 116match(b.betreff) against ('+abc' in boolean mode) 117group by a.text, b.id, b.betreff 118union 119select a.text, b.id, b.betreff 120from 121t2 a inner join t3 b on a.id = b.forum inner join 122t1 c on b.id = c.thread 123where 124match(c.beitrag) against ('+abc' in boolean mode) 125group by 126a.text, b.id, b.betreff 127order by 128match(b.betreff) against ('+abc' in boolean mode) desc; 129ERROR 42000: Table 'b' from one of the SELECTs cannot be used in ORDER clause 130select a.text, b.id, b.betreff 131from 132t2 a inner join t3 b on a.id = b.forum inner join 133t1 c on b.id = c.thread 134where 135match(b.betreff) against ('+abc' in boolean mode) 136union 137select a.text, b.id, b.betreff 138from 139t2 a inner join t3 b on a.id = b.forum inner join 140t1 c on b.id = c.thread 141where 142match(c.beitrag) against ('+abc' in boolean mode) 143order by 144match(b.betreff) against ('+abc' in boolean mode) desc; 145ERROR 42000: Table 'b' from one of the SELECTs cannot be used in ORDER clause 146select a.text, b.id, b.betreff 147from 148t2 a inner join t3 b on a.id = b.forum inner join 149t1 c on b.id = c.thread 150where 151match(b.betreff) against ('+abc' in boolean mode) 152union 153select a.text, b.id, b.betreff 154from 155t2 a inner join t3 b on a.id = b.forum inner join 156t1 c on b.id = c.thread 157where 158match(c.beitrag) against ('+abc' in boolean mode) 159order by 160match(betreff) against ('+abc' in boolean mode) desc; 161text id betreff 162(select b.id, b.betreff from t3 b) union 163(select b.id, b.betreff from t3 b) 164order by match(betreff) against ('+abc' in boolean mode) desc; 165id betreff 166(select b.id, b.betreff from t3 b) union 167(select b.id, b.betreff from t3 b) 168order by match(betreff) against ('+abc') desc; 169ERROR HY000: Can't find FULLTEXT index matching the column list 170select distinct b.id, b.betreff from t3 b 171order by match(betreff) against ('+abc' in boolean mode) desc; 172id betreff 173select b.id, b.betreff from t3 b group by b.id+1 174order by match(betreff) against ('+abc' in boolean mode) desc; 175id betreff 176drop table t1,t2,t3; 177