|
1
by brian
clean slate |
1 |
DROP TABLE IF EXISTS t1,t2,t3;
|
2 |
CREATE TABLE t1 (
|
|
3 |
a INT AUTO_INCREMENT PRIMARY KEY,
|
|
4 |
message CHAR(20),
|
|
5 |
FULLTEXT(message)
|
|
6 |
) comment = 'original testcase by sroussey@network54.com';
|
|
7 |
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("testbug"),
|
|
8 |
("steve"),("is"),("cool"),("steve is cool");
|
|
9 |
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve');
|
|
10 |
a FORMAT(MATCH (message) AGAINST ('steve'),6)
|
|
11 |
4 0.905873
|
|
12 |
7 0.895690
|
|
13 |
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve');
|
|
14 |
a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
|
|
15 |
4 1
|
|
16 |
7 1
|
|
17 |
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
|
|
18 |
a FORMAT(MATCH (message) AGAINST ('steve'),6)
|
|
19 |
4 0.905873
|
|
20 |
7 0.895690
|
|
21 |
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
|
|
22 |
a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
|
|
23 |
4 1
|
|
24 |
7 1
|
|
25 |
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY a;
|
|
26 |
a FORMAT(MATCH (message) AGAINST ('steve'),6)
|
|
27 |
4 0.905873
|
|
28 |
7 0.895690
|
|
29 |
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY a;
|
|
30 |
a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
|
|
31 |
4 1
|
|
32 |
7 1
|
|
33 |
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;
|
|
34 |
a FORMAT(MATCH (message) AGAINST ('steve'),6)
|
|
35 |
7 0.895690
|
|
36 |
4 0.905873
|
|
37 |
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;
|
|
38 |
a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
|
|
39 |
7 1
|
|
40 |
4 1
|
|
41 |
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve') ORDER BY 1;
|
|
42 |
a FORMAT(MATCH (message) AGAINST ('steve'),6)
|
|
43 |
7 0.895690
|
|
44 |
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;
|
|
45 |
a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
|
|
46 |
7 1
|
|
47 |
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) as rel FROM t1 ORDER BY rel;
|
|
48 |
a rel
|
|
49 |
1 0.000000
|
|
50 |
2 0.000000
|
|
51 |
3 0.000000
|
|
52 |
5 0.000000
|
|
53 |
6 0.000000
|
|
54 |
7 0.895690
|
|
55 |
4 0.905873
|
|
56 |
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel;
|
|
57 |
a rel
|
|
58 |
1 0
|
|
59 |
2 0
|
|
60 |
3 0
|
|
61 |
5 0
|
|
62 |
6 0
|
|
63 |
4 1
|
|
64 |
7 1
|
|
65 |
alter table t1 add key m (message);
|
|
66 |
explain SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message;
|
|
67 |
id select_type table type possible_keys key key_len ref rows Extra
|
|
68 |
1 SIMPLE t1 fulltext message message 0 1 Using where; Using filesort
|
|
69 |
SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message desc;
|
|
70 |
message
|
|
71 |
steve is cool
|
|
72 |
steve
|
|
73 |
drop table t1;
|
|
74 |
CREATE TABLE t1 (
|
|
75 |
a INT AUTO_INCREMENT PRIMARY KEY,
|
|
76 |
message CHAR(20),
|
|
77 |
FULLTEXT(message)
|
|
78 |
);
|
|
79 |
INSERT INTO t1 (message) VALUES ("testbug"),("testbug foobar");
|
|
80 |
SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1;
|
|
81 |
a rel
|
|
82 |
1 1
|
|
83 |
2 2
|
|
84 |
SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel,a;
|
|
85 |
a rel
|
|
86 |
1 1
|
|
87 |
2 2
|
|
88 |
drop table t1;
|
|
89 |
CREATE TABLE t1 (
|
|
90 |
id int(11) NOT NULL auto_increment,
|
|
91 |
thread int(11) NOT NULL default '0',
|
|
92 |
beitrag longtext NOT NULL,
|
|
93 |
PRIMARY KEY (id),
|
|
94 |
KEY thread (thread),
|
|
95 |
FULLTEXT KEY beitrag (beitrag)
|
|
96 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7923 ;
|
|
97 |
CREATE TABLE t2 (
|
|
98 |
id int(11) NOT NULL auto_increment,
|
|
99 |
text varchar(100) NOT NULL default '',
|
|
100 |
PRIMARY KEY (id),
|
|
101 |
KEY text (text)
|
|
102 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=63 ;
|
|
103 |
CREATE TABLE t3 (
|
|
104 |
id int(11) NOT NULL auto_increment,
|
|
105 |
forum int(11) NOT NULL default '0',
|
|
106 |
betreff varchar(70) NOT NULL default '',
|
|
107 |
PRIMARY KEY (id),
|
|
108 |
KEY forum (forum),
|
|
109 |
FULLTEXT KEY betreff (betreff)
|
|
110 |
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=996 ;
|
|
111 |
select a.text, b.id, b.betreff
|
|
112 |
from
|
|
113 |
t2 a inner join t3 b on a.id = b.forum inner join
|
|
114 |
t1 c on b.id = c.thread
|
|
115 |
where
|
|
116 |
match(b.betreff) against ('+abc' in boolean mode)
|
|
117 |
group by a.text, b.id, b.betreff
|
|
118 |
union
|
|
119 |
select a.text, b.id, b.betreff
|
|
120 |
from
|
|
121 |
t2 a inner join t3 b on a.id = b.forum inner join
|
|
122 |
t1 c on b.id = c.thread
|
|
123 |
where
|
|
124 |
match(c.beitrag) against ('+abc' in boolean mode)
|
|
125 |
group by
|
|
126 |
a.text, b.id, b.betreff
|
|
127 |
order by
|
|
128 |
match(b.betreff) against ('+abc' in boolean mode) desc;
|
|
129 |
ERROR 42S22: Unknown column 'b.betreff' in 'order clause'
|
|
130 |
select a.text, b.id, b.betreff
|
|
131 |
from
|
|
132 |
t2 a inner join t3 b on a.id = b.forum inner join
|
|
133 |
t1 c on b.id = c.thread
|
|
134 |
where
|
|
135 |
match(b.betreff) against ('+abc' in boolean mode)
|
|
136 |
union
|
|
137 |
select a.text, b.id, b.betreff
|
|
138 |
from
|
|
139 |
t2 a inner join t3 b on a.id = b.forum inner join
|
|
140 |
t1 c on b.id = c.thread
|
|
141 |
where
|
|
142 |
match(c.beitrag) against ('+abc' in boolean mode)
|
|
143 |
order by
|
|
144 |
match(b.betreff) against ('+abc' in boolean mode) desc;
|
|
145 |
ERROR 42S22: Unknown column 'b.betreff' in 'order clause'
|
|
146 |
select a.text, b.id, b.betreff
|
|
147 |
from
|
|
148 |
t2 a inner join t3 b on a.id = b.forum inner join
|
|
149 |
t1 c on b.id = c.thread
|
|
150 |
where
|
|
151 |
match(b.betreff) against ('+abc' in boolean mode)
|
|
152 |
union
|
|
153 |
select a.text, b.id, b.betreff
|
|
154 |
from
|
|
155 |
t2 a inner join t3 b on a.id = b.forum inner join
|
|
156 |
t1 c on b.id = c.thread
|
|
157 |
where
|
|
158 |
match(c.beitrag) against ('+abc' in boolean mode)
|
|
159 |
order by
|
|
160 |
match(betreff) against ('+abc' in boolean mode) desc;
|
|
161 |
text id betreff
|
|
162 |
(select b.id, b.betreff from t3 b) union
|
|
163 |
(select b.id, b.betreff from t3 b)
|
|
164 |
order by match(betreff) against ('+abc' in boolean mode) desc;
|
|
165 |
id betreff
|
|
166 |
(select b.id, b.betreff from t3 b) union
|
|
167 |
(select b.id, b.betreff from t3 b)
|
|
168 |
order by match(betreff) against ('+abc') desc;
|
|
169 |
ERROR HY000: Can't find FULLTEXT index matching the column list
|
|
170 |
select distinct b.id, b.betreff from t3 b
|
|
171 |
order by match(betreff) against ('+abc' in boolean mode) desc;
|
|
172 |
id betreff
|
|
173 |
select b.id, b.betreff from t3 b group by b.id+1
|
|
174 |
order by match(betreff) against ('+abc' in boolean mode) desc;
|
|
175 |
id betreff
|
|
176 |
drop table t1,t2,t3;
|