[Commits] c22d307afa1a6accbfe9857256cad01c1eacd677 Fixed bug mdev-10785. The condition pushed into WHERE/HAVING of a materialized view/derived table may differ for different executions of the same prepared statement. That's why the should be ANDed with the existing WHERE/HAVING conditions only after all permanent transformations of these conditions has been performed.

Igor Babaev igor at askmonty.org
Wed Sep 14 11:06:45 EEST 2016


commit c22d307afa1a6accbfe9857256cad01c1eacd677
Author: Igor Babaev <igor at askmonty.org>
Commit: Igor Babaev <igor at askmonty.org>

    Fixed bug mdev-10785.
    The condition pushed into WHERE/HAVING of a materialized
    view/derived table may differ for different executions of
    the same prepared statement. That's why the should be
    ANDed with the existing WHERE/HAVING conditions only after all
    permanent transformations of these conditions has been
    performed.
---
 mysql-test/r/derived_cond_pushdown.result |   16 ++++++++++++
 mysql-test/t/derived_cond_pushdown.test   |   17 ++++++++++++-
 sql/sql_derived.cc                        |   38 ++++------------------------
 sql/sql_lex.cc                            |    1 +
 sql/sql_lex.h                             |    2 +
 sql/sql_select.cc                         |   18 +++++++++++++
 6 files changed, 59 insertions(+), 33 deletions(-)

diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result
index 9401a03..91c6e62 100644
--- a/mysql-test/r/derived_cond_pushdown.result
+++ b/mysql-test/r/derived_cond_pushdown.result
@@ -7016,3 +7016,19 @@ SELECT * FROM t1 WHERE 1 IN ( SELECT * FROM v );
 i
 DROP VIEW v;
 DROP TABLE t1;
+#
+# MDEV-10785: second execution of a query with condition
+#             pushed into view
+#
+CREATE TABLE t1 (i int);
+CREATE VIEW v1 AS SELECT i FROM t1 WHERE i < 5;
+CREATE FUNCTION f (in1 int) RETURNS int RETURN in1;
+CREATE VIEW v2 AS SELECT * FROM v1 GROUP BY i;
+PREPARE stmt FROM "SELECT * FROM v2 WHERE f(0) <> 2";
+EXECUTE stmt;
+i
+EXECUTE stmt;
+i
+DROP FUNCTION f;
+DROP VIEW v2,v1;
+DROP TABLE t1;
diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test
index cfd73bd..141c705 100644
--- a/mysql-test/t/derived_cond_pushdown.test
+++ b/mysql-test/t/derived_cond_pushdown.test
@@ -907,4 +907,19 @@ CREATE VIEW v AS SELECT 5;
 SELECT * FROM t1 WHERE 1 IN ( SELECT * FROM v );
 DROP VIEW v;
 DROP TABLE t1; 
- 
\ No newline at end of file
+ 
+--echo #
+--echo # MDEV-10785: second execution of a query with condition
+--echo #             pushed into view
+--echo #
+
+CREATE TABLE t1 (i int);
+CREATE VIEW v1 AS SELECT i FROM t1 WHERE i < 5;
+CREATE FUNCTION f (in1 int) RETURNS int RETURN in1;
+CREATE VIEW v2 AS SELECT * FROM v1 GROUP BY i;
+PREPARE stmt FROM "SELECT * FROM v2 WHERE f(0) <> 2";
+EXECUTE stmt;
+EXECUTE stmt;
+DROP FUNCTION f;
+DROP VIEW v2,v1;
+DROP TABLE t1;
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index a743511..677a4c4 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -1145,7 +1145,7 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
     return false; 
 
   /* Do not push conditions into constant derived */
-  if (derived->fill_me)
+  if (unit->executed)
     return false;
 
   /* Do not push conditions into recursive with tables */
@@ -1191,18 +1191,8 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
                                  (uchar*) sl);
       if (extracted_cond_copy)
       {
-        /*
-          Create the conjunction of the existing where condition of sl
-          and the pushed condition, take it as the new where condition of sl
-          and fix this new condition
-        */
-        extracted_cond_copy->walk(&Item::cleanup_processor, 0, 0);
-        thd->change_item_tree(&sl->join->conds,
-                              and_conds(thd, sl->join->conds,
-                                        extracted_cond_copy));
-    
-        if (sl->join->conds->fix_fields(thd, &sl->join->conds))
-          goto err;
+       extracted_cond_copy->walk(&Item::cleanup_processor, 0, 0);
+       sl->cond_pushed_into_where= extracted_cond_copy;
       }      
   
       continue;
@@ -1236,19 +1226,9 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
       */
       extracted_cond_copy= remove_pushed_top_conjuncts(thd, extracted_cond_copy);
   
-      /*
-        Create the conjunction of the existing where condition of sl
-        and the pushed condition, take it as the new where condition of sl
-        and fix this new condition
-      */
       cond_over_grouping_fields->walk(&Item::cleanup_processor, 0, 0);
-      thd->change_item_tree(&sl->join->conds,
-                            and_conds(thd, sl->join->conds,
-                                      cond_over_grouping_fields));
-    
-      if (sl->join->conds->fix_fields(thd, &sl->join->conds))
-        goto err;
-           
+      sl->cond_pushed_into_where= cond_over_grouping_fields;
+
       if (!extracted_cond_copy)
         continue;
     }
@@ -1268,13 +1248,7 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
       and fix this new condition
     */
     extracted_cond_copy->walk(&Item::cleanup_processor, 0, 0);
-    thd->change_item_tree(&sl->join->having,
-    and_conds(thd, sl->join->having,
-    extracted_cond_copy));
-    sl->having_fix_field= 1;
-    if (sl->join->having->fix_fields(thd, &sl->join->having))
-      return true;
-    sl->having_fix_field= 0;
+    sl->cond_pushed_into_having= extracted_cond_copy;
   }
   thd->lex->current_select= save_curr_select;
   return false;
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 5c2b004..592de98 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2093,6 +2093,7 @@ void st_select_lex::init_query()
   item_list.empty();
   join= 0;
   having= prep_having= where= prep_where= 0;
+  cond_pushed_into_where= cond_pushed_into_having= 0;
   olap= UNSPECIFIED_OLAP_TYPE;
   having_fix_field= 0;
   context.select_lex= this;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 8811161..e499cc7 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -765,6 +765,8 @@ class st_select_lex: public st_select_lex_node
   Item *where, *having;                         /* WHERE & HAVING clauses */
   Item *prep_where; /* saved WHERE clause for prepared statement processing */
   Item *prep_having;/* saved HAVING clause for prepared statement processing */
+  Item *cond_pushed_into_where;  /* condition pushed into the select's WHERE  */
+  Item *cond_pushed_into_having; /* condition pushed into the select's HAVING */
   /* Saved values of the WHERE and HAVING clauses*/
   Item::cond_result cond_value, having_value;
   /* point on lex in which it was created, used in view subquery detection */
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 49bc619..aa08420 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1248,6 +1248,24 @@ int JOIN::optimize()
   
   if (setup_jtbm_semi_joins(this, join_list, &conds))
     DBUG_RETURN(1);
+
+  if (select_lex->cond_pushed_into_where)
+  {
+    conds= and_conds(thd, conds, select_lex->cond_pushed_into_where);
+    if (conds && conds->fix_fields(thd, &conds))
+      DBUG_RETURN(1);
+  }
+  if (select_lex->cond_pushed_into_having)
+  {
+    having= and_conds(thd, having, select_lex->cond_pushed_into_having);
+    if (having)
+    {
+      select_lex->having_fix_field= 1;
+      if (having->fix_fields(thd, &having))
+        DBUG_RETURN(1);
+      select_lex->having_fix_field= 0;
+    }
+  }
   
   conds= optimize_cond(this, conds, join_list, FALSE,
                        &cond_value, &cond_equal, OPT_LINK_EQUAL_FIELDS);


More information about the commits mailing list