MySQL Bugs: #22138: Server crash on complex select

Bug #22138	Server crash on complex select
Submitted:	9 Sep 2006 3:13	Modified:	23 Oct 2006 14:23
Reporter:	Dave Pullin (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.24a,5.0BK,5.1BK	OS:	Any (*)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
Server repeatably and invariably crashes on the following select:

select 
 week(date)/10 as week, group_concat(distinct t) 
from (
select 
 from_days(s) as date,t
 from (select 1 as s,'t' as t union select null, null ) as sub1
) as sub2
 group by week(date)/10

This is a massive simplification of a real-life query. All further simplifications that I have tried prevent the crash (even removing the '/10's prevents the crash). (Changing the inner select to be a table with the same content does not prevent the crash.)

Also occurs on Linux (Fedora Core 4), using  5.0.24-standard

Console log:
mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 5.0.24a-community-nt-log |
+--------------------------+
1 row in set (0.00 sec)

mysql> select
    ->  week(date)/10 as week, group_concat(distinct t)
    -> from (
    -> select
    ->  from_days(s) as date,t
    ->  from (select 1 as s,'t' as t union select null, null ) as sub1
    -> ) as sub2
    ->  group by week(date)/10;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select version();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...

How to repeat:
select 
 week(date)/10 as week, group_concat(distinct t) 
from (
select 
 from_days(s) as date,t
 from (select 1 as s,'t' as t union select null, null ) as sub1
) as sub2
 group by week(date)/10

Might be related to bug #22015, however the stack traces seem quite different.  Setting to verified and we'll let the devs decide.

5.0.24a-pro-nt
--------------
mysqld_nt!Cached_item_decimal::cmp+0x5b
mysqld_nt!count_field_types+0x157
mysqld_nt!remove_eq_conds+0x4c4
mysqld_nt!free_tmp_table+0x233
mysqld_nt!sub_select+0xa6
mysqld_nt!setup_end_select_func+0x264
mysqld_nt!JOIN::exec+0xe82
mysqld_nt!mysql_select+0x1c7
mysqld_nt!handle_select+0xad
mysqld_nt!mysql_execute_command+0x54e
mysqld_nt!mysql_parse+0x102
mysqld_nt!dispatch_command+0x562
mysqld_nt!do_command+0xad
mysqld_nt!handle_one_connection+0x26e
mysqld_nt!pthread_start+0x3b
mysqld_nt!_threadstart+0x6c

stack traces. 4.1 didn't crash.

Attachment: stacks.txt (plain/text, text), 6.18 KiB.

The simplest example that reproduces the crash is:

create table x1 as
 select from_days(s) as date,t
 from (select 1 as s,'t' as t union select null, null ) as sub1;

-- this crashes
select group_concat(t)
from x1
group by week(date)/10;

-- NOTICE: '*' works
select group_concat(t)
from x1
group by week(date)*10;

The problem is not related to subquery execution.

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

ChangeSet@1.2298, 2006-10-10 21:27:43+04:00, evgen@moonbone.local +3 -0
  Bug#22138: Unhandled NULL caused server crash
  
  The Cached_item_decimal::cmp() method wasn't checking for null pointer
  returned from the val_decimal() of the item being cached.
  This leads to server crash.
  
  The Cached_item_decimal::cmp() method now check for null values.

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

ChangeSet@1.2298, 2006-10-11 19:44:12+04:00, evgen@moonbone.local +3 -0
  Bug#22138: Unhandled NULL caused server crash
  
  The Cached_item_decimal::cmp() method wasn't checking for null pointer
  returned from the val_decimal() of the item being cached.
  This leads to server crash.
  
  The Cached_item_decimal::cmp() method now check for null values.

Pushed in 5.0.27/5.1.13-beta

Noted in 5.0.27, 5.1.13 changelogs.