bk commit into 4.1 tree (gkodinov:1.2486) BUG#14708

[kgeorge]

Below is the list of changes that have just been committed into a local
4.1 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2486 06/05/26 12:43:19 gkodinov@stripped +6 -0
  Bug #14708: Inconsistent treatment of NULLs in LEFT JOINed 
      FULLTEXT matching without index
  
  There is inherent implementation limitation of not processing 
  correctly columns from more than one table as arguments to 
  the freetext MATCH () function. But the compiler is not 
  reporting that as an error.
  
  Made it to throw an error in that situation until the 
  implementation limitation is lifted.
  Also fixed a small buglet causing counting negative 
  freetext weights as matches.

  sql/item_func.h
    1.130 06/05/26 12:43:11 gkodinov@stripped +1 -1
    rule out the negative weights : they're not a match.

  sql/item_func.cc
    1.262 06/05/26 12:43:11 gkodinov@stripped +4 -1
    Make multitable MATCH() arguments an error

  mysql-test/t/fulltext_left_join.test
    1.13 06/05/26 12:43:10 gkodinov@stripped +26 -0
    Testcase for the bug.

  mysql-test/t/fulltext.test
    1.78 06/05/26 12:43:10 gkodinov@stripped +2 -1
    disabled a multitable MATCH test

  mysql-test/r/fulltext_left_join.result
    1.13 06/05/26 12:43:10 gkodinov@stripped +18 -0
    Testcase for the bug.

  mysql-test/r/fulltext.result
    1.79 06/05/26 12:43:10 gkodinov@stripped +0 -3
    disabled a multitable MATCH test

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	gkodinov
# Host:	rakia.(none)
# Root:	/home/kgeorge/mysql/4.1/B14708

--- 1.261/sql/item_func.cc	2006-05-07 11:43:22 +03:00
+++ 1.262/sql/item_func.cc	2006-05-26 12:43:11 +03:00
@@ -3073,7 +3073,10 @@
     PARAM_TABLE_BIT can only appear from AGAINST argument.
   */
   if ((used_tables_cache & ~PARAM_TABLE_BIT) != item->used_tables())
-    key=NO_SUCH_KEY;
+  {
+    my_error(ER_WRONG_ARGUMENTS,MYF(0),"MATCH");
+    return TRUE;
+  }
 
   if (key == NO_SUCH_KEY && !(flags & FT_BOOL))
   {

--- 1.129/sql/item_func.h	2006-02-07 11:49:52 +02:00
+++ 1.130/sql/item_func.h	2006-05-26 12:43:11 +03:00
@@ -1072,7 +1072,7 @@
   bool fix_fields(THD *thd, struct st_table_list *tlist, Item **ref);
   bool eq(const Item *, bool binary_cmp) const;
   /* The following should be safe, even if we compare doubles */
-  longlong val_int() { DBUG_ASSERT(fixed == 1); return val()!=0.0; }
+  longlong val_int() { DBUG_ASSERT(fixed == 1); return val() > DBL_EPSILON; }
   double val();
   void print(String *str);
 

--- 1.78/mysql-test/r/fulltext.result	2006-02-01 18:40:10 +02:00
+++ 1.79/mysql-test/r/fulltext.result	2006-05-26 12:43:10 +03:00
@@ -335,9 +335,6 @@
 insert into t2 values (1, 1, 'xxfoo');
 insert into t2 values (2, 1, 'xxbar');
 insert into t2 values (3, 1, 'xxbuz');
-select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in
boolean mode);
-t1_id	name	t2_id	t1_id	name
-1	data1	1	1	xxfoo
 select * from t2 where match name against ('*a*b*c*d*e*f*' in boolean mode);
 t2_id	t1_id	name
 drop table t1,t2;

--- 1.77/mysql-test/t/fulltext.test	2006-02-01 18:40:10 +02:00
+++ 1.78/mysql-test/t/fulltext.test	2006-05-26 12:43:10 +03:00
@@ -250,7 +250,8 @@
 insert into t2 values (1, 1, 'xxfoo');
 insert into t2 values (2, 1, 'xxbar');
 insert into t2 values (3, 1, 'xxbuz');
-select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in
boolean mode);
+#TODO: multitable arguments to MATCH () not properly supported 
+#select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo'
in boolean mode);
 
 #
 # Bug #7858: bug with many short (< ft_min_word_len) words in boolean search

--- 1.12/mysql-test/r/fulltext_left_join.result	2004-10-26 19:29:53 +03:00
+++ 1.13/mysql-test/r/fulltext_left_join.result	2006-05-26 12:43:10 +03:00
@@ -50,3 +50,21 @@
 1	a1	2003-05-23 19:30:00	aberdeen town hall	1
 NULL	a2	2003-05-23 19:30:00	NULL	NULL
 drop table t1,t2;
+CREATE TABLE master (id INT NOT NULL PRIMARY KEY,
+d CHAR(200) NOT NULL, e CHAR(200));
+INSERT INTO master VALUES 
+(1, 'aword', NULL), (2, 'aword', 'bword'), 
+(3, 'bword', NULL), (4, 'bword', 'aword'), 
+(5, 'aword and bword', NULL);
+CREATE TABLE detail (id INT NOT NULL PRIMARY KEY,
+m_id INT NOT NULL, f CHAR(200), KEY (m_id));
+INSERT INTO detail VALUES
+(1, 1, 'bword'),
+(2, 3, 'aword'),
+(3, 5, '');
+SELECT m.id, m.d, m.e, d.id, d.f
+FROM master m LEFT JOIN detail d ON d.m_id = m.id
+WHERE MATCH(m.d, m.e, d.f) AGAINST ('+aword +bword' IN BOOLEAN MODE);
+ERROR HY000: Incorrect arguments to MATCH
+DROP TABLE master;
+DROP TABLE detail;

--- 1.12/mysql-test/t/fulltext_left_join.test	2005-07-28 03:21:41 +03:00
+++ 1.13/mysql-test/t/fulltext_left_join.test	2006-05-26 12:43:10 +03:00
@@ -45,4 +45,30 @@
 select * from t1 left join t2 on (venue_id = entity_id and match(name)
against('aberdeen')) where dt = '2003-05-23 19:30:00';
 drop table t1,t2;
 
+#
+# Bug #14708: Inconsistent treatment of NULLs in LEFT JOINed FULLTEXT
+# matching without index 
+#
+CREATE TABLE master (id INT NOT NULL PRIMARY KEY,
+	d CHAR(200) NOT NULL, e CHAR(200));
+INSERT INTO master VALUES 
+(1, 'aword', NULL), (2, 'aword', 'bword'), 
+(3, 'bword', NULL), (4, 'bword', 'aword'), 
+(5, 'aword and bword', NULL);
+
+CREATE TABLE detail (id INT NOT NULL PRIMARY KEY,
+	m_id INT NOT NULL, f CHAR(200), KEY (m_id));
+
+INSERT INTO detail VALUES
+(1, 1, 'bword'),
+(2, 3, 'aword'),
+(3, 5, '');
+
+--error 1210
+SELECT m.id, m.d, m.e, d.id, d.f
+FROM master m LEFT JOIN detail d ON d.m_id = m.id
+WHERE MATCH(m.d, m.e, d.f) AGAINST ('+aword +bword' IN BOOLEAN MODE);
+DROP TABLE master;
+DROP TABLE detail;
+
 # End of 4.1 tests