MariaDB crash with big_tables=1 and CTE

SET big_tables=1;

CREATE TABLE t1 (id int, name char(10), leftpar int, rightpar int);

INSERT INTO t1 VALUES

  (1, "A", 2, 3),(2, "LA", 4, 5),(4, "LLA", 6, 7),(6, "LLLA", NULL, NULL),(7, "RLLA", NULL, NULL),(5, "RLA", 8, 9),(8, "LRLA", NULL, NULL),

  (9, "RRLA", NULL, NULL),(3, "RA", 10, 11),(10, "LRA", 12, 13),(11, "RRA", 14, 15),(15, "RRRA", NULL, NULL),(16, "B", 17, 18),

  (17, "LB", NULL, NULL),(18, "RB", NULL, NULL);

CREATE TABLE t2 SELECT * FROM t1 ORDER BY rand();

WITH RECURSIVE tree_of_a AS

  (SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A"

    UNION ALL

  SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.leftpar

    UNION ALL

  SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.rightpar)

SELECT * FROM tree_of_a

ORDER BY path;

DROP TABLE t1,t2;

180313 13:31:55 [ERROR] mysqld got signal 11 ;

Server version: 10.2.14-MariaDB-debug-log

key_buffer_size=1048576

read_buffer_size=131072

max_used_connections=1

max_threads=153

thread_count=2

It is possible that mysqld could use up to 

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 63177 K  bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fb46c000a98

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...

stack_bottom = 0x7fb47c8c9e70 thread_stack 0x49000

/home/alice/git/10.2/sql/mysqld(my_print_stacktrace+0x38)[0x56289c7f614a]

/home/alice/git/10.2/sql/mysqld(handle_fatal_signal+0x3dc)[0x56289c08863d]

/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7fb48323e390]

sql/sql_select.cc:17585(create_internal_tmp_table(TABLE*, st_key*, st_maria_columndef*, st_maria_columndef**, unsigned long long))[0x56289be84fa2]

sql/sql_select.cc:18345(instantiate_tmp_table(TABLE*, st_key*, st_maria_columndef*, st_maria_columndef**, unsigned long long))[0x56289be86e8c]

sql/sql_cte.cc:1402(With_element::instantiate_tmp_tables())[0x56289bfe58cf]

sql/sql_derived.cc:1023(TABLE_LIST::fill_recursive(THD*))[0x56289bde961f]

sql/sql_derived.cc:1097(mysql_derived_fill(THD*, LEX*, TABLE_LIST*))[0x56289bde98e2]

sql/sql_derived.cc:197(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x56289bde7922]

sql/sql_select.cc:11941(st_join_table::preread_init())[0x56289be771fb]

sql/sql_select.cc:18659(sub_select(JOIN*, st_join_table*, bool))[0x56289be87370]

sql/sql_select.cc:18235(do_select(JOIN*, Procedure*))[0x56289be86aa2]

sql/sql_select.cc:3566(JOIN::exec_inner())[0x56289be6150f]

sql/sql_select.cc:3362(JOIN::exec())[0x56289be609b0]

sql/sql_select.cc:3763(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x56289be61b80]

sql/sql_select.cc:376(handle_select(THD*, LEX*, select_result*, unsigned long))[0x56289be56161]

sql/sql_parse.cc:6456(execute_sqlcom_select(THD*, TABLE_LIST*))[0x56289be220e0]

sql/sql_parse.cc:3467(mysql_execute_command(THD*))[0x56289be17e5e]

sql/sql_parse.cc:7902(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x56289be25acc]

sql/sql_parse.cc:1808(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x56289be136fb]

sql/sql_parse.cc:1360(do_command(THD*))[0x56289be12057]

sql/sql_connect.cc:1335(do_handle_one_connection(CONNECT*))[0x56289bf607d5]

sql/sql_connect.cc:1242(handle_one_connection)[0x56289bf60555]

perfschema/pfs.cc:1864(pfs_spawn_thread)[0x56289c2bdfd8]

/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7fb4832346ba]

/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fb4826c941d]

Trying to get some variables.

Some pointers may be invalid and cause the dump to abort.

Query (0x7fb46c0d6010): with recursive tree_of_a as ( select *, cast(id as char(200)) as path from t2 where name="A" union all select t2.*, concat(tree_of_a.path,",",t2.id) from t2 join tree_of_a on t2.id=tree_of_a.leftpar union all select t2.*, concat(tree_of_a.path,",",t2.id) from t2 join tree_of_a on t2.id=tree_of_a.rightpar ) select * from tree_of_a order by path

Connection ID (thread ID): 4

Status: NOT_KILLED

Program terminated with signal SIGSEGV, Segmentation fault.

#0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62

62	../sysdeps/unix/sysv/linux/pthread_kill.c: No such file or directory.

[Current thread is 1 (Thread 0x7fb47c8ca700 (LWP 3045))]

#0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62

#1  0x000056289c7f623c in my_write_core (sig=11) at /home/alice/git/10.2/mysys/stacktrace.c:477

#2  0x000056289c088a65 in handle_fatal_signal (sig=11) at /home/alice/git/10.2/sql/signal_handler.cc:305

#3  <signal handler called>

#4  0x000056289be84fa2 in create_internal_tmp_table (table=0x7fb46c0cc130, keyinfo=0x7fb46c0cd240, start_recinfo=0x7fb46c0cd308, recinfo=0x7fb46c0e3e00, options=0) at /home/alice/git/10.2/sql/sql_select.cc:17585

#5  0x000056289be86e8c in instantiate_tmp_table (table=0x7fb46c0cc130, keyinfo=0x7fb46c0cd240, start_recinfo=0x7fb46c0cd308, recinfo=0x7fb46c0e3e00, options=0) at /home/alice/git/10.2/sql/sql_select.cc:18345

#6  0x000056289bfe58cf in With_element::instantiate_tmp_tables (this=0x7fb46c05f3b0) at /home/alice/git/10.2/sql/sql_cte.cc:1403

#7  0x000056289bde961f in TABLE_LIST::fill_recursive (this=0x7fb46c0d5650, thd=0x7fb46c000a98) at /home/alice/git/10.2/sql/sql_derived.cc:1023

#8  0x000056289bde98e2 in mysql_derived_fill (thd=0x7fb46c000a98, lex=0x7fb46c004578, derived=0x7fb46c0d5650) at /home/alice/git/10.2/sql/sql_derived.cc:1097

#9  0x000056289bde7922 in mysql_handle_single_derived (lex=0x7fb46c004578, derived=0x7fb46c0d5650, phases=96) at /home/alice/git/10.2/sql/sql_derived.cc:197

#10 0x000056289be771fb in st_join_table::preread_init (this=0x7fb46c1ba510) at /home/alice/git/10.2/sql/sql_select.cc:11942

#11 0x000056289be87370 in sub_select (join=0x7fb46c1cfbe0, join_tab=0x7fb46c1ba510, end_of_records=false) at /home/alice/git/10.2/sql/sql_select.cc:18659

#12 0x000056289be86aa2 in do_select (join=0x7fb46c1cfbe0, procedure=0x0) at /home/alice/git/10.2/sql/sql_select.cc:18235

#13 0x000056289be6150f in JOIN::exec_inner (this=0x7fb46c1cfbe0) at /home/alice/git/10.2/sql/sql_select.cc:3566

#14 0x000056289be609b0 in JOIN::exec (this=0x7fb46c1cfbe0) at /home/alice/git/10.2/sql/sql_select.cc:3361

#15 0x000056289be61b80 in mysql_select (thd=0x7fb46c000a98, tables=0x7fb46c0d5650, wild_num=1, fields=..., conds=0x0, og_num=1, order=0x7fb46c057ff0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fb46c0350d0, unit=0x7fb46c004640, select_lex=0x7fb46c004d98) at /home/alice/git/10.2/sql/sql_select.cc:3761

#16 0x000056289be56161 in handle_select (thd=0x7fb46c000a98, lex=0x7fb46c004578, result=0x7fb46c0350d0, setup_tables_done_option=0) at /home/alice/git/10.2/sql/sql_select.cc:376

#17 0x000056289be220e0 in execute_sqlcom_select (thd=0x7fb46c000a98, all_tables=0x7fb46c0d5650) at /home/alice/git/10.2/sql/sql_parse.cc:6456

#18 0x000056289be17e5e in mysql_execute_command (thd=0x7fb46c000a98) at /home/alice/git/10.2/sql/sql_parse.cc:3467

#19 0x000056289be25acc in mysql_parse (thd=0x7fb46c000a98, rawbuf=0x7fb46c0d6010 "with recursive tree_of_a as\n(\nselect *, cast(id as char(200)) as path from t2 where name=\"A\"\nunion all\nselect t2.*, concat(tree_of_a.path,\",\",t2.id) from t2 join tree_of_a on\nt2.id=tree_of_a.leftpar\nu"..., length=345, parser_state=0x7fb47c8c91f0, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:7902

#20 0x000056289be136fb in dispatch_command (command=COM_QUERY, thd=0x7fb46c000a98, packet=0x7fb46c007fe9 "", packet_length=345, is_com_multi=false, is_next_command=false) at /home/alice/git/10.2/sql/sql_parse.cc:1806

#21 0x000056289be12057 in do_command (thd=0x7fb46c000a98) at /home/alice/git/10.2/sql/sql_parse.cc:1360

#22 0x000056289bf607d5 in do_handle_one_connection (connect=0x56289f993c18) at /home/alice/git/10.2/sql/sql_connect.cc:1335

#23 0x000056289bf60555 in handle_one_connection (arg=0x56289f993c18) at /home/alice/git/10.2/sql/sql_connect.cc:1241

#24 0x000056289c2bdfd8 in pfs_spawn_thread (arg=0x56289f8f3838) at /home/alice/git/10.2/storage/perfschema/pfs.cc:1862

#25 0x00007fb4832346ba in start_thread (arg=0x7fb47c8ca700) at pthread_create.c:333

#26 0x00007fb4826c941d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109