fulltext_order_by.test 2.18 KB
Newer Older
1 2
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
3 4 5
  a INT AUTO_INCREMENT PRIMARY KEY,
  message CHAR(20),
  FULLTEXT(message)
unknown's avatar
unknown committed
6
) comment = 'original testcase by sroussey@network54.com';
7
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("testbug"),
8
        ("steve"),("is"),("cool"),("steve is cool");
unknown's avatar
unknown committed
9
# basic MATCH
10
SELECT a, MATCH (message) AGAINST ('steve') FROM t1 WHERE MATCH (message) AGAINST ('steve');
11 12 13
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve');
SELECT a, MATCH (message) AGAINST ('steve') FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
unknown's avatar
unknown committed
14

15
# MATCH + ORDER BY (with ft-ranges)
16
SELECT a, MATCH (message) AGAINST ('steve') FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY a;
17
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY a;
unknown's avatar
unknown committed
18

19
# MATCH + ORDER BY (with normal ranges) + UNIQUE
20
SELECT a, MATCH (message) AGAINST ('steve') FROM t1 WHERE a in (2,7,4) and MATCH (message) AGAINST ('steve') ORDER BY a DESC;
21
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;
unknown's avatar
unknown committed
22

unknown's avatar
unknown committed
23
# MATCH + ORDER BY + UNIQUE (const_table)
24
SELECT a, MATCH (message) AGAINST ('steve') FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve') ORDER BY 1;
25
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;
unknown's avatar
unknown committed
26

unknown's avatar
unknown committed
27
# ORDER BY MATCH
28
SELECT a, MATCH (message) AGAINST ('steve') as rel FROM t1 ORDER BY rel;
29
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel;
30 31

drop table t1;
32 33 34 35 36 37 38 39 40 41 42 43 44 45

#
# reused boolean scan bug
#
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;
SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel,a;
drop table t1;