bk commit into 4.1 tree (konstantin:1.2225) BUG#9777

[konstantin]

Below is the list of changes that have just been committed into a local
4.1 repository of kostja. When kostja does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2225 05/05/05 12:55:09 konstantin@stripped +4 -0
  A fix and test case for Bug#9777 " Empty set returned by Prepared Statement when it
   should return a non empty one"
  (see comments for the changed files for details).

  sql/item.h
    1.179 05/05/05 12:55:01 konstantin@stripped +2 -5
    Declaration for Item_int_with_ref::new_item

  sql/item.cc
    1.200 05/05/05 12:55:01 konstantin@stripped +23 -1
    A fix for Bug#9777: when creating a constant item from within 
    Item_int_with_ref::new_item, create the item by value, not by name.
    This should work with prepared statements placeholders.
    Item_int_with_ref is a special optimization case used
     when we compare datetime constants with datetime value.
    Converting the item to integer early is OK as it is in line
    with the purpose of Item_int_with_ref - to speed up comparison by 
    using integers.
    Minor cleanups.

  mysql-test/t/ps.test
    1.33 05/05/05 12:55:01 konstantin@stripped +47 -0
    A test case for Bug#9777

  mysql-test/r/ps.result
    1.34 05/05/05 12:55:01 konstantin@stripped +38 -0
    A test case for Bug#9777: tests results fixed.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	konstantin
# Host:	dragonfly.local
# Root:	/opt/local/work/mysql-4.1-9777

--- 1.199/sql/item.cc	2005-05-03 12:47:22 +04:00
+++ 1.200/sql/item.cc	2005-05-05 12:55:01 +04:00
@@ -769,6 +769,13 @@
 }
 
 
+Item_uint::Item_uint(const char *str_arg, longlong i, uint length):
+  Item_int(str_arg, i, length)
+{
+  unsigned_flag= 1;
+}
+
+
 String *Item_uint::val_str(String *str)
 {
   // following assert is redundant, because fixed=1 assigned in constructor
@@ -1377,7 +1384,9 @@
   case NULL_VALUE:
     return new Item_null(name);
   case INT_VALUE:
-    return new Item_int(name, value.integer, max_length);
+    return (unsigned_flag ?
+            new Item_uint(name, value.integer, max_length) :
+            new Item_int(name, value.integer, max_length));
   case REAL_VALUE:
     return new Item_real(name, value.real, decimals, max_length);
   case STRING_VALUE:
@@ -2020,6 +2029,19 @@
     return item->val_int() == value && item->unsigned_flag == unsigned_flag;
   }
   return FALSE;
+}
+
+
+Item *Item_int_with_ref::new_item()
+{
+  DBUG_ASSERT(ref->basic_const_item());
+  /*
+    We need to evaluate the constant to make sure it works with
+    parameter markers.
+  */
+  return (ref->unsigned_flag ?
+          new Item_uint(ref->name, ref->val_int(), ref->max_length) :
+          new Item_int(ref->name, ref->val_int(), ref->max_length));
 }
 
 

--- 1.178/sql/item.h	2005-05-03 12:49:20 +04:00
+++ 1.179/sql/item.h	2005-05-05 12:55:01 +04:00
@@ -651,6 +651,7 @@
 {
 public:
   Item_uint(const char *str_arg, uint length);
+  Item_uint(const char *str_arg, longlong i, uint length);
   Item_uint(uint32 i) :Item_int((longlong) i, 10) 
     { unsigned_flag= 1; }
   double val()
@@ -1046,11 +1047,7 @@
   {
     return ref->save_in_field(field, no_conversions);
   }
-  Item *new_item()
-  {
-    return (ref->unsigned_flag)? new Item_uint(ref->name, ref->max_length) :
-                                 new Item_int(ref->name, ref->max_length);
-  }
+  Item *new_item();
 };
 
 

--- 1.33/mysql-test/r/ps.result	2005-05-03 12:47:22 +04:00
+++ 1.34/mysql-test/r/ps.result	2005-05-05 12:55:01 +04:00
@@ -519,3 +519,41 @@
 200887	860
 200887	200887
 deallocate prepare stmt;
+drop table t1;
+create table t1 (
+id bigint(20) not null auto_increment,
+code varchar(20) character set utf8 collate utf8_bin not null default '',
+company_name varchar(250) character set utf8 collate utf8_bin default null,
+setup_mode tinyint(4) default null,
+start_date datetime default null,
+primary key  (id), unique key code (code)
+);
+create table t2 (
+id bigint(20) not null auto_increment,
+email varchar(250) character set utf8 collate utf8_bin default null,
+name varchar(250) character set utf8 collate utf8_bin default null,
+t1_id bigint(20) default null,
+password varchar(250) character set utf8 collate utf8_bin default null,
+primary_contact tinyint(4) not null default '0',
+email_opt_in tinyint(4) not null default '1',
+primary key  (id), unique key email (email), key t1_id (t1_id),
+constraint t2_fk1 foreign key (t1_id) references t1 (id)
+);
+insert into t1 values
+(1, 'demo', 'demo s', 0, current_date()),
+(2, 'code2', 'name 2', 0, current_date()),
+(3, 'code3', 'name 3', 0, current_date());
+insert into t2 values
+(2, 'email1', 'name1', 3, 'password1', 0, 0),
+(3, 'email2', 'name1', 1, 'password2', 1, 0),
+(5, 'email3', 'name3', 2, 'password3', 0, 0);
+prepare stmt from 'select t2.id from t2, t1 where (t1.id=? and t2.t1_id=t1.id)';
+set @a=1;
+execute stmt using @a;
+id
+3
+select t2.id from t2, t1 where (t1.id=1 and t2.t1_id=t1.id);
+id
+3
+deallocate prepare stmt;
+drop table t1, t2;

--- 1.32/mysql-test/t/ps.test	2005-05-03 12:47:22 +04:00
+++ 1.33/mysql-test/t/ps.test	2005-05-05 12:55:01 +04:00
@@ -522,3 +522,50 @@
 # this query did not return all matching rows
 execute stmt using @a, @b;
 deallocate prepare stmt;
+drop table t1;
+
+#
+# Bug#9777 - another occurrence of the problem stated in Bug#9096:
+# we can not compare basic constants by their names, because a placeholder
+# is a basic constant while his name is always '?'
+#
+
+create table t1 (
+   id bigint(20) not null auto_increment,
+   code varchar(20) character set utf8 collate utf8_bin not null default '',
+   company_name varchar(250) character set utf8 collate utf8_bin default null,
+   setup_mode tinyint(4) default null,
+   start_date datetime default null,
+   primary key  (id), unique key code (code)
+);
+
+create table t2 (
+   id bigint(20) not null auto_increment,
+   email varchar(250) character set utf8 collate utf8_bin default null,
+   name varchar(250) character set utf8 collate utf8_bin default null,
+   t1_id bigint(20) default null,
+   password varchar(250) character set utf8 collate utf8_bin default null,
+   primary_contact tinyint(4) not null default '0',
+   email_opt_in tinyint(4) not null default '1',
+   primary key  (id), unique key email (email), key t1_id (t1_id),
+   constraint t2_fk1 foreign key (t1_id) references t1 (id)
+);
+
+insert into t1 values
+(1, 'demo', 'demo s', 0, current_date()),
+(2, 'code2', 'name 2', 0, current_date()),
+(3, 'code3', 'name 3', 0, current_date());
+
+insert into t2 values
+(2, 'email1', 'name1', 3, 'password1', 0, 0),
+(3, 'email2', 'name1', 1, 'password2', 1, 0),
+(5, 'email3', 'name3', 2, 'password3', 0, 0);
+
+prepare stmt from 'select t2.id from t2, t1 where (t1.id=? and t2.t1_id=t1.id)';
+set @a=1;
+execute stmt using @a;
+
+select t2.id from t2, t1 where (t1.id=1 and t2.t1_id=t1.id);
+
+deallocate prepare stmt;
+drop table t1, t2;