Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
11.1(EOL)
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.
Attachments
Activity
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
|
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
|
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
|
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.
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); |
also with sp and derived table
CALL sp();CALL sp();230725 17:25:50 [ERROR] mysqld got signal 11 ;Server version: 11.2.0-MariaDB-debug-log source revision: cb6307f7099ede2016e6a42e440176c290727fa5sigaction.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]