MySQL Bugs: #45130: Incorrect results with multiple MATCH() against a table with no fulltext indexes

Bug #45130	Incorrect results with multiple MATCH() against a table with no fulltext indexes
Submitted:	27 May 2009 13:03	Modified:	10 Sep 2009 9:25
Reporter:	Sergey Vojtovich	Email Updates:
Status:	In progress	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S3 (Non-critical)
Version:	5.0 and later	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Performing boolean fulltext search on different columns set without fulltext index may cause incorrect results.

Found while analysing BUG#37740.

Another problem, which may be fixed within the scope of this bug, is that equal MATCH() calls, which are present in both SELECT and ORDER BY expressions are evaluated twice.

The problem is around Item_func_match::eq(). It doesn't distinguish between different column sets when there is no index.

How to repeat:
CREATE TABLE t1(a TEXT, b BLOB);
INSERT INTO t1 VALUES('TEST1','TEST1');
SELECT MATCH(a) AGAINST('test1' IN BOOLEAN MODE), MATCH(b) AGAINST('test1' IN BOOLEAN MODE) FROM t1;
SELECT MATCH(a) AGAINST('test1' IN BOOLEAN MODE) FROM t1;
SELECT MATCH(b) AGAINST('test1' IN BOOLEAN MODE) FROM t1;

---

CREATE TABLE t1(a TEXT, b BLOB);
INSERT INTO t1 VALUES('TEST1','TEST1');
SELECT MATCH(a) AGAINST('test1' IN BOOLEAN MODE), MATCH(b) AGAINST('test1' IN BOOLEAN MODE) FROM t1;
MATCH(a) AGAINST('test1' IN BOOLEAN MODE)	MATCH(b) AGAINST('test1' IN BOOLEAN MODE)
0	0
SELECT MATCH(a) AGAINST('test1' IN BOOLEAN MODE) FROM t1;
MATCH(a) AGAINST('test1' IN BOOLEAN MODE)
1
SELECT MATCH(b) AGAINST('test1' IN BOOLEAN MODE) FROM t1;
MATCH(b) AGAINST('test1' IN BOOLEAN MODE)
0

Suggested fix:
Make Item_func_match::eq() to distinguish between different column sets when there is no index.

Thank you for the bug report. Verified as described.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/79993

2797 Anurag Shekhar	2009-08-04
      Bug #45130 Incorrect results with multiple MATCH() against a table with 
                 no fulltext indexes
      
      The problem was with Item_func_match::eq(). While checking for for key
      it was considering all the columns in the serach set part of one key.
      Changing this to treat individual columns fixed the problem.
     @ mysql-test/r/fulltext.result
        Updated results with added test case for 45130.
     @ mysql-test/t/fulltext.test
        Updated with test case for 45130.
     @ sql/item_func.cc
        Modified to use super class's eq method except for comparing item flags.
     @ sql/sql_base.cc
        Modified setup_ftfuncs() and init_ftfuncs() to skip and remove unfixed
        fields while fixing index and order by.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/81396

2790 Anurag Shekhar	2009-08-24
      Bug #45130 Incorrect results with multiple MATCH() against a table with 
               no fulltext indexes
      
      The problem was with Item_func_match::eq(). While checking for for key
      it was considering all the columns in the serach set part of one key.
      Changing this to treat individual columns fixed the problem.
     @ mysql-test/r/fulltext.result
        Updated results with added test case for 45130.
     @ mysql-test/t/fulltext.test
        Updated with test case for 45130.
     @ sql/item_func.cc
        Modified to skip if the item is unfixed and to use super class's eq 
        method except for comparing item flags.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/81683

2796 Anurag Shekhar	2009-08-27
      Bug #45130 Incorrect results with multiple MATCH() against a table with 
                 no fulltext indexes
      
      When multiple MATCH is performed in one query (against a table 
      without fulltext index)  the server is not distinguishing between 
      column sets for individual MATCH resulting in unexpected output of 
      the Match.
      
      The problem was with Item_func_match::eq(). While checking for for key
      it was considering all the columns in the serach set part of one key.
      Changing this to treat individual columns fixed the problem.
     @ mysql-test/r/fulltext.result
        Updated results with added test case for 45130.
     @ mysql-test/t/fulltext.test
        Updated with test case for 45130.
     @ sql/item_func.cc
        Modified to skip if the item is unfixed and to use super class's eq 
        method except for comparing item flags.