bzr commit into mysql-5.1 branch (svoj:2705) Bug#38842

[Sergey Vojtovich]

#At file:///home/svoj/devel/bzr-mysql/mysql-5.1-bugteam-bug38842/

 2705 Sergey Vojtovich	2008-11-10
      BUG#38842 - Fix for 25951 seems incorrect
      
      Prior to fix for bug 25951 index hints were honored for fulltext
      indexes, though most of them were not working as expected. With
      fix for bug 25951 index hints have no effect for fulltext
      indexes, which is correct, but doesn't allow users to provide
      some really useful hints to optimizer.
      
      Though it is not that trivial to make true index hints support
      for fulltext indexes, 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 index;
      - index hints FOR ORDER BY and FOR GROUP BY are still ignored
        for fulltext indexes;
      - USE/FORCE index hints are still ignored for fulltext indexes;
      - BOOLEAN mode searches honor IGNORE INDEX hint;
      - BOOLEAN mode searches honor FORCE/USE INDEX hints for regular
        indexes.
modified:
  mysql-test/r/fulltext.result
  mysql-test/t/fulltext.test
  sql/item_func.cc

per-file messages:
  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 ignore
    fulltext index and force/use regular index hints.
=== modified file 'mysql-test/r/fulltext.result'
--- a/mysql-test/r/fulltext.result	2008-02-12 19:09:16 +0000
+++ b/mysql-test/r/fulltext.result	2008-11-10 11:43:49 +0000
@@ -494,3 +494,55 @@ SELECT a FROM t1 WHERE MATCH a AGAINST (
 a
 City Of God
 DROP TABLE t1;
+CREATE TABLE t1 (a VARCHAR(255), b INT, FULLTEXT(a), KEY(b));
+INSERT INTO t1 VALUES('test', 1),('test', 1),('test', 1),('test', 1),
+('test', 1),('test', 2),('test', 3),('test', 4);
+EXPLAIN SELECT * FROM t1
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	fulltext	b,a	a	0		1	Using where
+EXPLAIN SELECT * FROM t1 USE INDEX(a)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	fulltext	a	a	0		1	Using where
+EXPLAIN SELECT * FROM t1 FORCE INDEX(a)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	fulltext	a	a	0		1	Using where
+EXPLAIN SELECT * FROM t1 IGNORE INDEX(a)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	b	b	5	const	4	Using where
+EXPLAIN SELECT * FROM t1 USE INDEX FOR ORDER BY(a)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	fulltext	b,a	a	0		1	Using where
+EXPLAIN SELECT * FROM t1 FORCE INDEX FOR ORDER BY(a)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	fulltext	b,a	a	0		1	Using where
+EXPLAIN SELECT * FROM t1 IGNORE INDEX FOR ORDER BY(a)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	fulltext	b,a	a	0		1	Using where
+EXPLAIN SELECT * FROM t1 USE INDEX FOR GROUP BY(a)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	fulltext	b,a	a	0		1	Using where
+EXPLAIN SELECT * FROM t1 FORCE INDEX FOR GROUP BY(a)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	fulltext	b,a	a	0		1	Using where
+EXPLAIN SELECT * FROM t1 IGNORE INDEX FOR GROUP BY(a)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	fulltext	b,a	a	0		1	Using where
+EXPLAIN SELECT * FROM t1 USE INDEX(b)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	b	b	5	const	4	Using where
+EXPLAIN SELECT * FROM t1 FORCE INDEX(b)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	b	b	5	const	4	Using where
+DROP TABLE t1;

=== modified file 'mysql-test/t/fulltext.test'
--- a/mysql-test/t/fulltext.test	2007-11-02 09:20:38 +0000
+++ b/mysql-test/t/fulltext.test	2008-11-10 11:43:49 +0000
@@ -417,4 +417,49 @@ SELECT a FROM t1 WHERE MATCH a AGAINST (
 SELECT a FROM t1 WHERE MATCH a AGAINST ('+city* of*' IN BOOLEAN MODE);
 DROP TABLE t1;
 
+#
+# 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 FOR ORDER BY(a)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+
+EXPLAIN SELECT * FROM t1 FORCE INDEX FOR ORDER BY(a)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+
+EXPLAIN SELECT * FROM t1 IGNORE INDEX FOR ORDER BY(a)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+
+EXPLAIN SELECT * FROM t1 USE INDEX FOR GROUP BY(a)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+
+EXPLAIN SELECT * FROM t1 FORCE INDEX FOR GROUP BY(a)
+WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
+
+EXPLAIN SELECT * FROM t1 IGNORE INDEX FOR GROUP BY(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;
+
 # End of 4.1 tests

=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc	2008-10-10 09:52:23 +0000
+++ b/sql/item_func.cc	2008-11-10 11:43:49 +0000
@@ -5381,7 +5381,8 @@ bool Item_func_match::fix_index()
   for (keynr=0 ; keynr < table->s->keys ; keynr++)
   {
     if ((table->key_info[keynr].flags & HA_FULLTEXT) &&
-        (table->s->keys_in_use.is_set(keynr)))
+        (flags & FT_BOOL ? table->keys_in_use_for_query.is_set(keynr) :
+                           table->s->keys_in_use.is_set(keynr)))
     {
       ft_to_key[fts]=keynr;
       ft_cnt[fts]=0;