[MDEV-31150] Crash on 2nd execution of update using mergeable derived table Created: 2023-04-28  Updated: 2023-08-10  Resolved: 2023-08-01

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update
Affects Version/s: 11.1
Fix Version/s: 11.1.3

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: regression


 Description    

CREATE TABLE t1 (a INT);
 
INSERT INTO t1 VALUES (1),(2);
 
CREATE TABLE t2 (b INT);
 
INSERT INTO t2 VALUES (3),(4);
 
 
 
PREPARE stmt FROM 'UPDATE t1 SET a = NULL WHERE a IN (WITH cte AS (SELECT b FROM t2) SELECT * FROM cte)';
 
EXECUTE stmt;
 
EXECUTE stmt;
 
 
 
# Cleanup
 
DROP TABLE t1, t2;

11.1 3ef11161

 
#3  <signal handler called>
 
#4  0x0000560d79a10c8a in multi_update_check_table_access (thd=0x62b00007e218, table=0x62d00019eb58, tables_for_update=1, updated_arg=0x7f1dafb56b40) at /data/src/11.1/sql/sql_update.cc:1499
 
#5  0x0000560d79a11f89 in Multiupdate_prelocking_strategy::handle_end (this=0x62d00019cbc0, thd=0x62b00007e218) at /data/src/11.1/sql/sql_update.cc:1653
 
#6  0x0000560d794edf0d in open_tables (thd=0x62b00007e218, options=..., start=0x7f1dafb56ec0, counter=0x62d00019cba0, flags=0, prelocking_strategy=0x62d00019cbc0) at /data/src/11.1/sql/sql_base.cc:4741
 
#7  0x0000560d794d4830 in open_tables (thd=0x62b00007e218, tables=0x7f1dafb56ec0, counter=0x62d00019cba0, flags=0, prelocking_strategy=0x62d00019cbc0) at /data/src/11.1/sql/sql_base.h:267
 
#8  0x0000560d794f3650 in open_tables_for_query (thd=0x62b00007e218, tables=0x62d00019c250, table_count=0x62d00019cba0, flags=0, prelocking_strategy=0x62d00019cbc0) at /data/src/11.1/sql/sql_base.cc:5740
 
#9  0x0000560d79870e47 in Sql_cmd_dml::prepare (this=0x62d00019cb88, thd=0x62b00007e218) at /data/src/11.1/sql/sql_select.cc:32458
 
#10 0x0000560d798711a4 in Sql_cmd_dml::execute (this=0x62d00019cb88, thd=0x62b00007e218) at /data/src/11.1/sql/sql_select.cc:32520
 
#11 0x0000560d79699ead in mysql_execute_command (thd=0x62b00007e218, is_called_from_prepared_stmt=true) at /data/src/11.1/sql/sql_parse.cc:4393
 
#12 0x0000560d79741a52 in Prepared_statement::execute (this=0x61900009f698, expanded_query=0x7f1dafb57c40, open_cursor=false) at /data/src/11.1/sql/sql_prepare.cc:4992
 
#13 0x0000560d7973cce9 in Prepared_statement::execute_loop (this=0x61900009f698, expanded_query=0x7f1dafb57c40, open_cursor=false, packet=0x0, packet_end=0x0) at /data/src/11.1/sql/sql_prepare.cc:4415
 
#14 0x0000560d797364b4 in mysql_sql_stmt_execute (thd=0x62b00007e218) at /data/src/11.1/sql/sql_prepare.cc:3456
 
#15 0x0000560d79697508 in mysql_execute_command (thd=0x62b00007e218, is_called_from_prepared_stmt=false) at /data/src/11.1/sql/sql_parse.cc:3960
 
#16 0x0000560d796b133a in mysql_parse (thd=0x62b00007e218, rawbuf=0x6290000e6238 "EXECUTE stmt", length=12, parser_state=0x7f1dafb58a20) at /data/src/11.1/sql/sql_parse.cc:7760
 
#17 0x0000560d79689ab1 in dispatch_command (command=COM_QUERY, thd=0x62b00007e218, packet=0x629000258219 "EXECUTE stmt", packet_length=12, blocking=true) at /data/src/11.1/sql/sql_parse.cc:1892
 
#18 0x0000560d796867ef in do_command (thd=0x62b00007e218, blocking=true) at /data/src/11.1/sql/sql_parse.cc:1405
 
#19 0x0000560d79b3e6e2 in do_handle_one_connection (connect=0x608000002eb8, put_in_cache=true) at /data/src/11.1/sql/sql_connect.cc:1416
 
#20 0x0000560d79b3e0a3 in handle_one_connection (arg=0x608000002e38) at /data/src/11.1/sql/sql_connect.cc:1318
 
#21 0x0000560d7a737870 in pfs_spawn_thread (arg=0x617000005b98) at /data/src/11.1/storage/perfschema/pfs.cc:2201
 
#22 0x00007f1db72a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
 
#23 0x00007f1db73285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81

Reproducible with InnoDB, MyISAM, Aria.

The failure in its current form appeared after this commit

commit 554278e24dbc2c0af9fcfd66c54ca6a99a3cf17f
 
Author: Igor Babaev
 
Date:   Mon Jan 9 22:39:39 2023 -0800
 
 
 
    MDEV-7487 Semi-join optimization for single-table update/delete statements

However, there were several commits before this one which were either not building or otherwise breaking, so it's hard to say when exactly the test started failing, but definitely somewhere between the commit above and 3a9358a4106a (MDEV-28883 Re-design the upper level), inclusive.

 Comments   
Comment by Alice Sherepa [ 2023-07-25 ]

also with sp and derived table

CREATE TABLE t1 (a INT);
 
INSERT INTO t1 VALUES (1),(2);
 
 
 
CREATE PROCEDURE sp() UPDATE t1 SET a = 5 WHERE 1 IN (SELECT * FROM (SELECT 1 from (values(1)) t)dt);
 
CALL sp();
 
CALL sp();
 
 
 
DROP TABLE t1;


230725 17:25:50 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 11.2.0-MariaDB-debug-log source revision: cb6307f7099ede2016e6a42e440176c290727fa5
 
 
 
sigaction.c:0(__restore_rt)[0x7f4528f8c420]
 
sql/sql_update.cc:1500(multi_update_check_table_access(THD*, TABLE_LIST*, unsigned long long, bool*))[0x557edcd81d20]
 
sql/sql_update.cc:1654(Multiupdate_prelocking_strategy::handle_end(THD*))[0x557edcd83081]
 
sql/sql_base.cc:4739(open_tables(THD*, DDL_options_st const&, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*))[0x557edc83ae02]
 
sql/sql_base.h:269(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*))[0x557edc8211f9]
 
sql/sql_base.cc:5738(open_tables_for_query(THD*, TABLE_LIST*, unsigned int*, unsigned int, DML_prelocking_strategy*))[0x557edc8406c6]
 
sql/sql_select.cc:33244(Sql_cmd_dml::prepare(THD*))[0x557edcbd5b9c]
 
sql/sql_select.cc:33306(Sql_cmd_dml::execute(THD*))[0x557edcbd5ee8]
 
sql/sql_parse.cc:4393(mysql_execute_command(THD*, bool))[0x557edc9eff1f]
 
sql/sp_instr.cc:944(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x557edd0ae009]
 
sql/sp_instr.cc:296(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*, bool))[0x557edd0aa173]
 
sql/sp_instr.cc:476(sp_lex_keeper::validate_lex_and_exec_core(THD*, unsigned int*, bool, sp_lex_instr*))[0x557edd0ab3c0]
 
sql/sp_instr.cc:850(sp_instr_stmt::execute(THD*, unsigned int*))[0x557edd0ad74d]
 
sql/sp_head.cc:1280(sp_head::execute(THD*, bool))[0x557edc777e97]
 
sql/sp_head.cc:2267(sp_head::execute_procedure(THD*, List<Item>*))[0x557edc77e0f7]
 
sql/sql_parse.cc:3024(do_execute_sp(THD*, sp_head*))[0x557edc9e6946]
 
sql/sql_parse.cc:3269(Sql_cmd_call::execute(THD*))[0x557edc9e8534]
 
sql/sql_parse.cc:5766(mysql_execute_command(THD*, bool))[0x557edc9fa5bf]
 
sql/sql_parse.cc:7769(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x557edca0780f]
 
sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x557edc9dfcef]
 
sql/sql_parse.cc:1405(do_command(THD*, bool))[0x557edc9dca39]
 
sql/sql_connect.cc:1416(do_handle_one_connection(CONNECT*, bool))[0x557edceb7d17]
 
sql/sql_connect.cc:1320(handle_one_connection)[0x557edceb7674]
 
perfschema/pfs.cc:2203(pfs_spawn_thread)[0x557eddb34a90]
 
nptl/pthread_create.c:478(start_thread)[0x7fbf986a5609]

Comment by Igor Babaev [ 2023-07-26 ]

The same problen can be reproduced when a mergeable derived table is used instead of CTE:

MariaDB [test]> PREPARE stmt FROM 'UPDATE t1 SET a = NULL WHERE a IN (SELECT * FROM (SELECT b FROM t2) dt)';
 
Query OK, 0 rows affected (0.001 sec)
 
Statement prepared
 
 
 
MariaDB [test]> EXECUTE stmt;
 
Query OK, 0 rows affected (0.002 sec)
 
Rows matched: 0  Changed: 0  Warnings: 0
 
 
 
MariaDB [test]> EXECUTE stmt;
 
ERROR 2013 (HY000): Lost connection to MySQL server during query


Thread 15 "mysqld" received signal SIGSEGV, Segmentation fault.
 
[Switching to Thread 0x7ffff4066700 (LWP 27568)]
 
0x0000555556125bda in multi_update_check_table_access (thd=0x7fffa0000b18, table=0x7fffa0034488, tables_for_update=1, updated_arg=0x7ffff4064090) at /home/igor/maria-git/10.4/sql/sql_update.cc:1500

Comment by Igor Babaev [ 2023-07-26 ]

The same problem can be reproduced with a multi-table update:

ariaDB [test]> CREATE TABLE t3 (c INT);
 
Query OK, 0 rows affected (0.030 sec)
 
 
 
MariaDB [test]> INSERT INTO t3 VALUES (1),(2);
 
Query OK, 2 rows affected (0.007 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> PREPARE stmt FROM 'UPDATE t1,t3 SET a = NULL WHERE a=c AND  a IN (SELECT * FROM (SELECT b FROM t2) dt)';
 
Query OK, 0 rows affected (0.002 sec)
 
Statement prepared
 
 
 
MariaDB [test]> EXECUTE stmt;
 
Query OK, 0 rows affected (0.003 sec)
 
Rows matched: 0  Changed: 0  Warnings: 0
 
 
 
MariaDB [test]> EXECUTE stmt;
 
ERROR 2013 (HY000): Lost connection to MySQL server during query


Thread 18 "mysqld" received signal SIGSEGV, Segmentation fault.
 
[Switching to Thread 0x7ffff4066700 (LWP 27922)]
 
0x0000555556125bda in multi_update_check_table_access (thd=0x7fff8c000b18, table=0x7fff8c087788, tables_for_update=1, updated_arg=0x7ffff4064090) at /home/igor/maria-git/10.4/sql/sql_update.cc:1500

Comment by Igor Babaev [ 2023-07-26 ]

We don't have such problem when using a mergeable view instead of the derived table/CTE:

unknown [test]> CREATE VIEW v1 AS SELECT b FROM t2;
 
Query OK, 0 rows affected (0.010 sec)
 
 
 
MariaDB [test]> PREPARE stmt FROM 'UPDATE t1,t3 SET a = NULL WHERE a=c AND  a IN (SELECT b FROM v1)';                   Query OK, 0 rows affected (0.002 sec)
 
Statement prepared
 
 
 
MariaDB [test]> EXECUTE stmt;                                                                        
Query OK, 0 rows affected (0.003 sec)
 
Rows matched: 0  Changed: 0  Warnings: 0
 
 
 
MariaDB [test]> EXECUTE stmt;
 
Query OK, 0 rows affected (0.002 sec)
 
Rows matched: 0  Changed: 0  Warnings: 0
 
 
 
MariaDB [test]> PREPARE stmt FROM 'UPDATE t1 SET a = NULL WHERE a IN (SELECT b FROM v1)';
 
Query OK, 0 rows affected (0.001 sec)
 
Statement prepared
 
 
 
MariaDB [test]> EXECUTE stmt;
 
Query OK, 0 rows affected (0.002 sec)
 
Rows matched: 0  Changed: 0  Warnings: 0
 
 
 
MariaDB [test]> EXECUTE stmt;
 
Query OK, 0 rows affected (0.002 sec)
 
Rows matched: 0  Changed: 0  Warnings: 0

or when using a non-mergeable derived table instead of our derived table/CTE.

MariaDB [test]> PREPARE stmt FROM 'UPDATE t1,t3 SET a = NULL WHERE a=c AND  a IN (SELECT * FROM (SELECT b FROM t2 GROUP BY b/2) dt)';
 
Query OK, 0 rows affected (0.002 sec)
 
Statement prepared
 
 
 
MariaDB [test]> EXECUTE stmt;
 
Query OK, 0 rows affected (0.004 sec)
 
Rows matched: 0  Changed: 0  Warnings: 0
 
 
 
MariaDB [test]> EXECUTE stmt;
 
Query OK, 0 rows affected (0.003 sec)
 
Rows matched: 0  Changed: 0  Warnings: 0
 
 
 
MariaDB [test]> PREPARE stmt FROM 'UPDATE t1 SET a = NULL WHERE a IN (SELECT * FROM (SELECT b FROM t2 GROUP BY b/2) dt)';
 
Query OK, 0 rows affected (0.001 sec)
 
Statement prepared
 
 
 
MariaDB [test]> EXECUTE stmt;
 
Query OK, 0 rows affected (0.003 sec)
 
Rows matched: 0  Changed: 0  Warnings: 0
 
 
 
MariaDB [test]> EXECUTE stmt;
 
Query OK, 0 rows affected (0.003 sec)
 
Rows matched: 0  Changed: 0  Warnings: 0

Comment by Igor Babaev [ 2023-07-26 ]

This problem cannot be observed in 10.4 and up to 11.1 because any mergeable derived table is considered as non-mergeable if it is used in a single-table UPDATE/DELETE or in a multi-table UPDATE/DELETE. This is of course a mistake that was fixed in the task MDEV-28883 pushed into 11.1.

Comment by Igor Babaev [ 2023-07-27 ]

A similar problem can be seen for the second call of a procedure containing any of the following statements:

UPDATE t1,t3 SET a = NULL WHERE a=c AND a IN (SELECT * FROM (SELECT b FROM t2) dt);
 
UPDATE t1,t3  SET a = NULL   WHERE a=c AND a IN (WITH cte AS (SELECT b FROM t2) SELECT * FROM cte);
 
UPDATE t1 SET a = NULL  WHERE a IN (SELECT * FROM (SELECT b FROM t2) dt);
 
UPDATE t1 SET a = NULL  WHERE a IN (WITH cte AS (SELECT b FROM t2) SELECT * FROM cte);

Comment by Oleksandr Byelkin [ 2023-07-27 ]

OK to push

Comment by Igor Babaev [ 2023-08-01 ]

A fix for this bug was pushed into 11.1

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