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