MySQL Bugs: #22015: nested group_concat in subselect results in server crash

Bug #22015	nested group_concat in subselect results in server crash
Submitted:	5 Sep 2006 12:24	Modified:	4 Oct 2006 19:27
Reporter:	Jochen Wersdörfer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	mysql-debug-5.0.24a-linux-i686-icc-glibc	OS:	Linux (Debian-GNU/Linux 2.6.16-2-k7)
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	group_concat, subselect

Description:
Well, MySQL dies with a segfault at a rather unspectacular subselect with two group_concats:

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=8388600
read_buffer_size=131072
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8df9450
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=0xa50b493c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81f41bf
0xffffe420
0x10
0x82f1530
0x82f0beb
0x82efd7b
0x82794c5
0x8286d78
0x825f8d9
0x825f492
0x82111da
0x821c7b0
0x820ea2f
0x820dea4
0x820cd4a
0xa7f990bd
0xa7de08fe
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack tra
ce. 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 0x8e18d38 = select group_concat(a), x from
  (select a, group_concat(b) x
     from t group by a
  ) as s
 group by x
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Following the steps in the "Using_stack_trace.html"-Documentation, i got:

jw@bunt:~$ resolve_stack_dump -s mysqld.sym -n mysqld.stack
0x81f41bf handle_segfault + 519
0xffffe420 _end + -142256016
0x10 (?)
0x82f1530 _Z12make_sortkeyP13st_sort_paramPhPc + 1114
0x82f0beb _Z13find_all_keysP13st_sort_paramP10SQL_SELECTPPhP11st_io_cacheS6_S6_ + 1653
0x82efd7b _Z8filesortP3THDP8st_tableP13st_sort_fieldjP10SQL_SELECTyPy + 1385
0x82794c5 _Z17create_sort_indexP3THDP4JOINP8st_orderyy + 925
0x8286d78 _ZN4JOIN4execEv + 7968
0x825f8d9 _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 849
0x825f492 _Z13handle_selectP3THDP6st_lexP13select_resultm + 422
0x82111da _Z21mysql_execute_commandP3THD + 2078
0x821c7b0 _Z11mysql_parseP3THDPcj + 530
0x820ea2f _Z16dispatch_command19enum_server_commandP3THDPcj + 2943
0x820dea4 _Z10do_commandP3THD + 658
0x820cd4a handle_one_connection + 888
0xa7f990bd _end + -1619065587
0xa7de08fe _end + -1620869810

Using InnoDB instead of MyIsam makes no difference, so probably it's
not a storage-engine issue.

How to repeat:
use test

CREATE TABLE `t` (
  `a` int(10) unsigned NOT NULL default '0',
  `b` int(10) unsigned NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into t (a, b) values (2, 1), (1, 2);

select group_concat(a), x from
  (select a, group_concat(b) x
     from t group by a
  ) as s
 group by x;

=> ERROR 2013 (HY000): Lost connection to MySQL server during query

Thank you for a bug report. Verified just as described with 5.0.25-BK (ChangeSet@1.2258, 2006-09-04 11:45:43+02:00) on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.25-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE `t` (
    ->   `a` int(10) unsigned NOT NULL default '0',
    ->   `b` int(10) unsigned NOT NULL default '0'
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> insert into t (a, b) values (2, 1), (1, 2);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select group_concat(a), x from
    ->   (select a, group_concat(b) x
    ->      from t group by a
    ->   ) as s
    ->  group by x;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
060905 14:50:18  mysqld restarted

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

ChangeSet@1.2273, 2006-09-19 09:45:41-07:00, igor@rurik.mysql.com +3 -0
  Fixed bug #22015: crash with GROUP_CONCAT over a derived table
  that returns the results of aggregation by GROUP_CONCAT.
  The crash was due to an overflow happened for the field
  sortoder->length.
  The fix prevents this overflow exploiting the fact that the
  value of sortoder->length cannot be greater than the value of
  thd->variables.max_sort_length.

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

ChangeSet@1.2275, 2006-09-20 08:08:57-07:00, igor@rurik.mysql.com +3 -0
  Fixed bug #22015: crash with GROUP_CONCAT over a derived table
  that returns the results of aggregation by GROUP_CONCAT.
  The crash was due to an overflow happened for the field
  sortoder->length.
  The fix prevents this overflow exploiting the fact that the
  value of sortoder->length cannot be greater than the value of
  thd->variables.max_sort_length.

Pushed in 5.0.26/5.1.12

Noted in 5.0.26, 5.1.12 changelogs.

For what it's worth you can workaround this bug on legacy installations - replace the GROUP BY with a calculated field - I use LEFT, with MD5 just in case the string is longer than the LEFT value, eg:

SELECT GROUP_CONCAT(a), x from   
(
  SELECT a, GROUP_CONCAT(b) AS x
  FROM t GROUP BY a
) AS s
GROUP BY LEFT(x, 10000), MD5(x);

Tested working on a legacy 5.0.19 system.