Bug #30788: Inconsistent retrieval of char/varchar

Index lookup does not always guarantee that we can
simply remove the relevant conditions from the WHERE
clause. Reasons can be e.g. conversion errors, 
partial indexes etc. 
The optimizer was removing these parts of the WHERE 
condition without any further checking.
This leads to "false positives" when using indexes.
Fixed by checking the index reference conditions
(using WHERE) when using indexes with sub-queries.

1 files changed, 21 insertions, 1 deletions

diff --git a/sql/item.h b/sql/item.h
index b611c59b8f1..ba65014d5e6 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;
+  }
 };