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, 27 insertions, 2 deletions

diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 882f92465b1..fc0cb4bf8d0 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -3198,7 +3198,11 @@ public:
     return used_tables() & tab_map;
   }
   bool excl_dep_on_in_subq_left_part(Item_in_subselect *subq_pred);
-
+  bool excl_dep_on_group_fields_for_having_pushdown(st_select_lex *sel);
+  bool create_pushable_equalities(THD *thd, List<Item> *equalities,
+                                  Pushdown_checker checker, uchar *arg);
+  /* Return the number of elements in this multiple equality */
+  uint elements_count() { return equal_items.elements; }
   friend class Item_equal_fields_iterator;
   bool count_sargable_conds(void *arg);
   friend class Item_equal_iterator<List_iterator_fast,Item>;
@@ -3218,12 +3222,17 @@ public:
   COND_EQUAL *upper_levels;       /* multiple equalities of upper and levels */
   List<Item_equal> current_level; /* list of multiple equalities of 
                                      the current and level           */
+  uint references;                /* number of conditions that have
+                                     reference on this COND_EQUAL */
+  uint work_references;           /* same as references */
   COND_EQUAL()
   { 
     upper_levels= 0;
+    references= 0;
+    work_references= 0;
   }
   COND_EQUAL(Item_equal *item, MEM_ROOT *mem_root)
-   :upper_levels(0)
+   :upper_levels(0), references(0), work_references(0)
   {
     current_level.push_back(item, mem_root);
   }
@@ -3231,11 +3240,27 @@ public:
   {
     max_members= cond_equal.max_members;
     upper_levels= cond_equal.upper_levels;
+    references= cond_equal.references;
+    work_references= cond_equal.work_references;
     if (cond_equal.current_level.is_empty())
       current_level.empty();
     else
       current_level= cond_equal.current_level;
   }
+  bool is_empty()
+  {
+    return (current_level.elements == 0);
+  }
+  void increase_references()
+  {
+    references++;
+    work_references++;
+  }
+  void clean_references()
+  {
+    references= 0;
+    work_references= 0;
+  }
 };