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

SHOW EXPLAIN: Assertion `this->optimized == 2' failed in JOIN::print_explain on query with a JOIN, TEMPTABLE view, single-row base table

    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

      mysqld: sql_select.cc:21160: int JOIN::print_explain(select_result_sink*, bool, bool, bool, bool, const char*): Assertion `this->optimized == 2' failed.
       
      #6  0x00007f9ae9559235 in __assert_fail () from /lib64/libc.so.6
      #7  0x00000000007a7fa7 in JOIN::print_explain (this=0x7f9acd09f168, result=0x24ba358, 
          on_the_fly=true, need_tmp_table=false, need_order=false, distinct=false, 
          message=0xcfc4cd "Not yet optimized") at sql_select.cc:21160
      #8  0x00000000005d60a5 in st_select_lex::print_explain (this=0x7f9acd08ede8, 
          output=0x24ba358) at sql_lex.cc:3784
      #9  0x00000000005d613e in st_select_lex_unit::print_explain (this=0x7f9acd08e8e0, 
          output=0x24ba358) at sql_lex.cc:3807
      #10 0x00000000006cca6c in Show_explain_request::get_explain_data (arg=0x7f9abe1beba0)
          at sql_class.cc:3042
      #11 0x000000000095b035 in Apc_target::process_apc_requests (this=0x7f9acd08e650)
          at my_apc.cc:255
      #12 0x000000000095a9c6 in Apc_target::disable (this=0x7f9acd08e650) at my_apc.cc:83
      #13 0x0000000000777cec in JOIN::exec (this=0x7f9acd09f758) at sql_select.cc:2126
      #14 0x000000000077b125 in mysql_select (thd=0x7f9acd08c338, 
          rref_pointer_array=0x23d6878, tables=0x7f9acd09ed70, wild_num=0, fields=..., conds=
          0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, 
          select_options=2416200192, result=0x7f9acd09f660, unit=0x23d6118, 
          select_lex=0x23d6620) at sql_select.cc:3023
      #15 0x00000000008fcd0a in mysql_derived_fill (thd=0x7f9acd08c338, lex=0x7f9acd08e840, 
          derived=0x23d56a8) at sql_derived.cc:889
      #16 0x00000000008fc952 in mysql_derived_optimize (thd=0x7f9acd08c338, 
          lex=0x7f9acd08e840, derived=0x23d56a8) at sql_derived.cc:778
      #17 0x00000000008fb90b in mysql_handle_single_derived (lex=0x7f9acd08e840, 
          derived=0x23d56a8, phases=4) at sql_derived.cc:176
      #18 0x000000000077022d in TABLE_LIST::handle_derived (this=0x23d56a8, 
          lex=0x7f9acd08e840, phases=4) at table.cc:5846
      #19 0x00000000005d4b46 in st_select_lex::handle_derived (this=0x7f9acd08ede8, 
          lex=0x7f9acd08e840, phases=4) at sql_lex.cc:3171
      #20 0x00000000007738cc in JOIN::optimize_inner (this=0x7f9acd09f168) at sql_select.cc:971
      #21 0x0000000000773796 in JOIN::optimize (this=0x7f9acd09f168) at sql_select.cc:938
      #22 0x000000000077b08f in mysql_select (thd=0x7f9acd08c338, 
          rref_pointer_array=0x7f9acd08f040, tables=0x23d56a8, wild_num=1, fields=..., 
          conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, 
          select_options=2147764736, result=0x23d7428, unit=0x7f9acd08e8e0, 
          select_lex=0x7f9acd08ede8) at sql_select.cc:3009
      #23 0x0000000000771c0a in handle_select (thd=0x7f9acd08c338, lex=0x7f9acd08e840, 
          result=0x23d7428, setup_tables_done_option=0) at sql_select.cc:332
      #24 0x0000000000700181 in execute_sqlcom_select (thd=0x7f9acd08c338, 
          all_tables=0x23d56a8) at sql_parse.cc:5178
      ---Type <return> to continue, or q <return> to quit---
      #25 0x00000000006f70c7 in mysql_execute_command (thd=0x7f9acd08c338) at sql_parse.cc:2285
      #26 0x0000000000702b5c in mysql_parse (thd=0x7f9acd08c338, 
          rawbuf=0x23d54c0 "SELECT * FROM view_A, B", length=23, 
          found_semicolon=0x7f9abe207c98) at sql_parse.cc:6179
      #27 0x00000000006f4876 in dispatch_command (command=COM_QUERY, thd=0x7f9acd08c338, 
          packet=0x7f9acd0903c9 "SELECT * FROM view_A, B", packet_length=23)
          at sql_parse.cc:1229
      #28 0x00000000006f3baa in do_command (thd=0x7f9acd08c338) at sql_parse.cc:924
      #29 0x00000000006f09ee in handle_one_connection (arg=0x7f9acd08c338)
          at sql_connect.cc:1218
      #30 0x00007f9aea214a4f in start_thread () from /lib64/libpthread.so.0

      bzr version-info

      revision-id: psergey@askmonty.org-20120426031831-0iujnre01v3tlwkf
      date: 2012-04-26 08:48:31 +0530
      build-date: 2012-05-01 22:20:22 +0300
      revno: 3504

      optimizer_switch (default):

      index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

      Grammar to reproduce (3.yy):

      thread1_init:
        CREATE TABLE A (a INT) ENGINE=MyISAM; CREATE ALGORITHM=TEMPTABLE VIEW view_A AS SELECT * FROM A; INSERT INTO A VALUES (8); CREATE TABLE B (b INT) ENGINE=MyISAM; INSERT INTO B VALUES (4),(5),(6),(7),(8),(9);
       
      thread2_init:
        SET @con_id = CONNECTION_ID()-1;
       
      query:
        SELECT * FROM view_A, B;
       
      thread2:
        SHOW EXPLAIN FOR @con_id;

      Run as:

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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              elenst Elena Stepanova
              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.