DROP TABLE IF EXISTS t1;
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 a, FORMAT(MATCH (message) AGAINST ('steve'),6) as rel FROM t1 ORDER BY rel;
a	rel
1	0.000000
2	0.000000
3	0.000000
5	0.000000
6	0.000000
7	0.895690
4	0.905873
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel;
a	rel
1	0
2	0
3	0
5	0
6	0
4	1
7	1
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;