# # Test of fulltext index # drop table if exists t1,t2,t3; CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)); INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), ('Full-text indexes', 'are called collections'), ('Only MyISAM tables','support collections'), ('Function MATCH ... AGAINST()','is used to do a search'), ('Full-text search in MySQL', 'implements vector space model'); select * from t1 where MATCH(a,b) AGAINST ("collections"); select * from t1 where MATCH(a,b) AGAINST ("indexes"); select * from t1 where MATCH(a,b) AGAINST ("indexes collections"); select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE); select * from t1 where MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE); select * from t1 where MATCH(a,b) AGAINST("support +collections" IN BOOLEAN MODE); select * from t1 where MATCH(a,b) AGAINST("sear*" IN BOOLEAN MODE); select * from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE); select * from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE); select * from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE); select * from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE); select *, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1; delete from t1 where a like "MySQL%"; update t1 set a='some test foobar' where MATCH a,b AGAINST ('model'); delete from t1 where MATCH(a,b) AGAINST ("indexes"); select * from t1; drop table t1; # # Check bug reported by Matthias Urlichs # CREATE TABLE t1 ( id int(11), ticket int(11), KEY ti (id), KEY tit (ticket) ); INSERT INTO t1 VALUES (2,3),(1,2); CREATE TABLE t2 ( ticket int(11), inhalt text, KEY tig (ticket), fulltext index tix (inhalt) ); INSERT INTO t2 VALUES (1,'foo'),(2,'bar'),(3,'foobar'); select t1.id FROM t2 as ttxt,t1,t1 as ticket2 WHERE ticket2.id = ttxt.ticket AND t1.id = ticket2.ticket and match(ttxt.inhalt) against ('foobar'); # In the following query MySQL didn't use the fulltext index select t1.id FROM t2 as ttxt,t1 INNER JOIN t1 as ticket2 ON ticket2.id = ttxt.ticket WHERE t1.id = ticket2.ticket and match(ttxt.inhalt) against ('foobar'); INSERT INTO t1 VALUES (3,3); select t1.id FROM t2 as ttxt,t1 INNER JOIN t1 as ticket2 ON ticket2.id = ttxt.ticket WHERE t1.id = ticket2.ticket and match(ttxt.inhalt) against ('foobar'); # Check that we get 'fulltext' index in SHOW CREATE show keys from t2; show create table t2; # check for bug reported by Stephan Skusa select * from t2 where MATCH inhalt AGAINST (NULL); # MATCH in HAVING (pretty useless, but still it should work) select * from t2 where MATCH inhalt AGAINST ('foobar'); select * from t2 having MATCH inhalt AGAINST ('foobar'); # # check of fulltext errors # CREATE TABLE t3 ( ticket int(11), inhalt text, KEY tig (ticket), fulltext index tix (inhalt) ); --error 1210 select * from t2 where MATCH inhalt AGAINST (t2.inhalt); --error 1191 select * from t2 where MATCH ticket AGAINST ('foobar'); --error 1210 select * from t2,t3 where MATCH (t2.inhalt,t3.inhalt) AGAINST ('foobar'); drop table t1,t2,t3; # # two more bugtests # CREATE TABLE t1 ( id int(11) auto_increment, title varchar(100) default '', PRIMARY KEY (id), KEY ind5 (title), FULLTEXT KEY FT1 (title) ) TYPE=MyISAM; insert into t1 (title) values ('this is a test'); update t1 set title='this is A test' where id=1; check table t1; update t1 set title='this test once revealed a bug' where id=1; select * from t1;