bk commit into 5.0 tree (davi:1.2591) BUG#33851

[Davi Arnaut]

Below is the list of changes that have just been committed into a local
5.0 repository of davi.  When davi 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@stripped, 2008-02-28 11:34:08-03:00, davi@stripped +11 -0
  Bug#33851 Passing UNSIGNED param to EXECUTE returns ERROR 1210
  
  The problem is that passing anything other than a integer to a limit
  clause in a prepared statement would fail. This limitation was introduced
  to avoid replication problems (e.g: replicating the statement with a
  string argument would cause a parse failure in the slave).
  
  The solution is to convert arguments to the limit clause to a integer
  value and use this converted value when persisting the query to the log.

  mysql-test/r/limit.result@stripped, 2008-02-28 11:34:04-03:00, davi@stripped +9 -0
    Update test case result.

  mysql-test/r/ps.result@stripped, 2008-02-28 11:34:04-03:00, davi@stripped +30 -0
    Add test case result for Bug#33851

  mysql-test/r/rpl_user_variables.result@stripped, 2008-02-28 11:34:04-03:00, davi@stripped
+16 -0
    Test case result for replication of prepared statement with
    limit clause.

  mysql-test/t/limit.test@stripped, 2008-02-28 11:34:04-03:00, davi@stripped +12 -5
    Test parameters to limit clause.

  mysql-test/t/ps.test@stripped, 2008-02-28 11:34:05-03:00, davi@stripped +19 -0
    Add test case for Bug#33851

  mysql-test/t/rpl_user_variables.test@stripped, 2008-02-28 11:34:05-03:00, davi@stripped +17
-0
    Test replication of a parameter which value is converted.

  sql/item.cc@stripped, 2008-02-28 11:34:05-03:00, davi@stripped +8 -3
    Convert value to integer if it's a parameter to a limit clause.

  sql/item.h@stripped, 2008-02-28 11:34:05-03:00, davi@stripped +2 -7
    Flag signal that item is a parameter to a limit clause.

  sql/item_func.cc@stripped, 2008-02-28 11:34:06-03:00, davi@stripped +1 -1
    Const member functions, object is not mutated.

  sql/sql_class.h@stripped, 2008-02-28 11:34:06-03:00, davi@stripped +1 -1
    Const member functions, object is not mutated.

  sql/sql_yacc.yy@stripped, 2008-02-28 11:34:06-03:00, davi@stripped +1 -1
    Flag that item is a parameter to a limit clause.

diff -Nrup a/mysql-test/r/limit.result b/mysql-test/r/limit.result
--- a/mysql-test/r/limit.result	2007-05-18 04:08:05 -03:00
+++ b/mysql-test/r/limit.result	2008-02-28 11:34:04 -03:00
@@ -94,11 +94,20 @@ drop table t1;
 prepare s from "select 1 limit ?";
 set @a='qwe';
 execute s using @a;
+1
+set @a=-1;
+execute s using @a;
 ERROR HY000: Incorrect arguments to EXECUTE
 prepare s from "select 1 limit 1, ?";
 execute s using @a;
 ERROR HY000: Incorrect arguments to EXECUTE
 prepare s from "select 1 limit ?, ?";
+execute s using @a, @a;
+ERROR HY000: Incorrect arguments to EXECUTE
+set @a=14632475938453979136;
+execute s using @a, @a;
+1
+set @a=-14632475938453979136;
 execute s using @a, @a;
 ERROR HY000: Incorrect arguments to EXECUTE
 End of 5.0 tests
diff -Nrup a/mysql-test/r/ps.result b/mysql-test/r/ps.result
--- a/mysql-test/r/ps.result	2008-02-21 14:58:27 -03:00
+++ b/mysql-test/r/ps.result	2008-02-28 11:34:04 -03:00
@@ -1861,4 +1861,34 @@ prepare stmt from "create view v1 as sel
 ERROR 42S02: Table 'test.t1' doesn't exist
 prepare stmt from "create view v1 as select * from `t1` `b`";
 ERROR 42S02: Table 'test.t1' doesn't exist
+prepare stmt from "select ?";
+set @arg= 123456789.987654321;
+select @arg;
+@arg
+123456789.987654321
+execute stmt using @arg;
+?
+123456789.987654321
+set @arg= "string";
+select @arg;
+@arg
+string
+execute stmt using @arg;
+?
+string
+set @arg= 123456;
+select @arg;
+@arg
+123456
+execute stmt using @arg;
+?
+123456
+set @arg= cast(-12345.54321 as decimal(20, 10));
+select @arg;
+@arg
+-12345.5432100000
+execute stmt using @arg;
+?
+-12345.5432100000
+deallocate prepare stmt;
 End of 5.0 tests.
diff -Nrup a/mysql-test/r/rpl_user_variables.result
b/mysql-test/r/rpl_user_variables.result
--- a/mysql-test/r/rpl_user_variables.result	2007-05-24 07:35:42 -03:00
+++ b/mysql-test/r/rpl_user_variables.result	2008-02-28 11:34:04 -03:00
@@ -290,6 +290,22 @@ select * from t1;
 a	b
 2	1
 drop table t1;
+create table t1(a int);
+insert into t1 values (1),(2);
+prepare s1 from 'insert into t1 select a from t1 limit ?';
+set @x='1.1';
+execute s1 using @x;
+select * from t1;
+a
+1
+2
+1
+select * from t1;
+a
+1
+2
+1
+drop table t1;
 End of 5.0 tests.
 DROP FUNCTION IF EXISTS f1;
 DROP FUNCTION IF EXISTS f2;
diff -Nrup a/mysql-test/t/limit.test b/mysql-test/t/limit.test
--- a/mysql-test/t/limit.test	2007-05-18 04:08:05 -03:00
+++ b/mysql-test/t/limit.test	2008-02-28 11:34:04 -03:00
@@ -76,15 +76,22 @@ drop table t1;
 # Bug #28464: a string argument to 'limit ?' PS
 #
 
-prepare s from "select 1 limit ?"; 
-set @a='qwe'; 
---error 1210
+prepare s from "select 1 limit ?";
+set @a='qwe';
+execute s using @a;
+set @a=-1;
+--error ER_WRONG_ARGUMENTS
 execute s using @a;
 prepare s from "select 1 limit 1, ?";
---error 1210
+--error ER_WRONG_ARGUMENTS
 execute s using @a;
 prepare s from "select 1 limit ?, ?";
---error 1210
+--error ER_WRONG_ARGUMENTS
+execute s using @a, @a;
+set @a=14632475938453979136;
+execute s using @a, @a;
+set @a=-14632475938453979136;
+--error ER_WRONG_ARGUMENTS
 execute s using @a, @a;
 
 --echo End of 5.0 tests
diff -Nrup a/mysql-test/t/ps.test b/mysql-test/t/ps.test
--- a/mysql-test/t/ps.test	2008-02-21 14:58:27 -03:00
+++ b/mysql-test/t/ps.test	2008-02-28 11:34:05 -03:00
@@ -1947,4 +1947,23 @@ prepare stmt from "create view v1 as sel
 --error ER_NO_SUCH_TABLE
 prepare stmt from "create view v1 as select * from `t1` `b`";
 
+#
+# Bug#33851: Passing UNSIGNED param to EXECUTE returns ERROR 1210
+#
+
+prepare stmt from "select ?";
+set @arg= 123456789.987654321;
+select @arg;
+execute stmt using @arg;
+set @arg= "string";
+select @arg;
+execute stmt using @arg;
+set @arg= 123456;
+select @arg;
+execute stmt using @arg;
+set @arg= cast(-12345.54321 as decimal(20, 10));
+select @arg;
+execute stmt using @arg;
+deallocate prepare stmt;
+
 --echo End of 5.0 tests.
diff -Nrup a/mysql-test/t/rpl_user_variables.test b/mysql-test/t/rpl_user_variables.test
--- a/mysql-test/t/rpl_user_variables.test	2007-05-24 07:35:42 -03:00
+++ b/mysql-test/t/rpl_user_variables.test	2008-02-28 11:34:05 -03:00
@@ -337,6 +337,23 @@ select * from t1;
 connection master;
 drop table t1;
 
+#
+# Bug#33851: Passing UNSIGNED param to EXECUTE returns ERROR 1210
+#
+
+connection master;
+create table t1(a int);
+insert into t1 values (1),(2);
+prepare s1 from 'insert into t1 select a from t1 limit ?';
+set @x='1.1';
+execute s1 using @x;
+select * from t1;
+sync_slave_with_master;
+connection slave;
+select * from t1;
+connection master;
+drop table t1;
+
 --echo End of 5.0 tests.
 
 # This test uses a stored function that uses user-defined variables to return data
diff -Nrup a/sql/item.cc b/sql/item.cc
--- a/sql/item.cc	2008-02-08 08:55:54 -02:00
+++ b/sql/item.cc	2008-02-28 11:34:05 -03:00
@@ -2385,7 +2385,7 @@ default_set_param_func(Item_param *param
 
 
 Item_param::Item_param(unsigned pos_in_query_arg) :
-  strict_type(FALSE),
+  limit_clause_param(FALSE),
   state(NO_VALUE),
   item_result_type(STRING_RESULT),
   /* Don't pretend to be a literal unless value for this item is set. */
@@ -2581,8 +2581,13 @@ bool Item_param::set_from_user_var(THD *
   {
     item_result_type= entry->type;
     unsigned_flag= entry->unsigned_flag;
-    if (strict_type && required_result_type != item_result_type)
-      DBUG_RETURN(1);
+    if (limit_clause_param)
+    {
+      my_bool unused;
+      set_int(entry->val_int(&unused), MY_INT64_NUM_DECIMAL_DIGITS);
+      item_type= Item::INT_ITEM;
+      DBUG_RETURN(!unsigned_flag && value.integer < 0 ? 1 : 0);
+    }
     switch (item_result_type) {
     case REAL_RESULT:
       set_double(*(double*)entry->value);
diff -Nrup a/sql/item.h b/sql/item.h
--- a/sql/item.h	2008-02-21 14:58:27 -03:00
+++ b/sql/item.h	2008-02-28 11:34:05 -03:00
@@ -1417,8 +1417,6 @@ class Item_param :public Item
   char cnvbuf[MAX_FIELD_WIDTH];
   String cnvstr;
   Item *cnvitem;
-  bool strict_type;
-  enum Item_result required_result_type;
 
 public:
   enum enum_item_param_state
@@ -1548,11 +1546,8 @@ public:
     Otherwise return FALSE.
   */
   bool eq(const Item *item, bool binary_cmp) const;
-  void set_strict_type(enum Item_result result_type_arg)
-  {
-    strict_type= TRUE;
-    required_result_type= result_type_arg;
-  }
+  /** Item is a argument to a limit clause. */
+  bool limit_clause_param;
 };
 
 
diff -Nrup a/sql/item_func.cc b/sql/item_func.cc
--- a/sql/item_func.cc	2008-01-17 15:13:30 -02:00
+++ b/sql/item_func.cc	2008-02-28 11:34:06 -03:00
@@ -3985,7 +3985,7 @@ double user_var_entry::val_real(my_bool 
 
 /* Get the value of a variable as an integer */
 
-longlong user_var_entry::val_int(my_bool *null_value)
+longlong user_var_entry::val_int(my_bool *null_value) const
 {
   if ((*null_value= (value == 0)))
     return LL(0);
diff -Nrup a/sql/sql_class.h b/sql/sql_class.h
--- a/sql/sql_class.h	2007-12-15 09:04:58 -02:00
+++ b/sql/sql_class.h	2008-02-28 11:34:06 -03:00
@@ -2329,7 +2329,7 @@ class user_var_entry
   bool unsigned_flag;
 
   double val_real(my_bool *null_value);
-  longlong val_int(my_bool *null_value);
+  longlong val_int(my_bool *null_value) const;
   String *val_str(my_bool *null_value, String *str, uint decimals);
   my_decimal *val_decimal(my_bool *null_value, my_decimal *result);
   DTCollation collation;
diff -Nrup a/sql/sql_yacc.yy b/sql/sql_yacc.yy
--- a/sql/sql_yacc.yy	2008-01-23 19:04:18 -02:00
+++ b/sql/sql_yacc.yy	2008-02-28 11:34:06 -03:00
@@ -6320,7 +6320,7 @@ limit_options:
 limit_option:
         param_marker
         {
-          ((Item_param *) $1)->set_strict_type(INT_RESULT);
+          ((Item_param *) $1)->limit_clause_param= TRUE;
         }
         | ULONGLONG_NUM { $$= new Item_uint($1.str, $1.length); }
         | LONG_NUM     { $$= new Item_uint($1.str, $1.length); }