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;

#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

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