From: Date: May 26 2006 11:43am Subject: bk commit into 4.1 tree (gkodinov:1.2486) BUG#14708 List-Archive: http://lists.mysql.com/commits/6904 X-Bug: 14708 Message-Id: <200605260943.k4Q9hTuF029432@localhost.localdomain> 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