MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Georgi Kodinov Date:November 10 2009 1:08pm
Subject:bzr commit into mysql-5.1-bugteam branch (joro:3195) Bug#47930
View as plain text  
#At file:///home/kgeorge/mysql/work/B47930-5.1-bugteam/ based on revid:joro@stripped

 3195 Georgi Kodinov	2009-10-27
      Bug #47930: MATCH IN BOOLEAN MODE returns too many results
        inside subquery
      
      Re-setting a fulltext index was a no-operation if not all
      the matches of a search were consumed by reading them.
      This was preventing a joined table using a fulltext index
      in a subquery that requires only 1 row of output (e.g. EXISTS) 
      from working correctly because the second execution of the 
      sub-query has the fulltext index cursor in a wrong state and
      was not finding results.
      Fixed by making the re-init code _ftb_init_index_search() 
      to re-set open cursors in addition to depleted ones.

    modified:
      mysql-test/r/fulltext.result
      mysql-test/t/fulltext.test
      storage/myisam/ft_boolean_search.c
=== modified file 'mysql-test/r/fulltext.result'
--- a/mysql-test/r/fulltext.result	2009-07-15 10:13:45 +0000
+++ b/mysql-test/r/fulltext.result	2009-10-27 12:43:12 +0000
@@ -551,3 +551,42 @@ EXECUTE s;
 MATCH (col) AGAINST('findme')
 DEALLOCATE PREPARE s;
 DROP TABLE t1;
+#
+# Bug #47930: MATCH IN BOOLEAN MODE returns too many results 
+#  inside subquery
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (1), (2);
+CREATE TABLE t2 (a int, b2 char(10), FULLTEXT KEY b2 (b2));
+INSERT INTO t2 VALUES (1,'Scargill');
+CREATE TABLE t3 (a int, b int);
+INSERT INTO t3 VALUES (1,1), (2,1);
+# t2 should use full text index
+EXPLAIN
+SELECT count(*) FROM t1 WHERE 
+not exists(
+SELECT 1 FROM t2, t3
+WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2	fulltext	b2	b2	0		1	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+# should return 0
+SELECT count(*) FROM t1 WHERE 
+not exists(
+SELECT 1 FROM t2, t3
+WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
+);
+count(*)
+0
+# should return 0
+SELECT count(*) FROM t1 WHERE 
+not exists(
+SELECT 1 FROM t2 IGNORE INDEX (b2), t3
+WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
+);
+count(*)
+0
+DROP TABLE t1,t2,t3;
+End of 5.1 tests

=== modified file 'mysql-test/t/fulltext.test'
--- a/mysql-test/t/fulltext.test	2009-07-15 10:13:45 +0000
+++ b/mysql-test/t/fulltext.test	2009-10-27 12:43:12 +0000
@@ -484,3 +484,44 @@ PREPARE s FROM 
 EXECUTE s;
 DEALLOCATE PREPARE s;
 DROP TABLE t1;
+
+--echo #
+--echo # Bug #47930: MATCH IN BOOLEAN MODE returns too many results 
+--echo #  inside subquery
+--echo #
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (1), (2);
+
+CREATE TABLE t2 (a int, b2 char(10), FULLTEXT KEY b2 (b2));
+INSERT INTO t2 VALUES (1,'Scargill');
+
+CREATE TABLE t3 (a int, b int);
+INSERT INTO t3 VALUES (1,1), (2,1);
+
+--echo # t2 should use full text index
+EXPLAIN
+SELECT count(*) FROM t1 WHERE 
+  not exists(
+   SELECT 1 FROM t2, t3
+   WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
+  );
+
+--echo # should return 0
+SELECT count(*) FROM t1 WHERE 
+  not exists(
+   SELECT 1 FROM t2, t3
+   WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
+  );
+
+--echo # should return 0
+SELECT count(*) FROM t1 WHERE 
+  not exists(
+   SELECT 1 FROM t2 IGNORE INDEX (b2), t3
+   WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
+  );
+
+DROP TABLE t1,t2,t3;
+
+
+--echo End of 5.1 tests

=== modified file 'storage/myisam/ft_boolean_search.c'
--- a/storage/myisam/ft_boolean_search.c	2009-08-28 16:21:54 +0000
+++ b/storage/myisam/ft_boolean_search.c	2009-10-27 12:43:12 +0000
@@ -475,8 +475,7 @@ static void _ftb_init_index_search(FT_IN
   int i;
   FTB_WORD   *ftbw;
 
-  if ((ftb->state != READY && ftb->state !=INDEX_DONE) ||
-      ftb->keynr == NO_SUCH_KEY)
+  if (ftb->state == UNINITIALIZED || ftb->keynr == NO_SUCH_KEY)
     return;
   ftb->state=INDEX_SEARCH;
 


Attachment: [text/bzr-bundle] bzr/joro@sun.com-20091027124312-2bfafz6ccdhzqhyd.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (joro:3195) Bug#47930Georgi Kodinov10 Nov