2686 Gleb Shchepa 2008-09-18
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_base.cc
sql/sql_lex.cc
sql/sql_lex.h
sql/sql_yacc.yy
2685 Tatiana A. Nurnberg 2008-09-16 [merge]
auto-merge
removed:
extra/yassl/taocrypt/taocrypt.vcproj
extra/yassl/yassl.vcproj
mysql-test/suite/rpl/t/rpl_view-slave.opt
server-tools/instance-manager/mysqlmanager.vcproj
added:
mysql-test/r/skip_log_bin.result
mysql-test/t/skip_log_bin-master.opt
mysql-test/t/skip_log_bin.test
win/build-vs9.bat
win/build-vs9_x64.bat
modified:
CMakeLists.txt
configure.in
extra/yassl/Makefile.am
extra/yassl/taocrypt/Makefile.am
libmysql/dll.c
mysql-test/extra/rpl_tests/rpl_ddl.test
mysql-test/extra/rpl_tests/rpl_log.test
mysql-test/include/wait_for_slave_sql_error_and_skip.inc
mysql-test/suite/funcs_1/datadict/processlist_priv.inc
mysql-test/suite/funcs_1/r/processlist_priv_no_prot.result
mysql-test/suite/funcs_1/r/processlist_priv_ps.result
mysql-test/suite/rpl/r/rpl_row_log.result
mysql-test/suite/rpl/r/rpl_row_log_innodb.result
mysql-test/suite/rpl/r/rpl_stm_log.result
mysql-test/suite/rpl/t/rpl_stm_log-slave.opt
mysql-test/suite/rpl_ndb/r/rpl_ndb_log.result
mysql-test/t/partition_not_windows.test
scripts/mysql_install_db.sh
sql/sql_insert.cc
zlib/zutil.h
=== 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-09-18 08:38:44 +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-09-18 08:38:44 +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-09-18 08:38:44 +0000
@@ -3805,6 +3805,24 @@ static user_var_entry *get_variable(HASH
return entry;
}
+
+bool Item_func_set_user_var::set_entry(THD *thd, bool create_if_not_exists)
+{
+ if (thd == entry_thd && entry)
+ return FALSE;
+ entry_thd= thd;
+ if (!(entry= get_variable(&thd->user_vars, name, create_if_not_exists)))
+ 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;
+ return FALSE;
+}
+
+
/*
When a user variable is updated (in a SET command or a query like
SELECT @a:= ).
@@ -3814,15 +3832,8 @@ bool Item_func_set_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) ||
- !(entry= get_variable(&thd->user_vars, name, 1)))
+ if (Item_func::fix_fields(thd, ref) || set_entry(thd, TRUE))
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
=== modified file 'sql/item_func.h'
--- a/sql/item_func.h 2008-03-26 09:27:00 +0000
+++ b/sql/item_func.h 2008-09-18 08:38:44 +0000
@@ -1294,6 +1294,17 @@ class Item_func_set_user_var :public Ite
{
enum Item_result cached_result_type;
user_var_entry *entry;
+ /*
+ The entry_thd variable is used:
+ 1) to skip unnecessary updates of the entry field (see above);
+ 2) to reset the entry field that was initialized in the other thread
+ (for example, an item tree of a trigger that updates user variables
+ may be shared between several connections, and the entry_thd field
+ prevents updates of one connection user variables from a concurrent
+ connection calling the same trigger that initially updated some
+ user variable it the first connection context).
+ */
+ THD *entry_thd;
char buffer[MAX_FIELD_WIDTH];
String value;
my_decimal decimal_buff;
@@ -1309,7 +1320,8 @@ class Item_func_set_user_var :public Ite
public:
LEX_STRING name; // keep it public
Item_func_set_user_var(LEX_STRING a,Item *b)
- :Item_func(b), cached_result_type(INT_RESULT), name(a)
+ :Item_func(b), cached_result_type(INT_RESULT),
+ entry(NULL), entry_thd(NULL), name(a)
{}
enum Functype functype() const { return SUSERVAR_FUNC; }
double val_real();
@@ -1340,6 +1352,7 @@ public:
}
void save_org_in_field(Field *field) { (void)save_in_field(field, 1, 0); }
bool register_field_in_read_map(uchar *arg);
+ bool set_entry(THD *thd, bool create_if_not_exists);
};
=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc 2008-09-03 14:45:40 +0000
+++ b/sql/sql_base.cc 2008-09-18 08:38:44 +0000
@@ -7296,6 +7296,22 @@ bool setup_fields(THD *thd, Item **ref_p
if (ref_pointer_array)
bzero(ref_pointer_array, sizeof(Item *) * fields.elements);
+ /*
+ We call set_entry() there (before fix_fields() of the whole list of field
+ items) because:
+ 1) the list of field items has same order as in the query, and the
+ Item_func_get_user_var item may go before the Item_func_set_user_var:
+ SELECT @a, @a := 10 FROM t;
+ 2) The entry->update_query_id value controls constantness of
+ Item_func_get_user_var items, so in presence of Item_func_set_user_var
+ items we have to refresh their entries before fixing of
+ Item_func_get_user_var items.
+ */
+ List_iterator<Item_func_set_user_var> li(thd->lex->set_var_list);
+ Item_func_set_user_var *var;
+ while ((var= li++))
+ var->set_entry(thd, FALSE);
+
Item **ref= ref_pointer_array;
thd->lex->current_select->cur_pos_in_select_list= 0;
while ((item= it++))
=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc 2008-07-15 01:43:12 +0000
+++ b/sql/sql_lex.cc 2008-09-18 08:38:44 +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-08-11 22:44:13 +0000
+++ b/sql/sql_lex.h 2008-09-18 08:38:44 +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-09-11 07:51:02 +0000
+++ b/sql/sql_yacc.yy 2008-09-18 08:38:44 +0000
@@ -8064,11 +8064,13 @@ variable:
variable_aux:
ident_or_text SET_VAR expr
{
- $$= new (YYTHD->mem_root) Item_func_set_user_var($1, $3);
+ Item_func_set_user_var *item;
+ $$= item= new (YYTHD->mem_root) Item_func_set_user_var($1, $3);
if ($$ == NULL)
MYSQL_YYABORT;
LEX *lex= Lex;
lex->uncacheable(UNCACHEABLE_RAND);
+ lex->set_var_list.push_back(item);
}
| ident_or_text
{
| Thread |
|---|
| • bzr push into mysql-5.1 branch (gshchepa:2685 to 2686) Bug#26020 | Gleb Shchepa | 18 Sep |
