[MDEV-22993] Crash on EXPLAIN with PUSHED DOWN SELECT and subquery Created: 2020-06-23  Updated: 2020-07-02  Resolved: 2020-06-24

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4, 10.5
Fix Version/s: 10.4.14, 10.5.5

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
Relates

 Description   

This is originally coming from CLX-251.

A testcase using FederatedX engine:
Apply the following diff:

diff --git a/mysql-test/suite/federated/federatedx_create_handlers.test b/mysql-test/suite/federated/federatedx_create_handlers.test
 
index 42a03e60d67..8f602886f09 100644
 
--- a/mysql-test/suite/federated/federatedx_create_handlers.test
 
+++ b/mysql-test/suite/federated/federatedx_create_handlers.test
 
@@ -167,6 +167,19 @@ select name into outfile 'tmp.txt' from federated.t1;
 
 let $path=`select concat(@@datadir, 'test/tmp.txt')`;
 
 remove_file $path;
 
 
+--echo #
 
+--echo # CLX-251: EXPLAIN with subselect crashes MariaDB server
 
+--echo #
 
+select sin(0);
 
+
 
+explain
 
+select * from federated.t1
 
+where name in (select name from federated.t2);
 
+
 
+explain format=json
 
+select * from federated.t1
 
+where name in (select name from federated.t2);
 
+
 
 DROP TABLE federated.t1, federated.t2, federated.t3, federated.t4;
 
 
 connection slave;

Run ./mtr federated.federatedx_create_handlers and it will crash like so:

Thread 1 (Thread 0x7f475e44b700 (LWP 32449)):
 
#0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:57
 
#1  0x000055da6c0301ab in my_write_core (sig=11) at /home/psergey/dev-git/10.4-cl3/mysys/stacktrace.c:481
 
#2  0x000055da6b792887 in handle_fatal_signal (sig=11) at /home/psergey/dev-git/10.4-cl3/sql/signal_handler.cc:343
 
#3  <signal handler called>
 
#4  0x000055da6b3adf88 in With_sum_func_cache::With_sum_func_cache (this=0x7f4740017362, a=0xa5a5a5a5a5a5a5a5, b=0x7f47400133f8) at /home/psergey/dev-git/10.4-cl3/sql/item.h:2440
 
#5  0x000055da6b3af99d in Item_func::Item_func (this=0x7f47400172b0, thd=0x7f4740000d50, a=0xa5a5a5a5a5a5a5a5, b=0x7f47400133f8) at /home/psergey/dev-git/10.4-cl3/sql/item_func.h:111
 
#6  0x000055da6b3b00b0 in Item_int_func::Item_int_func (this=0x7f47400172b0, thd=0x7f4740000d50, a=0xa5a5a5a5a5a5a5a5, b=0x7f47400133f8) at /home/psergey/dev-git/10.4-cl3/sql/item_func.h:997
 
#7  0x000055da6b3b03ea in Item_bool_func::Item_bool_func (this=0x7f47400172b0, thd=0x7f4740000d50, a=0xa5a5a5a5a5a5a5a5, b=0x7f47400133f8) at /home/psergey/dev-git/10.4-cl3/sql/item_cmpfunc.h:222
 
#8  0x000055da6b3b0572 in Item_bool_func2::Item_bool_func2 (this=0x7f47400172b0, thd=0x7f4740000d50, a=0xa5a5a5a5a5a5a5a5, b=0x7f47400133f8) at /home/psergey/dev-git/10.4-cl3/sql/item_cmpfunc.h:412
 
#9  0x000055da6b3b07ce in Item_bool_func2_with_rev::Item_bool_func2_with_rev (this=0x7f47400172b0, thd=0x7f4740000d50, a=0xa5a5a5a5a5a5a5a5, b=0x7f47400133f8) at /home/psergey/dev-git/10.4-cl3/sql/item_cmpfunc.h:474
 
#10 0x000055da6b3b0a3d in Item_bool_rowready_func2::Item_bool_rowready_func2 (this=0x7f47400172b0, thd=0x7f4740000d50, a=0xa5a5a5a5a5a5a5a5, b=0x7f47400133f8) at /home/psergey/dev-git/10.4-cl3/sql/item_cmpfunc.h:516
 
#11 0x000055da6b3b0aea in Item_func_eq::Item_func_eq (this=0x7f47400172b0, thd=0x7f4740000d50, a=0xa5a5a5a5a5a5a5a5, b=0x7f47400133f8) at /home/psergey/dev-git/10.4-cl3/sql/item_cmpfunc.h:708
 
#12 0x000055da6b7f3eb1 in Eq_creator::create (this=0x55da6cdada80 <eq_creator>, thd=0x7f4740000d50, a=0xa5a5a5a5a5a5a5a5, b=0x7f47400133f8) at /home/psergey/dev-git/10.4-cl3/sql/item_cmpfunc.cc:7315
 
#13 0x000055da6b87a290 in Item_in_subselect::create_single_in_to_exists_cond (this=0x7f4740014438, join=0x7f4740015f40, where_item=0x7f4740016440, having_item=0x7f4740016448) at /home/psergey/dev-git/10.4-cl3/sql/item_subselect.cc:2206
 
#14 0x000055da6b87c822 in Item_in_subselect::create_in_to_exists_cond (this=0x7f4740014438, join_arg=0x7f4740015f40) at /home/psergey/dev-git/10.4-cl3/sql/item_subselect.cc:2622
 
#15 0x000055da6b62dc2d in JOIN::choose_subquery_plan (this=0x7f4740015f40, join_tables=1) at /home/psergey/dev-git/10.4-cl3/sql/opt_subselect.cc:6392
 
#16 0x000055da6b4956d0 in make_join_statistics (join=0x7f4740015f40, tables_list=..., keyuse_array=0x7f4740016230) at /home/psergey/dev-git/10.4-cl3/sql/sql_select.cc:5601
 
#17 0x000055da6b489275 in JOIN::optimize_inner (this=0x7f4740015f40) at /home/psergey/dev-git/10.4-cl3/sql/sql_select.cc:2260
 
#18 0x000055da6b486bba in JOIN::optimize (this=0x7f4740015f40) at /home/psergey/dev-git/10.4-cl3/sql/sql_select.cc:1607
 
#19 0x000055da6b492062 in mysql_select (thd=0x7f4740000d50, tables=0x7f4740013560, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7f4740014fa0, unit=0x7f4740013c40, select_lex=0x7f4740012f88) at /home/psergey/dev-git/10.4-cl3/sql/sql_select.cc:4670
 
#20 0x000055da6b4cf5e6 in mysql_explain_union (thd=0x7f4740000d50, unit=0x7f4740013c40, result=0x7f4740014fa0) at /home/psergey/dev-git/10.4-cl3/sql/sql_select.cc:26966
 
#21 0x000055da6b4cf2e0 in select_describe (join=0x7f4740015618, need_tmp_table=false, need_order=false, distinct=false, message=0x0) at /home/psergey/dev-git/10.4-cl3/sql/sql_select.cc:26926
 
#22 0x000055da6b49168c in JOIN::exec_inner (this=0x7f4740015618) at /home/psergey/dev-git/10.4-cl3/sql/sql_select.cc:4432
 
#23 0x000055da6b4909e4 in JOIN::exec (this=0x7f4740015618) at /home/psergey/dev-git/10.4-cl3/sql/sql_select.cc:4252
 
#24 0x000055da6b4920fb in mysql_select (thd=0x7f4740000d50, tables=0x7f47400127a0, wild_num=1, fields=..., conds=0x7f4740014438, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7f4740014fa0, unit=0x7f4740004c78, select_lex=0x7f47400121d0) at /home/psergey/dev-git/10.4-cl3/sql/sql_select.cc:4684



 Comments   
Comment by Sergei Petrunia [ 2020-06-23 ]

The cause of this crash is: select_describe() should not attempt to produce a query plan if the whole select has been pushed to the storage engine.

After fixing this one, one gets this:

Thread 1 (Thread 0x7f8a87581700 (LWP 2269)):
 
#0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:57
 
#1  0x000055b7277dd1cf in my_write_core (sig=11) at /home/psergey/dev-git/10.4-cl3/mysys/stacktrace.c:481
 
#2  0x000055b726f3f8ab in handle_fatal_signal (sig=11) at /home/psergey/dev-git/10.4-cl3/sql/signal_handler.cc:343
 
#3  <signal handler called>
 
#4  0x000055b726dae98a in Explain_node::print_explain_for_children (this=0x7f8a74016718, query=0x7f8a740165a8, output=0x7f8a74014fa0, explain_flags=1 '\001', is_analyze=false) at /home/psergey/dev-git/10.4-cl3/sql/sql_explain.cc:663
 
#5  0x000055b726daf24e in Explain_select::print_explain (this=0x7f8a74016718, query=0x7f8a740165a8, output=0x7f8a74014fa0, explain_flags=1 '\001', is_analyze=false) at /home/psergey/dev-git/10.4-cl3/sql/sql_explain.cc:855
 
#6  0x000055b726daca19 in Explain_query::print_explain (this=0x7f8a740165a8, output=0x7f8a74014fa0, explain_flags=1 '\001', is_analyze=false) at /home/psergey/dev-git/10.4-cl3/sql/sql_explain.cc:211
 
#7  0x000055b726bf532b in execute_sqlcom_select (thd=0x7f8a74000d50, all_tables=0x7f8a740127a0) at /home/psergey/dev-git/10.4-cl3/sql/sql_parse.cc:6315
 
#8  0x000055b726bebd22 in mysql_execute_command (thd=0x7f8a74000d50) at /home/psergey/dev-git/10.4-cl3/sql/sql_parse.cc:3898
 
#9  0x000055b726bf963f in mysql_parse (thd=0x7f8a74000d50, rawbuf=0x7f8a740120c8 "explain\nselect * from federated.t1\nwhere name in (select name from federated.t2)", length=80, parser_state=0x7f8a87580550, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.4-cl3/sql/sql_parse.cc:7901
 
#10 0x000055b726be5bd4 in dispatch_command (command=COM_QUERY, thd=0x7f8a74000d50, packet=0x7f8a74008751 "", packet_length=80, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.4-cl3/sql/sql_parse.cc:1841
 
#11 0x000055b726be4375 in do_command (thd=0x7f8a74000d50) at /home/psergey/dev-git/10.4-cl3/sql/sql_parse.cc:1359
 
#12 0x000055b726d6cbe4 in do_handle_one_connection (connect=0x55b7294eaf10) at /home/psergey/dev-git/10.4-cl3/sql/sql_connect.cc:1412
 
#13 0x000055b726d6c933 in handle_one_connection (arg=0x55b7294eaf10) at /home/psergey/dev-git/10.4-cl3/sql/sql_connect.cc:1316
 
#14 0x000055b72776a548 in pfs_spawn_thread (arg=0x55b72952bc30) at /home/psergey/dev-git/10.4-cl3/storage/perfschema/pfs.cc:1869
 
#15 0x00007f8a92cd86db in start_thread (arg=0x7f8a87581700) at pthread_create.c:463
 
#16 0x00007f8a9176688f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Comment by Sergei Petrunia [ 2020-06-23 ]

The cause of the second crash:

The crash happens because

  • Explain data structure for select #1 has a link to query plan for select #2.
  • But there is no query plan for select #2.
Comment by Sergei Petrunia [ 2020-06-23 ]

After fixing the above, one can see that Derived Handler is affected by a similar issue:

+create table t5 (a int) engine=myisam;
 
+insert into t5 values (1),(2);
 
+explain 
+select * from t5, 
+(select id from federated.t1
 
+where name in (select name from federated.t2) or name like 'foo%') as TQ;
 
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 
+1      PRIMARY t5      ALL     NULL    NULL    NULL    NULL    2
 
+1      PRIMARY <derived2>      ALL     NULL    NULL    NULL    NULL    5       Using join buffer (flat, BNL join)
 
+2      PUSHED DERIVED  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
 
+3      MATERIALIZED    t2      ALL     NULL    NULL    NULL    NULL    7

Here, there is no crash, but there is query plan printed for the select with id=3. This plan is not going to be executed (as select with id =2 has "PUSHED DERIVED").

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