From: Date: November 10 2008 12:44pm Subject: bzr commit into mysql-5.1 branch (svoj:2705) Bug#38842 List-Archive: http://lists.mysql.com/commits/58332 X-Bug: 38842 Message-Id: <20081110114409.96BBB41CED0@june.myoffice.izhnet.ru> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7BIT #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;