From 1ba8ec70888603a4373b10a7c5d98b94fafb5ac1 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 9 Feb 2007 12:54:50 -0800 Subject: Fixed bug #26017. Objects of the class Item_equal contain an auxiliary member eval_item of the type cmp_item that is used only for direct evaluation of multiple equalities. Currently a multiple equality is evaluated directly only in the cases when the equality holds at most for one row in the result set. The compare collation of eval_item was determined incorectly. It could lead to returning incorrect results for some queries. mysql-test/r/join_outer.result: Added a test case for bug #26017. mysql-test/t/join_outer.test: Added a test case for bug #26017. sql/item_cmpfunc.cc: Fixed bug #26017. Objects of the class Item_equal contain an auxiliary member eval_item of the type cmp_item that is used only for direct evaluation of multiple equalities. Currently a multiple equality is evaluated directly only in the cases when the equality holds at most for one row in the result set. The compare collation of eval_item was determined incorrectly. It could lead to returning incorrect results for some queries. sql/item_cmpfunc.h: Fixed bug #26017. Removed the cmp_collation member from the Item_equal class as useless for the current implementation of the class. --- mysql-test/r/join_outer.result | 20 ++++++++++++++++++++ mysql-test/t/join_outer.test | 23 +++++++++++++++++++++++ sql/item_cmpfunc.cc | 4 +--- sql/item_cmpfunc.h | 1 - 4 files changed, 44 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 89bb26c4b3f..df66336bd81 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1194,3 +1194,23 @@ a b 3 3 4 NULL DROP TABLE t1,t2; +CREATE TABLE t1 ( +f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY, +f2 varchar(16) collate latin1_swedish_ci +); +CREATE TABLE t2 ( +f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY, +f3 varchar(16) collate latin1_swedish_ci +); +INSERT INTO t1 VALUES ('bla','blah'); +INSERT INTO t2 VALUES ('bla','sheep'); +SELECT * FROM t1 JOIN t2 USING(f1) WHERE f1='Bla'; +f1 f2 f3 +bla blah sheep +SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla'; +f1 f2 f3 +bla blah sheep +SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla'; +f1 f2 f3 +bla blah sheep +DROP TABLE t1,t2; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index 20462f2ca3f..a0620e144c2 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -802,3 +802,26 @@ SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 2=2); SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b WHERE (1=1 OR 1=0); DROP TABLE t1,t2; + +# +# Bug 26017: LEFT OUTER JOIN over two constant tables and +# a case-insensitive comparison predicate field=const +# + +CREATE TABLE t1 ( + f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY, + f2 varchar(16) collate latin1_swedish_ci +); +CREATE TABLE t2 ( + f1 varchar(16) collate latin1_swedish_ci PRIMARY KEY, + f3 varchar(16) collate latin1_swedish_ci +); + +INSERT INTO t1 VALUES ('bla','blah'); +INSERT INTO t2 VALUES ('bla','sheep'); + +SELECT * FROM t1 JOIN t2 USING(f1) WHERE f1='Bla'; +SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla'; +SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla'; + +DROP TABLE t1,t2; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 08f9c16384a..48e0fecc5f6 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -4034,11 +4034,9 @@ longlong Item_equal::val_int() void Item_equal::fix_length_and_dec() { - Item *item= const_item ? const_item : get_first(); + Item *item= get_first(); eval_item= cmp_item::get_comparator(item->result_type(), item->collation.collation); - if (item->result_type() == STRING_RESULT) - eval_item->cmp_charset= cmp_collation.collation; } bool Item_equal::walk(Item_processor processor, byte *arg) diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index f18728c554b..4a06f487126 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -1296,7 +1296,6 @@ class Item_equal: public Item_bool_func Item *const_item; /* optional constant item equal to fields items */ cmp_item *eval_item; bool cond_false; - DTCollation cmp_collation; public: inline Item_equal() : Item_bool_func(), const_item(0), eval_item(0), cond_false(0) -- cgit v1.2.1