path: root/mysql-test/suite/innodb_fts/t/opt.test

--source include/have_innodb.inc

#
# Tests for optimizations for InnoDB fulltext search (WL#6043)
#

CREATE TABLE wp(
  FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL DEFAULT '',
  text MEDIUMTEXT NOT NULL,
  dummy INTEGER,
  PRIMARY KEY (FTS_DOC_ID),
  UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
  FULLTEXT KEY idx (title,text)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO wp (title, text) VALUES
  ('MySQL Tutorial','DBMS stands for MySQL 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 to database comparison ...'),
  ('MySQL Security','When configured properly, MySQL ...');

CREATE TABLE t1 (i INTEGER);
INSERT INTO t1 SELECT FTS_DOC_ID FROM wp;

SET STATEMENT use_stat_tables=never FOR
ANALYZE TABLE t1;
SET STATEMENT use_stat_tables=never FOR
ANALYZE TABLE wp;

--disable_ps2_protocol

#
#  Show results of MATCH expressions for reference
#
SELECT FTS_DOC_ID, title, MATCH(title, text) AGAINST ('database') AS score1,
       MATCH(title, text) AGAINST ('mysql') AS score2
FROM wp;

#
# Test that filesort is not used if ordering on same match expression
# as where clause
#
--echo No sorting for this query
FLUSH STATUS;

SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
ORDER BY score DESC;

SHOW SESSION STATUS LIKE 'Sort%';

--echo No sorting for this query even if MATCH is part of an expression
FLUSH STATUS;

SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database') > 0.1
ORDER BY score DESC;

SHOW SESSION STATUS LIKE 'Sort%';

--echo No sorting even if there are several MATCH expressions as long as the
--echo right one is used in ORDER BY
FLUSH STATUS;

SELECT title, MATCH(title, text) AGAINST ('database') AS score1,
       MATCH(title, text) AGAINST ('mysql') AS score2
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
ORDER BY score1 DESC;

SHOW SESSION STATUS LIKE 'Sort%';

--echo No Sorting since FT table is first table in query
FLUSH STATUS;

SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp, t1
WHERE MATCH(title, text) AGAINST ('database') AND FTS_DOC_ID = t1.i
ORDER BY score DESC;

SHOW SESSION STATUS LIKE 'Sort_rows%';

--echo Sorting since there is no WHERE clause
FLUSH STATUS;

--sorted_result
SELECT MATCH(title, text) AGAINST ('database'), title AS score
FROM wp
ORDER BY score DESC;

SHOW SESSION STATUS LIKE 'Sort_rows%';

--echo Sorting since ordering on multiple columns
FLUSH STATUS;

SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
ORDER BY score DESC, FTS_DOC_ID;

SHOW SESSION STATUS LIKE 'Sort_rows%';

--echo Sorting since ordering is not descending
FLUSH STATUS;

SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
ORDER BY score ASC;

SHOW SESSION STATUS LIKE 'Sort_rows%';

--echo Sorting because one is ordering on a different MATCH expression
FLUSH STATUS;

SELECT title, MATCH(title, text) AGAINST ('mysql') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
ORDER BY score DESC;

SHOW SESSION STATUS LIKE 'Sort_rows%';

#
#  Tests for ORDER BY/LIMIT optimzation
#
--echo No sorting for this query
FLUSH STATUS;

SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp
ORDER BY score DESC LIMIT 2;

SHOW SESSION STATUS LIKE 'Sort%';

--echo Revert to table scan and sorting for this query since not
--echo enough matching rows to satisfy LIMIT clause
FLUSH STATUS;

SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp
ORDER BY score DESC LIMIT 2;

SHOW SESSION STATUS LIKE 'Handler_read%';
SHOW SESSION STATUS LIKE 'Sort_rows%';

--echo Sorting since no LIMIT clause
FLUSH STATUS;

--sorted_result
SELECT MATCH(title, text) AGAINST ('database') AS score, title
FROM wp
ORDER BY score DESC;

SHOW SESSION STATUS LIKE 'Sort_rows%';

--echo Sorting since there is a WHERE clause
FLUSH STATUS;

SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE dummy IS NULL
ORDER BY score DESC LIMIT 2;

SHOW SESSION STATUS LIKE 'Sort_rows%';

--echo Sorting since ordering is not on a simple MATCH expressions
FLUSH STATUS;

SELECT title, (MATCH(title, text) AGAINST ('database')) * 100 AS score
FROM wp
ORDER BY score DESC LIMIT 2;

SHOW SESSION STATUS LIKE 'Sort_rows%';

#
#  Test that there is no row accesses if all necessary information is
#  available in FTS result
#
--echo No ordinary handler accesses when only accessing FTS_DOC_ID and MATCH
FLUSH STATUS;

SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database');

SHOW SESSION STATUS LIKE 'Handler_read%';

--echo Still no handler accesses when adding FTS_DOC_ID to WHERE clause
FLUSH STATUS;

SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database') AND FTS_DOC_ID > 2;

SHOW SESSION STATUS LIKE 'Handler_read%';

--echo Still no handler accesses when ordering by MATCH expression
FLUSH STATUS;

SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
ORDER BY score;

SHOW SESSION STATUS LIKE 'Handler_read%';

--echo Optimization is disabled when ordering on FTS_DOC_ID
FLUSH STATUS;

SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
ORDER BY 1 DESC;

SHOW SESSION STATUS LIKE 'Handler_read%';

--echo Optimization also work with several MATCH expressions
FLUSH STATUS;

SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score1,
       MATCH(title, text) AGAINST ('mysql') AS score2
FROM wp
WHERE MATCH(title, text) AGAINST ('database');

SHOW SESSION STATUS LIKE 'Handler_read%';

--echo Optimization does not apply if sorting on a different MATCH expressions
--echo from the one used to access the
FLUSH STATUS;

SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score1,
       MATCH(title, text) AGAINST ('mysql') AS score2
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
ORDER BY score2 DESC;

SHOW SESSION STATUS LIKE 'Handler_read%';

FLUSH STATUS;

--echo Optimization does not apply for GROUP BY
SET @save_mode = @@sql_mode;
SET sql_mode = (select replace(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
GROUP BY score;

SHOW SESSION STATUS LIKE 'Handler_read%';
SET sql_mode = @save_mode;

#
#  Putting all three optimizations together
#
--echo No sorting and no table access with LIMIT clause and only information
--echo from FTS result
FLUSH STATUS;

SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
FROM wp
ORDER BY score DESC LIMIT 2;

SHOW STATUS LIKE 'Handler_read%';
SHOW SESSION STATUS LIKE 'Sort%';

#
# Count optimization
#
let $query =
SELECT COUNT(*)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);

--echo If count optimization applies, EXPLAIN shows
--echo "Select tables optimized away."
eval EXPLAIN $query;
FLUSH STATUS;
eval $query;
--echo Verify that there was no table access
SHOW STATUS LIKE 'Handler_read%';

let $query =
SELECT COUNT(title)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);

--echo Optimization applies also to COUNT(expr) as long as expr is not nullable
eval EXPLAIN $query;
eval $query;

let $query =
SELECT count(*)
FROM wp, t1
WHERE MATCH(title, text) AGAINST ('database');

--echo Optimization does not apply if not a single table query.
eval EXPLAIN $query;
eval $query;

let $query =
SELECT COUNT(title)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE) > 0;

--echo Optimization does not apply if MATCH is part of an expression
eval EXPLAIN $query;
eval $query;

let $query =
SELECT COUNT(title)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE) > 0;

--echo Optimization does not apply if MATCH is part of an expression
eval EXPLAIN $query;
eval $query;

let $query =
SELECT COUNT(dummy)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);

--echo Optimization does not apply if COUNT expression is nullable
eval EXPLAIN $query;
eval $query;

#
#  Verify that the queries optimized for InnoDB works with QUERY EXPANSION
#

# Query will also avoid sorting when query expansion is used
FLUSH STATUS;
--sorted_result
SELECT MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score,
title
FROM wp
WHERE MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION)
ORDER BY score DESC;
SHOW SESSION STATUS LIKE 'Sort%';

# Check ORDER BY/LIMIT query with no WHERE clause
FLUSH STATUS;
SELECT title,
       MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score
FROM wp
ORDER BY score DESC LIMIT 2;
SHOW SESSION STATUS LIKE 'Sort%';

# Check query where FTS result is "covering"
FLUSH STATUS;
SELECT FTS_DOC_ID docid,
       MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database');
SHOW SESSION STATUS LIKE 'Handler_read%';

# Check the combination of all three
FLUSH STATUS;
SELECT FTS_DOC_ID docid,
       MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score
FROM wp
ORDER BY score DESC LIMIT 2;
SHOW STATUS LIKE 'Handler_read%';
SHOW SESSION STATUS LIKE 'Sort%';

# Check the count optimization
let $query =
SELECT COUNT(*)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' WITH QUERY EXPANSION);
eval EXPLAIN $query;
FLUSH STATUS;
eval $query;
SHOW STATUS LIKE 'Handler_read%';

#
#  Verify that the queries optimized for InnoDB works with BOOLEAN MODE
#

# Query will also avoid sorting when Boolean mode is used
FLUSH STATUS;
--sorted_result
SELECT MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) score,
title
FROM wp
WHERE MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE)
ORDER BY score DESC;
SHOW SESSION STATUS LIKE 'Sort%';

# Check ORDER BY/LIMIT query with no WHERE clause
FLUSH STATUS;
--sorted_result
SELECT MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) score,
title
FROM wp
ORDER BY score DESC;
SHOW SESSION STATUS LIKE 'Sort%';

# Check query where FTS result is "covering"
FLUSH STATUS;
SELECT FTS_DOC_ID docid,
       MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('+MySQL -database');
SHOW SESSION STATUS LIKE 'Handler_read%';

# Check the combination of all three
FLUSH STATUS;
SELECT FTS_DOC_ID docid,
       MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
FROM wp
ORDER BY score DESC LIMIT 1;
SHOW STATUS LIKE 'Handler_read%';
SHOW SESSION STATUS LIKE 'Sort%';

# Check the count optimization
let $query =
SELECT COUNT(*)
FROM wp
WHERE MATCH(title,text) AGAINST ('+MySQL -database' IN BOOLEAN MODE);
eval EXPLAIN $query;
FLUSH STATUS;
eval $query;
SHOW STATUS LIKE 'Handler_read%';


#
#  Verify that the queries optimized for InnoDB works with
#  BOOLEAN proximity search
#

# Query will also avoid sorting when Boolean mode is used
FLUSH STATUS;
SELECT title,
       MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE)
ORDER BY score DESC;
SHOW SESSION STATUS LIKE 'Sort%';

# Check ORDER BY/LIMIT query with no WHERE clause
FLUSH STATUS;
SELECT title,
       MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score
FROM wp
ORDER BY score DESC LIMIT 1;
SHOW SESSION STATUS LIKE 'Sort%';

# Check query where FTS result is "covering"
FLUSH STATUS;
SELECT FTS_DOC_ID docid,
       MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('"MySQL database"@5');
SHOW SESSION STATUS LIKE 'Handler_read%';

# Check the combination of all three
FLUSH STATUS;
SELECT FTS_DOC_ID docid,
       MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score
FROM wp
ORDER BY score DESC LIMIT 1;
SHOW STATUS LIKE 'Handler_read%';
SHOW SESSION STATUS LIKE 'Sort%';

# Check the count optimization
let $query =
SELECT COUNT(*)
FROM wp
WHERE MATCH(title,text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE);
eval EXPLAIN $query;
FLUSH STATUS;
eval $query;
SHOW STATUS LIKE 'Handler_read%';

#
# Check that nothing goes wrong when combining different modes
#
SELECT title,
       MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION)
ORDER BY score DESC, title ASC;

SELECT title,
       MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('MySQL database' WITH QUERY EXPANSION)
ORDER BY score DESC, title ASC;

SELECT title,
       MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE)
ORDER BY score DESC, title ASC;


#
#  Verify that the queries optimized for InnoDB still works with MyISAM
#
ALTER TABLE wp ENGINE=myisam;

# Check avoid sorting query
FLUSH STATUS;
SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
ORDER BY score DESC;
SHOW SESSION STATUS LIKE 'Sort%';

# Check ORDER BY/LIMIT query with no WHERE clause
FLUSH STATUS;
SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp
ORDER BY score DESC LIMIT 2;
SHOW SESSION STATUS LIKE 'Sort%';

# Check query where FTS result is "covering"
FLUSH STATUS;
SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database');
SHOW SESSION STATUS LIKE 'Handler_read%';

# Check the combination of all three
FLUSH STATUS;
SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
FROM wp
ORDER BY score DESC LIMIT 2;
SHOW STATUS LIKE 'Handler_read%';
SHOW SESSION STATUS LIKE 'Sort%';

# Check the count optimization
let $query =
SELECT COUNT(*)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
eval EXPLAIN $query;
FLUSH STATUS;
eval $query;
SHOW STATUS LIKE 'Handler_read%';


DROP TABLE wp, t1;

--enable_ps2_protocol

# Tests for FT hints.

CREATE TABLE t1
(
  FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) DEFAULT '',
  text MEDIUMTEXT ,
  PRIMARY KEY (FTS_DOC_ID),
  UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
  FULLTEXT KEY ft_idx (title,text)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO t1 (title, text) VALUES
  ('MySQL Tutorial','DBMS stands for MySQL 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 database','In the following database to database comparison ...'),
  ('MySQL Security','When configured properly, MySQL ...'),
  ('InnoDB', 'InnoDB is a transaction-safe (ACID compliant) storage engine'),
  ('MySQL is a database management system', 'A database is a structured collection of data...'),
  ('MySQL databases are relational', 'A relational database stores data in separate tables rather than putting all the data in one big storeroom...'),
  ('MySQL software is Open Source', 'Open Source means that it is possible for anyone to use and modify the software...'),
  ('The MySQL Database Server is very fast, reliable, scalable, and easy to use', 'MySQL Server can run comfortably on a desktop or laptop...'),
  ('MySQL Server works in client/server or embedded systems', 'The MySQL Database Software is a client/server system...'),
  ('MyISAM', 'MyISAM is based on the older (and no longer available) ISAM storage engine but has many useful extensions'),
  ('A large amount of contributed MySQL software is available', 'MySQL Server has a practical set of features developed in close cooperation with our users'),
  (NULL,NULL);

-- disable_result_log
ANALYZE TABLE t1;
-- enable_result_log

--echo # No ranking

EXPLAIN
SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE);
SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE);

# Atm opt_sum_query does not support COUNT optimization if
# ORDER BY is present. TODO: fix it.
EXPLAIN
SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('data*' IN BOOLEAN MODE) ORDER BY title LIMIT 3;
SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('data*' IN BOOLEAN MODE) ORDER BY title LIMIT 3;

EXPLAIN
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);

EXPLAIN
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION);
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION);

EXPLAIN
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE);
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE);

# check case with 'for' stopword
EXPLAIN SELECT FTS_DOC_ID  FROM t1
WHERE MATCH(title, text) AGAINST ('+for' IN BOOLEAN MODE);
SELECT FTS_DOC_ID  FROM t1
WHERE MATCH(title, text) AGAINST ('+for' IN BOOLEAN MODE);


--echo # No sorting by rank

EXPLAIN SELECT FTS_DOC_ID, TITLE FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY title;
SELECT FTS_DOC_ID, TITLE FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY title;

EXPLAIN SELECT FTS_DOC_ID FROM t1
WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);
SELECT FTS_DOC_ID  FROM t1
WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);

EXPLAIN
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title;
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title;

EXPLAIN
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title;
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title;

--echo # LIMIT optimization

EXPLAIN SELECT FTS_DOC_ID, TITLE FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
LIMIT 3;
SELECT FTS_DOC_ID, TITLE FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
LIMIT 3;

EXPLAIN SELECT FTS_DOC_ID FROM t1
WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
LIMIT 3;
SELECT FTS_DOC_ID  FROM t1
WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
LIMIT 3;

EXPLAIN SELECT FTS_DOC_ID FROM t1
WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
ORDER BY title
LIMIT 3;
SELECT FTS_DOC_ID  FROM t1
WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
ORDER BY title
LIMIT 3;


EXPLAIN
SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title LIMIT 1;
SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title LIMIT 1;

EXPLAIN
SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title LIMIT 1;
SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title LIMIT 1;

EXPLAIN
SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank
FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY rank, FTS_DOC_ID
LIMIT 3;
SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank
FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY rank, FTS_DOC_ID
LIMIT 3;

EXPLAIN
SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank
FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY rank DESC, FTS_DOC_ID ASC
LIMIT 3;
SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank
FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY rank DESC, FTS_DOC_ID ASC
LIMIT 3;

EXPLAIN SELECT FTS_DOC_ID,  MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
FROM t1
ORDER BY rank DESC
LIMIT 2;
SELECT FTS_DOC_ID,  MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
FROM t1
ORDER BY rank DESC
LIMIT 2;

EXPLAIN SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank
FROM t1
ORDER BY rank DESC
LIMIT 3;
SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank
FROM t1
ORDER BY rank DESC
LIMIT 3;

EXPLAIN SELECT FTS_DOC_ID,  MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE)
ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE)  DESC,
FTS_DOC_ID ASC;
SELECT FTS_DOC_ID,  MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE)
ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE)  DESC,
FTS_DOC_ID ASC;

EXPLAIN SELECT FTS_DOC_ID,  MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) and FTS_DOC_ID > 1
ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE)  DESC
LIMIT 2;
SELECT FTS_DOC_ID,  MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) and FTS_DOC_ID > 1
ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE)  DESC
LIMIT 2;


EXPLAIN
SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
ORDER BY rank
LIMIT 1;
SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
ORDER BY rank
LIMIT 1;

EXPLAIN
SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
ORDER BY rank DESC
LIMIT 1;
SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
ORDER BY rank DESC
LIMIT 1;


EXPLAIN
SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
ORDER BY  MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
LIMIT 1;
SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
ORDER BY  MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) DESC
LIMIT 1;

--echo # WHERE optimization on MATCH > 'some_rank'

EXPLAIN SELECT FTS_DOC_ID FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) > 0.1;

SELECT FTS_DOC_ID FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) > 0.1;


--echo # additional test for correct behaviour

EXPLAIN SELECT * FROM t1 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC LIMIT 10;

SELECT FTS_DOC_ID FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) AND
MATCH (title, text) AGAINST ('mysql' IN NATURAL LANGUAGE MODE)
LIMIT 6;

--echo # test OR condition

SELECT FTS_DOC_ID
FROM t1
WHERE MATCH(title, text) AGAINST ('database')
  OR MATCH(title, text) AGAINST ('mysql')
ORDER BY  MATCH(title, text) AGAINST ('database') DESC, FTS_DOC_ID ASC;

EXPLAIN SELECT FTS_DOC_ID
FROM t1
WHERE MATCH(title, text) AGAINST ('database')
  OR MATCH(title, text) AGAINST ('mysql')
ORDER BY  MATCH(title, text) AGAINST ('database') DESC, FTS_DOC_ID ASC;

--echo # MATCH and GROUP BY, DISTINCT

SET sql_mode = (select replace(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

EXPLAIN SELECT FTS_DOC_ID FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
GROUP BY FTS_DOC_ID
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;
SELECT FTS_DOC_ID FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
GROUP BY FTS_DOC_ID
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;

EXPLAIN SELECT FTS_DOC_ID FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
GROUP BY title
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;

SELECT FTS_DOC_ID FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
GROUP BY title
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;

EXPLAIN SELECT MAX(FTS_DOC_ID) FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;
SELECT MAX(FTS_DOC_ID) FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;

EXPLAIN SELECT DISTINCT(title) FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;
SELECT DISTINCT(title) FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;

EXPLAIN SELECT DISTINCT(FTS_DOC_ID) FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;
SELECT DISTINCT(FTS_DOC_ID) FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;

SET sql_mode = @save_mode;

--echo # FTS index access

SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
FROM t1
ORDER BY rank DESC
LIMIT 2;
EXPLAIN SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
FROM t1
ORDER BY rank DESC
LIMIT 2;

SELECT a.FTS_DOC_ID, b.FTS_DOC_ID
FROM t1 a, t1 b
WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and
      MATCH(b.title, b.text) AGAINST ('+mysql' IN BOOLEAN MODE) and
      a.FTS_DOC_ID = b.FTS_DOC_ID;
EXPLAIN SELECT a.FTS_DOC_ID, b.FTS_DOC_ID
FROM t1 a, t1 b
WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and
      MATCH(b.title, b.text) AGAINST ('+mysql' IN BOOLEAN MODE)  and
      a.FTS_DOC_ID = b.FTS_DOC_ID;

SELECT a.FTS_DOC_ID, MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE),
       b.FTS_DOC_ID, MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE)
FROM t1 a, t1 b
WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and
      MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE);

EXPLAIN SELECT a.FTS_DOC_ID, MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE),
       b.FTS_DOC_ID, MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE)
FROM t1 a, t1 b
WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and
      MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE);

# Index only access by non-FTS index

EXPLAIN SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ("data*" IN BOOLEAN MODE) * 100;
SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ("data*" IN BOOLEAN MODE) * 100
FROM t1 WHERE MATCH(title, text) AGAINST ("data*" IN BOOLEAN MODE) * 100;
# Run query returning null record
SELECT * FROM t1 WHERE title IS NULL AND text IS NULL;

# More testing of index only access by non-FTS index

CREATE TABLE t2 SELECT FTS_DOC_ID as doc_id, title, text FROM t1;
ALTER TABLE t2 ADD PRIMARY KEY (doc_id);
ALTER TABLE t2 ADD FULLTEXT KEY ft_idx (title,text);

-- disable_result_log
ANALYZE TABLE t2;
-- enable_result_log

# No index access
EXPLAIN SELECT DOC_ID FROM t2 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;
SELECT DOC_ID FROM t2 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;
# Index access
EXPLAIN SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;
SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;

DROP TABLE t1, t2;

--echo "Check hints with uft8 charset for 2 cases"
set names utf8;
EVAL CREATE TABLE t1 (
        FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        text TEXT
        ) CHARACTER SET = utf8, ENGINE=InnoDB;

INSERT INTO t1 (title, text) VALUES
('Я могу есть стекло', 'оно мне не вредит'),
('Мога да ям стъкло', 'то не ми вреди'),
('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'),
('Příliš žluťoučký kůň', 'úpěl ďábelské kódy'),
('Sævör grét', 'áðan því úlpan var ónýt'),
('うゐのおくやま','けふこえて'),
('いろはにほへど　ちりぬる','あさきゆめみじ　ゑひもせず');
CREATE FULLTEXT INDEX idx on t1 (title, text);

--echo # No ranking

EXPLAIN
SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('вредит' IN NATURAL LANGUAGE MODE);
SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('вредит' IN NATURAL LANGUAGE MODE);

EXPLAIN
SELECT * FROM t1 WHERE MATCH(title, text) AGAINST ("оно" WITH QUERY EXPANSION);
SELECT * FROM t1 WHERE MATCH(title, text) AGAINST ("оно" WITH QUERY EXPANSION);
--echo # No sorting by rank
EXPLAIN SELECT FTS_DOC_ID FROM t1
WHERE MATCH(title, text) AGAINST ('+(Мога τίποτα)' IN BOOLEAN MODE);
SELECT FTS_DOC_ID  FROM t1
WHERE MATCH(title, text) AGAINST ('+(Мога τίποτα)' IN BOOLEAN MODE);
DROP TABLE t1;


--echo #
--echo # Bug #18924341 CRASH IN TEST_IF_SKIP_SORT_ORDER, GROUP BY MATCH AGAINST DESC
--echo #

CREATE TABLE t1 (f1 CHAR(1), FULLTEXT KEY (f1));
SELECT 1 FROM t1 NATURAL JOIN t1 a GROUP BY MATCH(t1.f1) AGAINST ("1") DESC;
DROP TABLE t1;


--echo #
--echo # Bug#20261601 ASSERTION FAILED: !FIRST_QEP_TAB->TABLE()->NO_KEYREAD
--echo #

CREATE TABLE t1(a INT PRIMARY KEY);
INSERT INTO t1 VALUES(1),(2);
--error ER_FT_MATCHING_KEY_NOT_FOUND
SELECT (SELECT MATCH(`a`)AGAINST('1') FROM t1) FROM t1;
SELECT 1, a IN (SELECT a FROM t1) FROM t1;
DROP TABLE t1;

--echo #
--echo # Bug#20442572 ASSERTION `!FIRST_QEP_TAB->TABLE()->NO_KEYREAD' FAILED.
--echo # Bug#75688 Assertion `!first_qep_tab->table()->no_keyread' failed.
--echo #

CREATE TABLE t1(a INT,b POINT NOT NULL,KEY(a));

HANDLER t1 OPEN;
#--error ER_FT_MATCHING_KEY_NOT_FOUND
select * from t1 where MATCH a,b AGAINST('"Now sUPPort"' IN BOOLEAN MODE);
prepare stmt1 from "truncate t1";
SELECT a IN(SELECT a FROM t1)FROM t1;

deallocate prepare stmt1;
DROP TABLE t1;

--echo #
--echo # Bug #20685427 INVALID WRITE OF FREED MEMORY IN ITEM_FUNC_MATCH::CLEANUP
--echo #

CREATE TABLE t1(a TEXT CHARSET LATIN1, FULLTEXT KEY(a)) ENGINE=INNODB;
--error ER_FT_MATCHING_KEY_NOT_FOUND
SELECT MATCH(a) AGAINST ('') FROM (SELECT a FROM t1 LIMIT 1) q;
DROP TABLE t1;

--echo #
--echo # Bug#21140067 EXPLAIN .. MATCH AGAINST: ASSERTION FAILED: TO <= END
--echo #

CREATE TABLE t1(f1 CHAR(1) CHARSET latin1, FULLTEXT(f1)) ENGINE=INNODB;
EXPLAIN SELECT 1 FROM t1 WHERE 1.238585e+308 <= MATCH(f1) AGAINST ('1' IN BOOLEAN MODE);

EXPLAIN FORMAT = JSON SELECT 1 FROM t1 WHERE 1.238585e+308 <= MATCH(f1) AGAINST ('1' IN BOOLEAN MODE);

DROP TABLE t1;

--echo #
--echo # Bug#21140088 MATCH AGAINST: ASSERTION FAILED: !TABLE || (!TABLE->READ_SET || BITMAP_IS_SET
--echo #

SET sql_mode='';
CREATE TABLE t1(a INT) ENGINE=INNODB;
CREATE TABLE t2(b TEXT CHARSET LATIN1, FULLTEXT(b), PRIMARY KEY(b(10))) ENGINE=INNODB;
INSERT INTO t2 VALUES ('a'),('b');
--error ER_WRONG_ARGUMENTS
SELECT NOT EXISTS (SELECT MATCH(b) AGAINST ('1') FROM t1) FROM t2 GROUP BY "a";
DROP TABLE t1, t2;

CREATE TABLE t1(a INT) ENGINE=MyISAM;
CREATE TABLE t2(b TEXT CHARSET LATIN1, FULLTEXT(b), PRIMARY KEY(b(10))) ENGINE=MyISAM;
INSERT INTO t2 VALUES ('a'),('b');
#--error ER_WRONG_ARGUMENTS
SELECT NOT EXISTS (SELECT MATCH(b) AGAINST ('1' in BOOLEAN MODE) FROM t1) FROM t2 GROUP BY "a";
DROP TABLE t1, t2;

SET sql_mode=default;

--echo #
--echo # Bug#21140039 ASSERTION FAILED: !FIRST_QEP_TAB->TABLE()->NO_KEYREAD MATCH AGAINST.....
--echo #

CREATE TABLE t1
(
 a INT,
 b INT,
 c CHAR(1) CHARSET latin1,
 PRIMARY KEY (b,a),
 FULLTEXT KEY (c)
) ENGINE=INNODB;
SELECT "a" NOT IN(SELECT b FROM t1 WHERE MATCH(c) AGAINST ('a' IN BOOLEAN MODE));
DROP TABLE t1;

--echo #
--echo # Bug#21300774 ASSERT `!INIT_FTFUNCS(THD, SELECT_LEX)` IN JOIN::RESET AT SQL/SQL_SELECT.CC:874
--echo #

CREATE TABLE t1 (f1 INT);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
CREATE TABLE t2 (ft TEXT, FULLTEXT KEY ft(ft));
INSERT INTO t2 VALUES ('abc');
INSERT INTO t2 VALUES ('def');

--error ER_SUBQUERY_NO_1_ROW
UPDATE t1 SET f1 =
(SELECT t1.f1 FROM t2 WHERE NOT TRUE AND
 MATCH (ft) AGAINST ((SELECT 'xyz' FROM t2)));

DROP TABLE t1, t2;

--echo #
--echo # Bug#22679209: FULL-TEXT QUERIES WITH ADDITIONAL SECONDARY INDEX
--echo #               GIVES NULL OR ZERO ROWS
--echo #

CREATE TABLE t1 (
f1 INTEGER,
title varchar(255),
body mediumtext,
KEY f1 (f1),
FULLTEXT KEY title (title),
FULLTEXT KEY body (body)
) ENGINE=InnoDB;

INSERT INTO t1 VALUES
(1, 'Insert into table', 'insert into table select from'),
(1, 'Delete from table', 'insert into table select from'),
(1, 'Update',            'perform update'),
(2, 'Insert into table', 'insert into table select from'),
( 2, 'Delete from table', 'some body text here'),
( 2, 'Update',            'perform update'),
( 3, 'Insert into table', 'insert into table select from'),
( 3, 'Delete from table', 'some body text here');

SELECT f1 FROM t1 WHERE f1=1 AND
(MATCH (title) AGAINST ('table' IN BOOLEAN MODE) OR
MATCH (body) AGAINST ('table' IN BOOLEAN MODE));

DROP TABLE t1;

--echo # End of test for Bug#22679209