diff options
Diffstat (limited to 'mysql-test/main/fulltext_order_by.result')
| -rw-r--r-- | mysql-test/main/fulltext_order_by.result | 176 |
1 files changed, 176 insertions, 0 deletions
diff --git a/mysql-test/main/fulltext_order_by.result b/mysql-test/main/fulltext_order_by.result new file mode 100644 index 00000000..a350a55c --- /dev/null +++ b/mysql-test/main/fulltext_order_by.result @@ -0,0 +1,176 @@ +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; |