Bug #31253 crash comparing datetime to double
Submitted: 27 Sep 2007 18:18 Modified: 24 Oct 2007 19:39
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S1 (Critical)
Version:5.1.23, 5.0.48 OS:Any
Assigned to: Tatiana Azundris Nuernberg
Tags: crash

[27 Sep 2007 18:18] Shane Bester
Description:
Version: '5.1.23-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  yes
070927 19:49:52 - mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388572
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337620 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x8dbc7d8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x42864cdc, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x820147b handle_segfault + 541
0x8505286 decimal2double + 22
0x8150bdd Item::val_real_from_decimal() + 83
0x81af00d Item_datetime_typecast::val_real() + 17
0x8187858 Arg_comparator::compare_real_fixed() + 22
0x8188632 Item_func_eq::val_int() + 92
0x8268dc3 make_join_select(JOIN*, SQL_SELECT*, Item*) + 727
0x825e255 JOIN::optimize() + 3239
0x8261b29 _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select + 553
0x825c69d handle_select(THD*, st_lex*, select_result*, unsigned long) + 365
0x8215e54 execute_sqlcom_select(THD*, TABLE_LIST*) + 772
0x820eb35 mysql_execute_command(THD*) + 1701
0x82178b0 mysql_parse(THD*, char const*, unsigned int, char const**) + 372
0x820cf60 dispatch_command(enum_server_command, THD*, char*, unsigned int) + 2354
0x820c622 do_command(THD*) + 600
0x820b01d handle_one_connection + 255
0x40038aa7 _end + 931809239
0x4017ec2e _end + 933144926
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/refman/5.1/en/resolve-stack-dump.html
and follow instructions on how to resolve the stack trace.
Resolved stack trace is much more helpful in diagnosing the
problem, so please do resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8deb9a8 = select 1 from t1 where (convert(`col000`,datetime)) = (coercibility(`col005`))
thd->thread_id=1

How to repeat:
drop table if exists t1;
create table `t1` (`col000` time)engine=myisam;
insert into `t1` set `col000` = '45:44:44';
select 1 from t1 where (convert(`col000`,datetime)) = 1;

Suggested fix:
Although I don't recognize this as a duplicate bug, it perhaps would be fixed along with the other recent type conversion bugs. Different stack trace+testcase, so different report.
[1 Oct 2007 23:06] Tatiana Azundris Nuernberg
## BT for crash:

#0  0x085bc814 in decimal2double (from=0x0, to=0xb51c2e58) at decimal.c:958
#1  0x08190606 in my_decimal2double (mask=30, d=0x0, result=0xb51c2e58) at my_decimal.h:280
#2  0x081805c9 in Item::val_real_from_decimal (this=0x8d81068) at item.cc:303
#3  0x081e0bed in Item_datetime_typecast::val_real (this=0x8d81068) at item_timefunc.h:857
#4  0x081b493a in Arg_comparator::compare_real_fixed (this=0x8d811b8) at item_cmpfunc.cc:1087
#5  0x081c3583 in Arg_comparator::compare (this=0x8d811b8) at item_cmpfunc.h:71
#6  0x081b90b1 in Item_func_eq::val_int (this=0x8d81140) at item_cmpfunc.cc:1502
#7  0x08292aa3 in make_join_select (join=0x8d812a0, select=0x8d82530, cond=0x8d81140) at sql_select.cc:5726

## BT leading up to crash

#0  Item::val_decimal_from_date (this=0x8d81088, decimal_value=0xb51f3e20) at item.cc:277
#1  0x081e0a5b in Item_datetime_typecast::val_decimal (this=0x8d81088, decimal_value=0xb51f3e20) at item_timefunc.h:862
#2  0x0818059a in Item::val_real_from_decimal (this=0x8d81088) at item.cc:300
#3  0x081e0bed in Item_datetime_typecast::val_real (this=0x8d81088) at item_timefunc.h:857
#4  0x081b493a in Arg_comparator::compare_real_fixed (this=0x8d811d8) at item_cmpfunc.cc:1087
#5  0x081c3583 in Arg_comparator::compare (this=0x8d811d8) at item_cmpfunc.h:71
#6  0x081b90b1 in Item_func_eq::val_int (this=0x8d81160) at item_cmpfunc.cc:1502
#7  0x08292aa3 in make_join_select (join=0x8d812c0, select=0x8d825f0, cond=0x8d81160) at sql_select.cc:5726

Problem is here:

double Item::val_real_from_decimal()
{
  /* Note that fix_fields may not be called for Item_avg_field items */
  double result;
  my_decimal value_buff, *dec_val= val_decimal(&value_buff);
  if (null_value)
    return 0.0;
  my_decimal2double(E_DEC_FATAL_ERROR, dec_val, &result);
  return result;
}

val_decimal() throws because time value is out of range (45:00:00 throws, 12:00:00 does not). This results in dec_val becoming NULL, but since null_value is not set, we then call my_decimal2double(...., dec_val==NULL, ...), which crashes like a drunk on a bike.

Potential fix:

my_decimal *Item::val_decimal_from_date(my_decimal *decimal_value)
{
  DBUG_ASSERT(fixed == 1);
  MYSQL_TIME ltime;
  if (get_date(&ltime, TIME_FUZZY_DATE))
  {
    my_decimal_set_zero(decimal_value);
--> null_value= 1;
    return 0;
  }
  return date2my_decimal(&ltime, decimal_value);
}

This prevents further evaluation and throws a "Truncated incorrect datetime" warning to boot, which seems reasonable.

Alt approaches:

double Item::val_real_from_decimal()
if (null_value) => if (!dec_val)
This should return 0.0 with no warning.

OR

my_decimal2double(E_DEC_FATAL_ERROR, &value_buff, &result);

value_buff is always set to allocated memory, and Item::val_decimal_from_date() called my_decimal_set_zero() on that buffer. Be a total waste of time of course to convert a value we already know is 0 / invalid.
[5 Oct 2007 10:09] 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/34954

ChangeSet@1.2526, 2007-10-05 12:08:38+02:00, tnurnberg@sin.intern.azundris.com +3 -0
  Bug #31253: crash comparing datetime to double
  
  convert(<invalid time>, datetime) in WHERE caused crash as function
  returned (void*)NULL, but did not flag SQL NULL. It does now.
[6 Oct 2007 1: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/35025

ChangeSet@1.2568, 2007-10-06 03:01:30+02:00, tnurnberg@sin.intern.azundris.com +1 -0
  Bug #31253: crash comparing datetime to double
  
  after-merge fixies
[6 Oct 2007 5:13] Tatiana Azundris Nuernberg
pushed to 5.0.52, 5.1.23 maint
[18 Oct 2007 21:34] Bugs System
Pushed into 5.1.23-beta
[18 Oct 2007 21:37] Bugs System
Pushed into 5.0.52
[24 Oct 2007 19:39] Paul Dubois
Noted in 5.0.52, 5.1.23 changelogs.

CONVERT(val, DATETIME) would fail on invalid input, but processing
was not aborted for the WHERE clause, leading to a server crash.