Merge gkodinov@bk-internal.mysql.com:/home/bk/mysql-5.0-opt

into  magare.gmz:/home/kgeorge/mysql/autopush/B30788-5.0-opt


sql/item.h:
  Auto merged
sql/sql_select.cc:
  Auto merged
mysql-test/r/subselect.result:
  merge of 5.0-opt
mysql-test/t/subselect.test:
  merge of 5.0-opt

2 files changed, 36 insertions, 12 deletions

diff --git a/sql/item.h b/sql/item.h
index a1135c2c725..5046e0ceb93 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -2452,8 +2452,18 @@ class Item_cache: public Item
 protected:
   Item *example;
   table_map used_table_map;
+  /*
+    Field that this object will get value from. This is set/used by 
+    index-based subquery engines to detect and remove the equality injected 
+    by IN->EXISTS transformation.
+    For all other uses of Item_cache, cached_field doesn't matter.
+  */  
+  Field *cached_field;
 public:
-  Item_cache(): example(0), used_table_map(0) {fixed= 1; null_value= 1;}
+  Item_cache(): example(0), used_table_map(0), cached_field(0) 
+  {
+    fixed= 1; null_value= 1;
+  }
 
   void set_used_tables(table_map map) { used_table_map= map; }
 
@@ -2465,6 +2475,8 @@ public:
     decimals= item->decimals;
     collation.set(item->collation);
     unsigned_flag= item->unsigned_flag;
+    if (item->type() == FIELD_ITEM)
+      cached_field= ((Item_field *)item)->field;
     return 0;
   };
   virtual void store(Item *)= 0;
@@ -2475,6 +2487,14 @@ public:
   // to prevent drop fixed flag (no need parent cleanup call)
   void cleanup() {}
   void print(String *str);
+  bool eq_def(Field *field) 
+  { 
+    return cached_field ? cached_field->eq_def (field) : FALSE;
+  }
+  bool eq(const Item *item, bool binary_cmp) const
+  {
+    return this == item;
+  }
 };
 
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index e25d0d78c87..ea47477c59c 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -217,6 +217,7 @@ static void select_describe(JOIN *join, bool need_tmp_table,bool need_order,
 			    bool distinct, const char *message=NullS);
 static Item *remove_additional_cond(Item* conds);
 static void add_group_and_distinct_keys(JOIN *join, JOIN_TAB *join_tab);
+static bool test_if_ref(Item_field *left_item,Item *right_item);
 
 
 /*
@@ -681,9 +682,6 @@ err:
     without "checking NULL", remove the predicates that were pushed down
     into the subquery.
 
-    We can remove the equalities that will be guaranteed to be true by the
-    fact that subquery engine will be using index lookup.
-
     If the subquery compares scalar values, we can remove the condition that
     was wrapped into trig_cond (it will be checked when needed by the subquery
     engine)
@@ -693,6 +691,12 @@ err:
     and non-NULL values, we'll do a full table scan and will rely on the
     equalities corresponding to non-NULL parts of left tuple to filter out
     non-matching records.
+
+    TODO: We can remove the equalities that will be guaranteed to be true by the
+    fact that subquery engine will be using index lookup. This must be done only
+    for cases where there are no conversion errors of significance, e.g. 257
+    that is searched in a byte. But this requires homogenization of the return 
+    codes of all Field*::store() methods.
 */
 
 void JOIN::remove_subq_pushed_predicates(Item **where)
@@ -700,17 +704,13 @@ void JOIN::remove_subq_pushed_predicates(Item **where)
   if (conds->type() == Item::FUNC_ITEM &&
       ((Item_func *)this->conds)->functype() == Item_func::EQ_FUNC &&
       ((Item_func *)conds)->arguments()[0]->type() == Item::REF_ITEM &&
-      ((Item_func *)conds)->arguments()[1]->type() == Item::FIELD_ITEM)
+      ((Item_func *)conds)->arguments()[1]->type() == Item::FIELD_ITEM &&
+      test_if_ref ((Item_field *)((Item_func *)conds)->arguments()[1],
+                   ((Item_func *)conds)->arguments()[0]))
   {
     *where= 0;
     return;
   }
-  if (conds->type() == Item::COND_ITEM &&
-      ((class Item_func *)this->conds)->functype() ==
-      Item_func::COND_AND_FUNC)
-  {
-    *where= remove_additional_cond(conds);
-  }
 }
 
 
@@ -1236,7 +1236,7 @@ JOIN::optimize()
   {
     if (!having)
     {
-      Item *where= 0;
+      Item *where= conds;
       if (join_tab[0].type == JT_EQ_REF &&
 	  join_tab[0].ref.items[0]->name == in_left_expr_name)
       {
@@ -11892,8 +11892,12 @@ static bool test_if_ref(Item_field *left_item,Item *right_item)
     Item *ref_item=part_of_refkey(field->table,field);
     if (ref_item && ref_item->eq(right_item,1))
     {
+      right_item= right_item->real_item();
       if (right_item->type() == Item::FIELD_ITEM)
 	return (field->eq_def(((Item_field *) right_item)->field));
+      /* remove equalities injected by IN->EXISTS transformation */
+      else if (right_item->type() == Item::CACHE_ITEM)
+        return ((Item_cache *)right_item)->eq_def (field);
       if (right_item->const_item() && !(right_item->is_null()))
       {
 	/*