bzr commit into mysql-5.1 branch (gshchepa:2688) Bug#26020

[Gleb Shchepa]

#At file:///work/bzr/5.1-bugteam-26020/

 2688 Gleb Shchepa	2008-08-14
      Bug#26020: User-Defined Variables are not consistent with
                 columns data types
      
      The "SELECT @lastId, @lastId := Id FROM t" query returns
      different result sets depending on the type of the Id column
      (INT or BIGINT).
      
      Note: this fix doesn't cover the case when a select query
      references an user variable and stored function that
      updates a value of that variable, in this case a result
      is indeterminate.
      
      
      The server uses incorrect assumption about a constantness of
      an user variable value as a select list item:
      
      The server caches a last query number where that variable
      was changed and compares this number with a current query
      number. If these numbers are different, the server guesses,
      that the variable is not updating in the current query, so
      a respective select list item is a constant. However, in some
      common cases the server updates cached query number too late.
      
      
      The server has been modified to memorize user variable
      assignments during the parse phase to take them into account
      on the next (query preparation) phase independently of the
      order of user variable references/assignments in a select
      item list.
modified:
  mysql-test/r/user_var.result
  mysql-test/t/user_var.test
  sql/item_func.cc
  sql/item_func.h
  sql/sql_lex.cc
  sql/sql_lex.h
  sql/sql_yacc.yy

per-file messages:
  mysql-test/r/user_var.result
    Added test case for bug #26020.
  mysql-test/t/user_var.test
    Added test case for bug #26020.
  sql/item_func.cc
    1. An unnecessary update of update_query_id has been removed
    from the Item_func_set_user_var::fix_fields function:
    this caching works fine for LOAD DATA (see the
    Item_user_var_as_out_param::fix_fields function) only.
    
    2. The Item_func_get_user_var::const_item function has been
    replaced with the Item_func_get_user_var::fix_fields function.
    New function 1) reimplements old const_item function for
    LOAD DATA queries and 2) takes into account the set_var_list
    list (list of all variable assignments in the current
    query) to calculate constantness of the item.
  sql/item_func.h
    1. The Item_func_set_user_var::update_used_tables function
    has been overloadded to not to reset the const_item_cache value.
    
    2. The Item_func_set_user_var::const_item function has been
    replace with the fix_fields function.
  sql/sql_lex.cc
    The lex_start function has been modified to reset
    the st_lex::set_var_list list.
  sql/sql_lex.h
    New st_lex::set_var_list field has been added to
    memorize all user variable assignments in the current
    select query.
  sql/sql_yacc.yy
    The variable_aux rule has been modified to memorize
    in-query user variable assignments in the
    st_lex::set_var_list list.
=== modified file 'mysql-test/r/user_var.result'
--- a/mysql-test/r/user_var.result	2007-12-13 11:56:04 +0000
+++ b/mysql-test/r/user_var.result	2008-08-13 20:00:41 +0000
@@ -121,8 +121,8 @@ select @a:=0;
 select @a+0, @a:=@a+0+count(*), count(*), @a+0 from t1 group by i;
 @a+0	@a:=@a+0+count(*)	count(*)	@a+0
 0	1	1	0
-1	3	2	0
-3	6	3	0
+0	2	2	0
+0	3	3	0
 set @a=0;
 select @a,@a:="hello",@a,@a:=3,@a,@a:="hello again" from t1 group by i;
 @a	@a:="hello"	@a	@a:=3	@a	@a:="hello again"
@@ -370,4 +370,33 @@ select @rownum := @rownum + 1 as row,
 @prev_score := a as score
 from t1 order by score desc;
 drop table t1;
+create table t1(b bigint);
+insert into t1 (b) values (10), (30), (10);
+set @var := 0;
+select if(b=@var, 999, b) , @var := b from t1  order by b;
+if(b=@var, 999, b)	@var := b
+10	10
+999	10
+30	30
+drop table t1;
+create temporary table t1 (id int);
+insert into t1 values (2), (3), (3), (4);
+set @lastid=-1;
+select @lastid != id, @lastid, @lastid := id from t1;
+@lastid != id	@lastid	@lastid := id
+1	-1	2
+1	2	3
+0	3	3
+1	3	4
+drop table t1;
+create temporary table t1 (id bigint);
+insert into t1 values (2), (3), (3), (4);
+set @lastid=-1;
+select @lastid != id, @lastid, @lastid := id from t1;
+@lastid != id	@lastid	@lastid := id
+1	-1	2
+1	2	3
+0	3	3
+1	3	4
+drop table t1;
 End of 5.1 tests

=== modified file 'mysql-test/t/user_var.test'
--- a/mysql-test/t/user_var.test	2007-12-13 11:56:04 +0000
+++ b/mysql-test/t/user_var.test	2008-08-13 20:00:41 +0000
@@ -263,4 +263,26 @@ from t1 order by score desc;
 --enable_result_log
 drop table t1;
 
+#
+# Bug#26020: User-Defined Variables are not consistent with columns data types
+#
+
+create table t1(b bigint);
+insert into t1 (b) values (10), (30), (10);
+set @var := 0;
+select if(b=@var, 999, b) , @var := b from t1  order by b;
+drop table t1;
+
+create temporary table t1 (id int);
+insert into t1 values (2), (3), (3), (4);
+set @lastid=-1;
+select @lastid != id, @lastid, @lastid := id from t1;
+drop table t1;
+
+create temporary table t1 (id bigint);
+insert into t1 values (2), (3), (3), (4);
+set @lastid=-1;
+select @lastid != id, @lastid, @lastid := id from t1;
+drop table t1;
+
 --echo End of 5.1 tests

=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc	2008-07-31 09:50:24 +0000
+++ b/sql/item_func.cc	2008-08-13 20:00:41 +0000
@@ -3817,12 +3817,6 @@ bool Item_func_set_user_var::fix_fields(
   if (Item_func::fix_fields(thd, ref) ||
       !(entry= get_variable(&thd->user_vars, name, 1)))
     return TRUE;
-  /* 
-     Remember the last query which updated it, this way a query can later know
-     if this variable is a constant item in the query (it is if update_query_id
-     is different from query_id).
-  */
-  entry->update_query_id= thd->query_id;
   /*
     As it is wrong and confusing to associate any 
     character set with NULL, @a should be latin2
@@ -4667,9 +4661,36 @@ void Item_func_get_user_var::fix_length_
 }
 
 
-bool Item_func_get_user_var::const_item() const
+bool Item_func_get_user_var::fix_fields(THD *thd, Item **ref)
 {
-  return (!var_entry || current_thd->query_id != var_entry->update_query_id);
+  DBUG_ENTER("Item_func_get_user_var::fix_fields");
+  DBUG_ASSERT(fixed == 0);
+  /* fix_fields will call Item_func_set_user_var::fix_length_and_dec */
+  if (Item_func::fix_fields(thd, ref))
+    DBUG_RETURN(TRUE);
+
+  const_item_cache= TRUE;
+
+  if (!var_entry)
+    DBUG_RETURN(FALSE);
+
+  if (thd->lex->sql_command == SQLCOM_LOAD)
+  {
+    const_item_cache= current_thd->query_id != var_entry->update_query_id;
+    DBUG_RETURN(FALSE);
+  }
+  List_iterator<Item_func_set_user_var> li(thd->lex->set_var_list);
+  Item_func_set_user_var *var;
+  while ((var= li++))
+  {
+    if (name.length == var->name.length && 
+        !memcmp(name.str, var->name.str, name.length))
+    {
+      const_item_cache= FALSE;
+      break;
+    }
+  }
+  DBUG_RETURN(FALSE);
 }
 
 

=== modified file 'sql/item_func.h'
--- a/sql/item_func.h	2008-03-26 09:27:00 +0000
+++ b/sql/item_func.h	2008-08-13 20:00:41 +0000
@@ -1360,6 +1360,7 @@ public:
   my_decimal *val_decimal(my_decimal*);
   String *val_str(String* str);
   void fix_length_and_dec();
+  bool fix_fields(THD *thd, Item **ref);
   virtual void print(String *str, enum_query_type query_type);
   enum Item_result result_type() const;
   /*
@@ -1367,7 +1368,7 @@ public:
     select @t1:=1,@t1,@t:="hello",@t from foo where (@t1:= t2.b)
   */
   const char *func_name() const { return "get_user_var"; }
-  bool const_item() const;
+  void update_used_tables() {}
   table_map used_tables() const
   { return const_item() ? 0 : RAND_TABLE_BIT; }
   bool eq(const Item *item, bool binary_cmp) const;

=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2008-07-15 01:43:12 +0000
+++ b/sql/sql_lex.cc	2008-08-13 20:00:41 +0000
@@ -293,6 +293,7 @@ void lex_start(THD *thd)
   lex->select_lex.init_query();
   lex->value_list.empty();
   lex->update_list.empty();
+  lex->set_var_list.empty();
   lex->param_list.empty();
   lex->view_list.empty();
   lex->prepared_stmt_params.empty();

=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h	2008-07-15 01:43:12 +0000
+++ b/sql/sql_lex.h	2008-08-13 20:00:41 +0000
@@ -1549,6 +1549,7 @@ typedef struct st_lex : public Query_tab
   List<Item>	      *insert_list,field_list,value_list,update_list;
   List<List_item>     many_values;
   List<set_var_base>  var_list;
+  List<Item_func_set_user_var> set_var_list; // in-query assignment list
   List<Item_param>    param_list;
   List<LEX_STRING>    view_list; // view list (list of field names in view)
   /*

=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy	2008-07-15 01:43:12 +0000
+++ b/sql/sql_yacc.yy	2008-08-13 20:00:41 +0000
@@ -7388,9 +7388,11 @@ variable:
 variable_aux:
           ident_or_text SET_VAR expr
           {
-            $$= new Item_func_set_user_var($1, $3);
+            Item_func_set_user_var *item;
+            $$= item= new Item_func_set_user_var($1, $3);
             LEX *lex= Lex;
             lex->uncacheable(UNCACHEABLE_RAND);
+            lex->set_var_list.push_back(item);
           }
         | ident_or_text
           {