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

Crash in convert_join_subqueries_to_semijoins

Details

    Description

      Query:

      INSERT INTO t1 (page_id, variable_name, value) SELECT  308, 'xxx', 50 FROM dual WHERE 'xxx' IN  (SELECT alias FROM t2);

      Log:

      Mar 12 10:41:32 uvn-234-24 mysqld: 190312 10:41:32 [ERROR] mysqld got signal 11 ;
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: This could be because you hit a bug. It is also possible that this binary
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: or one of the libraries it was linked against is corrupt, improperly built,
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: or misconfigured. This error can also be caused by malfunctioning hardware.
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: To report this bug, see https://mariadb.com/kb/en/reporting-bugs
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: We will try our best to scrape up some info that will hopefully help
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: diagnose the problem, but since we have already crashed,
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: something is definitely wrong and this may fail.
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: Server version: 10.2.22-MariaDB
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: key_buffer_size=134217728
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: read_buffer_size=131072
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: max_used_connections=2
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: max_threads=153
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: thread_count=8
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: It is possible that mysqld could use up to
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467240 K  bytes of memory
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: Hope that's ok; if not, decrease some variables in the equation.
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: Thread pointer: 0x7fce68000a88
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: Attempting backtrace. You can use the following information to find out
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: where mysqld died. If you see no messages after this, something went
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: terribly wrong...
      		 
      Mar 12 10:41:32 uvn-234-24 mysqld: stack_bottom = 0x7fce6c3f1d70 thread_stack 0x49000
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x7fce7c06bd6e]
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: mysys/stacktrace.c:268(my_print_stacktrace)[0x7fce7baed665]
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: sigaction.c:0(__restore_rt)[0x7fce7b04c5e0]
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: sql/opt_subselect.cc:1715(convert_join_subqueries_to_semijoins(JOIN*))[0x7fce7ba5b163]
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: sql/sql_select.cc:1259(JOIN::optimize_inner())[0x7fce7b9a8cf4]
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: sql/sql_select.cc:1116(JOIN::optimize())[0x7fce7b9ab93f]
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: sql/sql_select.cc:3804(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*))[0x7fce7b9ad0b4]
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: sql/sql_select.cc:376(handle_select(THD*, LEX*, select_result*, unsigned long))[0x7fce7b9adc94]
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: sql/sql_parse.cc:4548(mysql_execute_command(THD*))[0x7fce7b95dc8f]
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: sql/sql_parse.cc:8019(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x7fce7b95f43e]
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: sql/sql_parse.cc:1829(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x7fce7b962565]
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: sql/sql_parse.cc:1381(do_command(THD*))[0x7fce7b963165]
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: sql/sql_connect.cc:1336(do_handle_one_connection(CONNECT*))[0x7fce7ba2abaa]
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: sql/sql_connect.cc:1244(handle_one_connection)[0x7fce7ba2accd]
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: pthread_create.c:0(start_thread)[0x7fce7b044e25]
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: /lib64/libc.so.6(clone+0x6d)[0x7fce7961c34d]
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: Trying to get some variables.
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: Some pointers may be invalid and cause the dump to abort.
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: Query (0x7fce680110c0): INSERT INTO t1 (page_id, variable_name, value) SELECT  308, 'xxx', 50 FROM dual WHERE 'xxx' IN  (SELECT alias FROM t2)
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: Connection ID (thread ID): 25
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: Status: NOT_KILLED
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: 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=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
      		 
      Mar 12 10:41:33 uvn-234-24 mysqld: information that should help you find out what is causing the crash.

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19618 crash in insert select with sub query

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            alice Alice Sherepa added a comment -

            Thanks a lot! The bug is reproducible on MariaDB 5.5-10.4

            create table t1 ( a1 varchar(25));
            		 
            create table t2 ( a2 varchar(25)) ;
            		 
            insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2);

            5.5 8024f8c6b86b204c3475

            		 
            ==27847==ERROR: AddressSanitizer: SEGV on unknown address 0x000000000000 (pc 0x0000009475d4 bp 0x00000219c460 sp 0x7fbbc397f170 T15)
            		 
                #0 0x9475d3 in convert_subq_to_sj /5.5/sql/opt_subselect.cc:1712
            		 
                #1 0x9475d3 in convert_join_subqueries_to_semijoins(JOIN*) /5.5/sql/opt_subselect.cc:1247
            		 
                #2 0x75a425 in JOIN::optimize() /5.5/sql/sql_select.cc:1028
            		 
                #3 0x7680e5 in mysql_select(THD*, Item***, 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*) /5.5/sql/sql_select.cc:3119
            		 
                #4 0x768957 in handle_select(THD*, LEX*, select_result*, unsigned long) /5.5/sql/sql_select.cc:323
            		 
                #5 0x687d11 in mysql_execute_command(THD*) /5.5/sql/sql_parse.cc:3052
            		 
                #6 0x692657 in mysql_parse(THD*, char*, unsigned int, Parser_state*) /5.5/sql/sql_parse.cc:5924
            		 
                #7 0x695a54 in dispatch_command(enum_server_command, THD*, char*, unsigned int) /5.5/sql/sql_parse.cc:1067
            		 
                #8 0x699b32 in do_command(THD*) /5.5/sql/sql_parse.cc:793
            		 
                #9 0x8cbd69 in do_handle_one_connection(THD*) /5.5/sql/sql_connect.cc:1268
            		 
                #10 0x8cbfe2 in handle_one_connection /5.5/sql/sql_connect.cc:1184
            		 
                #11 0x14396f7 in pfs_spawn_thread /5.5/storage/perfschema/pfs.cc:1015
            		 
                #12 0x7fbbd1ebe6b9 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x76b9)
            		 
                #13 0x7fbbd136541c in clone (/lib/x86_64-linux-gnu/libc.so.6+0x10741c)

            alice Alice Sherepa added a comment - Thanks a lot! The bug is reproducible on MariaDB 5.5-10.4 create table t1 ( a1 varchar (25)); create table t2 ( a2 varchar (25)) ; insert into t1 select 'xxx' from dual where 'xxx' in ( select a2 from t2); 5.5 8024f8c6b86b204c3475 ==27847==ERROR: AddressSanitizer: SEGV on unknown address 0x000000000000 (pc 0x0000009475d4 bp 0x00000219c460 sp 0x7fbbc397f170 T15) #0 0x9475d3 in convert_subq_to_sj /5.5/sql/opt_subselect.cc:1712 #1 0x9475d3 in convert_join_subqueries_to_semijoins(JOIN*) /5.5/sql/opt_subselect.cc:1247 #2 0x75a425 in JOIN::optimize() /5.5/sql/sql_select.cc:1028 #3 0x7680e5 in mysql_select(THD*, Item***, 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*) /5.5/sql/sql_select.cc:3119 #4 0x768957 in handle_select(THD*, LEX*, select_result*, unsigned long) /5.5/sql/sql_select.cc:323 #5 0x687d11 in mysql_execute_command(THD*) /5.5/sql/sql_parse.cc:3052 #6 0x692657 in mysql_parse(THD*, char*, unsigned int, Parser_state*) /5.5/sql/sql_parse.cc:5924 #7 0x695a54 in dispatch_command(enum_server_command, THD*, char*, unsigned int) /5.5/sql/sql_parse.cc:1067 #8 0x699b32 in do_command(THD*) /5.5/sql/sql_parse.cc:793 #9 0x8cbd69 in do_handle_one_connection(THD*) /5.5/sql/sql_connect.cc:1268 #10 0x8cbfe2 in handle_one_connection /5.5/sql/sql_connect.cc:1184 #11 0x14396f7 in pfs_spawn_thread /5.5/storage/perfschema/pfs.cc:1015 #12 0x7fbbd1ebe6b9 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x76b9) #13 0x7fbbd136541c in clone (/lib/x86_64-linux-gnu/libc.so.6+0x10741c)
            igor Igor Babaev (Inactive) added a comment -

            A fix for this bug was pushed into 5.5.
            Should be merged upstream as it is.

            igor Igor Babaev (Inactive) added a comment - A fix for this bug was pushed into 5.5. Should be merged upstream as it is.
            serg Sergei Golubchik added a comment -

            The test is in subselect_sj.test and it obviously doesn't crash.

            But it's enough to flip the value of materialization in the optimizer_switch and it starts crashing again. In 5.5 it doesn't crash either way, but in 10.1 it's just bad luck, it doesn't crash because of the particular optimizer_switch settings in the specific place in the test file. This is pretty much impossible to detect during the merge ☹

            serg Sergei Golubchik added a comment - The test is in subselect_sj.test and it obviously doesn't crash. But it's enough to flip the value of materialization in the optimizer_switch and it starts crashing again. In 5.5 it doesn't crash either way, but in 10.1 it's just bad luck, it doesn't crash because of the particular optimizer_switch settings in the specific place in the test file. This is pretty much impossible to detect during the merge ☹
            igor Igor Babaev (Inactive) added a comment -

            Here's my observations:
            My patch for MDEV-18896 (commit 0dd12b4f2a72245a0fb491685c172a7b0e48cbc5)
            was incomplete:
            It did not allow to consider the subquery 

             'xxx' in (select a2 from t2)

            as a semi-join subquery because it was was in WHERE of a table-less query.
            Yet it still allowed to consider this subquery as a jtbm semi-join subquery.
            In 5.5 this did cause any problems because
            the conditions 

                     (thd->lex->sql_command == SQLCOM_SELECT ||                           // *
            		 
                      thd->lex->sql_command == SQLCOM_CREATE_TABLE) &&              // *

            in the 5.5 code of the function is_materialization_applicable() prevented registering the subquery
            as a jtbm semi-join subquery (for our command we had thd->lex->sql_command == SQLCOM_INSERT_SELECT)

            After the patch for MDEV-7220 (commit 775528ada3c2cc3a2a7ebafadebbcf44530aab50) applied to 10.0
            this condition disappeared from 10.0 and upstream and the subquery was registered as a jtbm semi-join subquery. This is incorrect because table-less queries does not accept any semi-join subqueries.

            Here's the explanation why we did not see any problem with the test case for MDEV-18896.
            in subselect_sj.test some of the previous test cases (the test case for bug BUG#803457) did not restore the state of the materialization switch that remained turned 'off'. If we set it on we observe a crash for the test case of MDEV-18896.

            igor Igor Babaev (Inactive) added a comment - Here's my observations: My patch for MDEV-18896 (commit 0dd12b4f2a72245a0fb491685c172a7b0e48cbc5) was incomplete: It did not allow to consider the subquery 'xxx' in (select a2 from t2) as a semi-join subquery because it was was in WHERE of a table-less query. Yet it still allowed to consider this subquery as a jtbm semi-join subquery. In 5.5 this did cause any problems because the conditions (thd->lex->sql_command == SQLCOM_SELECT || // * thd->lex->sql_command == SQLCOM_CREATE_TABLE) && // * in the 5.5 code of the function is_materialization_applicable() prevented registering the subquery as a jtbm semi-join subquery (for our command we had thd->lex->sql_command == SQLCOM_INSERT_SELECT) After the patch for MDEV-7220 (commit 775528ada3c2cc3a2a7ebafadebbcf44530aab50) applied to 10.0 this condition disappeared from 10.0 and upstream and the subquery was registered as a jtbm semi-join subquery. This is incorrect because table-less queries does not accept any semi-join subqueries. Here's the explanation why we did not see any problem with the test case for MDEV-18896 . in subselect_sj.test some of the previous test cases (the test case for bug BUG#803457) did not restore the state of the materialization switch that remained turned 'off'. If we set it on we observe a crash for the test case of MDEV-18896 .
            igor Igor Babaev (Inactive) added a comment -

            A complement/correction for the original fix of this bug was pushed into 5.5.
            I checked the correction against the reported test case from data.dump for 10.1,10.2,10.3 and it worked fine.
            So the correction should be applied upstream as it is.

            igor Igor Babaev (Inactive) added a comment - A complement/correction for the original fix of this bug was pushed into 5.5. I checked the correction against the reported test case from data.dump for 10.1,10.2,10.3 and it worked fine. So the correction should be applied upstream as it is.

            People

              igor Igor Babaev (Inactive)
              BB Silver Asu
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.