bk commit into 5.0 tree (evgen:1.2320) BUG#16861

[eugene]

Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen 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, 2006-12-12 03:48:12+03:00, evgen@stripped +4 -0
  Fixed bug#16861: User defined variable can have a wrong value if a tmp table was
  used.
  
  Sorting by RAND() uses a temporary table in order to get a correct results.
  User defined variable was set during filling the temporary table and later
  on it is substituted for its value from the temporary table. Due to this
  it contains the last value stored in the temporary table.
  
  Now if the result_field is set for the Item_func_set_user_var object it 
  updates variable from the result_field value when being sent to a client.
  
  The Item_func_set_user_var::check() now accepts a use_result_field
  parameter. Depending on it the result_field or the arg[0] is used to get
  current value.
  
  

  mysql-test/r/user_var.result@stripped, 2006-12-12 00:48:29+03:00, evgen@stripped +8 -1
    Extended the test case for bug#18681: User defined variable can have a wrong value if
a tmp table was used.

  mysql-test/t/user_var.test@stripped, 2006-12-12 00:48:28+03:00, evgen@stripped +4 -1
    Extended the test case for bug#18681: User defined variable can have a wrong value if
a tmp table was
    used.

  sql/item_func.cc@stripped, 2006-12-12 03:47:47+03:00, evgen@stripped +100 -0
    Bug#16861: User defined variable can have a wrong value if a tmp table was used.
    Added the save_in_field() member function to the Item_func_set_user_var class.

  sql/item_func.h@stripped, 2006-12-12 03:47:52+03:00, evgen@stripped +1 -0
    Bug#16861: User defined variable can have a wrong value if a tmp table was used.
    Added the save_in_field() member function to the Item_func_set_user_var class.

# 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:	evgen
# Host:	moonbone.local
# Root:	/work/16861-bug-5.0-opt-mysql

--- 1.315/sql/item_func.cc	2006-12-04 15:25:41 +03:00
+++ 1.316/sql/item_func.cc	2006-12-12 03:47:47 +03:00
@@ -4040,6 +4040,106 @@
     Item::make_field(tmp_field);
 }
 
+
+/*
+  Save a value of a user variable into a field
+
+  SYNOPSIS
+    save_in_field()
+      field           a field to save to
+      no_conversion   a flag indicating whether a conversions are allowed
+
+  DESCRIPTION
+    Save a function value of into a field and update the user variable
+    accordingly. If the result field is present and target field isn't
+    the result field then the value from the result field will be used.
+
+    The reason to have this function rather than simply using the result
+    field in the val_xxx() methods is that the value from the result field
+    can't be used always when the result field is defined.
+    Consider following examples:
+    1) when filling a tmp table the result field is present but the value of it
+    is undefined because it's yet have to be filled. Thus we can't use it.
+    2) on execution of an INSERT ... SELECT statement the save_in_field()
+    function will be called to fill the data in the new record. If the SELECT
+    part uses a tmp table then the result field is defined defined and
+    should be used in order to get a correct result.
+
+    Only in this function we can determine whether the result field contain
+    correct values. If the target field and the result field are the same
+    then obviously its values can't be used.
+
+    The difference between this function and regular function is that regular
+    functions are converted to fields after their values are stored in a tmp
+    table. This function can't be converted in the same way because we have
+    to update value of the user variable and this can be done only within a
+    function. Due to this we have to handle the result field in a special
+    way here and in the Item_func_set_user_var::send() function.
+
+  RETURN VALUES
+    FALSE       Ok
+    TRUE        Error
+*/
+
+int Item_func_set_user_var::save_in_field(Field *field, bool no_conversions)
+{
+  bool use_result_field= (result_field && result_field != field);
+  int error;
+
+  check(use_result_field);
+  update();
+
+  if (result_type() == STRING_RESULT ||
+      result_type() == REAL_RESULT &&
+      field->result_type() == STRING_RESULT)
+  {
+    String *result;
+    CHARSET_INFO *cs= collation.collation;
+    char buff[MAX_FIELD_WIDTH];		// Alloc buffer for small columns
+    str_value.set_quick(buff, sizeof(buff), cs);
+    result= entry->val_str(&null_value, &str_value, decimals);
+
+    if (null_value)
+    {
+      str_value.set_quick(0, 0, cs);
+      return set_field_to_null_with_conversions(field, no_conversions);
+    }
+
+    /* NOTE: If null_value == FALSE, "result" must be not NULL.  */
+
+    field->set_notnull();
+    error=field->store(result->ptr(),result->length(),cs);
+    str_value.set_quick(0, 0, cs);
+  }
+  else if (result_type() == REAL_RESULT)
+  {
+    double nr= entry->val_real(&null_value);
+    if (null_value)
+      return set_field_to_null(field);
+    field->set_notnull();
+    error=field->store(nr);
+  }
+  else if (result_type() == DECIMAL_RESULT)
+  {
+    my_decimal decimal_value;
+    my_decimal *value= entry->val_decimal(&null_value, &decimal_value);
+    if (null_value)
+      return set_field_to_null(field);
+    field->set_notnull();
+    error=field->store_decimal(value);
+  }
+  else
+  {
+    longlong nr= entry->val_int(&null_value);
+    if (null_value)
+      return set_field_to_null_with_conversions(field, no_conversions);
+    field->set_notnull();
+    error=field->store(nr, unsigned_flag);
+  }
+  return error;
+}
+
+
 String *
 Item_func_get_user_var::val_str(String *str)
 {

--- 1.160/sql/item_func.h	2006-11-28 16:47:47 +03:00
+++ 1.161/sql/item_func.h	2006-12-12 03:47:52 +03:00
@@ -1189,6 +1189,7 @@
   void print(String *str);
   void print_as_stmt(String *str);
   const char *func_name() const { return "set_user_var"; }
+  int save_in_field(Field *field, bool no_conversions);
 };
 
 

--- 1.43/mysql-test/r/user_var.result	2006-10-04 18:49:37 +04:00
+++ 1.44/mysql-test/r/user_var.result	2006-12-12 00:48:29 +03:00
@@ -301,7 +301,14 @@
 select @var;
 @var
 3
-drop table t1;
+create table t2 as select @var:=f2 from t1 group by f1 order by f2 desc limit 1;
+select * from t2;
+@var:=f2
+3
+select @var;
+@var
+3
+drop table t1,t2;
 insert into city 'blah';
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near ''blah'' at line 1
 SHOW COUNT(*) WARNINGS;

--- 1.38/mysql-test/t/user_var.test	2006-10-04 18:49:37 +04:00
+++ 1.39/mysql-test/t/user_var.test	2006-12-12 00:48:28 +03:00
@@ -210,7 +210,10 @@
 insert into t1 values (1,2),(2,3),(3,1);
 select @var:=f2 from t1 group by f1 order by f2 desc limit 1;
 select @var;
-drop table t1;
+create table t2 as select @var:=f2 from t1 group by f1 order by f2 desc limit 1;
+select * from t2;
+select @var;
+drop table t1,t2;
 
 #
 # Bug#19024 - SHOW COUNT(*) WARNINGS not return Errors