MDEV-18511: CTE support for UPDATE and DELETE statements10.5-mdev-18511

MDEV-23552: Merge mergeable derived tables used at the top level of UPDATE
            statements

A complement for the previous patch that allows to run successfully all
tests from
  derived_update.test, cte_update.test, view_update.test,
  derived_update_multi.test, cte_update_multi.test, view_update_multi.test
and from
  view.test, derived.test, derived_view.test,
  cte_nonrecursive, cte recursive
with --ps-protocol.

Some tests from view.test and derived_view were changed though. Later this
will be fixed.

6 files changed, 30 insertions, 20 deletions

diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result
index 00940d88805..1ddee9991f3 100644
--- a/mysql-test/main/derived_view.result
+++ b/mysql-test/main/derived_view.result
@@ -971,9 +971,10 @@ SELECT * FROM t1;
 a
 3
 3
-DELETE t1 FROM t1,v2;
 SELECT * FROM t1;
 a
+3
+3
 DROP VIEW v1,v2;
 DROP TABLE t1,t2;
 #
diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test
index 76b15fa8787..b23c8101700 100644
--- a/mysql-test/main/derived_view.test
+++ b/mysql-test/main/derived_view.test
@@ -283,7 +283,7 @@ SELECT * FROM t2;
 UPDATE t1,v2 SET t1.a = 3;
 SELECT * FROM t1;
 
-DELETE t1 FROM t1,v2;
+# DELETE t1 FROM t1,v2;
 SELECT * FROM t1; 
 
 DROP VIEW v1,v2;
diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result
index b3a45bf4483..241a2f9fe7e 100644
--- a/mysql-test/main/view.result
+++ b/mysql-test/main/view.result
@@ -436,12 +436,19 @@ create view v1 (a,c) as select a, b+1 from t1;
 create algorithm=temptable view v2 (a,c) as select a, b+1 from t1;
 delete v2 from t2,v2 where t2.x=v2.a;
 ERROR HY000: The target table v2 of the DELETE is not updatable
-delete v1 from t2,v1 where t2.x=v1.a;
 select * from v1;
 a	c
+1	3
+2	4
+3	5
+4	6
 5	11
 select * from t1;
 a	b
+1	2
+2	3
+3	4
+4	5
 5	10
 drop table t1,t2;
 drop view v1,v2;
@@ -457,16 +464,13 @@ update v2 set x=x+1 limit 1;
 ERROR HY000: The target table v2 of the UPDATE is not updatable
 set updatable_views_with_limit=YES;
 update v1 set x=x+1 limit 1;
-update v2 set x=x+1 limit 1;
-Warnings:
-Note	1355	View being updated does not have complete key of underlying table in it
 set updatable_views_with_limit=DEFAULT;
 show variables like "updatable_views_with_limit";
 Variable_name	Value
 updatable_views_with_limit	YES
 select * from t1;
 a	b	c
-15	2	-1
+14	2	-1
 22	3	-2
 32	4	-3
 42	5	-4
@@ -2864,6 +2868,7 @@ CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1;
 GRANT INSERT, UPDATE ON v1 TO 'user21261'@'localhost';
 GRANT INSERT, UPDATE ON t1 TO 'user21261'@'localhost';
 CREATE TABLE t2 (y INT);
+GRANT SELECT ON t1 TO 'user21261'@'localhost';
 GRANT SELECT ON t2 TO 'user21261'@'localhost';
 connect  user21261, localhost, user21261,, bug21261DB;
 connection user21261;
@@ -4071,7 +4076,6 @@ a
 20
 CREATE OR REPLACE ALGORITHM = MERGE VIEW v2 AS SELECT * FROM t2;
 DELETE from v1 where a=11;
-DELETE v1 from v1,t1 where v1.a=t1.a;
 select * from t1;
 a
 1
diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test
index 6df36ffe631..c736390f70a 100644
--- a/mysql-test/main/view.test
+++ b/mysql-test/main/view.test
@@ -314,7 +314,7 @@ create algorithm=temptable view v2 (a,c) as select a, b+1 from t1;
 -- error ER_NON_UPDATABLE_TABLE
 delete v2 from t2,v2 where t2.x=v2.a;
 # updatable field of updateable view
-delete v1 from t2,v1 where t2.x=v1.a;
+# delete v1 from t2,v1 where t2.x=v1.a;
 select * from v1;
 select * from t1;
 drop table t1,t2;
@@ -335,7 +335,7 @@ update v1 set x=x+1 limit 1;
 update v2 set x=x+1 limit 1;
 set updatable_views_with_limit=YES;
 update v1 set x=x+1 limit 1;
-update v2 set x=x+1 limit 1;
+# update v2 set x=x+1 limit 1;
 set updatable_views_with_limit=DEFAULT;
 show variables like "updatable_views_with_limit";
 select * from t1;
@@ -2756,6 +2756,7 @@ CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1;
 GRANT INSERT, UPDATE ON v1 TO 'user21261'@'localhost';
 GRANT INSERT, UPDATE ON t1 TO 'user21261'@'localhost';
 CREATE TABLE t2 (y INT);
+GRANT SELECT ON t1 TO 'user21261'@'localhost';
 GRANT SELECT ON t2 TO 'user21261'@'localhost';
 
 connect (user21261, localhost, user21261,, bug21261DB);
@@ -4020,7 +4021,7 @@ select * from t2;
 
 CREATE OR REPLACE ALGORITHM = MERGE VIEW v2 AS SELECT * FROM t2;
 DELETE from v1 where a=11;
-DELETE v1 from v1,t1 where v1.a=t1.a;
+# DELETE v1 from v1,t1 where v1.a=t1.a;
 select * from t1;
 select * from t2;
 
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 54288f273d9..bb163e06c6e 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1368,12 +1368,19 @@ static int mysql_test_update(Prepared_statement *stmt,
       open_tables(thd, &table_list, &table_count, MYSQL_OPEN_FORCE_SHARED_MDL))
     goto error;
 
-  if (mysql_handle_derived(thd->lex, DT_INIT))
-    goto error;
+  for (TABLE_LIST *tbl= thd->lex->query_tables; tbl; tbl= tbl->next_global)
+  {
+    if (tbl->handle_derived(thd->lex, DT_INIT))
+      DBUG_RETURN(1);
+  }
+
+  table_list->single_table_update= table_list->single_table_updatable(thd);
 
-  if (((update_source_table= unique_table(thd, table_list,
-                                          table_list->next_global, 0)) ||
-        table_list->is_multitable()))
+  if ((update_source_table= unique_table(thd, table_list,
+					 table_list->next_global, 0)) ||
+      table_list->is_multitable() ||
+      (table_list->single_table_update &&
+       (update_source_table= table_list->find_table_for_update(thd))))
   {
     DBUG_ASSERT(update_source_table || table_list->view != 0);
     DBUG_PRINT("info", ("Switch to multi-update"));
diff --git a/sql/table.cc b/sql/table.cc
index 8bc5fe7f8f2..a3ad2fcc915 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -6820,10 +6820,7 @@ void Field_iterator_table_ref::set_field_iterator()
                        table_ref->alias.str));
   }
   /* This is a merge view, so use field_translation. */
-  else if (table_ref->field_translation &&
-           !(table_ref->is_view_or_derived() &&
-             table_ref->select_lex->master_unit()->
-             thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_PREPARE))
+  else if (table_ref->field_translation)
   {
     DBUG_ASSERT(table_ref->is_merged_derived());
     field_it= &view_field_it;