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

Crash when executing prepared statement with subqueries

Details

    Description

      Consider the following simplified test case:

      create table bug (
      id binary(16) not null,
      c1 int(11),
      c2 varchar(9),
      c3 varchar(6),
      updated_at datetime
      );
       
      prepare `bug_stmt` FROM "select i1_0.c1 from bug i1_0 where i1_0.id in(select (select i3_0.id from bug i3_0 where i3_0.c2=i2_0.c2 and i3_0.c3='123' order by i3_0.updated_at desc limit 1) from bug i2_0 where i2_0.c3='123' group by i2_0.c2)";
       
      execute `bug_stmt`;
      

      We get a crash while running the execute statement

      240221  9:36:45 [ERROR] mysqld got signal 11 ;
      2024-02-21T09:36:45.665298605Z Sorry, we probably made a mistake, and this is a bug.
      2024-02-21T09:36:45.665300783Z 
      2024-02-21T09:36:45.665302492Z Your assistance in bug reporting will enable us to fix this for the next release.
      2024-02-21T09:36:45.665304154Z To report this bug, see https://mariadb.com/kb/en/reporting-bugs
      2024-02-21T09:36:45.665305862Z 
      2024-02-21T09:36:45.665307438Z We will try our best to scrape up some info that will hopefully help
      2024-02-21T09:36:45.665309093Z diagnose the problem, but since we have already crashed, 
      2024-02-21T09:36:45.665310812Z something is definitely wrong and this may fail.
      2024-02-21T09:36:45.665312409Z 
      2024-02-21T09:36:45.665314005Z Server version: 10.6.17-MariaDB-1:10.6.17+maria~ubu2004 source revision: 15c75ad083a55e198ae78324f22970694b72f22b
      2024-02-21T09:36:45.665315790Z key_buffer_size=16384
      2024-02-21T09:36:45.665319428Z read_buffer_size=262144
      2024-02-21T09:36:45.665321182Z max_used_connections=13
      2024-02-21T09:36:45.665322790Z max_threads=153
      2024-02-21T09:36:45.665324396Z thread_count=13
      2024-02-21T09:36:45.665326088Z It is possible that mysqld could use up to 
      2024-02-21T09:36:45.665327678Z key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 52983 K  bytes of memory
      2024-02-21T09:36:45.665329312Z Hope that's ok; if not, decrease some variables in the equation.
      2024-02-21T09:36:45.665330868Z 
      2024-02-21T09:36:45.665332460Z Thread pointer: 0x7f755c000c58
      2024-02-21T09:36:45.665334159Z Attempting backtrace. You can use the following information to find out
      2024-02-21T09:36:45.665335880Z where mysqld died. If you see no messages after this, something went
      2024-02-21T09:36:45.665337571Z terribly wrong...
      2024-02-21T09:36:45.665340121Z stack_bottom = 0x7f75c06f5d18 thread_stack 0x80000
      2024-02-21T09:36:45.667177433Z Printing to addr2line failed
      2024-02-21T09:36:45.667474790Z mariadbd(my_print_stacktrace+0x32)[0x5617eccba9c2]
      2024-02-21T09:36:45.668019437Z mariadbd(handle_fatal_signal+0x475)[0x5617ec769af5]
      2024-02-21T09:36:45.669267901Z /lib/x86_64-linux-gnu/libpthread.so.0(+0x14420)[0x7f75d7961420]
      2024-02-21T09:36:45.671052531Z mariadbd(+0x7f4f49)[0x5617ec548f49]
      2024-02-21T09:36:45.671729842Z mariadbd(_ZN10Item_equal4sortEPFiP4ItemS1_PvES2_+0x95)[0x5617ec7b84a5]
      2024-02-21T09:36:45.672146617Z mariadbd(+0x8125ff)[0x5617ec5665ff]
      2024-02-21T09:36:45.672664416Z mariadbd(_ZN4JOIN15optimize_stage2Ev+0x5bb)[0x5617ec5899bb]
      2024-02-21T09:36:45.673134204Z mariadbd(_ZN4JOIN14optimize_innerEv+0x1bd0)[0x5617ec58dd90]
      2024-02-21T09:36:45.673484384Z mariadbd(_ZN4JOIN8optimizeEv+0xb0)[0x5617ec590110]
      2024-02-21T09:36:45.673874487Z mariadbd(_ZN13st_select_lex31optimize_unflattened_subqueriesEb+0x116)[0x5617ec4fa996]
      2024-02-21T09:36:45.674170878Z mariadbd(_ZN4JOIN15optimize_stage2Ev+0x1e27)[0x5617ec58b227]
      2024-02-21T09:36:45.674578463Z mariadbd(_ZN4JOIN14optimize_innerEv+0x1bd0)[0x5617ec58dd90]
      2024-02-21T09:36:45.674976511Z mariadbd(_ZN4JOIN8optimizeEv+0xb0)[0x5617ec590110]
      2024-02-21T09:36:45.675332970Z mariadbd(_Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xb7)[0x5617ec5901e7]
      2024-02-21T09:36:45.675737164Z mariadbd(_Z13handle_selectP3THDP3LEXP13select_resultm+0x157)[0x5617ec5909f7]
      2024-02-21T09:36:45.676195233Z mariadbd(+0x7c7701)[0x5617ec51b701]
      2024-02-21T09:36:45.676552715Z mariadbd(_Z21mysql_execute_commandP3THDb+0x4781)[0x5617ec52a181]
      2024-02-21T09:36:45.676851058Z mariadbd(_ZN18Prepared_statement7executeEP6Stringb+0x4aa)[0x5617ec53af2a]
      2024-02-21T09:36:45.677160724Z mariadbd(_ZN18Prepared_statement12execute_loopEP6StringbPhS2_+0xa1)[0x5617ec53b151]
      2024-02-21T09:36:45.677485294Z mariadbd(_Z22mysql_sql_stmt_executeP3THD+0x193)[0x5617ec53b483]
      2024-02-21T09:36:45.677791537Z mariadbd(_Z21mysql_execute_commandP3THDb+0x29c6)[0x5617ec5283c6]
      2024-02-21T09:36:45.678088478Z mariadbd(_Z11mysql_parseP3THDPcjP12Parser_state+0x1e7)[0x5617ec515f27]
      2024-02-21T09:36:45.678443229Z mariadbd(_Z16dispatch_command19enum_server_commandP3THDPcjb+0x1085)[0x5617ec522825]
      2024-02-21T09:36:45.678776055Z mariadbd(_Z10do_commandP3THDb+0x13e)[0x5617ec524b8e]
      2024-02-21T09:36:45.679112037Z mariadbd(_Z24do_handle_one_connectionP7CONNECTb+0x3b7)[0x5617ec63dc17]
      2024-02-21T09:36:45.679441540Z mariadbd(handle_one_connection+0x5d)[0x5617ec63df6d]
      2024-02-21T09:36:45.679914125Z mariadbd(+0xc70f76)[0x5617ec9c4f76]
      2024-02-21T09:36:45.681245962Z /lib/x86_64-linux-gnu/libpthread.so.0(+0x8609)[0x7f75d7955609]
      2024-02-21T09:36:45.682451789Z /lib/x86_64-linux-gnu/libc.so.6(clone+0x43)[0x7f75d7541353]
      2024-02-21T09:36:45.682461370Z 
      2024-02-21T09:36:45.682464165Z Trying to get some variables.
      2024-02-21T09:36:45.682467292Z Some pointers may be invalid and cause the dump to abort.
      2024-02-21T09:36:45.682502035Z Query (0x7f755c01f1f8): select i1_0.c1 from bug i1_0 where i1_0.id in(select (select i3_0.id from bug i3_0 where i3_0.c2=i2_0.c2 and i3_0.c3='123' order by i3_0.updated_at desc limit 1) from bug i2_0 where i2_0.c3='123' group by i2_0.c2)
      2024-02-21T09:36:45.682506366Z 
      2024-02-21T09:36:45.682508141Z Connection ID (thread ID): 16
      2024-02-21T09:36:45.682522495Z Status: NOT_KILLED
      2024-02-21T09:36:45.682524683Z 
      2024-02-21T09:36:45.682535292Z Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=off,cset_narrowing=off
      2024-02-21T09:36:45.682540932Z 
      2024-02-21T09:36:45.682542770Z The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mariadbd/ contains
      2024-02-21T09:36:45.682544541Z information that should help you find out what is causing the crash.
      2024-02-21T09:36:45.682557474Z Writing a core file...
      2024-02-21T09:36:45.682563861Z Working directory at /var/lib/mysql
      2024-02-21T09:36:45.682566463Z Resource Limits:
      2024-02-21T09:36:45.682576509Z Limit                     Soft Limit           Hard Limit           Units     
      2024-02-21T09:36:45.682581531Z Max cpu time              unlimited            unlimited            seconds   
      2024-02-21T09:36:45.682583492Z Max file size             unlimited            unlimited            bytes     
      2024-02-21T09:36:45.682585198Z Max data size             unlimited            unlimited            bytes     
      2024-02-21T09:36:45.682586900Z Max stack size            8388608              unlimited            bytes     
      2024-02-21T09:36:45.682588503Z Max core file size        unlimited            unlimited            bytes     
      2024-02-21T09:36:45.682590057Z Max resident set          unlimited            unlimited            bytes     
      2024-02-21T09:36:45.682591630Z Max processes             unlimited            unlimited            processes 
      2024-02-21T09:36:45.682593215Z Max open files            1048576              1048576              files     
      2024-02-21T09:36:45.682594809Z Max locked memory         8388608              8388608              bytes     
      2024-02-21T09:36:45.682596379Z Max address space         unlimited            unlimited            bytes     
      2024-02-21T09:36:45.682597931Z Max file locks            unlimited            unlimited            locks     
      2024-02-21T09:36:45.682599587Z Max pending signals       126461               126461               signals   
      2024-02-21T09:36:45.682601227Z Max msgqueue size         819200               819200               bytes     
      2024-02-21T09:36:45.682602851Z Max nice priority         0                    0                    
      2024-02-21T09:36:45.682604462Z Max realtime priority     0                    0                    
      2024-02-21T09:36:45.682610745Z Max realtime timeout      unlimited            unlimited            us        
      2024-02-21T09:36:45.682615939Z Core pattern: |/usr/share/apport/apport -p%p -s%s -c%c -d%d -P%P -u%u -g%g -- %E
      2024-02-21T09:36:45.682618018Z 
      2024-02-21T09:36:45.682620158Z Kernel version: Linux version 6.5.0-18-generic (buildd@lcy02-amd64-070) (x86_64-linux-gnu-gcc-12 (Ubuntu 12.3.0-1ubuntu1~22.04) 12.3.0, GNU ld (GNU Binutils for Ubuntu) 2.38) #18~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Wed Feb  7 11:40:03 UTC 2
      2024-02-21T09:36:45.682622944Z 
      

      I agree the sql statement is not clean, but crashing seems to be too much

      Maybe related to MDEV-31296 ; but the issue is still there in 10.6.17 and 10.11.7
      Can be reproduced in 11.3.2 too

      Attachments

        Activity

          alice Alice Sherepa added a comment -

          Thank you for the report! I repeated as descibed on 10.4-11.4:

          --source include/have_innodb.inc
           
          CREATE TABLE t1 (id int, c1 int, c2 int) engine=innodb;
           
          execute immediate " SELECT t1.c1 FROM t1 WHERE t1.id in
            (SELECT  
              (SELECT a1.id FROM t1 a1 WHERE a1.c2=a2.c2 ORDER BY a1.c1 LIMIT 1)
             FROM t1 a2  
             GROUP BY a2.c2)";
           
          drop table t1;
          

          240221 16:21:37 [ERROR] mysqld got signal 11 ;
           
          Server version: 10.4.34-MariaDB-debug-log source revision: 8a505980c52e9d56e3aa523d17b6c1bc1f259ce5
           
          sql/signal_handler.cc:235(handle_fatal_signal)[0x55706d9f4887]
          sigaction.c:0(__restore_rt)[0x7f1055e51420]
          sql/sql_select.cc:15836(compare_fields_by_table_order(Item*, Item*, void*))[0x55706d359a0b]
          sql/sql_list.h:655(void bubble_sort<Item>(List<Item>*, int (*)(Item*, Item*, void*), void*))[0x55706db186a3]
          sql/item_cmpfunc.cc:6944(Item_equal::sort(int (*)(Item*, Item*, void*), void*))[0x55706db0180a]
          sql/sql_select.cc:16315(substitute_for_best_equal_field(THD*, st_join_table*, Item*, COND_EQUAL*, void*, bool))[0x55706d35c14e]
          sql/sql_select.cc:2537(JOIN::optimize_stage2())[0x55706d2f35b4]
          sql/sql_select.cc:2418(JOIN::optimize_inner())[0x55706d2f1e5e]
          sql/sql_select.cc:1731(JOIN::optimize())[0x55706d2ea993]
          sql/sql_lex.cc:4347(st_select_lex::optimize_unflattened_subqueries(bool))[0x55706d1baf6e]
          sql/opt_subselect.cc:5611(JOIN::optimize_unflattened_subqueries())[0x55706d7378a1]
          sql/sql_select.cc:2966(JOIN::optimize_stage2())[0x55706d2f7f81]
          sql/sql_select.cc:2418(JOIN::optimize_inner())[0x55706d2f1e5e]
          sql/sql_select.cc:1731(JOIN::optimize())[0x55706d2ea993]
          sql/sql_select.cc:4836(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*))[0x55706d30bbed]
          sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55706d2dc3e4]
          sql/sql_parse.cc:6549(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55706d2433b4]
          sql/sql_parse.cc:3980(mysql_execute_command(THD*))[0x55706d230599]
          sql/sql_prepare.cc:5025(Prepared_statement::execute(String*, bool))[0x55706d2a2b1f]
          sql/sql_prepare.cc:4472(Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*))[0x55706d29e012]
          sql/sql_prepare.cc:5150(Prepared_statement::execute_immediate(char const*, unsigned int))[0x55706d2a3c8b]
          sql/sql_prepare.cc:2967(mysql_sql_stmt_execute_immediate(THD*))[0x55706d2948fb]
          sql/sql_parse.cc:3987(mysql_execute_command(THD*))[0x55706d2305b6]
          sql/sql_parse.cc:8088(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55706d24c92f]
          sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55706d2226a1]
          sql/sql_parse.cc:1378(do_command(THD*))[0x55706d21f1cc]
          sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x55706d634c02]
          sql/sql_connect.cc:1324(handle_one_connection)[0x55706d6344a6]
          perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55706e2d3b3e]
          nptl/pthread_create.c:478(start_thread)[0x7f1055e45609]
           
          Query (0x62b0000a2b38): SELECT t1.c1 FROM t1 WHERE t1.id in
            (SELECT  
              (SELECT a1.id FROM t1 a1 WHERE a1.c2=a2.c2 ORDER BY a1.c1 LIMIT 1)
             FROM t1 a2  
             GROUP BY a2.c2)
          
          

          240221 15:17:30 [ERROR] mysqld got signal 11 ;
           
          Server version: 10.4.33-MariaDB source revision: 46e3a7658b774942e9320a1acb234373bb44e874
           
          sql/signal_handler.cc:235(handle_fatal_signal)[0x55e502f058ff]
          sigaction.c:0(__restore_rt)[0x7f13bea40420]
          sql/sql_select.cc:15831(compare_fields_by_table_order(Item*, Item*, void*))[0x55e502d526f6]
          sql/sql_list.h:655(Item_equal::sort(int (*)(Item*, Item*, void*), void*))[0x55e502f4e55d]
          sql/sql_list.h:441(base_list_iterator::next_fast())[0x55e502d40f47]
          sql/sql_class.h:4040(THD::is_error() const)[0x55e502d669f4]
          sql/sql_select.cc:2414(JOIN::optimize_inner())[0x55e502d69875]
          sql/sql_select.cc:1733(JOIN::optimize())[0x55e502d6bffd]
          sql/sql_lex.cc:4347(st_select_lex::optimize_unflattened_subqueries(bool))[0x55e502cf9260]
          sql/sql_select.cc:2962(JOIN::optimize_stage2())[0x55e502d67e9c]
          sql/sql_select.cc:2414(JOIN::optimize_inner())[0x55e502d69875]
          sql/sql_select.cc:1733(JOIN::optimize())[0x55e502d6bffd]
          sql/sql_select.cc:4832(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*))[0x55e502d6c111]
          sql/sql_select.cc:454(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55e502d6cd37]
          sql/sql_parse.cc:6524(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55e502bf02f0]
          sql/sql_parse.cc:3980(mysql_execute_command(THD*))[0x55e502d0f009]
          sql/sql_prepare.cc:5025(Prepared_statement::execute(String*, bool))[0x55e502d28275]
          sql/sql_prepare.cc:4472(Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*))[0x55e502d2838e]
          sql/sql_prepare.cc:5150(Prepared_statement::execute_immediate(char const*, unsigned int))[0x55e502d288ea]
          sql/sql_class.h:1456(Item_change_list_savepoint::rollback(Item_change_list*))[0x55e502d28af3]
          sql/sql_parse.cc:3986(mysql_execute_command(THD*))[0x55e502d0ce14]
          sql/sql_parse.cc:8062(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55e502d126a2]
          sql/sql_parse.cc:1921(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55e502d14b53]
          sql/sql_parse.cc:1379(do_command(THD*))[0x55e502d1631f]
          sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x55e502dfd372]
          sql/sql_connect.cc:1325(handle_one_connection)[0x55e502dfd45d]
          nptl/pthread_create.c:478(start_thread)[0x7f13bea34609]
           
          Query (0x7f1358025f08): SELECT t1.c1
          FROM t1
          WHERE t1.id in
            (SELECT
              (SELECT a1.id FROM t1 a1 WHERE a1.c2=a2.c2  AND a1.c3='123' ORDER BY a1.dt DESC LIMIT 1)
             FROM t1 a2 
             WHERE a2.c3='123'  
             GROUP BY a2.c2)
          

          if the select inside the stored procedure - it crashes on the 2nd call.

          240221 15:18:41 [ERROR] mysqld got signal 11 ;
           
          Server version: 10.4.33-MariaDB source revision: 46e3a7658b774942e9320a1acb234373bb44e874
           
          sql/signal_handler.cc:235(handle_fatal_signal)[0x5621537058ff]
          sigaction.c:0(__restore_rt)[0x7f4909cd4420]
          sql/sql_select.cc:15831(compare_fields_by_table_order(Item*, Item*, void*))[0x5621535526f6]
          sql/sql_list.h:655(Item_equal::sort(int (*)(Item*, Item*, void*), void*))[0x56215374e55d]
          sql/sql_list.h:441(base_list_iterator::next_fast())[0x562153540f47]
          sql/sql_class.h:4040(THD::is_error() const)[0x5621535669f4]
          sql/sql_select.cc:2414(JOIN::optimize_inner())[0x562153569875]
          sql/sql_select.cc:1733(JOIN::optimize())[0x56215356bffd]
          sql/sql_lex.cc:4347(st_select_lex::optimize_unflattened_subqueries(bool))[0x5621534f9260]
          sql/sql_select.cc:2962(JOIN::optimize_stage2())[0x562153567e9c]
          sql/sql_select.cc:2414(JOIN::optimize_inner())[0x562153569875]
          sql/sql_select.cc:1733(JOIN::optimize())[0x56215356bffd]
          sql/sql_select.cc:4832(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*))[0x56215356c111]
          sql/sql_select.cc:454(handle_select(THD*, LEX*, select_result*, unsigned long))[0x56215356cd37]
          sql/sql_parse.cc:6524(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5621533f02f0]
          sql/sql_parse.cc:3980(mysql_execute_command(THD*))[0x56215350f009]
          sql/sp_head.cc:3763(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x56215346d085]
          sql/sp_head.cc:3499(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x562153475915]
          sql/sp_head.cc:3668(sp_instr_stmt::execute(THD*, unsigned int*))[0x56215347642c]
          sql/sp_head.cc:1372(sp_head::execute(THD*, bool))[0x562153470df4]
          sql/sp_head.cc:2407(sp_head::execute_procedure(THD*, List<Item>*))[0x5621534727a9]
          sql/sql_parse.cc:3069(do_execute_sp(THD*, sp_head*))[0x562153502e2a]
          sql/sql_parse.cc:3308(Sql_cmd_call::execute(THD*))[0x562153505206]
          sql/sql_parse.cc:3319(Sql_cmd_call::execute(THD*))[0x562153505a40]
          sql/sql_parse.cc:6266(mysql_execute_command(THD*))[0x56215350cddc]
          sql/sql_parse.cc:8062(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5621535126a2]
          sql/sql_parse.cc:1921(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x562153514b53]
          sql/sql_parse.cc:1379(do_command(THD*))[0x56215351631f]
          sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x5621535fd372]
          sql/sql_connect.cc:1325(handle_one_connection)[0x5621535fd45d]
          nptl/pthread_create.c:478(start_thread)[0x7f4909cc8609]
           
          Query (0x7f488c041d90): SELECT t1.c1 FROM t1 WHERE t1.id in   (SELECT     (SELECT a1.id FROM t1 a1 WHERE a1.c2=a2.c2  AND a1.c3='123' ORDER BY a1.dt DESC LIMIT 1)    FROM t1 a2     WHERE a2.c3='123'      GROUP BY a2.c2)
          

          alice Alice Sherepa added a comment - Thank you for the report! I repeated as descibed on 10.4-11.4: --source include/have_innodb.inc   CREATE TABLE t1 (id int , c1 int , c2 int ) engine=innodb; execute immediate " SELECT t1.c1 FROM t1 WHERE t1.id in (SELECT (SELECT a1.id FROM t1 a1 WHERE a1.c2=a2.c2 ORDER BY a1.c1 LIMIT 1) FROM t1 a2 GROUP BY a2.c2)" ;   drop table t1; 240221 16:21:37 [ERROR] mysqld got signal 11 ;   Server version: 10.4.34-MariaDB-debug-log source revision: 8a505980c52e9d56e3aa523d17b6c1bc1f259ce5   sql/signal_handler.cc:235(handle_fatal_signal)[0x55706d9f4887] sigaction.c:0(__restore_rt)[0x7f1055e51420] sql/sql_select.cc:15836(compare_fields_by_table_order(Item*, Item*, void*))[0x55706d359a0b] sql/sql_list.h:655(void bubble_sort<Item>(List<Item>*, int (*)(Item*, Item*, void*), void*))[0x55706db186a3] sql/item_cmpfunc.cc:6944(Item_equal::sort(int (*)(Item*, Item*, void*), void*))[0x55706db0180a] sql/sql_select.cc:16315(substitute_for_best_equal_field(THD*, st_join_table*, Item*, COND_EQUAL*, void*, bool))[0x55706d35c14e] sql/sql_select.cc:2537(JOIN::optimize_stage2())[0x55706d2f35b4] sql/sql_select.cc:2418(JOIN::optimize_inner())[0x55706d2f1e5e] sql/sql_select.cc:1731(JOIN::optimize())[0x55706d2ea993] sql/sql_lex.cc:4347(st_select_lex::optimize_unflattened_subqueries(bool))[0x55706d1baf6e] sql/opt_subselect.cc:5611(JOIN::optimize_unflattened_subqueries())[0x55706d7378a1] sql/sql_select.cc:2966(JOIN::optimize_stage2())[0x55706d2f7f81] sql/sql_select.cc:2418(JOIN::optimize_inner())[0x55706d2f1e5e] sql/sql_select.cc:1731(JOIN::optimize())[0x55706d2ea993] sql/sql_select.cc:4836(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*))[0x55706d30bbed] sql/sql_select.cc:442(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55706d2dc3e4] sql/sql_parse.cc:6549(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55706d2433b4] sql/sql_parse.cc:3980(mysql_execute_command(THD*))[0x55706d230599] sql/sql_prepare.cc:5025(Prepared_statement::execute(String*, bool))[0x55706d2a2b1f] sql/sql_prepare.cc:4472(Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*))[0x55706d29e012] sql/sql_prepare.cc:5150(Prepared_statement::execute_immediate(char const*, unsigned int))[0x55706d2a3c8b] sql/sql_prepare.cc:2967(mysql_sql_stmt_execute_immediate(THD*))[0x55706d2948fb] sql/sql_parse.cc:3987(mysql_execute_command(THD*))[0x55706d2305b6] sql/sql_parse.cc:8088(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55706d24c92f] sql/sql_parse.cc:1860(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55706d2226a1] sql/sql_parse.cc:1378(do_command(THD*))[0x55706d21f1cc] sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x55706d634c02] sql/sql_connect.cc:1324(handle_one_connection)[0x55706d6344a6] perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55706e2d3b3e] nptl/pthread_create.c:478(start_thread)[0x7f1055e45609]   Query (0x62b0000a2b38): SELECT t1.c1 FROM t1 WHERE t1.id in (SELECT (SELECT a1.id FROM t1 a1 WHERE a1.c2=a2.c2 ORDER BY a1.c1 LIMIT 1) FROM t1 a2 GROUP BY a2.c2) 240221 15:17:30 [ERROR] mysqld got signal 11 ;   Server version: 10.4.33-MariaDB source revision: 46e3a7658b774942e9320a1acb234373bb44e874   sql/signal_handler.cc:235(handle_fatal_signal)[0x55e502f058ff] sigaction.c:0(__restore_rt)[0x7f13bea40420] sql/sql_select.cc:15831(compare_fields_by_table_order(Item*, Item*, void*))[0x55e502d526f6] sql/sql_list.h:655(Item_equal::sort(int (*)(Item*, Item*, void*), void*))[0x55e502f4e55d] sql/sql_list.h:441(base_list_iterator::next_fast())[0x55e502d40f47] sql/sql_class.h:4040(THD::is_error() const)[0x55e502d669f4] sql/sql_select.cc:2414(JOIN::optimize_inner())[0x55e502d69875] sql/sql_select.cc:1733(JOIN::optimize())[0x55e502d6bffd] sql/sql_lex.cc:4347(st_select_lex::optimize_unflattened_subqueries(bool))[0x55e502cf9260] sql/sql_select.cc:2962(JOIN::optimize_stage2())[0x55e502d67e9c] sql/sql_select.cc:2414(JOIN::optimize_inner())[0x55e502d69875] sql/sql_select.cc:1733(JOIN::optimize())[0x55e502d6bffd] sql/sql_select.cc:4832(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*))[0x55e502d6c111] sql/sql_select.cc:454(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55e502d6cd37] sql/sql_parse.cc:6524(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55e502bf02f0] sql/sql_parse.cc:3980(mysql_execute_command(THD*))[0x55e502d0f009] sql/sql_prepare.cc:5025(Prepared_statement::execute(String*, bool))[0x55e502d28275] sql/sql_prepare.cc:4472(Prepared_statement::execute_loop(String*, bool, unsigned char*, unsigned char*))[0x55e502d2838e] sql/sql_prepare.cc:5150(Prepared_statement::execute_immediate(char const*, unsigned int))[0x55e502d288ea] sql/sql_class.h:1456(Item_change_list_savepoint::rollback(Item_change_list*))[0x55e502d28af3] sql/sql_parse.cc:3986(mysql_execute_command(THD*))[0x55e502d0ce14] sql/sql_parse.cc:8062(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55e502d126a2] sql/sql_parse.cc:1921(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55e502d14b53] sql/sql_parse.cc:1379(do_command(THD*))[0x55e502d1631f] sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x55e502dfd372] sql/sql_connect.cc:1325(handle_one_connection)[0x55e502dfd45d] nptl/pthread_create.c:478(start_thread)[0x7f13bea34609]   Query (0x7f1358025f08): SELECT t1.c1 FROM t1 WHERE t1.id in (SELECT (SELECT a1.id FROM t1 a1 WHERE a1.c2=a2.c2 AND a1.c3='123' ORDER BY a1.dt DESC LIMIT 1) FROM t1 a2 WHERE a2.c3='123' GROUP BY a2.c2) if the select inside the stored procedure - it crashes on the 2nd call. 240221 15:18:41 [ERROR] mysqld got signal 11 ;   Server version: 10.4.33-MariaDB source revision: 46e3a7658b774942e9320a1acb234373bb44e874   sql/signal_handler.cc:235(handle_fatal_signal)[0x5621537058ff] sigaction.c:0(__restore_rt)[0x7f4909cd4420] sql/sql_select.cc:15831(compare_fields_by_table_order(Item*, Item*, void*))[0x5621535526f6] sql/sql_list.h:655(Item_equal::sort(int (*)(Item*, Item*, void*), void*))[0x56215374e55d] sql/sql_list.h:441(base_list_iterator::next_fast())[0x562153540f47] sql/sql_class.h:4040(THD::is_error() const)[0x5621535669f4] sql/sql_select.cc:2414(JOIN::optimize_inner())[0x562153569875] sql/sql_select.cc:1733(JOIN::optimize())[0x56215356bffd] sql/sql_lex.cc:4347(st_select_lex::optimize_unflattened_subqueries(bool))[0x5621534f9260] sql/sql_select.cc:2962(JOIN::optimize_stage2())[0x562153567e9c] sql/sql_select.cc:2414(JOIN::optimize_inner())[0x562153569875] sql/sql_select.cc:1733(JOIN::optimize())[0x56215356bffd] sql/sql_select.cc:4832(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*))[0x56215356c111] sql/sql_select.cc:454(handle_select(THD*, LEX*, select_result*, unsigned long))[0x56215356cd37] sql/sql_parse.cc:6524(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5621533f02f0] sql/sql_parse.cc:3980(mysql_execute_command(THD*))[0x56215350f009] sql/sp_head.cc:3763(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x56215346d085] sql/sp_head.cc:3499(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x562153475915] sql/sp_head.cc:3668(sp_instr_stmt::execute(THD*, unsigned int*))[0x56215347642c] sql/sp_head.cc:1372(sp_head::execute(THD*, bool))[0x562153470df4] sql/sp_head.cc:2407(sp_head::execute_procedure(THD*, List<Item>*))[0x5621534727a9] sql/sql_parse.cc:3069(do_execute_sp(THD*, sp_head*))[0x562153502e2a] sql/sql_parse.cc:3308(Sql_cmd_call::execute(THD*))[0x562153505206] sql/sql_parse.cc:3319(Sql_cmd_call::execute(THD*))[0x562153505a40] sql/sql_parse.cc:6266(mysql_execute_command(THD*))[0x56215350cddc] sql/sql_parse.cc:8062(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5621535126a2] sql/sql_parse.cc:1921(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x562153514b53] sql/sql_parse.cc:1379(do_command(THD*))[0x56215351631f] sql/sql_connect.cc:1419(do_handle_one_connection(CONNECT*))[0x5621535fd372] sql/sql_connect.cc:1325(handle_one_connection)[0x5621535fd45d] nptl/pthread_create.c:478(start_thread)[0x7f4909cc8609]   Query (0x7f488c041d90): SELECT t1.c1 FROM t1 WHERE t1.id in (SELECT (SELECT a1.id FROM t1 a1 WHERE a1.c2=a2.c2 AND a1.c3='123' ORDER BY a1.dt DESC LIMIT 1) FROM t1 a2 WHERE a2.c3='123' GROUP BY a2.c2)

          People

            psergei Sergei Petrunia
            Seb Sébastien Perol
            Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.