MySQL Bugs: #30587: mysql crashes when trying to group by TIME div NUMBER

Bug #30587	mysql crashes when trying to group by TIME div NUMBER
Submitted:	23 Aug 2007 8:45	Modified:	5 Nov 2007 3:13
Reporter:	Oleg Petrachev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	4.1, 5.0, 5.1	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	crash, div, GROUP, time

Description:
Mysql crashes when asked to select from table with grouping by time divided by some number.

Many other queries work fine, looks like that crash happens only when GROUP BY, table column with TIMESTAMP type and DIV meet together.

Tested on mysql 4.1.21 and 5.0.44.

How to repeat:

CREATE DATABASE time_bug;
USE time_bug;

CREATE TABLE time_bug (time timestamp);
INSERT INTO time_bug values(now()), (now());
SELECT time div 900 a FROM time_bug GROUP BY a;

5.1 is also affected, gdb backtrace from 5.1 crash:

(gdb) bt
#0  0x0868c7b3 in DoTrace (cs=0xa5a5a5a5) at dbug.c:1531
#1  0x0868bca7 in _db_enter_ (_func_=0x881aeff "NdbTableImpl::~NdbTableImpl", 
    _file_=0x881acf6 "NdbDictionaryImpl.cpp", _line_=424, _sfunc_=0x4177dfa8, 
    _sfile_=0x4177dfa4, _slevel_=0x4177dfa0, _sframep_=0x4177df9c) at dbug.c:972
#2  0x085e404b in ~NdbTableImpl (this=0x897f080) at NdbDictionaryImpl.cpp:424
#3  0x085fb832 in __tcf_1 () at DictCache.cpp:24
#4  0x400f6204 in exit () from /lib/tls/libc.so.6
#5  0x082781c9 in handle_segfault (sig=11) at mysqld.cc:2277
#6  <signal handler called>
#7  0x401370d7 in memset () from /lib/tls/libc.so.6
#8  0xffffffff in ?? ()
#9  0x0866a61f in _mymalloc (size=4294967288, filename=0x8755a4d "sql_string.cc", 
    lineno=51, MyFlags=16) at safemalloc.c:194
#10 0x08273f13 in String::real_alloc (this=0x4177e8c0, arg_length=4294967288)
    at sql_string.cc:51
#11 0x081f0978 in String::alloc (this=0x4177e8c0, arg_length=4294967285)
    at sql_string.h:199
#12 0x0824adda in Field_longlong::val_str (this=0x8ffbc90, val_buffer=0x4177e8c0, 
    val_ptr=0x4177e8c0) at field.cc:3678
#13 0x081cc00c in Field::val_str (this=0x8ffbc90, str=0x4177e8c0) at field.h:108
#14 0x0826fc6b in Protocol_text::store (this=0x8fd0a10, field=0x8ffbc90)
    at protocol.cc:950
#15 0x081c66ce in Item_field::send (this=0x8ffea38, protocol=0x8fd0a10, 
    buffer=0x4177ec3c) at item.cc:4997
#16 0x082615f0 in select_send::send_data (this=0x8ffd140, items=@0x8ffe29c)
    at sql_class.cc:1351
#17 0x082f80ac in end_send (join=0x8ffd150, join_tab=0x9002d50, end_of_records=false)
    at sql_select.cc:11674
#18 0x082f4172 in evaluate_join_record (join=0x8ffd150, join_tab=0x9002bd0, error=0, 
    report_error=0x8fd09e8 '�' <repeats 200 times>...) at sql_select.cc:10957
#19 0x082f452a in sub_select (join=0x8ffd150, join_tab=0x9002bd0, end_of_records=false)
---Type <return> to continue, or q <return> to quit---
    at sql_select.cc:10841
#20 0x082f48a0 in do_select (join=0x8ffd150, fields=0x8ffe29c, table=0x0, 
    procedure=0x0) at sql_select.cc:10604
#21 0x083097e7 in JOIN::exec (this=0x8ffd150) at sql_select.cc:2097
#22 0x08309c38 in mysql_select (thd=0x8fd0710, rref_pointer_array=0x8fd18a0, 
    tables=0x8ffce10, wild_num=0, fields=@0x8fd1830, conds=0x0, og_num=1, order=0x0, 
    group=0x8ffd0b8, having=0x0, proc_param=0x0, select_options=2147764736, 
    result=0x8ffd140, unit=0x8fd1540, select_lex=0x8fd179c) at sql_select.cc:2273
#23 0x08309f63 in handle_select (thd=0x8fd0710, lex=0x8fd14e4, result=0x8ffd140, 
    setup_tables_done_option=0) at sql_select.cc:258
#24 0x0828628c in execute_sqlcom_select (thd=0x8fd0710, all_tables=0x8ffce10)
    at sql_parse.cc:4493
#25 0x0828c08e in mysql_execute_command (thd=0x8fd0710) at sql_parse.cc:1838
#26 0x08294ce7 in mysql_parse (thd=0x8fd0710, 
    inBuf=0x8ffcb80 '�' <repeats 200 times>..., length=46, found_semicolon=0x41780310)
    at sql_parse.cc:5396
#27 0x0829580b in dispatch_command (command=COM_QUERY, thd=0x8fd0710, 
    packet=0x8ff4b21 '�' <repeats 200 times>..., packet_length=47) at sql_parse.cc:909
#28 0x082969ab in do_command (thd=0x8fd0710) at sql_parse.cc:668
#29 0x08284a88 in handle_one_connection (arg=0x8fd0710) at sql_connect.cc:1094
#30 0x40038297 in start_thread () from /lib/tls/libpthread.so.0
#31 0x4018a37e in clone () from /lib/tls/libc.so.6
#32 0x41780bb0 in ?? ()

this also crashes. SELECT distinct time div 900 a FROM time_bug;

time type isn't the only type that crashes. this crashes too:

drop table if exists table0;
create table table0(a longblob)engine=myisam;
insert into table0 values('1'),('2'),('3');
select distinct (a div 254576881) from `table0` ;

whoever fixes this should test every single type.

another crashing variant without group by or distinct.  union all.

drop table if exists t1;
create table t1(a longblob)engine=myisam;
insert into t1 values('1'),('2'),('3');
select (a div 254576881) from `t1` union all select (a div 254576881) from `t1`;

another variant using SET:

drop table if exists t1;
create table t1(`col5` set('a','b','c'))engine=myisam;
insert into t1 values ('a');
select col5 div 2 from t1 union select col5 div 2 from t1;

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

ChangeSet@1.2529, 2007-09-19 17:08:56+02:00, gkodinov@macbook.local +3 -0
  Bug #30587: mysql crashes when trying to group by TIME div NUMBER
  
  The Field_timestamp class was not returning correct information 
  about the number of decimals. It was returning the default number 
  for Field_str, that was wrong. 
  This caused an excessive amount of memory to be allocated for the 
  field's buffer and crashed the server.
  
   
  Fixed by returning a correct number of decimals for a timestamp field.

Georgi, please check my testcase on "[23 Aug 19:52] Shane Bester"
which doesn't use a timestamp field.  Did you fix that crash too?

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

ChangeSet@1.2529, 2007-09-22 11:10:32+02:00, gkodinov@macbook.local +3 -0
  Bug #30587: mysql crashes when trying to group by TIME div NUMBER
  
  When calculating the result length of an integer DIV function 
  the number of decimals was used without checking the result type
  first. Thus an uninitialized number of decimals was used for some 
  types. This caused an excessive amount of memory to be allocated 
  for the field's buffer and crashed the server.
  
   
  Fixed by checking the result type of the argument before using the
  decimals field.

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

ChangeSet@1.2529, 2007-09-28 13:56:44+03:00, gkodinov@macbook.gmz +3 -0
  Bug #30587: mysql crashes when trying to group by TIME div NUMBER
  
  When calculating the result length of an integer DIV function 
  the number of decimals was used without checking the result type
  first. Thus an uninitialized number of decimals was used for some 
  types. This caused an excessive amount of memory to be allocated 
  for the field's buffer and crashed the server.
  
   
  Fixed by using the number of decimals only for data types that 
  can have decimals and thus have valid decimals number.

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

ChangeSet@1.2529, 2007-09-28 16:46:05+03:00, gkodinov@macbook.gmz +3 -0
  Bug #30587: mysql crashes when trying to group by TIME div NUMBER
  
  When calculating the result length of an integer DIV function 
  the number of decimals was used without checking the result type
  first. Thus an uninitialized number of decimals was used for some 
  types. This caused an excessive amount of memory to be allocated 
  for the field's buffer and crashed the server.
  
   
  Fixed by using the number of decimals only for data types that 
  can have decimals and thus have valid decimals number.

Pushed into 5.0.52

Pushed into 5.1.23-beta

Pushed into 6.0.4-alpha

Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

Using GROUP BY on an expression of the form timestamp_col DIV number
caused a server crash due to incorrect calculation of number of
decimals.