fulltext.result 26.6 KB
Newer Older
unknown's avatar
unknown committed
1 2 3 4 5 6 7 8 9
CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)) ENGINE = InnoDB;
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');
ANALYZE TABLE t1;
SHOW INDEX FROM t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
Sergei Golubchik's avatar
Sergei Golubchik 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 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
select * from t1 where MATCH(a,b) AGAINST ("collections");
a	b
Full-text indexes	are called collections
Only MyISAM tables	support collections
explain extended select * from t1 where MATCH(a,b) AGAINST ("collections");
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
Warnings:
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'))
select * from t1 where MATCH(a,b) AGAINST ("indexes");
a	b
Full-text indexes	are called collections
select * from t1 where MATCH(a,b) AGAINST ("indexes collections");
a	b
Full-text indexes	are called collections
Only MyISAM tables	support collections
select * from t1 where MATCH(a,b) AGAINST ("only");
a	b
Only MyISAM tables	support collections
select * from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION);
a	b
Only MyISAM tables	support collections
Full-text indexes	are called collections
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 ("indexes" WITH QUERY EXPANSION);
a	b
Full-text indexes	are called collections
Only MyISAM tables	support collections
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 ("indexes collections" WITH QUERY EXPANSION);
a	b
Only MyISAM tables	support collections
Full-text indexes	are called collections
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 ("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
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 ("indexes" IN BOOLEAN MODE WITH QUERY EXPANSION);
59
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITH QUERY EXPANSION)' at line 1
unknown's avatar
unknown committed
60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122
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
select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
explain extended select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE);
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
Warnings:
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))
select * from t1 where MATCH(a,b) AGAINST("support  collections" IN BOOLEAN MODE);
a	b
Only MyISAM tables	support collections
MySQL has now support	for full-text search
Full-text indexes	are called collections
select * from t1 where MATCH(a,b) AGAINST("support +collections" IN BOOLEAN MODE);
a	b
Only MyISAM tables	support collections
Full-text indexes	are called collections
select * from t1 where MATCH(a,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
Full-text search in MySQL	implements vector space model
select * from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
a	b
Only MyISAM tables	support collections
select * from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
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
select * from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
a	b
Full-text search in MySQL	implements vector space model
123
MySQL has now support	for full-text search
unknown's avatar
unknown committed
124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165
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
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	0.15835624933242798
Full-text indexes	are called collections	0.15835624933242798
Only MyISAM tables	support collections	0.31671249866485596
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;
a	b	x
MySQL has now support	for full-text search	0.15835624933242798
Full-text indexes	are called collections	0.15835624933242798
Only MyISAM tables	support collections	0.31671249866485596
Function MATCH ... AGAINST()	is used to do a search	0
Full-text search in MySQL	implements vector space model	0
select * from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE);
a	b
Full-text indexes	are called collections
select * from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE);
a	b
select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
select * from t1 where MATCH a,b AGAINST ('"now   support"' IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
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
select * from t1 where MATCH a,b AGAINST ('"text i"' IN BOOLEAN MODE);
a	b
select * from t1 where MATCH a,b AGAINST ('"xt indexes"' IN BOOLEAN MODE);
a	b
166
Full-text indexes	are called collections
unknown's avatar
unknown committed
167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203
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
select * from t1 where MATCH a,b AGAINST ('+collections -supp* -foobar*' IN BOOLEAN MODE);
a	b
Full-text indexes	are called collections
select * from t1 where MATCH a,b AGAINST('"space model' IN BOOLEAN MODE);
a	b
Full-text search in MySQL	implements vector space model
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;
a	b
Only MyISAM tables	support collections
Function MATCH ... AGAINST()	is used to do a search
some test foobar	implements vector space model
drop table t1;
create table t1 (a varchar(200) not null, fulltext (a)) engine = innodb;
insert t1 values ("aaa10 bbb20"), ("aaa20 bbb15"), ("aaa30 bbb10");
select * from t1 where match a against ("+aaa* +bbb*" in boolean mode);
a
aaa10 bbb20
aaa20 bbb15
aaa30 bbb10
select * from t1 where match a against ("+aaa* +bbb1*" in boolean mode);
a
aaa20 bbb15
aaa30 bbb10
select * from t1 where match a against ("+aaa* +ccc*" in boolean mode);
a
select * from t1 where match a against ("+aaa10 +(bbb*)" in boolean mode);
a
aaa10 bbb20
select * from t1 where match a against ("+(+aaa* +bbb1*)" in boolean mode);
a
204 205
aaa20 bbb15
aaa30 bbb10
unknown's avatar
unknown committed
206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236
select * from t1 where match a against ("(+aaa* +bbb1*)" in boolean mode);
a
aaa20 bbb15
aaa30 bbb10
drop table t1;
CREATE TABLE t1 (
id int(11),
ticket int(11),
KEY ti (id),
KEY tit (ticket)
) ENGINE = InnoDB;
INSERT INTO t1 VALUES (2,3),(1,2);
CREATE TABLE t2 (
ticket int(11),
inhalt text,
KEY tig (ticket),
fulltext index tix (inhalt)
) ENGINE = InnoDB;
INSERT INTO t2 VALUES (1,'foo'),(2,'bar'),(3,'foobar');
INSERT INTO t1 VALUES (3,3);
ANALYZE TABLE t1;
ANALYZE TABLE t2;
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');
id
3
show keys from t2;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t2	1	tig	1	ticket	A	3	NULL	NULL	YES	BTREE		
Sergei Golubchik's avatar
Sergei Golubchik committed
237
t2	1	tix	1	inhalt	NULL	NULL	NULL	NULL	YES	FULLTEXT		
unknown's avatar
unknown committed
238 239 240 241
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ticket` int(11) DEFAULT NULL,
242
  `inhalt` text DEFAULT NULL,
unknown's avatar
unknown committed
243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336
  KEY `tig` (`ticket`),
  FULLTEXT KEY `tix` (`inhalt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
select * from t2 where MATCH inhalt AGAINST (NULL);
ticket	inhalt
select * from t2 where  MATCH inhalt AGAINST ('foobar');
ticket	inhalt
3	foobar
select * from t2 having MATCH inhalt AGAINST ('foobar');
ticket	inhalt
3	foobar
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
CREATE TABLE t3 (
ticket int(11),
inhalt text,
KEY tig (ticket),
fulltext index tix (inhalt)
) ENGINE = InnoDB;
select * from t2 where MATCH inhalt AGAINST (t2.inhalt);
ERROR HY000: Incorrect arguments to AGAINST
select * from t2 where MATCH ticket AGAINST ('foobar');
ERROR HY000: Can't find FULLTEXT index matching the column list
select * from t2,t3 where MATCH (t2.inhalt,t3.inhalt) AGAINST ('foobar');
ERROR HY000: Incorrect arguments to MATCH
drop table t1,t2,t3;
CREATE TABLE t1 (
id int(11)  auto_increment,
title varchar(100)  default '',
PRIMARY KEY  (id),
KEY ind5 (title)
) ENGINE = InnoDB;
CREATE FULLTEXT INDEX ft1 ON t1(title);
insert into t1 (title) values ('this is a test');
select * from t1 where match title against ('test' in boolean mode);
id	title
1	this is a test
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
update t1 set title=NULL where id=1;
drop table t1;
CREATE TABLE t1 (a int(11), b text, FULLTEXT KEY (b)) ENGINE = InnoDB;
insert into t1 values (1,"I wonder why the fulltext index doesnt work?");
SELECT * from t1 where MATCH (b) AGAINST ('apples');
a	b
insert into t1 values (2,"fullaaa fullzzz");
select * from t1 where match b against ('full*' in boolean mode);
a	b
1	I wonder why the fulltext index doesnt work?
2	fullaaa fullzzz
drop table t1;
CREATE TABLE t1 ( id int(11) NOT NULL auto_increment primary key, mytext text NOT NULL, FULLTEXT KEY mytext (mytext)) ENGINE = InnoDB;
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;
create table t1 (a text, fulltext key (a)) ENGINE = InnoDB;
insert into t1 values ('aaaa');
select * from t1 where match (a) against ('aaaa');
a
aaaa
drop table t1;
create table t1 ( ref_mag text not null, fulltext (ref_mag)) ENGINE = InnoDB;
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;
create table t1 (t1_id int(11) primary key, name varchar(32)) ENGINE = InnoDB;
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)) ENGINE = InnoDB;
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);
337
ERROR HY000: Incorrect arguments to MATCH
unknown's avatar
unknown committed
338
select * from t2 where match name against ('*a*b*c*d*e*f*' in boolean mode);
339
ERROR HY000: Can't find FULLTEXT index matching the column list
unknown's avatar
unknown committed
340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397
drop table t1,t2;
create table t1 (a text, fulltext key (a)) ENGINE = InnoDB;
insert into t1 select "xxxx yyyy zzzz";
drop table t1;
SET NAMES latin1;
CREATE TABLE t1 (t text character set utf8 not null, fulltext(t)) ENGINE = InnoDB;
INSERT t1 VALUES ('Mit freundlichem Gr'), ('aus Osnabrck');
SET NAMES koi8r;
INSERT t1 VALUES ("  - "),(", !"),
(" , !"),("  !");
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('');
t	collation(t)
  - 	utf8_general_ci
DROP TABLE t1;
CREATE TABLE t1 (s varchar(255), FULLTEXT (s)) ENGINE = InnoDB 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
para para para
pra para para
select * from t1 where match(s) against('par*' in boolean mode);
s
para para para
pra para para
DROP TABLE t1;
CREATE TABLE t1 (h text, FULLTEXT (h)) ENGINE = InnoDB;
INSERT INTO t1 VALUES ('Jesses Hasse Ling and his syncopators of Swing');
select count(*) from t1;
count(*)
1
drop table t1;
CREATE TABLE t1 ( a TEXT, FULLTEXT (a) ) ENGINE = InnoDB;
INSERT INTO t1 VALUES ('testing ft_nlq_find_relevance');
SELECT MATCH(a) AGAINST ('nosuchword') FROM t1;
MATCH(a) AGAINST ('nosuchword')
0
DROP TABLE t1;
create table t1 (a int primary key, b text, fulltext(b)) ENGINE = InnoDB;
create table t2 (a int, b text) ENGINE = InnoDB;
insert t1 values (1, "aaaa"), (2, "bbbb");
insert t2 values (10, "aaaa"), (2, "cccc");
replace t1 select * from t2;
drop table t1, t2;
CREATE TABLE t1 (t VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci, FULLTEXT (t)) ENGINE = InnoDB;
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;
CREATE TABLE t1 (a VARCHAR(30), FULLTEXT(a)) ENGINE = InnoDB;
INSERT INTO t1 VALUES('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
INSERT INTO t1 VALUES('testword\'\'');
SELECT a FROM t1 WHERE MATCH a AGAINST('testword' IN BOOLEAN MODE);
a
testword''
SELECT a FROM t1 WHERE MATCH a AGAINST('testword\'\'' IN BOOLEAN MODE);
a
398
testword''
unknown's avatar
unknown committed
399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478
INSERT INTO t1 VALUES('test\'s');
SELECT a FROM t1 WHERE MATCH a AGAINST('test' IN BOOLEAN MODE);
a
test's
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(10000), FULLTEXT(a)) ENGINE = InnoDB;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10000) DEFAULT NULL,
  FULLTEXT KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t1;
CREATE TABLE t1 (a TEXT, FULLTEXT KEY(a)) ENGINE = InnoDB;
INSERT INTO t1 VALUES('test'),('test1'),('test');
ANALYZE TABLE t1;
PREPARE stmt from "SELECT a, FORMAT(MATCH(a) AGAINST('test1 test'),6) FROM t1 WHERE MATCH(a) AGAINST('test1 test')";
EXECUTE stmt;
a	FORMAT(MATCH(a) AGAINST('test1 test'),6)
test1	0.227645
test	0.031008
test	0.031008
EXECUTE stmt;
a	FORMAT(MATCH(a) AGAINST('test1 test'),6)
test1	0.227645
test	0.031008
test	0.031008
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(255), FULLTEXT(a)) ENGINE = InnoDB;
SELECT * FROM t1 IGNORE INDEX(a) WHERE MATCH(a) AGAINST('test');
a
SELECT * FROM t1 WHERE MATCH(a) AGAINST('test');
a
DROP TABLE t1;
CREATE TABLE t1(a TEXT, fulltext(a)) ENGINE = InnoDB;
INSERT INTO t1 VALUES(' aaaaa aaaa');
SELECT * FROM t1 WHERE MATCH(a) AGAINST ('"aaaa"' IN BOOLEAN MODE);
a
 aaaaa aaaa
DROP TABLE t1;
CREATE TABLE t1(a VARCHAR(20), FULLTEXT(a)) ENGINE = InnoDB;
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
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city (of*)' IN BOOLEAN MODE);
a
Offside
City Of God
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city* of*' IN BOOLEAN MODE);
a
City Of God
DROP TABLE t1;
create table t1(a text,b date,fulltext index(a)) ENGINE = InnoDB;
insert into t1 set a='water',b='2008-08-04';
select 1 from t1 where match(a) against ('water' in boolean mode) and b>='2008-08-01';
1
1
drop table t1;
show warnings;
Level	Code	Message
CREATE TABLE t1 (a VARCHAR(255), b INT, FULLTEXT(a), KEY(b)) ENGINE = InnoDB;
INSERT INTO t1 VALUES('test', 1),('test', 1),('test', 1),('test', 1),
('test', 1),('test', 2),('test', 3),('test', 4);
ANALYZE TABLE t1;
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;
479
ERROR HY000: Can't find FULLTEXT index matching the column list
unknown's avatar
unknown committed
480 481
EXPLAIN SELECT * FROM t1 USE INDEX(b)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
482
ERROR HY000: Can't find FULLTEXT index matching the column list
unknown's avatar
unknown committed
483 484
EXPLAIN SELECT * FROM t1 FORCE INDEX(b)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
485
ERROR HY000: Can't find FULLTEXT index matching the column list
unknown's avatar
unknown committed
486 487 488 489 490 491 492 493 494 495 496 497
DROP TABLE t1;
CREATE TABLE t1(a CHAR(10), fulltext(a)) ENGINE = InnoDB;
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)
0.000000001885928302414186
SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) FROM t1;
MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE)
0.000000003771856604828372
DROP TABLE t1;
CREATE TABLE t1(a TEXT) ENGINE = InnoDB;
SELECT GROUP_CONCAT(a) AS st FROM t1 HAVING MATCH(st) AGAINST('test' IN BOOLEAN MODE);
498
ERROR HY000: Incorrect arguments to MATCH
unknown's avatar
unknown committed
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 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548
DROP TABLE t1;
CREATE TABLE t1(a VARCHAR(64), FULLTEXT(a)) ENGINE = InnoDB;
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;
CREATE TABLE t1 (col text, FULLTEXT KEY full_text (col)) ENGINE = InnoDB;
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;
#
# 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 MariaDB server version for the right syntax to use near 'USING BTREE (col1))' at line 2
CREATE TABLE t2(col1 TEXT) ENGINE = InnoDB;
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 MariaDB 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 MariaDB server version for the right syntax to use near 'USING BTREE (col1)' at line 1
DROP TABLE t2;
End of 5.0 tests
#
# Bug #47930: MATCH IN BOOLEAN MODE returns too many results 
#  inside subquery
#
CREATE TABLE t1 (a int) ENGINE = InnoDB;
INSERT INTO t1 VALUES (1), (2);
CREATE TABLE t2 (a int, b2 char(10), FULLTEXT KEY b2 (b2)) ENGINE = InnoDB;
INSERT INTO t2 VALUES (1,'Scargill');
CREATE TABLE t3 (a int, b int) ENGINE = InnoDB;
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
549 550
2	MATERIALIZED	t2	fulltext	b2	b2	0		1	Using where
2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
unknown's avatar
unknown committed
551 552 553 554 555 556 557
# 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(*)
558
0
unknown's avatar
unknown committed
559 560 561 562 563
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)
);
564
ERROR HY000: Can't find FULLTEXT index matching the column list
unknown's avatar
unknown committed
565 566 567 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 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (a VARCHAR(4), FULLTEXT(a)) ENGINE = InnoDB;
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;
#
# 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)) ENGINE = InnoDB;
SELECT 1 FROM t1 WHERE MATCH(a) AGAINST ('') AND ROW(a,a) > ROW(1,1);
1
DROP TABLE t1;
#
# BUG#51866 - crash with repair by sort and fulltext keys
#
CREATE TABLE t1(a CHAR(4), FULLTEXT(a)) ENGINE = InnoDB;
INSERT INTO t1 VALUES('aaaa');
DROP TABLE t1;
#
# Bug#54484 explain + prepared statement: crash and Got error -1 from storage engine
#
CREATE TABLE t1(f1 VARCHAR(6) NOT NULL, FULLTEXT KEY(f1), UNIQUE(f1)) ENGINE = InnoDB;
INSERT INTO t1 VALUES ('test');
SELECT 1 FROM t1 WHERE 1 >
ALL((SELECT 1 FROM t1 JOIN t1 a
ON (MATCH(t1.f1) against (""))
WHERE t1.f1 GROUP BY t1.f1)) xor f1;
1
1
PREPARE stmt FROM
'SELECT 1 FROM t1 WHERE 1 >
 ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a
 ON (MATCH(t1.f1) against (""))
 WHERE t1.f1 GROUP BY t1.f1)) xor f1';
EXECUTE stmt;
1
1
EXECUTE stmt;
1
1
DEALLOCATE PREPARE stmt;
PREPARE stmt FROM
'SELECT 1 FROM t1 WHERE 1 >
 ALL((SELECT 1 FROM t1 JOIN t1 a
 ON (MATCH(t1.f1) against (""))
 WHERE t1.f1 GROUP BY t1.f1))';
EXECUTE stmt;
1
1
EXECUTE stmt;
1
1
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
End of 5.1 tests
637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656
CREATE TABLE z(a INTEGER) engine=innodb;
CREATE TABLE q(b TEXT CHARSET latin1, fulltext(b)) engine=innodb;
EXPLAIN SELECT 1 FROM q WHERE (SELECT MATCH(b) AGAINST ('*') FROM z);
ERROR 42000: syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'
SELECT 1 FROM q WHERE (SELECT MATCH(b) AGAINST ('*') FROM z);
ERROR 42000: syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'
EXPLAIN SELECT MATCH(b) AGAINST ('*') FROM z;
ERROR 42S22: Unknown column 'b' in 'field list'
SELECT MATCH(b) AGAINST ('*') FROM z;
ERROR 42S22: Unknown column 'b' in 'field list'
EXPLAIN SELECT MATCH(a) AGAINST ('*') FROM z;
ERROR HY000: Can't find FULLTEXT index matching the column list
SELECT MATCH(a) AGAINST ('*') FROM z;
ERROR HY000: Can't find FULLTEXT index matching the column list
EXPLAIN SELECT MATCH(b) AGAINST ('*') FROM q;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	q	ALL	NULL	NULL	NULL	NULL	1	
SELECT MATCH(b) AGAINST ('*') FROM q;
ERROR 42000: syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'
DROP TABLE z, q;
657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691
create table t (
FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY, t TEXT, FULLTEXT KEY (t)
) ENGINE=InnoDB;
INSERT INTO t values (1, 'foo bar'), (2, 'foo bar'), (3, 'foo');
SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE)
LIMIT 0;
FTS_DOC_ID	t
SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE)
LIMIT 1;
FTS_DOC_ID	t
1	foo bar
SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE)
LIMIT 2;
FTS_DOC_ID	t
1	foo bar
2	foo bar
SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE)
LIMIT 3;
FTS_DOC_ID	t
1	foo bar
2	foo bar
3	foo
SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE)
LIMIT 4;
FTS_DOC_ID	t
1	foo bar
2	foo bar
3	foo
SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE)
LIMIT 5;
FTS_DOC_ID	t
1	foo bar
2	foo bar
3	foo
DROP TABLE t;