#
# Test of fulltext index
#

--source include/have_innodb.inc

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');
-- disable_result_log
ANALYZE TABLE t1;
-- enable_result_log
SHOW INDEX FROM t1;

# nl search

select * from t1 where MATCH(a,b) AGAINST ("collections");
explain extended select * from t1 where MATCH(a,b) AGAINST ("collections");
select * from t1 where MATCH(a,b) AGAINST ("indexes");
select * from t1 where MATCH(a,b) AGAINST ("indexes collections");
select * from t1 where MATCH(a,b) AGAINST ("only");

# query expansion

select * from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION);
select * from t1 where MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION);
select * from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY EXPANSION);

# IN NATURAL LANGUAGE MODE
select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE);
select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);
--error 1064
select * from t1 where MATCH(a,b) AGAINST ("indexes" IN BOOLEAN MODE WITH QUERY EXPANSION);

# add_ft_keys() tests

explain select * from t1 where MATCH(a,b) AGAINST ("collections");
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0;
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>1;
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=0;
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=1;
explain select * from t1 where 0<MATCH(a,b) AGAINST ("collections");
explain select * from t1 where 1<MATCH(a,b) AGAINST ("collections");
explain select * from t1 where 0<=MATCH(a,b) AGAINST ("collections");
explain select * from t1 where 1<=MATCH(a,b) AGAINST ("collections");
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0 and a like '%ll%';

# boolean search

select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE);
explain extended select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE);
select * from t1 where MATCH(a,b) AGAINST("support  collections" IN BOOLEAN MODE);
select * from t1 where MATCH(a,b) AGAINST("support +collections" IN BOOLEAN MODE);
select * from t1 where MATCH(a,b) AGAINST("sear*" IN BOOLEAN MODE);
select * from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
select * from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
select * from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
select * from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
select *, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
select *, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;

select * from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE);

select * from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE);
select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE);
select * from t1 where MATCH a,b AGAINST ('"now   support"' IN BOOLEAN MODE);
select * from t1 where MATCH a,b AGAINST ('"text search"  "now support"' IN BOOLEAN MODE);
select * from t1 where MATCH a,b AGAINST ('"text search" -"now support"' IN BOOLEAN MODE);
select * from t1 where MATCH a,b AGAINST ('"text search" +"now support"' IN BOOLEAN MODE);
select * from t1 where MATCH a,b AGAINST ('"text i"' IN BOOLEAN MODE);
select * from t1 where MATCH a,b AGAINST ('"xt indexes"' IN BOOLEAN MODE);

select * from t1 where MATCH a,b AGAINST ('+(support collections) +foobar*' IN BOOLEAN MODE);
select * from t1 where MATCH a,b AGAINST ('+(+(support collections)) +foobar*' IN BOOLEAN MODE);
select * from t1 where MATCH a,b AGAINST ('+collections -supp* -foobar*' IN BOOLEAN MODE);

# bug#2708, bug#3870 crash

select * from t1 where MATCH a,b AGAINST('"space model' IN BOOLEAN MODE);
 
# INNODB_FTS: No FTS index on column a or b. InnoDB do now support
# FT type search when there is no FTS INDEX

# select * from t1 where MATCH a AGAINST ("search" IN BOOLEAN MODE);
# select * from t1 where MATCH b AGAINST ("sear*" IN BOOLEAN MODE);

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

#update/delete with fulltext index

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;
drop table t1;

#
# why to scan strings for trunc*
#

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);
select * from t1 where match a against ("+aaa* +bbb1*" in boolean mode);
select * from t1 where match a against ("+aaa* +ccc*" in boolean mode);
select * from t1 where match a against ("+aaa10 +(bbb*)" in boolean mode);

# FTS_INNODB: INVESTIGATE
select * from t1 where match a against ("+(+aaa* +bbb1*)" in boolean mode);
select * from t1 where match a against ("(+aaa* +bbb1*)" in boolean mode);
drop table t1;

#
# Check bug reported by Matthias Urlichs
#

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');

#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');

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

INSERT INTO t1 VALUES (3,3);
-- disable_result_log
ANALYZE TABLE t1;
ANALYZE TABLE t2;
-- enable_result_log
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');

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

show keys from t2;
show create table t2;

# check for bug reported by Stephan Skusa

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

# MATCH in HAVING (pretty useless, but still it should work)

select * from t2 where  MATCH inhalt AGAINST ('foobar');

# INNODB_FTS: INVESTIGATE
select * from t2 having MATCH inhalt AGAINST ('foobar');

#
# check of fulltext errors
#

--error 1283
CREATE TABLE t3 (t int(11),i text,fulltext tix (t,i));
--error 1283
CREATE TABLE t3 (t int(11),i text,
                 j varchar(200) CHARACTER SET latin2,
                 fulltext tix (i,j));

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

--error 1210
select * from t2 where MATCH inhalt AGAINST (t2.inhalt);
--error 1191
select * from t2 where MATCH ticket AGAINST ('foobar');
--error 1210
select * from t2,t3 where MATCH (t2.inhalt,t3.inhalt) AGAINST ('foobar');

drop table t1,t2,t3;

#
# three more bugtests
#

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);
update t1 set title='this is A test' where id=1;

check table t1;
update t1 set title='this test once revealed a bug' where id=1;
select * from t1;
update t1 set title=NULL where id=1;

drop table t1;

# one more bug - const_table related

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');

insert into t1 values (2,"fullaaa fullzzz");
select * from t1 where match b against ('full*' in boolean mode);

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;
drop table t1;

#
# Check bug reported by Julian Ladisch
# ERROR 1030: Got error 127 from table handler
#

create table t1 (a text, fulltext key (a)) ENGINE = InnoDB;
insert into t1 values ('aaaa');

# INNODB_FTS: InnoDB do not support "repair" command
# repair table t1;
select * from t1 where match (a) against ('aaaa');
drop table t1;

#
# bug #283 by jocelyn fournier <joc@presence-pc.com>
# FULLTEXT index on a TEXT filed converted to a CHAR field doesn't work anymore
# 

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);
alter table t1 change ref_mag ref_mag char (255) not null;
select ref_mag from t1 where match ref_mag against ('+test' in boolean mode);
drop table t1;

#
# bug #942: JOIN
# NOTE: Not related to FTS, no FTS index created

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');
# INNODB_FTS: InnoDB do not support MATCH expressions with arguments from 
# different tables
--error ER_WRONG_ARGUMENTS
select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in boolean mode);

#
# Bug #7858: bug with many short (< ft_min_word_len) words in boolean search
#
# INNODB_FTS: Note there is no fulltext index on table. InnoDB do not support
# Fulltext search in such case
--error ER_FT_MATCHING_KEY_NOT_FOUND
select * from t2 where match name against ('*a*b*c*d*e*f*' in boolean mode);
drop table t1,t2;

#
# bug with repair-by-sort and incorrect records estimation
#

create table t1 (a text, fulltext key (a)) ENGINE = InnoDB;
insert into t1 select "xxxx yyyy zzzz";
drop table t1;

#
# UTF8
# INNODB_FTS: Charset Support (FIX)
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 ('');
#SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('*' IN BOOLEAN MODE);
#SELECT * FROM t1 WHERE MATCH t AGAINST ('' IN BOOLEAN MODE);
#SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrck');
#SET NAMES latin1;
#SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrck');
#SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck');
#SELECT t, collation(t),FORMAT(MATCH t AGAINST ('Osnabruck'),6) FROM t1 WHERE MATCH t AGAINST ('Osnabruck');
#alter table t1 modify t text character set latin1 collate latin1_german2_ci not null;
#alter table t1 modify t varchar(200) collate latin1_german2_ci not null;
#SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrck');
#SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck');
DROP TABLE t1;

#
# bug#3964
#

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);
select * from t1 where match(s) against('par*' in boolean mode);
DROP TABLE t1;

#
# icc -ip bug (ip = interprocedural optimization)
# bug#5528
#
CREATE TABLE t1 (h text, FULLTEXT (h)) ENGINE = InnoDB;
INSERT INTO t1 VALUES ('Jesses Hasse Ling and his syncopators of Swing');

# INNODB_FTS: InnoDB do not support "repair" command
# REPAIR TABLE t1;
select count(*) from t1;
drop table t1;

#
# testing out of bounds memory access in ft_nlq_find_relevance()
# (bug#8522); visible in valgrind.
#
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;
DROP TABLE t1;
#
# bug#6784
# mi_flush_bulk_insert (on dup key error in mi_write)
# was mangling info->dupp_key_pos
#

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;

#
# bug#8351
#
# INNODB_FTS: Charset Support
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);
DROP TABLE t1;

#
# BUG#11684 - repair crashes mysql when table has fulltext index
#

CREATE TABLE t1 (a VARCHAR(30), FULLTEXT(a)) ENGINE = InnoDB;
INSERT INTO t1 VALUES('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');

# INNODB_FTS: InnoDB do not support repair
#SET myisam_repair_threads=2;
#REPAIR TABLE t1;
#SET myisam_repair_threads=@@global.myisam_repair_threads;

#
# BUG#5686 - #1034 - Incorrect key file for table - only utf8
#
INSERT INTO t1 VALUES('testword\'\'');
SELECT a FROM t1 WHERE MATCH a AGAINST('testword' IN BOOLEAN MODE);
SELECT a FROM t1 WHERE MATCH a AGAINST('testword\'\'' IN BOOLEAN MODE);

#
# BUG#14194: Problem with fulltext boolean search and apostrophe
#
# INNODB_FTS: Add "apostrophe" support
INSERT INTO t1 VALUES('test\'s');
SELECT a FROM t1 WHERE MATCH a AGAINST('test' IN BOOLEAN MODE);
DROP TABLE t1;

#
# BUG#13835: max key length is 1000 bytes when trying to create
#            a fulltext index
#
CREATE TABLE t1 (a VARCHAR(10000), FULLTEXT(a)) ENGINE = InnoDB;
SHOW CREATE TABLE t1;
DROP TABLE t1;

#
# BUG#14496: Crash or strange results with prepared statement,
#            MATCH and FULLTEXT
#
CREATE TABLE t1 (a TEXT, FULLTEXT KEY(a)) ENGINE = InnoDB;
INSERT INTO t1 VALUES('test'),('test1'),('test');
-- disable_result_log
ANALYZE TABLE t1;
-- enable_result_log
PREPARE stmt from "SELECT a, FORMAT(MATCH(a) AGAINST('test1 test'),6) FROM t1 WHERE MATCH(a) AGAINST('test1 test')";

EXECUTE stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE t1;

#
# BUG#25951 - ignore/use index does not work with fulltext
#
CREATE TABLE t1 (a VARCHAR(255), FULLTEXT(a)) ENGINE = InnoDB;
SELECT * FROM t1 IGNORE INDEX(a) WHERE MATCH(a) AGAINST('test');
# INNODB_FTS: InnoDB do have have this option (disable keys)
# ALTER TABLE t1 DISABLE KEYS;
# --error 1191
SELECT * FROM t1 WHERE MATCH(a) AGAINST('test');
DROP TABLE t1;

#
# BUG#11392 - fulltext search bug
#
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);
DROP TABLE t1;

#
# BUG#29445 - match ... against () never returns
#
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);
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city (of*)' IN BOOLEAN MODE);
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city* of*' IN BOOLEAN MODE);
DROP TABLE t1;

# End of 4.1 tests

#
# bug#34374 - mysql generates incorrect warning
#
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';
drop table t1;
show warnings;

#
# BUG#38842 - Fix for 25951 seems incorrect
#
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);

-- disable_result_log
ANALYZE TABLE t1;
-- enable_result_log
EXPLAIN SELECT * FROM t1
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;

EXPLAIN SELECT * FROM t1 USE INDEX(a)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;

EXPLAIN SELECT * FROM t1 FORCE INDEX(a)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;

--error ER_FT_MATCHING_KEY_NOT_FOUND
EXPLAIN SELECT * FROM t1 IGNORE INDEX(a)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;

--error ER_FT_MATCHING_KEY_NOT_FOUND
EXPLAIN SELECT * FROM t1 USE INDEX(b)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;

--error ER_FT_MATCHING_KEY_NOT_FOUND
EXPLAIN SELECT * FROM t1 FORCE INDEX(b)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;

DROP TABLE t1;

#
# BUG#37245 - Full text search problem
#
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;
SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) FROM t1;
DROP TABLE t1;

#
# BUG#36737 - having + full text operator crashes mysql
#
CREATE TABLE t1(a TEXT) ENGINE = InnoDB;
--error ER_WRONG_ARGUMENTS
SELECT GROUP_CONCAT(a) AS st FROM t1 HAVING MATCH(st) AGAINST('test' IN BOOLEAN MODE);
DROP TABLE t1;

#
# BUG#42907 - Multi-term boolean fulltext query containing a single
#             quote fails in 5.1.x
#
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);
DROP TABLE t1;

#
# BUG#37740 Server crashes on execute statement with full text search and match against
#
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;
DEALLOCATE PREPARE s;
DROP TABLE t1;


--echo #
--echo # Bug #49250 : spatial btree index corruption and crash
--echo # Part two : fulltext syntax check
--echo #

--error ER_PARSE_ERROR
CREATE TABLE t1(col1 TEXT,
  FULLTEXT INDEX USING BTREE (col1));
CREATE TABLE t2(col1 TEXT) ENGINE = InnoDB;
--error ER_PARSE_ERROR
CREATE FULLTEXT INDEX USING BTREE ON t2(col);
--error ER_PARSE_ERROR
ALTER TABLE t2 ADD FULLTEXT INDEX USING BTREE (col1);

DROP TABLE t2;


--echo End of 5.0 tests


--echo #
--echo # Bug #47930: MATCH IN BOOLEAN MODE returns too many results 
--echo #  inside subquery
--echo #

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);

SELECT * FROM t2 where MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE);

--echo # 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)
  );

# INNODB_FTS: INVESTIGATE
--echo # 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)
  );

--error ER_FT_MATCHING_KEY_NOT_FOUND
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)
  );

DROP TABLE t1,t2,t3;

#
# BUG#50351 - ft_min_word_len=2 Causes query to hang
#
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);
DROP TABLE t1;

--echo #
--echo # Bug #49445: Assertion failed: 0, file .\item_row.cc, line 55 with 
--echo #   fulltext search and row op
--echo #

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);
DROP TABLE t1;

--echo #
--echo # BUG#51866 - crash with repair by sort and fulltext keys
--echo #
CREATE TABLE t1(a CHAR(4), FULLTEXT(a)) ENGINE = InnoDB;
INSERT INTO t1 VALUES('aaaa');
# INNODB_FTS: Do not support "set myisam_sort_buffer" commands
#SET myisam_sort_buffer_size=4;
#REPAIR TABLE t1;
#SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size;
DROP TABLE t1;

--echo #
--echo # Bug#54484 explain + prepared statement: crash and Got error -1 from storage engine
--echo #

CREATE TABLE t1(f1 VARCHAR(6) NOT NULL, FULLTEXT KEY(f1), UNIQUE(f1)) ENGINE = InnoDB;
INSERT INTO t1 VALUES ('test');
--disable_warnings
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;

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;
EXECUTE stmt;

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;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;
--enable_warnings

DROP TABLE t1;

--echo End of 5.1 tests

# This is an adapted and extended version of an Oracle test for
# Bug#21140111: Explain ... match against: Assertion failed: ret ...
# No bug was repeatable for MariaDB.

CREATE TABLE z(a INTEGER) engine=innodb;
CREATE TABLE q(b TEXT CHARSET latin1, fulltext(b)) engine=innodb;

--error ER_PARSE_ERROR
EXPLAIN SELECT 1 FROM q WHERE (SELECT MATCH(b) AGAINST ('*') FROM z);
--error ER_PARSE_ERROR
SELECT 1 FROM q WHERE (SELECT MATCH(b) AGAINST ('*') FROM z);
--error ER_BAD_FIELD_ERROR
EXPLAIN SELECT MATCH(b) AGAINST ('*') FROM z;
--error ER_BAD_FIELD_ERROR
SELECT MATCH(b) AGAINST ('*') FROM z;
--error ER_FT_MATCHING_KEY_NOT_FOUND
EXPLAIN SELECT MATCH(a) AGAINST ('*') FROM z;
--error ER_FT_MATCHING_KEY_NOT_FOUND
SELECT MATCH(a) AGAINST ('*') FROM z;
EXPLAIN SELECT MATCH(b) AGAINST ('*') FROM q;
--error ER_PARSE_ERROR
SELECT MATCH(b) AGAINST ('*') FROM q;

DROP TABLE z, q;

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');
let $limit=0;
let $N=6;
while ($N)
{
 eval SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE)
 LIMIT $limit;
 inc $limit;
 dec $N;
}

DROP TABLE t;

--echo #
--echo # MDEV-25295 Aborted FTS_DOC_ID_INDEX considered as
--echo # 	existing FTS_DOC_ID_INDEX during DDL
--echo #
SET sql_mode='';
CREATE TABLE t1 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL,title CHAR(1),body TEXT)engine=innodb;
INSERT INTO t1 (FTS_DOC_ID,title,body)VALUES(1,0,0), (1,0,0);
--error ER_DUP_ENTRY
CREATE FULLTEXT INDEX idx1 ON t1 (title,body);
--error ER_DUP_ENTRY
CREATE FULLTEXT INDEX idx1 ON t1 (title,body);
DROP TABLE t1;
SET sql_mode = DEFAULT;

--echo #
--echo # MDEV-25070 SIGSEGV in fts_create_in_mem_aux_table
--echo #
CREATE TABLE t1 (a CHAR, FULLTEXT KEY(a)) ENGINE=InnoDB;
--disable_warnings
ALTER TABLE t1 DISCARD TABLESPACE;
ALTER TABLE t1 ADD FULLTEXT INDEX (a);
SHOW CREATE TABLE t1;
DROP TABLE t1;
--enable_warnings

--echo #
--echo # MDEV-25257 SEGV in fts_get_next_doc_id upon some INSERT
--echo #
SET @save = @@global.innodb_file_per_table;
SET @@global.innodb_file_per_table = 0;

CREATE TABLE t1 (
   col_int INTEGER, col_text TEXT,
   col_int_g INTEGER GENERATED ALWAYS AS (col_int)
) ENGINE = InnoDB ROW_FORMAT = Redundant ;
ALTER TABLE t1 ADD FULLTEXT KEY `ftidx` ( col_text ) ;
ALTER TABLE t1 DROP KEY `ftidx` ;
INSERT INTO t1 (col_int, col_text) VALUES ( 1255, NULL);
DROP TABLE t1;
SET @@global.innodb_file_per_table = @save;

--echo #
--echo # MDEV-20797  FULLTEXT search with apostrophe,
--echo #		and mandatory words
--echo #
CREATE TABLE t1(f1 TINYTEXT NOT NULL, FULLTEXT(f1))ENGINE=InnoDB;
INSERT INTO t1 VALUES('O''Brien'), ('O Brien'), ('''Brien');
INSERT INTO t1 VALUES('Brien'), ('O ''Brien'), ('O'' Brien');
INSERT INTO t1 VALUES('Doh''nuts');
SELECT * FROM t1 WHERE MATCH (f1) AGAINST ("+O'Brien" IN BOOLEAN MODE);
SELECT * FROM t1 WHERE MATCH (f1) AGAINST ("+Doh'nuts" IN BOOLEAN MODE);
SELECT * FROM t1 WHERE MATCH (f1) AGAINST ("+''Brien" IN BOOLEAN MODE);
DROP TABLE t1;

--echo #
--echo # MDEV-29058  Assertion `index->type == 32' failed
--echo #	in dict_index_build_internal_fts
--echo #
call mtr.add_suppression("InnoDB: Index `f` of table `test`.`t2` is corrupted");
call mtr.add_suppression("InnoDB: Skip adjustment of root pages for index `f`.");
let $MYSQLD_DATADIR = `SELECT @@datadir`;
CREATE TABLE t1 (f CHAR(8), FULLTEXT KEY (f)) ENGINE=InnoDB;
CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 DISCARD TABLESPACE;
--disable_warnings
FLUSH TABLES t1 FOR EXPORT;
--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd
--copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/test/t2.cfg
UNLOCK TABLES;
ALTER TABLE t1 DISCARD TABLESPACE;
ALTER TABLE t2 IMPORT TABLESPACE;
--enable_warnings
DROP TABLE t2, t1;

--echo #
--echo #  MDEV-29778  Having Unique index interference with MATCH
--echo #		from a FULLTEXT
--echo #
CREATE TABLE t1(f1 VARCHAR(100), FULLTEXT(f1),
		UNIQUE INDEX(f1))ENGINE=InnoDB;
INSERT INTO t1 VALUES("test");
SELECT f1, MATCH(f1) AGAINST ("test" IN BOOLEAN MODE) FROM t1;
DROP TABLE t1;

--echo # End of 10.3 tests

--echo #
--echo #  MDEV-35183 ADD FULLTEXT INDEX unnecessarily DROPS FTS
--echo #		COMMON TABLES
--echo #
CREATE TABLE t1 (
        ID INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200), book VARCHAR(200),
        FULLTEXT fidx(title)) ENGINE = InnoDB;
INSERT INTO t1(title) VALUES('database');
ALTER TABLE t1 DROP INDEX fidx;
create table t2 as select space from information_schema.innodb_sys_tables where name like "test/FTS_%_CONFIG";
ALTER TABLE t1 ADD FULLTEXT fidx_1(book);
select i_s.space=t2.space from information_schema.innodb_sys_tables i_s join t2 where name like "test/FTS_%_CONFIG";
SHOW CREATE TABLE t1;
DROP TABLE t1, t2;

--echo # End of 10.5 tests