Gecko [ www.jdcpatala.org ]

Name	Size	Permission
basic.result	12.32 KB	-rw-r--r--
bug_32831765.result	6.29 KB	-rw-r--r--
compatibility.result	9.81 KB	-rw-r--r--
compatibility_win.result	9.81 KB	-rw-r--r--
ddl.result	10.34 KB	-rw-r--r--
fic.result	8.01 KB	-rw-r--r--
foreign_key_check.result	808 B	-rw-r--r--
foreign_key_update.result	663 B	-rw-r--r--
fulltext.result	28.64 KB	-rw-r--r--
fulltext2.result	8.45 KB	-rw-r--r--
fulltext3.result	1.79 KB	-rw-r--r--
fulltext_cache.result	2.77 KB	-rw-r--r--
fulltext_distinct.result	1.45 KB	-rw-r--r--
fulltext_left_join.result	4.27 KB	-rw-r--r--
fulltext_misc.result	8.19 KB	-rw-r--r--
fulltext_multi.result	965 B	-rw-r--r--
fulltext_order_by.result	6.42 KB	-rw-r--r--
fulltext_table_evict.result	688 B	-rw-r--r--
fulltext_update.result	1.12 KB	-rw-r--r--
fulltext_var.result	1.33 KB	-rw-r--r--
index_table.result	6.84 KB	-rw-r--r--
innobase_drop_fts_index_table....	209 B	-rw-r--r--
large_records.result	9.04 KB	-rw-r--r--
limit_union.result	6 KB	-rw-r--r--
mecab_sjis.result	6.62 KB	-rw-r--r--
mecab_ujis.result	6.62 KB	-rw-r--r--
mecab_utf8.result	6.65 KB	-rw-r--r--
misc.result	62.97 KB	-rw-r--r--
misc_1.result	38.56 KB	-rw-r--r--
misc_debug.result	3.72 KB	-rw-r--r--
multiple_index.result	9.16 KB	-rw-r--r--
ngram.result	38.03 KB	-rw-r--r--
ngram_1.result	395.4 KB	-rw-r--r--
ngram_2.result	5.11 KB	-rw-r--r--
ngram_debug.result	8.63 KB	-rw-r--r--
opt.result	68.87 KB	-rw-r--r--
phrase.result	2.1 KB	-rw-r--r--
phrase_clear_no_match.result	2.68 KB	-rw-r--r--
plugin.result	7.52 KB	-rw-r--r--
plugin_1.result	14.8 KB	-rw-r--r--
plugin_debug.result	13.39 KB	-rw-r--r--
proximity.result	7.33 KB	-rw-r--r--
result_cache_limit.result	1.48 KB	-rw-r--r--
savepoint.result	8.69 KB	-rw-r--r--
stopword.result	31.83 KB	-rw-r--r--
stopword_charset.result	10.47 KB	-rw-r--r--
subexpr.result	3.88 KB	-rw-r--r--
sync.result	5.56 KB	-rw-r--r--
sync_block.result	2.64 KB	-rw-r--r--
sync_ddl.result	4.61 KB	-rw-r--r--
tablespace_location.result	43.89 KB	-rw-r--r--
tablespace_location_error.resu...	16.93 KB	-rw-r--r--
transaction.result	42.15 KB	-rw-r--r--
truncate.result	1.22 KB	-rw-r--r--

Code Editor : fulltext.result

drop table if exists t1,t2,t3;
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
t1	1	a	1	a	NULL	5	NULL	NULL	YES	FULLTEXT		
t1	1	a	2	b	NULL	5	NULL	NULL	YES	FULLTEXT		
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	fulltext	a	a	0	const	1	100.00	Using where; Ft_hints: sorted
Warnings:
Warning	1681	'EXTENDED' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ 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);
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 'QUERY EXPANSION)' at line 1
explain select * from t1 where MATCH(a,b) AGAINST ("collections");
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	fulltext	a	a	0	const	1	100.00	Using where; Ft_hints: sorted
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections'))
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	fulltext	a	a	0	const	1	100.00	Using where; Ft_hints: sorted, rank > 0
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')) > 0)
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	fulltext	a	a	0	const	1	100.00	Using where; Ft_hints: sorted, rank > 1
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')) > 1)
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=0;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')) >= 0)
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	fulltext	a	a	0	const	1	100.00	Using where; Ft_hints: sorted, rank >= 1
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')) >= 1)
explain select * from t1 where 0<MATCH(a,b) AGAINST ("collections");
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	fulltext	a	a	0	const	1	100.00	Using where; Ft_hints: sorted, rank > 0
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (0 < (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')))
explain select * from t1 where 1<MATCH(a,b) AGAINST ("collections");
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	fulltext	a	a	0	const	1	100.00	Using where; Ft_hints: sorted, rank > 1
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (1 < (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')))
explain select * from t1 where 0<=MATCH(a,b) AGAINST ("collections");
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (0 <= (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')))
explain select * from t1 where 1<=MATCH(a,b) AGAINST ("collections");
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	fulltext	a	a	0	const	1	100.00	Using where; Ft_hints: sorted, rank >= 1
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (1 <= (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')))
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0 and a like '%ll%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	fulltext	a	a	0	const	1	100.00	Using where; Ft_hints: sorted, rank > 0
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (((match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')) > 0) and (`test`.`t1`.`a` like '%ll%'))
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	fulltext	a	a	0	const	1	100.00	Using where; Ft_hints: no_ranking
Warnings:
Warning	1681	'EXTENDED' is deprecated and will be removed in a future release.
Note	1003	/* select#1 */ 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
MySQL has now support	for full-text search
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
Full-text indexes	are called collections
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
aaa20 bbb15
aaa30 bbb10
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		
t2	1	tix	1	inhalt	NULL	3	NULL	NULL	YES	FULLTEXT		
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ticket` int(11) DEFAULT NULL,
  `inhalt` text,
  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);
Warnings:
Warning	124	InnoDB rebuilding table to add column FTS_DOC_ID
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);
ERROR HY000: Incorrect arguments to MATCH
select * from t2 where match name against ('*a*b*c*d*e*f*' in boolean mode);
ERROR HY000: Can't find FULLTEXT index matching the column list
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 Osnabr�ck');
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 ('p�ra para para'),('para para para');
select * from t1 where match(s) against('para' in boolean mode);
s
para para para
p�ra para para
select * from t1 where match(s) against('par*' in boolean mode);
s
para para para
p�ra 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 Osnabr�ck');
SELECT COUNT(*) FROM t1 WHERE MATCH(t) AGAINST ('"osnabr�ck"' 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
testword''
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	fulltext	b,a	a	0	const	1	62.50	Using where; Ft_hints: no_ranking
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = 1) and (match `test`.`t1`.`a` against ('test' in boolean mode)))
EXPLAIN SELECT * FROM t1 USE INDEX(a)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	fulltext	a	a	0	const	1	25.00	Using where; Ft_hints: no_ranking
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX (`a`) where ((`test`.`t1`.`b` = 1) and (match `test`.`t1`.`a` against ('test' in boolean mode)))
EXPLAIN SELECT * FROM t1 FORCE INDEX(a)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	fulltext	a	a	0	const	1	25.00	Using where; Ft_hints: no_ranking
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` FORCE INDEX (`a`) where ((`test`.`t1`.`b` = 1) and (match `test`.`t1`.`a` against ('test' in boolean mode)))
EXPLAIN SELECT * FROM t1 IGNORE INDEX(a)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
ERROR HY000: Can't find FULLTEXT index matching the column list
EXPLAIN SELECT * FROM t1 USE INDEX(b)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
ERROR HY000: Can't find FULLTEXT index matching the column list
EXPLAIN SELECT * FROM t1 FORCE INDEX(b)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
ERROR HY000: Can't find FULLTEXT index matching the column list
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);
ERROR HY000: Incorrect arguments to MATCH
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 MySQL 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 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
#
# 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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
2	DEPENDENT SUBQUERY	t2	NULL	fulltext	b2	b2	0	const	1	100.00	Using where; Ft_hints: no_ranking
2	DEPENDENT SUBQUERY	t3	NULL	ALL	NULL	NULL	NULL	NULL	2	50.00	Using where
Warnings:
Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (not(exists(/* select#2 */ select 1 from `test`.`t2` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`a`) and (match `test`.`t2`.`b2` against ('scargill' in boolean mode))))))
# 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(*)
1
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)
);
ERROR HY000: Can't find FULLTEXT index matching the column list
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

${this.title}

Code Editor : fulltext.result