[MDEV-28617] Crash with INSERT...SELECT using a derived table in GROUP BY clause Created: 2022-05-19  Updated: 2022-09-05  Resolved: 2022-08-03

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Delete, Data Manipulation - Insert
Affects Version/s: 10.3.35, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.3.36, 10.4.26, 10.5.17, 10.6.9, 10.7.5, 10.8.4, 10.9.2

Type: Bug Priority: Critical
Reporter: Shihao Wen Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: fuzzer
Environment:

ubuntu 18.04

Attachments: HTML File 75_stack    

 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.

 Comments   
Comment by Daniel Black [ 2022-05-19 ]

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

Comment by Alice Sherepa [ 2022-05-20 ] 

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))  ))

Comment by Igor Babaev [ 2022-08-01 ]

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)));

Comment by Igor Babaev [ 2022-08-02 ]

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.

Comment by Oleksandr Byelkin [ 2022-08-02 ]

OK to push

Comment by Igor Babaev [ 2022-08-03 ]

A fix for this bug was pushed into 10.3

Generated at Thu Feb 08 10:02:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.