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, 113 insertions, 1 deletions

diff --git a/sql/item.cc b/sql/item.cc
index ffde92c4214..7e74bb1fd85 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -200,6 +200,11 @@ void Item::set_name(const char *str, uint length, CHARSET_INFO *cs)
 
 bool Item::eq(const Item *item, bool binary_cmp) const
 {
+  /*
+    Note, that this is never TRUE if item is a Item_param:
+    for all basic constants we have special checks, and Item_param's
+    type() can be only among basic constant types.
+  */
   return type() == item->type() && name && item->name &&
     !my_strcasecmp(system_charset_info,name,item->name);
 }
@@ -254,7 +259,7 @@ Item *Item_string::safe_charset_converter(CHARSET_INFO *tocs)
 
 bool Item_string::eq(const Item *item, bool binary_cmp) const
 {
-  if (type() == item->type())
+  if (type() == item->type() && item->basic_const_item())
   {
     if (binary_cmp)
       return !stringcmp(&str_value, &item->str_value);
@@ -1356,6 +1361,70 @@ bool Item_param::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
   return 0;
 }
 
+
+bool Item_param::basic_const_item() const
+{
+  if (state == NO_VALUE || state == TIME_VALUE)
+    return FALSE;
+  return TRUE;
+}
+
+Item *
+Item_param::new_item()
+{
+  /* see comments in the header file */
+  switch (state) {
+  case NULL_VALUE:
+    return new Item_null(name);
+  case INT_VALUE:
+    return new Item_int(name, value.integer, max_length);
+  case REAL_VALUE:
+    return new Item_real(name, value.real, decimals, max_length);
+  case STRING_VALUE:
+  case LONG_DATA_VALUE:
+    return new Item_string(name, str_value.c_ptr_quick(), str_value.length(),
+                           str_value.charset());
+  case TIME_VALUE:
+    break;
+  case NO_VALUE:
+  default:
+    DBUG_ASSERT(0);
+  };
+  return 0;
+}
+
+
+bool
+Item_param::eq(const Item *arg, bool binary_cmp) const
+{
+  Item *item;
+  if (!basic_const_item() || !arg->basic_const_item() || arg->type() != type())
+    return FALSE;
+  /*
+    We need to cast off const to call val_int(). This should be OK for
+    a basic constant.
+  */
+  item= (Item*) arg;
+
+  switch (state) {
+  case NULL_VALUE:
+    return TRUE;
+  case INT_VALUE:
+    return value.integer == item->val_int() &&
+           unsigned_flag == item->unsigned_flag;
+  case REAL_VALUE:
+    return value.real == item->val();
+  case STRING_VALUE:
+  case LONG_DATA_VALUE:
+    if (binary_cmp)
+      return !stringcmp(&str_value, &item->str_value);
+    return !sortcmp(&str_value, &item->str_value, collation.collation);
+  default:
+    break;
+  }
+  return FALSE;
+}
+
 /* End of Item_param related */
 
 
@@ -1937,6 +2006,23 @@ int Item_int::save_in_field(Field *field, bool no_conversions)
   return field->store(nr);
 }
 
+
+bool Item_int::eq(const Item *arg, bool binary_cmp) const
+{
+  /* No need to check for null value as basic constant can't be NULL */
+  if (arg->basic_const_item() && arg->type() == type())
+  {
+    /*
+      We need to cast off const to call val_int(). This should be OK for
+      a basic constant.
+    */
+    Item *item= (Item*) arg;
+    return item->val_int() == value && item->unsigned_flag == unsigned_flag;
+  }
+  return FALSE;
+}
+
+
 Item_num *Item_uint::neg()
 {
   return new Item_real(name, - ((double) value), 0, max_length);
@@ -1951,6 +2037,21 @@ int Item_real::save_in_field(Field *field, bool no_conversions)
   return field->store(nr);
 }
 
+
+bool Item_real::eq(const Item *arg, bool binary_cmp) const
+{
+  if (arg->basic_const_item() && arg->type() == type())
+  {
+    /*
+      We need to cast off const to call val_int(). This should be OK for
+      a basic constant.
+    */
+    Item *item= (Item*) arg;
+    return item->val() == value;
+  }
+  return FALSE;
+}
+
 /****************************************************************************
 ** varbinary item
 ** In string context this is a binary string
@@ -2017,6 +2118,17 @@ int Item_varbinary::save_in_field(Field *field, bool no_conversions)
 }
 
 
+bool Item_varbinary::eq(const Item *arg, bool binary_cmp) const
+{
+  if (arg->basic_const_item() && arg->type() == type())
+  {
+    if (binary_cmp)
+      return !stringcmp(&str_value, &arg->str_value);
+    return !sortcmp(&str_value, &arg->str_value, collation.collation);
+  }
+  return FALSE;
+}
+
 /*
   Pack data in buffer for sending
 */