Details

    Description

      I've just upgraded from 10.4 to 10.5 and now some queries results in a server crash.
      The issue can be reproduced by creating this simple table and make a query on it. It doesn't matter if the table is empty or not.

      CREATE TABLE `test` (
      	`a` BIGINT(20) NOT NULL,
      	`b` BIGINT(20) NOT NULL,
      	`c` BIGINT(20) NOT NULL,
      	PRIMARY KEY (`a`, `b`, `c`) USING BTREE,
      	INDEX `KEY_2` (`b`) USING BTREE
      ) ENGINE=InnoDB;
      

      Then running this query produce the crash:

      SELECT count(*) FROM test
      WHERE c = 1
      AND a IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,
                31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,
      			 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90)
      AND b IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,
                31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,
      			 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90);
      

      I've found out, that it is important how many values are in the lists. Less number seems to work.

      Attachments

        Issue Links

          Activity

            alice Alice Sherepa added a comment - - edited

            Thanks a lot! I repeated as described on 10.5

            10.5 16388f393c63b3e2158db1e

            Version: '10.5.9-MariaDB-debug-log' 
            210226 10:40:13 [ERROR] mysqld got signal 11 ;
             
            Server version: 10.5.9-MariaDB-debug-log
             
            sql/signal_handler.cc:211(handle_fatal_signal)[0x560bdad5c5d8]
            sigaction.c:0(__restore_rt)[0x7f0ceb7a13c0]
            sql/opt_range.cc:9816(and_all_keys(RANGE_OPT_PARAM*, SEL_ARG*, SEL_ARG*, unsigned int))[0x560bdb18a75d]
            sql/opt_range.cc:9875(key_and(RANGE_OPT_PARAM*, SEL_ARG*, SEL_ARG*, unsigned int))[0x560bdb18aca0]
            sql/opt_range.cc:10065(key_and_with_limit(RANGE_OPT_PARAM*, unsigned int, SEL_ARG*, SEL_ARG*, unsigned int))[0x560bdb18bf4c]
            sql/opt_range.cc:9151(and_range_trees(RANGE_OPT_PARAM*, SEL_TREE*, SEL_TREE*, SEL_TREE*))[0x560bdb186cb1]
            sql/opt_range.cc:9263(tree_and(RANGE_OPT_PARAM*, SEL_TREE*, SEL_TREE*))[0x560bdb187611]
            sql/opt_range.cc:8315(Item_cond_and::get_mm_tree(RANGE_OPT_PARAM*, Item**))[0x560bdb17e57b]
            sql/opt_range.cc:2880(SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool, bool, bool, bool))[0x560bdb15a7ff]
            sql/sql_select.cc:4763(get_quick_record_count(THD*, SQL_SELECT*, TABLE*, Bitmap<64u> const*, unsigned long long))[0x560bda639220]
            sql/sql_select.cc:5494(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x560bda640177]
            sql/sql_select.cc:2255(JOIN::optimize_inner())[0x560bda61ec4e]
            sql/sql_select.cc:1627(JOIN::optimize())[0x560bda61812e]
            sql/sql_select.cc:4705(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*))[0x560bda638925]
            sql/sql_select.cc:417(handle_select(THD*, LEX*, select_result*, unsigned long))[0x560bda60a358]
            sql/sql_parse.cc:6282(execute_sqlcom_select(THD*, TABLE_LIST*))[0x560bda573ac6]
            sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x560bda5629c1]
            sql/sql_parse.cc:8063(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x560bda57edc0]
            sql/sql_parse.cc:1892(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x560bda554fb9]
            sql/sql_parse.cc:1370(do_command(THD*))[0x560bda5518e2]
            sql/sql_connect.cc:1410(do_handle_one_connection(CONNECT*, bool))[0x560bda994778]
            sql/sql_connect.cc:1314(handle_one_connection)[0x560bda9940dc]
            perfschema/pfs.cc:2203(pfs_spawn_thread)[0x560bdb69fdf9]
            nptl/pthread_create.c:478(start_thread)[0x7f0ceb795609]
            x86_64/clone.S:97(__GI___clone)[0x7f0ceb369293]
             
            Query (0x62b0000a12a8): SELECT count(*) FROM test
            WHERE c = 1
            AND a IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,
            31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,
            58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90)
            AND b IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,
            31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,
            58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90)
            
            

            currently it is fixed in 10.5 (by patch for MDEV-24953),

            workaround, that is mentioned there (to set optimizer_max_sel_arg_weight to a value lower than SEL_ARG::MAX_SEL_ARGS (which is 16000)) helps also in this case:

            MariaDB [test]> set optimizer_max_sel_arg_weight=100;
            Query OK, 0 rows affected (0.000 sec)
             
            MariaDB [test]> SELECT count(*) FROM test WHERE c = 1 AND a IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90) AND b IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90);
            +----------+
            | count(*) |
            +----------+
            |        0 |
            +----------+
            1 row in set (0.001 sec)
             
            MariaDB [test]> set optimizer_max_sel_arg_weight=16000;
            Query OK, 0 rows affected (0.000 sec)
             
            MariaDB [test]> SELECT count(*) FROM test WHERE c = 1 AND a IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90) AND b IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90);
            ERROR 2013 (HY000): Lost connection to MySQL server during query
            

            alice Alice Sherepa added a comment - - edited Thanks a lot! I repeated as described on 10.5 10.5 16388f393c63b3e2158db1e Version: '10.5.9-MariaDB-debug-log' 210226 10:40:13 [ERROR] mysqld got signal 11 ;   Server version: 10.5.9-MariaDB-debug-log   sql/signal_handler.cc:211(handle_fatal_signal)[0x560bdad5c5d8] sigaction.c:0(__restore_rt)[0x7f0ceb7a13c0] sql/opt_range.cc:9816(and_all_keys(RANGE_OPT_PARAM*, SEL_ARG*, SEL_ARG*, unsigned int))[0x560bdb18a75d] sql/opt_range.cc:9875(key_and(RANGE_OPT_PARAM*, SEL_ARG*, SEL_ARG*, unsigned int))[0x560bdb18aca0] sql/opt_range.cc:10065(key_and_with_limit(RANGE_OPT_PARAM*, unsigned int, SEL_ARG*, SEL_ARG*, unsigned int))[0x560bdb18bf4c] sql/opt_range.cc:9151(and_range_trees(RANGE_OPT_PARAM*, SEL_TREE*, SEL_TREE*, SEL_TREE*))[0x560bdb186cb1] sql/opt_range.cc:9263(tree_and(RANGE_OPT_PARAM*, SEL_TREE*, SEL_TREE*))[0x560bdb187611] sql/opt_range.cc:8315(Item_cond_and::get_mm_tree(RANGE_OPT_PARAM*, Item**))[0x560bdb17e57b] sql/opt_range.cc:2880(SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool, bool, bool, bool))[0x560bdb15a7ff] sql/sql_select.cc:4763(get_quick_record_count(THD*, SQL_SELECT*, TABLE*, Bitmap<64u> const*, unsigned long long))[0x560bda639220] sql/sql_select.cc:5494(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x560bda640177] sql/sql_select.cc:2255(JOIN::optimize_inner())[0x560bda61ec4e] sql/sql_select.cc:1627(JOIN::optimize())[0x560bda61812e] sql/sql_select.cc:4705(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*))[0x560bda638925] sql/sql_select.cc:417(handle_select(THD*, LEX*, select_result*, unsigned long))[0x560bda60a358] sql/sql_parse.cc:6282(execute_sqlcom_select(THD*, TABLE_LIST*))[0x560bda573ac6] sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x560bda5629c1] sql/sql_parse.cc:8063(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x560bda57edc0] sql/sql_parse.cc:1892(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x560bda554fb9] sql/sql_parse.cc:1370(do_command(THD*))[0x560bda5518e2] sql/sql_connect.cc:1410(do_handle_one_connection(CONNECT*, bool))[0x560bda994778] sql/sql_connect.cc:1314(handle_one_connection)[0x560bda9940dc] perfschema/pfs.cc:2203(pfs_spawn_thread)[0x560bdb69fdf9] nptl/pthread_create.c:478(start_thread)[0x7f0ceb795609] x86_64/clone.S:97(__GI___clone)[0x7f0ceb369293]   Query (0x62b0000a12a8): SELECT count(*) FROM test WHERE c = 1 AND a IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90) AND b IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90) currently it is fixed in 10.5 (by patch for MDEV-24953 ), workaround, that is mentioned there (to set optimizer_max_sel_arg_weight to a value lower than SEL_ARG::MAX_SEL_ARGS (which is 16000)) helps also in this case: MariaDB [test]> set optimizer_max_sel_arg_weight=100; Query OK, 0 rows affected (0.000 sec)   MariaDB [test]> SELECT count(*) FROM test WHERE c = 1 AND a IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90) AND b IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90); +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.001 sec)   MariaDB [test]> set optimizer_max_sel_arg_weight=16000; Query OK, 0 rows affected (0.000 sec)   MariaDB [test]> SELECT count(*) FROM test WHERE c = 1 AND a IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90) AND b IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57, 58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90); ERROR 2013 (HY000): Lost connection to MySQL server during query

            Closing as Duplicate of MDEV-24953

            psergei Sergei Petrunia added a comment - Closing as Duplicate of MDEV-24953

            People

              psergei Sergei Petrunia
              csiebert Carsten Siebert
              Votes:
              1 Vote for this issue
              Watchers:
              5 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.