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

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

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28973 Assertion `cur_sj_inner_tables == 0' failed in JOIN::dbug_verify_sj_inner_tables

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            Description {code:sql}
            --source include/have_innodb.inc

            CREATE TABLE t1 (a INT) ENGINE=InnoDB;
            CREATE TABLE t2 (b INT) ENGINE=InnoDB;

            SET optimizer_search_depth= 1;

            SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 JOIN t1);

            # Cleanup
            DROP TABLE t1, t2;
            {code}

            {noformat:title=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
            {noformat}

            EXPLAIN also fails.

            No obvious effect on a non-debug build.
            EXPLAIN from the non-debug build:
            {code:sql|title=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"
                      }
                    }
                  }
                }
              }
            }
            {code}

            The failure started happening on 10.3 after this commit:
            {noformat}
            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)
            {noformat}             		{code:sql}
            --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;
            {code}

            {noformat:title=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
            {noformat}

            EXPLAIN also fails.

            No obvious effect on a non-debug build.
            EXPLAIN from the non-debug build:
            {code:sql|title=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"
                      }
                    }
                  }
                }
              }
            }
            {code}

            The failure started happening on 10.3 after this commit:
            {noformat}
            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)
            {noformat}
            elenst Elena Stepanova made changes -
            Summary Assertion `cur_sj_inner_tables == 0' failed in JOIN::dbug_verify_sj_inner_tables with optimizer_search_depth=1 Assertion `cur_sj_inner_tables == 0' failed in JOIN::dbug_verify_sj_inner_tables with low optimizer_search_depth value
            Roel Roel Van de Paar made changes -
            Roel Roel Van de Paar added a comment - - edited

            MDEV-28973 was marked as a duplicate. Reproducing additional testcase here.

            SET optimizer_search_depth=1;
            		 
            SELECT * FROM INFORMATION_SCHEMA.FILES WHERE LOGFILE_GROUP_NAME IN (SELECT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE  TABLESPACE_NAME IN (SELECT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (NULL)));

            Roel Roel Van de Paar added a comment - - edited MDEV-28973 was marked as a duplicate. Reproducing additional testcase here. SET optimizer_search_depth=1; SELECT * FROM INFORMATION_SCHEMA.FILES WHERE LOGFILE_GROUP_NAME IN ( SELECT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME IN ( SELECT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ( NULL )));
            Roel Roel Van de Paar made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            Roel Roel Van de Paar made changes -
            Labels regression affects-tests regression
            Roel Roel Van de Paar added a comment -

            Additional testcase

            CREATE TABLE t (c DEC,c2 BINARY,c3 INT,c4 BINARY KEY,c5 DEC UNIQUE KEY,c6 NUMERIC(0,0) DEFAULT 3);
            		 
            SET SESSION optimizer_search_depth=1;
            		 
            SELECT * FROM t WHERE 1 IN (SELECT 1 FROM t WHERE 1 IN (SELECT 1 FROM t WHERE 1 IN (SELECT 1)));

            Roel Roel Van de Paar added a comment - Additional testcase CREATE TABLE t (c DEC,c2 BINARY,c3 INT,c4 BINARY KEY,c5 DEC UNIQUE KEY,c6 NUMERIC(0,0) DEFAULT 3); SET SESSION optimizer_search_depth=1; SELECT * FROM t WHERE 1 IN (SELECT 1 FROM t WHERE 1 IN (SELECT 1 FROM t WHERE 1 IN (SELECT 1)));
            Roel Roel Van de Paar added a comment - - edited

            There is something seriously going wrong here. Stack smashing or similar observed on at least 7 occassions:

            10.10.0 2db18fdb3d68d906fbd188ec570a64502ba55849 (Debug)

            		 
            mysqld: /test/bb-10.10-MDEV-28883_dbg/sql/opt_subselect.cc:3612: void JOIN::dbug_verify_sj_inner_tables(uint) const: Assertion `cur_sj_inner_tables == 0' failed.

            10.10.0 2db18fdb3d68d906fbd188ec570a64502ba55849 (Debug)

            		 
            Core was generated by `/test/MDEV-28883_MD280622-mariadb-10.10.0-linux-x86_64-dbg/bin/mysqld --no-defa'.
            		 
            Program terminated with signal SIGABRT, Aborted.
            		 
            #0  0x0000150a3106b00b in ?? ()
            		 
            [Current thread is 1 (LWP 652564)]
            		 
            (gdb) bt
            		 
            #0  0x0000150a3106b00b in ?? ()
            		 
            Backtrace stopped: Cannot access memory at address 0x1509f8361f60

            This stack smashing issue was observed in bb-10.10-MDEV-28883. igor FYI

            Roel Roel Van de Paar added a comment - - edited There is something seriously going wrong here. Stack smashing or similar observed on at least 7 occassions: 10.10.0 2db18fdb3d68d906fbd188ec570a64502ba55849 (Debug) mysqld: /test/bb-10.10-MDEV-28883_dbg/sql/opt_subselect.cc:3612: void JOIN::dbug_verify_sj_inner_tables(uint) const: Assertion `cur_sj_inner_tables == 0' failed. 10.10.0 2db18fdb3d68d906fbd188ec570a64502ba55849 (Debug) Core was generated by `/test/MDEV-28883_MD280622-mariadb-10.10.0-linux-x86_64-dbg/bin/mysqld --no-defa'. Program terminated with signal SIGABRT, Aborted. #0 0x0000150a3106b00b in ?? () [Current thread is 1 (LWP 652564)] (gdb) bt #0 0x0000150a3106b00b in ?? () Backtrace stopped: Cannot access memory at address 0x1509f8361f60 This stack smashing issue was observed in bb-10.10- MDEV-28883 . igor FYI
            Roel Roel Van de Paar made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            Roel Roel Van de Paar made changes -
            Labels affects-tests regression affects-tests regression stack-smashing
            Roel Roel Van de Paar added a comment - - edited

            Note: the following UniqueID will be filtered for now till this issue receives a patch. It could be that a different testcase than the ones already here lead to this issue. i.e. there may be issues/testcases we are missing. I would propose to fix this ticket asap and am happy to retest once done. Note to self: use -NEW 

            cur_sj_inner_tables == 0|SIGABRT|Backtrace stopped: Cannot access memory at address

            Roel Roel Van de Paar added a comment - - edited Note: the following UniqueID will be filtered for now till this issue receives a patch. It could be that a different testcase than the ones already here lead to this issue. i.e. there may be issues/testcases we are missing. I would propose to fix this ticket asap and am happy to retest once done. Note to self: use -NEW cur_sj_inner_tables == 0|SIGABRT|Backtrace stopped: Cannot access memory at address
            Roel Roel Van de Paar added a comment -

            Additional testcase to test with

            SET SESSION optimizer_search_depth=1;
            		 
            SELECT logfile_group_name,file_name,total_extents,INITIAL_SIZE,ENGINE,EXTRA FROM information_schema.FILES WHERE file_type=''AND file_name AND logfile_group_name AND logfile_group_name IN (SELECT logfile_group_name FROM information_schema.FILES WHERE file_type=''AND TABLESPACE_name IN (SELECT 1 FROM information_schema.PARTITIONS WHERE table_schema IN ('')));

            Roel Roel Van de Paar added a comment - Additional testcase to test with SET SESSION optimizer_search_depth=1; SELECT logfile_group_name,file_name,total_extents,INITIAL_SIZE,ENGINE,EXTRA FROM information_schema.FILES WHERE file_type= '' AND file_name AND logfile_group_name AND logfile_group_name IN ( SELECT logfile_group_name FROM information_schema.FILES WHERE file_type= '' AND TABLESPACE_name IN ( SELECT 1 FROM information_schema.PARTITIONS WHERE table_schema IN ( '' )));
            psergei Sergei Petrunia made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            psergei Sergei Petrunia added a comment -

            bb-10.3-mdev28871

            psergei Sergei Petrunia added a comment - bb-10.3-mdev28871
            psergei Sergei Petrunia made changes -
            Fix Version/s 10.3.36 [ 27513 ]
            Fix Version/s 10.4.26 [ 27511 ]
            Fix Version/s 10.5.17 [ 27509 ]
            Fix Version/s 10.6.9 [ 27507 ]
            Fix Version/s 10.7.5 [ 27505 ]
            Fix Version/s 10.8.4 [ 27503 ]
            Fix Version/s 10.9.2 [ 27115 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.7 [ 24805 ]
            Fix Version/s 10.8 [ 26121 ]
            Fix Version/s 10.9 [ 26905 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            Roel Roel Van de Paar added a comment - - edited

            Thank you psergei for the very quick turnaround. I am testing the branch you provided.

            Update: 15000+ trials did not run into issue. It is likely the issue is fixed completely, though the re-test was on the patched 10.3 (and not the 10.10 MDEV-28883 tree). Thank you!

            Roel Roel Van de Paar added a comment - - edited Thank you psergei for the very quick turnaround. I am testing the branch you provided. Update: 15000+ trials did not run into issue. It is likely the issue is fixed completely, though the re-test was on the patched 10.3 (and not the 10.10 MDEV-28883 tree). Thank you!
            Roel Roel Van de Paar made changes -
            Affects Version/s 10.10 [ 27530 ]
            Roel Roel Van de Paar added a comment - - edited

            For completeness, here is one of the most compact testcases which was previously reduced.

            CREATE TABLE t SELECT 1 AS c;
            		 
            SET optimizer_search_depth=1;
            		 
            SELECT * FROM t WHERE 1 IN (SELECT 1 FROM t WHERE 1 IN (SELECT 1 FROM t WHERE 1 IN (SELECT 1)));

            Roel Roel Van de Paar added a comment - - edited For completeness, here is one of the most compact testcases which was previously reduced. CREATE TABLE t SELECT 1 AS c; SET optimizer_search_depth=1; SELECT * FROM t WHERE 1 IN ( SELECT 1 FROM t WHERE 1 IN ( SELECT 1 FROM t WHERE 1 IN ( SELECT 1)));

            People

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