Fix for bug lp:944706, task MDEV-193

The patch enables back constant subquery execution during
query optimization after it was disabled during the development
of MWL#89 (cost-based choice of IN-TO-EXISTS vs MATERIALIZATION).

The main idea is that constant subqueries are allowed to be executed
during optimization if their execution is not expensive.

The approach is as follows:
- Constant subqueries are recursively optimized in the beginning of
  JOIN::optimize of the outer query. This is done by the new method
  JOIN::optimize_constant_subqueries(). This is done so that the cost
  of executing these queries can be estimated.
- Optimization of the outer query proceeds normally. During this phase
  the optimizer may request execution of non-expensive constant subqueries.
  Each place where the optimizer may potentially execute an expensive
  expression is guarded with the predicate Item::is_expensive().
- The implementation of Item_subselect::is_expensive has been extended
  to use the number of examined rows (estimated by the optimizer) as a
  way to determine whether the subquery is expensive or not.
- The new system variable "expensive_subquery_limit" controls how many
  examined rows are considered to be not expensive. The default is 100.

In addition, multiple changes were needed to make this solution work
in the light of the changes made by MWL#89. These changes were needed
to fix various crashes and wrong results, and legacy bugs discovered
during development.

1 files changed, 24 insertions, 2 deletions

diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 6fd6e53424f..fb5307f9e3a 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -3405,7 +3405,23 @@ bool st_select_lex::add_index_hint (THD *thd, char *str, uint length)
 }
 
 
-bool st_select_lex::optimize_unflattened_subqueries()
+/**
+  Optimize all subqueries that have not been flattened into semi-joins.
+
+  @details
+  This functionality is a method of SELECT_LEX instead of JOIN because
+  SQL statements as DELETE/UPDATE do not have a corresponding JOIN object.
+
+  @see JOIN::optimize_unflattened_subqueries
+
+  @param const_only  Restrict subquery optimization to constant subqueries
+
+  @return Operation status
+  @retval FALSE     success.
+  @retval TRUE      error occurred.
+*/
+
+bool st_select_lex::optimize_unflattened_subqueries(bool const_only)
 {
   for (SELECT_LEX_UNIT *un= first_inner_unit(); un; un= un->next_unit())
   {
@@ -3415,11 +3431,17 @@ bool st_select_lex::optimize_unflattened_subqueries()
     {
       if (subquery_predicate->substype() == Item_subselect::IN_SUBS)
       {
-        Item_in_subselect *in_subs=(Item_in_subselect*)subquery_predicate;
+        Item_in_subselect *in_subs= (Item_in_subselect*) subquery_predicate;
         if (in_subs->is_jtbm_merged)
           continue;
       }
 
+      if (const_only && !subquery_predicate->const_item())
+      {
+        /* Skip non-constant subqueries if the caller asked so. */
+        continue;
+      }
+
       bool empty_union_result= true;
       /*
         If the subquery is a UNION, optimize all the subqueries in the UNION. If