drop table if exists t1,t2,t3;
CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)) charset utf8mb4;
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;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
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	Visible	Expression
t1	1	a	1	a	NULL	5	NULL	NULL	YES	FULLTEXT			YES	NULL
t1	1	a	2	b	NULL	5	NULL	NULL	YES	FULLTEXT			YES	NULL
EXPLAIN FORMAT=tree select * from t1 where MATCH(a,b) AGAINST ("collections");
EXPLAIN
-> Filter: (match t1.a,t1.b against ('collections'))  (cost=0.35 rows=1)
    -> Full-text index search on t1 using a (a='collections')  (cost=0.35 rows=1)

select * from t1 where MATCH(a,b) AGAINST ("collections");
a	b
Full-text indexes	are called collections
Only MyISAM tables	support collections
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
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'))
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
select * from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION);
a	b
Full-text indexes	are called collections
MySQL has now support	for full-text search
Only MyISAM tables	support collections
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
select * from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY EXPANSION);
a	b
Full-text indexes	are called collections
MySQL has now support	for full-text search
Only MyISAM tables	support collections
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
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
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
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
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	20.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
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
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
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	20.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
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
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 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
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 ('support -collections' in boolean mode))
select * from t1 where MATCH(a,b) AGAINST("support  collections" IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
Full-text indexes	are called collections
Only MyISAM tables	support collections
select * from t1 where MATCH(a,b) AGAINST("support +collections" IN BOOLEAN MODE);
a	b
Full-text indexes	are called collections
Only MyISAM tables	support 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
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("+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	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;
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 * 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
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
select * from t1 where MATCH a AGAINST ("search" IN BOOLEAN MODE);
a	b
Full-text search in MySQL	implements vector space model
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
select * from t1 where MATCH(a,b) AGAINST ("collections") UNION ALL select * from t1 where MATCH(a,b) AGAINST ("indexes");
a	b
Full-text indexes	are called collections
Full-text indexes	are called collections
Only MyISAM tables	support collections
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));
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)
) charset utf8mb4;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t1 VALUES (2,3),(1,2);
CREATE TABLE t2 (
ticket int(11),
inhalt text,
KEY tig (ticket),
fulltext index tix (inhalt)
) charset utf8mb4;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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');
id
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
INSERT INTO t1 VALUES (3,3);
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	Visible	Expression
t2	1	tig	1	ticket	A	NULL	NULL	NULL	YES	BTREE			YES	NULL
t2	1	tix	1	inhalt	NULL	NULL	NULL	NULL	YES	FULLTEXT			YES	NULL
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ticket` int DEFAULT NULL,
  `inhalt` text,
  KEY `tig` (`ticket`),
  FULLTEXT KEY `tix` (`inhalt`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
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)
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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=MyISAM;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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=MyISAM;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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=MyISAM;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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));
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;
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;
create table t1 (t1_id int(11) primary key, name varchar(32));
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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));
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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);
t1_id	name	t2_id	name
1	data1	1	xxfoo
select * from t2 where match name against ('*a*b*c*d*e*f*' in boolean mode);
t2_id	t1_id	name
drop table t1,t2;
create table t1 (a text, fulltext key (a));
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));
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
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)
  - 	utf8mb3_general_ci
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('*' IN BOOLEAN MODE);
t	collation(t)
  !	utf8mb3_general_ci
SELECT * FROM t1 WHERE MATCH t AGAINST ('' IN BOOLEAN MODE);
t
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrck');
t	collation(t)
SET NAMES latin1;
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrck');
t	collation(t)
aus Osnabrck	utf8mb3_general_ci
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck');
t	collation(t)
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	utf8mb3_general_ci	1.591140
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
alter table t1 modify t varchar(200) collate latin1_german2_ci not null;
Warnings:
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
SET sql_mode = default;
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
DROP TABLE t1;
CREATE TABLE t1 (s varchar(255), FULLTEXT (s)) DEFAULT CHARSET=utf8;
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
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;
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;
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;
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;
CREATE TABLE t1 (t VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci, FULLTEXT (t));
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
Warning	3778	'utf8mb3_unicode_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
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));
INSERT INTO t1 VALUES('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
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)) charset utf8mb4;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10000) DEFAULT NULL,
  FULLTEXT KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t1;
CREATE TABLE t1 (a TEXT, FULLTEXT KEY(a));
INSERT INTO t1 VALUES('test'),('test1'),('test');
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.685267
EXECUTE stmt;
a	FORMAT(MATCH(a) AGAINST('test1 test'),6)
test1	0.685267
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
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;
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;
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
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
City Of God
DROP TABLE t1;
create table t1(a text,b date,fulltext index(a))engine=myisam;
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));
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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	fulltext	b,a	a	0	const	1	50.00	Using where
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	12.50	Using where
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	12.50	Using where
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;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	b	b	5	const	4	12.50	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` IGNORE INDEX (`a`) where ((`test`.`t1`.`b` = 1) and (match `test`.`t1`.`a` against ('test' in boolean mode)))
EXPLAIN SELECT * FROM t1 USE INDEX(b)
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	ref	b	b	5	const	4	12.50	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` USE INDEX (`b`) where ((`test`.`t1`.`b` = 1) and (match `test`.`t1`.`a` against ('test' in boolean mode)))
EXPLAIN SELECT * FROM t1 FORCE INDEX(b)
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	ref	b	b	5	const	4	12.50	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` FORCE INDEX (`b`) where ((`test`.`t1`.`b` = 1) and (match `test`.`t1`.`a` against ('test' in boolean mode)))
DROP TABLE t1;
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;
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 MATCH
DROP TABLE t1;
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;
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;
#
# 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
#
# 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	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	2	100.00	NULL
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where; Not exists
1	SIMPLE	t3	NULL	ALL	NULL	NULL	NULL	NULL	2	100.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` anti join (`test`.`t2` join `test`.`t3`) on(((`test`.`t3`.`a` = `test`.`t1`.`a`) and (match `test`.`t2`.`b2` against ('scargill' in boolean mode)))) where true
# 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;
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;
#
# 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;
#
# 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;
#
# 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));
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
# Bug#21140111: Explain ... match against: Assertion failed: ret ...
CREATE TABLE z(a INTEGER) engine=innodb;
CREATE TABLE q(b TEXT CHARSET latin1, fulltext(b)) engine=innodb;
explain format=json SELECT 1 FROM q
WHERE (SELECT MATCH(b) AGAINST ('*') FROM z);
ERROR HY000: Incorrect arguments to MATCH
DROP TABLE z, q;
#
# Bug#26271244: ASSERT FAILS WHEN SHOW CREATE TABLE CALLED ON A FILE
# CREATED FOR FULLTEXT INDEX
#
# Create Innodb table with full text index
CREATE TABLE t1(id INT, c CHAR(100) , FULLTEXT KEY `msg_idx` (`c`))
ENGINE INNODB;
CREATE TABLE t2 (a int, b2 char(10), FULLTEXT KEY b2 (b2));
# Find name of first FTS index "table"
# SHOW CREATE TABLE on an FTS index table should give error
# (not crash). Query log and error message suppressed as the name of
# FTS index table is not stable.
Got one of the listed errors
# Msg_type Error ER_NO_SUCH_TABLE,ER_NO_SUCH_TABLE
# Msg_type Error ER_NO_SUCH_TABLE,ER_NO_SUCH_TABLE
Got one of the listed errors
Got one of the listed errors
Got one of the listed errors
Got one of the listed errors
Got one of the listed errors
DROP TABLE t1, t2;
#
# BUG#21625016: INNODB FULL TEXT CASE SENSITIVE NOT WORKING
#
CREATE TABLE t1(fld1 VARCHAR(10) COLLATE 'latin1_bin', FULLTEXT INDEX (fld1))
ENGINE=InnoDB;
INSERT INTO t1 VALUES ('abCD'),('ABCD');
# With the patch, case sensitive comparison is performed since
# binary collation is used.
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN BOOLEAN MODE);
fld1
abCD
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN BOOLEAN MODE);
fld1
ABCD
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN NATURAL LANGUAGE MODE);
fld1
abCD
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN NATURAL LANGUAGE MODE);
fld1
ABCD
# Since binary collation is not used, case insensitive comparison is
# performed.
ALTER TABLE t1 MODIFY fld1 VARCHAR(10) COLLATE 'latin1_general_cs';
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN BOOLEAN MODE);
fld1
abCD
ABCD
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN BOOLEAN MODE);
fld1
abCD
ABCD
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN NATURAL LANGUAGE MODE);
fld1
abCD
ABCD
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN NATURAL LANGUAGE MODE);
fld1
abCD
ABCD
# With the patch, case sensitive comparison is performed.
ALTER TABLE t1 MODIFY fld1 VARCHAR(10) COLLATE 'utf8mb4_bin';
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN BOOLEAN MODE);
fld1
abCD
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN BOOLEAN MODE);
fld1
ABCD
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN NATURAL LANGUAGE MODE);
fld1
abCD
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN NATURAL LANGUAGE MODE);
fld1
ABCD
# Test(using case insensitive collation) added for coverage
ALTER TABLE t1 MODIFY fld1 VARCHAR(10) COLLATE 'utf8mb4_general_ci';
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN BOOLEAN MODE);
fld1
abCD
ABCD
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN BOOLEAN MODE);
fld1
abCD
ABCD
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN NATURAL LANGUAGE MODE);
fld1
abCD
ABCD
SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN NATURAL LANGUAGE MODE);
fld1
abCD
ABCD
DROP TABLE t1;
#
# Bug#32611913: ASSERT FAILURE IN ITEM_FUNC_MATCH::EQ()
#
CREATE TABLE t(x VARCHAR(10), FULLTEXT KEY (x));
SELECT x FROM t GROUP BY x, MATCH(x) AGAINST ('abc')
HAVING MATCH(x) AGAINST ('abc');
x
DROP TABLE t;
#
# Bug#34311472: Assertion in val_real() failed in MySQL 8.0.29
#
CREATE TABLE t(x INT);
INSERT INTO t VALUES (1);
SELECT * FROM t WHERE MATCH(x) AGAINST('abc' IN BOOLEAN MODE) AND x = 1;
x
DROP TABLE t;