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

Segfault on select query using index for group-by and filesort

Details

    Description

      When I execute:

      SELECT T2.`Name` 
      FROM (
      	SELECT `Country` FROM `religion` GROUP BY `Country` ORDER BY COUNT(DISTINCT `Name`) DESC LIMIT 1 
      ) AS T1 
      INNER JOIN `country` AS T2
      ON T1.`Country`=T2.`Code`
      

      MariaDB crashes. The log is attached. A dump of the data is attached.

      Expected behaviour: The query returns country name with the highest count of distinct religions.

      Reproducibility: On macOS 12.6, case insensitive file system, with a fresh installation of MariaDB 10.7.3 from Homebrew I only get a misleading error message (the db remains responsive to other queries):

      2003 - Can't connect to MySQL server on '127.0.0.1' (61 "Connection refused")
      

      Severity: It causes a crash on a select query → marking as Critical.

      What I checked:

      1. I renamed `Country` column name to not clash with `country` table. It didn't have any impact.
      2. When I change `LIMIT 1` to `LIMIT 2`, the query works.

      Attachments

        1. log.txt
          5 kB
        2. Mondial.sql
          48 kB

        Issue Links

          Activity

            alice Alice Sherepa added a comment - - edited

            Thank you very much for the report!
            I repeated as described on 10.3-10.10, with InnoDB.

            --source include/have_innodb.inc 
             
            CREATE TABLE t1 ( id varchar(35), a varchar(4)) engine=innodb;
            INSERT INTO t1 VALUES
            ('Afghanistan','AFG'),('Albania','AXA'),('Algeria','AUS'),('American Samoa','AMSA'),('Andorra','AND');
             
            CREATE TABLE t2 ( b varchar(4), id varchar(50), PRIMARY KEY (id,b), KEY (b) ) engine=innodb;
            INSERT INTO t2 VALUES
            ('BERM','African Methodist Episcopal'),('AUS','Anglican'),('AXA','Anglican'),('BERM','Anglican'),('BS','Anglican');
             
            SELECT t1.id 
            FROM (SELECT b FROM t2 GROUP BY b ORDER BY COUNT(DISTINCT id) LIMIT 1 ) dt 
            JOIN t1  ON dt.b=t1.a;
            

            10.3-10.11:

            10.3 4e9206736c403206915c

            221202 10:49:11 [ERROR] mysqld got signal 11 ;
             
            Server version: 10.3.38-MariaDB-debug-log
            sql/signal_handler.cc:231(handle_fatal_signal)[0x55908b22d842]
            sigaction.c:0(__restore_rt)[0x7f53a1743420]
            sql/sql_select.h:529(st_join_table::is_using_agg_loose_index_scan())[0x55908ac342d7]
            sql/sql_select.cc:3521(JOIN::create_postjoin_aggr_table(st_join_table*, List<Item>*, st_order*, bool, bool, bool))[0x55908ab73637]
            sql/sql_select.cc:3085(JOIN::make_aggr_tables_info())[0x55908ab6e655]
            sql/sql_select.cc:2720(JOIN::optimize_stage2())[0x55908ab69fa6]
            sql/sql_select.cc:1535(JOIN::optimize())[0x55908ab5df78]
            sql/sql_derived.cc:962(mysql_derived_optimize(THD*, LEX*, TABLE_LIST*))[0x55908a9ef628]
            sql/sql_derived.cc:193(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x55908a9ea8cd]
            sql/table.cc:8563(TABLE_LIST::handle_derived(LEX*, unsigned int))[0x55908adcfc3b]
            sql/sql_lex.h:4032(LEX::handle_list_of_derived(TABLE_LIST*, unsigned int))[0x55908aa2ff04]
            sql/sql_lex.cc:4184(st_select_lex::handle_derived(LEX*, unsigned int))[0x55908aa50e73]
            sql/sql_select.cc:2050(JOIN::optimize_stage2())[0x55908ab632de]
            sql/sql_select.cc:2026(JOIN::optimize_inner())[0x55908ab62dfa]
            sql/sql_select.cc:1542(JOIN::optimize())[0x55908ab5e02c]
            sql/sql_select.cc:4368(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*))[0x55908ab7b707]
            sql/sql_select.cc:372(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55908ab519c5]
            sql/sql_parse.cc:6340(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55908aac2633]
            sql/sql_parse.cc:3871(mysql_execute_command(THD*))[0x55908aab0668]
            sql/sql_parse.cc:7855(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55908aacc314]
            sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55908aaa3291]
            sql/sql_parse.cc:1398(do_command(THD*))[0x55908aa9fdb0]
            sql/sql_connect.cc:1404(do_handle_one_connection(CONNECT*))[0x55908ae75f7d]
            sql/sql_connect.cc:1310(handle_one_connection)[0x55908ae75837]
            perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55908c4a82ef]
            nptl/pthread_create.c:478(start_thread)[0x7f53a1737609]
             
            Query (0x62b000000290): SELECT t1.id 
            FROM (SELECT b FROM t2 GROUP BY b ORDER BY COUNT(DISTINCT id) LIMIT 1 ) dt 
            JOIN t1  ON dt.b=t1.a
            

            bb-11.0 3135acf0bba521cd032c

            mariadbd: /10.11/sql/sql_select.cc:7793: double matching_candidates_in_table(JOIN_TAB*, bool, uint): Assertion `table_records == 0 || sel <= s->table->opt_range_condition_rows / table_records' failed.
            221202 10:58:11 [ERROR] mysqld got signal 6 ;
             
            Server version: 11.0.1-MariaDB-debug-log
             
            ??:0(__assert_fail)[0x7f0e5ac35fd6]
            sql/sql_select.cc:7796(matching_candidates_in_table(st_join_table*, bool, unsigned int))[0x560d1e0eb538]
            sql/sql_select.cc:8964(best_access_path(JOIN*, st_join_table*, unsigned long long, POSITION const*, unsigned int, bool, double, POSITION*, POSITION*))[0x560d1e0f2a76]
            sql/sql_select.cc:10664(get_costs_for_tables(JOIN*, unsigned long long, unsigned int, double, Json_writer_object*, st_join_table**, SORT_POSITION**, unsigned long long*, bool))[0x560d1e0fc372]
            sql/sql_select.cc:10921(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned long long*))[0x560d1e0fd6d4]
            sql/sql_select.cc:9897(greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int))[0x560d1e0f8001]
            sql/sql_select.cc:9413(choose_plan(JOIN*, unsigned long long, TABLE_LIST*))[0x560d1e0f596b]
            sql/sql_select.cc:6041(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x560d1e0dd116]
            sql/sql_select.cc:2556(JOIN::optimize_inner())[0x560d1e0b8e89]
            sql/sql_select.cc:1890(JOIN::optimize())[0x560d1e0b1f66]
            sql/sql_select.cc:5100(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*))[0x560d1e0d3840]
            sql/sql_select.cc:608(handle_select(THD*, LEX*, select_result*, unsigned long long))[0x560d1e0a39bd]
            sql/sql_parse.cc:6263(execute_sqlcom_select(THD*, TABLE_LIST*))[0x560d1dfc7654]
            sql/sql_parse.cc:3947(mysql_execute_command(THD*, bool))[0x560d1dfb5f4d]
            sql/sql_parse.cc:7998(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x560d1dfd23e4]
            sql/sql_parse.cc:1896(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x560d1dfa86aa]
            sql/sql_parse.cc:1407(do_command(THD*, bool))[0x560d1dfa53e6]
            sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x560d1e46cee4]
            sql/sql_connect.cc:1320(handle_one_connection)[0x560d1e46c841]
            perfschema/pfs.cc:2203(pfs_spawn_thread)[0x560d1f0a2d36]
            nptl/pthread_create.c:478(start_thread)[0x7f0e5b150609]
             
            Query (0x6290001092a8): SELECT t1.id 
            FROM (SELECT b FROM t2 GROUP BY b ORDER BY COUNT(DISTINCT id) LIMIT 1 ) dt 
            JOIN t1  ON dt.b=t1.a
            

            alice Alice Sherepa added a comment - - edited Thank you very much for the report! I repeated as described on 10.3-10.10, with InnoDB. --source include/have_innodb.inc   CREATE TABLE t1 ( id varchar (35), a varchar (4)) engine=innodb; INSERT INTO t1 VALUES ( 'Afghanistan' , 'AFG' ),( 'Albania' , 'AXA' ),( 'Algeria' , 'AUS' ),( 'American Samoa' , 'AMSA' ),( 'Andorra' , 'AND' );   CREATE TABLE t2 ( b varchar (4), id varchar (50), PRIMARY KEY (id,b), KEY (b) ) engine=innodb; INSERT INTO t2 VALUES ( 'BERM' , 'African Methodist Episcopal' ),( 'AUS' , 'Anglican' ),( 'AXA' , 'Anglican' ),( 'BERM' , 'Anglican' ),( 'BS' , 'Anglican' );   SELECT t1.id FROM ( SELECT b FROM t2 GROUP BY b ORDER BY COUNT ( DISTINCT id) LIMIT 1 ) dt JOIN t1 ON dt.b=t1.a; 10.3-10.11: 10.3 4e9206736c403206915c 221202 10:49:11 [ERROR] mysqld got signal 11 ;   Server version: 10.3.38-MariaDB-debug-log sql/signal_handler.cc:231(handle_fatal_signal)[0x55908b22d842] sigaction.c:0(__restore_rt)[0x7f53a1743420] sql/sql_select.h:529(st_join_table::is_using_agg_loose_index_scan())[0x55908ac342d7] sql/sql_select.cc:3521(JOIN::create_postjoin_aggr_table(st_join_table*, List<Item>*, st_order*, bool, bool, bool))[0x55908ab73637] sql/sql_select.cc:3085(JOIN::make_aggr_tables_info())[0x55908ab6e655] sql/sql_select.cc:2720(JOIN::optimize_stage2())[0x55908ab69fa6] sql/sql_select.cc:1535(JOIN::optimize())[0x55908ab5df78] sql/sql_derived.cc:962(mysql_derived_optimize(THD*, LEX*, TABLE_LIST*))[0x55908a9ef628] sql/sql_derived.cc:193(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x55908a9ea8cd] sql/table.cc:8563(TABLE_LIST::handle_derived(LEX*, unsigned int))[0x55908adcfc3b] sql/sql_lex.h:4032(LEX::handle_list_of_derived(TABLE_LIST*, unsigned int))[0x55908aa2ff04] sql/sql_lex.cc:4184(st_select_lex::handle_derived(LEX*, unsigned int))[0x55908aa50e73] sql/sql_select.cc:2050(JOIN::optimize_stage2())[0x55908ab632de] sql/sql_select.cc:2026(JOIN::optimize_inner())[0x55908ab62dfa] sql/sql_select.cc:1542(JOIN::optimize())[0x55908ab5e02c] sql/sql_select.cc:4368(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*))[0x55908ab7b707] sql/sql_select.cc:372(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55908ab519c5] sql/sql_parse.cc:6340(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55908aac2633] sql/sql_parse.cc:3871(mysql_execute_command(THD*))[0x55908aab0668] sql/sql_parse.cc:7855(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55908aacc314] sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55908aaa3291] sql/sql_parse.cc:1398(do_command(THD*))[0x55908aa9fdb0] sql/sql_connect.cc:1404(do_handle_one_connection(CONNECT*))[0x55908ae75f7d] sql/sql_connect.cc:1310(handle_one_connection)[0x55908ae75837] perfschema/pfs.cc:1871(pfs_spawn_thread)[0x55908c4a82ef] nptl/pthread_create.c:478(start_thread)[0x7f53a1737609]   Query (0x62b000000290): SELECT t1.id FROM (SELECT b FROM t2 GROUP BY b ORDER BY COUNT(DISTINCT id) LIMIT 1 ) dt JOIN t1 ON dt.b=t1.a bb-11.0 3135acf0bba521cd032c mariadbd: /10.11/sql/sql_select.cc:7793: double matching_candidates_in_table(JOIN_TAB*, bool, uint): Assertion `table_records == 0 || sel <= s->table->opt_range_condition_rows / table_records' failed. 221202 10:58:11 [ERROR] mysqld got signal 6 ;   Server version: 11.0.1-MariaDB-debug-log   ??:0(__assert_fail)[0x7f0e5ac35fd6] sql/sql_select.cc:7796(matching_candidates_in_table(st_join_table*, bool, unsigned int))[0x560d1e0eb538] sql/sql_select.cc:8964(best_access_path(JOIN*, st_join_table*, unsigned long long, POSITION const*, unsigned int, bool, double, POSITION*, POSITION*))[0x560d1e0f2a76] sql/sql_select.cc:10664(get_costs_for_tables(JOIN*, unsigned long long, unsigned int, double, Json_writer_object*, st_join_table**, SORT_POSITION**, unsigned long long*, bool))[0x560d1e0fc372] sql/sql_select.cc:10921(best_extension_by_limited_search(JOIN*, unsigned long long, unsigned int, double, double, unsigned int, unsigned int, unsigned long long*))[0x560d1e0fd6d4] sql/sql_select.cc:9897(greedy_search(JOIN*, unsigned long long, unsigned int, unsigned int))[0x560d1e0f8001] sql/sql_select.cc:9413(choose_plan(JOIN*, unsigned long long, TABLE_LIST*))[0x560d1e0f596b] sql/sql_select.cc:6041(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x560d1e0dd116] sql/sql_select.cc:2556(JOIN::optimize_inner())[0x560d1e0b8e89] sql/sql_select.cc:1890(JOIN::optimize())[0x560d1e0b1f66] sql/sql_select.cc:5100(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*))[0x560d1e0d3840] sql/sql_select.cc:608(handle_select(THD*, LEX*, select_result*, unsigned long long))[0x560d1e0a39bd] sql/sql_parse.cc:6263(execute_sqlcom_select(THD*, TABLE_LIST*))[0x560d1dfc7654] sql/sql_parse.cc:3947(mysql_execute_command(THD*, bool))[0x560d1dfb5f4d] sql/sql_parse.cc:7998(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x560d1dfd23e4] sql/sql_parse.cc:1896(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x560d1dfa86aa] sql/sql_parse.cc:1407(do_command(THD*, bool))[0x560d1dfa53e6] sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x560d1e46cee4] sql/sql_connect.cc:1320(handle_one_connection)[0x560d1e46c841] perfschema/pfs.cc:2203(pfs_spawn_thread)[0x560d1f0a2d36] nptl/pthread_create.c:478(start_thread)[0x7f0e5b150609]   Query (0x6290001092a8): SELECT t1.id FROM (SELECT b FROM t2 GROUP BY b ORDER BY COUNT(DISTINCT id) LIMIT 1 ) dt JOIN t1 ON dt.b=t1.a
            oleg.smirnov Oleg Smirnov added a comment -

            Valgrind complains on particular value best_key->key==1. Adding the following initialization before the condition eliminates the Valgrind error:

            sql_select.cc

              if (s->quick && best_key && s->quick->index == best_key->key &&  best_key->key == 1)
              {
                  s->table->quick_key_parts[best_key->key]= 0;
              }
              Json_writer_object trace_access_scan(thd);
              if ((records >= s->found_records || best > s->read_time) &&            // (1)
                  !(best_key && best_key->key == MAX_KEY) &&                         // (2)
                  !(s->quick && best_key && s->quick->index == best_key->key &&      // (2)
                    best_max_key_part >= s->table->quick_key_parts[best_key->key]) &&// (2)
                  !((s->table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) &&   // (3)
                    ! s->table->covering_keys.is_clear_all() && best_key && !s->quick) &&// (3)
                  !(s->table->force_index && best_key && !s->quick) &&               // (4)
                  !(best_key && s->table->pos_in_table_list->jtbm_subselect))        // (5)
            

            Setting data breakpoint at memory pointed by s->table->quick_key_parts I can see it is bzero'ed along with the whole TABLE structure here:

            table.cc

            enum open_frm_error open_table_from_share(THD *thd, TABLE_SHARE *share,
                                   const LEX_CSTRING *alias, uint db_stat, uint prgflag,
                                   uint ha_open_flags, TABLE *outparam,
                                   bool is_create_table, List<String> *partitions_to_open)
            {
            ...
              bzero((char*) outparam, sizeof(*outparam));
            ...
            }
            

            And no other changes are made to quick_key_parts after that (at least reported by gdb).

            Test case to reproduce:

            --source include/have_innodb.inc
            CREATE TABLE t1 (a varchar(35), b varchar(4)) ENGINE=InnoDB;
            INSERT INTO t1 VALUES
            ('Albania','AXA'),('Australia','AUS'),('American Samoa','AMSA'),('Bahamas','BS');
             
            CREATE TABLE t2 (a varchar(4), b varchar(50), PRIMARY KEY (b,a), KEY (a)) ENGINE=InnoDB;
            INSERT INTO t2 VALUES
            ('BERM','African Methodist Episcopal'),('AUS','Anglican'),('BERM','Anglican'),('BS','Anglican'),('BS','Baptist'),('BS','Methodist');
             
            let query=
            SELECT t1.a
            FROM (SELECT a FROM t2 GROUP BY a ORDER BY COUNT(DISTINCT b) LIMIT 1) dt
            JOIN t1 ON dt.a=t1.b;
            eval $query;
             
            DROP TABLES t1, t2;
            

            Must be run with

            --valgrind

            option for MariaDB bb-10.3-MDEV-30143 built with -DWITH_VALGRIND=ON.

            oleg.smirnov Oleg Smirnov added a comment - Valgrind complains on particular value best_key->key==1. Adding the following initialization before the condition eliminates the Valgrind error: sql_select.cc if (s->quick && best_key && s->quick->index == best_key->key && best_key->key == 1) { s->table->quick_key_parts[best_key->key]= 0; } Json_writer_object trace_access_scan(thd); if ((records >= s->found_records || best > s->read_time) && // (1) !(best_key && best_key->key == MAX_KEY) && // (2) !(s->quick && best_key && s->quick->index == best_key->key && // (2) best_max_key_part >= s->table->quick_key_parts[best_key->key]) &&// (2) !((s->table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) && // (3) ! s->table->covering_keys.is_clear_all() && best_key && !s->quick) &&// (3) !(s->table->force_index && best_key && !s->quick) && // (4) !(best_key && s->table->pos_in_table_list->jtbm_subselect)) // (5) Setting data breakpoint at memory pointed by s->table->quick_key_parts I can see it is bzero'ed along with the whole TABLE structure here: table.cc enum open_frm_error open_table_from_share(THD *thd, TABLE_SHARE *share, const LEX_CSTRING *alias, uint db_stat, uint prgflag, uint ha_open_flags, TABLE *outparam, bool is_create_table, List<String> *partitions_to_open) { ... bzero((char*) outparam, sizeof(*outparam)); ... } And no other changes are made to quick_key_parts after that (at least reported by gdb). Test case to reproduce: --source include/have_innodb.inc CREATE TABLE t1 (a varchar(35), b varchar(4)) ENGINE=InnoDB; INSERT INTO t1 VALUES ('Albania','AXA'),('Australia','AUS'),('American Samoa','AMSA'),('Bahamas','BS');   CREATE TABLE t2 (a varchar(4), b varchar(50), PRIMARY KEY (b,a), KEY (a)) ENGINE=InnoDB; INSERT INTO t2 VALUES ('BERM','African Methodist Episcopal'),('AUS','Anglican'),('BERM','Anglican'),('BS','Anglican'),('BS','Baptist'),('BS','Methodist');   let query= SELECT t1.a FROM (SELECT a FROM t2 GROUP BY a ORDER BY COUNT(DISTINCT b) LIMIT 1) dt JOIN t1 ON dt.a=t1.b; eval $query;   DROP TABLES t1, t2; Must be run with --valgrind option for MariaDB bb-10.3- MDEV-30143 built with -DWITH_VALGRIND=ON.

            Ok to push.
            Please add a note in the commit comment describing the change in best_access_path().

            psergei Sergei Petrunia added a comment - Ok to push. Please add a note in the commit comment describing the change in best_access_path().
            oleg.smirnov Oleg Smirnov added a comment -

            Pushed to 10.4.

            oleg.smirnov Oleg Smirnov added a comment - Pushed to 10.4.

            People

              oleg.smirnov Oleg Smirnov
              yzan Jan Motl
              Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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