1#
2# Test of fulltext index
3# bug reported by  Tibor Simko <tibor.simko@cern.ch>
4#
5
6--disable_warnings
7DROP TABLE IF EXISTS t1, t2;
8--enable_warnings
9
10CREATE 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;
19CREATE 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
27INSERT INTO t1 (tag,value) VALUES ('foo123','bar111');
28INSERT INTO t1 (tag,value) VALUES ('foo123','bar222');
29INSERT INTO t1 (tag,value) VALUES ('bar345','baz333 ar');
30
31INSERT INTO t2 VALUES (2231626,64280,0);
32INSERT INTO t2 VALUES (2231626,64281,0);
33INSERT INTO t2 VALUES (12346, 3, 1);
34
35SELECT * FROM t1; SELECT * FROM t2;
36
37SELECT DISTINCT t2.id_t2 FROM t2, t1
38WHERE MATCH (t1.value) AGAINST ('baz333') AND t1.id = t2.id_t1;
39
40SELECT DISTINCT t2.id_t2 FROM t2, t1
41WHERE MATCH (t1.value) AGAINST ('baz333' IN BOOLEAN MODE)
42AND t1.id = t2.id_t1;
43
44DROP TABLE t1, t2;
45
46# End of 4.1 tests
47