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

Crash with INSERT...SELECT using a derived table in GROUP BY clause

Details

    Description

      poc:

      CREATE TABLE v859 ( v860 BOOLEAN NOT NULL , v861 INT , v862 INT ) ;
       CREATE TABLE v863 ( v864 INT NOT NULL , v865 INT , v866 INT ) ;
       INSERT INTO v863 ( v866 ) VALUES ( ( ( TRUE , v864 ) NOT IN ( SELECT ( - 49 ) AS v867 , -128 FROM v859 GROUP BY ( TRUE , v862 ) NOT IN ( SELECT v864 , ( SELECT v861 FROM ( WITH v869 AS ( SELECT v866 FROM ( SELECT NOT v864 <= 'x' , v866 FROM v863 GROUP BY v866 ) AS v868 ) SELECT v866 , ( v866 = 67 OR v866 > 'x' ) FROM v863 ) AS v870 NATURAL JOIN v859 WHERE v866 = v862 ) AS v871 FROM v863 ) , v861 ) OR v864 > 'x' ) ) ;
       UPDATE v863 SET v864 = 0 WHERE v864 = 127 ;
       INSERT INTO v859 ( v860 ) VALUES ( ( ( SELECT ARRAY [ 0 , 58 , 91 ] ) ) [ 30 ] ) , ( 78 ) ;
       SELECT ROW_NUMBER ( v861 ) OVER v872 , RANK ( v861 ) OVER v872 FROM v859 WINDOW v872 AS ( PARTITION BY v861 ORDER BY v861 DESC ) ;
      

      output:
      SUMMARY: AddressSanitizer: SEGV /sql/table.cc:5997 in TABLE_LIST::set_check_materialized()

      The full error log is in the attachment.

      Attachments

        Activity

          danblack Daniel Black added a comment -

          Confirmed on 10.3.35+c9b5a05341d7342db5f369493ea200b5fb9db243 for the first insert statement. Following SQL not needed.

          danblack Daniel Black added a comment - Confirmed on 10.3.35+c9b5a05341d7342db5f369493ea200b5fb9db243 for the first insert statement. Following SQL not needed.
          alice Alice Sherepa added a comment -

          CREATE TABLE t1 (k int) ;
           
          INSERT INTO t1 (k) VALUES 
          (1 IN (SELECT 1 FROM dual GROUP BY (SELECT (SELECT 1 FROM (SELECT 1 FROM t1)dt))  )) ;
          

          bb-10.2-release 0ba528fe56f6c637d9fbc9d177a

          Version: '10.2.44-MariaDB-debug-log' 
          220520 17:54:15 [ERROR] mysqld got signal 11 ;
           
          Server version: 10.2.44-MariaDB-debug-log
           
          mysys/stacktrace.c:172(my_print_stacktrace)[0x555ba9c5da81]
          sql/signal_handler.cc:221(handle_fatal_signal)[0x555ba9503f58]
          sql/table.cc:5785(TABLE_LIST::set_check_materialized())[0x555ba9392d50]
          sql/table.h:2521(TABLE_LIST::set_materialized_derived())[0x555ba9219974]
          sql/sql_base.cc:1096(find_dup_table(THD*, TABLE_LIST*, TABLE_LIST*, unsigned int))[0x555ba92081ba]
          sql/sql_base.cc:1139(unique_table(THD*, TABLE_LIST*, TABLE_LIST*, unsigned int))[0x555ba92082d9]
          sql/sql_insert.cc:1592(mysql_prepare_insert(THD*, TABLE_LIST*, TABLE*, List<Item>&, List<Item>*, List<Item>&, List<Item>&, enum_duplicates, Item**, bool))[0x555ba925a425]
          sql/sql_insert.cc:771(mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool))[0x555ba9257fca]
          sql/sql_parse.cc:4217(mysql_execute_command(THD*))[0x555ba92803ec]
          sql/sql_parse.cc:7793(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x555ba928b8f6]
          sql/sql_parse.cc:1830(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x555ba9279b51]
          sql/sql_parse.cc:1381(do_command(THD*))[0x555ba927864c]
          sql/sql_connect.cc:1336(do_handle_one_connection(CONNECT*))[0x555ba93d4c87]
          sql/sql_connect.cc:1242(handle_one_connection)[0x555ba93d49ec]
          perfschema/pfs.cc:1871(pfs_spawn_thread)[0x555ba9bff442]
          nptl/pthread_create.c:478(start_thread)[0x7fd6a9677609]
           
          Query (0x7fd64c012708): INSERT INTO t1 (k) VALUES 
          (1 IN (SELECT 1 FROM dual GROUP BY (SELECT (SELECT 1 FROM (SELECT 1 FROM t1)dt))  ))
          
          

          alice Alice Sherepa added a comment - CREATE TABLE t1 (k int ) ;   INSERT INTO t1 (k) VALUES (1 IN ( SELECT 1 FROM dual GROUP BY ( SELECT ( SELECT 1 FROM ( SELECT 1 FROM t1)dt)) )) ; bb-10.2-release 0ba528fe56f6c637d9fbc9d177a Version: '10.2.44-MariaDB-debug-log' 220520 17:54:15 [ERROR] mysqld got signal 11 ;   Server version: 10.2.44-MariaDB-debug-log   mysys/stacktrace.c:172(my_print_stacktrace)[0x555ba9c5da81] sql/signal_handler.cc:221(handle_fatal_signal)[0x555ba9503f58] sql/table.cc:5785(TABLE_LIST::set_check_materialized())[0x555ba9392d50] sql/table.h:2521(TABLE_LIST::set_materialized_derived())[0x555ba9219974] sql/sql_base.cc:1096(find_dup_table(THD*, TABLE_LIST*, TABLE_LIST*, unsigned int))[0x555ba92081ba] sql/sql_base.cc:1139(unique_table(THD*, TABLE_LIST*, TABLE_LIST*, unsigned int))[0x555ba92082d9] sql/sql_insert.cc:1592(mysql_prepare_insert(THD*, TABLE_LIST*, TABLE*, List<Item>&, List<Item>*, List<Item>&, List<Item>&, enum_duplicates, Item**, bool))[0x555ba925a425] sql/sql_insert.cc:771(mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool))[0x555ba9257fca] sql/sql_parse.cc:4217(mysql_execute_command(THD*))[0x555ba92803ec] sql/sql_parse.cc:7793(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x555ba928b8f6] sql/sql_parse.cc:1830(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x555ba9279b51] sql/sql_parse.cc:1381(do_command(THD*))[0x555ba927864c] sql/sql_connect.cc:1336(do_handle_one_connection(CONNECT*))[0x555ba93d4c87] sql/sql_connect.cc:1242(handle_one_connection)[0x555ba93d49ec] perfschema/pfs.cc:1871(pfs_spawn_thread)[0x555ba9bff442] nptl/pthread_create.c:478(start_thread)[0x7fd6a9677609]   Query (0x7fd64c012708): INSERT INTO t1 (k) VALUES (1 IN (SELECT 1 FROM dual GROUP BY (SELECT (SELECT 1 FROM (SELECT 1 FROM t1)dt)) ))
          igor Igor Babaev added a comment - - edited

          This bug manifests itself for INSERT...SELECT statements whose WHERE condition contained an IN/ANY/ALL predicand with such grouping subquery that:
          its GROUP BY clause can be eliminated,
          the GROUP clause contains a subquery over a mergeable derived table referencing the updated table.
          Here's an example of such INSERT...SELECT statement:

          insert into t1
          select b from t2
            where b in (select c from t3
                               group by (select * from (select a from t1) dt where a = 1));
          

          Execution of this query causes a crash of the server at the prepare phase.

          The bug causes similar crash when executing a single-table DELETE statement if it uses a WHERE condition with EXISTS subquery whose WHERE condition is such as described above for affected INSERT...SELECT statements. Here's an example of such DELETE statement

          delete from t1
            where exists (select b from t2
                                   where b in (select c from t3
                                                      group by (select * from (select a from t1) dt where a = 1)));
          

          igor Igor Babaev added a comment - - edited This bug manifests itself for INSERT...SELECT statements whose WHERE condition contained an IN/ANY/ALL predicand with such grouping subquery that: its GROUP BY clause can be eliminated, the GROUP clause contains a subquery over a mergeable derived table referencing the updated table. Here's an example of such INSERT...SELECT statement: insert into t1 select b from t2 where b in ( select c from t3 group by ( select * from ( select a from t1) dt where a = 1)); Execution of this query causes a crash of the server at the prepare phase. The bug causes similar crash when executing a single-table DELETE statement if it uses a WHERE condition with EXISTS subquery whose WHERE condition is such as described above for affected INSERT...SELECT statements. Here's an example of such DELETE statement delete from t1 where exists ( select b from t2 where b in ( select c from t3 group by ( select * from ( select a from t1) dt where a = 1)));
          igor Igor Babaev added a comment -

          When executing the INSERT...SELECT statement from the previous comment we come to the call of JOIN::prepare() for the subquery of the IN predicand. JOIN::prepare notices that the GROUP BY clause of the subquery can be e liminated and it calls remove_redundant_subquery_clauses() that removes the subquery of the GROUP BY clause. The corresponding select together with underlying select of the derived table are excluded from the select tree of the statement. The call of select_insert::prepare() checks whether any of the tables used the SELECT part of the statement is the same as the updated table by invoking unique_table() and then find_dup_table() inside it. find_dup_table() sees that this is the case for table t1 used in the derived table and it forces the materialization of the derived table dt calling TABLE_LIST::set_materialized_derived() in spite of the fact that the
          select specifying this derived table was excluded from the tree. TABLE_LIST::set_check_materialized() is called dt. At this moment we discover that unit specifying dt has not been marked as excluded and the first attempt to get its first select causes a crash.

          igor Igor Babaev added a comment - When executing the INSERT...SELECT statement from the previous comment we come to the call of JOIN::prepare() for the subquery of the IN predicand. JOIN::prepare notices that the GROUP BY clause of the subquery can be e liminated and it calls remove_redundant_subquery_clauses() that removes the subquery of the GROUP BY clause. The corresponding select together with underlying select of the derived table are excluded from the select tree of the statement. The call of select_insert::prepare() checks whether any of the tables used the SELECT part of the statement is the same as the updated table by invoking unique_table() and then find_dup_table() inside it. find_dup_table() sees that this is the case for table t1 used in the derived table and it forces the materialization of the derived table dt calling TABLE_LIST::set_materialized_derived() in spite of the fact that the select specifying this derived table was excluded from the tree. TABLE_LIST::set_check_materialized() is called dt. At this moment we discover that unit specifying dt has not been marked as excluded and the first attempt to get its first select causes a crash.

          OK to push

          sanja Oleksandr Byelkin added a comment - OK to push
          igor Igor Babaev added a comment -

          A fix for this bug was pushed into 10.3

          igor Igor Babaev added a comment - A fix for this bug was pushed into 10.3

          People

            igor Igor Babaev
            nobody Shihao Wen
            Votes:
            0 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.