|
--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)
|
|