Server crashes in delete_tree_element / ... / Item_func_group_concat::repack_tree

#3  <signal handler called>

#4  0x000055be0ec41156 in delete_tree_element (tree=0x7fee10555d38, element=0xa5a5a5a5a5a5a5a5) at /data/src/10.1/mysys/tree.c:182

#5  0x000055be0ec41009 in free_tree (tree=0x7fee10555d38, free_flags=0) at /data/src/10.1/mysys/tree.c:145

#6  0x000055be0ec41110 in delete_tree (tree=0x7fee10555d38) at /data/src/10.1/mysys/tree.c:168

#7  0x000055be0e69b9c6 in Item_func_group_concat::repack_tree (this=0x7fee10555be8, thd=0x7fee123f2070) at /data/src/10.1/sql/item_sum.cc:3348

#8  0x000055be0e69bdb4 in Item_func_group_concat::add (this=0x7fee10555be8) at /data/src/10.1/sql/item_sum.cc:3410

#9  0x000055be0e69d663 in Aggregator_simple::add (this=0x7fee10556020) at /data/src/10.1/sql/item_sum.h:672

#10 0x000055be0e41a64f in Item_sum::aggregator_add (this=0x7fee10555be8) at /data/src/10.1/sql/item_sum.h:521

#11 0x000055be0e410c75 in update_sum_func (func_ptr=0x7fee105afaa0) at /data/src/10.1/sql/sql_select.cc:23534

#12 0x000055be0e407c2f in end_send_group (join=0x7fee105ae088, join_tab=0x7fee105563e0, end_of_records=false) at /data/src/10.1/sql/sql_select.cc:19824

#13 0x000055be0e404fbe in evaluate_join_record (join=0x7fee105ae088, join_tab=0x7fee10556098, error=0) at /data/src/10.1/sql/sql_select.cc:18702

#14 0x000055be0e404a64 in sub_select (join=0x7fee105ae088, join_tab=0x7fee10556098, end_of_records=false) at /data/src/10.1/sql/sql_select.cc:18518

#15 0x000055be0e404133 in do_select (join=0x7fee105ae088, fields=0x7fee105ae488, table=0x0, procedure=0x0) at /data/src/10.1/sql/sql_select.cc:18134

#16 0x000055be0e3de98e in JOIN::exec_inner (this=0x7fee105ae088) at /data/src/10.1/sql/sql_select.cc:3275

#17 0x000055be0e3dbc1f in JOIN::exec (this=0x7fee105ae088) at /data/src/10.1/sql/sql_select.cc:2562

#18 0x000055be0e3df1b5 in mysql_select (thd=0x7fee123f2070, rref_pointer_array=0x7fee123f6560, tables=0x7fee10447658, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x0, group=0x7fee104489d8, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fee10448b70, unit=0x7fee123f5bb8, select_lex=0x7fee123f62b8) at /data/src/10.1/sql/sql_select.cc:3499

#19 0x000055be0e3d486e in handle_select (thd=0x7fee123f2070, lex=0x7fee123f5af0, result=0x7fee10448b70, setup_tables_done_option=0) at /data/src/10.1/sql/sql_select.cc:388

#20 0x000055be0e3a4215 in execute_sqlcom_select (thd=0x7fee123f2070, all_tables=0x7fee10447658) at /data/src/10.1/sql/sql_parse.cc:5941

#21 0x000055be0e39a897 in mysql_execute_command (thd=0x7fee123f2070) at /data/src/10.1/sql/sql_parse.cc:3038

#22 0x000055be0e3a7e59 in mysql_parse (thd=0x7fee123f2070, rawbuf=0x7fee10443088 "SELECT \n( SELECT MAX(a) FROM t1 WHERE t2_sq.c > 0 ) AS f, \nGROUP_CONCAT( t2_sq.b ORDER BY 1 ) AS gc \nFROM\n( SELECT t2_a.* FROM t2 AS t2_a, t2 AS t2_b ) AS t2_sq, \nt3,\nt4 \nGROUP BY f", length=181, parser_state=0x7fee1b7e01e0) at /data/src/10.1/sql/sql_parse.cc:7459

#23 0x000055be0e396861 in dispatch_command (command=COM_QUERY, thd=0x7fee123f2070, packet=0x7fee143f0071 "", packet_length=181) at /data/src/10.1/sql/sql_parse.cc:1499

#24 0x000055be0e3955ec in do_command (thd=0x7fee123f2070) at /data/src/10.1/sql/sql_parse.cc:1131

#25 0x000055be0e4d0f05 in do_handle_one_connection (thd_arg=0x7fee123f2070) at /data/src/10.1/sql/sql_connect.cc:1330

#26 0x000055be0e4d0c69 in handle_one_connection (arg=0x7fee123f2070) at /data/src/10.1/sql/sql_connect.cc:1242

#27 0x000055be0e7cf618 in pfs_spawn_thread (arg=0x7fee18839ef0) at /data/src/10.1/storage/perfschema/pfs.cc:1861

#28 0x00007fee1b460494 in start_thread (arg=0x7fee1b7e1700) at pthread_create.c:333

#29 0x00007fee193fb93f in clone () from /lib/x86_64-linux-gnu/libc.so.6

SET group_concat_max_len= 8;

CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES (1),(2);

CREATE TABLE t2 (b DATE, c INT);

INSERT INTO t2 VALUES ('2019-12-04',1),('2020-03-28',2);

CREATE TABLE t3 (d INT);

INSERT INTO t3 VALUES

  (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14);

CREATE TABLE t4 (e INT);

INSERT INTO t4 VALUES

  (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15);

SELECT 

  ( SELECT MAX(a) FROM t1 WHERE t2_sq.c > 0 ) AS f, 

  GROUP_CONCAT( t2_sq.b ORDER BY 1 ) AS gc 

FROM

  ( SELECT t2_a.* FROM t2 AS t2_a, t2 AS t2_b ) AS t2_sq, 

  t3,

  t4 

GROUP BY f;

# Cleanup

DROP TABLE t1, t2, t3, t4;

commit 979cad229148ba8d28d94c1ca621bacd11847b66

Author: Sergei Golubchik

Date:   Wed Jun 21 16:07:54 2017 +0200

    MDEV-9531 GROUP_CONCAT with ORDER BY inside takes a lot of memory while it's executed

    group concat tree is allocated in a memroot, so the only way to free

    memory is to copy a part of the tree into a new memroot.

    track the accumilated length of the result, and when it crosses

    the threshold  - copy the result into a new tree, free the old one.