MDEV-29390: Improve coverage for UPDATE and DELETE statements in MTR test suites

Created tests for "delete" based on update_use_source.test

For the update_use_source.test tests, data recovery in the table has been changed
from a rollback transaction to a complete delete and re-insert of the data with
optimize table. Cases are now being checked on three engines.

Added tests for update/delete with LooseScan and DuplicateWeedout optimization strategies
Added tests for engine MEMORY on delete and update
Added tests for multi-update with JSON_TABLE
Added tests for multi-update and multi-delete for engine Connect

9 files changed, 1214 insertions, 127 deletions

diff --git a/mysql-test/include/delete_use_source.inc b/mysql-test/include/delete_use_source.inc
new file mode 100644
index 00000000000..3f789767142
--- /dev/null
+++ b/mysql-test/include/delete_use_source.inc
@@ -0,0 +1,94 @@
+create table t1 (c1 integer, c2 integer, c3 integer);
+
+insert into t1(c1,c2,c3)
+        values (1,1,1),(1,2,2),(1,3,3),
+               (2,1,4),(2,2,5),(2,3,6),
+               (2,4,7),(2,5,8);
+insert into t1 select c1+10,c2,c3+10 from t1;
+insert into t1 select c1+20,c2+1,c3+20 from t1;
+analyze table t1 persistent for all;
+
+create view v1 as select * from t1 where c2=2;
+
+--echo #######################################
+--echo #        Test without any index       #
+--echo #######################################
+--source include/delete_use_source_cases.inc
+--source include/delete_use_source_cases_non_innodb.inc
+
+--echo #######################################
+--echo #          Test with an index         #
+--echo #######################################
+create index t1_c2 on t1 (c2,c1);
+--source include/delete_use_source_cases.inc
+--source include/delete_use_source_cases_non_innodb.inc
+
+--echo #######################################
+--echo #       Test with a primary key       #
+--echo #######################################
+drop index t1_c2 on t1;
+alter table t1 add primary key (c3);
+--source include/delete_use_source_cases.inc
+--source include/delete_use_source_cases_non_innodb.inc
+
+drop view v1;
+drop table t1;
+
+--echo #
+--echo # Test on dynamic columns (blob)
+--echo #
+create table assets (
+  item_name varchar(32) primary key, -- A common attribute for all items
+  dynamic_cols  blob  -- Dynamic columns will be stored here
+);
+
+INSERT INTO assets
+  VALUES ('MariaDB T-shirt',
+    COLUMN_CREATE('color', 'blue', 'size', 'XL'));
+INSERT INTO assets
+  VALUES ('Thinkpad Laptop',
+    COLUMN_CREATE('color', 'black', 'price', 500));
+INSERT INTO assets
+  VALUES ('Fridge',
+    COLUMN_CREATE('color', 'white', 'warranty', '5 years'));
+INSERT INTO assets
+  VALUES ('Microwave',
+    COLUMN_CREATE('warranty', '3 years'));
+SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color
+  FROM assets ORDER BY item_name;
+UPDATE assets SET dynamic_cols=COLUMN_DELETE(dynamic_cols, 'color')
+  WHERE item_name='Fridge';
+SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color
+  FROM assets ORDER BY item_name;
+DELETE FROM assets
+ WHERE item_name in
+  (select b.item_name from assets b
+    where COLUMN_GET(b.dynamic_cols, 'color' as char) ='black');
+SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color
+  FROM assets ORDER BY item_name;
+DELETE FROM assets WHERE item_name='Microwave';
+SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color
+  FROM assets ORDER BY item_name;
+drop table assets ;
+
+
+--echo #
+--echo # Test on fulltext columns
+--echo #
+CREATE TABLE ft2(copy TEXT,FULLTEXT(copy));
+INSERT INTO ft2(copy) VALUES
+ ('MySQL vs MariaDB database'),
+ ('Oracle vs MariaDB database'),
+ ('PostgreSQL vs MariaDB database'),
+ ('MariaDB overview'),
+ ('Foreign keys'),
+ ('Primary keys'),
+ ('Indexes'),
+ ('Transactions'),
+ ('Triggers');
+
+SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database');
+DELETE FROM ft2 WHERE  MATCH(copy) AGAINST('database');
+SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database');
+drop table ft2;
+
diff --git a/mysql-test/include/delete_use_source_cases.inc b/mysql-test/include/delete_use_source_cases.inc
new file mode 100644
index 00000000000..24408b7dd50
--- /dev/null
+++ b/mysql-test/include/delete_use_source_cases.inc
@@ -0,0 +1,214 @@
+--echo #
+--echo # Delete with value from subquery on the same table
+--echo #
+
+analyze table t1 persistent for all;
+
+let $c = c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain select * from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete with EXISTS subquery over the updated table
+--echo # in WHERE + possibly sargable condition
+--echo #
+
+analyze table t1 persistent for all;
+
+let $c = c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain select * from t1 where $c;
+eval explain $q;
+eval analyze $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete with IN predicand over the updated table in WHERE
+--echo #
+let $c = c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain select * from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete with a limit - can be deleted
+--echo #
+let $c = c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain select * from t1 where $c;
+eval explain $q;
+eval analyze $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete with a limit and an order by
+--echo #
+
+let $c = c1 in (select a.c2 from t1 a where a.c3 = t1.c3)
+                order by c3 desc limit 1;
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain select * from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete: 2 execution of PS
+--echo #
+
+prepare create_tmp_stmt from
+    "create table tmp as select * from t1
+      where c2=(select a.c3 from t1 a where a.c3 = ?)";
+prepare delete_t1_stmt from
+    "delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)";
+set @a:=5;
+execute create_tmp_stmt using @a;
+execute delete_t1_stmt using @a;
+execute delete_t1_stmt using @a;
+--sorted_result
+select * from t1;
+
+prepare insert_tmp_stmt from
+    "insert into tmp(c1,c2,c3) select * from t1
+      where c2=(select a.c3 from t1 a where a.c3 = ?)";
+set @a:=2;
+execute insert_tmp_stmt using @a;
+execute delete_t1_stmt using @a;
+--sorted_result
+select * from t1;
+
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+--sorted_result
+select * from t1;
+
+drop table tmp;
+
+--echo #
+--echo # Delete in stored procedure
+--echo #
+
+delimiter //;
+create procedure sp()
+begin
+  delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3)
+    order by c3 desc limit 1;
+end
+//
+delimiter ;//
+
+create table tmp as select * from t1
+  where c1 in (select a.c2 from t1 a where a.c3 = t1.c3)
+    order by c3 desc limit 1;
+CALL sp;
+insert into tmp(c1,c2,c3) select * from t1
+  where c1 in (select a.c2 from t1 a where a.c3 = t1.c3)
+    order by c3 desc limit 1;
+CALL sp;
+insert into tmp(c1,c2,c3) select * from t1
+  where c1 in (select a.c2 from t1 a where a.c3 = t1.c3)
+    order by c3 desc limit 1;
+CALL sp;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+
+--echo #
+--echo # Delete in stored function
+--echo #
+delimiter //;
+create function f1(IN a INT) returns int
+begin
+  delete from t1 where c3 < a  order by c3 limit 1;
+  return  1;
+end;//
+delimiter ;//
+
+set @a:=7;
+create table tmp as select * from t1 where c3 < @a
+  order by c3 limit 1;
+select f1(@a);
+insert into tmp(c1,c2,c3) select * from t1 where c3 < @a
+  order by c3 limit 1;
+select f1(@a);
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+drop table tmp;
+
+--echo #
+--echo # Delete in trigger
+--echo #
+
+create table t2 (c1 integer);
+insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8);
+
+CREATE TABLE cnt(del integer);
+INSERT INTO cnt VALUES(0);
+
+CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW
+  UPDATE cnt SET del=del+1;
+CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW
+  DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2);
+
+CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3;
+--enable_info ONCE
+DELETE FROM t1 WHERE c2>=3;
+
+--sorted_result
+select * from t1;
+--sorted_result
+SELECT * FROM t2;
+SELECT * FROM cnt;
+
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+
+DROP TRIGGER tr1;
+DROP TRIGGER tr2;
+drop table t2, cnt, tmp;
+
+--echo #
+--echo # Delete with a reference to view in subquery
+--echo #
+let $c = t1.c2 in ( select max(a.c2) from v1 a
+                      where a.c1 = t1.c1);
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain select * from t1 where $c;
+eval explain $q;
+eval analyze $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
diff --git a/mysql-test/include/delete_use_source_cases_memory.inc b/mysql-test/include/delete_use_source_cases_memory.inc
new file mode 100644
index 00000000000..7a61b59e2a5
--- /dev/null
+++ b/mysql-test/include/delete_use_source_cases_memory.inc
@@ -0,0 +1,294 @@
+--echo #
+--echo # Delete with value from subquery on the same table
+--echo #
+
+analyze table t1 persistent for all;
+
+let $c = c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain select * from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete with EXISTS subquery over the updated table
+--echo # in WHERE + possibly sargable condition
+--echo #
+
+analyze table t1 persistent for all;
+
+let $c = c1 = 1 and exists (select 'X' from t1 a where a.c1 = t1.c2);
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain select * from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete with EXISTS subquery over the updated table
+--echo # in WHERE + non-sargable condition
+--echo #
+
+analyze table t1 persistent for all;
+
+let $c = exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3;
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain select * from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete with order by
+--echo #
+
+analyze table t1 persistent for all;
+
+let $c = exists (select 'X' from t1 a where a.c2 = t1.c2)
+          and c2 >= 3 order by c2;
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain select * from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete with IN predicand over the updated table in WHERE
+--echo #
+let $c = c3 in (select distinct a.c1 from t1 a where t1.c2=a.c2);
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain select * from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete with a limit - can be deleted
+--echo #
+let $c = c1 in (select a.c2 from t1 a where a.c2 = t1.c3) limit 1;
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain select * from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete with a limit and an order by
+--echo #
+
+let $c = c1 in (select a.c2 from t1 a where a.c3 = t1.c3)
+                order by c3 desc limit 1;
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain select * from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete: 2 execution of PS
+--echo #
+
+prepare create_tmp_stmt from
+    "create table tmp as select * from t1
+      where c2=(select a.c3 from t1 a where a.c3 = ?)";
+prepare delete_t1_stmt from
+    "delete from t1 where c2=(select a.c3 from t1 a where a.c3 = ?)";
+set @a:=5;
+execute create_tmp_stmt using @a;
+execute delete_t1_stmt using @a;
+execute delete_t1_stmt using @a;
+--sorted_result
+select * from t1;
+
+prepare insert_tmp_stmt from
+    "insert into tmp(c1,c2,c3) select * from t1
+      where c2=(select a.c3 from t1 a where a.c3 = ?)";
+set @a:=2;
+execute insert_tmp_stmt using @a;
+execute delete_t1_stmt using @a;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+--sorted_result
+select * from t1;
+
+drop table tmp;
+
+--echo #
+--echo # Delete in stored procedure
+--echo #
+
+delimiter //;
+create procedure sp()
+begin
+  delete from t1 where c1 in (select a.c2 from t1 a where a.c3 = t1.c3)
+    order by c3 desc limit 1;
+end
+//
+delimiter ;//
+
+create table tmp as select * from t1
+  where c1 in (select a.c2 from t1 a where a.c3 = t1.c3)
+    order by c3 desc limit 1;
+CALL sp;
+insert into tmp(c1,c2,c3) select * from t1
+  where c1 in (select a.c2 from t1 a where a.c3 = t1.c3)
+    order by c3 desc limit 1;
+CALL sp;
+insert into tmp(c1,c2,c3) select * from t1
+  where c1 in (select a.c2 from t1 a where a.c3 = t1.c3)
+    order by c3 desc limit 1;
+CALL sp;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop procedure sp;
+drop table tmp;
+
+--echo #
+--echo # Delete in stored function
+--echo #
+delimiter //;
+create function f1(IN a INT) returns int
+begin
+  delete from t1 where c3 < a  order by c3 limit 1;
+  return  1;
+end;//
+delimiter ;//
+
+set @a:=7;
+create table tmp as select * from t1 where c3 < @a
+  order by c3 limit 1;
+select f1(@a);
+insert into tmp(c1,c2,c3) select * from t1 where c3 < @a
+  order by c3 limit 1;
+select f1(@a);
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop function f1;
+drop table tmp;
+
+--echo #
+--echo # Delete in trigger
+--echo #
+
+create table t2 (c1 integer);
+insert into t2(c1) values (1), (2), (3), (4), (5), (6), (7), (8);
+
+CREATE TABLE cnt(del integer);
+INSERT INTO cnt VALUES(0);
+
+CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW
+  UPDATE cnt SET del=del+1;
+CREATE TRIGGER tr2 AFTER DELETE ON t1 FOR EACH ROW
+  DELETE FROM t2 WHERE c1> (SELECT count(*)-1 FROM t2);
+
+CREATE TABLE tmp as SELECT * FROM t1 WHERE c2>=3;
+--enable_info ONCE
+DELETE FROM t1 WHERE c2>=3;
+
+--sorted_result
+select * from t1;
+--sorted_result
+SELECT * FROM t2;
+SELECT * FROM cnt;
+
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+
+DROP TRIGGER tr1;
+DROP TRIGGER tr2;
+drop table t2, cnt, tmp;
+
+--echo #
+--echo Delete with a reference to view in subquery
+--echo #
+let $c = t1.c2 in ( select max(a.c2) from v1 a
+                      where a.c1 = t1.c1);
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain select * from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete from view
+--echo #
+
+analyze table t1 persistent for all;
+
+let $c = v1.c1 in
+        (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5;
+eval create table tmp as select * from v1 where $c;
+let $q = delete from v1 where $c;
+eval explain select * from v1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete from view using reference
+--echo # to the same view in subquery
+--echo #
+
+analyze table t1 persistent for all;
+
+let $c = v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
+          and c1 = 2
+          and exists (select 'X' from v1 a where a.c1 = v1.c1);
+eval create table tmp as select * from v1 where $c;
+let $q = delete from v1 where $c;
+eval explain select * from v1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
diff --git a/mysql-test/include/delete_use_source_cases_non_innodb.inc b/mysql-test/include/delete_use_source_cases_non_innodb.inc
new file mode 100644
index 00000000000..caa581716dc
--- /dev/null
+++ b/mysql-test/include/delete_use_source_cases_non_innodb.inc
@@ -0,0 +1,81 @@
+--echo #
+--echo # Delete with EXISTS subquery over the updated table
+--echo # in WHERE + non-sargable condition
+--echo #
+
+analyze table t1 persistent for all;
+
+let $c = exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 = 3;
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+eval explain select * from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete with order by
+--echo #
+
+analyze table t1 persistent for all;
+
+let $c = exists (select 'X' from t1 a where a.c2 = t1.c2)
+          and c2 >= 3 order by c2;
+eval create table tmp as select * from t1 where $c;
+let $q = delete from t1 where $c;
+--replace_column 9 #
+eval explain select * from t1 where $c;
+--replace_column 9 #
+eval explain $q;
+--replace_column 9 #
+eval analyze $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete from view
+--echo #
+
+analyze table t1 persistent for all;
+
+let $c = v1.c1 in
+        (select max(a.c1) from t1 a where a.c2 = v1.c2) and c3 = 5;
+eval create table tmp as select * from v1 where $c;
+let $q = delete from v1 where $c;
+eval explain select * from v1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
+
+--echo #
+--echo # Delete from view using reference
+--echo # to the same view in subquery
+--echo #
+
+analyze table t1 persistent for all;
+
+let $c = v1.c2 in (select max(a.c2) from t1 a where a.c3 = v1.c3)
+          and c1 = 2
+          and exists (select 'X' from v1 a where a.c1 = v1.c1);
+eval create table tmp as select * from v1 where $c;
+let $q = delete from v1 where $c;
+--replace_column 9 #
+eval explain select * from v1 where $c;
+--replace_column 9 #
+eval explain $q;
+--replace_column 9 #
+eval analyze $q;
+--sorted_result
+select * from t1;
+insert into t1(c1,c2,c3) select c1,c2,c3 from tmp;
+drop table tmp;
diff --git a/mysql-test/include/delete_use_source_innodb.inc b/mysql-test/include/delete_use_source_innodb.inc
new file mode 100644
index 00000000000..83915d47656
--- /dev/null
+++ b/mysql-test/include/delete_use_source_innodb.inc
@@ -0,0 +1,91 @@
+create table t1 (c1 integer, c2 integer, c3 integer);
+
+insert into t1(c1,c2,c3)
+        values (1,1,1),(1,2,2),(1,3,3),
+               (2,1,4),(2,2,5),(2,3,6),
+               (2,4,7),(2,5,8);
+insert into t1 select c1+10,c2,c3+10 from t1;
+insert into t1 select c1+20,c2+1,c3+20 from t1;
+analyze table t1 persistent for all;
+
+create view v1 as select * from t1 where c2=2;
+
+--echo #######################################
+--echo #        Test without any index       #
+--echo #######################################
+--source include/delete_use_source_cases.inc
+
+--echo #######################################
+--echo #          Test with an index         #
+--echo #######################################
+create index t1_c2 on t1 (c2,c1);
+--source include/delete_use_source_cases.inc
+
+--echo #######################################
+--echo #       Test with a primary key       #
+--echo #######################################
+drop index t1_c2 on t1;
+alter table t1 add primary key (c3);
+--source include/delete_use_source_cases.inc
+
+drop view v1;
+drop table t1;
+
+--echo #
+--echo # Test on dynamic columns (blob)
+--echo #
+create table assets (
+  item_name varchar(32) primary key, -- A common attribute for all items
+  dynamic_cols  blob  -- Dynamic columns will be stored here
+);
+
+INSERT INTO assets
+  VALUES ('MariaDB T-shirt',
+    COLUMN_CREATE('color', 'blue', 'size', 'XL'));
+INSERT INTO assets
+  VALUES ('Thinkpad Laptop',
+    COLUMN_CREATE('color', 'black', 'price', 500));
+INSERT INTO assets
+  VALUES ('Fridge',
+    COLUMN_CREATE('color', 'white', 'warranty', '5 years'));
+INSERT INTO assets
+  VALUES ('Microwave',
+    COLUMN_CREATE('warranty', '3 years'));
+SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color
+  FROM assets ORDER BY item_name;
+UPDATE assets SET dynamic_cols=COLUMN_DELETE(dynamic_cols, 'color')
+  WHERE item_name='Fridge';
+SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color
+  FROM assets ORDER BY item_name;
+DELETE FROM assets
+ WHERE item_name in
+  (select b.item_name from assets b
+    where COLUMN_GET(b.dynamic_cols, 'color' as char) ='black');
+SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color
+  FROM assets ORDER BY item_name;
+DELETE FROM assets WHERE item_name='Microwave';
+SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color
+  FROM assets ORDER BY item_name;
+drop table assets ;
+
+
+--echo #
+--echo # Test on fulltext columns
+--echo #
+CREATE TABLE ft2(copy TEXT,FULLTEXT(copy));
+INSERT INTO ft2(copy) VALUES
+ ('MySQL vs MariaDB database'),
+ ('Oracle vs MariaDB database'),
+ ('PostgreSQL vs MariaDB database'),
+ ('MariaDB overview'),
+ ('Foreign keys'),
+ ('Primary keys'),
+ ('Indexes'),
+ ('Transactions'),
+ ('Triggers');
+
+SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database');
+DELETE FROM ft2 WHERE  MATCH(copy) AGAINST('database');
+SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database');
+drop table ft2;
+
diff --git a/mysql-test/include/delete_use_source_memory.inc b/mysql-test/include/delete_use_source_memory.inc
new file mode 100644
index 00000000000..dbaae1a6120
--- /dev/null
+++ b/mysql-test/include/delete_use_source_memory.inc
@@ -0,0 +1,33 @@
+create table t1 (c1 integer, c2 integer, c3 integer);
+
+insert into t1(c1,c2,c3)
+        values (1,1,1),(1,2,2),(1,3,3),
+               (2,1,4),(2,2,5),(2,3,6),
+               (2,4,7),(2,5,8);
+insert into t1 select c1+10,c2,c3+10 from t1;
+insert into t1 select c1+20,c2+1,c3+20 from t1;
+analyze table t1 persistent for all;
+
+create view v1 as select * from t1 where c2=2;
+
+--echo #######################################
+--echo #        Test without any index       #
+--echo #######################################
+--source include/delete_use_source_cases_memory.inc
+
+--echo #######################################
+--echo #          Test with an index         #
+--echo #######################################
+create index t1_c2 on t1 (c2,c1);
+--source include/delete_use_source_cases_memory.inc
+
+--echo #######################################
+--echo #       Test with a primary key       #
+--echo #######################################
+drop index t1_c2 on t1;
+alter table t1 add primary key (c3);
+--source include/delete_use_source_cases_memory.inc
+
+drop view v1;
+drop table t1;
+
diff --git a/mysql-test/include/update_use_source.inc b/mysql-test/include/update_use_source.inc
index 3c487704233..e1b66d2b996 100644
--- a/mysql-test/include/update_use_source.inc
+++ b/mysql-test/include/update_use_source.inc
@@ -1,150 +1,188 @@
 # Include to test update with same table as source and target
 
---echo #
---echo # Update a with value from subquery on the same table, no search clause. ALL access
---echo #
-
-#Enable view protocol  after fix MDEV-29207
---disable_view_protocol
-start transaction;
+create table t1 (old_c1 integer,
+                 old_c2 integer,
+                 c1 integer,
+                 c2 integer,
+                 c3 integer);
+
+create view v1 as select * from t1 where c2=2;
+delimiter /;
+create trigger trg_t1 before update on t1 for each row
+begin
+  set new.old_c1=old.c1;
+  set new.old_c2=old.c2;
+end;
+/
+delimiter ;/
+
+insert into t1(c1,c2,c3)
+        values (1,1,1), (1,2,2), (1,3,3),
+               (2,1,4), (2,2,5), (2,3,6),
+               (2,4,7), (2,5,8);
+insert into t1 select NULL, NULL, c1+10,c2,c3+10 from t1;
+insert into t1 select NULL, NULL, c1+20,c2+1,c3+20 from t1;
+analyze table t1 persistent for all;
+
+create table tmp as select * from t1;
+
+--echo #######################################
+--echo #        Test without any index       #
+--echo #######################################
+--source include/update_use_source_cases.inc
+
+--echo #######################################
+--echo #          Test with an index         #
+--echo #######################################
+create index t1_c2 on t1 (c2,c1);
+analyze table t1;
+--source include/update_use_source_cases.inc
+
+--echo #######################################
+--echo #       Test with a primary key       #
+--echo #######################################
+drop index t1_c2 on t1;
+alter table t1 add primary key (c3);
+analyze table t1;
+--source include/update_use_source_cases.inc
+
+--echo # Update with error "Subquery returns more than 1 row"
+--error ER_SUBQUERY_NO_1_ROW
+update t1 set c2=(select c2 from t1);
+--sorted_result
+select c1,c2,c3 from t1;
+
+--echo # Update with error "Subquery returns more than 1 row"
+--echo # and order by
+--error ER_SUBQUERY_NO_1_ROW
+update t1 set c2=(select c2 from t1) order by c3;
+--sorted_result
+select c1,c2,c3 from t1;
+
+-- echo # Duplicate value on update a primary key
+--error ER_DUP_ENTRY
+
+update t1 set c3=0
+    where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+--sorted_result
+select c1,c2,c3 from t1;
+truncate table t1;
+insert into t1 select * from tmp;
+
+-- echo # Duplicate value on update a primary key with ignore
 --enable_info ONCE
-update t1
-   set c1=(select a.c3
-             from t1 a
-            where a.c3 = t1.c3);
-select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
-rollback;
-
---echo #
---echo # Update with search clause on the same table
---echo #
-
-start transaction;
+update ignore t1 set c3=0
+    where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+--sorted_result
+select c1,c2,c3 from t1;
+truncate table t1;
+insert into t1 select * from tmp;
+
+-- echo # Duplicate value on update a primary key and limit
+--error ER_DUP_ENTRY
+update t1 set c3=0
+    where exists (select 'X' from t1 a where a.c2 = t1.c2)
+      and c2 >= 3 limit 2;
+--sorted_result
+select c1,c2,c3 from t1;
+truncate table t1;
+insert into t1 select * from tmp;
+
+-- echo # Duplicate value on update a primary key with ignore
+-- echo # and limit
 --enable_info ONCE
-update t1
-   set c1=10
- where c1 <2
-   and exists (select 'X'
-                 from t1 a
-                where a.c1 = t1.c1);
-select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
-rollback;
-
---echo #
---echo # Update via RANGE or INDEX access if an index or a primary key exists
---echo #
-
-explain update t1 set c1=0 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 > 3;
-start transaction;
+update ignore t1 set c3=0
+    where exists (select 'X' from t1 a where a.c2 = t1.c2)
+    and c2 >= 3 limit 2;
+--sorted_result
+select c1,c2,c3 from t1;
+truncate table t1;
+insert into t1 select * from tmp;
+
+--echo # Update no rows found
 --enable_info ONCE
-update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
-select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
-rollback;
-
---echo #
---echo # Update with order by
---echo #
-
-start transaction;
-update t1 set c1=c1+10 where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3 order by c2;
-select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
-rollback;
-
---echo #
---echo Update using a view in subquery
---echo #
-
-start transaction;
+update t1 set c1=10
+    where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c1 + 10);
+--sorted_result
+select c1,c2,c3 from t1;
+truncate table t1;
+insert into t1 select * from tmp;
+
+--echo # Update no rows changed
+drop trigger trg_t1;
 --enable_info ONCE
-update t1
-   set c1=c1 +(select max(a.c2)
-             from v1 a
-            where a.c1 = t1.c1) ;
-select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
-rollback;
+update t1 set c1=c1
+    where c1 <2 and exists (select 'X' from t1 a where a.c1 = t1.c1);
+--sorted_result
+select c1,c2,c3 from t1;
+truncate table t1;
+insert into t1 select * from tmp;
 
 --echo #
---echo # Update throw a view
+--echo # Check call of after trigger
 --echo #
 
-start transaction;
---enable_info ONCE
-update v1
-   set c1=c1 + (select max(a.c2)
-             from t1 a
-            where a.c1 = v1.c1) +10
-where c3 > 3;
-select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
-rollback;
-
---echo #
---echo # Update through a view and using the view in subquery
---echo #
+delimiter /;
+create or replace trigger trg_t2 after update on t1 for each row
+begin
+  declare msg varchar(100);
+  if (new.c3 = 5) then
+    set msg=concat('in after update trigger on ',new.c3);
+    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
+  end if;
+end;
+/
+delimiter ;/
+--error 1644
 
-start transaction;
---enable_info ONCE
-update v1
-   set c1=c1 + 1
- where c1 <2
-   and exists (select 'X'
-                 from v1 a
-                where a.c1 = v1.c1);
-select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
-rollback;
+update t1 set c1=2
+    where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1);
+--sorted_result
+select c1,c2,c3 from t1;
+truncate table t1;
+insert into t1 select * from tmp;
 
 --echo #
---echo # Update through  a view and using the view in subquery
+--echo # Check update with order by and after trigger
 --echo #
 
-start transaction;
---enable_info ONCE
-update v1
-   set c1=(select max(a.c1)+10
-             from v1 a
-            where a.c1 = v1.c1)
- where c1 <10
-   and exists (select 'X'
-                 from v1 a
-                where a.c2 = v1.c2);
-select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
-rollback;
+--error 1644
+update t1 set c1=2
+    where c3 in (select distinct a.c3 from t1 a where a.c1=t1.c1)
+    order by t1.c2, t1.c1;
+--sorted_result
+select c1,c2,c3 from t1;
+truncate table t1;
+insert into t1 select * from tmp;
+
+drop view v1;
 
 --echo #
---echo # Update of the index or primary key (c3)
+--echo # Check update on view with check option
 --echo #
 
-start transaction;
-explain update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
---enable_info ONCE
-update t1 set c3=c3+10 where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
-select c3 from t1;
-rollback;
+create view v1 as select * from t1 where c2=2 with check option;
 
---echo #
---echo # update with a limit
---echo #
+-- error 1369
+update v1 set c2=3 where c1=1;
+--sorted_result
+select c1,c2,c3 from t1;
+truncate table t1;
+insert into t1 select * from tmp;
 
-start transaction;
---enable_info ONCE
-update t1
-   set c1=(select a.c3
-             from t1 a
-            where a.c3 = t1.c3)
-    limit 2;
-select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
-rollback;
+-- error 1369
+update v1 set c2=(select max(c3) from v1) where c1=1;
+--sorted_result
+select c1,c2,c3 from t1;
+truncate table t1;
+insert into t1 select * from tmp;
 
---echo #
---echo # update with a limit and an order by
---echo #
+update v1 set c2=(select min(va.c3) from v1 va), c1=0 where c1=1;
+--sorted_result
+select c1,c2,c3 from t1;
+truncate table t1;
+insert into t1 select * from tmp;
 
-start transaction;
---enable_info ONCE
-update t1
-   set c1=(select a.c3
-             from t1 a
-            where a.c3 = t1.c3)
-  order by c3 desc limit 2;
-select concat(old_c1,'->',c1),c3, case when c1 != old_c1 then '*' else ' ' end "Changed" from t1 ;
-rollback;
---enable_view_protocol
+drop table tmp;
+drop view v1;
+drop table t1;
diff --git a/mysql-test/include/update_use_source_cases.inc b/mysql-test/include/update_use_source_cases.inc
new file mode 100644
index 00000000000..f1025f75cb1
--- /dev/null
+++ b/mysql-test/include/update_use_source_cases.inc
@@ -0,0 +1,183 @@
+--echo #
+--echo # Update with value from subquery on the same table
+--echo #
+
+analyze table t1 persistent for all;
+let $q = update t1 set c1=(select a.c3 from t1 a where a.c3 = t1.c3);
+eval explain select * from t1;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+select concat(old_c1,'->',c1),c3,
+   case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+truncate table t1;
+insert into t1 select * from tmp;
+
+--echo #
+--echo # Update with EXISTS subquery over the updated table
+--echo # in WHERE + possibly sargable condition
+--echo #
+
+analyze table t1 persistent for all;
+let $c = c1 <2
+          and exists (select 'X' from t1 a where a.c1 = t1.c1);
+let $q = update t1 set c1=10 where $c;
+eval explain select * from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+select concat(old_c1,'->',c1),c3,
+   case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+truncate table t1;
+insert into t1 select * from tmp;
+
+--echo #
+--echo # Update with EXISTS subquery over the updated table
+--echo # in WHERE + non-sargable condition
+--echo #
+
+analyze table t1 persistent for all;
+let $c = exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+let $q = update t1 set c1=c1+10 where $c;
+eval explain select * from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+select concat(old_c1,'->',c1),c3,
+   case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+truncate table t1;
+insert into t1 select * from tmp;
+
+--echo #
+--echo # Update with order by
+--echo #
+
+analyze table t1 persistent for all;
+let $c = exists (select 'X' from t1 a where a.c2 = t1.c2)
+            and c2 >= 3 order by c2;
+let $q = update t1 set c1=c1+10 where $c;
+eval explain select * from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+select concat(old_c1,'->',c1),c3,
+   case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+truncate table t1;
+insert into t1 select * from tmp;
+
+--echo #
+--echo # Update with a reference to view in subquery
+--echo # in settable value
+--echo #
+
+analyze table t1 persistent for all;
+let $q = update t1 set c1=c1 +(select max(a.c2) from v1 a
+                              where a.c1 = t1.c1);
+eval explain select * from t1;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+select concat(old_c1,'->',c1),c3,
+   case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+truncate table t1;
+insert into t1 select * from tmp;
+
+--echo #
+--echo # Update view
+--echo #
+
+analyze table t1 persistent for all;
+let $q = update v1 set c1=c1 + (select max(a.c2) from t1 a
+                                 where a.c1 = v1.c1) +10 where c3 > 3;
+eval explain select * from v1;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+select concat(old_c1,'->',c1),c3,
+   case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+truncate table t1;
+insert into t1 select * from tmp;
+
+--echo #
+--echo # Update view with reference to the same view in subquery
+--echo #
+
+analyze table t1 persistent for all;
+let $c = c1 <2
+         and exists (select 'X' from v1 a where a.c1 = v1.c1);
+let $q = update v1 set c1=c1 + 1 where $c;
+eval explain select * from v1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+select concat(old_c1,'->',c1),c3,
+   case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+truncate table t1;
+insert into t1 select * from tmp;
+
+--echo #
+--echo # Update view with EXISTS and reference to the same view in subquery
+--echo #
+
+analyze table t1 persistent for all;
+let $c = c1 <10 and exists (select 'X' from v1 a where a.c2 = v1.c2);
+let $q = update v1
+      set c1=(select max(a.c1)+10 from v1 a where a.c1 = v1.c1)
+          where $c;
+eval explain select * from v1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+select concat(old_c1,'->',c1),c3,
+   case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+truncate table t1;
+insert into t1 select * from tmp;
+
+--echo #
+--echo # Update with IN predicand over the updated table in WHERE
+--echo #
+
+analyze table t1 persistent for all;
+let $c = c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1);
+let $q = update t1 set c3=c3+110 where $c;
+eval explain select * from t1 where $c;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+--sorted_result
+select c3 from t1;
+truncate table t1;
+insert into t1 select * from tmp;
+
+--echo #
+--echo # Update with a limit
+--echo #
+
+analyze table t1 persistent for all;
+let $q = update t1
+         set c1=(select a.c3 from t1 a where a.c3 = t1.c3) limit 2;
+eval explain select * from t1 limit 2;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+select concat(old_c1,'->',c1),c3,
+   case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+truncate table t1;
+insert into t1 select * from tmp;
+
+--echo #
+--echo # Update with a limit and an order by
+--echo #
+
+analyze table t1 persistent for all;
+let $q=update t1
+         set c1=(select a.c3 from t1 a where a.c3 = t1.c3)
+            order by c3 desc limit 2;
+eval explain select * from t1 order by c3 desc limit 2;
+eval explain $q;
+--enable_info ONCE
+eval $q;
+select concat(old_c1,'->',c1),c3,
+   case when c1 != old_c1 then '*' else ' ' end "Changed" from t1;
+truncate table t1;
+insert into t1 select * from tmp;
diff --git a/mysql-test/include/update_use_source_ext.inc b/mysql-test/include/update_use_source_ext.inc
new file mode 100644
index 00000000000..004f7a96fd5
--- /dev/null
+++ b/mysql-test/include/update_use_source_ext.inc
@@ -0,0 +1,59 @@
+--echo #
+--echo # Test on dynamic columns (blob)
+--echo #
+
+create table assets (
+  item_name varchar(32) primary key, -- A common attribute for all items
+  dynamic_cols  blob  -- Dynamic columns will be stored here
+);
+INSERT INTO assets VALUES ('MariaDB T-shirt',
+    COLUMN_CREATE('color', 'blue', 'size', 'XL'));
+INSERT INTO assets VALUES ('Thinkpad Laptop',
+    COLUMN_CREATE('color', 'black', 'price', 500));
+SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color
+    FROM assets;
+UPDATE assets
+  SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years')
+    WHERE item_name='Thinkpad Laptop';
+SELECT item_name,
+  COLUMN_GET(dynamic_cols, 'warranty' as char) AS color
+    FROM assets;
+UPDATE assets
+  SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '4 years')
+    WHERE item_name in
+      (select b.item_name from assets b
+        where COLUMN_GET(b.dynamic_cols, 'color' as char) ='black');
+SELECT item_name,
+  COLUMN_GET(dynamic_cols, 'warranty' as char) AS color
+    FROM assets;
+
+UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty',
+    (select COLUMN_GET(b.dynamic_cols, 'color' as char)
+      from assets b
+      where assets.item_name = item_name));
+SELECT item_name,
+  COLUMN_GET(dynamic_cols, 'warranty' as char) AS color
+    FROM assets;
+drop table assets;
+
+--echo #
+--echo # Test on fulltext columns
+--echo #
+CREATE TABLE ft2(copy TEXT,FULLTEXT(copy));
+INSERT INTO ft2(copy) VALUES
+ ('MySQL vs MariaDB database'),
+ ('Oracle vs MariaDB database'),
+ ('PostgreSQL vs MariaDB database'),
+ ('MariaDB overview'),
+ ('Foreign keys'),
+ ('Primary keys'),
+ ('Indexes'),
+ ('Transactions'),
+ ('Triggers');
+SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database');
+
+update ft2 set copy = (select max(concat('mykeyword ',substr(b.copy,1,5)))
+  from ft2 b WHERE MATCH(b.copy) AGAINST('database'))
+  where MATCH(copy) AGAINST('keys');
+SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('mykeyword');
+drop table ft2;