BUG#38842 - Fix for 25951 seems incorrect

With fix for bug 25951 index hints are ignored for fulltext
searches, as handling of fulltext indexes is different from
handling regular indexes. Meaning it is not possible to
implement true index hints support for fulltext indexes within
the scope of current fulltext architecture.

The problem is that prior to fix for bug 25951, some useful
index hints still could be given for boolean mode searches.

This patch implements special index hints support for fulltext
indexes with the following characteristics:
- all index hints are still ignored for NLQ mode searches -
  it cannot work without an index;
- for 5.1 and up index hints FOR ORDER BY and FOR GROUP BY are
  still ignored for fulltext indexes;
- boolean mode searches honor USE/FORCE/IGNORE INDEX hints;
- as opposed to index hints for regular indexes, index hints
  for fulltext BOOLEAN mode searches affect the usage of the
  index for the whole query.

mysql-test/r/fulltext.result:
  A test case for BUG#38842.
mysql-test/t/fulltext.test:
  A test case for BUG#38842.
sql/item_func.cc:
  For boolean mode searches, which can work without fulltext
  index, use keys_in_use_for_query bitmap instead of keys_in_use.
  
  The effect is that boolean mode searches now honor index hints.

1 files changed, 27 insertions, 0 deletions

diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test
index e5f1db14b7f..fa087d89efb 100644
--- a/mysql-test/t/fulltext.test
+++ b/mysql-test/t/fulltext.test
@@ -396,3 +396,30 @@ SELECT * FROM t1 WHERE MATCH(a) AGAINST ('"aaaa"' IN BOOLEAN MODE);
 DROP TABLE t1;
 
 # End of 4.1 tests
+
+#
+# BUG#38842 - Fix for 25951 seems incorrect
+#
+CREATE TABLE t1 (a VARCHAR(255), b INT, FULLTEXT(a), KEY(b));
+INSERT INTO t1 VALUES('test', 1),('test', 1),('test', 1),('test', 1),
+                     ('test', 1),('test', 2),('test', 3),('test', 4);
+
+EXPLAIN SELECT * FROM t1
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+
+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;
+
+EXPLAIN SELECT * FROM t1 IGNORE INDEX(a)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+
+EXPLAIN SELECT * FROM t1 USE INDEX(b)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+
+EXPLAIN SELECT * FROM t1 FORCE INDEX(b)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+
+DROP TABLE t1;