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, 26 insertions, 0 deletions

diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index b204e59267e..ef36b8b1b0b 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -496,3 +496,29 @@ SELECT FOUND_ROWS();
 execute stmt;                                                                   
 SELECT FOUND_ROWS();                                                            
 deallocate prepare stmt;
+
+#
+# Bug#9096 "select doesn't return all matched records if prepared statements
+# is used"
+# The bug was is bad co-operation of the optimizer's algorithm which determines
+# which keys can be used to execute a query, constants propagation
+# part of the optimizer and parameter markers used by prepared statements.
+
+drop table if exists t1;
+create table t1 (c1 int(11) not null, c2 int(11) not null,
+             primary key  (c1,c2), key c2 (c2), key c1 (c1));
+
+insert into t1 values (200887, 860);
+insert into t1 values (200887, 200887);
+
+select * from t1 where (c1=200887 and c2=200887) or c2=860;
+
+prepare stmt from
+"select * from t1 where (c1=200887 and c2=200887) or c2=860";
+execute stmt;
+prepare stmt from
+"select * from t1 where (c1=200887 and c2=?) or c2=?";
+set @a=200887, @b=860;
+# this query did not return all matching rows
+execute stmt using @a, @b;
+deallocate prepare stmt;