1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
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;
|
