Bug #28450 Certain datetime expression in select list causes assertion failure
Submitted: 15 May 2007 18:00 Modified: 7 Jun 2007 17:06
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1-bk, 5.0-bk OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any

[15 May 2007 18:00] Sergey Petrunya
Description:
A query with datetime expression in the select list

  select '2007-01-01' + interval a day from t1;

fails with assertion failure in the server. If SELECT is replaced with CREATE ... SELECT then the statement executes successfully.

How to repeat:
Run this:

create table t1 (a int);
insert into t1 values (1),(2); 
select '2007-01-01' + interval a day from t1;

And see it crash like this:

#0  0xffffe410 in __kernel_vsyscall ()
#1  0xb7d83690 in raise () from /lib/libc.so.6
#2  0xb7d84cf0 in abort () from /lib/libc.so.6
#3  0xb7d7cdab in __assert_fail () from /lib/libc.so.6
#4  0x082672bf in Protocol_text::store_date (this=0x8fcab58, tm=0xb50c99b8) at protocol.cc:990
#5  0x081b6e49 in Item::send (this=0x8fcc0a0, protocol=0x8fcab58, buffer=0xb50c9d6c) at item.cc:4970
#6  0x0825e7c2 in select_send::send_data (this=0x8fcc400, items=@0x8fcb94c) at sql_class.cc:1214
#7  0x082db339 in end_send (join=0x8fcc410, join_tab=0x8fcda60, end_of_records=false) at sql_select.cc:11609
#8  0x082e1474 in evaluate_join_record (join=0x8fcc410, join_tab=0x8fcd8e0, error=0, report_error=0x8fcab30 "")
    at sql_select.cc:10892
#9  0x082e16a9 in sub_select (join=0x8fcc410, join_tab=0x8fcd8e0, end_of_records=false) at sql_select.cc:10782
#10 0x082e99eb in do_select (join=0x8fcc410, fields=0x8fcb94c, table=0x0, procedure=0x0) at sql_select.cc:10539
#11 0x082ffd90 in JOIN::exec (this=0x8fcc410) at sql_select.cc:2093
#12 0x082fb8a5 in mysql_select (thd=0x8fca858, rref_pointer_array=0x8fcb9bc, tables=0x8fcc1f0, wild_num=0, 
    fields=@0x8fcb94c, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, 
    select_options=2147764736, result=0x8fcc400, unit=0x8fcb67c, select_lex=0x8fcb8b8) at sql_select.cc:2258
#13 0x08300057 in handle_select (thd=0x8fca858, lex=0x8fcb614, result=0x8fcc400, setup_tables_done_option=0)
    at sql_select.cc:258
#14 0x0828373e in execute_sqlcom_select (thd=0x8fca858, all_tables=0x8fcc1f0) at sql_parse.cc:4422
#15 0x08284f8d in mysql_execute_command (thd=0x8fca858) at sql_parse.cc:1837
#16 0x0828d9dc in mysql_parse (thd=0x8fca858, inBuf=0x8fcbef0 "select '2007-01-01' + interval a day from t1", length=44)
    at sql_parse.cc:5314
#17 0x0828e4d2 in dispatch_command (command=COM_QUERY, thd=0x8fca858, packet=0x8fd48d1 "", packet_length=45)
    at sql_parse.cc:903
[15 May 2007 20:14] Sveta Smirnova
Thank you for the report.

Verified as described.
[29 May 2007 18: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/commits/27613

ChangeSet@1.2504, 2007-05-29 22:39:25+04:00, evgen@moonbone.local +4 -0
  Bug#28450: The Item_date_add_interval in select list may fail the field 
  type assertion.
  
  The bug caused by the fix for bug#16377.
  The "+ INTERVAL" (Item_date_add_interval) function detects its result type
  by the type of its first argument. But in some cases it returns STRING
  as the result type. This happen when, for example, the first argument is a 
  DATE represented as string. All this makes the get_datetime_value()
  function to misinterpret such result and return wrong DATE/DATETIME value.
  To avoid such cases in the fix #16377 the code that detects correct result
  field type on the first execution was added to the
  Item_date_add_interval::get_date() function. But because of this the result
  field type of the Item_date_add_interval item stored by the send_fields()
  function is differ from its result field type when the item is actually
  being sent and thus failing th field type assertion.
  
  Now the get_datetime_value() detects that the DATE value was returned by
  some item not only by checking the result field type but also by comparing
  the returned value with the 100000000L constant - any DATE value should be
  less than it.
  Removed result field type adjusting code from the
  Item_date_add_interval::get_date() function.
[29 May 2007 20:05] 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/27618

ChangeSet@1.2504, 2007-05-30 00:02:32+04:00, evgen@moonbone.local +4 -0
  Bug#28450: The Item_date_add_interval in select list may fail the field 
  type assertion.
  
  The bug caused by the fix for bug#16377.
  The "+ INTERVAL" (Item_date_add_interval) function detects its result type
  by the type of its first argument. But in some cases it returns STRING
  as the result type. This happens when, for example, the first argument is a 
  DATE represented as string. All this makes the get_datetime_value()
  function to misinterpret such result and return wrong DATE/DATETIME value.
  To avoid such cases in the fix for #16377 the code that detects correct result
  field type on the first execution was added to the
  Item_date_add_interval::get_date() function. Due to this the result
  field type of the Item_date_add_interval item stored by the send_fields()
  function differs from item's result field type at the moment when
  the item is actually sent. It causes an assertion failure.
  
  Now the get_datetime_value() detects that the DATE value is returned by
  some item not only by checking the result field type but also by comparing
  the returned value with the 100000000L constant - any DATE value should be
  less than this value.
  Removed result field type adjusting code from the
  Item_date_add_interval::get_date() function.
[29 May 2007 20:20] 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/27619

ChangeSet@1.2504, 2007-05-30 00:17:26+04:00, evgen@moonbone.local +4 -0
  Bug#28450: The Item_date_add_interval in select list may fail the field 
  type assertion.
  
  The bug was introduced by the patch for bug #16377.
  The "+ INTERVAL" (Item_date_add_interval) function detects its result type
  by the type of its first argument. But in some cases it returns STRING
  as the result type. This happens when, for example, the first argument is a 
  DATE represented as string. All this makes the get_datetime_value()
  function to misinterpret such result and return wrong DATE/DATETIME value.
  To avoid such cases in the fix for #16377 the code that detects correct result
  field type on the first execution was added to the
  Item_date_add_interval::get_date() function. Due to this the result
  field type of the Item_date_add_interval item stored by the send_fields()
  function differs from item's result field type at the moment when
  the item is actually sent. It causes an assertion failure.
  
  Now the get_datetime_value() detects that the DATE value is returned by
  some item not only by checking the result field type but also by comparing
  the returned value with the 100000000L constant - any DATE value should be
  less than this value.
  Removed result field type adjusting code from the
  Item_date_add_interval::get_date() function.
[29 May 2007 20:36] 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/27621

ChangeSet@1.2504, 2007-05-30 00:33:12+04:00, evgen@moonbone.local +4 -0
  Bug#28450: The Item_date_add_interval in select list may fail the field 
  type assertion.
  
  The bug was introduced by the patch for bug #16377.
  The "+ INTERVAL" (Item_date_add_interval) function detects its result type
  by the type of its first argument. But in some cases it returns STRING
  as the result type. This happens when, for example, the first argument is a 
  DATE represented as string. All this makes the get_datetime_value()
  function misinterpret such result and return wrong DATE/DATETIME value.
  To avoid such cases in the fix for #16377 the code that detects correct result
  field type on the first execution was added to the
  Item_date_add_interval::get_date() function. Due to this the result
  field type of the Item_date_add_interval item stored by the send_fields()
  function differs from item's result field type at the moment when
  the item is actually sent. It causes an assertion failure.
  
  Now the get_datetime_value() detects that the DATE value is returned by
  some item not only by checking the result field type but also by comparing
  the returned value with the 100000000L constant - any DATE value should be
  less than this value.
  Removed result field type adjusting code from the
  Item_date_add_interval::get_date() function.
[4 Jun 2007 21:21] Bugs System
Pushed into 5.1.20-beta
[4 Jun 2007 21:23] Bugs System
Pushed into 5.0.44
[7 Jun 2007 17:06] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.44 and 5.1.20 changelogs.