MDEV-7486: Condition pushdown from HAVING into WHERE

Condition can be pushed from the HAVING clause into the WHERE clause
if it depends only on the fields that are used in the GROUP BY list
or depends on the fields that are equal to grouping fields.
Aggregate functions can't be pushed down.

How the pushdown is performed on the example:

SELECT t1.a,MAX(t1.b)
FROM t1
GROUP BY t1.a
HAVING (t1.a>2) AND (MAX(c)>12);

=>

SELECT t1.a,MAX(t1.b)
FROM t1
WHERE (t1.a>2)
GROUP BY t1.a
HAVING (MAX(c)>12);

The implementation scheme:

1. Extract the most restrictive condition cond from the HAVING clause of
   the select that depends only on the fields that are used in the GROUP BY
   list of the select (directly or indirectly through equalities)
2. Save cond as a condition that can be pushed into the WHERE clause
   of the select
3. Remove cond from the HAVING clause if it is possible

The optimization is implemented in the function
st_select_lex::pushdown_from_having_into_where().

New test file having_cond_pushdown.test is created.

1 files changed, 146 insertions, 64 deletions

diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 228fcd0f7e6..2e339f03540 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -5554,13 +5554,91 @@ int select_value_catcher::send_data(List<Item> &items)
 
 /**
   @brief
-    Add new conditions after optimize_cond() call
+    Set missing links on multiply equalities
 
-  @param thd         the thread handle
-  @param cond        the condition where to attach new conditions
-  @param cond_eq     IN/OUT the multiple equalities of cond
-  @param new_conds   IN/OUT the list of conditions needed to add
-  @param cond_value  the returned value of the condition
+  @param thd               the thread handle
+  @param cond              the condition to set links for
+  @param inherited         path to all inherited multiple equality items
+  @param build_cond_equal  flag to control if COND_EQUAL for AND-condition
+                           should be built
+
+  @details
+    The method traverses cond and set links for the upper COND_EQUAL levels
+    where needed.
+    If build_cond_equal is set to true it builds for each AND-level except the
+    external one COND_EQUAL.
+*/
+
+static
+void set_cond_equal_links(THD *thd, Item *cond, COND_EQUAL *inherited,
+                          bool build_cond_equal)
+{
+  if (cond->type() == Item::FUNC_ITEM &&
+      ((Item_func*) cond)->functype() == Item_func::MULT_EQUAL_FUNC)
+  {
+    ((Item_equal *)cond)->upper_levels= inherited;
+    if (inherited)
+      inherited->increase_references();
+  }
+
+  if (cond->type() != Item::COND_ITEM)
+    return;
+
+  List_iterator<Item> it(*((Item_cond *)cond)->argument_list());
+  Item *item;
+  while ((item=it++))
+  {
+    if (item->type() != Item::COND_ITEM ||
+        ((Item_cond*) item)->functype() != Item_func::COND_AND_FUNC)
+    {
+      set_cond_equal_links(thd, item, inherited, build_cond_equal);
+      continue;
+    }
+    Item_cond_and *and_item= (Item_cond_and *)item;
+    if (build_cond_equal)
+    {
+      COND_EQUAL new_cond_equal;
+      List_iterator<Item> li(*and_item->argument_list());
+      Item *elem;
+
+      while ((elem=li++))
+      {
+        if (elem->type() == Item::FUNC_ITEM &&
+            ((Item_func*) elem)->functype() == Item_func::MULT_EQUAL_FUNC)
+        {
+          if (new_cond_equal.current_level.push_back((Item_equal *)elem,
+                                                     thd->mem_root))
+            return;
+          li.remove();
+        }
+      }
+      List<Item> *equal_list=
+        (List<Item> *)&and_item->m_cond_equal.current_level;
+      and_item->m_cond_equal.copy(new_cond_equal);
+      and_item->argument_list()->append(equal_list);
+    }
+    and_item->m_cond_equal.upper_levels= inherited;
+    and_item->m_cond_equal.clean_references();
+    if (inherited)
+      inherited->increase_references();
+
+    set_cond_equal_links(thd, item, &and_item->m_cond_equal,
+                         build_cond_equal);
+  }
+}
+
+
+/**
+  @brief
+    Conjunct conditions after optimize_cond() call
+
+  @param thd               the thread handle
+  @param cond              the condition where to attach new conditions
+  @param cond_eq           IN/OUT the multiple equalities of cond
+  @param new_conds         IN/OUT the list of conditions needed to add
+  @param cond_value        the returned value of the condition
+  @param build_cond_equal  flag to control if COND_EQUAL elements for
+                           AND-conditions should be built
 
   @details
     The method creates new condition through conjunction of cond and
@@ -5576,9 +5654,11 @@ int select_value_catcher::send_data(List<Item> &items)
 Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond,
                                            COND_EQUAL **cond_eq,
                                            List<Item> &new_conds,
-                                           Item::cond_result *cond_value)
+                                           Item::cond_result *cond_value,
+                                           bool build_cond_equal)
 {
   COND_EQUAL new_cond_equal;
+  COND_EQUAL *inherited= 0;
   Item *item;
   Item_equal *equality;
   bool is_simplified_cond= false;
@@ -5651,6 +5731,7 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond,
 
     and_args->append((List<Item> *) cond_equalities);
     *cond_eq= &((Item_cond_and *) cond)->m_cond_equal;
+    inherited= &((Item_cond_and *)cond)->m_cond_equal;
 
     propagate_new_equalities(thd, cond, cond_equalities,
                              cond_equal->upper_levels,
@@ -5692,21 +5773,12 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond,
       li.rewind();
       while ((item=li++))
       {
-        if (item->fix_fields_if_needed(thd, NULL))
+        if (!item->is_fixed() && item->fix_fields(thd, NULL))
           return NULL;
         if (item->const_item() && !item->val_int())
           is_simplified_cond= true;
       }
-
-      if (new_conds.elements > 1)
-        new_conds_list.append(&new_conds);
-      else
-      {
-        li.rewind();
-        item= li++;
-        if (new_conds_list.push_back(item, thd->mem_root))
-          return NULL;
-      }
+      new_conds_list.append(&new_conds);
     }
 
     if (is_mult_eq)
@@ -5716,16 +5788,12 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond,
       eq_cond->merge_into_list(thd, &new_cond_equal.current_level,
                                false, false);
 
-       while ((equality= it++))
-       {
-         if (equality->const_item() && !equality->val_int())
-           is_simplified_cond= true;
-       }
-       (*cond_eq)->copy(new_cond_equal);
-    }
+      while ((equality= it++))
+      {
+        if (equality->const_item() && !equality->val_int())
+          is_simplified_cond= true;
+      }
 
-    if (new_cond_equal.current_level.elements > 0)
-    {
       if (new_cond_equal.current_level.elements +
           new_conds_list.elements == 1)
       {
@@ -5735,8 +5803,9 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond,
         if (equality->fix_fields(thd, NULL))
           return NULL;
       }
-      new_conds_list.append((List<Item> *)&new_cond_equal.current_level);
+      *cond_eq= &new_cond_equal;
     }
+    new_conds_list.append((List<Item> *)&new_cond_equal.current_level);
 
     if (new_conds_list.elements > 1)
     {
@@ -5746,6 +5815,7 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond,
       and_cond->m_cond_equal.copy(new_cond_equal);
       cond= (Item *)and_cond;
       *cond_eq= &((Item_cond_and *)cond)->m_cond_equal;
+      inherited= &((Item_cond_and *)cond)->m_cond_equal;
     }
     else
     {
@@ -5753,7 +5823,7 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond,
       cond= iter++;
     }
 
-    if (cond->fix_fields_if_needed(thd, NULL))
+    if (!cond->is_fixed() && cond->fix_fields(thd, NULL))
       return NULL;
 
     if (new_cond_equal.current_level.elements > 0)
@@ -5769,6 +5839,12 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond,
   */
   if (is_simplified_cond)
     cond= cond->remove_eq_conds(thd, cond_value, true);
+
+  if (cond)
+  {
+    set_cond_equal_links(thd, cond, inherited, build_cond_equal);
+  }
+
   return cond;
 }
 
@@ -6440,33 +6516,27 @@ bool JOIN::choose_tableless_subquery_plan()
 }
 
 
-/*
-  Check if the item exists in the fields list of the left part of
-  the IN subquery predicate subq_pred and returns its corresponding
-  item from the select of the right part of subq_pred.
-*/
-Item *Item::get_corresponding_field_in_insubq(Item_in_subselect *subq_pred)
+bool Item::pushable_equality_checker_for_subquery(uchar *arg)
 {
-  DBUG_ASSERT(type() == Item::FIELD_ITEM ||
-              (type() == Item::REF_ITEM &&
-               ((Item_ref *) this)->ref_type() == Item_ref::VIEW_REF));
-
-  List_iterator<Field_pair> it(subq_pred->corresponding_fields);
-  Field_pair *ret;
-  Item_field *field_item= (Item_field *) (real_item());
-  while ((ret= it++))
-  {
-    if (field_item->field == ret->field)
-      return ret->corresponding_item;
-  }
-  return NULL;
+  return
+  get_corresponding_field_pair(this,
+                            ((Item_in_subselect *)arg)->corresponding_fields);
 }
 
 
-bool Item_field::excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred)
+/*
+  Checks if 'item' or some item equal to it is equal to the field from
+  some Field_pair of 'pair_list' and returns matching Field_pair or
+  NULL if the matching Field_pair wasn't found.
+*/
+
+Field_pair *find_matching_field_pair(Item *item, List<Field_pair> pair_list)
 {
-  if (((Item *)this)->get_corresponding_field_in_insubq(subq_pred))
-    return true;
+  Field_pair *field_pair= get_corresponding_field_pair(item, pair_list);
+  if (field_pair)
+    return field_pair;
+
+  Item_equal *item_equal= item->get_item_equal();
   if (item_equal)
   {
     Item_equal_fields_iterator it(*item_equal);
@@ -6475,10 +6545,19 @@ bool Item_field::excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred)
     {
       if (equal_item->const_item())
         continue;
-      if (equal_item->get_corresponding_field_in_insubq(subq_pred))
-        return true;
+      field_pair= get_corresponding_field_pair(equal_item, pair_list);
+      if (field_pair)
+        return field_pair;
     }
   }
+  return NULL;
+}
+
+
+bool Item_field::excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred)
+{
+  if (find_matching_field_pair(((Item *) this), subq_pred->corresponding_fields))
+    return true;
   return false;
 }
 
@@ -6488,7 +6567,7 @@ bool Item_direct_view_ref::excl_dep_on_in_subq_left_part(Item_in_subselect *subq
   if (item_equal)
   {
     DBUG_ASSERT(real_item()->type() == Item::FIELD_ITEM);
-    if (((Item *)this)->get_corresponding_field_in_insubq(subq_pred))
+    if (get_corresponding_field_pair(((Item *)this), subq_pred->corresponding_fields))
       return true;
   }
   return (*ref)->excl_dep_on_in_subq_left_part(subq_pred);
@@ -6552,7 +6631,7 @@ Item *get_corresponding_item(THD *thd, Item *item,
               (item->type() == Item::REF_ITEM &&
               ((Item_ref *) item)->ref_type() == Item_ref::VIEW_REF));
 
-  Item *corresonding_item;
+  Field_pair *field_pair;
   Item_equal *item_equal= item->get_item_equal();
 
   if (item_equal)
@@ -6561,15 +6640,20 @@ Item *get_corresponding_item(THD *thd, Item *item,
     Item *equal_item;
     while ((equal_item= it++))
     {
-      corresonding_item=
-        equal_item->get_corresponding_field_in_insubq(subq_pred);
-      if (corresonding_item)
-        return corresonding_item;
+      field_pair=
+        get_corresponding_field_pair(equal_item, subq_pred->corresponding_fields);
+      if (field_pair)
+        return field_pair->corresponding_item;
     }
-    return NULL;
   }
   else
-    return item->get_corresponding_field_in_insubq(subq_pred);
+  {
+    field_pair=
+        get_corresponding_field_pair(item, subq_pred->corresponding_fields);
+    if (field_pair)
+        return field_pair->corresponding_item;
+  }
+  return NULL;
 }
 
 
@@ -6845,9 +6929,7 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond)
   if (!remaining_cond)
     goto exit;
 
-  remaining_cond->walk(&Item::cleanup_excluding_const_fields_processor,
-                       0, 0);
-  sel->cond_pushed_into_having= remaining_cond;
+  sel->mark_or_conds_to_avoid_pushdown(remaining_cond);
 
 exit:
   thd->lex->current_select= save_curr_select;