MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP

Consider a query of the form:

  select ... from (select item2 as COL1) as T where COL1=123

Condition pushdown into derived table will try to push "COL1=123" condition
down into table T.
The process of pushdown involves "substituting" the item, that is,
replacing Item_field("T.COL1") with its "producing item" item2.
In order to use item2, one needs to clone it (call Item::build_clone).

If the item is not cloneable (e.g. Item_func_sp is not), the pushdown
process will fail and nothing at all will be pushed.

Fixed by introducing transform_condition_or_part() which will try to apply
the transformation for as many parts of condition as possible. The parts of
condition that couldn't be transformed are dropped.

3 files changed, 289 insertions, 6 deletions

diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result
index 28532ae88a4..f5ef3c3a84d 100644
--- a/mysql-test/r/derived_cond_pushdown.result
+++ b/mysql-test/r/derived_cond_pushdown.result
@@ -10673,4 +10673,150 @@ Warnings:
 Note	1003	select `v2`.`a` AS `a`,`v2`.`f` AS `f`,`v2`.`g` AS `g` from `test`.`v2` where `v2`.`f` = `v2`.`a` and `v2`.`g` = `v2`.`a`
 drop view v1,v2;
 drop table t1;
+#
+# MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP
+#
+create function f1(a int) returns int DETERMINISTIC return (a+1);
+create table t1 (
+pk int primary key,
+a int,
+b int,
+key(a)
+);
+create table t2(a int);
+insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t3(a int);
+insert into t3 select A.a + B.a* 10 + C.a * 100 from t2 A, t2 B, t2 C;
+insert into t1 select a,a,a from t3;
+create view v1 as
+select
+t1.a as col1,
+f1(t1.b) as col2
+from
+t1;
+create view v2 as
+select
+t1.a as col1,
+f1(t1.b) as col2
+from
+t1;
+create view v3 as
+select col2, col1 from v1
+union all
+select col2, col1 from v2;
+explain select * from v3 where col1=123;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DERIVED	t1	ref	a	a	5	const	1	
+3	UNION	t1	ref	a	a	5	const	1	
+# This must use ref accesses for reading table t1, not full scans:
+explain format=json
+select * from v3 where col1=123 and col2=321;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ALL",
+      "rows": 2,
+      "filtered": 100,
+      "attached_condition": "v3.col1 = 123 and v3.col2 = 321",
+      "materialized": {
+        "query_block": {
+          "union_result": {
+            "table_name": "<union2,3>",
+            "access_type": "ALL",
+            "query_specifications": [
+              {
+                "query_block": {
+                  "select_id": 2,
+                  "table": {
+                    "table_name": "t1",
+                    "access_type": "ref",
+                    "possible_keys": ["a"],
+                    "key": "a",
+                    "key_length": "5",
+                    "used_key_parts": ["a"],
+                    "ref": ["const"],
+                    "rows": 1,
+                    "filtered": 100
+                  }
+                }
+              },
+              {
+                "query_block": {
+                  "select_id": 3,
+                  "table": {
+                    "table_name": "t1",
+                    "access_type": "ref",
+                    "possible_keys": ["a"],
+                    "key": "a",
+                    "key_length": "5",
+                    "used_key_parts": ["a"],
+                    "ref": ["const"],
+                    "rows": 1,
+                    "filtered": 100
+                  }
+                }
+              }
+            ]
+          }
+        }
+      }
+    }
+  }
+}
+drop function f1;
+drop view v1,v2,v3;
+drop table t1, t2,t3;
+#
+# Another testcase, with pushdown through GROUP BY
+#
+create table t1 (a int, b  int);
+insert into t1 values (1,1),(2,2),(3,3);
+create function f1(a int) returns int DETERMINISTIC return (a+1);
+create view v2(a, a2, s) as 
+select a, f1(a), sum(b) from t1 group by a, f1(a);
+# Here, 
+#   "(s+1) > 10" will be pushed into HAVING
+#   "a > 1" will be pushed all the way to the table scan on t1
+#   "a2>123" will be pushed into HAVING (as it refers to an SP call which
+#                                       prevents pushing it to the WHERE)
+explain format=json
+select * from v2 where (s+1) > 10 AND a > 1 and a2>123;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ALL",
+      "rows": 3,
+      "filtered": 100,
+      "attached_condition": "v2.s + 1 > 10 and v2.a > 1 and v2.a2 > 123",
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "having_condition": "s + 1 > 10 and a2 > 123",
+          "filesort": {
+            "sort_key": "t1.a, f1(t1.a)",
+            "temporary_table": {
+              "table": {
+                "table_name": "t1",
+                "access_type": "ALL",
+                "rows": 3,
+                "filtered": 100,
+                "attached_condition": "t1.a > 1"
+              }
+            }
+          }
+        }
+      }
+    }
+  }
+}
+drop view v2;
+drop function f1;
+drop table t1;
 # End of 10.2 tests
diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test
index 58f38ac1e5a..9b7658a725e 100644
--- a/mysql-test/t/derived_cond_pushdown.test
+++ b/mysql-test/t/derived_cond_pushdown.test
@@ -2237,4 +2237,74 @@ eval explain extended $q2;
 drop view v1,v2;
 drop table t1;
 
+--echo #
+--echo # MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP
+--echo #
+create function f1(a int) returns int DETERMINISTIC return (a+1);
+
+create table t1 (
+  pk int primary key,
+  a int,
+  b int,
+  key(a)
+);
+
+create table t2(a int);
+insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t3(a int);
+insert into t3 select A.a + B.a* 10 + C.a * 100 from t2 A, t2 B, t2 C;
+
+insert into t1 select a,a,a from t3;
+
+create view v1 as
+select
+  t1.a as col1,
+  f1(t1.b) as col2
+from
+  t1;
+
+create view v2 as
+select
+  t1.a as col1,
+  f1(t1.b) as col2
+from
+  t1;
+create view v3 as
+select col2, col1 from v1
+union all
+select col2, col1 from v2;
+
+explain select * from v3 where col1=123;
+
+--echo # This must use ref accesses for reading table t1, not full scans:
+explain format=json
+select * from v3 where col1=123 and col2=321;
+
+drop function f1;
+drop view v1,v2,v3;
+drop table t1, t2,t3;
+
+--echo #
+--echo # Another testcase, with pushdown through GROUP BY
+--echo #
+create table t1 (a int, b  int);
+insert into t1 values (1,1),(2,2),(3,3);
+
+create function f1(a int) returns int DETERMINISTIC return (a+1);
+
+create view v2(a, a2, s) as 
+select a, f1(a), sum(b) from t1 group by a, f1(a);
+
+--echo # Here, 
+--echo #   "(s+1) > 10" will be pushed into HAVING
+--echo #   "a > 1" will be pushed all the way to the table scan on t1
+--echo #   "a2>123" will be pushed into HAVING (as it refers to an SP call which
+--echo #                                       prevents pushing it to the WHERE)
+explain format=json
+select * from v2 where (s+1) > 10 AND a > 1 and a2>123;
+
+drop view v2;
+drop function f1;
+drop table t1;
 --echo # End of 10.2 tests
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 632baf4bc5b..30bffeb7581 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -1192,6 +1192,68 @@ bool mysql_derived_reinit(THD *thd, LEX *lex, TABLE_LIST *derived)
 }
 
 
+/*
+  @brief
+    Given condition cond and transformer+argument, try transforming as many
+    conjuncts as possible.
+
+  @detail
+    The motivation of this function is to convert the condition that's being
+    pushed into a WHERE clause with derived_field_transformer_for_where or
+    with derived_grouping_field_transformer_for_where.
+    The transformer may fail for some sub-condition, in this case we want to
+    convert the most restrictive part of the condition that can be pushed.
+
+    This function only does it for top-level AND: conjuncts that could not be
+    converted are dropped.
+
+  @return
+    Converted condition, or NULL if nothing could be converted
+*/
+
+static
+Item *transform_condition_or_part(THD *thd,
+                                  Item *cond,
+                                  Item_transformer transformer,
+                                  uchar *arg)
+{
+  if (cond->type() != Item::COND_ITEM ||
+      ((Item_cond*) cond)->functype() != Item_func::COND_AND_FUNC)
+  {
+    Item *new_item= cond->transform(thd, transformer, arg);
+      // Indicate that the condition is not pushable
+    if (!new_item)
+      cond->clear_extraction_flag();
+    return new_item;
+  }
+
+  List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
+  Item *item;
+  while ((item=li++))
+  {
+    Item *new_item= item->transform(thd, transformer, arg);
+    if (!new_item)
+    {
+      // Indicate that the condition is not pushable
+      item->clear_extraction_flag();
+      li.remove();
+    }
+    else
+      li.replace(new_item);
+  }
+
+  switch (((Item_cond*) cond)->argument_list()->elements)
+  {
+  case 0:
+    return NULL;
+  case 1:
+    return ((Item_cond*) cond)->argument_list()->head();
+  default:
+    return cond;
+  }
+}
+
+
 /**
   @brief
   Extract the condition depended on derived table/view and pushed it there 
@@ -1287,9 +1349,11 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
     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);
+      extracted_cond_copy=
+        transform_condition_or_part(thd,
+                                    extracted_cond_copy,
+                                    &Item::derived_field_transformer_for_where,
+                                    (uchar*)sl);
       if (extracted_cond_copy)
       {
         extracted_cond_copy->walk(
@@ -1316,9 +1380,12 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived)
       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);
+    {
+      cond_over_grouping_fields= 
+        transform_condition_or_part(thd, cond_over_grouping_fields,
+                                    &Item::derived_grouping_field_transformer_for_where,
+                                    (uchar*) sl);
+    }
      
     if (cond_over_grouping_fields)
     {