Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22993

Crash on EXPLAIN with PUSHED DOWN SELECT and subquery

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.4(EOL), 10.5
    • 10.4.14, 10.5.5
    • Optimizer
    • None

    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

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          psergei Sergei Petrunia added a comment -

          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

          psergei Sergei Petrunia added a comment - 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
          psergei Sergei Petrunia added a comment -

          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.
          psergei Sergei Petrunia added a comment - 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.
          psergei Sergei Petrunia added a comment -

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

          psergei Sergei Petrunia added a comment - 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").

          People

            psergei Sergei Petrunia
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.