Daniel Black added a comment - 2023-05-05 06:55 From 10.6-release I needed to specify "character set utf8mb4" on sssss.t but otherwise crashes as described. Also occurs on InnoDB. 10.6-db3342b325f81c7f928d722a469f9ceceb6dc30d #0 get_sort_by_table (a=0x0, b=<optimized out>, tables=<optimized out>, const_tables=<optimized out>) at /home/dan/repos/mariadb-server-10.6/sql/sql_select.cc:26152 bt 26152 if (map != table->table->map) [Current thread is 1 (Thread 0x7f0698b546c0 (LWP 1060660))]   (gdb) bt full #0 get_sort_by_table (a=0x0, b=<optimized out>, tables=<optimized out>, const_tables=<optimized out>) at /home/dan/repos/mariadb-server-10.6/sql/sql_select.cc:26152 ti = {<base_list_iterator> = {list = <optimized out>, el = 0x155a0e0 <end_of_list>, prev = <optimized out>, current = 0x155a0e0 <end_of_list>}, <No data fields>} map = 2 table = 0x0 #1 0x0000000000739087 in make_join_statistics (join=join@entry=0x7f0618017538, tables_list=@0x7f0618016078: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7f0618018518, last = 0x7f0618018518, elements = 1}, <No data fields>}, keyuse_array=keyuse_array@entry=0x7f0618017858) at /home/dan/repos/mariadb-server-10.6/sql/sql_select.cc:5676 error = <optimized out> table = <optimized out> outer_join = <optimized out> no_rows_const_tables = <optimized out> sargables = <optimized out> ti = <optimized out> thd = 0x7f0618000c68 table_count = 1 table_vector = <optimized out> stat = <optimized out> stat_ref = 0x7f061804f638 stat_vector = <optimized out> i = <optimized out> stat_end = 0x7f061804f638 all_table_map = <optimized out> found_const_table_map = <optimized out> const_count = <optimized out> s = 0x0 tables = <optimized out> keyuse = <optimized out> start_keyuse = <optimized out> const_ref = <optimized out> has_expensive_keyparts = <optimized out> key = <optimized out> eq_part = <optimized out> ref_changed = <optimized out> #2 0x0000000000735e96 in JOIN::optimize_inner (this=this@entry=0x7f0618017538) at /home/dan/repos/mariadb-server-10.6/sql/sql_select.cc:2509 trace_wrapper = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xe1fbe8 <vtable for Json_writer_object+16>, my_writer = 0x0, context = {writer = 0x0}, closed = false}, <No data fields>} trace_prepare = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xe1fbe8 <vtable for Json_writer_object+16>, my_writer = 0x0, context = {writer = 0x0}, closed = false}, <No data fields>} trace_steps = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xe1fc60 <vtable for Json_writer_array+16>, my_writer = 0x0, context = {writer = 0x0}, closed = false}, <No data fields>} eq_list = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x155a0e0 <end_of_list>, last = 0x7f0698b52a00, elements = 0}, <No data fields>} sel = <optimized out> ignore_on_expr = <optimized out> #3 0x000000000073371c in JOIN::optimize (this=this@entry=0x7f0618017538) at /home/dan/repos/mariadb-server-10.6/sql/sql_select.cc:1848 res = 0 init_state = JOIN::OPTIMIZATION_IN_PROGRESS #4 0x000000000072dbc1 in mysql_select (thd=thd@entry=0x7f0618000c68, tables=<optimized out>, fields=@0x7f0618016118: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7f0618016430, last = 0x7f0618016430, elements = 1}, <No data fields>}, conds=<optimized out>, og_num=<optimized out>, order=<optimized out>, group=0x0, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x7f0618017510, unit=0x7f0618004e18, select_lex=0x7f0618015e60) at /home/dan/repos/mariadb-server-10.6/sql/sql_select.cc:5055 err = <optimized out> free_join = true join = 0x7f0618017538 #5 0x000000000072da9f in handle_select (thd=thd@entry=0x7f0618000c68, lex=lex@entry=0x7f0618004d50, result=result@entry=0x7f0618017510, setup_tables_done_option=setup_tables_done_option@entry=0) at /home/dan/repos/mariadb-server-10.6/sql/sql_select.cc:559 unit = 0x7f0618004e18 select_lex = 0x7f0618015e60 res = <optimized out> #6 0x000000000070c358 in execute_sqlcom_select (thd=thd@entry=0x7f0618000c68, all_tables=0x7f0618016470) at /home/dan/repos/mariadb-server-10.6/sql/sql_parse.cc:6273 save_protocol = 0x0 lex = 0x7f0618004d50 result = 0x7f0618017510 res = <optimized out> #7 0x00000000007072d8 in mysql_execute_command (thd=thd@entry=0x7f0618000c68, is_called_from_prepared_stmt=<optimized out>) at /home/dan/repos/mariadb-server-10.6/sql/sql_parse.cc:3949 privileges_requested = <optimized out> ots = {ctx = 0x7f0618004a20, traceable = false} res = 0 up_result = 0 lex = 0x7f0618004d50 select_lex = 0x7f0618015e60 first_table = 0x7f0618016470 unit = 0x7f0618004e18 have_table_map_for_update = <optimized out> all_tables = 0x7f0618015d10 rpl_filter = <optimized out> orig_binlog_format = <optimized out> orig_current_stmt_binlog_format = <optimized out> error = <optimized out> wsrep_error_label = <optimized out> #8 0x000000000070317e in mysql_parse (thd=thd@entry=0x7f0618000c68, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x7f0698b535a0) at /home/dan/repos/mariadb-server-10.6/sql/sql_parse.cc:8036 found_semicolon = <optimized out> error = <optimized out> lex = 0x7f0618004d50 err = false #9 0x0000000000701735 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f0618000c68, packet=packet@entry=0x7f0618008519 "(SELECT `t` FROM `tttttt` a INNER JOIN `sssss` b ON a.id=b.id LIMIT 20) ORDER BY `t` COLLATE utf8mb4_general_ci DESC", packet_length=packet_length@entry=116, blocking=true) at /home/dan/repos/mariadb-server-10.6/sql/sql_parse.cc:1896 parser_state = {m_lip = {lookahead_token = -1, lookahead_yylval = 0x0, m_thd = 0x7f0618000c68, m_ptr = 0x7f06180130b5 "\004", m_tok_start = 0x7f06180130b5 "\004", m_tok_end = 0x7f06180130b5 "\004", m_end_of_query = 0x7f06180130b4 "", m_tok_start_prev = 0x7f06180130b4 "", m_buf = 0x7f0618013040 "(SELECT `t` FROM `tttttt` a INNER JOIN `sssss` b ON a.id=b.id LIMIT 20) ORDER BY `t` COLLATE utf8mb4_general_ci DESC", m_buf_length = 116, m_echo = true, m_echo_saved = true, m_cpp_buf = 0x7f0618013110 "(SELECT `t` FROM `tttttt` a INNER JOIN `sssss` b ON a.id=b.id LIMIT 20) ORDER BY `t` COLLATE utf8mb4_general_ci DESC", m_cpp_ptr = 0x7f0618013184 "", m_cpp_tok_start = 0x7f0618013184 "", m_cpp_tok_start_prev = 0x7f0618013184 "", m_cpp_tok_end = 0x7f0618013184 "", m_body_utf8 = 0x0, m_body_utf8_ptr = 0x0, 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 = NO_COMMENT, m_cpp_text_start = 0x7f061801316d "utf8mb4_general_ci DESC", m_cpp_text_end = 0x7f061801317f " DESC", 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 = 0x0} packet_end = <optimized out> net = <optimized out> error = false do_end_of_statement = true drop_more_results = false #10 0x0000000000703610 in do_command (thd=0x7f0618000c68, blocking=true) at /home/dan/repos/mariadb-server-10.6/sql/sql_parse.cc:1409 packet = 0x7f0618008518 "\003(SELECT `t` FROM `tttttt` a INNER JOIN `sssss` b ON a.id=b.id LIMIT 20) ORDER BY `t` COLLATE utf8mb4_general_ci DESC" net = <optimized out> command = COM_QUERY packet_length = 117 return_value = <optimized out> #11 0x0000000000800e5e in do_handle_one_connection (connect=<optimized out>, connect@entry=0x302e9e8, put_in_cache=true) at /home/dan/repos/mariadb-server-10.6/sql/sql_connect.cc:1416 create_user = true thr_create_utime = <optimized out> thd = 0x7f0618015d10 #12 0x0000000000800c7b in handle_one_connection (arg=arg@entry=0x302e9e8) at /home/dan/repos/mariadb-server-10.6/sql/sql_connect.cc:1318 connect = 0x302e9e8 #13 0x0000000000b13eae in pfs_spawn_thread (arg=0x2fdf2a8) at /home/dan/repos/mariadb-server-10.6/storage/perfschema/pfs.cc:2201 typed_arg = 0x2fdf2a8 klass = <optimized out> pfs = <optimized out> user_start_routine = 0x800c00 <handle_one_connection(void*)> user_arg = 0x302e9e8

Alice Sherepa added a comment - 2023-05-05 08:58 Thank you for the report! This is the same problem as MDEV-29681 - I will add this test case there.