Merge branch 'bb-10.3-mdev12387' into 10.4bb-10.4-mdev12387

1 files changed, 90 insertions, 121 deletions

diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index ab66384c6cb..2df740b6811 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -1242,25 +1242,61 @@ bool mysql_derived_reinit(THD *thd, LEX *lex, TABLE_LIST *derived)
 
 /**
   @brief
-  Extract the condition depended on derived table/view and pushed it there 
+    Extract condition that can be pushed into a derived table/view
    
-  @param thd       The thread handle
-  @param cond      The condition from which to extract the pushed condition 
-  @param derived   The reference to the derived table/view
+  @param thd       the thread handle
+  @param cond      current condition
+  @param derived   the reference to the derived table/view
 
   @details
-   This functiom builds the most restrictive condition depending only on
-   the derived table/view that can be extracted from the condition cond. 
-   The built condition is pushed into the having clauses of the
-   selects contained in the query specifying the derived table/view.
-   The function also checks for each select whether any condition depending
-   only on grouping fields can be extracted from the pushed condition.
-   If so, it pushes the condition over grouping fields into the where
-   clause of the select.
-  
-  @retval
-    true    if an error is reported 
-    false   otherwise
+    This function builds the most restrictive condition depending only on
+    the derived table/view (directly or indirectly through equality) that
+    can be extracted from the given condition cond and pushes it into the
+    derived table/view.
+
+    Example of the transformation:
+
+    SELECT *
+    FROM t1,
+    (
+      SELECT x,MAX(y) AS max_y
+      FROM t2
+      GROUP BY x
+    ) AS d_tab
+    WHERE d_tab.x>1 AND d_tab.max_y<30;
+
+    =>
+
+    SELECT *
+    FROM t1,
+    (
+      SELECT x,z,MAX(y) AS max_y
+      FROM t2
+      WHERE x>1
+      HAVING max_y<30
+      GROUP BY x
+    ) AS d_tab
+    WHERE d_tab.x>1 AND d_tab.max_y<30;
+
+    In details:
+    1. Check what pushable formula can be extracted from cond
+    2. Build a clone PC of the formula that can be extracted
+       (the clone is built only if the extracted formula is a AND subformula
+        of cond or conjunction of such subformulas)
+    Do for every select specifying derived table/view:
+    3. If there is no HAVING clause prepare PC to be conjuncted with
+       WHERE clause of the select. Otherwise do 4-7.
+    4. Check what formula PC_where can be extracted from PC to be pushed
+       into the WHERE clause of the select
+    5. Build PC_where and if PC_where is a conjunct(s) of PC remove it from PC
+       getting PC_having
+    6. Prepare PC_where to be conjuncted with the WHERE clause of the select
+    7. Prepare PC_having to be conjuncted with the HAVING clause of the select
+  @note
+    This method is similar to pushdown_cond_for_in_subquery()
+
+  @retval TRUE   if an error occurs
+  @retval FALSE  otherwise
 */
 
 bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
@@ -1300,63 +1336,25 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
   if (!some_select_allows_cond_pushdown)
     DBUG_RETURN(false);
 
-  /*
-    Build the most restrictive condition extractable from 'cond'
-    that can be pushed into the derived table 'derived'.
-    All subexpressions of this condition are cloned from the
-    subexpressions of 'cond'.
-    This condition has to be fixed yet.
-  */
+  /* 1. Check what pushable formula can be extracted from cond */
   Item *extracted_cond;
-  derived->check_pushable_cond_for_table(cond);
-  extracted_cond= derived->build_pushable_cond_for_table(thd, cond);
+  cond->check_pushable_cond(&Item::pushable_cond_checker_for_derived,
+                            (uchar *)(&derived->table->map));
+  /* 2. Build a clone PC of the formula that can be extracted */
+  extracted_cond=
+    cond->build_pushable_cond(thd,
+                              &Item::pushable_equality_checker_for_derived,
+                              ((uchar *)&derived->table->map));
   if (!extracted_cond)
   {
     /* Nothing can be pushed into the derived table */
     DBUG_RETURN(false);
   }
-  /* Push extracted_cond into every select of the unit specifying 'derived' */
+
   st_select_lex *save_curr_select= thd->lex->current_select;
   for (; sl; sl= sl->next_select())
   {
     Item *extracted_cond_copy;
-    if (!sl->cond_pushdown_is_allowed())
-      continue;
-    thd->lex->current_select= sl;
-    if (sl->have_window_funcs())
-    {
-      if (sl->join->group_list || sl->join->implicit_grouping)
-        continue;
-      ORDER *common_partition_fields= 
-	       sl->find_common_window_func_partition_fields(thd);           
-      if (!common_partition_fields)
-        continue;
-      extracted_cond_copy= !sl->next_select() ?
-                           extracted_cond :
-                           extracted_cond->build_clone(thd);
-      if (!extracted_cond_copy)
-        continue;
-
-      Item *cond_over_partition_fields;; 
-      sl->collect_grouping_fields(thd, common_partition_fields);
-      sl->check_cond_extraction_for_grouping_fields(extracted_cond_copy,
-                                                    derived);
-      cond_over_partition_fields=
-        sl->build_cond_for_grouping_fields(thd, extracted_cond_copy, true);
-      if (cond_over_partition_fields)
-        cond_over_partition_fields= cond_over_partition_fields->transform(thd,
-                         &Item::derived_grouping_field_transformer_for_where,
-                         (uchar*) sl);
-      if (cond_over_partition_fields)
-      {
-        cond_over_partition_fields->walk(
-          &Item::cleanup_excluding_const_fields_processor, 0, 0);
-        sl->cond_pushed_into_where= cond_over_partition_fields;     
-      }
-
-      continue;
-    }
-
     /*
       For each select of the unit except the last one
       create a clone of extracted_cond
@@ -1367,73 +1365,44 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
     if (!extracted_cond_copy)
       continue;
 
-    if (!sl->join->group_list && !sl->with_sum_func)
-    {
-      /* extracted_cond_copy is pushed into where of sl */
-      extracted_cond_copy= extracted_cond_copy->transform(thd,
-                                 &Item::derived_field_transformer_for_where,
-                                 (uchar*) sl);
-      if (extracted_cond_copy)
-      {
-        extracted_cond_copy->walk(
-          &Item::cleanup_excluding_const_fields_processor, 0, 0);
-        sl->cond_pushed_into_where= extracted_cond_copy;
-      }      
-  
-      continue;
-    }
-
-    /*
-      Figure out what can be extracted from the pushed condition
-      that could be pushed into the where clause of sl
-    */
-    Item *cond_over_grouping_fields;
-    sl->collect_grouping_fields(thd, sl->join->group_list);
-    sl->check_cond_extraction_for_grouping_fields(extracted_cond_copy,
-                                                  derived);
-    cond_over_grouping_fields=
-      sl->build_cond_for_grouping_fields(thd, extracted_cond_copy, true);
-  
-    /*
-      Transform the references to the 'derived' columns from the condition
-      pushed into the where clause of sl to make them usable in the new context
-    */
-    if (cond_over_grouping_fields)
-      cond_over_grouping_fields= cond_over_grouping_fields->transform(thd,
-                         &Item::derived_grouping_field_transformer_for_where,
-                         (uchar*) sl);
-     
-    if (cond_over_grouping_fields)
+    /* Collect fields that are used in the GROUP BY of sl */
+    if (sl->have_window_funcs())
     {
-      /*
-        In extracted_cond_copy remove top conjuncts that
-        has been pushed into the where clause of sl
-      */
-      extracted_cond_copy= remove_pushed_top_conjuncts(thd, extracted_cond_copy);
-
-      cond_over_grouping_fields->walk(
-        &Item::cleanup_excluding_const_fields_processor, 0, 0);
-      sl->cond_pushed_into_where= cond_over_grouping_fields;
-
-      if (!extracted_cond_copy)
+      if (sl->group_list.first || sl->join->implicit_grouping)
+        continue;
+      ORDER *common_partition_fields=
+         sl->find_common_window_func_partition_fields(thd);
+      if (!common_partition_fields)
         continue;
+      sl->collect_grouping_fields(thd, common_partition_fields);
     }
+    else
+      sl->collect_grouping_fields(thd, sl->group_list.first);
+
+    Item *remaining_cond= NULL;
+    /* Do 4-6 */
+    sl->pushdown_cond_into_where_clause(thd, extracted_cond_copy,
+                                    &remaining_cond,
+                                    &Item::derived_field_transformer_for_where,
+                                    (uchar *) sl);
     
+    if (!remaining_cond)
+      continue;
     /*
-      Transform the references to the 'derived' columns from the condition
-      pushed into the having clause of sl to make them usable in the new context
+       7. Prepare PC_having to be conjuncted with the HAVING clause of
+          the select
     */
-    extracted_cond_copy= extracted_cond_copy->transform(thd,
-                         &Item::derived_field_transformer_for_having,
-                         (uchar*) sl);
-    if (!extracted_cond_copy)
+    remaining_cond=
+      remaining_cond->transform(thd,
+                                &Item::derived_field_transformer_for_having,
+                                (uchar *) sl);
+    if (!remaining_cond)
       continue;
 
-    extracted_cond_copy->walk(&Item::cleanup_excluding_const_fields_processor,
-                              0, 0);
-    sl->cond_pushed_into_having= extracted_cond_copy;
+    remaining_cond->walk(&Item::cleanup_excluding_const_fields_processor,
+                         0, 0);
+    sl->cond_pushed_into_having= remaining_cond;
   }
   thd->lex->current_select= save_curr_select;
   DBUG_RETURN(false);
 }
-