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:
None 
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
Triage: Triaged: D2 (Serious) / R3 (Medium) / E3 (Medium)

[1 Feb 2007 16:56] Elan Oren
Description:
We have found a strange behavior dealing with local variables.

Please take a look at the following example:

set @var := 0
;

insert into test.temp (b, c)
select if(b = @var, 999, b) , @var := b
from test.temp 
order by b;

test.temp contains the following values (all columns are bigint): 
a	b	c
1	10	0
2	30	0
3	10	0

The comparison between a column defined as BIGINT and a local variable defined as INTEGER (set @var := 0) does not work properly.

We ran this test on the above table (including the contained values) and we expected to receive one row with the value 999 in column b (because the value '10' appears more than once and the query is ordered by b).

When we changed the local variable type to string (set @var := ''), we got the expected results.

We would like to understand why it doesn't work properly in the first case.

How to repeat:
Happens each time you run this statement.
[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)