Bug #29417 debug asser on decimal

Description:
This is related to Bug #29415. But it can be triggered outside the scope of 29415  too.

See how to repeat. each set of SQL triggers the following assert. (attempting to materialize an obersized decimal item):

> Assertion failed:
> ((precision <= ((9 * 9) - 8*2)) && (dec <= 30)), function Field_new_decimal, file field.cc, line 2290.

How to repeat:
# Only as a result of 29415
# This one only crashes on the amount of digits after the point

drop table if exists t1;
create table t1 select cast(1 as decimal(610,20)); # works
drop table if exists t1;
create table t1 select cast(1 as decimal(61,60));  # assert

# without cast

set @a = null; set @b = null;
drop table if exists t1;
CREATE TABLE `t1` (  `a` int(11) DEFAULT NULL,  `b` int(11) DEFAULT NULL ) ENGINE=MyISAM;
insert into t1 select 3,30; insert into t1 select 1,10; insert into t1 select 2,10;

select a, @a := sum(a) from t1 group  by  @a,a ; # make @a a decimal type
set @a = null;set @b = null;
select a  from t1 order  by  @a:=null , @b := @a;

# or shorter

set @a = cast(1 as decimal); # correct cast, this does the same as @a := sum()
select 1 from t1 group by @b := @a, @b;

Suggested fix:
In the examples with user variables the assert happens as soon as the user variable will be materialized to a field.

The user variable does not need to be in the groub by. But mysql must attempt to create a temporary table containing the variable.

Some code research (on 5.1.20):

In item_func.cc (line 4610)  Item_func_get_user_var::fix_length_and_dec()
    case DECIMAL_RESULT:
      max_length= DECIMAL_MAX_STR_LENGTH;
      decimals= DECIMAL_MAX_SCALE;
      break;

In my_decimal.h
#define DECIMAL_BUFF_LENGTH 9
#define DECIMAL_MAX_POSSIBLE_PRECISION (DECIMAL_BUFF_LENGTH * 9)
#define DECIMAL_MAX_STR_LENGTH (DECIMAL_MAX_POSSIBLE_PRECISION + 2)

that makes 83 for DECIMAL_MAX_STR_LENGTH; it seems that mysql has internally a higher precission for the decimal type.

In sql_selct.cc (line 9153) create_tmp_field_from_item:
  case DECIMAL_RESULT:
    new_field= new Field_new_decimal(item->max_length, maybe_null, item->name,
                                     item->decimals, item->unsigned_flag);
    break;
 

item->max_length is 83, as set in Item_func_get_user_var::fix_length_and_dec

which results in precission = DECIMAL_MAX_POSSIBLE_PRECISION = 81
but an assert against DECIMAL_MAX_PRECISION = 65

#11 0x284e5bac in raise () from /usr/lib/libpthread.so.1
#12 0x286a3c1b in abort () from /lib/libc.so.5
#13 0x2867e9ff in __assert () from /lib/libc.so.5
#14 0x080c8037 in Field_new_decimal (this=0xa3dc410, len_arg=83, name=0x0, dec_arg=30 '\036', unsigned_arg=false) at field.cc:2335
#15 0x08150d1b in create_tmp_field_from_item (thd=0xa365000, item=0xa3c18f8, table=0xa3fc010, copy_func=0xbfa33adc, modify_item=true, convert_blob_length=171820048)
    at field.h:51
#16 0x0815119f in create_tmp_field (thd=0xa365000, table=0xa3fc010, item=0xa3c18f8, type=Item::FUNC_ITEM, copy_func=0xbfa33adc, from_field=0xa3fc8d8,
    default_field=0x0, group=false, modify_item=true, table_cant_handle_bit_fields=false, make_copy_field=false, convert_blob_length=0) at sql_select.cc:9329
#17 0x08151cf8 in create_tmp_table (thd=0xa365000, param=0xa3c2e00, fields=@0xa3c2ebc, group=0x0, distinct=false, save_sum_fields=false, select_options=2147764736,
    rows_limit=18446744073709551615, table_alias=0xa37c200 "@\234m(\025\ufffd\233\ufffd") at sql_select.cc:9654
#18 0x08141d5b in JOIN::optimize (this=0xa3c1da0) at sql_select.cc:1364
#19 0x08144251 in mysql_select (thd=0xa365000, rref_pointer_array=0xa3660cc, tables=0xa3c11b8, wild_num=0, fields=@0xa36605c, conds=0x0, og_num=2, order=0xa3c1800,
    group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xa3c1d90, unit=0xa365d8c, select_lex=0xa365fc8) at sql_select.cc:2261
#20 0x0813f4c3 in handle_select (thd=0xa365000, lex=0xa365d30, result=0xa3c1d90, setup_tables_done_option=0) at sql_select.cc:246
#21 0x080feef5 in execute_sqlcom_select (thd=0xa365000, all_tables=0xa3c11b8) at sql_parse.cc:4486
#22 0x080f8d11 in mysql_execute_command (thd=0xa365000) at sql_parse.cc:1838
#23 0x0810068b in mysql_parse (thd=0xa365000, inBuf=0xa3c1010 "select a  from t1 order  by  @a:=null , @b := @a", length=3215149556, found_semicolon=0xbfa34acc)
    at sql_parse.cc:5388
#24 0x080f73ca in dispatch_command (command=COM_QUERY, thd=0xa365000, packet=0xa3b5001 "select a  from t1 order  by  @a:=null , @b := @a", packet_length=49)
    at sql_parse.cc:909
#25 0x080f6bb0 in do_command (thd=0xa365000) at sql_parse.cc:668
#26 0x080f585b in handle_one_connection (arg=0x0) at sql_connect.cc:1094
#27 0x284f3902 in pthread_mutexattr_init () from /usr/lib/libpthread.so

Thank you for the bug report. Verified as described.

070628  9:51:14 [Note] libexec/mysqld: ready for connections.
Version: '5.1.21-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
mysqld: field.cc:2336: Field_new_decimal::Field_new_decimal(uint32, bool, const char*, uint8, bool): Assertion `(precision <= ((9 * 9) - 8*2)) && (dec <= 30)' failed.
070628  9:51:52 - mysqld got signal 6;

This problem can be demonstrated with the following query:

mysql> select a+cast(1 as decimal(66,60)) as aa, sum(b) from t1 group by aa;
ERROR 2013 (HY000): Lost connection to MySQL server during query

having on the server side:

Version: '5.0.46-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread -1257141344 (LWP 5033)]
mysqld: field.cc:2290: Field_new_decimal::Field_new_decimal(uint32, bool, const char*, st_table*, uint8, bool): Assertion `(precision <= ((9 * 9) - 8*2)) && (dec <= 30)' failed.

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/30481

ChangeSet@1.2521, 2007-07-07 12:31:55-07:00, igor@olga.mysql.com +3 -0
  Fixed bug #29417.
  An assertion abort could occur for some grouping queries that employed 
  decimal user variables with assignments to them.
  
  The problem appeared the constructors of the class Field_new_decimal
  because the function my_decimal_length_to_precision did not guarantee
  returning decimal precision not greater than DECIMAL_MAX_PRECISION.

Pushed into 5.1.21-beta

Pushed into 5.0.46

Noted in 5.0.46, 5.1.21 changelogs.

Assertion failure could occur for grouping queries that employed  
DECIMAL user variables with assignments to them.