A fix and test case for Bug#9096 "select doesn't return all matched

records if prepared statements is used".
This fix changes equality evaluation method of basic constants from
by-name to by-value, thus effectively enabling use of parameter markers
in some optimizations (constants propagation, evaluation of possible
keys for query).


mysql-test/r/ps.result:
  Test results for the test case for Bug#9096
mysql-test/t/ps.test:
  A short test case for Bug#9096 "select doesn't return all matched records if
   prepared statements is used". The is enough to reproduce the
  glitch in update_ref_and_keys causing the bug to occur.
sql/item.cc:
  Implement by-value equality evaluation of basic constants.
  This is needed to work with Item_param values. Until now
  Item_param was compared with other items by its name, which is always "?".
  The bug at hand showed up when an integer
  constant was created from one parameter marker (with value 200887 and
   name "?") and then compared by-name with another parameter marker
  (with value 860 and name "?"). True returned by this comparison resulted
  in a wrong table access method used to evaluate the query.
  Implement Item_param methods needed to emulate "basic constant" mode at 
  full.
sql/item.h:
  Change declaration of basic_const_item(): now it also widens its 
  argument from const Item * to Item * if the argument is a basic constant.
  Declare eq() for all basic constatns, as long as now they 
  are compared by value, not by name. Each constant needs its own
  comparison method.
  Declarations of Item_param methods needed to fully emulate 
  a basic constant when parameter value is set.
sql/item_func.cc:
  Fix wrong casts.

1 files changed, 23 insertions, 4 deletions

diff --git a/sql/item.h b/sql/item.h
index d949095b455..335a5b5547a 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -234,7 +234,7 @@ public:
   virtual table_map not_null_tables() const { return used_tables(); }
   /*
     Returns true if this is a simple constant item like an integer, not
-    a constant expression
+    a constant expression. Used in the optimizer to propagate basic constants.
   */
   virtual bool basic_const_item() const { return 0; }
   /* cloning of constant items (0 if it is not const) */
@@ -581,7 +581,6 @@ public:
 
   bool convert_str_value(THD *thd);
 
-  Item *new_item() { return new Item_param(pos_in_query); }
   /*
     If value for parameter was not set we treat it as non-const
     so noone will use parameters value in fix_fields still
@@ -590,12 +589,29 @@ public:
   virtual table_map used_tables() const
   { return state != NO_VALUE ? (table_map)0 : PARAM_TABLE_BIT; }
   void print(String *str) { str->append('?'); }
-  /* parameter never equal to other parameter of other item */
-  bool eq(const Item *item, bool binary_cmp) const { return 0; }
   bool is_null()
   { DBUG_ASSERT(state != NO_VALUE); return state == NULL_VALUE; }
+  bool basic_const_item() const;
+  /*
+    This method is used to make a copy of a basic constant item when
+    propagating constants in the optimizer. The reason to create a new
+    item and not use the existing one is not precisely known (2005/04/16).
+    Probably we are trying to preserve tree structure of items, in other
+    words, avoid pointing at one item from two different nodes of the tree.
+    Return a new basic constant item if parameter value is a basic
+    constant, assert otherwise. This method is called only if
+    basic_const_item returned TRUE.
+  */
+  Item *new_item();
+  /*
+    Implement by-value equality evaluation if parameter value
+    is set and is a basic constant (integer, real or string).
+    Otherwise return FALSE.
+  */
+  bool eq(const Item *item, bool binary_cmp) const;
 };
 
+
 class Item_int :public Item_num
 {
 public:
@@ -622,6 +638,7 @@ public:
   void cleanup() {}
   void print(String *str);
   Item_num *neg() { value= -value; return this; }
+  bool eq(const Item *, bool binary_cmp) const;
 };
 
 
@@ -677,6 +694,7 @@ public:
   void cleanup() {}
   Item *new_item() { return new Item_real(name,value,decimals,max_length); }
   Item_num *neg() { value= -value; return this; }
+  bool eq(const Item *, bool binary_cmp) const;
 };
 
 
@@ -810,6 +828,7 @@ public:
   enum_field_types field_type() const { return MYSQL_TYPE_STRING; }
   // to prevent drop fixed flag (no need parent cleanup call)
   void cleanup() {}
+  bool eq(const Item *item, bool binary_cmp) const;
 };