DROP TABLE IF EXISTS t1,t2,t3;
CREATE TABLE t1 (
a INT AUTO_INCREMENT PRIMARY KEY,
message CHAR(20),
FULLTEXT(message)
) comment = 'original testcase by sroussey@network54.com';
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("testbug"),
("steve"),("is"),("cool"),("steve is cool");
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve');
a	FORMAT(MATCH (message) AGAINST ('steve'),6)
4	0.905873
7	0.895690
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve');
a	MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
4	1
7	1
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
a	FORMAT(MATCH (message) AGAINST ('steve'),6)
4	0.905873
7	0.895690
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
a	MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
4	1
7	1
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY a;
a	FORMAT(MATCH (message) AGAINST ('steve'),6)
4	0.905873
7	0.895690
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY a;
a	MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
4	1
7	1
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE a in (2,7,4) and MATCH (message) AGAINST ('steve') ORDER BY a DESC;
a	FORMAT(MATCH (message) AGAINST ('steve'),6)
7	0.895690
4	0.905873
SELECT 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;
a	MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
7	1
4	1
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve') ORDER BY 1;
a	FORMAT(MATCH (message) AGAINST ('steve'),6)
7	0.895690
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY 1;
a	MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
7	1
SELECT IF(a=7,'steve-is-cool',IF(a=4,'cool', 'other')), FORMAT(MATCH (message) AGAINST ('steve'),6) as rel FROM t1 ORDER BY rel;
IF(a=7,'steve-is-cool',IF(a=4,'cool', 'other'))	rel
other	0.000000
other	0.000000
other	0.000000
other	0.000000
other	0.000000
steve-is-cool	0.895690
cool	0.905873
SELECT IF(a=7,'match',IF(a=4,'match', 'no-match')), MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel;
IF(a=7,'match',IF(a=4,'match', 'no-match'))	rel
no-match	0
no-match	0
no-match	0
no-match	0
no-match	0
match	1
match	1
alter table t1 add key m (message);
explain SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	message	message	0		1	Using where; Using filesort
SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message desc;
message
steve is cool
steve
drop table t1;
CREATE TABLE t1 (
a INT AUTO_INCREMENT PRIMARY KEY,
message CHAR(20),
FULLTEXT(message)
);
INSERT INTO t1 (message) VALUES ("testbug"),("testbug foobar");
SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1;
a	rel
1	1
2	2
SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel,a;
a	rel
1	1
2	2
drop table t1;
CREATE TABLE t1 (
id int(11) NOT NULL auto_increment,
thread int(11) NOT NULL default '0',
beitrag longtext NOT NULL,
PRIMARY KEY  (id),
KEY thread (thread),
FULLTEXT KEY beitrag (beitrag)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7923 ;
CREATE TABLE t2 (
id int(11) NOT NULL auto_increment,
text varchar(100) NOT NULL default '',
PRIMARY KEY  (id),
KEY text (text)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=63 ;
CREATE TABLE t3 (
id int(11) NOT NULL auto_increment,
forum int(11) NOT NULL default '0',
betreff varchar(70) NOT NULL default '',
PRIMARY KEY  (id),
KEY forum (forum),
FULLTEXT KEY betreff (betreff)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=996 ;
select a.text, b.id, b.betreff
from 
t2 a inner join t3 b on a.id = b.forum inner join
t1 c on b.id = c.thread
where 
match(b.betreff) against ('+abc' in boolean mode)
group by a.text, b.id, b.betreff
union
select a.text, b.id, b.betreff
from 
t2 a inner join t3 b on a.id = b.forum inner join
t1 c on b.id = c.thread
where 
match(c.beitrag) against ('+abc' in boolean mode)
group by 
a.text, b.id, b.betreff
order by 
match(b.betreff) against ('+abc' in boolean mode) desc;
ERROR 42000: Table 'b' from one of the SELECTs cannot be used in ORDER clause
select a.text, b.id, b.betreff
from 
t2 a inner join t3 b on a.id = b.forum inner join
t1 c on b.id = c.thread
where 
match(b.betreff) against ('+abc' in boolean mode)
union
select a.text, b.id, b.betreff
from 
t2 a inner join t3 b on a.id = b.forum inner join
t1 c on b.id = c.thread
where 
match(c.beitrag) against ('+abc' in boolean mode)
order by 
match(b.betreff) against ('+abc' in boolean mode) desc;
ERROR 42000: Table 'b' from one of the SELECTs cannot be used in ORDER clause
select a.text, b.id, b.betreff
from 
t2 a inner join t3 b on a.id = b.forum inner join
t1 c on b.id = c.thread
where 
match(b.betreff) against ('+abc' in boolean mode)
union
select a.text, b.id, b.betreff
from 
t2 a inner join t3 b on a.id = b.forum inner join
t1 c on b.id = c.thread
where 
match(c.beitrag) against ('+abc' in boolean mode)
order by 
match(betreff) against ('+abc' in boolean mode) desc;
text	id	betreff
(select b.id, b.betreff from t3 b) union 
(select b.id, b.betreff from t3 b) 
order by match(betreff) against ('+abc' in boolean mode) desc;
id	betreff
(select b.id, b.betreff from t3 b) union 
(select b.id, b.betreff from t3 b) 
order by match(betreff) against ('+abc') desc;
ERROR HY000: Can't find FULLTEXT index matching the column list
select distinct b.id, b.betreff from t3 b 
order by match(betreff) against ('+abc' in boolean mode) desc;
id	betreff
select b.id, b.betreff from t3 b group by b.id+1 
order by match(betreff) against ('+abc' in boolean mode) desc;
id	betreff
drop table t1,t2,t3;