| Bug #26020 | User-Defined Variables are not consistence with columns data types | ||
|---|---|---|---|
| Submitted: | 1 Feb 2007 16:56 | Modified: | 17 Oct 2008 17:13 |
| Reporter: | Elan Oren | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S2 (Serious) |
| Version: | 5.0.24-standard, 4.1 BK, 5.1 BK | OS: | Linux (Linux) |
| Assigned to: | Gleb Shchepa | CPU Architecture: | Any |
| Tags: | bfsm_2007_02_15 | ||
[1 Feb 2007 16:56]
Elan Oren
[2 Feb 2007 8:47]
Sveta Smirnova
Thank you for the report. It seems to be duplicate of Bug #7498. Please upgrade to currnet 5.0.33 version, try again and say us results.
[8 Feb 2007 7:55]
Sveta Smirnova
test case
Attachment: bug26020.test (application/octet-stream, text), 389 bytes.
[8 Feb 2007 7:58]
Sveta Smirnova
Thank you for the report. Further analysis showed problem still repeatable with current sources. Verified as described. All versions are affected.
[24 Jun 2008 16:01]
Mark Callaghan
Another testcase that does the wrong thing on 5.0.37, 5.0.51, 5.0.62 and 5.1.25 What is the ETA for the fix? This is a wrong query result. CREATE TEMPORARY TABLE testi (Id int); INSERT INTO testi VALUES (2), (3), (3), (4); SET @lastId=-1; select @lastId != Id, @lastId, @lastId := Id from testi; CREATE TEMPORARY TABLE testbi (Id bigint); INSERT INTO testbi VALUES (2), (3), (3), (4); SET @lastId=-1; select @lastId != Id, @lastId, @lastId := Id from testbi; Correct result (for int) @lastId != Id @lastId @lastId := Id 1 -1 2 1 2 3 0 3 3 1 3 4 Incorrect result (for bigint) @lastId != Id @lastId @lastId := Id 1 -1 2 1 2 3 1 3 3 1 3 4
[24 Jun 2008 16:02]
Mark Callaghan
Stack trace to the code that evaluates val_int() to get the value of the user variable when the column type is bigint. Stack frame 0 should be using Item_func_get_user_var::val_int().
#0 Item_int::val_int (this=0x8a2e938) at item.h:1501
#1 0x0813b75c in Arg_comparator::compare_int_signed (this=0x8a2d1a4) at item_cmpfunc.cc:643
#2 0x08146373 in Arg_comparator::compare (this=0x8a2d1a4) at item_cmpfunc.h:66
#3 0x0813c8c4 in Item_func_ne::val_int (this=0x8a2d130) at item_cmpfunc.cc:996
#4 0x08110f60 in Item::send (this=0x8a2d130, protocol=0x89eec34, buffer=0xf4ca707c) at item.cc:4740
#5 0x08188b24 in select_send::send_data (this=0x8a2da78, items=@0x89ee4bc) at sql_class.cc:964
#6 0x081ff214 in end_send (join=0x8a2da88, join_tab=0x8a2ed88, end_of_records=false) at sql_select.cc:11179
#7 0x081fb77e in evaluate_join_record (join=0x8a2da88, join_tab=0x8a2ec10, error=0, report_error=0x89eec0c "") at sql_select.cc:10472
#8 0x081fbb5f in sub_select (join=0x8a2da88, join_tab=0x8a2ec10, end_of_records=false) at sql_select.cc:10361
#9 0x081fbe3b in do_select (join=0x8a2da88, fields=0x89ee4bc, table=0x0, procedure=0x0) at sql_select.cc:10118
#10 0x0820dd21 in JOIN::exec (this=0x8a2da88) at sql_select.cc:1930
#11 0x0820e088 in mysql_select (thd=0x89ee158, rref_pointer_array=0x89ee54c, tables=0x8a2d870, wild_num=0, fields=@0x89ee4bc, conds=0x0, og_num=0,
order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2189707776, result=0x8a2da78, unit=0x89ee1f8, select_lex=0x89ee430)
at sql_select.cc:2097
#12 0x0820e3fa in handle_select (thd=0x89ee158, lex=0x89ee194, result=0x8a2da78, setup_tables_done_option=0) at sql_select.cc:256
#13 0x081b22d0 in mysql_execute_command (thd=0x89ee158) at sql_parse.cc:2628
#14 0x081b9d1f in mysql_parse (thd=0x89ee158, inBuf=0x8a2cf90 "select @lastId != Id, @lastId, @lastId := Id from test", length=54) at sql_parse.cc:5948
#15 0x081ba707 in dispatch_command (command=COM_QUERY, thd=0x89ee158, packet=0x8a24f61 "", packet_length=55) at sql_parse.cc:1786
[24 Jun 2008 16:28]
Mark Callaghan
This call stack shows where the Item_int is created
#1 0x0814609f in Item_int (this=0x8a2e938, i=-1, length=21) at item.h:1493
#2 0x081461e1 in Item_int_with_ref (this=0x8a2e938, i=-1, ref_arg=0x8a2d010, unsigned_arg=0 '\0') at item.h:2009
#3 0x0813a9f9 in convert_constant_item (thd=0x89ee158, field=0x89efda0, item=0x8a2d180) at item_cmpfunc.cc:269
#4 0x0813ad21 in Item_bool_func2::fix_length_and_dec (this=0x8a2d130) at item_cmpfunc.cc:345
#5 0x08128a45 in Item_func::fix_fields (this=0x8a2d130, thd=0x89ee158, ref=0x8a2d22c) at item_func.cc:189
#6 0x081de262 in setup_fields (thd=0x89ee158, ref_pointer_array=0x8a2e880, fields=@0x89ee4bc, set_query_id=true, sum_func_list=0x8a2e7c0,
allow_sum_func=true) at sql_base.cc:4441
#7 0x08207000 in JOIN::prepare (this=0x8a2da88, rref_pointer_array=0x89ee54c, tables_init=0x8a2d870, wild_num=0, conds_init=0x0, og_num=0,
order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x89ee430, unit_arg=0x89ee1f8) at sql_select.cc:345
#8 0x0820e06b in mysql_select (thd=0x89ee158, rref_pointer_array=0x89ee54c, tables=0x8a2d870, wild_num=0, fields=@0x89ee4bc, conds=0x0, og_num=0,
order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2189707776, result=0x8a2da78, unit=0x89ee1f8, select_lex=0x89ee430)
at sql_select.cc:2075
#9 0x0820e48e in handle_select (thd=0x89ee158, lex=0x89ee194, result=0x8a2da78, setup_tables_done_option=0) at sql_select.cc:256
#10 0x081b2360 in mysql_execute_command (thd=0x89ee158) at sql_parse.cc:2628
#11 0x081b9daf in mysql_parse (thd=0x89ee158, inBuf=0x8a2cf90 "select @lastId != Id, @lastId, @lastId := Id from testi", length=55) at sql_parse.cc:5948
#12 0x081ba797 in dispatch_command (command=COM_QUERY, thd=0x89ee158, packet=0x8a24f61 "select @lastId != Id, @lastId, @lastId := Id from testi",
packet_length=56) at sql_parse.cc:1786
#13 0x081bbbfa in do_command (thd=0x89ee158) at sql_parse.cc:1568
#14 0x081bc1f5 in handle_one_connection (arg=0x89ee158) at sql_parse.cc:1194
#15 0x4df183a8 in start_thread () from /lib/tls/i686/cmov/libpthread.so.0
#16 0x4de697fe in clone () from /lib/tls/i686/cmov/libc.so.6
It is done by the code in convert_constant_item. In this case, (*item)->const_item() is true. Alas, there is little debug value in *item as the name field is null:
(gdb) p **item
$2 = {_vptr.Item = 0x84728e8, rsize = 0, str_value = {Ptr = 0x0, str_length = 0, Alloced_length = 0, alloced = false, str_charset = 0x8615300},
name = 0x0, orig_name = 0x0, next = 0x0, max_length = 20, name_length = 0, marker = 0 '\0', decimals = 0 '\0', maybe_null = 1 '\001',
null_value = 0 '\0', unsigned_flag = 0 '\0', with_sum_func = 0 '\0', fixed = 1 '\001', is_autogenerated_name = 1 '\001', collation = {
collation = 0x8615300, derivation = DERIVATION_IMPLICIT}, with_subselect = 0 '\0', cmp_context = INT_RESULT}
static bool convert_constant_item(THD *thd, Field *field, Item **item)
{
if (!(*item)->with_subselect && (*item)->const_item())
{
/* For comparison purposes allow invalid dates like 2000-01-32 */
ulong orig_sql_mode= thd->variables.sql_mode;
thd->variables.sql_mode|= MODE_INVALID_DATES;
if (!(*item)->save_in_field(field, 1) && !((*item)->null_value))
{
Item *tmp=new Item_int_with_ref(field->val_int(), *item,
test(field->flags & UNSIGNED_FLAG));
thd->variables.sql_mode= orig_sql_mode;
if (tmp)
thd->change_item_tree(item, tmp);
return 1; // Item was replaced
}
thd->variables.sql_mode= orig_sql_mode;
}
return 0;
}
[24 Jun 2008 16:31]
Sveta Smirnova
Workaround: select if(cast(b as signed)=@var, 999, b) , @var := b from t1 order by b; (for original description) select @lastId != cast(Id as signed), @lastId, @lastId := Id from testbi; (For Mark's case)
[24 Jun 2008 17:50]
Mark Callaghan
And for anyone reading ths, but problem is from Item_bool_func2::fix_length_and_dec(), field->can_be_compared_as_longlong() is true when the bigint column and conversion to a constant is done.
real_item= args[1]->real_item();
if (real_item->type() == FIELD_ITEM /* && !real_item->const_item() */)
{
Field *field=((Item_field*) real_item)->field;
if (field->can_be_compared_as_longlong())
{
if (convert_constant_item(thd, field,&args[0]))
{
cmp.set_cmp_func(this, tmp_arg, tmp_arg+1,
INT_RESULT); // Works for all types.
args[0]->cmp_context= args[1]->cmp_context= INT_RESULT;
return;
}
}
}
}
set_cmp_func();
}
And I will guess that the root of the problem is the implementation of const_item(). This is called at the start of the statement. For statements that get and set user variables, current_thd->query_id != var_entry->update_query_id at the start of the query but should become equal during the query. This makes me worry a lot about SQL that uses user variables.
bool Item_func_get_user_var::const_item() const
{
return (!var_entry || current_thd->query_id != var_entry->update_query_id);
}
A workaround might be to use this which can miss out on optimizations. A better fix is to know whether the user variable can be set in the same statement, but fix_fields might not be called for Item_func_set_user_var until after being called for Item_func_get_user_var.
bool Item_func_get_user_var::const_item() const
{
return (!var_entry);
}
[13 Aug 2008 20:01]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/51565 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.
[20 Aug 2008 16:44]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/52072 2688 Gleb Shchepa 2008-08-20 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.
[12 Sep 2008 6:26]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/53920 2683 Gleb Shchepa 2008-09-12 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.
[18 Sep 2008 9:14]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/54275 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.
[18 Sep 2008 12:25]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/54294 2687 Gleb Shchepa 2008-09-18 Post-push fix for bug #26020: User-Defined Variables are not consistence with columns data types. --ps-protocol problem has been fixed.
[18 Sep 2008 12:35]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/54295 2687 Gleb Shchepa 2008-09-18 Post-push fix for bug #26020: User-Defined Variables are not consistence with columns data types. --ps-protocol problem has been fixed.
[7 Oct 2008 20:06]
Paul DuBois
Noted in 5.1.29 changelog. The server could improperly type user-defined variables used in the select list of a query. Leaving report status unchanged; this is early documentation of an upcoming push into 5.1.29.
[9 Oct 2008 18:02]
Bugs System
Pushed into 5.1.30 (revid:gshchepa@mysql.com-20080918122409-kpl4wd31ec9b80ia) (version source revid:kgeorge@mysql.com-20081001094725-vf4mqjkmajlm22qy) (pib:4)
[9 Oct 2008 18:41]
Paul DuBois
Setting report to NDI pending push into 6.0.x.
[17 Oct 2008 16:45]
Bugs System
Pushed into 6.0.8-alpha (revid:gshchepa@mysql.com-20080918122409-kpl4wd31ec9b80ia) (version source revid:kgeorge@mysql.com-20081001100520-exs1tupnfanm1mij) (pib:5)
[17 Oct 2008 17:13]
Paul DuBois
Noted in 6.0.8 changelog.
[28 Oct 2008 21:03]
Bugs System
Pushed into 5.1.29-ndb-6.2.17 (revid:gshchepa@mysql.com-20080918122409-kpl4wd31ec9b80ia) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)
[28 Oct 2008 22:21]
Bugs System
Pushed into 5.1.29-ndb-6.3.19 (revid:gshchepa@mysql.com-20080918122409-kpl4wd31ec9b80ia) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)
[1 Nov 2008 9:47]
Bugs System
Pushed into 5.1.29-ndb-6.4.0 (revid:gshchepa@mysql.com-20080918122409-kpl4wd31ec9b80ia) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)
