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

Assertion `cur_sj_inner_tables == 0' failed in JOIN::dbug_verify_sj_inner_tables with low optimizer_search_depth value

    XMLWordPrintable

    Details

      Description

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (a INT) ENGINE=InnoDB;
      CREATE TABLE t2 (b INT) ENGINE=InnoDB;
       
      # Data is optional, fails either way
      INSERT INTO t1 VALUES (1),(2);
      INSERT INTO t1 VALUES (3),(4);
       
      SET optimizer_search_depth= 1;
       
      SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 JOIN t1);
       
      # Cleanup
      DROP TABLE t1, t2;
      

      10.3 c4f65d8f

      mysqld: /data/src/10.3/sql/opt_subselect.cc:3432: void JOIN::dbug_verify_sj_inner_tables(uint) const: Assertion `cur_sj_inner_tables == 0' failed.
      220616 20:12:58 [ERROR] mysqld got signal 6 ;
       
      #4  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
              set = {__val = {552967, 139994771838662, 139998681464832, 139993185326976, 139993185327077, 139993185326976, 139993185326976, 139993185327123, 139993185327276, 139993185326976, 139993185327276, 0, 0, 0, 0, 0}}
              pid = <optimized out>
              tid = <optimized out>
              ret = <optimized out>
      #5  0x00007f53129fd537 in __GI_abort () at abort.c:79
              save_stage = 1
              act = {__sigaction_handler = {sa_handler = 0x7f52b40bb200, sa_sigaction = 0x7f52b40bb200}, sa_mask = {__val = {0, 0, 139993183944832, 0, 0, 0, 21474836480, 0, 139994664656656, 139994772970448, 139994772955432, 0, 11084539815827878656, 139994772938712, 139994782081024, 139994772955432}}, sa_flags = -971238304, sa_restorer = 0xd68}
              sigs = {__val = {32, 0 <repeats 15 times>}}
      #6  0x00007f53129fd40f in __assert_fail_base (fmt=0x7f5312b66128 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x564ec61c1b56 "cur_sj_inner_tables == 0", file=0x564ec61c1460 "/data/src/10.3/sql/opt_subselect.cc", line=3432, function=<optimized out>) at assert.c:92
              str = 0x7f52b40bb200 ""
              total = 4096
      #7  0x00007f5312a0c662 in __GI___assert_fail (assertion=0x564ec61c1b56 "cur_sj_inner_tables == 0", file=0x564ec61c1460 "/data/src/10.3/sql/opt_subselect.cc", line=3432, function=0x564ec61c1b70 "void JOIN::dbug_verify_sj_inner_tables(uint) const") at assert.c:101
      No locals.
      #8  0x0000564ec570c24d in JOIN::dbug_verify_sj_inner_tables (this=0x7f52b4015950, prefix_size=1) at /data/src/10.3/sql/opt_subselect.cc:3432
              cur_map = 0
              nests_entered = 0
              __PRETTY_FUNCTION__ = "void JOIN::dbug_verify_sj_inner_tables(uint) const"
      #9  0x0000564ec570a8a0 in update_sj_state (join=0x7f52b4015950, new_tab=0x7f52b40abfd8, idx=0, remaining_tables=7) at /data/src/10.3/sql/opt_subselect.cc:2907
      No locals.
      #10 0x0000564ec559418d in greedy_search (join=0x7f52b4015950, remaining_tables=7, search_depth=1, prune_level=1, use_cond_selectivity=1) at /data/src/10.3/sql/sql_select.cc:8213
              is_interleave_error = false
              pos = 0x3e66c541194d
              record_count = 1
              read_time = 0
              idx = 0
              best_idx = 1
              size_remain = 2
              best_pos = {table = 0x7f52b40abfd8, records_read = 1, cond_selectivity = 1, read_time = 1, prefix_cost = {io_count = 0, avg_io_cost = 1, cpu_cost = 0, import_cost = 0, mem_cost = 0}, prefix_record_count = -2.4983353906949635e-127, key = 0x0, ref_depend_map = 0, use_join_buffer = false, sj_strategy = SJ_OPT_NONE, n_sj_tables = 2779096485, dups_producing_tables = 11936128518282651045, inner_tables_handled_with_other_sjs = 11936128518282651045, dups_weedout_picker = {<Semi_join_strategy_picker> = {_vptr.Semi_join_strategy_picker = 0x564ec6875438 <vtable for Duplicate_weedout_picker+16>}, first_dupsweedout_table = 2779096485, dupsweedout_tables = 11936128518282651045, is_used = 165}, firstmatch_picker = {<Semi_join_strategy_picker> = {_vptr.Semi_join_strategy_picker = 0x564ec6875478 <vtable for Firstmatch_picker+16>}, first_firstmatch_table = 2779096485, first_firstmatch_rtbl = 11936128518282651045, firstmatch_need_tables = 11936128518282651045, is_used = 165}, loosescan_picker = {<Semi_join_strategy_picker> = {_vptr.Semi_join_strategy_picker = 0x564ec68754b8 <vtable for LooseScan_picker+16>}, first_loosescan_table = 2779096485, loosescan_need_tables = 11936128518282651045, loosescan_key = 64, loosescan_parts = 2779096485, is_used = 165}, sjmat_picker = {<Semi_join_strategy_picker> = {_vptr.Semi_join_strategy_picker = 0x564ec68754f8 <vtable for Sj_materialization_picker+16>}, is_used = 165, sjm_scan_last_inner = 2779096485, sjm_scan_need_tables = 11936128518282651045}, spl_plan = 0x0}
              best_table = 0x7f52b40abfd8
              n_tables = 2
              _db_stack_frame_ = {func = 0x564ec617ca0f "choose_plan", file = 0x564ec617b5c0 "/data/src/10.3/sql/sql_select.cc", level = 2147483659, line = -1, prev = 0x7f530c41e2e0}
              __PRETTY_FUNCTION__ = "bool greedy_search(JOIN*, table_map, uint, uint, uint)"
      #11 0x0000564ec559323e in choose_plan (join=0x7f52b4015950, join_tables=7) at /data/src/10.3/sql/sql_select.cc:7740
              search_depth = 1
              prune_level = 1
              use_cond_selectivity = 1
              straight_join = false
              _db_stack_frame_ = {func = 0x564ec61c1a8f "optimize_semijoin_nests", file = 0x564ec61c1460 "/data/src/10.3/sql/opt_subselect.cc", level = 2147483658, line = -1, prev = 0x7f530c41e3b0}
              jtab_sort_func = 0x564ec5593713 <join_tab_cmp_embedded_first(void const*, void const*, void const*)>
              __PRETTY_FUNCTION__ = "bool choose_plan(JOIN*, table_map)"
      #12 0x0000564ec57097db in optimize_semijoin_nests (join=0x7f52b4015950, all_table_map=7) at /data/src/10.3/sql/opt_subselect.cc:2372
              n_tables = 22094
              subjoin_read_time = 4.6885017004413169e-310
              sjm = 0x564ec6b683c0 <end_of_list>
              subjoin_out_rows = 4.6885017006209098e-310
              subq_select = 0x7f52b4017378
              rowlen = 0
              lookup_cost = 6.9165822885423159e-310
              write_cost = 6.9165822885423159e-310
              _db_stack_frame_ = {func = 0x564ec617c3f3 "make_join_statistics", file = 0x564ec617b5c0 "/data/src/10.3/sql/sql_select.cc", level = 2147483657, line = -1, prev = 0x7f530c41e5d0}
              sj_list_it = {<base_list_iterator> = {list = 0x7f52b40055a0, el = 0x7f52b4017378, prev = 0x7f52b40055a0, current = 0x7f52b4017378}, <No data fields>}
              sj_nest = 0x7f52b4016890
      #13 0x0000564ec558b90e in make_join_statistics (join=0x7f52b4015950, tables_list=@0x7f52b40055f0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7f52b4015eb8, last = 0x7f52b4016658, elements = 3}, <No data fields>}, keyuse_array=0x7f52b4015c40) at /data/src/10.3/sql/sql_select.cc:5191
              error = 0
              table = 0x7f52b40b6830
              i = 3
              table_count = 3
              const_count = 0
              key = 32595
              found_const_table_map = 0
              all_table_map = 7
              const_ref = {map = 139645291688536}
              eq_part = {map = 139993184033888}
              has_expensive_keyparts = 127
              table_vector = 0x7f52b40ac900
              stat = 0x7f52b40abc48
              stat_end = 0x7f52b40ac6f8
              s = 0x7f52b40ac6f8
              stat_ref = 0x7f52b40ac6f8
              stat_vector = 0x7f52b40ac8e0
              keyuse = 0x0
              start_keyuse = 0x7f5310d0f230
              outer_join = 0
              no_rows_const_tables = 0
              sargables = 0x7f52b4017870
              ti = {<base_list_iterator> = {list = 0x7f52b40055f0, el = 0x564ec6b683c0 <end_of_list>, prev = 0x7f52b4016658, current = 0x564ec6b683c0 <end_of_list>}, <No data fields>}
              tables = 0x0
              _db_stack_frame_ = {func = 0x564ec617ba63 "JOIN::optimize_inner", file = 0x564ec617b5c0 "/data/src/10.3/sql/sql_select.cc", level = 2147483656, line = -1, prev = 0x7f530c41e6a0}
              ref_changed = 0
              __PRETTY_FUNCTION__ = "bool make_join_statistics(JOIN*, List<TABLE_LIST>&, DYNAMIC_ARRAY*)"
      #14 0x0000564ec55801ad in JOIN::optimize_inner (this=0x7f52b4015950) at /data/src/10.3/sql/sql_select.cc:1977
              _db_stack_frame_ = {func = 0x564ec617c396 "mysql_select", file = 0x564ec617b5c0 "/data/src/10.3/sql/sql_select.cc", level = 2147483655, line = -1, prev = 0x7f530c41e7a0}
              sel = 0x7f52b40053d8
              ignore_on_expr = false
              __PRETTY_FUNCTION__ = "int JOIN::optimize_inner()"
      #15 0x0000564ec557e67c in JOIN::optimize (this=0x7f52b4015950) at /data/src/10.3/sql/sql_select.cc:1519
              res = 0
              init_state = JOIN::NOT_OPTIMIZED
      #16 0x0000564ec55887aa in mysql_select (thd=0x7f52b4000d90, tables=0x7f52b4012d08, wild_num=1, fields=@0x7f52b4005500: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7f52b4012cc0, last = 0x7f52b4012cc0, elements = 1}, <No data fields>}, conds=0x7f52b40155f8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f52b4015928, unit=0x7f52b4004c40, select_lex=0x7f52b40053d8) at /data/src/10.3/sql/sql_select.cc:4340
              err = 0
              free_join = true
              _db_stack_frame_ = {func = 0x564ec617b5fd "handle_select", file = 0x564ec617b5c0 "/data/src/10.3/sql/sql_select.cc", level = 2147483654, line = -1, prev = 0x7f530c41e860}
              join = 0x7f52b4015950
      #17 0x0000564ec5579db9 in handle_select (thd=0x7f52b4000d90, lex=0x7f52b4004b80, result=0x7f52b4015928, setup_tables_done_option=0) at /data/src/10.3/sql/sql_select.cc:372
              unit = 0x7f52b4004c40
              res = false
              select_lex = 0x7f52b40053d8
              _db_stack_frame_ = {func = 0x564ec61702c8 "mysql_execute_command", file = 0x564ec616f6a8 "/data/src/10.3/sql/sql_parse.cc", level = 2147483653, line = -1, prev = 0x7f530c41eed0}
      #18 0x0000564ec5540d9c in execute_sqlcom_select (thd=0x7f52b4000d90, all_tables=0x7f52b4012d08) at /data/src/10.3/sql/sql_parse.cc:6339
              save_protocol = 0x0
              lex = 0x7f52b4004b80
              result = 0x7f52b4015928
              res = false
              __PRETTY_FUNCTION__ = "bool execute_sqlcom_select(THD*, TABLE_LIST*)"
      #19 0x0000564ec55377a4 in mysql_execute_command (thd=0x7f52b4000d90) at /data/src/10.3/sql/sql_parse.cc:3870
              privileges_requested = 1
              res = 0
              up_result = 0
              lex = 0x7f52b4004b80
              select_lex = 0x7f52b40053d8
              first_table = 0x7f52b4012d08
              all_tables = 0x7f52b4012d08
              unit = 0x7f52b4004c40
              have_table_map_for_update = false
              rpl_filter = 0x388c60f2d9c
              _db_stack_frame_ = {func = 0x564ec61715a0 "mysql_parse", file = 0x564ec616f6a8 "/data/src/10.3/sql/sql_parse.cc", level = 2147483652, line = -1, prev = 0x7f530c41f400}
              __PRETTY_FUNCTION__ = "int mysql_execute_command(THD*)"
              orig_binlog_format = BINLOG_FORMAT_MIXED
              orig_current_stmt_binlog_format = BINLOG_FORMAT_STMT
      #20 0x0000564ec554509c in mysql_parse (thd=0x7f52b4000d90, rawbuf=0x7f52b4012ad8 "SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 JOIN t1)", length=54, parser_state=0x7f530c41f5b0, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:7870
              found_semicolon = 0x0
              error = 32594
              lex = 0x7f52b4004b80
              err = false
              _db_stack_frame_ = {func = 0x564ec616fb32 "dispatch_command", file = 0x564ec616f6a8 "/data/src/10.3/sql/sql_parse.cc", level = 2147483651, line = -1, prev = 0x7f530c41f590}
              __PRETTY_FUNCTION__ = "void mysql_parse(THD*, char*, uint, Parser_state*, bool, bool)"
      #21 0x0000564ec55318d1 in dispatch_command (command=COM_QUERY, thd=0x7f52b4000d90, packet=0x7f52b4008f31 "SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 JOIN t1)", packet_length=54, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1852
              packet_end = 0x7f52b4012b0e ""
              parser_state = {m_lip = {lookahead_token = -1, lookahead_yylval = 0x0, m_thd = 0x7f52b4000d90, m_ptr = 0x7f52b4012b0f "\004", m_tok_start = 0x7f52b4012b0f "\004", m_tok_end = 0x7f52b4012b0f "\004", m_end_of_query = 0x7f52b4012b0e "", m_tok_start_prev = 0x7f52b4012b0e "", m_buf = 0x7f52b4012ad8 "SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 JOIN t1)", m_buf_length = 54, m_echo = true, m_echo_saved = 12, m_cpp_buf = 0x7f52b4012b68 "SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 JOIN t1)", m_cpp_ptr = 0x7f52b4012b9e "", m_cpp_tok_start = 0x7f52b4012b9e "", m_cpp_tok_start_prev = 0x7f52b4012b9e "", m_cpp_tok_end = 0x7f52b4012b9e "", m_body_utf8 = 0x0, m_body_utf8_ptr = 0x10000c60cd553 <error: Cannot access memory at address 0x10000c60cd553>, m_cpp_utf8_processed_ptr = 0x0, next_state = MY_LEX_END, found_semicolon = 0x0, ignore_space = false, stmt_prepare_mode = false, multi_statements = true, yylineno = 1, m_digest = 0x0, in_comment = NO_COMMENT, in_comment_saved = PRESERVE_COMMENT, m_cpp_text_start = 0x7f52b4012b9b "t1)", m_cpp_text_end = 0x7f52b4012b9d ")", m_underscore_cs = 0x0}, m_yacc = {yacc_yyss = 0x0, yacc_yyvs = 0x0, m_set_signal_info = {m_item = {0x0 <repeats 12 times>}}, m_lock_type = TL_READ_DEFAULT, m_mdl_type = MDL_SHARED_READ}, m_digest_psi = 0x7f52b4004658}
              net = 0x7f52b4001098
              error = false
              do_end_of_statement = true
              _db_stack_frame_ = {func = 0x564ec616f8bd "do_command", file = 0x564ec616f6a8 "/data/src/10.3/sql/sql_parse.cc", level = 2147483650, line = -1, prev = 0x7f530c41fdf0}
              drop_more_results = false
              __PRETTY_FUNCTION__ = "bool dispatch_command(enum_server_command, THD*, char*, uint, bool, bool)"
              res = <optimized out>
      #22 0x0000564ec553028f in do_command (thd=0x7f52b4000d90) at /data/src/10.3/sql/sql_parse.cc:1398
              return_value = false
              packet = 0x7f52b4008f30 "\003SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 JOIN t1)"
              packet_length = 55
              net = 0x7f52b4001098
              command = COM_QUERY
              _db_stack_frame_ = {func = 0x564ec64f1db0 "?func", file = 0x564ec64f1db6 "?file", level = 2147483649, line = -1, prev = 0x0}
              __PRETTY_FUNCTION__ = "bool do_command(THD*)"
      #23 0x0000564ec56ad8e4 in do_handle_one_connection (connect=0x564ec838cbe0) at /data/src/10.3/sql/sql_connect.cc:1403
              create_user = true
              thr_create_utime = 1821690148455
              thd = 0x7f52b4000d90
      #24 0x0000564ec56ad64f in handle_one_connection (arg=0x564ec838cbe0) at /data/src/10.3/sql/sql_connect.cc:1308
              connect = 0x564ec838cbe0
      #25 0x0000564ec605d86a in pfs_spawn_thread (arg=0x564ec83b5220) at /data/src/10.3/storage/perfschema/pfs.cc:1869
              typed_arg = 0x564ec83b5220
              user_arg = 0x564ec838cbe0
              user_start_routine = 0x564ec56ad61f <handle_one_connection(void*)>
              pfs = 0x7f5310d0db40
              klass = 0x564ec7eba280
      #26 0x00007f5312ba5ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
              ret = <optimized out>
              pd = <optimized out>
              unwind_buf = {cancel_jmp_buf = {{jmp_buf = {139994664666880, 5762966883419168269, 140728480335582, 140728480335583, 139994664665024, 311296, -5666597764456636915, -5666638097395212787}, mask_was_saved = 0}}, priv = {pad = {0x0, 0x0, 0x0, 0x0}, data = {prev = 0x0, cleanup = 0x0, canceltype = 0}}}
              not_first_call = 0
      #27 0x00007f5312ad5def in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
      

      EXPLAIN also fails.

      No obvious effect on a non-debug build.
      EXPLAIN from the non-debug build:

      10.3 c4f65d8f non-debug

      EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 JOIN t1);
      EXPLAIN
      {
        "query_block": {
          "select_id": 1,
          "const_condition": "1",
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows": 1,
            "filtered": 100
          },
          "table": {
            "table_name": "<subquery2>",
            "access_type": "eq_ref",
            "possible_keys": ["distinct_key"],
            "key": "distinct_key",
            "key_length": "4",
            "used_key_parts": ["b"],
            "ref": ["func"],
            "rows": 1,
            "filtered": 100,
            "materialized": {
              "unique": 1,
              "query_block": {
                "select_id": 2,
                "table": {
                  "table_name": "t2",
                  "access_type": "ALL",
                  "rows": 1,
                  "filtered": 100
                },
                "block-nl-join": {
                  "table": {
                    "table_name": "t1",
                    "access_type": "ALL",
                    "rows": 1,
                    "filtered": 100
                  },
                  "buffer_type": "flat",
                  "buffer_size": "256Kb",
                  "join_type": "BNL"
                }
              }
            }
          }
        }
      }
      

      The failure started happening on 10.3 after this commit:

      commit 19c721631ef21bcf3ce3ea3a036da5e234b0f49c
      Author: Sergei Petrunia
      Date:   Mon Jun 6 22:21:22 2022 +0300
       
          MDEV-28749: restore_prev_nj_state() doesn't update cur_sj_inner_tables correctly
          
          (Try 2) (Cherry-pick back into 10.3)
      

        Attachments

          Issue Links

            Activity

              People

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