MDEV-7487 Semi-join optimization for single-table update/delete statementsbb-10.10-mdev-7487

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>

1 files changed, 56 insertions, 7 deletions

diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index fa338f0e60c..cd88e7856a2 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -533,6 +533,31 @@ bool is_materialization_applicable(THD *thd, Item_in_subselect *in_subs,
 }
 
 
+/**
+  @brief Check whether an IN subquery must be excluded from conversion to SJ
+
+  @param thd  global context the processed statement
+  @returns true if the IN subquery must be excluded from conversion to SJ
+
+  @note
+  Currently a top level IN subquery of an update/delete statement
+  is not converted to SJ if the statement contains ORDER BY ... LIMIT
+  or contains RETURNING.  
+*/
+
+bool SELECT_LEX::is_sj_conversion_prohibited(THD *thd)
+{
+  DBUG_ASSERT(master_unit()->item->substype() == Item_subselect::IN_SUBS);
+  SELECT_LEX *outer_sl= outer_select();
+  return !outer_sl->outer_select() &&
+         (thd->lex->sql_command == SQLCOM_UPDATE ||
+          thd->lex->sql_command == SQLCOM_DELETE) &&
+	 ((outer_sl->order_list.elements &&
+           outer_sl->get_limit() != HA_POS_ERROR) ||
+          (thd->lex->sql_command == SQLCOM_DELETE &&
+           thd->lex->has_returning()));
+}
+
 /*
   Check if we need JOIN::prepare()-phase subquery rewrites and if yes, do them
 
@@ -675,9 +700,9 @@ int check_and_do_in_subquery_rewrites(JOIN *join)
         3. Subquery does not have GROUP BY or ORDER BY
         4. Subquery does not use aggregate functions or HAVING
         5. Subquery predicate is at the AND-top-level of ON/WHERE clause
-        6. We are not in a subquery of a single table UPDATE/DELETE that 
-             doesn't have a JOIN (TODO: We should handle this at some
-             point by switching to multi-table UPDATE/DELETE)
+        6. We are not in a top level subquery of a single-table UPDATE or
+	   DELETE with ORDER BY ... LIMIT and we are not in a top level
+           subquery of a single-table DELETE with returning
         7. We're not in a table-less subquery like "SELECT 1"
         8. No execution method was already chosen (by a prepared statement)
         9. Parent select is not a table-less select
@@ -692,9 +717,7 @@ int check_and_do_in_subquery_rewrites(JOIN *join)
         !select_lex->group_list.elements && !join->order &&           // 3
         !join->having && !select_lex->with_sum_func &&                // 4
         in_subs->emb_on_expr_nest &&                                  // 5
-        select_lex->outer_select()->join &&                           // 6
-        (!thd->lex->m_sql_cmd ||
-	 thd->lex->m_sql_cmd->sql_command_code() == SQLCOM_UPDATE_MULTI) &&
+        !select_lex->is_sj_conversion_prohibited(thd) &&              // 6
         parent_unit->first_select()->leaf_tables.elements &&          // 7
         !in_subs->has_strategy() &&                                   // 8
         select_lex->outer_select()->table_list.first &&               // 9
@@ -754,7 +777,8 @@ int check_and_do_in_subquery_rewrites(JOIN *join)
       */
       if (in_subs && !in_subs->has_strategy())
       {
-        if (is_materialization_applicable(thd, in_subs, select_lex))
+        if (!select_lex->is_sj_conversion_prohibited(thd) &&
+            is_materialization_applicable(thd, in_subs, select_lex))
         {
           in_subs->add_strategy(SUBS_MATERIALIZATION);
 
@@ -7260,3 +7284,28 @@ bool TABLE_LIST::is_sjm_scan_table()
 {
   return is_active_sjm() && sj_mat_info->is_sj_scan;
 }
+
+
+/**
+  @brief Check whether this subselect can be lifted to the top level
+  @returns true if the test is positive, false otherwise
+*/
+
+bool SELECT_LEX::is_sj_subselect_lifted_to_top()
+{
+  st_select_lex *sl= this;
+  st_select_lex *outer_sl= outer_select();
+  for ( ; outer_sl; sl= outer_sl, outer_sl= outer_sl->outer_select())
+  {
+    List_iterator_fast<Item_in_subselect> it(outer_sl->sj_subselects);
+    Item_in_subselect *in_subs;
+    while ((in_subs= it++))
+    {
+      if (in_subs->unit->first_select() == sl)
+        break;
+    }
+    if (!in_subs)
+      return false;
+  }
+  return true;
+}