bk commit into 5.0 tree (igor:1.2593) BUG#33833

[igor]

Below is the list of changes that have just been committed into a local
5.0 repository of igor.  When igor 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@stripped, 2008-01-26 21:45:35-08:00, igor@stripped +5 -0
  Fixed bug #33833.
  Two disjuncts containing equalities of the form key=const1 and key=const2 can
  be merged into one if const1 is equal to const2. To check it the common 
  collation of the constants were used rather than the collation of the field key.
  For example when the default collation of the constants was cases insensitive
  while the collation of the field was case sensitive, then two or-ed equality 
  predicates key='b' and key='B' incorrectly were merged into one f='b'. As a 
  result ref access was used instead of range access and wrong result sets were 
  returned in many cases. 
  Fixed the problem by comparing constant in the or-ed predicate with collation of
  the key field.

  mysql-test/r/range.result@stripped, 2008-01-26 21:45:30-08:00, igor@stripped +13 -0
    Added a test case for bug #33833.

  mysql-test/t/range.test@stripped, 2008-01-26 21:45:30-08:00, igor@stripped +17 -0
    Added a test case for bug #33833.

  sql/item.cc@stripped, 2008-01-26 21:45:30-08:00, igor@stripped +43 -0
    Fixed bug #33833.
    Added the method eq_by_collation that compares two items almost as 
    the method Item::eq, but it rather enforces a given collation for
    the comparison.

  sql/item.h@stripped, 2008-01-26 21:45:30-08:00, igor@stripped +1 -0
    Fixed bug #33833.
    Added the method eq_by_collation that compares two items almost as 
    the method Item::eq, but it rather enforces a given collation for
    the comparison.

  sql/sql_select.cc@stripped, 2008-01-26 21:45:30-08:00, igor@stripped +3 -1
    Fixed bug #33833.
    Two disjuncts containing equalities of the form key=const1 and key=const2 can
    be merged into one if const1 is equal to const2. To check it the common 
    collation of the constants were used rather than the collation of the field key.
    For example when the default collation of the constants was cases insensitive
    while the collation of the field was case sensitive, then two or-ed equality 
    predicates key='b' and key='B' incorrectly were merged into one f='b'. As a 
    result ref access was used instead of range access and wrong result sets were 
    returned in many cases. 
    Fixed the problem by comparing constant in the or-ed predicate with collation of
    the key field.

diff -Nrup a/mysql-test/r/range.result b/mysql-test/r/range.result
--- a/mysql-test/r/range.result	2007-12-13 02:38:19 -08:00
+++ b/mysql-test/r/range.result	2008-01-26 21:45:30 -08:00
@@ -1153,3 +1153,16 @@ explain select * from t1 where dateval >
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	dateval	dateval	4	NULL	2	Using where
 drop table t1;
+CREATE TABLE t1 (
+a varchar(32), index (a)
+) DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
+INSERT INTO t1 VALUES
+('B'), ('A'), ('A'), ('C'), ('B'), ('A'), ('A');
+SELECT a FROM t1 WHERE a='b' OR a='B';
+a
+B
+B
+EXPLAIN SELECT a FROM t1 WHERE a='b' OR a='B';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	35	NULL	3	Using where; Using index
+DROP TABLE t1;
diff -Nrup a/mysql-test/t/range.test b/mysql-test/t/range.test
--- a/mysql-test/t/range.test	2007-12-13 02:38:19 -08:00
+++ b/mysql-test/t/range.test	2008-01-26 21:45:30 -08:00
@@ -955,4 +955,21 @@ explain select * from t1 where dateval >
 
 drop table t1;
 
+#
+# Bug #33833: different or-ed predicates were erroneously merged into one that
+# resulted in ref access instead of range access and  a wrong result set
+#
+
+CREATE TABLE t1 (
+  a varchar(32), index (a)
+) DEFAULT CHARSET=latin1 COLLATE=latin1_bin;
+
+INSERT INTO t1 VALUES
+  ('B'), ('A'), ('A'), ('C'), ('B'), ('A'), ('A');
+
+SELECT a FROM t1 WHERE a='b' OR a='B';
+EXPLAIN SELECT a FROM t1 WHERE a='b' OR a='B';
+
+DROP TABLE t1;
+
 # End of 5.0 tests
diff -Nrup a/sql/item.cc b/sql/item.cc
--- a/sql/item.cc	2008-01-11 09:57:33 -08:00
+++ b/sql/item.cc	2008-01-26 21:45:30 -08:00
@@ -4302,6 +4302,49 @@ String *Item::check_well_formed_result(S
   return str;
 }
 
+/*
+  Compare two items using a given collation
+  
+  SYNOPSIS
+    eq_by_collation()
+    item               item to compare with
+    binary_cmp         TRUE <-> compare as binaries
+    cs                 collation to use when comparing strings
+
+  DESCRIPTION
+    This method works exactly as Item::eq if the collation cs coincides with
+    the collation of the compared objects. Otherwise, first the collations that
+    differ from cs are replaced for cs and then the items are compared by
+    Item::eq. After the comparison the original collations of items are
+    restored.
+
+  RETURN
+    1    compared items has been detected as equal   
+    0    otherwise
+*/
+
+bool Item::eq_by_collation(Item *item, bool binary_cmp, CHARSET_INFO *cs)
+{
+  CHARSET_INFO *save_cs= 0;
+  CHARSET_INFO *save_item_cs= 0;
+  if (collation.collation != cs)
+  {
+    save_cs= collation.collation;
+    collation.collation= cs;
+  }
+  if (item->collation.collation != cs)
+  {
+    save_item_cs= item->collation.collation;
+    item->collation.collation= cs;
+  }
+  bool res= eq(item, binary_cmp);
+  if (save_cs)
+    collation.collation= save_cs;
+  if (save_item_cs)
+    item->collation.collation= save_item_cs;
+  return res;
+}  
+
 
 /*
   Create a field to hold a string value from an item
diff -Nrup a/sql/item.h b/sql/item.h
--- a/sql/item.h	2007-12-13 02:49:11 -08:00
+++ b/sql/item.h	2008-01-26 21:45:30 -08:00
@@ -873,6 +873,7 @@ public:
   virtual Field::geometry_type get_geometry_type() const
     { return Field::GEOM_GEOMETRY; };
   String *check_well_formed_result(String *str, bool send_error= 0);
+  bool eq_by_collation(Item *item, bool binary_cmp, CHARSET_INFO *cs); 
 };
 
 
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2008-01-18 11:50:30 -08:00
+++ b/sql/sql_select.cc	2008-01-26 21:45:30 -08:00
@@ -2887,7 +2887,9 @@ merge_key_fields(KEY_FIELD *start,KEY_FI
 	  }
 	}
 	else if (old->eq_func && new_fields->eq_func &&
-		 old->val->eq(new_fields->val, old->field->binary()))
+                 old->val->eq_by_collation(new_fields->val, 
+                                           old->field->binary(),
+                                           old->field->charset()))
 
 	{
 	  old->level= and_level;