fulltext_order_by.test 4.65 KB
Newer Older
1
--disable_warnings
2
DROP TABLE IF EXISTS t1;
3 4
--enable_warnings

5
CREATE TABLE t1 (
6 7 8
  a INT AUTO_INCREMENT PRIMARY KEY,
  message CHAR(20),
  FULLTEXT(message)
9
) comment = 'original testcase by sroussey@network54.com';
10
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("testbug"),
11
        ("steve"),("is"),("cool"),("steve is cool");
12
# basic MATCH
wax@kishkin.ru's avatar
wax@kishkin.ru committed
13
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve');
14
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve');
wax@kishkin.ru's avatar
wax@kishkin.ru committed
15
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
16
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
17

18
# MATCH + ORDER BY (with ft-ranges)
wax@kishkin.ru's avatar
wax@kishkin.ru committed
19
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY a;
20
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY a;
21

22
# MATCH + ORDER BY (with normal ranges) + UNIQUE
wax@kishkin.ru's avatar
wax@kishkin.ru committed
23
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;
24
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;
25

26
# MATCH + ORDER BY + UNIQUE (const_table)
wax@kishkin.ru's avatar
wax@kishkin.ru committed
27
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve') ORDER BY 1;
28
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;
29

30
# ORDER BY MATCH
wax@kishkin.ru's avatar
wax@kishkin.ru committed
31
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) as rel FROM t1 ORDER BY rel;
32
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel;
33

34 35 36 37 38 39 40 41
#
# BUG#6635 - test_if_skip_sort_order() thought it can skip filesort
# for fulltext searches too
#
alter table t1 add key m (message);
explain SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message;
SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message desc;

42
drop table t1;
43 44 45 46 47 48 49 50 51 52 53 54 55 56

#
# 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;

57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137
# BUG#11869
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 ;

--error 1109
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 1109
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;

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;

drop table t1,t2,t3;