MDEV-7487 Semi-join optimization for single-table update/delete statements

This patch allows to use semi-join optimization at the top level of
single-table update and delete statements.
The problem of supporting such optimization became easy to resolve after
processing a single-table update/delete statement started using JOIN
structure. This allowed to use JOIN::prepare() not only for multi-table
updates/deletes but for single-table ones as well. This was done in the
patch for mdev-28883:
Re-design the upper level of handling UPDATE and DELETE statements.

Note that JOIN::prepare() detects all subqueries that can be considered
as candidates for semi-join optimization. The code added by this patch
looks for such candidates at the top level and if such candidates are found
in the processed single-table update/delete the statement is handled in
the same way as a multi-table update/delete.

    Approved by Oleksandr Byelkin <sanja@mariadb.com>

6 files changed, 18 insertions, 11 deletions

diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index fbf8aaeed4f..9c8fc6dc0b3 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -567,11 +567,11 @@ bool SELECT_LEX::is_sj_conversion_prohibited(THD *thd)
   switch (thd->lex->sql_command) {
   case SQLCOM_UPDATE:
     return
-      !((Sql_cmd_update *) cmd)->is_multitable() ||
+      !((Sql_cmd_update *) cmd)->is_multitable() &&
       ((Sql_cmd_update *) cmd)->processing_as_multitable_update_prohibited(thd);
   case SQLCOM_DELETE:
     return
-      !((Sql_cmd_delete *) cmd)->is_multitable() ||
+      !((Sql_cmd_delete *) cmd)->is_multitable() &&
       ((Sql_cmd_delete *) cmd)->processing_as_multitable_delete_prohibited(thd);
   default:
     return false;
diff --git a/sql/opt_trace.cc b/sql/opt_trace.cc
index 3f2433fd8b9..1008690ccaa 100644
--- a/sql/opt_trace.cc
+++ b/sql/opt_trace.cc
@@ -491,8 +491,7 @@ void Opt_trace_start::init(THD *thd,
       !list_has_optimizer_trace_table(tbl) &&
       !sets_var_optimizer_trace(sql_command, set_vars) &&
       !thd->system_thread &&
-      !ctx->disable_tracing_if_required() &&
-      !(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_PREPARE))
+      !ctx->disable_tracing_if_required())
   {
     ctx->start(thd, tbl, sql_command, query, query_length, query_charset,
                thd->variables.optimizer_trace_max_mem_size);
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index f674243219b..bae35cd3264 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -1214,7 +1214,7 @@ retry:
         }
       }
       else if (thd->lex->sql_command == SQLCOM_DELETE)
-      {
+     {
         Sql_cmd_delete *cmd= (Sql_cmd_delete *) (thd->lex->m_sql_cmd);
         if (cmd->is_multitable() || derived->derived->outer_select())
           materialize= false;
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 6c8be793e78..18d4b4b6af5 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -348,7 +348,6 @@ bool Sql_cmd_delete::delete_from_single_table(THD *thd)
   query_plan.using_filesort= FALSE;
 
   THD_STAGE_INFO(thd, stage_init_update);
-  create_explain_query(thd->lex, thd->mem_root);
 
   const bool delete_history= table_list->vers_conditions.delete_history;
   DBUG_ASSERT(!(delete_history && table_list->period_conditions.is_set()));
@@ -1668,6 +1667,10 @@ bool Sql_cmd_delete::prepare_inner(THD *thd)
     {
       goto err;
     }
+
+    if (!multitable &&
+        select_lex->sj_subselects.elements)
+      multitable= true;
   }
 
   if (multitable)
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 6acd045e88e..6eda8ef2e88 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -5972,8 +5972,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
           s->needed_reg=select->needed_reg;
           select->quick=0;
           impossible_range= records == 0 && s->table->reginfo.impossible_range;
-          if (join->thd->lex->sql_command == SQLCOM_SELECT &&
-              optimizer_flag(join->thd, OPTIMIZER_SWITCH_USE_ROWID_FILTER))
+          if (optimizer_flag(join->thd, OPTIMIZER_SWITCH_USE_ROWID_FILTER))
             s->table->init_cost_info_for_usable_range_rowid_filters(join->thd);
         }
         if (!impossible_range)
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 4f0659d987a..ff1d70e7f8e 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -382,7 +382,6 @@ bool Sql_cmd_update::update_single_table(THD *thd)
   DBUG_ENTER("Sql_cmd_update::update_single_table");
 
   THD_STAGE_INFO(thd, stage_init_update);
-  create_explain_query(thd->lex, thd->mem_root);
 
   thd->table_map_for_update= 0;
 
@@ -2479,6 +2478,8 @@ int multi_update::do_updates()
     table = cur_table->table;
     if (table == table_to_update)
       continue;					// Already updated
+    if (table->file->pushed_rowid_filter)
+      table->file->disable_pushed_rowid_filter();
     org_updated= updated;
     tmp_table= tmp_tables[cur_table->shared];
     tmp_table->file->extra(HA_EXTRA_CACHE);	// Change to read cache
@@ -2673,7 +2674,8 @@ int multi_update::do_updates()
     check_opt_it.rewind();
     while (TABLE *tbl= check_opt_it++)
         tbl->file->ha_rnd_end();
-
+    if (table->file->save_pushed_rowid_filter)
+      table->file->enable_pushed_rowid_filter();
   }
   DBUG_RETURN(0);
 
@@ -2684,6 +2686,8 @@ err:
   }
 
 err2:
+  if (table->file->save_pushed_rowid_filter)
+    table->file->enable_pushed_rowid_filter();
   if (table->file->inited)
     (void) table->file->ha_rnd_end();
   if (tmp_table->file->inited)
@@ -2987,7 +2991,9 @@ bool Sql_cmd_update::prepare_inner(THD *thd)
     {
       goto err;
     }
-
+    if (!multitable &&
+        select_lex->sj_subselects.elements)
+      multitable= true;
   }
 
   if (table_list->has_period())