[MDEV-22748] MariaDB crash on WITH RECURSIVE large query Created: 2020-05-29  Updated: 2021-04-19  Resolved: 2020-06-09

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.3.17, 10.3.23, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.5.4, 10.2.33, 10.3.24, 10.4.14

Type: Bug Priority: Major
Reporter: Nilnandan Joshi Assignee: Igor Babaev
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-17024 Crash on large query Closed

 Description   

This should be fixed in these version as per the MDEV-17024
But it is not. Latest MariaDB 10.3.23 version still crashing due it.

Here is the same test case. 

CREATE TABLE t1 (  YEAR int(4), d1 date , d2 date) ;
 
INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20');
 
CREATE TABLE t2 (id int, tm date);
 
INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30');
 
CREATE TABLE t3 (id int, tm date);
 
INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30');
 
 
WITH RECURSIVE 
cte AS
 
  (SELECT  YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn FROM t1
 
   UNION ALL 
   SELECT YEAR(cte.st + INTERVAL 1 MONTH), cte.st + INTERVAL 1 MONTH,  t1.d2 + INTERVAL 1 DAY
 
   FROM cte JOIN t1
 
   WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),
 
               
cte2 AS (SELECT YEAR, COUNT(*) 
        FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn),
 
cte3 AS (SELECT YEAR, COUNT(*) 
        FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn)
 
SELECT 1 FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR);


2020-05-29 12:02:06 0 [Note] /home/nilnandan/Downloads/10.3.23/bin/mysqld: ready for connections.
 
Version: '10.3.23-MariaDB'  socket: '/tmp/mysql_sandbox10323.sock'  port: 10323  MariaDB Server
 
pure virtual method called
 
terminate called without an active exception
 
200529 12:03:51 [ERROR] mysqld got signal 6 ;
 
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.
 
 
 
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
 
 
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.
 
 
 
Server version: 10.3.23-MariaDB
 
key_buffer_size=134217728
 
read_buffer_size=131072
 
max_used_connections=1
 
max_threads=153
 
thread_count=7
 
It is possible that mysqld could use up to
 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467423 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x7fb7fc000c08
 
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 = 0x7fb864de7e90 thread_stack 0x49000
 
/home/nilnandan/Downloads/10.3.23/bin/mysqld(my_print_stacktrace+0x2e)[0x55c4e919bade]
 
mysys/stacktrace.c:270(my_print_stacktrace)[0x55c4e8bf4caf]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12890)[0x7fb869edb890]
 
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xc7)[0x7fb868ba9e97]
 
linux/raise.c:51(__GI_raise)[0x7fb868bab801]
 
/usr/lib/x86_64-linux-gnu/libstdc++.so.6(+0x8c957)[0x7fb869386957]
 
/home/nilnandan/Downloads/10.3.23/bin/mysqld(_ZN10__cxxabiv111__terminateEPFvvE+0x6)[0x55c4e92532f6]
 
libsupc++/eh_terminate.cc:42(__cxxabiv1::__terminate(void (*)()))[0x55c4e9253323]
 
/home/nilnandan/Downloads/10.3.23/bin/mysqld(+0xe2015f)[0x55c4e921b15f]
 
/home/nilnandan/Downloads/10.3.23/bin/mysqld(_ZN7handler22ha_rnd_init_with_errorEb+0x17)[0x55c4e8bfb5a7]
 
sql/handler.h:3104(handler::ha_rnd_init_with_error(bool))[0x55c4e8acfaab]
 
sql/table.cc:8191(TABLE::insert_all_rows_into_tmp_table(THD*, TABLE*, TMP_TABLE_PARAM*, bool))[0x55c4e89e31bb]
 
sql/sql_derived.cc:1142(mysql_derived_fill(THD*, LEX*, TABLE_LIST*))[0x55c4e89e2cbc]
 
sql/sql_derived.cc:199(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x55c4e8a4e859]
 
sql/sql_select.cc:12825(st_join_table::preread_init())[0x55c4e8a4ec18]
 
sql/sql_select.cc:19649(sub_select(JOIN*, st_join_table*, bool))[0x55c4e8a7176b]
 
sql/sql_select.cc:19222(do_select)[0x55c4e8a71983]
 
sql/sql_select.cc:3897(JOIN::exec())[0x55c4e8a6fe9a]
 
sql/sql_select.cc:4303(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*))[0x55c4e89e2fa9]
 
sql/sql_derived.cc:1113(mysql_derived_fill(THD*, LEX*, TABLE_LIST*))[0x55c4e89e2cbc]
 
sql/sql_derived.cc:199(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x55c4e8a4e859]
 
sql/sql_select.cc:12825(st_join_table::preread_init())[0x55c4e8a4ec18]
 
sql/sql_select.cc:19649(sub_select(JOIN*, st_join_table*, bool))[0x55c4e8a43dbc]
 
sql/sql_class.h:3884(evaluate_join_record(JOIN*, st_join_table*, int))[0x55c4e8a4ea9e]
 
sql/sql_select.cc:19689(sub_select(JOIN*, st_join_table*, bool))[0x55c4e8a43dbc]
 
sql/sql_class.h:3884(evaluate_join_record(JOIN*, st_join_table*, int))[0x55c4e8a4ea9e]
 
sql/sql_select.cc:19689(sub_select(JOIN*, st_join_table*, bool))[0x55c4e8a7176b]
 
sql/sql_select.cc:19222(do_select)[0x55c4e8a71983]
 
sql/sql_select.cc:3897(JOIN::exec())[0x55c4e8a6fe9a]
 
sql/sql_select.cc:4303(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*))[0x55c4e8a7099c]
 
sql/sql_select.cc:382(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55c4e892570f]
 
sql/sql_parse.cc:6294(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55c4e8a1850b]
 
sql/sql_parse.cc:3820(mysql_execute_command(THD*))[0x55c4e8a1ed62]
 
sql/sql_parse.cc:7817(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55c4e8a205cf]
 
sql/sql_parse.cc:1919(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55c4e8a221ab]
 
sql/sql_parse.cc:1404(do_command(THD*))[0x55c4e8af6c56]
 
sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x55c4e8af6d6d]
 
nptl/pthread_create.c:463(start_thread)[0x7fb869ed06db]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7fb868c8c88f]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7fb7fc00f610): WITH RECURSIVE  cte AS   (SELECT  YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn FROM t1    UNION ALL     SELECT YEAR(cte.st + INTERVAL 1 MONTH), cte.st + INTERVAL 1 MONTH,  t1.d2 + INTERVAL 1 DAY    FROM cte JOIN t1    WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),                 cte2 AS (SELECT YEAR, COUNT(*)          FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn), cte3 AS (SELECT YEAR, COUNT(*)          FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn) SELECT 1 FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR)
 
Connection ID (thread ID): 10
 
Status: NOT_KILLED



 Comments   
Comment by Alice Sherepa [ 2020-05-29 ]

on debug versions:

10.2 8afcc37c685a0b78c3da2b8

 
#4  0x000055de58ffa5b5 in TABLE::insert_all_rows_into_tmp_table (this=0x7fe5f4032e50, thd=thd@entry=0x7fe5f4000c08, tmp_table=0x7fe5f4035630, tmp_table_param=0x7fe5f4091b08, with_cleanup=with_cleanup@entry=true) at /10.2/sql/table.cc:8006
 
#5  0x000055de58f1e851 in TABLE_LIST::fill_recursive (this=this@entry=0x7fe5f41722b0, thd=thd@entry=0x7fe5f4000c08) at /10.2/sql/sql_derived.cc:1008
 
#6  0x000055de58f1eb93 in mysql_derived_fill (thd=0x7fe5f4000c08, lex=0x7fe5f4004598, derived=0x7fe5f41722b0) at /10.2/sql/sql_derived.cc:1069
 
#7  0x000055de58f1e6eb in mysql_handle_single_derived (lex=0x7fe5f4004598, derived=derived@entry=0x7fe5f41722b0, phases=phases@entry=96) at /10.2/sql/sql_derived.cc:198
 
#8  0x000055de58f79d30 in st_join_table::preread_init (this=0x7fe5f403ce20) at /10.2/sql/sql_select.cc:12105
 
#9  0x000055de58f7a008 in sub_select (join=0x7fe5f4036990, join_tab=0x7fe5f403ce20, end_of_records=<optimized out>) at /10.2/sql/sql_select.cc:18840
 
#10 0x000055de58f9d210 in do_select (procedure=<optimized out>, join=0x7fe5f4036990) at /10.2/sql/sql_select.cc:18416
 
#11 JOIN::exec_inner (this=this@entry=0x7fe5f4036990) at /10.2/sql/sql_select.cc:3638
 
#12 0x000055de58f9d413 in JOIN::exec (this=this@entry=0x7fe5f4036990) at /10.2/sql/sql_select.cc:3433
 
#13 0x000055de58f9b93b in mysql_select (thd=thd@entry=0x7fe5f4000c08, tables=0x7fe5f41722b0, wild_num=0, fields=..., conds=<optimized out>, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416184064, result=0x7fe5f41896d0, unit=0x7fe5f4171b20, select_lex=0x7fe5f4090a80) at /10.2/sql/sql_select.cc:3833
 
#14 0x000055de58f1e98a in mysql_derived_fill (thd=0x7fe5f4000c08, lex=0x7fe5f4004598, derived=0x7fe5f4039120) at /10.2/sql/sql_derived.cc:1097
 
#15 0x000055de58f1e6eb in mysql_handle_single_derived (lex=0x7fe5f4004598, derived=derived@entry=0x7fe5f4039120, phases=phases@entry=96) at /10.2/sql/sql_derived.cc:198
 
#16 0x000055de58f79d30 in st_join_table::preread_init (this=0x7fe5f4031340) at /10.2/sql/sql_select.cc:12105
 
#17 0x000055de58f7a008 in sub_select (join=0x7fe5f4175700, join_tab=0x7fe5f4031340, end_of_records=<optimized out>) at /10.2/sql/sql_select.cc:18840
 
#18 0x000055de58f7256e in evaluate_join_record (join=join@entry=0x7fe5f4175700, join_tab=join_tab@entry=0x7fe5f4030f90, error=<optimized out>) at /10.2/sql/sql_select.cc:19092
 
#19 0x000055de58f79eb9 in sub_select (join=0x7fe5f4175700, join_tab=0x7fe5f4030f90, end_of_records=<optimized out>) at /10.2/sql/sql_select.cc:18872
 
#20 0x000055de58f9d210 in do_select (procedure=<optimized out>, join=0x7fe5f4175700) at /10.2/sql/sql_select.cc:18416
 
#21 JOIN::exec_inner (this=this@entry=0x7fe5f4175700) at /10.2/sql/sql_select.cc:3638
 
#22 0x000055de58f9d413 in JOIN::exec (this=this@entry=0x7fe5f4175700) at /10.2/sql/sql_select.cc:3433
 
#23 0x000055de58f9b93b in mysql_select (thd=thd@entry=0x7fe5f4000c08, tables=0x7fe5f4038ad0, wild_num=0, fields=..., conds=<optimized out>, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fe5f4091a90, unit=0x7fe5f4004658, select_lex=0x7fe5f4004db8) at /10.2/sql/sql_select.cc:3833
 
#24 0x000055de58f9c2cf in handle_select (thd=thd@entry=0x7fe5f4000c08, lex=lex@entry=0x7fe5f4004598, result=result@entry=0x7fe5f4091a90, setup_tables_done_option=setup_tables_done_option@entry=0) at /10.2/sql/sql_select.cc:373
 
#25 0x000055de58e77c8d in execute_sqlcom_select (thd=thd@entry=0x7fe5f4000c08, all_tables=0x7fe5f4038ad0) at /10.2/sql/sql_parse.cc:6226
 
#26 0x000055de58f44995 in mysql_execute_command (thd=thd@entry=0x7fe5f4000c08) at /10.2/sql/sql_parse.cc:3532
 
#27 0x000055de58f4b21a in mysql_parse (thd=thd@entry=0x7fe5f4000c08, rawbuf=<optimized out>, length=500, parser_state=parser_state@entry=0x7fe605224570, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /10.2/sql/sql_parse.cc:7741
 
#28 0x000055de58f4caf6 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7fe5f4000c08, packet=packet@entry=0x7fe5f41771d9 "", packet_length=packet_length@entry=500, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /10.2/sql/sql_parse.cc:1832
 
#29 0x000055de58f4e8dc in do_command (thd=0x7fe5f4000c08) at /10.2/sql/sql_parse.cc:1386
 
#30 0x000055de59020042 in do_handle_one_connection (connect=connect@entry=0x55de5c612038) at /10.2/sql/sql_connect.cc:1336
 
#31 0x000055de5902019d in handle_one_connection (arg=arg@entry=0x55de5c612038) at /10.2/sql/sql_connect.cc:1241
 
#32 0x000055de59613e01 in pfs_spawn_thread (arg=0x55de5c5b9598) at /10.2/storage/perfschema/pfs.cc:1869
 
#33 0x00007fe60c55d6db in start_thread (arg=0x7fe605225700) at pthread_create.c:463
 
#34 0x00007fe60bb5f88f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

10.3 ecc7f305dde85d704a37e584

 
#3  <signal handler called>
 
#4  0x000055e4b367a43f in base_list_iterator::next_fast (this=0x7f1c109a8d60) at /10.3/sql/sql_list.h:442
 
#5  0x000055e4b38a2c91 in List_iterator_fast<TABLE>::operator++ (this=0x7f1c109a8d60) at /10.3/sql/sql_list.h:562
 
#6  0x000055e4b39bbee6 in With_element::instantiate_tmp_tables (this=0x7f1c00017360) at /10.3/sql/sql_cte.cc:1460
 
#7  0x000055e4b3752443 in TABLE_LIST::fill_recursive (this=0x7f1c00018390, thd=0x7f1c00000d50) at /10.3/sql/sql_derived.cc:1050
 
#8  0x000055e4b3752854 in mysql_derived_fill (thd=0x7f1c00000d50, lex=0x7f1c00004b58, derived=0x7f1c00018390) at /10.3/sql/sql_derived.cc:1141
 
#9  0x000055e4b375023c in mysql_handle_single_derived (lex=0x7f1c00004b58, derived=0x7f1c00018390, phases=96) at /10.3/sql/sql_derived.cc:199
 
#10 0x000055e4b380820d in st_join_table::preread_init (this=0x7f1c0004ca88) at /10.3/sql/sql_select.cc:12831
 
#11 0x000055e4b3819d93 in sub_select (join=0x7f1c0003ccc8, join_tab=0x7f1c0004ca88, end_of_records=false) at /10.3/sql/sql_select.cc:19655
 
#12 0x000055e4b38193cf in do_select (join=0x7f1c0003ccc8, procedure=0x0) at /10.3/sql/sql_select.cc:19227
 
#13 0x000055e4b37f02b9 in JOIN::exec_inner (this=0x7f1c0003ccc8) at /10.3/sql/sql_select.cc:4107
 
#14 0x000055e4b37ef68c in JOIN::exec (this=0x7f1c0003ccc8) at /10.3/sql/sql_select.cc:3901
 
#15 0x000055e4b37f0993 in mysql_select (thd=0x7f1c00000d50, tables=0x7f1c00018390, wild_num=0, fields=..., conds=0x7f1c0009a8e0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416184064, result=0x7f1c0003cbe0, unit=0x7f1c000178b0, select_lex=0x7f1c00017498) at /10.3/sql/sql_select.cc:4306
 
#16 0x000055e4b375299a in mysql_derived_fill (thd=0x7f1c00000d50, lex=0x7f1c00004b58, derived=0x7f1c0009e590) at /10.3/sql/sql_derived.cc:1169
 
#17 0x000055e4b375023c in mysql_handle_single_derived (lex=0x7f1c00004b58, derived=0x7f1c0009e590, phases=96) at /10.3/sql/sql_derived.cc:199
 
#18 0x000055e4b380820d in st_join_table::preread_init (this=0x7f1c00054760) at /10.3/sql/sql_select.cc:12831
 
#19 0x000055e4b3819d93 in sub_select (join=0x7f1c000a00f8, join_tab=0x7f1c00054760, end_of_records=false) at /10.3/sql/sql_select.cc:19655
 
#20 0x000055e4b381a66d in evaluate_join_record (join=0x7f1c000a00f8, join_tab=0x7f1c000543d0, error=0) at /10.3/sql/sql_select.cc:19907
 
#21 0x000055e4b3819f31 in sub_select (join=0x7f1c000a00f8, join_tab=0x7f1c000543d0, end_of_records=false) at /10.3/sql/sql_select.cc:19687
 
#22 0x000055e4b38193cf in do_select (join=0x7f1c000a00f8, procedure=0x0) at /10.3/sql/sql_select.cc:19227
 
#23 0x000055e4b37f02b9 in JOIN::exec_inner (this=0x7f1c000a00f8) at /10.3/sql/sql_select.cc:4107
 
#24 0x000055e4b37ef68c in JOIN::exec (this=0x7f1c000a00f8) at /10.3/sql/sql_select.cc:3901
 
#25 0x000055e4b37f0993 in mysql_select (thd=0x7f1c00000d50, tables=0x7f1c0009df38, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f1c0009de50, unit=0x7f1c00004c18, select_lex=0x7f1c000053a0) at /10.3/sql/sql_select.cc:4306
 
#26 0x000055e4b37e1f7a in handle_select (thd=0x7f1c00000d50, lex=0x7f1c00004b58, result=0x7f1c0009de50, setup_tables_done_option=0) at /10.3/sql/sql_select.cc:382
 
#27 0x000055e4b37a96b1 in execute_sqlcom_select (thd=0x7f1c00000d50, all_tables=0x7f1c0009df38) at /10.3/sql/sql_parse.cc:6294
 
#28 0x000055e4b379fe3c in mysql_execute_command (thd=0x7f1c00000d50) at /10.3/sql/sql_parse.cc:3820
 
#29 0x000055e4b37ad9ea in mysql_parse (thd=0x7f1c00000d50, rawbuf=0x7f1c00012a78 "WITH RECURSIVE \ncte AS\n(SELECT  YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn FROM t1\nUNION ALL \nSELECT YEAR(cte.st + INTERVAL 1 MONTH), cte.st + INTERVAL 1 MONTH,  t1.d2 + INTERVAL"..., length=500, parser_state=0x7f1c109aa540, is_com_multi=false, is_next_command=false) at /10.3/sql/sql_parse.cc:7818
 
#30 0x000055e4b379a1cf in dispatch_command (command=COM_QUERY, thd=0x7f1c00000d50, packet=0x7f1c00008ed1 "", packet_length=500, is_com_multi=false, is_next_command=false) at /10.3/sql/sql_parse.cc:1856
 
#31 0x000055e4b3798aad in do_command (thd=0x7f1c00000d50) at /10.3/sql/sql_parse.cc:1402
 
#32 0x000055e4b39120a7 in do_handle_one_connection (connect=0x55e4b729d470) at /10.3/sql/sql_connect.cc:1403
 
#33 0x000055e4b3911de3 in handle_one_connection (arg=0x55e4b729d470) at /10.3/sql/sql_connect.cc:1308
 
#34 0x000055e4b42c9b24 in pfs_spawn_thread (arg=0x55e4b72b7e80) at /10.3/storage/perfschema/pfs.cc:1869
 
#35 0x00007f1c17e166db in start_thread (arg=0x7f1c109ab700) at pthread_create.c:463
 
#36 0x00007f1c1720088f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

On 10.2 it is sporadic, I get the crash only on every second execution of the test, but on 10.3-10.5 test fails every time

Comment by Igor Babaev [ 2020-05-31 ]

Analysis:
Set a break-point in mysql_derived_fill() at 

    unit->cleanup();

First stop:

p derived->alias
 
$35 = {str = 0x7fff9002e4a0 "cte", length = 3}

when processing 

gdb) p dbug_print_select(select_lex)
 
$36 = 0x5555577a9740 <dbug_item_print_buf> "select cte.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from cte join t3 where t3.tm between cte.st and cte.fn"

Step into st_select_lex_unit::cleanup()
Condition

 
   if (!thd->stmt_arena->is_stmt_prepare() && !thd->lex->describe &&
 
       with_element && with_element->is_recursive && union_result)

is met.
Condition

     if (++result->cleanup_count == with_element->rec_outer_references)

is not met (1 != 2). So exit the function without cleanup of the unit.

Next stop in mysql_derived_fill() for

p derived->alias
 
$37 = {str = 0x7fff90030220 "cte3", length = 4}

Step into st_select_lex_unit::cleanup()
Condition

   if (!thd->stmt_arena->is_stmt_prepare() && !thd->lex->describe &&
 
       with_element && with_element->is_recursive && union_result)

is not met and we come to the code

   for (SELECT_LEX *sl= first_select(); sl; sl= sl->next_select())
 
      error|= sl->cleanup();

Step into cleanup() for the first_select of this unit (spec for cte3).
Now in st_select_lex::cleanup()
Come to the code

for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ;
 
       lex_unit= lex_unit->next_unit())
 
   {
 
      error= (bool) ((uint) error | (uint) lex_unit->cleanup());
 
   }

Step into first_inner_unit().
This is the unit for a with element and the element is cte:

p with_element
 
$41 = (With_element *) 0x7fff90018600
 
p *with_element->query_name
 
$43 = {str = 0x7fff90013f58 "cte", length = 3}

Condition

  if (!thd->stmt_arena->is_stmt_prepare() && !thd->lex->describe &&
 
       with_element && with_element->is_recursive && union_result)

is met.
Condition

    if (++result->cleanup_count == with_element->rec_outer_references)

is also met (2 == 2) and we come to the code

  if (with_element && with_element->is_recursive)
 
  {
 
     if (union_result)
 
     {
 
       ((select_union_recursive *) union_result)->cleanup();
 
       delete union_result;
 
       union_result= 0;
 
     }
 
    with_element->mark_as_cleaned();
 
  }

that deletes (select_union_recursive *) union_result together with rec_tables.
Now when we come to materialization of cte2 we call TABLE_LIST::fill_recursive() for table reference cte occurred in the spec of cte2. TABLE_LIST::fill_recursive(), in its turn calls, With_element::instantiate_tmp_tables() for the with element cte. The latter causes a crash because With_element->rec_result->rec_tables are destroyed.

Comment by Igor Babaev [ 2020-05-31 ]

Observation
The following query in which specification of cte2 follows specification of cte3 returns a proper result set:

WITH RECURSIVE 
cte AS
 
  (SELECT  YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn FROM t1
 
   UNION ALL 
   SELECT YEAR(cte.st + INTERVAL 1 MONTH), cte.st + INTERVAL 1 MONTH,  t1.d2 + INTERVAL 1 DAY
 
   FROM cte JOIN t1
 
   WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),             
cte3 AS (SELECT YEAR, COUNT(*) 
        FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn),
 
cte2 AS (SELECT YEAR, COUNT(*) 
        FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn)
 
SELECT 1 FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR);


MariaDB [test]> WITH RECURSIVE 
    -> cte AS
 
    ->   (SELECT  YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn FROM t1
 
    ->    UNION ALL 
    ->    SELECT YEAR(cte.st + INTERVAL 1 MONTH), cte.st + INTERVAL 1 MONTH,  t1.d2 + INTERVAL 1 DAY
 
    ->    FROM cte JOIN t1
 
    ->    WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),             
    -> cte3 AS (SELECT YEAR, COUNT(*) 
    ->         FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn),
 
    -> cte2 AS (SELECT YEAR, COUNT(*) 
    ->         FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn)
 
    -> SELECT 1 FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR);
 
+---+
 
| 1 |
 
+---+
 
| 1 |
 
+---+

Why?
The analysis for this query shows that the specification of cte is attached now to the specification of cte2 (not to the sp ecification for cte3 as for the first query). However cte2 and cte3 are populated in the same order: first cte3, then cte2. So the code

for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ;
 
       lex_unit= lex_unit->next_unit())
 
{
 
   error= (bool) ((uint) error | (uint) lex_unit->cleanup());
 
}

has no effect when after cte3 has been filled.

Comment by Igor Babaev [ 2020-05-31 ]

I was lucky (or rather unlucky) with the test case query pushed for MDEV-17024:

create table t1 (id int);
 
insert into t1 values (1), (2), (3);
 
 
 
with recursive
 
rcte(a) as
 
(select 1 union select cast(a+1 as unsigned) from rcte where a < 10),
 
cte1 as
 
(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3),
 
cte2 as
 
(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
 
select * from cte1, cte2;

If slightly change the query for:

with recursive
 
rcte(a) as
 
(select 1 union select cast(a+1 as unsigned) from rcte where a < 10),
 
cte1 as
 
(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3),
 
cte2 as
 
(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
 
select * from cte2, cte1;

we have a crash

Thread 34 "mysqld" received signal SIGSEGV, Segmentation fault.
 
0x0000555555c7b131 in base_list_iterator::next_fast (this=0x7fffe05364d0) at /home/igor/maria-git/10.4/sql/sql_list.h:443

Comment by Igor Babaev [ 2020-05-31 ]

Question:
Why to attach the specification of cte to a select where it is referred to? It does not make sense as we have several non-recursive references to cte.
Here's the code from TABLE_LIST::set_as_with_table() that does it

  if (!with_elem->is_referenced() || with_elem->is_recursive)
 
  {
 
    derived= with_elem->spec;
 
    if (derived != select_lex->master_unit() &&
 
        !is_with_table_recursive_reference())
 
    {
 
       derived->move_as_slave(select_lex);
 
    }

Interesting that the spec of cte first is attached to the spec of cte2, then to the spec of cte3 (for the reporting query)
Let's do not move the spec for a recursive CTE:

@@ bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem)
 
   {
 
     derived= with_elem->spec;
 
     if (derived != select_lex->master_unit() &&
 
+        !with_elem->is_recursive &&
 
         !is_with_table_recursive_reference())
 
     {
 
        derived->move_as_slave(select_lex);

Comment by Igor Babaev [ 2020-05-31 ]

The above change resolves the problem of premature cleanup for the spec of a recursive CTE. Moreover with this patch we could do without the changes in the code added for MDEV-17024. However if we want to perform the cleanup of the spec of a recursive CTE when cleaning up the select with the last reference to this CTE it's better to preserve this code and slightly correct it.

Comment by Oleksandr Byelkin [ 2020-06-05 ]

OK to push

Generated at Thu Feb 08 09:17:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.