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

choose_best_splitting: crash on update query using correlated subquery after minor update

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Critical
    • Resolution: Unresolved
    • 10.9.6, 10.9.7, 10.5, 10.6, 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.7(EOL)
    • 10.5, 10.6, 10.11
    • Optimizer
    • Linux Debian jemalloc ldd (Debian GLIBC 2.31-13+deb11u6) 2.31

    Description

      Following mineur update from 10.9.5 to 10.9.7 the attached query will crash on the updated replica but also when executed in command line .

      Server version: 10.9.7-MariaDB-1:10.9.7+maria~deb11-log source revision: 33fd519ca7318cd85bee56e8f79df4608ead194e
      key_buffer_size=268435456
      read_buffer_size=1048576
      max_used_connections=1
      max_threads=65636
      thread_count=12
      It is possible that mysqld could use up to 
      key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 201930294 K  bytes of memory
      Hope that's ok; if not, decrease some variables in the equation.
      Thread pointer: 0x7f74f0c071d8
      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 = 0x7f75782fc4a8 thread_stack 0x40000
      /usr/sbin/mariadbd(my_print_stacktrace+0x2e)[0x559f942c648e]
      /usr/sbin/mariadbd(handle_fatal_signal+0x485)[0x559f93d9ff15]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x13140)[0x7f8de2d9e140]
      /usr/sbin/mariadbd(_ZN13st_join_table21choose_best_splittingEjyPK8POSITIONPy+0x564)[0x559f93d1d784]
      /usr/sbin/mariadbd(_Z16best_access_pathP4JOINP13st_join_tableyPK8POSITIONjbdPS3_S6_+0x14f)[0x559f93b9246f]
      /usr/sbin/mariadbd(+0x84c21f)[0x559f93b9621f]
      /usr/sbin/mariadbd(+0x84c4de)[0x559f93b964de]
      /usr/sbin/mariadbd(+0x84c4de)[0x559f93b964de]
      /usr/sbin/mariadbd(_Z11choose_planP4JOINy+0x2a4)[0x559f93b97034]
      /usr/sbin/mariadbd(+0x877760)[0x559f93bc1760]
      /usr/sbin/mariadbd(_ZN4JOIN14optimize_innerEv+0x12e4)[0x559f93bc6064]
      /usr/sbin/mariadbd(_ZN4JOIN8optimizeEv+0xa0)[0x559f93bc6670]
      /usr/sbin/mariadbd(_Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xca)[0x559f93bc675a]
      /usr/sbin/mariadbd(_Z18mysql_multi_updateP3THDP10TABLE_LISTP4ListI4ItemES6_PS4_y15enum_duplicatesbP18st_select_lex_unitP13st_select_lexPP12multi_update+0x16d)[0x559f93c2d2dd]
      /usr/sbin/mariadbd(_Z21mysql_execute_commandP3THDb+0x45a0)[0x559f93b4d080]
      /usr/sbin/mariadbd(_Z11mysql_parseP3THDPcjP12Parser_state+0x1e7)[0x559f93b4e917]
      /usr/sbin/mariadbd(_ZN15Query_log_event14do_apply_eventEP14rpl_group_infoPKcj+0xb26)[0x559f93ee5636]
      /usr/sbin/mariadbd(_ZN9Log_event11apply_eventEP14rpl_group_info+0x74)[0x559f93ed8b74]
      /usr/sbin/mariadbd(_Z39apply_event_and_update_pos_for_parallelP9Log_eventP3THDP14rpl_group_info+0x173)[0x559f93a8aee3]
      /usr/sbin/mariadbd(+0x994f4c)[0x559f93cdef4c]
      /usr/sbin/mariadbd(handle_rpl_parallel_thread+0xdc5)[0x559f93ce3b05]
      /usr/sbin/mariadbd(+0xc86282)[0x559f93fd0282]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x7ea7)[0x7f8de2d92ea7]
      /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f8de2999a2f]
      

      Create Table: CREATE TABLE `lastname_page` (
        `obituary_id` int(11) unsigned NOT NULL,
        `lastname_uri` varchar(255) NOT NULL,
        `lastname_page` smallint(5) unsigned DEFAULT NULL,
        PRIMARY KEY (`obituary_id`,`lastname_uri`),
        KEY `lastname_uri_page_id` (`lastname_uri`,`lastname_page`,`obituary_id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
       
      CREATE TABLE `data_entity` (
        `uri` varchar(255) NOT NULL,
        `type_uri` varchar(255) NOT NULL,
        `latitude` float DEFAULT NULL,
        `longitude` float DEFAULT NULL,
        `status` tinyint(4) DEFAULT 1,
        PRIMARY KEY (`uri`),
        KEY `data_entity_type_uri_idx` (`type_uri`),
        KEY `data_entity_latitude_longitude_idx` (`latitude`,`longitude`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
      

      query in the attached file:

      UPDATE data_entity
               INNER JOIN (
                  SELECT lastname_uri
                  FROM lastname_page
                  INNER JOIN data_entity on uri = lastname_uri
                  WHERE lastname_uri IN (<list of 31124 constants>)
                  GROUP BY lastname_uri
                  HAVING count(*) >= 10
                ) lp ON lp.lastname_uri = uri
              SET status = 1
              WHERE uri IN (<list of 31124 constants, seems same as above>)
              AND status = 0
      

      Attachments

        Issue Links

          Activity

            alice Alice Sherepa added a comment - - edited

            test case from MDEV-32624 - reproducible after 10.5 e87440b (MDEV-26301)
            Crash is repeatable on 10.5-10.11, but not on 11.0+

            --source include/have_innodb.inc
             
            CREATE TABLE `table1` (
            `id_product`int(11),
            `id_stock`varchar(32),
            `quantity` mediumint(9),
            PRIMARY KEY (`id_product`,`id_stock`)
            ) engine=innodb;
             
            CREATE TABLE `table2` (
            `id_product`int(10) unsigned,
            `id_shop`int(10) unsigned,
            PRIMARY KEY (`id_product`,`id_shop`)
            )  engine=innodb;
             
             
            SELECT backups.id_product, quantity_backup, quantity_bl
            FROM (
            SELECT id_product, SUM(quantity) as quantity_backup
            FROM`table1`
            ) backups
            INNER JOIN (
            SELECT id_product
            FROM`table1`
            GROUP BY id_product
            ) normals ON (normals.id_product = backups.id_product)
            LEFT JOIN (
            SELECT quantity as quantity_bl, id_product FROM table1
            ) bl ON (backups.id_product = bl.id_product )
            WHERE
            backups.id_product IN (
            SELECT id_product
            FROM table2
            );
            

            Version: '10.5.20-MariaDB-debug-log'  
            231030 15:51:48 [ERROR] mysqld got signal 11 ;
             
            Server version: 10.5.20-MariaDB-debug-log source revision: e87440b79ec6f3e3ed2e6639a3239a4d02630b7e
             
            sql/signal_handler.cc:241(handle_fatal_signal)[0x55a1094053ea]
            sigaction.c:0(__restore_rt)[0x7feeb8d12420]
            sql/opt_split.cc:1047(st_join_table::choose_best_splitting(unsigned int, unsigned long long, unsigned long long*))[0x55a10924134d]
            sql/sql_select.cc:7555(best_access_path(JOIN*, st_join_table*, unsigned long long, POSITION const*, unsigned int, bool, double, POSITION*, POSITION*))[0x55a108cca97c]
            sql/sql_select.cc:9869(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x55a108cd798c]
            sql/sql_select.cc:9958(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x55a108cd867f]
            sql/sql_select.cc:9958(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x55a108cd867f]
            sql/sql_select.cc:9037(greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int, unsigned int))[0x55a108cd36f7]
            sql/sql_select.cc:8599(choose_plan(JOIN*, unsigned long long))[0x55a108cd13c3]
            sql/sql_select.cc:5738(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x55a108cbc0e7]
            sql/sql_select.cc:2342(JOIN::optimize_inner())[0x55a108c98b66]
            sql/sql_select.cc:1700(JOIN::optimize())[0x55a108c9203f]
            sql/sql_select.cc:4830(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*))[0x55a108cb2ea6]
            sql/sql_select.cc:450(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55a108c83d35]
            sql/sql_parse.cc:6331(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55a108be98c9]
            sql/sql_parse.cc:4008(mysql_execute_command(THD*))[0x55a108bd863c]
            sql/sql_parse.cc:8106(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55a108bf4bc3]
            sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55a108bca819]
            sql/sql_parse.cc:1375(do_command(THD*))[0x55a108bc718b]
            sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x55a109023ea7]
            sql/sql_connect.cc:1320(handle_one_connection)[0x55a10902380b]
            perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55a109cad45a]
            nptl/pthread_create.c:478(start_thread)[0x7feeb8d06609]
             
            Query (0x62b0000852a8): SELECT backups.id_product, quantity_backup, quantity_bl
            FROM (
            SELECT id_product, SUM(quantity) as quantity_backup
            FROM`table1`
            ) backups
            INNER JOIN (
            SELECT id_product
            FROM`table1`
            GROUP BY id_product
            ) normals ON (normals.id_product = backups.id_product)
            LEFT JOIN (
            SELECT quantity as quantity_bl, id_product FROM table1
            ) bl ON (backups.id_product = bl.id_product )
            WHERE
            backups.id_product IN (
            SELECT id_product
            FROM table2
            )
            

            alice Alice Sherepa added a comment - - edited test case from MDEV-32624 - reproducible after 10.5 e87440b ( MDEV-26301 ) Crash is repeatable on 10.5-10.11, but not on 11.0+ --source include/have_innodb.inc CREATE TABLE `table1` ( `id_product` int (11), `id_stock` varchar (32), `quantity` mediumint(9), PRIMARY KEY (`id_product`,`id_stock`) ) engine=innodb; CREATE TABLE `table2` ( `id_product` int (10) unsigned, `id_shop` int (10) unsigned, PRIMARY KEY (`id_product`,`id_shop`) ) engine=innodb;     SELECT backups.id_product, quantity_backup, quantity_bl FROM ( SELECT id_product, SUM (quantity) as quantity_backup FROM `table1` ) backups INNER JOIN ( SELECT id_product FROM `table1` GROUP BY id_product ) normals ON (normals.id_product = backups.id_product) LEFT JOIN ( SELECT quantity as quantity_bl, id_product FROM table1 ) bl ON (backups.id_product = bl.id_product ) WHERE backups.id_product IN ( SELECT id_product FROM table2 ); Version: '10.5.20-MariaDB-debug-log' 231030 15:51:48 [ERROR] mysqld got signal 11 ;   Server version: 10.5.20-MariaDB-debug-log source revision: e87440b79ec6f3e3ed2e6639a3239a4d02630b7e   sql/signal_handler.cc:241(handle_fatal_signal)[0x55a1094053ea] sigaction.c:0(__restore_rt)[0x7feeb8d12420] sql/opt_split.cc:1047(st_join_table::choose_best_splitting(unsigned int, unsigned long long, unsigned long long*))[0x55a10924134d] sql/sql_select.cc:7555(best_access_path(JOIN*, st_join_table*, unsigned long long, POSITION const*, unsigned int, bool, double, POSITION*, POSITION*))[0x55a108cca97c] sql/sql_select.cc:9869(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x55a108cd798c] sql/sql_select.cc:9958(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x55a108cd867f] sql/sql_select.cc:9958(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x55a108cd867f] sql/sql_select.cc:9037(greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int, unsigned int))[0x55a108cd36f7] sql/sql_select.cc:8599(choose_plan(JOIN*, unsigned long long))[0x55a108cd13c3] sql/sql_select.cc:5738(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x55a108cbc0e7] sql/sql_select.cc:2342(JOIN::optimize_inner())[0x55a108c98b66] sql/sql_select.cc:1700(JOIN::optimize())[0x55a108c9203f] sql/sql_select.cc:4830(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*))[0x55a108cb2ea6] sql/sql_select.cc:450(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55a108c83d35] sql/sql_parse.cc:6331(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55a108be98c9] sql/sql_parse.cc:4008(mysql_execute_command(THD*))[0x55a108bd863c] sql/sql_parse.cc:8106(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55a108bf4bc3] sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55a108bca819] sql/sql_parse.cc:1375(do_command(THD*))[0x55a108bc718b] sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x55a109023ea7] sql/sql_connect.cc:1320(handle_one_connection)[0x55a10902380b] perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55a109cad45a] nptl/pthread_create.c:478(start_thread)[0x7feeb8d06609]   Query (0x62b0000852a8): SELECT backups.id_product, quantity_backup, quantity_bl FROM ( SELECT id_product, SUM(quantity) as quantity_backup FROM`table1` ) backups INNER JOIN ( SELECT id_product FROM`table1` GROUP BY id_product ) normals ON (normals.id_product = backups.id_product) LEFT JOIN ( SELECT quantity as quantity_bl, id_product FROM table1 ) bl ON (backups.id_product = bl.id_product ) WHERE backups.id_product IN ( SELECT id_product FROM table2 )
            alice Alice Sherepa added a comment - - edited

            Test case from MDEV-32602 , crashes on 10.5-11.2, after e87440b commit

            CREATE TABLE t1 ( id int NOT NULL, PRIMARY KEY (id));
             
            INSERT INTO t1 VALUES
            (-65632),(-65622),(-65570),(-65560),(-65553),(-65543),(-65504),(-65494),(-65491),(-65481),(-65442),(-65432),(-65425),(-65415),(-65363),(-65353),(-32864),(-32854),(-32802),(-32792),(-32785),(-32775),(-32736),(-32726),(-32723),(-32713),(-32674),(-32664),(-32657),(-32647),(-32595),(-32585),(-96),(-86),(-34),(-24),(-17),(-7),(32),(42),(45),(55),(94),(104),(111),(121),(173),(183),(2191719),(2191729),(2191781),(2191791),(2191798),(2191808),(2191847),(2191857),(2191860),(2191870),(2191909),(2191919),(2191926),(2191936),(2191988),(2191998),(2224487),(2224497),(2224549),(2224559),(2224566),(2224576),(2224615),(2224625),(2224628),(2224638),(2224677),(2224687),(2224694),(2224704),(2224756),(2224766),(2257255),(2257265),(2257317),(2257327),(2257334),(2257344),(2257383),(2257393),(2257396),(2257406),(2257445),(2257455),(2257462),(2257472),(2257524),(2257534);
             
             
            INSERT INTO t1 SELECT id  
            FROM t1 
            NATURAL JOIN ( SELECT * FROM t1 WHERE id < 70 GROUP BY id )dt
            WHERE id IN ( SELECT id LIKE 'x' FROM t1 ) ;
             
            DROP TABLE t1;
            

            Version: '10.5.23-MariaDB-debug-log'  
            231028 13:29:08 [ERROR] mysqld got signal 11 ;
             
            Server version: 10.5.23-MariaDB-debug-log source revision: b5e43a1d3539c7254c298dc9f63a261281345d59
             
            sql/signal_handler.cc:241(handle_fatal_signal)[0x5615c8385d90]
            sigaction.c:0(__restore_rt)[0x7fc23cb66420]
            sql/opt_split.cc:1049(st_join_table::choose_best_splitting(unsigned int, unsigned long long, POSITION const*, unsigned long long*))[0x5615c81c7a1e]
            sql/sql_select.cc:7570(best_access_path(JOIN*, st_join_table*, unsigned long long, POSITION const*, unsigned int, bool, double, POSITION*, POSITION*))[0x5615c7c4ccce]
            sql/sql_select.cc:9886(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5615c7c59d6a]
            sql/sql_select.cc:9975(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5615c7c5aa5d]
            sql/sql_select.cc:9975(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5615c7c5aa5d]
            sql/sql_select.cc:9054(greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int, unsigned int))[0x5615c7c55ad5]
            sql/sql_select.cc:8616(choose_plan(JOIN*, unsigned long long))[0x5615c7c537a1]
            sql/sql_select.cc:5753(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x5615c7c3e42f]
            sql/sql_select.cc:2363(JOIN::optimize_inner())[0x5615c7c1b02c]
            sql/sql_select.cc:1721(JOIN::optimize())[0x5615c7c14505]
            sql/sql_select.cc:4845(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*))[0x5615c7c351cc]
            sql/sql_select.cc:450(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5615c7c06073]
            sql/sql_parse.cc:4790(mysql_execute_command(THD*))[0x5615c7b5f885]
            sql/sql_parse.cc:8120(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5615c7b76977]
            sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5615c7b4c3a5]
            sql/sql_parse.cc:1375(do_command(THD*))[0x5615c7b48d0d]
            sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x5615c7fa795b]
            sql/sql_connect.cc:1320(handle_one_connection)[0x5615c7fa72bf]
            perfschema/pfs.cc:2203(pfs_spawn_thread)[0x5615c8c33aba]
            nptl/pthread_create.c:478(start_thread)[0x7fc23cb5a609]
             
            Query (0x62b0000852a8): INSERT INTO t1 SELECT id  
            FROM t1 
            NATURAL JOIN ( SELECT * FROM t1 WHERE id < 70 GROUP BY id )dt
            WHERE id IN ( SELECT id LIKE 'x' FROM t1 )
            

            alice Alice Sherepa added a comment - - edited Test case from MDEV-32602 , crashes on 10.5-11.2, after e87440b commit CREATE TABLE t1 ( id int NOT NULL , PRIMARY KEY (id));   INSERT INTO t1 VALUES (-65632),(-65622),(-65570),(-65560),(-65553),(-65543),(-65504),(-65494),(-65491),(-65481),(-65442),(-65432),(-65425),(-65415),(-65363),(-65353),(-32864),(-32854),(-32802),(-32792),(-32785),(-32775),(-32736),(-32726),(-32723),(-32713),(-32674),(-32664),(-32657),(-32647),(-32595),(-32585),(-96),(-86),(-34),(-24),(-17),(-7),(32),(42),(45),(55),(94),(104),(111),(121),(173),(183),(2191719),(2191729),(2191781),(2191791),(2191798),(2191808),(2191847),(2191857),(2191860),(2191870),(2191909),(2191919),(2191926),(2191936),(2191988),(2191998),(2224487),(2224497),(2224549),(2224559),(2224566),(2224576),(2224615),(2224625),(2224628),(2224638),(2224677),(2224687),(2224694),(2224704),(2224756),(2224766),(2257255),(2257265),(2257317),(2257327),(2257334),(2257344),(2257383),(2257393),(2257396),(2257406),(2257445),(2257455),(2257462),(2257472),(2257524),(2257534);     INSERT INTO t1 SELECT id FROM t1 NATURAL JOIN ( SELECT * FROM t1 WHERE id < 70 GROUP BY id )dt WHERE id IN ( SELECT id LIKE 'x' FROM t1 ) ;   DROP TABLE t1; Version: '10.5.23-MariaDB-debug-log' 231028 13:29:08 [ERROR] mysqld got signal 11 ;   Server version: 10.5.23-MariaDB-debug-log source revision: b5e43a1d3539c7254c298dc9f63a261281345d59   sql/signal_handler.cc:241(handle_fatal_signal)[0x5615c8385d90] sigaction.c:0(__restore_rt)[0x7fc23cb66420] sql/opt_split.cc:1049(st_join_table::choose_best_splitting(unsigned int, unsigned long long, POSITION const*, unsigned long long*))[0x5615c81c7a1e] sql/sql_select.cc:7570(best_access_path(JOIN*, st_join_table*, unsigned long long, POSITION const*, unsigned int, bool, double, POSITION*, POSITION*))[0x5615c7c4ccce] sql/sql_select.cc:9886(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5615c7c59d6a] sql/sql_select.cc:9975(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5615c7c5aa5d] sql/sql_select.cc:9975(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned int))[0x5615c7c5aa5d] sql/sql_select.cc:9054(greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int, unsigned int))[0x5615c7c55ad5] sql/sql_select.cc:8616(choose_plan(JOIN*, unsigned long long))[0x5615c7c537a1] sql/sql_select.cc:5753(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x5615c7c3e42f] sql/sql_select.cc:2363(JOIN::optimize_inner())[0x5615c7c1b02c] sql/sql_select.cc:1721(JOIN::optimize())[0x5615c7c14505] sql/sql_select.cc:4845(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*))[0x5615c7c351cc] sql/sql_select.cc:450(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5615c7c06073] sql/sql_parse.cc:4790(mysql_execute_command(THD*))[0x5615c7b5f885] sql/sql_parse.cc:8120(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5615c7b76977] sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5615c7b4c3a5] sql/sql_parse.cc:1375(do_command(THD*))[0x5615c7b48d0d] sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x5615c7fa795b] sql/sql_connect.cc:1320(handle_one_connection)[0x5615c7fa72bf] perfschema/pfs.cc:2203(pfs_spawn_thread)[0x5615c8c33aba] nptl/pthread_create.c:478(start_thread)[0x7fc23cb5a609]   Query (0x62b0000852a8): INSERT INTO t1 SELECT id FROM t1 NATURAL JOIN ( SELECT * FROM t1 WHERE id < 70 GROUP BY id )dt WHERE id IN ( SELECT id LIKE 'x' FROM t1 )
            alice Alice Sherepa added a comment - - edited

            not reproducible on current 10.5 2c60d43d7df134c3875b3760a5d54a-11.3, after 6cfd2ba397 commit Merge branch '10.4' into 10.5
            but maybe not fixed - possible that query plan changed. Currently:

            MariaDB [test]> explain extended SELECT backups.id_product, quantity_backup, quantity_bl FROM ( SELECT id_product, SUM(quantity) as quantity_backup FROM`table1` ) backups INNER JOIN ( SELECT id_product FROM`table1` GROUP BY id_product ) normals ON (normals.id_product = backups.id_product) LEFT JOIN ( SELECT quantity as quantity_bl, id_product FROM table1 ) bl ON (backups.id_product = bl.id_product ) WHERE backups.id_product IN ( SELECT id_product FROM table2 );
            +------+--------------+-------------+-------+---------------+---------+---------+------------------------+------+----------+-----------------------------------------------------------+
            | id   | select_type  | table       | type  | possible_keys | key     | key_len | ref                    | rows | filtered | Extra                                                     |
            +------+--------------+-------------+-------+---------------+---------+---------+------------------------+------+----------+-----------------------------------------------------------+
            |    1 | PRIMARY      | <subquery5> | ALL   | distinct_key  | NULL    | NULL    | NULL                   | 1    |   100.00 |                                                           |
            |    1 | PRIMARY      | <derived2>  | ref   | key1          | key1    | 5       | test.table2.id_product | 2    |   100.00 | Using where                                               |
            |    1 | PRIMARY      | table1      | ALL   | PRIMARY       | NULL    | NULL    | NULL                   | 1    |   100.00 | Using where; Using join buffer (flat, BNL join)           |
            |    1 | PRIMARY      | <derived3>  | ref   | key0          | key0    | 4       | backups.id_product     | 2    |   100.00 |                                                           |
            |    5 | MATERIALIZED | table2      | index | PRIMARY       | PRIMARY | 8       | NULL                   | 1    |   100.00 | Using index                                               |
            |    3 | DERIVED      | table1      | range | PRIMARY       | PRIMARY | 4       | NULL                   | 2    |   100.00 | Using index for group-by; Using temporary; Using filesort |
            |    2 | DERIVED      | table1      | ALL   | NULL          | NULL    | NULL    | NULL                   | 1    |   100.00 |                                                           |
            +------+--------------+-------------+-------+---------------+---------+---------+------------------------+------+----------+-----------------------------------------------------------+
            7 rows in set, 1 warning (0,001 sec)
            

            I could not compare with the previous one - EXPLAIN crashes also.

            alice Alice Sherepa added a comment - - edited not reproducible on current 10.5 2c60d43d7df134c3875b3760a5d54a-11.3, after 6cfd2ba397 commit Merge branch '10.4' into 10.5 but maybe not fixed - possible that query plan changed. Currently: MariaDB [test]> explain extended SELECT backups.id_product, quantity_backup, quantity_bl FROM ( SELECT id_product, SUM(quantity) as quantity_backup FROM`table1` ) backups INNER JOIN ( SELECT id_product FROM`table1` GROUP BY id_product ) normals ON (normals.id_product = backups.id_product) LEFT JOIN ( SELECT quantity as quantity_bl, id_product FROM table1 ) bl ON (backups.id_product = bl.id_product ) WHERE backups.id_product IN ( SELECT id_product FROM table2 ); +------+--------------+-------------+-------+---------------+---------+---------+------------------------+------+----------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------+-------------+-------+---------------+---------+---------+------------------------+------+----------+-----------------------------------------------------------+ | 1 | PRIMARY | <subquery5> | ALL | distinct_key | NULL | NULL | NULL | 1 | 100.00 | | | 1 | PRIMARY | <derived2> | ref | key1 | key1 | 5 | test.table2.id_product | 2 | 100.00 | Using where | | 1 | PRIMARY | table1 | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (flat, BNL join) | | 1 | PRIMARY | <derived3> | ref | key0 | key0 | 4 | backups.id_product | 2 | 100.00 | | | 5 | MATERIALIZED | table2 | index | PRIMARY | PRIMARY | 8 | NULL | 1 | 100.00 | Using index | | 3 | DERIVED | table1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using index for group-by; Using temporary; Using filesort | | 2 | DERIVED | table1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | | +------+--------------+-------------+-------+---------------+---------+---------+------------------------+------+----------+-----------------------------------------------------------+ 7 rows in set, 1 warning (0,001 sec) I could not compare with the previous one - EXPLAIN crashes also.

            --source include/have_innodb.inc
            SET @@max_statement_time=0.0001;
            --error 0, 1969
            CREATE TABLE t (c1 DATETIME PRIMARY KEY,c2 VARCHAR(40)) ENGINE=InnoDB;
            --error 0, 1969
            UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2;
            --error 0, 1969
            UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2;
            --error 0, 1969
            DROP TABLE t;
            

            Sporadid. Run MTR with --repeat 10000. Example occurence:

            CS 10.5.27 e886c2ba02ac021c648f84aa8f910af4fb4fb4bb (Debug)

            $ ./mtr --repeat 10000 test
            ....
            main.test                                [ 390 pass ]      7
            SET @@max_statement_time=0.0001;
            CREATE TABLE t (c1 DATETIME PRIMARY KEY,c2 VARCHAR(40)) ENGINE=InnoDB;
            UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2;
            UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2;
            main.test                                [ 391 fail ]
                    Test ended at 2024-09-14 10:41:15
             
            CURRENT_TEST: main.test
            mysqltest: At line 8: query 'UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2' failed with wrong errno 2013: 'Lost connection to MySQL server during query', instead of 0...
             
             
            Server [mysqld.1 - pid: 2104200, winpid: 2104200, exit: 256] failed during test run
            Server log from this test:
            ----------SERVER LOG START-----------
            ----------SERVER LOG END-------------
             
             
             - found 'core' (0/5)
            

            Leads to:

            CS 10.5.27 e886c2ba02ac021c648f84aa8f910af4fb4fb4bb (Debug)

            Core was generated by `/test/MD090924-mariadb-10.5.27-linux-x86_64-dbg/bin/mariadbd --defaults-group-s'.
            Program terminated with signal SIGSEGV, Segmentation fault.
            #0  0x0000557ff3d54b21 in st_join_table::choose_best_splitting (this=this@entry=0x14d558b5c800, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x14d558b5cdf0, spl_pd_boundary=spl_pd_boundary@entry=0x14d5eabfce88)at /test/10.5_dbg/sql/opt_split.cc:1090
             
            [Current thread is 1 (LWP 2104708)]
            (gdb) bt
            #0  0x0000557ff3d54b21 in st_join_table::choose_best_splitting (this=this@entry=0x14d558b5c800, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x14d558b5cdf0, spl_pd_boundary=spl_pd_boundary@entry=0x14d5eabfce88)at /test/10.5_dbg/sql/opt_split.cc:1090
            #1  0x0000557ff3b896d1 in best_access_path (join=join@entry=0x14d558018d18, s=s@entry=0x14d558b5c800, remaining_tables=remaining_tables@entry=2, join_positions=0x14d558b5cdf0, idx=idx@entry=1, disable_jbuf=disable_jbuf@entry=false, record_count=record_count@entry=1, pos=0x14d558b5cf30, loose_scan_pos=0x14d5eabfd070)at /test/10.5_dbg/sql/sql_select.cc:7627
            #2  0x0000557ff3b89b71 in best_extension_by_limited_search (join=join@entry=0x14d558018d18, remaining_tables=remaining_tables@entry=2, idx=idx@entry=1, record_count=record_count@entry=1, read_time=1.2, search_depth=search_depth@entry=61, prune_level=prune_level@entry=1, use_cond_selectivity=use_cond_selectivity@entry=4)at /test/10.5_dbg/sql/sql_select.cc:9943
            #3  0x0000557ff3b8a4ab in best_extension_by_limited_search (join=join@entry=0x14d558018d18, remaining_tables=remaining_tables@entry=3, idx=idx@entry=0, record_count=record_count@entry=1, read_time=read_time@entry=0, search_depth=search_depth@entry=62, prune_level=prune_level@entry=1, use_cond_selectivity=use_cond_selectivity@entry=4)at /test/10.5_dbg/sql/sql_select.cc:10032
            #4  0x0000557ff3b8b396 in greedy_search (use_cond_selectivity=4, prune_level=<optimized out>, search_depth=62, remaining_tables=3, join=0x14d558018d18) at /test/10.5_dbg/sql/sql_select.cc:9111
            #5  choose_plan (join=join@entry=0x14d558018d18, join_tables=<optimized out>)at /test/10.5_dbg/sql/sql_select.cc:8673
            #6  0x0000557ff3bb52fb in make_join_statistics (join=join@entry=0x14d558018d18, tables_list=@0x14d558005998: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14d558018cf8, last = 0x14d558018d08, elements = 2}, <No data fields>}, keyuse_array=keyuse_array@entry=0x14d558019010)at /test/10.5_dbg/sql/sql_select.cc:5810
            #7  0x0000557ff3bbb7ad in JOIN::optimize_inner (this=this@entry=0x14d558018d18)at /test/10.5_dbg/sql/sql_select.cc:2416
            #8  0x0000557ff3bbbcfe in JOIN::optimize (this=this@entry=0x14d558018d18)at /test/10.5_dbg/sql/sql_select.cc:1765
            #9  0x0000557ff3bbbe01 in mysql_select (thd=thd@entry=0x14d558000d58, tables=tables@entry=0x14d558014320, fields=@0x14d5eabfdc90: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x557ff51d4530 <end_of_list>, last = 0x14d5eabfdc90, elements = 0}, <No data fields>}, conds=conds@entry=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x14d558018bf8, unit=0x14d558004f08, select_lex=0x14d558005740)at /test/10.5_dbg/sql/sql_select.cc:4902
            #10 0x0000557ff3c3075d in mysql_multi_update (thd=thd@entry=0x14d558000d58, table_list=0x14d558014320, fields=fields@entry=0x14d5580058a8, values=values@entry=0x14d558005e68, conds=0x0, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x14d558004f08, select_lex=0x14d558005740, result=0x14d5eabfded0)at /test/10.5_dbg/sql/sql_update.cc:2003
            #11 0x0000557ff3b4466c in mysql_execute_command (thd=thd@entry=0x14d558000d58)at /test/10.5_dbg/sql/sql_parse.cc:4575
            #12 0x0000557ff3b4acf1 in mysql_parse (thd=thd@entry=0x14d558000d58, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x14d5eabfe2b0, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false)at /test/10.5_dbg/sql/sql_parse.cc:8236
            #13 0x0000557ff3b4d39c in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14d558000d58, packet=packet@entry=0x14d55819c9c9 "UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2", packet_length=packet_length@entry=116, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false)at /test/10.5_dbg/sql/sql_class.h:238
            #14 0x0000557ff3b4fa8d in do_command (thd=0x14d558000d58)at /test/10.5_dbg/sql/sql_parse.cc:1376
            #15 0x0000557ff3c88d12 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x557ff66a9c88, put_in_cache=put_in_cache@entry=true)at /test/10.5_dbg/sql/sql_connect.cc:1417
            #16 0x0000557ff3c89042 in handle_one_connection (arg=arg@entry=0x557ff66a9c88)at /test/10.5_dbg/sql/sql_connect.cc:1319
            #17 0x0000557ff40cb95a in pfs_spawn_thread (arg=0x557ff660ed28)at /test/10.5_dbg/storage/perfschema/pfs.cc:2201
            #18 0x000014d5efe9ca94 in start_thread (arg=<optimized out>)at ./nptl/pthread_create.c:447
            #19 0x000014d5eff29c3c in clone3 ()at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:78
            

            And

            CS 11.7.0 5bbda9711131845ae6b4315a268b4d1710943a85 (Optimized)

            Core was generated by `/test/MD090924-mariadb-11.7.0-linux-x86_64-opt/bin/mariadbd --defaults-group-su'.
            Program terminated with signal SIGSEGV, Segmentation fault.
            #0  0x000055b96ce95bb7 in st_join_table::choose_best_splitting (this=this@entry=0x146e941e10c0, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x146e941e1750, spl_pd_boundary=spl_pd_boundary@entry=0x146ed4467020)at /test/server_opt/sql/opt_split.cc:1105
             
            [Current thread is 1 (LWP 3149311)]
            (gdb) bt
            #0  0x000055b96ce95bb7 in st_join_table::choose_best_splitting (this=this@entry=0x146e941e10c0, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x146e941e1750, spl_pd_boundary=spl_pd_boundary@entry=0x146ed4467020)at /test/server_opt/sql/opt_split.cc:1105
            #1  0x000055b96ccd14a8 in best_access_path (join=join@entry=0x146e94b311a0, s=s@entry=0x146e941e10c0, remaining_tables=remaining_tables@entry=2, join_positions=0x146e941e1750, idx=idx@entry=1, disable_jbuf=disable_jbuf@entry=false, record_count=record_count@entry=1, pos=0x146e941e2080, loose_scan_pos=0x146e941e21d0)at /test/server_opt/sql/sql_select.cc:8582
            #2  0x000055b96ccd5477 in get_costs_for_tables (join=join@entry=0x146e94b311a0, remaining_tables=remaining_tables@entry=2, idx=idx@entry=1, record_count=record_count@entry=1, pos=pos@entry=0x146e941e1720, store_position=0x146ed4467308, allowed_tables=0x146ed4467318, stop_on_eq_ref=false, trace_one_table=0x146ed4467320) at /test/server_opt/sql/sql_select.cc:11329
            #3  0x000055b96ccdef1e in best_extension_by_limited_search (join=join@entry=0x146e94b311a0, remaining_tables=remaining_tables@entry=2, idx=idx@entry=1, record_count=record_count@entry=1, read_time=<optimized out>, search_depth=search_depth@entry=61, use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x146ed44674b0)at /test/server_opt/sql/sql_select.cc:11586
            #4  0x000055b96ccdfa0b in best_extension_by_limited_search (join=join@entry=0x146e94b311a0, remaining_tables=<optimized out>, idx=idx@entry=0, record_count=record_count@entry=1, read_time=read_time@entry=0, search_depth=search_depth@entry=62, use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x146ed44675c0)at /test/server_opt/sql/sql_select.cc:11812
            #5  0x000055b96cce0799 in greedy_search (use_cond_selectivity=4, search_depth=62, remaining_tables=3, join=0x146e94b311a0)at /test/server_opt/sql/sql_select.cc:10557
            #6  choose_plan (join=join@entry=0x146e94b311a0, join_tables=<optimized out>, emb_sjm_nest=emb_sjm_nest@entry=0x0)at /test/server_opt/sql/sql_select.cc:10076
            #7  0x000055b96cd07b6f in make_join_statistics (join=join@entry=0x146e94b311a0, tables_list=@0x146e940059e8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x146e94016dc8, last = 0x146e94b31190, elements = 2}, <No data fields>}, keyuse_array=keyuse_array@entry=0x146e94b314f8)at /test/server_opt/sql/sql_select.cc:6312
            #8  0x000055b96cd0c700 in JOIN::optimize_inner (this=this@entry=0x146e94b311a0)at /test/server_opt/sql/sql_select.cc:2691
            #9  0x000055b96cd0ce4a in JOIN::optimize (this=this@entry=0x146e94b311a0)at /test/server_opt/sql/sql_select.cc:1989
            #10 0x000055b96cd0f07e in Sql_cmd_dml::execute_inner (this=this@entry=0x146e94016730, thd=thd@entry=0x146e94000c68)at /test/server_opt/sql/sql_select.cc:33844
            #11 0x000055b96cd7e679 in Sql_cmd_update::execute_inner (this=0x146e94016730, thd=0x146e94000c68) at /test/server_opt/sql/sql_update.cc:3109
            #12 0x000055b96ccc5621 in Sql_cmd_dml::execute (this=0x146e94016730, thd=0x146e94000c68) at /test/server_opt/sql/sql_select.cc:33793
            #13 0x000055b96cc88b05 in mysql_execute_command (thd=thd@entry=0x146e94000c68, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false)at /test/server_opt/sql/sql_parse.cc:4403
            #14 0x000055b96cc8d39e in mysql_parse (thd=0x146e94000c68, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /test/server_opt/sql/sql_parse.cc:7876
            #15 0x000055b96cc8fedd in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x146e94000c68, packet=packet@entry=0x146e94008869 "UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2", packet_length=packet_length@entry=116, blocking=blocking@entry=true)at /test/server_opt/sql/sql_parse.cc:1991
            #16 0x000055b96cc91b3d in do_command (thd=0x146e94000c68, blocking=blocking@entry=true) at /test/server_opt/sql/sql_parse.cc:1405
            #17 0x000055b96cdc8d3f in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55b970ec33a8, put_in_cache=put_in_cache@entry=true)at /test/server_opt/sql/sql_connect.cc:1448
            #18 0x000055b96cdc90b5 in handle_one_connection (arg=arg@entry=0x55b970ec33a8)at /test/server_opt/sql/sql_connect.cc:1350
            #19 0x000055b96d1907bf in pfs_spawn_thread (arg=0x55b970e67578)at /test/server_opt/storage/perfschema/pfs.cc:2198
            #20 0x0000146edac9ca94 in start_thread (arg=<optimized out>)at ./nptl/pthread_create.c:447
            #21 0x0000146edad29c3c in clone3 ()at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:78
            

            Reproducible on 10.5, 11.1, 11.2 and 11.7, on optimized and debug builds. All stacks seen thus far accross versions and build types:

            SIGSEGV|st_join_table::choose_best_splitting|best_access_path|best_extension_by_limited_search|best_extension_by_limited_search
            SIGSEGV|st_join_table::choose_best_splitting|best_access_path|get_costs_for_tables|best_extension_by_limited_search
            

            Issue generally tends to repeat around 500 MTR rounds, though it varies a bit, and I have seen a 5000 rounds MTR run on 11.7 opt not crash, only to crash after 200 rounds on a repeat run.

            Roel Roel Van de Paar added a comment - --source include/have_innodb.inc SET @@max_statement_time=0.0001; --error 0, 1969 CREATE TABLE t (c1 DATETIME PRIMARY KEY ,c2 VARCHAR (40)) ENGINE=InnoDB; --error 0, 1969 UPDATE t t INNER JOIN ( SELECT c1, MAX (c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2; --error 0, 1969 UPDATE t t INNER JOIN ( SELECT c1, MAX (c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2; --error 0, 1969 DROP TABLE t; Sporadid. Run MTR with --repeat 10000. Example occurence: CS 10.5.27 e886c2ba02ac021c648f84aa8f910af4fb4fb4bb (Debug) $ ./mtr --repeat 10000 test .... main.test [ 390 pass ] 7 SET @@max_statement_time=0.0001; CREATE TABLE t (c1 DATETIME PRIMARY KEY,c2 VARCHAR(40)) ENGINE=InnoDB; UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2; UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2; main.test [ 391 fail ] Test ended at 2024-09-14 10:41:15   CURRENT_TEST: main.test mysqltest: At line 8: query 'UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2' failed with wrong errno 2013: 'Lost connection to MySQL server during query', instead of 0...     Server [mysqld.1 - pid: 2104200, winpid: 2104200, exit: 256] failed during test run Server log from this test: ----------SERVER LOG START----------- ----------SERVER LOG END-------------     - found 'core' (0/5) Leads to: CS 10.5.27 e886c2ba02ac021c648f84aa8f910af4fb4fb4bb (Debug) Core was generated by `/test/MD090924-mariadb-10.5.27-linux-x86_64-dbg/bin/mariadbd --defaults-group-s'. Program terminated with signal SIGSEGV, Segmentation fault. #0 0x0000557ff3d54b21 in st_join_table::choose_best_splitting (this=this@entry=0x14d558b5c800, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x14d558b5cdf0, spl_pd_boundary=spl_pd_boundary@entry=0x14d5eabfce88)at /test/10.5_dbg/sql/opt_split.cc:1090   [Current thread is 1 (LWP 2104708)] (gdb) bt #0 0x0000557ff3d54b21 in st_join_table::choose_best_splitting (this=this@entry=0x14d558b5c800, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x14d558b5cdf0, spl_pd_boundary=spl_pd_boundary@entry=0x14d5eabfce88)at /test/10.5_dbg/sql/opt_split.cc:1090 #1 0x0000557ff3b896d1 in best_access_path (join=join@entry=0x14d558018d18, s=s@entry=0x14d558b5c800, remaining_tables=remaining_tables@entry=2, join_positions=0x14d558b5cdf0, idx=idx@entry=1, disable_jbuf=disable_jbuf@entry=false, record_count=record_count@entry=1, pos=0x14d558b5cf30, loose_scan_pos=0x14d5eabfd070)at /test/10.5_dbg/sql/sql_select.cc:7627 #2 0x0000557ff3b89b71 in best_extension_by_limited_search (join=join@entry=0x14d558018d18, remaining_tables=remaining_tables@entry=2, idx=idx@entry=1, record_count=record_count@entry=1, read_time=1.2, search_depth=search_depth@entry=61, prune_level=prune_level@entry=1, use_cond_selectivity=use_cond_selectivity@entry=4)at /test/10.5_dbg/sql/sql_select.cc:9943 #3 0x0000557ff3b8a4ab in best_extension_by_limited_search (join=join@entry=0x14d558018d18, remaining_tables=remaining_tables@entry=3, idx=idx@entry=0, record_count=record_count@entry=1, read_time=read_time@entry=0, search_depth=search_depth@entry=62, prune_level=prune_level@entry=1, use_cond_selectivity=use_cond_selectivity@entry=4)at /test/10.5_dbg/sql/sql_select.cc:10032 #4 0x0000557ff3b8b396 in greedy_search (use_cond_selectivity=4, prune_level=<optimized out>, search_depth=62, remaining_tables=3, join=0x14d558018d18) at /test/10.5_dbg/sql/sql_select.cc:9111 #5 choose_plan (join=join@entry=0x14d558018d18, join_tables=<optimized out>)at /test/10.5_dbg/sql/sql_select.cc:8673 #6 0x0000557ff3bb52fb in make_join_statistics (join=join@entry=0x14d558018d18, tables_list=@0x14d558005998: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x14d558018cf8, last = 0x14d558018d08, elements = 2}, <No data fields>}, keyuse_array=keyuse_array@entry=0x14d558019010)at /test/10.5_dbg/sql/sql_select.cc:5810 #7 0x0000557ff3bbb7ad in JOIN::optimize_inner (this=this@entry=0x14d558018d18)at /test/10.5_dbg/sql/sql_select.cc:2416 #8 0x0000557ff3bbbcfe in JOIN::optimize (this=this@entry=0x14d558018d18)at /test/10.5_dbg/sql/sql_select.cc:1765 #9 0x0000557ff3bbbe01 in mysql_select (thd=thd@entry=0x14d558000d58, tables=tables@entry=0x14d558014320, fields=@0x14d5eabfdc90: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x557ff51d4530 <end_of_list>, last = 0x14d5eabfdc90, elements = 0}, <No data fields>}, conds=conds@entry=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x14d558018bf8, unit=0x14d558004f08, select_lex=0x14d558005740)at /test/10.5_dbg/sql/sql_select.cc:4902 #10 0x0000557ff3c3075d in mysql_multi_update (thd=thd@entry=0x14d558000d58, table_list=0x14d558014320, fields=fields@entry=0x14d5580058a8, values=values@entry=0x14d558005e68, conds=0x0, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x14d558004f08, select_lex=0x14d558005740, result=0x14d5eabfded0)at /test/10.5_dbg/sql/sql_update.cc:2003 #11 0x0000557ff3b4466c in mysql_execute_command (thd=thd@entry=0x14d558000d58)at /test/10.5_dbg/sql/sql_parse.cc:4575 #12 0x0000557ff3b4acf1 in mysql_parse (thd=thd@entry=0x14d558000d58, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x14d5eabfe2b0, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false)at /test/10.5_dbg/sql/sql_parse.cc:8236 #13 0x0000557ff3b4d39c in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14d558000d58, packet=packet@entry=0x14d55819c9c9 "UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2", packet_length=packet_length@entry=116, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false)at /test/10.5_dbg/sql/sql_class.h:238 #14 0x0000557ff3b4fa8d in do_command (thd=0x14d558000d58)at /test/10.5_dbg/sql/sql_parse.cc:1376 #15 0x0000557ff3c88d12 in do_handle_one_connection (connect=<optimized out>, connect@entry=0x557ff66a9c88, put_in_cache=put_in_cache@entry=true)at /test/10.5_dbg/sql/sql_connect.cc:1417 #16 0x0000557ff3c89042 in handle_one_connection (arg=arg@entry=0x557ff66a9c88)at /test/10.5_dbg/sql/sql_connect.cc:1319 #17 0x0000557ff40cb95a in pfs_spawn_thread (arg=0x557ff660ed28)at /test/10.5_dbg/storage/perfschema/pfs.cc:2201 #18 0x000014d5efe9ca94 in start_thread (arg=<optimized out>)at ./nptl/pthread_create.c:447 #19 0x000014d5eff29c3c in clone3 ()at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:78 And CS 11.7.0 5bbda9711131845ae6b4315a268b4d1710943a85 (Optimized) Core was generated by `/test/MD090924-mariadb-11.7.0-linux-x86_64-opt/bin/mariadbd --defaults-group-su'. Program terminated with signal SIGSEGV, Segmentation fault. #0 0x000055b96ce95bb7 in st_join_table::choose_best_splitting (this=this@entry=0x146e941e10c0, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x146e941e1750, spl_pd_boundary=spl_pd_boundary@entry=0x146ed4467020)at /test/server_opt/sql/opt_split.cc:1105   [Current thread is 1 (LWP 3149311)] (gdb) bt #0 0x000055b96ce95bb7 in st_join_table::choose_best_splitting (this=this@entry=0x146e941e10c0, idx=idx@entry=1, remaining_tables=remaining_tables@entry=2, join_positions=join_positions@entry=0x146e941e1750, spl_pd_boundary=spl_pd_boundary@entry=0x146ed4467020)at /test/server_opt/sql/opt_split.cc:1105 #1 0x000055b96ccd14a8 in best_access_path (join=join@entry=0x146e94b311a0, s=s@entry=0x146e941e10c0, remaining_tables=remaining_tables@entry=2, join_positions=0x146e941e1750, idx=idx@entry=1, disable_jbuf=disable_jbuf@entry=false, record_count=record_count@entry=1, pos=0x146e941e2080, loose_scan_pos=0x146e941e21d0)at /test/server_opt/sql/sql_select.cc:8582 #2 0x000055b96ccd5477 in get_costs_for_tables (join=join@entry=0x146e94b311a0, remaining_tables=remaining_tables@entry=2, idx=idx@entry=1, record_count=record_count@entry=1, pos=pos@entry=0x146e941e1720, store_position=0x146ed4467308, allowed_tables=0x146ed4467318, stop_on_eq_ref=false, trace_one_table=0x146ed4467320) at /test/server_opt/sql/sql_select.cc:11329 #3 0x000055b96ccdef1e in best_extension_by_limited_search (join=join@entry=0x146e94b311a0, remaining_tables=remaining_tables@entry=2, idx=idx@entry=1, record_count=record_count@entry=1, read_time=<optimized out>, search_depth=search_depth@entry=61, use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x146ed44674b0)at /test/server_opt/sql/sql_select.cc:11586 #4 0x000055b96ccdfa0b in best_extension_by_limited_search (join=join@entry=0x146e94b311a0, remaining_tables=<optimized out>, idx=idx@entry=0, record_count=record_count@entry=1, read_time=read_time@entry=0, search_depth=search_depth@entry=62, use_cond_selectivity=use_cond_selectivity@entry=4, processed_eq_ref_tables=processed_eq_ref_tables@entry=0x146ed44675c0)at /test/server_opt/sql/sql_select.cc:11812 #5 0x000055b96cce0799 in greedy_search (use_cond_selectivity=4, search_depth=62, remaining_tables=3, join=0x146e94b311a0)at /test/server_opt/sql/sql_select.cc:10557 #6 choose_plan (join=join@entry=0x146e94b311a0, join_tables=<optimized out>, emb_sjm_nest=emb_sjm_nest@entry=0x0)at /test/server_opt/sql/sql_select.cc:10076 #7 0x000055b96cd07b6f in make_join_statistics (join=join@entry=0x146e94b311a0, tables_list=@0x146e940059e8: {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x146e94016dc8, last = 0x146e94b31190, elements = 2}, <No data fields>}, keyuse_array=keyuse_array@entry=0x146e94b314f8)at /test/server_opt/sql/sql_select.cc:6312 #8 0x000055b96cd0c700 in JOIN::optimize_inner (this=this@entry=0x146e94b311a0)at /test/server_opt/sql/sql_select.cc:2691 #9 0x000055b96cd0ce4a in JOIN::optimize (this=this@entry=0x146e94b311a0)at /test/server_opt/sql/sql_select.cc:1989 #10 0x000055b96cd0f07e in Sql_cmd_dml::execute_inner (this=this@entry=0x146e94016730, thd=thd@entry=0x146e94000c68)at /test/server_opt/sql/sql_select.cc:33844 #11 0x000055b96cd7e679 in Sql_cmd_update::execute_inner (this=0x146e94016730, thd=0x146e94000c68) at /test/server_opt/sql/sql_update.cc:3109 #12 0x000055b96ccc5621 in Sql_cmd_dml::execute (this=0x146e94016730, thd=0x146e94000c68) at /test/server_opt/sql/sql_select.cc:33793 #13 0x000055b96cc88b05 in mysql_execute_command (thd=thd@entry=0x146e94000c68, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false)at /test/server_opt/sql/sql_parse.cc:4403 #14 0x000055b96cc8d39e in mysql_parse (thd=0x146e94000c68, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /test/server_opt/sql/sql_parse.cc:7876 #15 0x000055b96cc8fedd in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x146e94000c68, packet=packet@entry=0x146e94008869 "UPDATE t t INNER JOIN (SELECT c1, MAX(c2) AS max_c2 FROM t GROUP BY c1) t_max ON t.c1=t_max.c1 SET t.c2=t_max.max_c2", packet_length=packet_length@entry=116, blocking=blocking@entry=true)at /test/server_opt/sql/sql_parse.cc:1991 #16 0x000055b96cc91b3d in do_command (thd=0x146e94000c68, blocking=blocking@entry=true) at /test/server_opt/sql/sql_parse.cc:1405 #17 0x000055b96cdc8d3f in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55b970ec33a8, put_in_cache=put_in_cache@entry=true)at /test/server_opt/sql/sql_connect.cc:1448 #18 0x000055b96cdc90b5 in handle_one_connection (arg=arg@entry=0x55b970ec33a8)at /test/server_opt/sql/sql_connect.cc:1350 #19 0x000055b96d1907bf in pfs_spawn_thread (arg=0x55b970e67578)at /test/server_opt/storage/perfschema/pfs.cc:2198 #20 0x0000146edac9ca94 in start_thread (arg=<optimized out>)at ./nptl/pthread_create.c:447 #21 0x0000146edad29c3c in clone3 ()at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:78 Reproducible on 10.5, 11.1, 11.2 and 11.7, on optimized and debug builds. All stacks seen thus far accross versions and build types: SIGSEGV|st_join_table::choose_best_splitting|best_access_path|best_extension_by_limited_search|best_extension_by_limited_search SIGSEGV|st_join_table::choose_best_splitting|best_access_path|get_costs_for_tables|best_extension_by_limited_search Issue generally tends to repeat around 500 MTR rounds, though it varies a bit, and I have seen a 5000 rounds MTR run on 11.7 opt not crash, only to crash after 200 rounds on a repeat run.

            No UB/ASAN issues observed on 11.2 dbg, even with long runs.

            Roel Roel Van de Paar added a comment - No UB/ASAN issues observed on 11.2 dbg, even with long runs.

            People

              psergei Sergei Petrunia
              stephane@skysql.com VAROQUI Stephane
              Votes:
              3 Vote for this issue
              Watchers:
              12 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.