fulltext.test 6.02 KB
Newer Older
1 2 3 4
#
# Test of fulltext index
#

5
drop table if exists t1,t2,t3;
6

7
CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
unknown's avatar
unknown committed
8 9 10 11 12
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');
13 14 15

# nl search
 
16 17 18
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");
19

20 21 22
# UNION of fulltext's
select * from t1 where MATCH(a,b) AGAINST ("collections") UNION ALL select * from t1 where MATCH(a,b) AGAINST ("indexes");

23 24
# boolean search

unknown's avatar
unknown committed
25 26 27 28 29 30 31
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);
unknown's avatar
unknown committed
32
select * from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
33 34
select *, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
select *, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;
35

unknown's avatar
unknown committed
36 37
select * from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE);

38
select * from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE);
unknown's avatar
unknown committed
39
select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE);
unknown's avatar
unknown committed
40 41 42
select * from t1 where MATCH a,b AGAINST ('"text search"  "now support"' IN BOOLEAN MODE);
select * from t1 where MATCH a,b AGAINST ('"text search" -"now support"' IN BOOLEAN MODE);
select * from t1 where MATCH a,b AGAINST ('"text search" +"now support"' IN BOOLEAN MODE);
unknown's avatar
unknown committed
43
select * from t1 where MATCH a,b AGAINST ('"text i"' IN BOOLEAN MODE);
unknown's avatar
unknown committed
44

45 46
# boolean w/o index:

unknown's avatar
unknown committed
47 48
select * from t1 where MATCH a AGAINST ("search" IN BOOLEAN MODE);
select * from t1 where MATCH b AGAINST ("sear*" IN BOOLEAN MODE);
49 50 51

#update/delete with fulltext index

52
delete from t1 where a like "MySQL%";
53 54 55
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;
56
drop table t1;
57 58 59 60 61 62 63 64 65 66

#
# Check bug reported by Matthias Urlichs
#

CREATE TABLE t1 (
  id int(11),
  ticket int(11),
  KEY ti (id),
  KEY tit (ticket)
67
);
68 69 70 71 72 73
INSERT INTO t1 VALUES (2,3),(1,2);

CREATE TABLE t2 (
  ticket int(11),
  inhalt text,
  KEY tig (ticket),
74 75
  fulltext index tix (inhalt)
);
76 77
INSERT INTO t2 VALUES (1,'foo'),(2,'bar'),(3,'foobar');

78 79 80
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');
81 82

# In the following query MySQL didn't use the fulltext index
83 84 85
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');
86 87

INSERT INTO t1 VALUES (3,3);
unknown's avatar
unknown committed
88 89 90 91
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');
92 93 94 95 96

# Check that we get 'fulltext' index in SHOW CREATE

show keys from t2;
show create table t2;
unknown's avatar
unknown committed
97 98 99

# check for bug reported by Stephan Skusa

100 101
select * from t2 where MATCH inhalt AGAINST (NULL);

unknown's avatar
unknown committed
102 103 104 105 106
# 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');

107 108 109 110 111 112 113 114 115 116 117 118
#
# check of fulltext errors
#

CREATE TABLE t3 (
  ticket int(11),
  inhalt text,
  KEY tig (ticket),
  fulltext index tix (inhalt)
);

--error 1210
119 120 121
select * from t2 where MATCH inhalt AGAINST (t2.inhalt);
--error 1191
select * from t2 where MATCH ticket AGAINST ('foobar');
122
--error 1210
123
select * from t2,t3 where MATCH (t2.inhalt,t3.inhalt) AGAINST ('foobar');
124 125

drop table t1,t2,t3;
unknown's avatar
unknown committed
126 127

#
unknown's avatar
unknown committed
128
# three more bugtests
unknown's avatar
unknown committed
129 130 131 132 133 134 135 136 137 138 139
#

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');
unknown's avatar
unknown committed
140
select * from t1 where match title against ('test' in boolean mode);
unknown's avatar
unknown committed
141 142 143 144
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;
145
update t1 set title=NULL where id=1;
unknown's avatar
unknown committed
146 147 148 149 150 151 152 153 154

drop table t1;

# one more bug - const_table related

CREATE TABLE t1 (a int(11), b text, FULLTEXT KEY (b)) TYPE=MyISAM;
insert into t1 values (1,"I wonder why the fulltext index doesnt work?");
SELECT * from t1 where MATCH (b) AGAINST ('apples');

unknown's avatar
unknown committed
155 156 157
insert into t1 values (2,"fullaaa fullzzz");
select * from t1 where match b against ('full*' in boolean mode);

unknown's avatar
unknown committed
158
drop table t1;
159 160 161 162
CREATE TABLE t1 ( id int(11) NOT NULL auto_increment primary key, mytext text NOT NULL, FULLTEXT KEY mytext (mytext)) TYPE=MyISAM;
INSERT INTO t1 VALUES (1,'my small mouse'),(2,'la-la-la'),(3,'It is so funny'),(4,'MySQL Tutorial');
select 8 from t1;
drop table t1;
unknown's avatar
unknown committed
163

unknown's avatar
unknown committed
164 165 166 167 168 169 170 171 172 173 174 175
#
# Check bug reported by Julian Ladisch
# ERROR 1030: Got error 127 from table handler
#

drop table if exists t1;
create table t1 (a text, fulltext key (a));
insert into t1 values ('aaaa');
repair table t1;
select * from t1 where match (a) against ('aaaa');
drop table t1;

176 177 178 179 180 181 182 183 184 185 186 187 188
#
# bug 283 by jocelyn fournier <joc@presence-pc.com>
# FULLTEXT index on a TEXT filed converted to a CHAR field doesn't work anymore
# 

drop table if exists t1;
create table t1 ( ref_mag text not null, fulltext (ref_mag));
insert into t1 values ('test');
select ref_mag from t1 where match ref_mag against ('+test' in boolean mode);
alter table t1 change ref_mag ref_mag char (255) not null;
select ref_mag from t1 where match ref_mag against ('+test' in boolean mode);
drop table t1;