#-----------------------------------------------------------------------------
# Test With alter/create/drop index
#-----------------------------------------------------------------------------

--source include/have_innodb.inc
--source include/no_valgrind_without_big.inc
--source include/default_charset.inc
let collation=UTF8_UNICODE_CI;
--source include/have_collation.inc

# Create FTS table
CREATE TABLE t1 (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        a VARCHAR(200),
        b TEXT
        ) ENGINE = InnoDB;

# Insert rows
INSERT INTO t1 (a,b) VALUES
        ('MySQL Tutorial','DBMS stands for DataBase ...')  ,
        ('How To Use MySQL Well','After you went through a ...'),
        ('Optimizing MySQL','In this tutorial we will show ...');

# Create the FTS index Using Alter Table
ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
EVAL SHOW CREATE TABLE t1;

# Insert rows
INSERT INTO t1 (a,b) VALUES
        ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
        ('MySQL vs. YourSQL','In the following database comparison ...'),
        ('MySQL Security','When configured properly, MySQL ...');

# Select word "tutorial" in the table
SELECT id FROM t1 WHERE MATCH (a,b)
        AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);

# boolean mode
select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
select id, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
select id, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;
select id from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE);
select id from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE);
select id from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE);

# query expansion
select id from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION);
select id from t1 where MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION);
select id from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY EXPANSION);

# Drop index 
ALTER TABLE t1 DROP INDEX idx;

# Create the FTS index again
CREATE FULLTEXT INDEX idx on t1 (a,b);

# Select word "tutorial" in the table
SELECT id FROM t1 WHERE MATCH (a,b)
        AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);

# boolean mode
select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
select id, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
select id, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;
select id from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE);
select id from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE);
select id from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE);

# query expansion
select id from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION);
select id from t1 where MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION);
select id from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY EXPANSION);

# insert for proximity search 
INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...');
# Insert into table with similar word of different distances
INSERT INTO t1 (a,b) VALUES
        ('test proximity search, test, proximity and phrase',
         'search, with proximity innodb');

INSERT INTO t1 (a,b) VALUES
        ('test proximity fts search, test, proximity and phrase',
         'search, with proximity innodb');

INSERT INTO t1 (a,b) VALUES
        ('test more proximity fts search, test, more proximity and phrase',
         'search, with proximity innodb');

# This should only return the first document
SELECT id FROM t1
        WHERE MATCH (a,b)
        AGAINST ('"proximity search"@2' IN BOOLEAN MODE);

# This would return no document
SELECT id FROM t1
        WHERE MATCH (a,b)
        AGAINST ('"proximity search"@1' IN BOOLEAN MODE);

# This give you all three documents
SELECT id FROM t1
        WHERE MATCH (a,b)
        AGAINST ('"proximity search"@3' IN BOOLEAN MODE);

# Similar boundary testing for the words
SELECT id FROM t1
        WHERE MATCH (a,b)
        AGAINST ('"test proximity"@3' IN BOOLEAN MODE);

# Test with more word The last document will return, please notice there
# is no ordering requirement for proximity search.
SELECT id FROM t1
        WHERE MATCH (a,b)
        AGAINST ('"more test proximity"@3' IN BOOLEAN MODE);

SELECT id FROM t1
        WHERE MATCH (a,b)
        AGAINST ('"more test proximity"@2' IN BOOLEAN MODE);

# The phrase search will not require exact word ordering
SELECT id FROM t1
        WHERE MATCH (a,b)
        AGAINST ('"more fts proximity"@02' IN BOOLEAN MODE);

DROP TABLE t1;



#------------------------------------------------------------------------------
# Test with FTS condition in subquery
#------------------------------------------------------------------------------

# Create FTS table
CREATE TABLE t1 (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        a VARCHAR(200),
        b TEXT
        ) ENGINE = InnoDB;

# Create the FTS index again
CREATE FULLTEXT INDEX idx on t1 (a,b);

# Insert rows
INSERT INTO t1 (a,b) VALUES
        ('MySQL Tutorial','DBMS stands for DataBase ...')  ,
        ('How To Use MySQL Well','After you went through a ...'),
        ('Optimizing MySQL','In this tutorial we will show ...');

# Insert rows
INSERT INTO t1 (a,b) VALUES
        ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
        ('MySQL vs. YourSQL','In the following database comparison ...'),
        ('MySQL Security','When configured properly, MySQL ...');


# Select word "tutorial" in the table
SELECT id FROM t1 WHERE MATCH (a,b)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);

# Select word "tutorial" in the table
SELECT id FROM t1 WHERE id = (SELECT MAX(id) FROM t1 WHERE MATCH (a,b)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE));

# Select word "tutorial" in the table
SELECT id FROM t1 WHERE id = (SELECT MIN(id) FROM t1 WHERE MATCH (a,b)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE));

# Select word "tutorial" in the table
SELECT id FROM t1 WHERE id = (SELECT MIN(id) FROM t1 WHERE MATCH (a,b)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)) OR id = 3 ;


# Select word "tutorial" in the table - innodb crash
SELECT id FROM t1 WHERE CONCAT(t1.a,t1.b) IN (
SELECT CONCAT(a,b) FROM t1 AS t2 WHERE 
MATCH (t2.a,t2.b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)
) OR t1.id = 3 ;


# Select word "tutorial" in the table  - innodb crash
SELECT id FROM t1 WHERE CONCAT(t1.a,t1.b) IN (
SELECT CONCAT(a,b) FROM t1 AS t2 
WHERE MATCH (t2.a,t2.b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) 
AND t2.id != 3) ;

# Select word "tutorial" in the table
SELECT id FROM t1 WHERE id IN (SELECT MIN(id) FROM t1 WHERE 
MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)) OR id = 3 ;

# Select word except  "tutorial" in the table
SELECT id FROM t1 WHERE id NOT IN (SELECT MIN(id) FROM t1 
WHERE MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)) ;


# Select word "tutorial" in the table
SELECT id FROM t1 WHERE EXISTS (SELECT t2.id FROM t1 AS t2 WHERE 
MATCH (t2.a,t2.b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) 
AND t1.id = t2.id) ;


# Select not word like "tutorial" using subquery
SELECT id FROM t1 WHERE NOT EXISTS (SELECT t2.id FROM t1 AS t2 WHERE 
MATCH (t2.a,t2.b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) 
AND t1.id = t2.id) ;

DROP TABLE t1;

# boolean search 
CREATE TABLE t1 (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        a VARCHAR(200),
        b TEXT ,
        FULLTEXT (a,b)
        ) ENGINE = InnoDB;

INSERT INTO t1(a,b) 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');

# Select word "tutorial" in the table
SELECT id FROM t1 WHERE t1.id = (SELECT MAX(t2.id) FROM t1 AS t2 WHERE 
        MATCH(t2.a,t2.b) AGAINST("+support +collections" IN BOOLEAN MODE));
SELECT id FROM t1 WHERE t1.id != (SELECT MIN(t2.id) FROM t1 AS t2 WHERE 
        MATCH(t2.a,t2.b) AGAINST("+search" IN BOOLEAN MODE));

SELECT id FROM t1 WHERE t1.id IN (SELECT t2.id FROM t1 AS t2 WHERE 
MATCH (t2.a,t2.b) AGAINST ("+call* +coll*" IN BOOLEAN MODE));

SELECT id FROM t1 WHERE EXISTS (SELECT id FROM t1 AS t2 WHERE 
MATCH t2.a,t2.b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE) AND t2.id=t1.id);


#query expansion search
# result differ for query expansion search even wo subquery
#SELECT id FROM t1 WHERE t1.id = ( SELECT  MAX(t2.id) FROM t1 AS t2 WHERE 
#MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION));
#SELECT id FROM t1 WHERE t1.id IN ( SELECT t2.id FROM t1 AS t2 WHERE
#MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION));
#SELECT id FROM t1 WHERE ( SELECT COUNT(*) FROM t1 AS t2 WHERE
#MATCH(t2.a,t2.b) AGAINST ("indexes collections" WITH QUERY EXPANSION)) >= 1 
#AND t1.id <=3 ;

# proximity search 
# insert for proximity search 
INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...');
# Insert into table with similar word of different distances
INSERT INTO t1 (a,b) VALUES
        ('test proximity search, test, proximity and phrase',
         'search, with proximity innodb');

INSERT INTO t1 (a,b) VALUES
        ('test proximity fts search, test, proximity and phrase',
         'search, with proximity innodb');

INSERT INTO t1 (a,b) VALUES
        ('test more proximity fts search, test, more proximity and phrase',
         'search, with proximity innodb');


SELECT id FROM t1 WHERE t1.id = (SELECT MAX(t2.id) FROM t1 AS t2 WHERE
MATCH(t2.a,t2.b) AGAINST ('"proximity search"@2' IN BOOLEAN MODE));
SELECT id FROM t1 WHERE t1.id > (SELECT MIN(t2.id) FROM t1 AS t2 WHERE
MATCH(t2.a,t2.b) AGAINST ('"proximity search"@2' IN BOOLEAN MODE));

SELECT id FROM t1 WHERE t1.id IN (SELECT t2.id FROM t1 AS t2 WHERE
MATCH (t2.a,t2.b) AGAINST ('"proximity search"@2' IN BOOLEAN MODE));

SELECT id FROM t1 WHERE EXISTS (SELECT id FROM t1 AS t2 WHERE
MATCH t2.a,t2.b AGAINST ('"proximity search"@2' IN BOOLEAN MODE)
AND t2.id=t1.id);

SELECT id FROM t1 WHERE EXISTS (SELECT id FROM t1 AS t2 WHERE
MATCH t2.a,t2.b AGAINST ('"more test proximity"@3' IN BOOLEAN MODE)
AND t2.id=t1.id);

SELECT id FROM t1 WHERE EXISTS (SELECT id FROM t1 AS t2 WHERE
MATCH t2.a,t2.b AGAINST ('"more test proximity"@2' IN BOOLEAN MODE)
AND t2.id=t1.id);


#------------------------------------------------------------------------------
# create table AS SELECT from fts indexed table
#------------------------------------------------------------------------------
CREATE TABLE t2 ENGINE = InnoDB AS SELECT id FROM t1 WHERE
MATCH a,b AGAINST ('support') ;
SHOW CREATE TABLE t2;
SELECT id FROM t2;
DROP TABLE t2;

CREATE TABLE t2 ENGINE = InnoDB AS SELECT id FROM t1 WHERE
MATCH a,b AGAINST("+support +collections" IN BOOLEAN MODE);
SHOW CREATE TABLE t2;
SELECT id FROM t2;
DROP TABLE t2;

CREATE TABLE t2 ENGINE = InnoDB AS SELECT id FROM t1 WHERE
MATCH a,b AGAINST ('"proximity search"@10' IN BOOLEAN MODE);
SHOW CREATE TABLE t2;
SELECT id FROM t2;
DROP TABLE t2;

DROP TABLE t1;


#------------------------------------------------------------------------------
# Verift FTS with NULL records
#------------------------------------------------------------------------------
# Create FTS table
EVAL CREATE TABLE t1 (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        a VARCHAR(200),
        b TEXT
        ) ENGINE = InnoDB;

# Create the FTS index again
CREATE FULLTEXT INDEX idx on t1 (a,b);


# Insert rows
INSERT INTO t1 (a,b) VALUES
('MySQL from Tutorial','DBMS stands for DataBase ...');

let $counter = 50;
--disable_query_log
WHILE ($counter > 0) {
   INSERT INTO t1 (a,b) VALUES (NULL,NULL);
   dec $counter;
}
--enable_query_log
INSERT INTO t1 (a,b) VALUES
('when To Use MySQL Well','After that you went through a ...');

let $counter = 50;
--disable_query_log
WHILE ($counter > 0) {
   INSERT INTO t1 (a,b) VALUES (NULL,NULL);
   dec $counter;
}
--enable_query_log
INSERT INTO t1 (a,b) VALUES
('where will Optimizing MySQL','what In this tutorial we will show ...');

INSERT INTO t1 (a,b) VALUES
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL null...');

SELECT COUNT(*) FROM t1;
SELECT COUNT(*) FROM t1 WHERE a IS NULL;
SELECT COUNT(*) FROM t1 WHERE b IS NOT NULL;

SELECT id FROM t1
     WHERE MATCH (a,b)
     AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);

SELECT id FROM t1
     WHERE MATCH (a,b)
     AGAINST (NULL IN NATURAL LANGUAGE MODE);
SELECT id FROM t1
     WHERE MATCH (a,b)
     AGAINST (NULL WITH QUERY EXPANSION);
SELECT id FROM t1
     WHERE MATCH (a,b)
     AGAINST ('null' IN NATURAL LANGUAGE MODE);
# Boolean search
# Select rows contain "MySQL" but not "YourSQL"
SELECT id FROM t1 WHERE MATCH (a,b)
AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
SELECT id FROM t1 WHERE MATCH (a,b)
AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE) AND (a IS NOT NULL OR b IS NOT NULL);
SELECT id FROM t1 WHERE MATCH (a,b)
AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE) AND (a IS NULL AND b IS NOT NULL);

# Select rows contain at least one of the two words
SELECT id FROM t1 WHERE MATCH (a,b)
AGAINST ('DBMS Security' IN BOOLEAN MODE);

# Test query expansion
SELECT COUNT(*) FROM t1
WHERE MATCH (a,b)
AGAINST ('database' WITH QUERY EXPANSION);

# proximity
SELECT id FROM t1
WHERE MATCH (a,b)
AGAINST ('"following database"@10' IN BOOLEAN MODE);


DROP TABLE t1;



#------------------------------------------------------------------------------
# More FTS test from peter's testing
#------------------------------------------------------------------------------
set names utf8;


--echo "----------Test1---------"
# Create FTS table
create table t50 (s1 varchar(60) character set utf8 collate utf8_bin) engine = innodb;
create fulltext index i on t50 (s1);
# INNODB_FTS: Assert - fixed
# Assert : InnoDB: Failing assertion: rbt_validate(result_doc->tokens)
insert into t50 values ('ABCDE'),('FGHIJ'),('KLMNO'),('VÐƷWİ');
# it was giving empty result set instead of one record  
select * from t50 where match(s1) against ('VÐƷWİ'); 
drop table t50;


--echo "----------Test2---------"
create table t50 (s1 int unsigned primary key auto_increment, s2
varchar(60) character set utf8) engine = innodb;
create fulltext index i on t50 (s2);
insert into t50 (s2) values ('FGHIJ'),('KLMNO'),('VÐƷWİ'),('ABCDE');
# INNODB_FTS: RESULT DIFF
# Order by does not sort result. 
# Optimizer's Evgeny is investigate a similar issue. InnoDB FTS is used only 
# for FT search, and should not be used as regular index for such order by query. 
# Correct the result file when fixed.
select * from t50 order by s2; 
drop table t50;


--echo "----------Test3---------"
create table t50 (id int unsigned primary key auto_increment, s2
varchar(60) character set utf8) engine = innodb;
create fulltext index i on t50 (s2);
insert into t50 (s2) values ('FGHIJ'),('KLMNO'),('VÐƷWİ'),('ABCDE');
set @@autocommit=0;
update t50 set s2 = lower(s2);
update t50 set s2 = upper(s2); 
commit;
select * from t50 where match(s2) against ('VÐƷWİ FGHIJ KLMNO ABCDE' in boolean mode);
select * from t50;
drop table t50;
set @@autocommit=1;

--echo "----------Test4---------"
create table t50 (id int unsigned primary key auto_increment, s2
varchar(60) character set utf8) engine = innodb;
create fulltext index i on t50 (s2);
insert into t50 (s2) values ('FGHIJ'),('KLMNO'),('VÐƷWİ'),('ABCD*');
select * from t50 where match(s2) against ('abcd*' in natural language
mode); 
# INNODB_FTS: RESULT DIFF(Expected). InnoDB do not index "*", so
# word "ABCD" indexed, instead of "ABCD*"
select * from t50 where match(s2) against ('abcd*' in boolean mode); 
drop table t50;


--echo "----------Test5---------"
create table t50 (s1 int, s2 varchar(200), fulltext key(s2)) engine = innodb;
set @@autocommit=0;
insert into t50 values (1,'Sunshine'),(2,'Lollipops');
select * from t50 where match(s2) against('Rainbows');
rollback;
select * from t50;
drop table t50;
set @@autocommit=1;

--echo "----------Test6---------"
CREATE TABLE t1 (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        a VARCHAR(200),
        b TEXT
        ) ENGINE = InnoDB;

# Insert rows
INSERT INTO t1 (a,b) VALUES
        ('aab` MySQL Tutorial','DBMS stands for DataBase ...')  ,
        ('aas How To Use MySQL Well','After you went through a ...'),
        ('aac Optimizing MySQL','In this tutorial we will show ...');
# Insert rows
INSERT INTO t1 (a,b) VALUES
        ('aac 1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
        ('aab MySQL vs. YourSQL','In the following database comparison ...'),
        ('aaa MySQL Security','When configured properly, MySQL ...');
# Create the FTS index Using Alter Table
ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);

-- disable_query_log
-- disable_result_log
ANALYZE TABLE t1;
-- enable_result_log
-- enable_query_log

SELECT * FROM t1 ORDER BY MATCH(a,b) AGAINST ('aac') DESC;
SELECT * FROM t1 ORDER BY MATCH(a,b) AGAINST ('aab') DESC; 

--echo "----------Test7---------"
select * from t1 where match(a,b) against ('aaa')
union select * from t1 where match(a,b) against ('aab')
union select * from t1 where match(a,b) against ('aac');

select * from t1 where match(a,b) against ('aaa')
                or    match(a,b) against ('aab')
                or    match(a,b) against ('aac'); 

DROP TABLE t1;

--echo "----------Test8---------"
# Create FTS table
CREATE TABLE t1 (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        a VARCHAR(200),
        b TEXT
        ) ENGINE = InnoDB;

# Insert rows
INSERT INTO t1 (a,b) VALUES
        ('MySQL Tutorial','DBMS stands for DataBase ... abcd')  ,
        ('How To Use MySQL Well','After you went through a q ...abdd'),
        ('Optimizing MySQL','In this tutorial we will show ...abed');

# Create the FTS index Using Alter Table
ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
EVAL SHOW CREATE TABLE t1;

# Insert rows
INSERT INTO t1 (a,b) VALUES
        ('1001 MySQL Tricks','1. Never run mysqld as root. 2. q ...'),
        ('MySQL vs. YourSQL use','In the following database comparison ...'),
        ('MySQL Security','When run configured properly, MySQL ...');

SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('run');
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('use');
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('went');
# rows should be matched as 'q' is single char its not indexed
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('run') AND NOT MATCH(a,b) AGAINST ('q');
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('use') AND NOT MATCH(a,b) AGAINST ('q');
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('went') AND NOT MATCH(a,b) AGAINST ('q');

--echo "----------Test9---------"
CREATE TABLE t2 AS SELECT * FROM t1;
ALTER TABLE t2 ENGINE=MYISAM;
CREATE FULLTEXT INDEX i ON t2 (a,b);
SET @x = (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('run'));
SET @x = @x + (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('use'));
SET @x = @x + (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('went'));
SET @x = @x + (SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('run'));
SET @x2 = (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('run'));
SET @x2 = @x2 + (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('use'));
SET @x2 = @x2 + (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('went'));
SET @x2 = @x2 + (SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('run'));
# Innodb returns value for x which is correct
SELECT @x, @x2;


DROP TABLE t2;

--echo "----------Test10---------"
CREATE TABLE t2 AS SELECT * FROM t1;
ALTER TABLE t2 ENGINE=MYISAM;
CREATE FULLTEXT INDEX i ON t2 (a,b);
SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('abc*' IN BOOLEAN MODE);
SELECT COUNT(*) FROM t1 WHERE MATCH(a,b) AGAINST ('abc*' IN BOOLEAN MODE);

DROP TABLE t2;


--echo "----------Test11---------"
CREATE TABLE t2 AS SELECT * FROM t1;
ALTER TABLE t2 ENGINE = MYISAM;
CREATE FULLTEXT INDEX i ON t2 (a,b);
ALTER TABLE t2 ENGINE=InnoDB;
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ('run');
SELECT COUNT(*) FROM t2 WHERE MATCH(a,b) AGAINST ('abc*' IN BOOLEAN MODE);
DROP TABLE t2,t1;


--echo "----------Test13---------"
set names utf8;

CREATE TABLE t1 (s1 INT, s2 VARCHAR(200) CHARACTER SET UTF8 COLLATE UTF8_SPANISH_CI) ENGINE = InnoDB;
CREATE FULLTEXT INDEX i ON t1 (s2);
INSERT INTO t1 VALUES (1,'aaCen'),(2,'aaCha'),(3,'aaCio'),(4,'aaçen'),(5,'aaçha'),(6,'aaçio');
SELECT * FROM t1 WHERE MATCH(s2) AGAINST ('aach*' IN BOOLEAN MODE);
SELECT * FROM t1 WHERE MATCH(s2) AGAINST ('aaC*' IN BOOLEAN MODE);
DROP TABLE t1;

--echo "----------Test14---------"
CREATE TABLE t1(s1 INT , s2 VARCHAR(100) CHARACTER SET sjis) ENGINE = InnoDB;
CREATE FULLTEXT INDEX i ON t1 (s2);
INSERT INTO t1 VALUES (1,'ペペペ'),(2,'テテテ'),(3,'ルルル'),(4,'グググ');
# Innodb Asset : file ha_innodb.cc line 4557
#SELECT * FROM t1 WHERE MATCH(s2) AGAINST ('テテ*' IN BOOLEAN MODE);
DROP TABLE t1;


--echo "----------Test15---------"
CREATE TABLE t1 (s1 VARCHAR (60) CHARACTER SET UTF8 COLLATE UTF8_UNICODE_520_CI) ENGINE = MyISAM;
CREATE FULLTEXT INDEX i ON t1 (s1);
INSERT INTO t1 VALUES
('a'),('b'),('c'),('d'),('ŁŁŁŁ'),('LLLL'),(NULL),('ŁŁŁŁ ŁŁŁŁ'),('LLLLLLLL');
SELECT * FROM t1 WHERE MATCH(s1) AGAINST ('LLLL' COLLATE UTF8_UNICODE_520_CI);
CREATE TABLE t2 (s1 VARCHAR(60) CHARACTER SET UTF8 COLLATE UTF8_POLISH_CI) ENGINE = InnoDB;
CREATE FULLTEXT INDEX i ON t2 ( s1);
INSERT INTO t2 VALUES
('a'),('b'),('c'),('d'),('ŁŁŁŁ'),('LLLL'),(NULL),('ŁŁŁŁ ŁŁŁŁ'),('LLLLLLLL');
SELECT * FROM t2 WHERE MATCH(s1) AGAINST ('LLLL' COLLATE UTF8_UNICODE_520_CI);
--disable_warnings
DROP TABLE t1,t2;
--enable_warnings

--echo "----------Test16---------"
CREATE TABLE t1 (s1 INT, s2 VARCHAR(50) CHARACTER SET UTF8) ENGINE = InnoDB;
CREATE FULLTEXT INDEX i ON t1(s2);
INSERT INTO t1 VALUES (2, 'ğė Daśi      p          ');
SELECT * FROM t1 WHERE MATCH(s2) AGAINST ('+p +"ğė Daśi*"' IN BOOLEAN MODE);
DROP TABLE t1;


--echo "----------Test19---------"
#19 Failure with Boolean quoted search
CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF8) ENGINE = InnoDB;
INSERT INTO t1 VALUES (1,'İóëɠ');
CREATE FULLTEXT INDEX i ON t1 (char_column);
SELECT * FROM t1 WHERE MATCH(char_column) AGAINST ('"İóëɠ"' IN BOOLEAN MODE);
DROP TABLE t1;

--echo "----------Test20---------"
#20 Crash with utf32 and boolean mode.
CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF32, char_column2 VARCHAR(60) character set utf8) ENGINE = InnoDB;
INSERT INTO t1 (char_column) VALUES ('abcde'),('fghij'),('klmno'),('qrstu');
UPDATE t1 SET char_column2 = char_column;
CREATE FULLTEXT INDEX i ON t1 (char_column2);
--error ER_FT_MATCHING_KEY_NOT_FOUND
SELECT * FROM t1 WHERE MATCH(char_column) AGAINST ('abc*' IN BOOLEAN MODE);
DROP TABLE t1;

--echo "----------Test22---------"
# case 22
CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF8) ENGINE = InnoDB;
INSERT INTO t1 VALUES (1,'aaa'),(2,'bbb'),(3,'ccc');
CREATE FULLTEXT INDEX i ON t1 (char_column);
HANDLER t1 OPEN;
--error ER_KEY_DOESNT_SUPPORT
HANDLER t1 READ i = ('aaa');
DROP TABLE t1;
#23. Duplicate key error when there are no unique indexes (procedure test)
#24 Failure after cascading update - already have tests

--echo "----------Test25---------"
#25 Failure with Croatian boolean truncated search.
CREATE TABLE t1 ( id INT , char_column VARCHAR(60) CHARACTER SET UTF8 COLLATE UTF8_CROATIAN_CI) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,'LJin'),(2,'ljin'),(3,'lmin'),(4,'LJLJLJLJLJ');
CREATE FULLTEXT INDEX i ON t1 (char_column);
#inndob:error incorrect result correct it after fix
SELECT count(*) FROM t1 WHERE MATCH (char_column) AGAINST ('lj*' IN BOOLEAN MODE);
DROP TABLE t1;

#26. Index error when run procedure call from multiple clients

--echo "----------Test27---------"
#27 Crash after server restart
CREATE TABLE t1 (id INT,char_column VARCHAR(60));
CREATE TABLE t2 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, a TEXT)ENGINE=InnoDB;
ALTER TABLE t2 DROP a;
CREATE FULLTEXT INDEX i ON t1 (char_column);
INSERT INTO t1 values (1,'aaa');

CREATE TABLE mdev20987_1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=InnoDB;
CREATE TABLE mdev20987_2(f1 INT NOT NULL, f2 CHAR(100),
                FULLTEXT(f2),
                FOREIGN KEY(f1) REFERENCES mdev20987_1(f1))ENGINE=InnoDB;
INSERT INTO mdev20987_1 VALUES(1);
INSERT INTO mdev20987_2 VALUES(1, 'mariadb');

CREATE TABLE mdev22358 (a INT, b TEXT, FULLTEXT KEY ftidx (b)) ENGINE=InnoDB;
ALTER TABLE mdev22358 DROP KEY ftidx;
INSERT INTO mdev22358 (a) VALUES (2),(2);
--error ER_DUP_ENTRY
ALTER TABLE mdev22358 ADD UNIQUE KEY uidx (a), ADD FULLTEXT KEY ftidx (b);
--source include/restart_mysqld.inc
SHOW CREATE TABLE t2;
DELETE FROM t1 WHERE MATCH(char_column) AGAINST ('bbb');
DROP TABLE t1, t2, mdev20987_2, mdev20987_1, mdev22358;

--echo "----------Test28---------"
create table `fts_test`(`a` text,fulltext key(`a`))engine=innodb;
set session autocommit=0;
insert into `fts_test` values ('');
savepoint `b`;
savepoint `b`;
set session autocommit=1;
DROP TABLE fts_test;

# Continue test savepoint related operations. With a commit after
# multiple rollback to savepoints
--echo "----------Test29---------"
CREATE TABLE articles (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        body TEXT,
        FULLTEXT (title,body)
        ) ENGINE=InnoDB;

INSERT INTO articles (title,body) VALUES
        ('MySQL Tutorial','DBMS stands for DataBase ...');


start transaction;

INSERT INTO articles (title,body) VALUES 
('How To Use MySQL Well','After you went through a ...');

savepoint `a1`;

INSERT INTO articles (title,body) VALUES
('Optimizing MySQL','In this tutorial we will show ...');

savepoint `a2`;

INSERT INTO articles (title,body) VALUES
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...');

savepoint `a3`;

INSERT INTO articles (title,body) VALUES
('MySQL vs. YourSQL','In the following database comparison ...');

savepoint `a4`;

# FTS do not parse those uncommitted rows, only one row should show up
SELECT * FROM articles
        WHERE MATCH (title,body)
        AGAINST ('Database' IN NATURAL LANGUAGE MODE);

rollback to savepoint a3;

# The last inserted row should not be there
select title, body from articles;

INSERT INTO articles (title,body) VALUES
('MySQL Security','When configured properly, MySQL ...');

savepoint `a5`;

select title, body from articles;

rollback to savepoint a2;

select title, body from articles;

commit;

SELECT * FROM articles
        WHERE MATCH (title,body)
        AGAINST ('Database' IN NATURAL LANGUAGE MODE);

SELECT * FROM articles
        WHERE MATCH (title,body)
        AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);

DROP TABLE articles;

# Continue test savepoint related operations. With a rollback after
# multiple rollback to savepoints
--echo "----------Test30---------"
CREATE TABLE articles (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        body TEXT,
        FULLTEXT (title,body)
        ) ENGINE=InnoDB;

INSERT INTO articles (title,body) VALUES
        ('MySQL Tutorial','DBMS stands for DataBase ...');

start transaction;

INSERT INTO articles (title,body) VALUES 
('How To Use MySQL Well','After you went through a ...');

savepoint `a1`;

INSERT INTO articles (title,body) VALUES
('Optimizing MySQL','In this tutorial we will show ...');

savepoint `a2`;

INSERT INTO articles (title,body) VALUES
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...');

savepoint `a3`;

INSERT INTO articles (title,body) VALUES
('MySQL vs. YourSQL','In the following database comparison ...');

savepoint `a4`;

# FTS do not parse those uncommitted rows, only one row should show up
SELECT * FROM articles
        WHERE MATCH (title,body)
        AGAINST ('Database' IN NATURAL LANGUAGE MODE);

rollback to savepoint a3;

# The last inserted row should not be there
select title, body from articles;

INSERT INTO articles (title,body) VALUES
('MySQL Security','When configured properly, MySQL ...');

savepoint `a5`;

select title, body from articles;

rollback to savepoint a2;

select title, body from articles;

rollback;

SELECT * FROM articles
        WHERE MATCH (title,body)
        AGAINST ('Database' IN NATURAL LANGUAGE MODE);

SELECT * FROM articles
        WHERE MATCH (title,body)
        AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);

DROP TABLE articles;

# Test for Bug #13907075 - DIFFERENT RESULTS FOR DIFFERENT TERM ORDER
# WITH INNODB BOOLEAN FULLTEXT SEARCH. The FTS_IGNORE ("-") operation
# is orderless
# Create FTS table
CREATE TABLE articles (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        body TEXT,
        FULLTEXT (title,body)
        ) ENGINE=InnoDB;

# Insert six rows
INSERT INTO articles (title,body) VALUES
        ('MySQL Tutorial','DBMS stands for DataBase ...')  ,
        ('How To Use MySQL Well','After you went through a ...'),
        ('Optimizing MySQL','In this tutorial we will show ...'),
        ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
        ('MySQL vs. YourSQL','In the following database comparison ...'),
        ('MySQL Security','When configured properly, MySQL ...');

-- disable_result_log
ANALYZE TABLE articles;
-- enable_result_log

SELECT *,  MATCH(title, body) AGAINST ('-database +MySQL' IN BOOLEAN MODE) AS score from articles;

SELECT *, MATCH(title, body) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score  FROM articles;

# With subquery
SELECT * FROM articles where  MATCH(title, body) AGAINST ('MySQL - (database - tutorial)' IN BOOLEAN MODE);

SELECT * FROM articles where MATCH(title, body) AGAINST ('MySQL - (- tutorial database)' IN BOOLEAN MODE);

# More complex query
SELECT * FROM articles where MATCH(title, body) AGAINST ('MySQL - (- tutorial database) -Tricks' IN BOOLEAN MODE);

SELECT * FROM articles where MATCH(title, body) AGAINST ('-Tricks MySQL - (- tutorial database)' IN BOOLEAN MODE);

DROP TABLE articles;

# Test for Bug 13940669 - 64901: INNODB: ASSERTION FAILURE IN
# THREAD 34387022112 IN FILE REM0CMP.CC LINE 5

create table t1 (FTS_DOC_ID bigint unsigned auto_increment not null primary key,
title varchar(200),body text,fulltext(title,body)) engine=innodb;

insert into t1 set body='test';

select * from t1 where match(title,body) against('%test');

select * from t1 where match(title,body) against('%');

select * from t1 where match(title,body) against('%%%%');

drop table t1;

# Test for Bug 13881758 - 64745: CREATE FULLTEXT INDEX CAUSES CRASH
# Create a database with empty space in its name
CREATE DATABASE `benu database`;

USE `benu database`;

# Create FTS table
CREATE TABLE t1 (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        a VARCHAR(200),
        b TEXT
        ) ENGINE = InnoDB;

# Insert rows
INSERT INTO t1 (a,b) VALUES
        ('MySQL Tutorial','DBMS stands for DataBase ...')  ,
        ('How To Use MySQL Well','After you went through a ...'),
        ('Optimizing MySQL','In this tutorial we will show ...');

# Create the FTS index Using Alter Table
ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
EVAL SHOW CREATE TABLE t1;

# Insert rows
INSERT INTO t1 (a,b) VALUES
        ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
        ('MySQL vs. YourSQL','In the following database comparison ...'),
        ('MySQL Security','When configured properly, MySQL ...');

# Select word "tutorial" in the table
SELECT id FROM t1 WHERE MATCH (a,b)
        AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);

# boolean mode
select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
select id, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
select id, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;
select id from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE);
select id from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE);
select id from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE);

DROP DATABASE `benu database`;

USE test;

# Test for Bug #14101706 - CRASH WITH DDL IN ROW_MERGE_BUILD_INDEXES
# WHEN FULLTEXT INDEXES EXIST

CREATE TABLE `t21` (`a` text, `b` int not null,
fulltext key (`a`), fulltext key (`a`)
) ENGINE=INNODB DEFAULT CHARSET=LATIN1;

--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER  TABLE `t21` ADD UNIQUE INDEX (`b`), ALGORITHM=INPLACE;
ALTER  TABLE `t21` ADD UNIQUE INDEX (`b`);

DROP TABLE t21;

CREATE TABLE `t21` (`a` text, `b` int not null,
fulltext key (`a`)) ENGINE=INNODB DEFAULT CHARSET=LATIN1;

ALTER  TABLE `t21` ADD UNIQUE INDEX (`b`);

DROP TABLE t21;

# Test primary index rebuild
CREATE TABLE t1 (
        id INT NOT NULL,
        a VARCHAR(200),
        b TEXT
        ) ENGINE = InnoDB;

# Insert rows
INSERT INTO t1 VALUES
        (1, 'MySQL Tutorial','DBMS stands for DataBase ...')  ,
        (2, 'How To Use MySQL Well','After you went through a ...'),
        (3, 'Optimizing MySQL','In this tutorial we will show ...');

ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);

ALTER  TABLE t1 ADD UNIQUE INDEX (`id`);

# Select word "tutorial" in the table
SELECT id FROM t1 WHERE MATCH (a,b)
        AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);

# boolean mode
select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
select id, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;

DROP TABLE t1;

# Test create the FTS and primary index in the same clause
CREATE TABLE t1 (
        id INT NOT NULL,
        a VARCHAR(200),
        b TEXT
        ) ENGINE = InnoDB;

# Insert rows
INSERT INTO t1 VALUES
        (1, 'MySQL Tutorial','DBMS stands for DataBase ...')  ,
        (2, 'How To Use MySQL Well','After you went through a ...'),
        (3, 'Optimizing MySQL','In this tutorial we will show ...');

ALTER  TABLE t1 ADD UNIQUE INDEX (`id`), ADD FULLTEXT INDEX idx (a,b);

# Select word "tutorial" in the table
SELECT id FROM t1 WHERE MATCH (a,b)
        AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);

# boolean mode
select id from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
select id from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
select id from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
select id from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);

DROP TABLE t1;

# Create FTS table with FTS_DOC_ID already existed
CREATE TABLE t1 (
        FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
        a VARCHAR(200),
        b TEXT
        ) ENGINE = InnoDB;

# Insert rows
INSERT INTO t1 VALUES
        (1, 'MySQL Tutorial','DBMS stands for DataBase ...')  ,
        (2, 'How To Use MySQL Well','After you went through a ...'),
        (3, 'Optimizing MySQL','In this tutorial we will show ...');

ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);

ALTER  TABLE t1 ADD UNIQUE INDEX (`FTS_DOC_ID`);

# Select word "tutorial" in the table
SELECT FTS_DOC_ID FROM t1 WHERE MATCH (a,b)
        AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);

# boolean mode
select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
select FTS_DOC_ID, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;

DROP TABLE t1;

# Create FTS table with FTS_DOC_ID and FTS_DOC_ID_INDEX
CREATE TABLE t1 (
        FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
        a VARCHAR(200),
        b TEXT
        ) ENGINE = InnoDB;

# Insert rows
INSERT INTO t1 VALUES
        (1, 'MySQL Tutorial','DBMS stands for DataBase ...')  ,
        (2, 'How To Use MySQL Well','After you went through a ...'),
        (3, 'Optimizing MySQL','In this tutorial we will show ...');

ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b), ADD UNIQUE INDEX FTS_DOC_ID_INDEX (FTS_DOC_ID);

# Select word "tutorial" in the table
SELECT FTS_DOC_ID FROM t1 WHERE MATCH (a,b)
        AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);

# boolean mode
select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
select FTS_DOC_ID from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
select FTS_DOC_ID, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;

DROP TABLE t1;

# Test for bug #14079609 - FTS: CRASH IN FTS_TRX_TABLE_CMP WITH SAVEPOINTS, XA

CREATE TABLE t2 (`b` char(2),fulltext(`b`)) ENGINE=INNODB
DEFAULT CHARSET=LATIN1;

CREATE TABLE t3 LIKE t2;

INSERT INTO `t2` VALUES();

COMMIT WORK AND CHAIN;

INSERT  INTO `t3` VALUES ();
UPDATE  `t2` SET `b` = 'a';

SAVEPOINT BATCH1;

DROP TABLE t2;
DROP TABLE t3;

# Create FTS table
CREATE TABLE t1 (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        a VARCHAR(200),
        b TEXT
        ) ENGINE = InnoDB;

# Insert rows
INSERT INTO t1 (a,b) VALUES
        ('MySQL Tutorial','DBMS stands for DataBase ...')  ,
        ('How To Use MySQL Well','After you went through a ...'),
        ('Optimizing MySQL','In this tutorial we will show ...');

# Create the FTS index Using Alter Table
ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);

COMMIT WORK AND CHAIN;

INSERT INTO t1 (a,b) VALUES
        ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
        ('MySQL vs. YourSQL','In the following database comparison ...'),
        ('MySQL Security','When configured properly, MySQL ...');

SAVEPOINT BATCH1;

SELECT id FROM t1 WHERE MATCH (a,b)
        AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);

INSERT INTO t1 (a,b) VALUES
        ('1002 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
        ('MySQL vs. YourSQL','In the following database comparison ...'),
        ('MySQL Security','When configured properly, MySQL ...');


ROLLBACK TO SAVEPOINT BATCH1;

COMMIT;

SELECT id FROM t1 WHERE MATCH (a,b)
        AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);

DROP TABLE t1;

# Test for Bug 14588091 - FTS: BUFFER OVERFLOW IN FTS_AST_CREATE_NODE_TEXT
CREATE TABLE `t` (`a` char(20) character set utf8 default null,
fulltext key (`a`)) ENGINE=INNODB;
INSERT INTO `t` VALUES ('a');
INSERT INTO `t` VALUES ('aaa');

# 0x22 is the '"', 0xdd is not encoded in utf8
SELECT MATCH(`a`) AGAINST (0x22dd22) FROM `t`;
SELECT MATCH(`a`) AGAINST (0x2222) FROM `t`;
SELECT MATCH(`a`) AGAINST (0x22) FROM `t`;

# this should show one match
SELECT MATCH(`a`) AGAINST (0x2261616122) FROM `t`;

# again 0xdd should be ignored
SELECT MATCH(`a`) AGAINST (0x2261dd6122) FROM `t`;

SELECT MATCH(`a`) AGAINST (0x2261dd612222226122) FROM `t`;

DROP TABLE t;

# InnoDB FTS does not support index scan from handler
CREATE TABLE t(a CHAR(1),FULLTEXT KEY(a)) ENGINE=INNODB;
HANDLER t OPEN;
HANDLER t READ a NEXT;
HANDLER t READ a PREV;
DROP TABLE t;

CREATE TABLE `%`(a TEXT, FULLTEXT INDEX(a)) ENGINE=INNODB;
CREATE TABLE `A B`(a TEXT, FULLTEXT INDEX(a)) ENGINE=INNODB;
DROP TABLE `%`;
DROP TABLE `A B`;

CREATE TABLE `t-26`(a VARCHAR(10),FULLTEXT KEY(a)) ENGINE=INNODB;
INSERT INTO `t-26` VALUES('117');
DROP TABLE `t-26`;

# Test on phrase search with stopwords contained in the search string
CREATE TABLE `t1` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `content` TEXT NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT INDEX `IDX_CONTEXT_FULLTEXT`(`content`)
)
ENGINE = InnoDB;

insert into t1 (content)
values
('This is a story which has has a complicated phrase structure here in the
middle'),
('This is a story which doesn''t have that text'),
('This is a story that has complicated the phrase structure');

select * from t1
where match(content) against('"complicated phrase structure"' in boolean
mode);

# Test single phrase search with "+" symbol, one row should be returned
select * from t1
where match(content) against('+"complicated phrase structure"' in boolean
mode);

# Test phrase search with stopwords in between, one row should be returned
select * from t1
where match(content) against('"complicated the phrase structure"' in boolean
mode);

# Test phrase search with multiple "+" symbols
select * from t1 where match(content) against('+"this is a story which" +"complicated the phrase structure"' in boolean mode);

# Test phrase search with leading word is a stopword, such stopword would be
# ignored
select * from t1 where match(content) against('"the complicated the phrase structure"' in boolean mode);

# Test phrase search with non-matching stopword in between, no row should be
# returned
select * from t1 where match(content) against('"complicated a phrase structure"' in boolean mode);

DROP TABLE t1;

CREATE TABLE my (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
c VARCHAR(32), FULLTEXT(c)) ENGINE = INNODB;

INSERT INTO my (c) VALUES ('green-iguana');

SELECT * FROM my WHERE MATCH(c) AGAINST ('green-iguana');

DROP TABLE my;

CREATE TABLE ift (
  `a` int(11) NOT NULL,
  `b` text,
  PRIMARY KEY (`a`),
  FULLTEXT KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO ift values (1, "skip");
INSERT INTO ift values (2, "skip and networking");
INSERT INTO ift values (3, "--skip-networking");
INSERT INTO ift values (4, "-donot--skip-networking");

SELECT * FROM ift WHERE MATCH (b) AGAINST ('--skip-networking');
SELECT * FROM ift WHERE MATCH (b) AGAINST ('skip-networking');
SELECT * FROM ift WHERE MATCH (b) AGAINST ('----');
SELECT * FROM ift WHERE MATCH (b) AGAINST ('-donot--skip-networking');

DROP TABLE ift;

# Test special cases of wildword.
# Create FTS table
CREATE TABLE articles (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        body TEXT,
        FULLTEXT (title,body)
        ) ENGINE=InnoDB;

# Insert six rows
INSERT INTO articles (title,body) VALUES
        ('MySQL Tutorial','DBMS stands for DataBase ...')  ,
        ('How To Use MySQL Well','After you went through a ...'),
        ('Optimizing MySQL','In this tutorial we will show ...'),
        ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
        ('MySQL vs. YourSQL','In the following database comparison ...'),
        ('( that''s me )','When configured properly, MySQL ...');

SELECT * FROM articles WHERE MATCH (title,body)
        AGAINST ('( yours''s* )' IN BOOLEAN MODE);

SELECT * FROM articles WHERE MATCH (title,body)
	AGAINST ('s*' IN BOOLEAN MODE);

SELECT * FROM articles WHERE MATCH (title,body)
        AGAINST ('stands\'] | * | show[@database' IN NATURAL LANGUAGE MODE);

DROP TABLE articles;

# Test for BUG#16429688 - FTS: SYNTAX ERROR, UNEXPECTED '*', EXPECTING $END
CREATE TABLE t1(a TEXT CHARACTER SET LATIN1, FULLTEXT INDEX(a)) ENGINE=INNODB;

--error ER_PARSE_ERROR
SELECT * FROM t1 WHERE MATCH(a) AGAINST("*");

DROP TABLE t1;

# Test for BUG#16516193 - LITERAL PHRASES CANNOT BE COMBINED WITH + OR - OPERATOR
# Create FTS table
CREATE TABLE t1 (
	id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	a VARCHAR(200),
	FULLTEXT (a)
	) ENGINE= InnoDB;

# Insert rows
INSERT INTO t1 (a) VALUES
	('Do you know MySQL is a good database'),
	('How to build a good database'),
	('Do you know'),
	('Do you know MySQL'),
	('How to use MySQL'),
	('Do you feel good'),
	('MySQL is good'),
	('MySQL is good to know'),
	('What is database');

SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know mysql"' IN BOOLEAN MODE);

SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+("know mysql")' IN BOOLEAN MODE);

SELECT * FROM t1 WHERE MATCH (a) AGAINST ('("know mysql" good)' IN BOOLEAN MODE);

SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+("know mysql" good)' IN BOOLEAN MODE);

SELECT * FROM t1 WHERE MATCH (a) AGAINST ('(good "know mysql")' IN BOOLEAN MODE);

SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+(good "know mysql")' IN BOOLEAN MODE);

SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+("know mysql" "good database")' IN BOOLEAN MODE);

SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know mysql" +"good database"' IN BOOLEAN MODE);

SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know database"@4' IN BOOLEAN MODE);

SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"know database"@8' IN BOOLEAN MODE);

# Drop table
DROP TABLE t1;

# Test for BUG#16885178 - INNODB FULLTEXT PHRASE SEARCH VALGRIND ERROR
CREATE TABLE t1 (
	id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	a VARCHAR(200),
	FULLTEXT (a)
	) ENGINE= InnoDB;

# Insert a special row
INSERT INTO t1 (a) VALUES
        ('know mysql good database');

# This phrase search fails in valgrind test before the fix.
SELECT * FROM t1 WHERE MATCH (a) AGAINST ('+"good database"' IN BOOLEAN MODE);

DROP TABLE t1;

# MDEV-19974 InnoDB: Cannot load compressed BLOB
--disable_query_log
SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed;
SET GLOBAL innodb_read_only_compressed=OFF;
--enable_query_log

CREATE TABLE t1(f1 TEXT, FULLTEXT KEY(f1))ENGINE=InnoDB;
INSERT INTO t1 VALUES(repeat("this is the test case", 500));
ALTER TABLE t1 KEY_BLOCK_SIZE=4;
ALTER TABLE t1 KEY_BLOCK_SIZE=0;
--disable_query_log
SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed;
--enable_query_log
DROP TABLE t1;