Bug #12589 Assert when creating temp. table from decimal stored procedure variable
Submitted: 15 Aug 2005 19:35 Modified: 30 Sep 2005 18:52
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0-bk OS:Linux (Linux)
Assigned to: Bugs System

[15 Aug 2005 19:35] Sergey Petrunya
Description:
One can cause the server to fail an assertion by doing the following:
* declare an SP variable of type decimal
* Attempt to assign it some invalid value
Create a temporary table from that variable.

How to repeat:
Run this: 

delimiter //
create procedure test()
begin
  declare spv1 decimal(3,3);
  set spv1= 123.456;

  set spv1 = 'test';
  create temporary table tm1 as select spv1;
  show create table tm1;
  drop temporary table tm1;
end//

call test()//

And see server to fail an assertion:
  mysqld: decimal.c:1417: decimal_bin_size: Assertion `scale >= 0 && precision > 0 && scale <= precision' failed.

(gdb) where
  #0  0xb7e013d1 in kill () from /lib/libc.so.6
  #1  0xb7f5f131 in pthread_kill () from /lib/libpthread.so.0
  #2  0xb7f5f4ab in raise () from /lib/libpthread.so.0
  #3  0xb7e01164 in raise () from /lib/libc.so.6
  #4  0xb7e0263d in abort () from /lib/libc.so.6
  #5  0xb7dfa58f in __assert_fail () from /lib/libc.so.6
  #6  0x0842e6cc in decimal_bin_size (precision=0, scale=0) at decimal.c:1417
  #7  0x0811e628 in my_decimal_get_binary_size (precision=0, scale=0) at my_decimal.h:167
  #8  0x0816a756 in Field_new_decimal (this=0x8c89bb8, len_arg=1, maybe_null=true, name=0x8c96f40 "spv1", t_arg=0xb57f44b4, dec_arg=0 '\0', unsigned_arg=false) at field.cc:2309
  #9  0x081f787e in create_tmp_field_from_item (thd=0x8c61788, item=0x8c96eb8, table=0xb57f44b4, copy_func=0x0, modify_item=false, convert_blob_length=0) at sql_select.cc:7965
  #10 0x081f7c54 in create_tmp_field (thd=0x8c61788, table=0xb57f44b4, item=0x8c96eb8, type=DECIMAL_ITEM, copy_func=0x0, from_field=0xb57f448c, group=false, modify_item=false, table_cant_handle_bit_fields=false, convert_blob_length=0) at sql_select.cc:8107
  #11 0x08284c65 in create_table_from_items (thd=0x8c61788, create_info=0x8c9d020, create_table=0x8c96d48, extra_fields=0x8c9cf48, keys=0x8c9cf3c, items=0x8c9cdac, lock=0x8c88dd4) at sql_table.cc:1726
  #12 0x0820e70e in select_create::prepare (this=0x8c88d58, values=@0x8c9cdac, u=0x8c9cb40) at sql_insert.cc:2478
  #13 0x081e4cd3 in JOIN::prepare (this=0x8c88de0, rref_pointer_array=0x8c9ce44, tables_init=0x0, 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=0x8c9cd30, unit_arg=0x8c9cb40) at sql_select.cc:475
  #14 0x081e9ef0 in mysql_select (thd=0x8c61788, rref_pointer_array=0x8c9ce44, tables=0x0, wild_num=0, fields=@0x8c9cdac, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2457881088, result=0x8c88d58, unit=0x8c9cb40, select_lex=0x8c9cd30) at sql_select.cc:2064
  #15 0x081e4315 in handle_select (thd=0x8c61788, lex=0x8c9cb30, result=0x8c88d58, setup_tables_done_option=0) at sql_select.cc:238
  #16 0x081aabc7 in mysql_execute_command (thd=0x8c61788) at sql_parse.cc:2880
  #17 0x082e1975 in sp_instr_stmt::exec_core (this=0x8c96f50, thd=0x8c61788, nextp=0xb57f5354) at sp_head.cc:1604
  #18 0x082e1674 in sp_lex_keeper::reset_lex_and_exec_core (this=0x8c96f78, thd=0x8c61788, nextp=0xb57f5354, open_tables=false, instr=0x8c96f50) at sp_head.cc:1510
  #19 0x082e18bc in sp_instr_stmt::execute (this=0x8c96f50, thd=0x8c61788, nextp=0xb57f5354) at sp_head.cc:1581
  #20 0x082df09d in sp_head::execute (this=0x8c96818, thd=0x8c61788) at sp_head.cc:670
  #21 0x082dfdf7 in sp_head::execute_procedure (this=0x8c96818, thd=0x8c61788, args=0x8c61bfc) at sp_head.cc:950
  #22 0x081aeede in mysql_execute_command (thd=0x8c61788) at sql_parse.cc:4206
  #23 0x081b2367 in mysql_parse (thd=0x8c61788, inBuf=0x8c8ccb0 "call test_splocal4()", length=20) at sql_parse.cc:5426
  #24 0x081a7a9e in dispatch_command (command=COM_QUERY, thd=0x8c61788, packet=0x8c84c51 "call test_splocal4()", packet_length=21) at sql_parse.cc:1657
  #25 0x081a72a6 in do_command (thd=0x8c61788) at sql_parse.cc:1460
  #26 0x081a63b4 in handle_one_connection (arg=0x8c61788) at sql_parse.cc:1113
  #27 0xb7f5c13d in pthread_start_thread () from /lib/libpthread.so.0
  #28 0xb7f5c2e2 in pthread_start_thread_event () from /lib/libpthread.so.0
  #29 0xb7e8e07a in clone () from /lib/libc.so.6
(gdb) 

  #6  0x0842e6cc in decimal_bin_size (precision=0, scale=0) at decimal.c:1417
  Current language:  auto; currently c
(gdb) info locals
  intg = 0
  intg0 = 0
  frac0 = 0
  intg0x = 0
  frac0x = 0
(gdb) where

Suggested fix:
It seems not to be logical that size/precision of SP local variable is changed by assignment operator (what is the point of specifying size/precicion in DECLARE statement then??).

In addition to fixing this bug out we should figure out what type conversion rules we [should] have for SP variables.
[15 Aug 2005 19:41] Miguel Solorzano
Verified on Suse 9.3.
Thank you for the bug report.
[14 Sep 2005 16:02] Per-Erik Martin
Set to P2 since it's a crashing bug.
[20 Sep 2005 12:50] 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/internals/30100
[27 Sep 2005 15:42] 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/internals/30392
[29 Sep 2005 15:26] Per-Erik Martin
Pushed to bk 5.0.14
[30 Sep 2005 18:52] Paul Dubois
Noted in 5.0.14 changelog.