[MDEV-30362] 【BUG】【core dump】group by + having = core dump Created: 2023-01-09  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.6.4, 10.4, 10.4.27, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2

Type: Bug Priority: Major
Reporter: niezhibiao Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: crash, not-10.3
Environment:

ubuntu 16.04.4 LTS
MariaDB 10.6.4


Issue Links:
Duplicate
is duplicated by MDEV-32404 Segmentation fault at /mariadb-11.3.0... Closed
is duplicated by MDEV-32599 Server crash when query on Aria table Closed
Relates
relates to MDEV-26402 A SEGV in Item_field::used_tables/upd... Closed

 Description   

【重现步骤】
1、创建表 t1
CREATE TABLE `t1` (
`col_int` int(11) DEFAULT NULL,
`col_varchar_16` varchar(16) COLLATE utf8mb4_bin DEFAULT NULL,
`col_varchar_16_key` varchar(16) COLLATE utf8mb4_bin DEFAULT NULL,
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_varchar_16_key` (`col_varchar_16_key`),
KEY `col_int_key` (`col_int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

2、执行以下查询
SELECT DISTINCT col_varchar_16_key AS field1 , COUNT( distinct col_varchar_16 ) AS field2 , pk AS field3 FROM `t1` WHERE pk = 9 GROUP BY field1, field3 HAVING ((field3 = 6 OR field2 IS NULL XOR field1 > 2) AND field1 = 3 OR field2 IS NULL);

【预期结果】
正确返回结果

【实际结果】
MariaDB server core dump

【备注】
堆栈信息如下:
230109 9:51:08 [ERROR] mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

To report this bug, see https://mariadb.com/kb/en/reporting-bugs

We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Server version: 10.5.15-MariaDB-debug
key_buffer_size=134217728
read_buffer_size=131072
max_used_connections=1
max_threads=153
thread_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467986 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f2fd8000b18
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x7f30542aae28 thread_stack 0x49000
mysys/stacktrace.c:212(my_print_stacktrace)[0x563a733033a2]
sql/signal_handler.cc:225(handle_fatal_signal)[0x563a729e2b24]
??:0(__restore_rt)[0x7f30577b4390]
sql/item.cc:3453(Item_field::used_tables() const)[0x563a72a10d7f]
sql/sql_select.cc:14303(update_depend_map_for_order(JOIN*, st_order*))[0x563a726e80a1]
sql/sql_select.cc:14411(remove_const(JOIN*, st_order*, Item*, bool, bool*))[0x563a726e842d]
sql/sql_select.cc:2333(JOIN::optimize_inner())[0x563a726c4147]
sql/sql_select.cc:1712(JOIN::optimize())[0x563a726c1c75]
sql/sql_select.cc:4824(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x563a726cd225]
sql/sql_select.cc:457(handle_select(THD*, LEX*, select_result*, unsigned long))[0x563a726bc733]
sql/sql_parse.cc:6314(execute_sqlcom_select(THD*, TABLE_LIST*))[0x563a7267e858]
sql/sql_parse.cc:4005(mysql_execute_command(THD*))[0x563a72675a9d]
sql/sql_parse.cc:8100(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x563a726836af]
sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x563a7266f5d0]
sql/sql_parse.cc:1371(do_command(THD*))[0x563a7266dc96]
sql/sql_connect.cc:1418(do_handle_one_connection(CONNECT*, bool))[0x563a7281eedf]
sql/sql_connect.cc:1314(handle_one_connection)[0x563a7281eb6a]



 Comments   
Comment by niezhibiao [ 2023-01-09 ]

请尽快处理

Comment by Daniel Black [ 2023-01-09 ]

Confirmed with 10.4

10.4-111a752b968561b34a88f33052519cb989a8a90f

Program terminated with signal SIGSEGV, Segmentation fault.
#0  0x00000000008a0d97 in Item_field::used_tables (this=0x7fae30012e28) at /home/dan/repos/mariadb-server-10.4/sql/item.cc:3403
3403	  if (field->table->const_table)
[Current thread is 1 (Thread 0x7fae89cb26c0 (LWP 12018))]
Missing separate debuginfos, use: dnf debuginfo-install glibc-2.36-8.fc37.x86_64 libaio-0.3.111-14.fc37.x86_64 libcap-2.48-5.fc37.x86_64 libgcc-12.2.1-4.fc37.x86_64 libstdc++-12.2.1-4.fc37.x86_64 libxcrypt-4.4.33-4.fc37.x86_64 libzstd-1.5.2-3.fc37.x86_64 lz4-libs-1.9.3-5.fc37.x86_64 pcre-8.45-1.fc37.2.x86_64 snappy-1.1.9-5.fc37.x86_64 systemd-libs-251.10-588.fc37.x86_64 xz-libs-5.2.5-10.fc37.x86_64 zlib-1.2.12-5.fc37.x86_64
(gdb) bt full
#0  0x00000000008a0d97 in Item_field::used_tables (this=0x7fae30012e28) at /home/dan/repos/mariadb-server-10.4/sql/item.cc:3403
No locals.
#1  0x00000000006f5b1c in update_depend_map_for_order (join=0x7fae30015e90, order=0x7fae30013fb8) at /home/dan/repos/mariadb-server-10.4/sql/sql_select.cc:14299
        depend_map = <optimized out>
#2  remove_const (join=join@entry=0x7fae30015e90, first_order=0x7fae30013fb8, cond=0x7fae30016d50, change_list=true, simple_order=simple_order@entry=0x7fae30016164) at /home/dan/repos/mariadb-server-10.4/sql/sql_select.cc:14406
        first_table = 0
        first_is_base_table = <optimized out>
        not_const_tables = 18446744073709551615
        prev_ptr = 0x7fae89cb0a20
        order = <optimized out>
        tmp_order = <optimized out>
        ref = <optimized out>
#3  0x00000000006f3c10 in JOIN::optimize_inner (this=this@entry=0x7fae30015e90) at /home/dan/repos/mariadb-server-10.4/sql/sql_select.cc:2321
        trace_wrapper = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xf2e008 <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 = 0xf2e008 <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 = 0xf2e080 <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 = 0x16b6310 <end_of_list>, last = 0x7fae89cb0b10, elements = 0}, <No data fields>}
        sel = <optimized out>
        ignore_on_expr = <optimized out>
#4  0x00000000006f1494 in JOIN::optimize (this=this@entry=0x7fae30015e90) at /home/dan/repos/mariadb-server-10.4/sql/sql_select.cc:1685
        res = 0
        init_state = JOIN::OPTIMIZATION_IN_PROGRESS
#5  0x00000000006ec35d in mysql_select (thd=thd@entry=0x7fae30000c58, tables=<optimized out>, wild_num=<optimized out>, fields=@0x7fae30012ad0: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x7fae30012f40, last = 0x7fae30013340, elements = 3}, <No data fields>}, conds=<optimized out>, og_num=<optimized out>, order=<optimized out>, group=<optimized out>, having=<optimized out>, proc_param=<optimized out>, select_options=<optimized out>, result=<optimized out>, unit=<optimized out>, select_lex=<optimized out>) at /home/dan/repos/mariadb-server-10.4/sql/sql_select.cc:4781
        err = <optimized out>
        free_join = true
        join = 0x7fae30015e90
#6  0x00000000006ec245 in handle_select (thd=thd@entry=0x7fae30000c58, lex=lex@entry=0x7fae30004910, result=result@entry=0x7fae30015e68, setup_tables_done_option=setup_tables_done_option@entry=0) at /home/dan/repos/mariadb-server-10.4/sql/sql_select.cc:437
        unit = 0x7fae300049d0
        select_lex = 0x7fae30012988
        res = <optimized out>
#7  0x00000000006cd692 in execute_sqlcom_select (thd=thd@entry=0x7fae30000c58, all_tables=0x7fae300133a8) at /home/dan/repos/mariadb-server-10.4/sql/sql_parse.cc:6452
        save_protocol = 0x0
        lex = 0x7fae30004910
        result = 0x7fae30015e68
        res = <optimized out>
#8  0x00000000006c8193 in mysql_execute_command (thd=thd@entry=0x7fae30000c58) at /home/dan/repos/mariadb-server-10.4/sql/sql_parse.cc:3966
        privileges_requested = <optimized out>
        ots = {ctx = 0x7fae300045f8, traceable = false}
        trace_command = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xf2e008 <vtable for Json_writer_object+16>, my_writer = 0x0, context = {writer = 0x0}, closed = false}, <No data fields>}
        trace_command_steps = {<Json_writer_struct> = {_vptr$Json_writer_struct = 0xf2e080 <vtable for Json_writer_array+16>, my_writer = 0x0, context = {writer = 0x0}, closed = false}, <No data fields>}
        res = 0
        up_result = 0
        lex = 0x7fae30004910
        select_lex = 0x7fae30012988
        first_table = 0x7fae300133a8
        unit = 0x7fae300049d0
        have_table_map_for_update = <optimized out>
        all_tables = 0x0
        rpl_filter = <optimized out>
        orig_binlog_format = <optimized out>
        orig_current_stmt_binlog_format = <optimized out>
        error = <optimized out>
        wsrep_error_label = <optimized out>
#9  0x00000000006c44ea in mysql_parse (thd=thd@entry=0x7fae30000c58, rawbuf=0x7fae30012750 "SELECT DISTINCT col_varchar_16_key AS field1 , COUNT( distinct col_varchar_16 ) AS field2 , pk AS field3 FROM `t1` WHERE pk = 9 GROUP BY field1, field3 HAVING ((field3 = 6 OR field2 IS NULL XOR field1"..., length=<optimized out>, parser_state=parser_state@entry=0x7fae89cb1670, is_com_multi=false, is_next_command=false) at /home/dan/repos/mariadb-server-10.4/sql/sql_parse.cc:7984
        found_semicolon = <optimized out>
        error = <optimized out>
        lex = 0x7fae30004910
        err = false
#10 0x00000000006c284d in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7fae30000c58, packet=packet@entry=0x7fae30007d59 "SELECT DISTINCT col_varchar_16_key AS field1 , COUNT( distinct col_varchar_16 ) AS field2 , pk AS field3 FROM `t1` WHERE pk = 9 GROUP BY field1, field3 HAVING ((field3 = 6 OR field2 IS NULL XOR field1"..., packet_length=packet_length@entry=239, is_com_multi=false, is_next_command=false) at /home/dan/repos/mariadb-server-10.4/sql/sql_parse.cc:1857
        parser_state = {m_lip = {lookahead_token = -1, lookahead_yylval = 0x0, m_thd = 0x7fae30000c58, m_ptr = 0x7fae30012840 "\004", m_tok_start = 0x7fae30012840 "\004", m_tok_end = 0x7fae30012840 "\004", m_end_of_query = 0x7fae3001283f "", m_tok_start_prev = 0x7fae3001283f "", m_buf = 0x7fae30012750 "SELECT DISTINCT col_varchar_16_key AS field1 , COUNT( distinct col_varchar_16 ) AS field2 , pk AS field3 FROM `t1` WHERE pk = 9 GROUP BY field1, field3 HAVING ((field3 = 6 OR field2 IS NULL XOR field1"..., m_buf_length = 239, m_echo = true, m_echo_saved = 101, m_cpp_buf = 0x7fae30012898 "SELECT DISTINCT col_varchar_16_key AS field1 , COUNT( distinct col_varchar_16 ) AS field2 , pk AS field3 FROM `t1` WHERE pk = 9 GROUP BY field1, field3 HAVING ((field3 = 6 OR field2 IS NULL XOR field1"..., m_cpp_ptr = 0x7fae30012987 "", m_cpp_tok_start = 0x7fae30012987 "", m_cpp_tok_start_prev = 0x7fae30012987 "", m_cpp_tok_end = 0x7fae30012987 "", 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 = 0x7fae30012978 "field2 IS NULL)", m_cpp_text_end = 0x7fae3001297e " IS NULL)", 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 = <optimized out>
#11 0x00000000006c4974 in do_command (thd=0x7fae30000c58) at /home/dan/repos/mariadb-server-10.4/sql/sql_parse.cc:1378
        packet = <optimized out>
        net = 0x7fae30000f00
        packet_length = <optimized out>
        command = COM_QUERY
        return_value = <optimized out>
#12 0x00000000007a6cd9 in do_handle_one_connection (connect=<optimized out>) at /home/dan/repos/mariadb-server-10.4/sql/sql_connect.cc:1420
        create_user = true
        thr_create_utime = <optimized out>
        thd = 0x7fae30012e28
#13 0x00000000007a6b13 in handle_one_connection (arg=0x309f3e8) at /home/dan/repos/mariadb-server-10.4/sql/sql_connect.cc:1324
        connect = 0x309f3e8
#14 0x00007faea168e14d in start_thread () from /lib64/libc.so.6
No symbol table info available.
#15 0x00007faea170fa00 in clone3 () from /lib64/libc.so.6
No symbol table info available.
(gdb) p field
$1 = (Field *) 0x0

Comment by Alice Sherepa [ 2023-01-09 ]

Thank you for the report! I repeated on 10.4-10.11, with InnoDB/Myisam:

CREATE TABLE t1 (a int, b int);
SELECT  a, count(b) AS c FROM t1 GROUP BY a HAVING c IS NULL xor a > 2;

10.4 111a752b968561b34a88f3305251

230109 11:29:45 [ERROR] mysqld got signal 11 ;
 
Server version: 10.4.28-MariaDB-debug-log
 
sigaction.c:0(__restore_rt)[0x7fb6cd234420]
sql/item.cc:3403(Item_field::used_tables() const)[0x55feb2b1cdfb]
sql/sql_select.cc:14299(update_depend_map_for_order(JOIN*, st_order*))[0x55feb2422dfb]
sql/sql_select.cc:14407(remove_const(JOIN*, st_order*, Item*, bool, bool*))[0x55feb2423849]
sql/sql_select.cc:2321(JOIN::optimize_inner())[0x55feb23c3526]
sql/sql_select.cc:1685(JOIN::optimize())[0x55feb23bc915]
sql/sql_select.cc:4781(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55feb23dd9d1]
sql/sql_select.cc:437(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55feb23ae7d6]
sql/sql_parse.cc:6452(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55feb231c900]
sql/sql_parse.cc:3966(mysql_execute_command(THD*))[0x55feb230a21e]
sql/sql_parse.cc:7984(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55feb2325cdf]
sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55feb22fc56c]
sql/sql_parse.cc:1378(do_command(THD*))[0x55feb22f906c]
sql/sql_connect.cc:1420(do_handle_one_connection(CONNECT*))[0x55feb26fb343]
sql/sql_connect.cc:1325(handle_one_connection)[0x55feb26fabe7]
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55feb3386619]
nptl/pthread_create.c:478(start_thread)[0x7fb6cd228609]
 
Query (0x62b0000a1290): SELECT  a, count(b) AS c FROM t1 GROUP BY a HAVING c IS NULL xor a > 2

Generated at Thu Feb 08 10:15:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.