fulltext.result 25.3 KB
Newer Older
unknown's avatar
unknown committed
1 2 3 4 5 6 7
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');
unknown's avatar
unknown committed
8 9
SHOW INDEX FROM t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
unknown's avatar
unknown committed
10 11
t1	1	a	1	a	NULL	NULL	NULL	NULL	YES	FULLTEXT	
t1	1	a	2	b	NULL	NULL	NULL	NULL	YES	FULLTEXT	
unknown's avatar
unknown committed
12
select * from t1 where MATCH(a,b) AGAINST ("collections");
13 14 15
a	b
Only MyISAM tables	support collections
Full-text indexes	are called collections
16
explain extended select * from t1 where MATCH(a,b) AGAINST ("collections");
17 18
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	100.00	Using where
19
Warnings:
20
Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections'))
unknown's avatar
unknown committed
21
select * from t1 where MATCH(a,b) AGAINST ("indexes");
22 23
a	b
Full-text indexes	are called collections
unknown's avatar
unknown committed
24
select * from t1 where MATCH(a,b) AGAINST ("indexes collections");
25 26 27
a	b
Full-text indexes	are called collections
Only MyISAM tables	support collections
28 29
select * from t1 where MATCH(a,b) AGAINST ("only");
a	b
30
select * from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION);
31 32 33
a	b
Only MyISAM tables	support collections
Full-text indexes	are called collections
34 35 36
MySQL has now support	for full-text search
select * from t1 where MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION);
a	b
37
Full-text indexes	are called collections
38 39 40
Only MyISAM tables	support collections
select * from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY EXPANSION);
a	b
41
Full-text indexes	are called collections
unknown's avatar
unknown committed
42
Only MyISAM tables	support collections
43
MySQL has now support	for full-text search
44 45 46 47 48 49 50 51 52
select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE);
a	b
Full-text indexes	are called collections
select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);
a	b
Full-text indexes	are called collections
Only MyISAM tables	support collections
select * from t1 where MATCH(a,b) AGAINST ("indexes" IN BOOLEAN MODE WITH QUERY EXPANSION);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WITH QUERY EXPANSION)' at line 1
unknown's avatar
unknown committed
53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82
explain select * from t1 where MATCH(a,b) AGAINST ("collections");
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	Using where
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	Using where
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	Using where
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	Using where
explain select * from t1 where 0<MATCH(a,b) AGAINST ("collections");
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	Using where
explain select * from t1 where 1<MATCH(a,b) AGAINST ("collections");
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	Using where
explain select * from t1 where 0<=MATCH(a,b) AGAINST ("collections");
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
explain select * from t1 where 1<=MATCH(a,b) AGAINST ("collections");
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	Using where
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0 and a like '%ll%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	Using where
unknown's avatar
unknown committed
83
select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE);
84 85
a	b
MySQL has now support	for full-text search
86
explain extended select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE);
87 88
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	100.00	Using where
89
Warnings:
90
Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('support -collections' in boolean mode))
unknown's avatar
unknown committed
91
select * from t1 where MATCH(a,b) AGAINST("support  collections" IN BOOLEAN MODE);
92 93 94 95
a	b
MySQL has now support	for full-text search
Full-text indexes	are called collections
Only MyISAM tables	support collections
unknown's avatar
unknown committed
96
select * from t1 where MATCH(a,b) AGAINST("support +collections" IN BOOLEAN MODE);
97 98 99
a	b
Full-text indexes	are called collections
Only MyISAM tables	support collections
unknown's avatar
unknown committed
100
select * from t1 where MATCH(a,b) AGAINST("sear*" IN BOOLEAN MODE);
101 102 103 104
a	b
MySQL has now support	for full-text search
Function MATCH ... AGAINST()	is used to do a search
Full-text search in MySQL	implements vector space model
unknown's avatar
unknown committed
105
select * from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
106 107
a	b
Only MyISAM tables	support collections
unknown's avatar
unknown committed
108
select * from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
109 110 111 112
a	b
MySQL has now support	for full-text search
Function MATCH ... AGAINST()	is used to do a search
Full-text search in MySQL	implements vector space model
unknown's avatar
unknown committed
113
select * from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
114 115 116
a	b
MySQL has now support	for full-text search
Full-text search in MySQL	implements vector space model
unknown's avatar
unknown committed
117 118 119
select * from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
a	b
Function MATCH ... AGAINST()	is used to do a search
120 121 122 123 124 125 126 127
select *, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
a	b	x
MySQL has now support	for full-text search	1
Full-text indexes	are called collections	1
Only MyISAM tables	support collections	2
Function MATCH ... AGAINST()	is used to do a search	0
Full-text search in MySQL	implements vector space model	0
select *, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;
unknown's avatar
unknown committed
128 129 130 131 132 133
a	b	x
MySQL has now support	for full-text search	1
Full-text indexes	are called collections	1
Only MyISAM tables	support collections	2
Function MATCH ... AGAINST()	is used to do a search	0
Full-text search in MySQL	implements vector space model	0
unknown's avatar
unknown committed
134 135 136
select * from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE);
a	b
Full-text indexes	are called collections
137 138
select * from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE);
a	b
unknown's avatar
unknown committed
139 140 141
select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
142 143
select * from t1 where MATCH a,b AGAINST ('"now   support"' IN BOOLEAN MODE);
a	b
unknown's avatar
unknown committed
144
MySQL has now support	for full-text search
unknown's avatar
unknown committed
145 146 147 148 149 150 151 152 153 154
select * from t1 where MATCH a,b AGAINST ('"text search"  "now support"' IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
Full-text search in MySQL	implements vector space model
select * from t1 where MATCH a,b AGAINST ('"text search" -"now support"' IN BOOLEAN MODE);
a	b
Full-text search in MySQL	implements vector space model
select * from t1 where MATCH a,b AGAINST ('"text search" +"now support"' IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
unknown's avatar
unknown committed
155 156
select * from t1 where MATCH a,b AGAINST ('"text i"' IN BOOLEAN MODE);
a	b
unknown's avatar
unknown committed
157 158
select * from t1 where MATCH a,b AGAINST ('"xt indexes"' IN BOOLEAN MODE);
a	b
unknown's avatar
unknown committed
159 160 161 162
select * from t1 where MATCH a,b AGAINST ('+(support collections) +foobar*' IN BOOLEAN MODE);
a	b
select * from t1 where MATCH a,b AGAINST ('+(+(support collections)) +foobar*' IN BOOLEAN MODE);
a	b
unknown's avatar
unknown committed
163 164 165
select * from t1 where MATCH a,b AGAINST ('+collections -supp* -foobar*' IN BOOLEAN MODE);
a	b
Full-text indexes	are called collections
unknown's avatar
unknown committed
166 167 168
select * from t1 where MATCH a,b AGAINST('"space model' IN BOOLEAN MODE);
a	b
Full-text search in MySQL	implements vector space model
unknown's avatar
unknown committed
169
select * from t1 where MATCH a AGAINST ("search" IN BOOLEAN MODE);
unknown's avatar
unknown committed
170 171
a	b
Full-text search in MySQL	implements vector space model
unknown's avatar
unknown committed
172 173 174 175
select * from t1 where MATCH b AGAINST ("sear*" IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
Function MATCH ... AGAINST()	is used to do a search
unknown's avatar
unknown committed
176 177 178 179 180
select * from t1 where MATCH(a,b) AGAINST ("collections") UNION ALL select * from t1 where MATCH(a,b) AGAINST ("indexes");
a	b
Only MyISAM tables	support collections
Full-text indexes	are called collections
Full-text indexes	are called collections
unknown's avatar
unknown committed
181
delete from t1 where a like "MySQL%";
182 183 184 185 186 187 188
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;
a	b
Only MyISAM tables	support collections
Function MATCH ... AGAINST()	is used to do a search
some test foobar	implements vector space model
unknown's avatar
unknown committed
189
drop table t1;
unknown's avatar
unknown committed
190 191 192 193 194
create table t1 (a varchar(200) not null, fulltext (a));
insert t1 values ("aaa10 bbb20"), ("aaa20 bbb15"), ("aaa30 bbb10");
select * from t1 where match a against ("+aaa* +bbb*" in boolean mode);
a
aaa30 bbb10
195 196
aaa20 bbb15
aaa10 bbb20
unknown's avatar
unknown committed
197 198 199
select * from t1 where match a against ("+aaa* +bbb1*" in boolean mode);
a
aaa30 bbb10
200
aaa20 bbb15
unknown's avatar
unknown committed
201 202
select * from t1 where match a against ("+aaa* +ccc*" in boolean mode);
a
unknown's avatar
unknown committed
203 204 205
select * from t1 where match a against ("+aaa10 +(bbb*)" in boolean mode);
a
aaa10 bbb20
unknown's avatar
unknown committed
206 207 208
select * from t1 where match a against ("+(+aaa* +bbb1*)" in boolean mode);
a
aaa30 bbb10
209
aaa20 bbb15
unknown's avatar
unknown committed
210 211 212
select * from t1 where match a against ("(+aaa* +bbb1*)" in boolean mode);
a
aaa30 bbb10
213
aaa20 bbb15
unknown's avatar
unknown committed
214
drop table t1;
unknown's avatar
unknown committed
215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231
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');
232
id
unknown's avatar
unknown committed
233 234 235
select ticket2.id FROM t2 as ttxt,t2 INNER JOIN t1 as ticket2 ON
ticket2.id = t2.ticket
WHERE ticket2.id = ticket2.ticket and match(ttxt.inhalt) against ('foobar');
236
id
unknown's avatar
unknown committed
237
INSERT INTO t1 VALUES (3,3);
unknown's avatar
unknown committed
238 239 240
select ticket2.id FROM t2 as ttxt,t2
INNER JOIN t1 as ticket2 ON ticket2.id = t2.ticket
WHERE ticket2.id = ticket2.ticket and
unknown's avatar
unknown committed
241
match(ttxt.inhalt) against ('foobar');
242 243
id
3
unknown's avatar
unknown committed
244
show keys from t2;
245 246
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t2	1	tig	1	ticket	A	NULL	NULL	NULL	YES	BTREE	
unknown's avatar
unknown committed
247
t2	1	tix	1	inhalt	NULL	NULL	NULL	NULL	YES	FULLTEXT	
unknown's avatar
unknown committed
248
show create table t2;
249 250
Table	Create Table
t2	CREATE TABLE `t2` (
251
  `ticket` int(11) DEFAULT NULL,
252
  `inhalt` text,
253 254
  KEY `tig` (`ticket`),
  FULLTEXT KEY `tix` (`inhalt`)
unknown's avatar
unknown committed
255
) ENGINE=MyISAM DEFAULT CHARSET=latin1
unknown's avatar
unknown committed
256
select * from t2 where MATCH inhalt AGAINST (NULL);
257
ticket	inhalt
unknown's avatar
unknown committed
258
select * from t2 where  MATCH inhalt AGAINST ('foobar');
unknown's avatar
unknown committed
259 260
ticket	inhalt
3	foobar
unknown's avatar
unknown committed
261
select * from t2 having MATCH inhalt AGAINST ('foobar');
unknown's avatar
unknown committed
262 263
ticket	inhalt
3	foobar
264 265 266 267 268 269
CREATE TABLE t3 (t int(11),i text,fulltext tix (t,i));
ERROR HY000: Column 't' cannot be part of FULLTEXT index
CREATE TABLE t3 (t int(11),i text,
j varchar(200) CHARACTER SET latin2,
fulltext tix (i,j));
ERROR HY000: Column 'j' cannot be part of FULLTEXT index
unknown's avatar
unknown committed
270 271 272 273 274 275 276
CREATE TABLE t3 (
ticket int(11),
inhalt text,
KEY tig (ticket),
fulltext index tix (inhalt)
);
select * from t2 where MATCH inhalt AGAINST (t2.inhalt);
277
ERROR HY000: Incorrect arguments to AGAINST
unknown's avatar
unknown committed
278
select * from t2 where MATCH ticket AGAINST ('foobar');
279
ERROR HY000: Can't find FULLTEXT index matching the column list
unknown's avatar
unknown committed
280
select * from t2,t3 where MATCH (t2.inhalt,t3.inhalt) AGAINST ('foobar');
281
ERROR HY000: Incorrect arguments to MATCH
unknown's avatar
unknown committed
282
drop table t1,t2,t3;
unknown's avatar
unknown committed
283 284 285 286
CREATE TABLE t1 (
id int(11)  auto_increment,
title varchar(100)  default '',
PRIMARY KEY  (id),
unknown's avatar
unknown committed
287
KEY ind5 (title)
unknown's avatar
unknown committed
288
) ENGINE=MyISAM;
unknown's avatar
unknown committed
289
CREATE FULLTEXT INDEX ft1 ON t1(title);
unknown's avatar
unknown committed
290
insert into t1 (title) values ('this is a test');
unknown's avatar
unknown committed
291 292 293
select * from t1 where match title against ('test' in boolean mode);
id	title
1	this is a test
unknown's avatar
unknown committed
294 295 296 297 298 299 300 301
update t1 set title='this is A test' where id=1;
check table t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
update t1 set title='this test once revealed a bug' where id=1;
select * from t1;
id	title
1	this test once revealed a bug
302
update t1 set title=NULL where id=1;
unknown's avatar
unknown committed
303
drop table t1;
unknown's avatar
unknown committed
304
CREATE TABLE t1 (a int(11), b text, FULLTEXT KEY (b)) ENGINE=MyISAM;
unknown's avatar
unknown committed
305 306 307
insert into t1 values (1,"I wonder why the fulltext index doesnt work?");
SELECT * from t1 where MATCH (b) AGAINST ('apples');
a	b
unknown's avatar
unknown committed
308 309 310 311 312
insert into t1 values (2,"fullaaa fullzzz");
select * from t1 where match b against ('full*' in boolean mode);
a	b
2	fullaaa fullzzz
1	I wonder why the fulltext index doesnt work?
unknown's avatar
unknown committed
313
drop table t1;
unknown's avatar
unknown committed
314
CREATE TABLE t1 ( id int(11) NOT NULL auto_increment primary key, mytext text NOT NULL, FULLTEXT KEY mytext (mytext)) ENGINE=MyISAM;
315 316 317 318 319 320 321 322
INSERT INTO t1 VALUES (1,'my small mouse'),(2,'la-la-la'),(3,'It is so funny'),(4,'MySQL Tutorial');
select 8 from t1;
8
8
8
8
8
drop table t1;
unknown's avatar
unknown committed
323 324 325 326 327 328 329 330
create table t1 (a text, fulltext key (a));
insert into t1 values ('aaaa');
repair table t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
select * from t1 where match (a) against ('aaaa');
a
drop table t1;
331 332 333 334 335 336 337 338 339 340
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);
ref_mag
test
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);
ref_mag
test
drop table t1;
unknown's avatar
unknown committed
341 342 343 344 345 346 347 348
create table t1 (t1_id int(11) primary key, name varchar(32));
insert into t1 values (1, 'data1');
insert into t1 values (2, 'data2');
create table t2 (t2_id int(11) primary key, t1_id int(11), name varchar(32));
insert into t2 values (1, 1, 'xxfoo');
insert into t2 values (2, 1, 'xxbar');
insert into t2 values (3, 1, 'xxbuz');
select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in boolean mode);
unknown's avatar
unknown committed
349 350
t1_id	name	t2_id	name
1	data1	1	xxfoo
351
select * from t2 where match name against ('*a*b*c*d*e*f*' in boolean mode);
352
t2_id	t1_id	name
unknown's avatar
unknown committed
353
drop table t1,t2;
354 355 356
create table t1 (a text, fulltext key (a));
insert into t1 select "xxxx yyyy zzzz";
drop table t1;
357 358 359 360 361 362
SET NAMES latin1;
CREATE TABLE t1 (t text character set utf8 not null, fulltext(t));
INSERT t1 VALUES ('Mit freundlichem Gr'), ('aus Osnabrck');
SET NAMES koi8r;
INSERT t1 VALUES ("  - "),(", !"),
(" , !"),("  !");
363 364 365 366 367 368
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('');
t	collation(t)
  - 	utf8_general_ci
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('*' IN BOOLEAN MODE);
t	collation(t)
  !	utf8_general_ci
369 370
SELECT * FROM t1 WHERE MATCH t AGAINST ('' IN BOOLEAN MODE);
t
371 372
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrck');
t	collation(t)
373
SET NAMES latin1;
374 375 376 377 378
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrck');
t	collation(t)
aus Osnabrck	utf8_general_ci
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck');
t	collation(t)
unknown's avatar
unknown committed
379 380 381
SELECT t, collation(t),FORMAT(MATCH t AGAINST ('Osnabruck'),6) FROM t1 WHERE MATCH t AGAINST ('Osnabruck');
t	collation(t)	FORMAT(MATCH t AGAINST ('Osnabruck'),6)
aus Osnabrck	utf8_general_ci	1.591140
382
alter table t1 modify t varchar(200) collate latin1_german2_ci not null;
383
Warnings:
384 385 386 387
Warning	1366	Incorrect string value: '\xD0\xAD\xD1\x82\xD0\xBE...' for column 't' at row 3
Warning	1366	Incorrect string value: '\xD0\x9E\xD1\x82\xD0\xBB...' for column 't' at row 4
Warning	1366	Incorrect string value: '\xD0\x9D\xD0\xB5 \xD0...' for column 't' at row 5
Warning	1366	Incorrect string value: '\xD0\xB8 \xD0\xB1\xD1...' for column 't' at row 6
388 389 390 391 392 393
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrck');
t	collation(t)
aus Osnabrck	latin1_german2_ci
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck');
t	collation(t)
aus Osnabrck	latin1_german2_ci
394
DROP TABLE t1;
395 396 397 398 399 400 401 402 403 404 405
CREATE TABLE t1 (s varchar(255), FULLTEXT (s)) DEFAULT CHARSET=utf8;
insert into t1 (s) values ('pra para para'),('para para para');
select * from t1 where match(s) against('para' in boolean mode);
s
pra para para
para para para
select * from t1 where match(s) against('par*' in boolean mode);
s
pra para para
para para para
DROP TABLE t1;
unknown's avatar
unknown committed
406 407 408 409 410 411 412 413 414
CREATE TABLE t1 (h text, FULLTEXT (h));
INSERT INTO t1 VALUES ('Jesses Hasse Ling and his syncopators of Swing');
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
select count(*) from t1;
count(*)
1
drop table t1;
unknown's avatar
unknown committed
415 416 417 418 419 420
CREATE TABLE t1 ( a TEXT, FULLTEXT (a) );
INSERT INTO t1 VALUES ('testing ft_nlq_find_relevance');
SELECT MATCH(a) AGAINST ('nosuchword') FROM t1;
MATCH(a) AGAINST ('nosuchword')
0
DROP TABLE t1;
unknown's avatar
unknown committed
421 422 423 424 425 426
create table t1 (a int primary key, b text, fulltext(b));
create table t2 (a int, b text);
insert t1 values (1, "aaaa"), (2, "bbbb");
insert t2 values (10, "aaaa"), (2, "cccc");
replace t1 select * from t2;
drop table t1, t2;
unknown's avatar
unknown committed
427 428 429 430 431 432 433
CREATE TABLE t1 (t VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci, FULLTEXT (t));
SET NAMES latin1;
INSERT INTO t1 VALUES('Mit freundlichem Gr aus Osnabrck');
SELECT COUNT(*) FROM t1 WHERE MATCH(t) AGAINST ('"osnabrck"' IN BOOLEAN MODE);
COUNT(*)
1
DROP TABLE t1;
unknown's avatar
unknown committed
434 435 436 437 438 439 440
CREATE TABLE t1 (a VARCHAR(30), FULLTEXT(a));
INSERT INTO t1 VALUES('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
SET myisam_repair_threads=2;
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
SET myisam_repair_threads=@@global.myisam_repair_threads;
441 442 443 444
INSERT INTO t1 VALUES('testword\'\'');
SELECT a FROM t1 WHERE MATCH a AGAINST('testword' IN BOOLEAN MODE);
a
testword''
unknown's avatar
unknown committed
445 446 447
SELECT a FROM t1 WHERE MATCH a AGAINST('testword\'\'' IN BOOLEAN MODE);
a
testword''
448 449 450 451
INSERT INTO t1 VALUES('test\'s');
SELECT a FROM t1 WHERE MATCH a AGAINST('test' IN BOOLEAN MODE);
a
test's
unknown's avatar
unknown committed
452
DROP TABLE t1;
453 454 455 456
CREATE TABLE t1 (a VARCHAR(10000), FULLTEXT(a));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
457
  `a` varchar(10000) DEFAULT NULL,
458 459 460
  FULLTEXT KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
461 462
CREATE TABLE t1 (a TEXT, FULLTEXT KEY(a));
INSERT INTO t1 VALUES('test'),('test1'),('test');
463
PREPARE stmt from "SELECT a, FORMAT(MATCH(a) AGAINST('test1 test'),6) FROM t1 WHERE MATCH(a) AGAINST('test1 test')";
464
EXECUTE stmt;
465 466
a	FORMAT(MATCH(a) AGAINST('test1 test'),6)
test1	0.685267
467
EXECUTE stmt;
468 469
a	FORMAT(MATCH(a) AGAINST('test1 test'),6)
test1	0.685267
470 471
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
472 473 474 475 476 477 478
CREATE TABLE t1 (a VARCHAR(255), FULLTEXT(a));
SELECT * FROM t1 IGNORE INDEX(a) WHERE MATCH(a) AGAINST('test');
a
ALTER TABLE t1 DISABLE KEYS;
SELECT * FROM t1 WHERE MATCH(a) AGAINST('test');
ERROR HY000: Can't find FULLTEXT index matching the column list
DROP TABLE t1;
unknown's avatar
unknown committed
479 480 481 482 483 484
CREATE TABLE t1(a TEXT);
INSERT INTO t1 VALUES(' aaaaa aaaa');
SELECT * FROM t1 WHERE MATCH(a) AGAINST ('"aaaa"' IN BOOLEAN MODE);
a
 aaaaa aaaa
DROP TABLE t1;
485 486 487 488 489
CREATE TABLE t1(a VARCHAR(20), FULLTEXT(a));
INSERT INTO t1 VALUES('Offside'),('City Of God');
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city of*' IN BOOLEAN MODE);
a
City Of God
490 491 492 493
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city (of*)' IN BOOLEAN MODE);
a
City Of God
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city* of*' IN BOOLEAN MODE);
494 495
a
City Of God
496
DROP TABLE t1;
497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524
CREATE TABLE t1 (a VARCHAR(255), b INT, FULLTEXT(a), KEY(b));
INSERT INTO t1 VALUES('test', 1),('test', 1),('test', 1),('test', 1),
('test', 1),('test', 2),('test', 3),('test', 4);
EXPLAIN SELECT * FROM t1
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	b,a	a	0		1	Using where
EXPLAIN SELECT * FROM t1 USE INDEX(a)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	Using where
EXPLAIN SELECT * FROM t1 FORCE INDEX(a)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	Using where
EXPLAIN SELECT * FROM t1 IGNORE INDEX(a)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	b	b	5	const	4	Using where
EXPLAIN SELECT * FROM t1 USE INDEX(b)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	b	b	5	const	4	Using where
EXPLAIN SELECT * FROM t1 FORCE INDEX(b)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	b	b	5	const	4	Using where
DROP TABLE t1;
525 526 527 528 529 530 531 532 533
CREATE TABLE t1(a CHAR(10));
INSERT INTO t1 VALUES('aaa15');
SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) FROM t1;
MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE)
1
SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) FROM t1;
MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE)
2
DROP TABLE t1;
534 535 536 537
CREATE TABLE t1(a TEXT);
SELECT GROUP_CONCAT(a) AS st FROM t1 HAVING MATCH(st) AGAINST('test' IN BOOLEAN MODE);
ERROR HY000: Incorrect arguments to AGAINST
DROP TABLE t1;
538 539 540 541 542 543 544 545
CREATE TABLE t1(a VARCHAR(64), FULLTEXT(a));
INSERT INTO t1 VALUES('awrd bwrd cwrd'),('awrd bwrd cwrd'),('awrd bwrd cwrd');
SELECT * FROM t1 WHERE MATCH(a) AGAINST('+awrd bwrd* +cwrd*' IN BOOLEAN MODE);
a
awrd bwrd cwrd
awrd bwrd cwrd
awrd bwrd cwrd
DROP TABLE t1;
546 547 548 549 550 551 552 553
CREATE TABLE t1 (col text, FULLTEXT KEY full_text (col));
PREPARE s FROM 
"SELECT MATCH (col) AGAINST('findme') FROM t1 ORDER BY MATCH (col) AGAINST('findme')"
  ;
EXECUTE s;
MATCH (col) AGAINST('findme')
DEALLOCATE PREPARE s;
DROP TABLE t1;
554
#
555 556 557 558 559 560 561 562 563 564 565 566 567
# Bug #49250 : spatial btree index corruption and crash
# Part two : fulltext syntax check
#
CREATE TABLE t1(col1 TEXT,
FULLTEXT INDEX USING BTREE (col1));
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE (col1))' at line 2
CREATE TABLE t2(col1 TEXT);
CREATE FULLTEXT INDEX USING BTREE ON t2(col);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE ON t2(col)' at line 1
ALTER TABLE t2 ADD FULLTEXT INDEX USING BTREE (col1);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE (col1)' at line 1
DROP TABLE t2;
End of 5.0 tests
568
#
569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605
# Bug #47930: MATCH IN BOOLEAN MODE returns too many results 
#  inside subquery
#
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (1), (2);
CREATE TABLE t2 (a int, b2 char(10), FULLTEXT KEY b2 (b2));
INSERT INTO t2 VALUES (1,'Scargill');
CREATE TABLE t3 (a int, b int);
INSERT INTO t3 VALUES (1,1), (2,1);
# t2 should use full text index
EXPLAIN
SELECT count(*) FROM t1 WHERE 
not exists(
SELECT 1 FROM t2, t3
WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
2	DEPENDENT SUBQUERY	t2	fulltext	b2	b2	0		1	Using where
2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
# should return 0
SELECT count(*) FROM t1 WHERE 
not exists(
SELECT 1 FROM t2, t3
WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
);
count(*)
0
# should return 0
SELECT count(*) FROM t1 WHERE 
not exists(
SELECT 1 FROM t2 IGNORE INDEX (b2), t3
WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
);
count(*)
0
DROP TABLE t1,t2,t3;
606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625
CREATE TABLE t1 (a VARCHAR(4), FULLTEXT(a));
INSERT INTO t1 VALUES
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('awrd'),('cwrd'),
('awrd');
SELECT COUNT(*) FROM t1 WHERE MATCH(a) AGAINST("+awrd bwrd* +cwrd*" IN BOOLEAN MODE);
COUNT(*)
0
DROP TABLE t1;
626 627 628 629 630 631 632 633
#
# Bug #49445: Assertion failed: 0, file .\item_row.cc, line 55 with 
#   fulltext search and row op
#
CREATE TABLE t1(a CHAR(1),FULLTEXT(a));
SELECT 1 FROM t1 WHERE MATCH(a) AGAINST ('') AND ROW(a,a) > ROW(1,1);
1
DROP TABLE t1;
634 635 636 637 638 639 640 641 642 643 644 645 646
#
# BUG#51866 - crash with repair by sort and fulltext keys
#
CREATE TABLE t1(a CHAR(4), FULLTEXT(a));
INSERT INTO t1 VALUES('aaaa');
SET myisam_sort_buffer_size=4;
Warnings:
Warning	1292	Truncated incorrect myisam_sort_buffer_size value: '4'
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size;
DROP TABLE t1;
647
End of 5.1 tests