MDEV-12387 Push conditions into materialized subqueries

The logic and the implementation scheme are similar with the
MDEV-9197 Pushdown conditions into non-mergeable views/derived tables

How the push down is made on the example:

select * from t1
where a>3 and b>10 and
 (a,b) in (select x,max(y) from t2 group by x);

-->

select * from t1
where a>3 and b>10 and
  (a,b) in (select x,max(y)
            from t2
            where x>3
            group by x
            having max(y)>10);

The implementation scheme:

1. Search for the condition cond that depends only on the fields
   from the left part of the IN subquery (left_part)
2. Find fields F_group in the select of the right part of the
   IN subquery (right_part) that are used in the GROUP BY
3. Extract from the cond condition cond_where that depends only on the
   fields from the left_part that stay at the same places in the left_part
   (have the same indexes) as the F_group fields in the projection of the
   right_part
4. Transform cond_where so it can be pushed into the WHERE clause of the
   right_part and delete cond_where from the cond
5. Transform cond so it can be pushed into the HAVING clause of the right_part

The optimization is made in the
Item_in_subselect::pushdown_cond_for_in_subquery() and is controlled by the
variable condition_pushdown_for_subquery.

New test file in_subq_cond_pushdown.test is created.

There are also some changes made for setup_jtbm_semi_joins().
Now it is decomposed into the 2 procedures: setup_degenerate_jtbm_semi_joins()
that is called before optimize_cond() for cond and setup_jtbm_semi_joins()
that is called after optimize_cond().
New setup_jtbm_semi_joins() is made in the way so that the result of its work is
the same as if it was called before optimize_cond().

The code that is common for pushdown into materialized derived and into materialized
IN subqueries is factored out into pushdown_cond_for_derived(),
Item_in_subselect::pushdown_cond_for_in_subquery() and
st_select_lex::pushdown_cond_into_where_clause().

1 files changed, 9 insertions, 4 deletions

diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index dfe3287615b..fa5cbe8c424 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -33,6 +33,7 @@ class subselect_hash_sj_engine;
 class Item_bool_func2;
 class Comp_creator;
 class With_element;
+class Field_pair;
 
 typedef class st_select_lex SELECT_LEX;
 
@@ -570,6 +571,8 @@ public:
   */
   bool is_registered_semijoin;
   
+  List<Field_pair> corresponding_fields;
+
   /*
     Used to determine how this subselect item is represented in the item tree,
     in case there is a need to locate it there and replace with something else.
@@ -741,6 +744,8 @@ public:
     return 0;
   };
 
+  bool pushdown_cond_for_in_subquery(THD *thd, Item *cond);
+
   friend class Item_ref_null_helper;
   friend class Item_is_not_null_test;
   friend class Item_in_optimizer;
@@ -852,7 +857,6 @@ protected:
   void set_row(List<Item> &item_list, Item_cache **row);
 };
 
-
 class subselect_single_select_engine: public subselect_engine
 {
   bool prepared;       /* simple subselect is prepared */
@@ -886,9 +890,10 @@ public:
 
   friend class subselect_hash_sj_engine;
   friend class Item_in_subselect;
-  friend bool setup_jtbm_semi_joins(JOIN *join, List<TABLE_LIST> *join_list,
-                                    Item **join_where);
-
+  friend bool execute_degenerate_jtbm_semi_join(THD *thd,
+                                                TABLE_LIST *tbl,
+                                                Item_in_subselect *subq_pred,
+                                                List<Item> &eq_list);
 };