1--disable_warnings
2DROP TABLE IF EXISTS t1,t2,t3;
3--enable_warnings
4
5CREATE TABLE t1 (
6  a INT AUTO_INCREMENT PRIMARY KEY,
7  message CHAR(20),
8  FULLTEXT(message)
9) comment = 'original testcase by sroussey@network54.com';
10INSERT INTO t1 (message) VALUES ("Testing"),("table"),("testbug"),
11        ("steve"),("is"),("cool"),("steve is cool");
12# basic MATCH
13SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve');
14SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve');
15SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
16SELECT 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)
19SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY a;
20SELECT 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
23SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE a in (2,7,4) and MATCH (message) AGAINST ('steve') ORDER BY a DESC;
24SELECT 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)
27SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve') ORDER BY 1;
28SELECT 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
31SELECT IF(a=7,'steve-is-cool',IF(a=4,'cool', 'other')), FORMAT(MATCH (message) AGAINST ('steve'),6) as rel FROM t1 ORDER BY rel;
32SELECT IF(a=7,'match',IF(a=4,'match', 'no-match')), MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel;
33
34#
35# BUG#6635 - test_if_skip_sort_order() thought it can skip filesort
36# for fulltext searches too
37#
38alter table t1 add key m (message);
39explain SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message;
40SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message desc;
41
42drop table t1;
43
44#
45# reused boolean scan bug
46#
47CREATE TABLE t1 (
48  a INT AUTO_INCREMENT PRIMARY KEY,
49  message CHAR(20),
50  FULLTEXT(message)
51);
52INSERT INTO t1 (message) VALUES ("testbug"),("testbug foobar");
53SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1;
54SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel,a;
55drop table t1;
56
57# BUG#11869
58CREATE TABLE t1 (
59  id int(11) NOT NULL auto_increment,
60  thread int(11) NOT NULL default '0',
61  beitrag longtext NOT NULL,
62  PRIMARY KEY  (id),
63  KEY thread (thread),
64  FULLTEXT KEY beitrag (beitrag)
65) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7923 ;
66
67CREATE TABLE t2 (
68  id int(11) NOT NULL auto_increment,
69  text varchar(100) NOT NULL default '',
70  PRIMARY KEY  (id),
71  KEY text (text)
72) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=63 ;
73
74CREATE TABLE t3 (
75  id int(11) NOT NULL auto_increment,
76  forum int(11) NOT NULL default '0',
77  betreff varchar(70) NOT NULL default '',
78  PRIMARY KEY  (id),
79  KEY forum (forum),
80  FULLTEXT KEY betreff (betreff)
81) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=996 ;
82
83--error ER_TABLENAME_NOT_ALLOWED_HERE
84select a.text, b.id, b.betreff
85from
86  t2 a inner join t3 b on a.id = b.forum inner join
87  t1 c on b.id = c.thread
88where
89  match(b.betreff) against ('+abc' in boolean mode)
90group by a.text, b.id, b.betreff
91union
92select a.text, b.id, b.betreff
93from
94  t2 a inner join t3 b on a.id = b.forum inner join
95  t1 c on b.id = c.thread
96where
97  match(c.beitrag) against ('+abc' in boolean mode)
98group by
99  a.text, b.id, b.betreff
100order by
101  match(b.betreff) against ('+abc' in boolean mode) desc;
102
103--error ER_TABLENAME_NOT_ALLOWED_HERE
104select a.text, b.id, b.betreff
105from
106  t2 a inner join t3 b on a.id = b.forum inner join
107  t1 c on b.id = c.thread
108where
109  match(b.betreff) against ('+abc' in boolean mode)
110union
111select a.text, b.id, b.betreff
112from
113  t2 a inner join t3 b on a.id = b.forum inner join
114  t1 c on b.id = c.thread
115where
116  match(c.beitrag) against ('+abc' in boolean mode)
117order by
118  match(b.betreff) against ('+abc' in boolean mode) desc;
119
120select a.text, b.id, b.betreff
121from
122  t2 a inner join t3 b on a.id = b.forum inner join
123  t1 c on b.id = c.thread
124where
125  match(b.betreff) against ('+abc' in boolean mode)
126union
127select a.text, b.id, b.betreff
128from
129  t2 a inner join t3 b on a.id = b.forum inner join
130  t1 c on b.id = c.thread
131where
132  match(c.beitrag) against ('+abc' in boolean mode)
133order by
134  match(betreff) against ('+abc' in boolean mode) desc;
135
136# BUG#11869 part2: used table type doesn't support FULLTEXT indexes error
137(select b.id, b.betreff from t3 b) union
138(select b.id, b.betreff from t3 b)
139order by match(betreff) against ('+abc' in boolean mode) desc;
140
141--error 1191
142(select b.id, b.betreff from t3 b) union
143(select b.id, b.betreff from t3 b)
144order by match(betreff) against ('+abc') desc;
145
146select distinct b.id, b.betreff from t3 b
147order by match(betreff) against ('+abc' in boolean mode) desc;
148
149select b.id, b.betreff from t3 b group by b.id+1
150order by match(betreff) against ('+abc' in boolean mode) desc;
151
152drop table t1,t2,t3;
153
154# End of 4.1 tests
155