MDEV-19491 update query stopped working after mariadb upgrade 10.2.23 -> 10.2.24

as well as

MDEV-19500 Update with join stopped worked if there is a call to a procedure in a trigger
MDEV-19521 Update Table Fails with Trigger and Stored Function
MDEV-19497 Replication stops because table not found
MDEV-19527 UPDATE + JOIN + TRIGGERS = table doesn't exists error

Reimplement the fix for (5d510fdbf00)

MDEV-18507 can't update temporary table when joined with table with triggers on read-only

instead of calling open_tables() twice, put multi-update
prepare code inside open_tables() loop.

Add a test for a MDL backoff-and-retry loop inside open_tables()
across multi-update prepare code.

1 files changed, 107 insertions, 97 deletions

diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index ac066507042..e01fe0926dd 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -1188,103 +1188,81 @@ bool unsafe_key_update(List<TABLE_LIST> leaves, table_map tables_for_update)
 }
 
 
-/*
-  make update specific preparation and checks after opening tables
+class Multiupdate_prelocking_strategy : public DML_prelocking_strategy
+{
+  bool done;
+  bool has_prelocking_list;
+public:
+  void reset(THD *thd);
+  bool handle_end(THD *thd);
+};
+
+void Multiupdate_prelocking_strategy::reset(THD *thd)
+{
+  done= false;
+  has_prelocking_list= thd->lex->requires_prelocking();
+}
 
-  SYNOPSIS
-    mysql_multi_update_prepare()
-    thd         thread handler
+/**
+  Determine what tables could be updated in the multi-update
 
-  RETURN
-    FALSE OK
-    TRUE  Error
+  For these tables we'll need to open triggers and continue prelocking
+  until all is open.
 */
-
-int mysql_multi_update_prepare(THD *thd)
+bool Multiupdate_prelocking_strategy::handle_end(THD *thd)
 {
+  DBUG_ENTER("Multiupdate_prelocking_strategy::handle_end");
+  if (done)
+    DBUG_RETURN(0);
+
   LEX *lex= thd->lex;
-  TABLE_LIST *table_list= lex->query_tables;
-  TABLE_LIST *tl;
-  List<Item> *fields= &lex->select_lex.item_list;
-  table_map tables_for_update;
-  bool update_view= 0;
-  DML_prelocking_strategy prelocking_strategy;
-  bool has_prelocking_list= thd->lex->requires_prelocking();
+  SELECT_LEX *select_lex= &lex->select_lex;
+  TABLE_LIST *table_list= lex->query_tables, *tl;
 
-  /*
-    if this multi-update was converted from usual update, here is table
-    counter else junk will be assigned here, but then replaced with real
-    count in open_tables()
-  */
-  uint  table_count= lex->table_count;
-  const bool using_lock_tables= thd->locked_tables_mode != LTM_NONE;
-  bool original_multiupdate= (thd->lex->sql_command == SQLCOM_UPDATE_MULTI);
-  DBUG_ENTER("mysql_multi_update_prepare");
+  done= true;
 
-  /* following need for prepared statements, to run next time multi-update */
-  thd->lex->sql_command= SQLCOM_UPDATE_MULTI;
+  if (mysql_handle_derived(lex, DT_INIT) ||
+      mysql_handle_derived(lex, DT_MERGE_FOR_INSERT) ||
+      mysql_handle_derived(lex, DT_PREPARE))
+    DBUG_RETURN(1);
 
   /*
-    Open tables and create derived ones, but do not lock and fill them yet.
-
-    During prepare phase acquire only S metadata locks instead of SW locks to
-    keep prepare of multi-UPDATE compatible with concurrent LOCK TABLES WRITE
-    and global read lock.
-  */
-  if ((original_multiupdate && open_tables(thd, &table_list, &table_count,
-                                           thd->stmt_arena->is_stmt_prepare()
-                                           ? MYSQL_OPEN_FORCE_SHARED_MDL : 0,
-                                           &prelocking_strategy)) ||
-      mysql_handle_derived(lex, DT_INIT))
-    DBUG_RETURN(TRUE);
-  /*
     setup_tables() need for VIEWs. JOIN::prepare() will call setup_tables()
     second time, but this call will do nothing (there are check for second
     call in setup_tables()).
   */
 
-  //We need to merge for insert prior to prepare.
-  if (mysql_handle_derived(lex, DT_MERGE_FOR_INSERT))
-    DBUG_RETURN(TRUE);
-  if (mysql_handle_derived(lex, DT_PREPARE))
-    DBUG_RETURN(TRUE);
-
-  if (setup_tables_and_check_access(thd, &lex->select_lex.context,
-                                    &lex->select_lex.top_join_list,
-                                    table_list, lex->select_lex.leaf_tables,
-                                    FALSE, UPDATE_ACL, SELECT_ACL, FALSE))
-    DBUG_RETURN(TRUE);
+  if (setup_tables_and_check_access(thd, &select_lex->context,
+        &select_lex->top_join_list, table_list, select_lex->leaf_tables,
+        FALSE, UPDATE_ACL, SELECT_ACL, FALSE))
+    DBUG_RETURN(1);
 
-  if (lex->select_lex.handle_derived(thd->lex, DT_MERGE))  
-    DBUG_RETURN(TRUE);
+  if (select_lex->handle_derived(thd->lex, DT_MERGE))
+    DBUG_RETURN(1);
 
+  List<Item> *fields= &lex->select_lex.item_list;
   if (setup_fields_with_no_wrap(thd, 0, *fields, MARK_COLUMNS_WRITE, 0, 0))
-    DBUG_RETURN(TRUE);
+    DBUG_RETURN(1);
 
   for (tl= table_list; tl ; tl= tl->next_local)
-  {
     if (tl->view)
     {
-      update_view= 1;
-      break;
+      if (check_fields(thd, *fields))
+        DBUG_RETURN(1);
+      else
+        break;
     }
-  }
 
-  if (update_view && check_fields(thd, *fields))
-    DBUG_RETURN(TRUE);
+  table_map tables_for_update= thd->table_map_for_update= get_table_map(fields);
 
-  thd->table_map_for_update= tables_for_update= get_table_map(fields);
-
-  if (unsafe_key_update(lex->select_lex.leaf_tables, tables_for_update))
-    DBUG_RETURN(true);
-
-  TABLE_LIST **new_tables= lex->query_tables_last;
-  DBUG_ASSERT(*new_tables== NULL);
+  if (unsafe_key_update(select_lex->leaf_tables, tables_for_update))
+    DBUG_RETURN(1);
 
   /*
     Setup timestamp handling and locking mode
   */
-  List_iterator<TABLE_LIST> ti(lex->select_lex.leaf_tables);
+  List_iterator<TABLE_LIST> ti(select_lex->leaf_tables);
+  const bool using_lock_tables= thd->locked_tables_mode != LTM_NONE;
   while ((tl= ti++))
   {
     TABLE *table= tl->table;
@@ -1299,7 +1277,7 @@ int mysql_multi_update_prepare(THD *thd)
       if (!tl->single_table_updatable() || check_key_in_view(thd, tl))
       {
         my_error(ER_NON_UPDATABLE_TABLE, MYF(0), tl->alias, "UPDATE");
-        DBUG_RETURN(TRUE);
+        DBUG_RETURN(1);
       }
 
       DBUG_PRINT("info",("setting table `%s` for update", tl->alias));
@@ -1310,8 +1288,8 @@ int mysql_multi_update_prepare(THD *thd)
       tl->updating= 1;
       if (tl->belong_to_view)
         tl->belong_to_view->updating= 1;
-      if (extend_table_list(thd, tl, &prelocking_strategy, has_prelocking_list))
-        DBUG_RETURN(TRUE);
+      if (extend_table_list(thd, tl, this, has_prelocking_list))
+        DBUG_RETURN(1);
     }
     else
     {
@@ -1333,15 +1311,62 @@ int mysql_multi_update_prepare(THD *thd)
     }
   }
 
-  uint addon_table_count= 0;
-  if (*new_tables)
+  /* check single table update for view compound from several tables */
+  for (tl= table_list; tl; tl= tl->next_local)
   {
-    Sroutine_hash_entry **new_routines= thd->lex->sroutines_list.next;
-    DBUG_ASSERT(*new_routines == NULL);
-    if (open_tables(thd, new_tables, &addon_table_count, new_routines,
-                    thd->stmt_arena->is_stmt_prepare()
-                    ? MYSQL_OPEN_FORCE_SHARED_MDL : 0,
-                    &prelocking_strategy))
+    TABLE_LIST *for_update= 0;
+    if (tl->is_merged_derived() &&
+        tl->check_single_table(&for_update, tables_for_update, tl))
+    {
+      my_error(ER_VIEW_MULTIUPDATE, MYF(0), tl->view_db.str, tl->view_name.str);
+      DBUG_RETURN(1);
+    }
+  }
+
+  DBUG_RETURN(0);
+}
+
+/*
+  make update specific preparation and checks after opening tables
+
+  SYNOPSIS
+    mysql_multi_update_prepare()
+    thd         thread handler
+
+  RETURN
+    FALSE OK
+    TRUE  Error
+*/
+
+int mysql_multi_update_prepare(THD *thd)
+{
+  LEX *lex= thd->lex;
+  TABLE_LIST *table_list= lex->query_tables;
+  TABLE_LIST *tl;
+  Multiupdate_prelocking_strategy prelocking_strategy;
+  uint table_count= lex->table_count;
+  DBUG_ENTER("mysql_multi_update_prepare");
+
+  /*
+    Open tables and create derived ones, but do not lock and fill them yet.
+
+    During prepare phase acquire only S metadata locks instead of SW locks to
+    keep prepare of multi-UPDATE compatible with concurrent LOCK TABLES WRITE
+    and global read lock.
+  */
+  if (thd->lex->sql_command == SQLCOM_UPDATE_MULTI)
+  {
+    if (open_tables(thd, &table_list, &table_count,
+        thd->stmt_arena->is_stmt_prepare() ? MYSQL_OPEN_FORCE_SHARED_MDL : 0,
+        &prelocking_strategy))
+      DBUG_RETURN(TRUE);
+  }
+  else
+  {
+    /* following need for prepared statements, to run next time multi-update */
+    thd->lex->sql_command= SQLCOM_UPDATE_MULTI;
+    prelocking_strategy.reset(thd);
+    if (prelocking_strategy.handle_end(thd))
       DBUG_RETURN(TRUE);
   }
 
@@ -1358,24 +1383,9 @@ int mysql_multi_update_prepare(THD *thd)
     }
   }
 
-  /* check single table update for view compound from several tables */
-  for (tl= table_list; tl; tl= tl->next_local)
-  {
-    if (tl->is_merged_derived())
-    {
-      TABLE_LIST *for_update= 0;
-      if (tl->check_single_table(&for_update, tables_for_update, tl))
-      {
-	my_error(ER_VIEW_MULTIUPDATE, MYF(0),
-		 tl->view_db.str, tl->view_name.str);
-	DBUG_RETURN(-1);
-      }
-    }
-  }
-
   /* now lock and fill tables */
   if (!thd->stmt_arena->is_stmt_prepare() &&
-      lock_tables(thd, table_list, table_count + addon_table_count, 0))
+      lock_tables(thd, table_list, table_count, 0))
   {
     DBUG_RETURN(TRUE);
   }
@@ -1387,7 +1397,7 @@ int mysql_multi_update_prepare(THD *thd)
   */
   lex->select_lex.exclude_from_table_unique_test= TRUE;
   /* We only need SELECT privilege for columns in the values list */
-  ti.rewind();
+  List_iterator<TABLE_LIST> ti(lex->select_lex.leaf_tables);
   while ((tl= ti++))
   {
     TABLE *table= tl->table;