|
1
by brian
clean slate |
1 |
#
|
2 |
# Test of fulltext index |
|
3 |
# bug reported by Tibor Simko <tibor.simko@cern.ch> |
|
4 |
#
|
|
5 |
||
6 |
--disable_warnings |
|
7 |
DROP TABLE IF EXISTS t1, t2; |
|
8 |
--enable_warnings |
|
9 |
||
10 |
CREATE TABLE t1 ( |
|
11 |
id mediumint unsigned NOT NULL auto_increment, |
|
12 |
tag char(6) NOT NULL default '', |
|
13 |
value text NOT NULL default '', |
|
14 |
PRIMARY KEY (id), |
|
15 |
KEY kt(tag), |
|
16 |
KEY kv(value(15)), |
|
17 |
FULLTEXT KEY kvf(value) |
|
18 |
) ENGINE=MyISAM; |
|
19 |
CREATE TABLE t2 ( |
|
20 |
id_t2 mediumint unsigned NOT NULL default '0', |
|
21 |
id_t1 mediumint unsigned NOT NULL default '0', |
|
22 |
field_number tinyint unsigned NOT NULL default '0', |
|
23 |
PRIMARY KEY (id_t2,id_t1,field_number), |
|
24 |
KEY id_t1(id_t1) |
|
25 |
) ENGINE=MyISAM; |
|
26 |
||
27 |
INSERT INTO t1 (tag,value) VALUES ('foo123','bar111'); |
|
28 |
INSERT INTO t1 (tag,value) VALUES ('foo123','bar222'); |
|
29 |
INSERT INTO t1 (tag,value) VALUES ('bar345','baz333 ar'); |
|
30 |
||
31 |
INSERT INTO t2 VALUES (2231626,64280,0); |
|
32 |
INSERT INTO t2 VALUES (2231626,64281,0); |
|
33 |
INSERT INTO t2 VALUES (12346, 3, 1); |
|
34 |
||
35 |
SELECT * FROM t1; SELECT * FROM t2; |
|
36 |
||
37 |
SELECT DISTINCT t2.id_t2 FROM t2, t1 |
|
38 |
WHERE MATCH (t1.value) AGAINST ('baz333') AND t1.id = t2.id_t1; |
|
39 |
||
40 |
SELECT DISTINCT t2.id_t2 FROM t2, t1 |
|
41 |
WHERE MATCH (t1.value) AGAINST ('baz333' IN BOOLEAN MODE) |
|
42 |
AND t1.id = t2.id_t1; |
|
43 |
||
44 |
DROP TABLE t1, t2; |
|
45 |
||
46 |
# End of 4.1 tests |