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

SHOW EXPLAIN: server crashes in JOIN::print_explain on a query with select tables optimized away

    XMLWordPrintable

    Details

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

      Description

      #4  0x00000000007a85b6 in JOIN::print_explain (this=0x1d40278, result=0x1cb3ab8, 
          explain_flags=0 '\000', on_the_fly=true, need_tmp_table=false, need_order=false, 
          distinct=false, message=0x0) at sql_select.cc:21247
      #5  0x00000000005d6106 in st_select_lex::print_explain (this=0x1cf24b0, output=0x1cb3ab8, 
          explain_flags=0 '\000') at sql_lex.cc:3769
      #6  0x00000000005d6279 in st_select_lex_unit::print_explain (this=0x1d3baf0, output=0x1cb3ab8, 
          explain_flags=0 '\000') at sql_lex.cc:3820
      #7  0x00000000005d6172 in st_select_lex::print_explain (this=0x1cf14a0, output=0x1cb3ab8, 
          explain_flags=0 '\000') at sql_lex.cc:3784
      #8  0x00000000005d6279 in st_select_lex_unit::print_explain (this=0x1cf17f0, output=0x1cb3ab8, 
          explain_flags=0 '\000') at sql_lex.cc:3820
      #9  0x00000000005d6172 in st_select_lex::print_explain (this=0x1ce4c68, output=0x1cb3ab8, 
          explain_flags=0 '\000') at sql_lex.cc:3784
      #10 0x00000000005d6279 in st_select_lex_unit::print_explain (this=0x1ce4760, output=0x1cb3ab8, 
          explain_flags=0 '\000') at sql_lex.cc:3820
      #11 0x00000000006ccbb1 in Show_explain_request::get_explain_data (arg=0x7f093ae47ba0)
          at sql_class.cc:3042
      #12 0x000000000095b261 in Apc_target::process_apc_requests (this=0x1ce44d0) at my_apc.cc:255
      #13 0x00000000006c8a1c in THD::check_killed (this=0x1ce21b8) at sql_class.cc:1904
      #14 0x000000000079a59f in evaluate_join_record (join=0x1d3d488, join_tab=0x1d42f58, error=0)
          at sql_select.cc:15829
      #15 0x000000000079a204 in sub_select (join=0x1d3d488, join_tab=0x1d42f58, end_of_records=false)
          at sql_select.cc:15744
      #16 0x00000000007999d5 in do_select (join=0x1d3d488, fields=0x1d3d818, table=0x0, procedure=0x0)
          at sql_select.cc:15405
      #17 0x000000000077aa3a in JOIN::exec_inner (this=0x1d3d488) at sql_select.cc:2810
      #18 0x0000000000777e1d in JOIN::exec (this=0x1d3d488) at sql_select.cc:2125
      #19 0x0000000000685dbb in subselect_single_select_engine::exec (this=0x1d3c900)
          at item_subselect.cc:3006
      #20 0x000000000067eae9 in Item_subselect::exec (this=0x1d3c7e8) at item_subselect.cc:587
      #21 0x0000000000680143 in Item_singlerow_subselect::val_int (this=0x1d3c7e8)
          at item_subselect.cc:1101
      #22 0x000000000063a6a6 in Arg_comparator::compare_int_signed (this=0x1d3ca00)
          at item_cmpfunc.cc:1167
      #23 0x000000000061b590 in Arg_comparator::compare (this=0x1d3ca00) at item_cmpfunc.h:72
      #24 0x000000000063c5a6 in Item_func_eq::val_int (this=0x1d3c940) at item_cmpfunc.cc:1851
      #25 0x000000000079a63e in evaluate_join_record (join=0x1d3cb78, join_tab=0x1d415a8, error=0)
          at sql_select.cc:15840
      #26 0x000000000079a204 in sub_select (join=0x1d3cb78, join_tab=0x1d415a8, end_of_records=false)
          at sql_select.cc:15744
      #27 0x00000000007999d5 in do_select (join=0x1d3cb78, fields=0x1ce4d70, table=0x0, procedure=0x0)
          at sql_select.cc:15405
      #28 0x000000000077aa3a in JOIN::exec_inner (this=0x1d3cb78) at sql_select.cc:2810
      #29 0x0000000000777e1d in JOIN::exec (this=0x1d3cb78) at sql_select.cc:2125
      #30 0x000000000077b2d3 in mysql_select (thd=0x1ce21b8, rref_pointer_array=0x1ce4ec0, 
          tables=0x1cf0f88, wild_num=1, fields=..., conds=0x1d3c940, og_num=0, order=0x0, group=0x0, 
          having=0x0, proc_param=0x0, select_options=2147764736, result=0x1d3cb58, unit=0x1ce4760, 
          select_lex=0x1ce4c68) at sql_select.cc:3031
      #31 0x0000000000771d4e in handle_select (thd=0x1ce21b8, lex=0x1ce46c0, result=0x1d3cb58, 
          setup_tables_done_option=0) at sql_select.cc:332
      #32 0x00000000007002c5 in execute_sqlcom_select (thd=0x1ce21b8, all_tables=0x1cf0f88)
          at sql_parse.cc:5178
      #33 0x00000000006f720b in mysql_execute_command (thd=0x1ce21b8) at sql_parse.cc:2285
      #34 0x0000000000702ca0 in mysql_parse (thd=0x1ce21b8, 
          rawbuf=0x1cf0d00 "SELECT * FROM C WHERE a = ( SELECT MAX(a) FROM C\nWHERE pk = ( SELECT MAX(pk) FROM C WHERE pk = 3 ) )", length=100, found_semicolon=0x7f093ae00c98) at sql_parse.cc:6179
      #35 0x00000000006f49ba in dispatch_command (command=COM_QUERY, thd=0x1ce21b8, packet=0x1ce6359 "", 
          packet_length=101) at sql_parse.cc:1229
      #36 0x00000000006f3cee in do_command (thd=0x1ce21b8) at sql_parse.cc:924
      #37 0x00000000006f0b32 in handle_one_connection (arg=0x1ce21b8) at sql_connect.cc:1218

      bzr version-info

      revision-id: psergey@askmonty.org-20120514183900-rvddjradoeiynwif
      date: 2012-05-14 22:39:00 +0400
      revno: 3510

      Normal EXPLAIN from the before executing the query:

      id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered
              Extra
      1       PRIMARY C       ALL     NULL    NULL    NULL    NULL    32768   100.00  Using where
      2       SUBQUERY        C       const   PRIMARY PRIMARY 4       const   1       100.00  Using where
      3       SUBQUERY        NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables
       optimized away
      Warnings:
      Note    1003    select `test`.`C`.`pk` AS `pk`,`test`.`C`.`a` AS `a` from `test`.`C` where (`test`.`C`.`a` = (select max(`test`.`C`.`a`) from `test`.`C` where (`test`.`C`.`pk` = (select max(`test`.`C`.`pk`) from `test`.`C` where multiple equal(3, `test`.`C`.`pk`)))))

      Reproducible with the default optimizer_switch as well as with all OFFs except for in_to_exists which is required to execute the query.

      MTR test case:

       
      CREATE TABLE C (pk INT PRIMARY KEY AUTO_INCREMENT, a INT ) ENGINE=MyISAM ;
      INSERT INTO C (a) VALUES
        (4),(6),(7),(1),(0),(7),(7),(1),
        (5),(2),(0),(1),(8),(1),(1),(9);
      INSERT INTO C SELECT NULL, a FROM C;
      INSERT INTO C SELECT NULL, a FROM C;
      INSERT INTO C SELECT NULL, a FROM C;
      INSERT INTO C SELECT NULL, a FROM C;
      INSERT INTO C SELECT NULL, a FROM C;
      INSERT INTO C SELECT NULL, a FROM C;
      INSERT INTO C SELECT NULL, a FROM C;
      INSERT INTO C SELECT NULL, a FROM C;
      INSERT INTO C SELECT NULL, a FROM C;
      INSERT INTO C SELECT NULL, a FROM C;
      INSERT INTO C SELECT NULL, a FROM C;
       
      --connect (con1,localhost,root,,)
      --let $con_id = `SELECT CONNECTION_ID()`
      --send
      SELECT * FROM C WHERE a = ( SELECT MAX(a) FROM C
           WHERE pk = ( SELECT MAX(pk) FROM C WHERE pk = 3 ) ) ;
       
      --connection default
      --let $run = 100
       
      while ($run)
      {
        --error 0,1220
        --eval SHOW EXPLAIN FOR $con_id
        --dec $run
      }
       
      --connection con1
      --reap

      In case you cannot reproduce it with the MTR case, you might want to try RQG.
      RQG grammar (test.yy):

      query_init:
        CREATE TABLE C (`pk` INT PRIMARY KEY, `a` INT ) ENGINE=MyISAM ; INSERT INTO C
        VALUES (1,4),(2,62),(3,7),(4,1),(5,0),(6,7),(7,7),(8,1),(9,7),(10,1),
        (11,5),(12,2),(13,0),(14,1),(15,8),(16,1),(17,1),(18,9),(19,1),(20,5) ;
       
      thread2:
          SHOW EXPLAIN FOR @con_id ;
       
      thread2_init:
          SET @con_id = CONNECTION_ID()-1;
       
      query:
           SELECT * FROM C WHERE `a` = ( SELECT MAX(`a`) FROM C
           WHERE `pk` = ( SELECT MAX(`pk`) FROM C WHERE `pk` = 3 ) ) ;

      Run as:

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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.