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

SHOW EXPLAIN: server crashes in JOIN::print_explain with IN subquery and aggregate function



    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.0.0
    • Component/s: None
    • Labels:


      #4  <signal handler called>
      #5  0x08383dcb in JOIN::print_explain (this=0xa0f0258, result=0x9ff0560, 
          explain_flags=0 '\000', on_the_fly=true, need_tmp_table=false, need_order=false, 
          distinct=false, message=0x0) at sql_select.cc:21264
      #6  0x081e8fa7 in st_select_lex::print_explain (this=0x9fd4818, output=0x9ff0560, 
          explain_flags=0 '\000') at sql_lex.cc:3773
      #7  0x081e9109 in st_select_lex_unit::print_explain (this=0x9fd4524, output=0x9ff0560, 
          explain_flags=0 '\000') at sql_lex.cc:3824
      #8  0x082bd29f in Show_explain_request::get_explain_data (arg=0x943d1694)
          at sql_class.cc:3042
      #9  0x0850ae24 in Apc_target::process_apc_requests (this=0x9fd433c) at my_apc.cc:255
      #10 0x0850a875 in Apc_target::disable (this=0x9fd433c) at my_apc.cc:83
      #11 0x08357d43 in JOIN::exec (this=0xa0f0258) at sql_select.cc:2134
      #12 0x0835ac4f in mysql_select (thd=0x9fd2d30, rref_pointer_array=0x9fd4958, 
          tables=0xa0ef240, wild_num=1, fields=..., conds=0xa0f00a0, og_num=0, order=0x0, 
          group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xa0f0240, 
          unit=0x9fd4524, select_lex=0x9fd4818) at sql_select.cc:3031
      #13 0x0835277f in handle_select (thd=0x9fd2d30, lex=0x9fd44c8, result=0xa0f0240, 
          setup_tables_done_option=0) at sql_select.cc:332
      #14 0x082ed586 in execute_sqlcom_select (thd=0x9fd2d30, all_tables=0xa0ef240)
          at sql_parse.cc:5178
      #15 0x082e480b in mysql_execute_command (thd=0x9fd2d30) at sql_parse.cc:2285
      #16 0x082efbc1 in mysql_parse (thd=0x9fd2d30, 
          rawbuf=0xa0ef078 "SELECT * FROM A WHERE ( 5, 78 ) IN ( SELECT `a1`, MAX(`a1`) FROM A GROUP BY `a1` )", length=82, found_semicolon=0x9438a234) at sql_parse.cc:6179
      #17 0x082e244b in dispatch_command (command=COM_QUERY, thd=0x9fd2d30, packet=0xa0e9709 "", 
          packet_length=82) at sql_parse.cc:1229
      #18 0x082e18f5 in do_command (thd=0x9fd2d30) at sql_parse.cc:924
      #19 0x082de7e1 in handle_one_connection (arg=0x9fd2d30) at sql_connect.cc:1218
      #20 0xb774bb25 in start_thread () from /lib/libpthread.so.0

      bzr version-info

      revision-id: psergey@askmonty.org-20120516162254-kxt6xh902iqb6rsc
      date: 2012-05-16 20:22:54 +0400
      revno: 3513

      The provided RQG test repeatedly runs the same query in one thread, and SHOW EXPLAIN for the first thread in another thread. It always fails within seconds after start when I run it.
      Before server crashes, EXPLAIN result comes in two variants, I am not sure which one causes the problem, or maybe there is a third which does not reach the client:

      1 PRIMARY <subquery2> const distinct_key distinct_key 8  1
      1 PRIMARY A ALL     20 Using join buffer (flat, BNL join)
      2 MATERIALIZED A index  a1 4  20 Using index
      1 PRIMARY <subquery2> const distinct_key distinct_key 8 const,const 1
      1 PRIMARY A ALL     20 Using join buffer (flat, BNL join)
      2 MATERIALIZED A index  a1 4  20 Using index

      Run RQG test as

      perl runall.pl \
      --threads=2 \
      --duration=600 \
      --queries=100M \
      --skip-gendata \
      --grammar=test.yy \
      --basedir=<your basedir>

      RQG grammar (test.yy):

        DROP TABLE IF EXISTS A; CREATE TABLE A ( `pk` INT NOT NULL PRIMARY KEY, `a1` INT NOT NULL, KEY(`a1`) ) ENGINE=MyISAM ; INSERT INTO A VALUES (1,5),(2,4),(3,6),(4,9),(5,2),(6,8),(7,4),(8,8),(9,0),(10,43),(11,23),(12,3),(13,45),(14,16),(15,2),(16,33),(17,2),(18,5),(19,9),(20,2);
        SELECT * FROM A WHERE ( 5, 78 ) IN ( SELECT `a1`, MAX(`a1`) FROM A GROUP BY `a1` ) ;
        SET @con_id = CONNECTION_ID()-1;
        SHOW EXPLAIN FOR @con_id ;


          Issue Links



              psergei Sergei Petrunia
              elenst Elena Stepanova
              0 Vote for this issue
              1 Start watching this issue



                  Git Integration

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