See also MDEV-7220 for a related discussion. Non-semijoin optimizations (e.g. Materialization vs IN->EXISTS) would are not used in single-table UPDATE/DELETE, either. And that is a deficiency, too.

OK to push

In branch bb-10.11-MDEV-7487rebased-against-11.0MDEV-29390-test

• Created tests on update and delete:

  Engine	Update (update_use_source.test)	Delete (delete_use_source_engins.test)
  InnoDB
  Aria
  MyISAM
  MEMORY

• Added tests for update/delete with LooseScan and DuplicateWeedout optimization strategies (in delete_single_to_multi.test, update_single_to_multi.test)
• Added test for multi-update with JSON_TABLE (in mysql-test/suite/json/t/json_table.test)

With scripts there were created tests based on "main" suite to check for a server crash on update. Example of converting:
this

SELECT 1 FROM t2 WHERE pk > ANY (SELECT 1 FROM t2);

converts to

create or replace table ut_tmp (a int);

update ut_tmp, t2 set ut_tmp.a = 10 WHERE pk > ANY (SELECT 1 FROM );

drop table ut_tmp;

During the tests execution there was only one crash related to the parameter:

set debug_dbug="+d,cache_sp_in_show_create";

But this parameter ("cache_sp_in_show_create")created only for tests with special behavior in lock_sync.test so there were no null-pointer checks and that's why I got this crach.

Checked engine with update/delete tests:

Engine	main.delete	main.delete_single_to_multi	main.update	main.update_single_to_multi	main.view	comments
InnoDB						In main.view was problem like MDEV-8632, but it repeats from 10.3
Aria
MyISAM
RocksDB
MEMORY		O		O

In branch bb-10.11-MDEV-7487rebased-against-11.0MDEV-29390-test:

• Added test for multi-update with JSON_TABLE
• Added tests for multi-update and multi-delete for engine Connect

Tables with versioning:

• tables with time period do not support multi-update (there is a case in period.update)
• checked update/delete with tests from update.test, update_single_to_multi.test, delete.test, delete_single_to_multi.test - in these cases for all possible tables were added "with system versioning"

Engine Spider check with small tests for (multi-)update/delete

Testing of this task is completed. The latest version of the tests can be taken from the branch bb-10.11-MDEV-7487rebased-against-11.0MDEV-29390-test-2
At the moment, there is not found critical bugs associated with this implementation, but there are bugs that are a legacy of older versions (e.g. MDEV-30539). Bugs like this and low test coverage can give potential risks (especially after merge).

The commit for this task was pushed into 11.1