MDEV-25080 Fix pushdown of statements including derived tablesbb-10.11-mdev-25080-fixes

Derived tables sometimes prevent a SELECT or UNIT statement from being
pushed down as a whole. This commit fixes this by adding a recoursive
decent down the derived tables and checking tables more accurately

4 files changed, 119 insertions, 19 deletions

diff --git a/mysql-test/suite/federated/federatedx_create_handlers.result b/mysql-test/suite/federated/federatedx_create_handlers.result
index 6b9be6bdf03..5872910869d 100644
--- a/mysql-test/suite/federated/federatedx_create_handlers.result
+++ b/mysql-test/suite/federated/federatedx_create_handlers.result
@@ -602,8 +602,7 @@ EXPLAIN
 SELECT * FROM 
 (SELECT a FROM federated.t1 UNION ALL SELECT * FROM federated.t2) q;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	8	
-2	PUSHED DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+1	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
 # There is an uncacheable side effect due to fetch into @var,
 # so the UNION cannot be pushed down as a whole.
 # But separate SELECTs can be pushed, and the results are combined
@@ -766,6 +765,41 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 4	INTERSECT	t1	ALL	NULL	NULL	NULL	NULL	3	
 NULL	INTERSECT RESULT	<intersect3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
 NULL	UNIT RESULT	<unit1,2,5>	ALL	NULL	NULL	NULL	NULL	NULL	
+# Parenthesis must not prevent the whole UNIONs pushdown
+EXPLAIN (SELECT * FROM federated.t1 UNION
+SELECT * FROM federated.t2) UNION ALL
+SELECT * FROM federated.t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+(SELECT * FROM federated.t1 UNION
+SELECT * FROM federated.t2) UNION ALL
+SELECT * FROM federated.t1;
+a
+abc
+bcd
+cde
+def
+efg
+abc
+bcd
+cde
+EXPLAIN (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2)
+UNION ALL (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+NULL	PUSHED UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+(SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2) UNION ALL
+(SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2);
+a
+abc
+bcd
+cde
+def
+efg
+abc
+bcd
+cde
+def
+efg
 DROP TABLES federated.t1, federated.t2, t3, t4;
 connection slave;
 DROP TABLE federated.t1, federated.t2;
diff --git a/mysql-test/suite/federated/federatedx_create_handlers.test b/mysql-test/suite/federated/federatedx_create_handlers.test
index 71fe0932cb0..b43a097daad 100644
--- a/mysql-test/suite/federated/federatedx_create_handlers.test
+++ b/mysql-test/suite/federated/federatedx_create_handlers.test
@@ -423,6 +423,21 @@ EXPLAIN SELECT * FROM federated.t2 UNION ALL
   SELECT * FROM t4 INTERSECT
   SELECT * FROM federated.t1;
 
+--echo # Parenthesis must not prevent the whole UNIONs pushdown
+EXPLAIN (SELECT * FROM federated.t1 UNION
+  SELECT * FROM federated.t2) UNION ALL
+  SELECT * FROM federated.t1;
+
+(SELECT * FROM federated.t1 UNION
+  SELECT * FROM federated.t2) UNION ALL
+  SELECT * FROM federated.t1;
+
+EXPLAIN (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2)
+  UNION ALL (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2);
+
+(SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2) UNION ALL
+  (SELECT * FROM federated.t1 UNION SELECT * FROM federated.t2);
+
 DROP TABLES federated.t1, federated.t2, t3, t4;
 
 connection slave;
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 6c4ef7eb358..24643fa4dcb 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -1298,11 +1298,58 @@ bool init_item_int(THD* thd, Item_int* &item)
 
 /**
   @brief
+    Recursive subroutine to be called from find_unit_handler() (see below).
+    Must not be called directly, only from find_unit_handler().
+*/
+static select_handler *find_unit_handler_for_lex(THD *thd,
+                                                 SELECT_LEX *sel_lex,
+                                                 SELECT_LEX_UNIT* unit)
+{
+  if (!(sel_lex->join))
+    return nullptr;
+  for (TABLE_LIST *tbl= sel_lex->join->tables_list; tbl; tbl= tbl->next_local)
+  {
+    if (!tbl->table)
+      continue;
+    if (tbl->derived)
+    {
+      /*
+        Skip derived table for now as they will be checked
+        in the subsequent loop
+      */
+      continue;
+    }
+    handlerton *ht= tbl->table->file->partition_ht();
+    if (!ht->create_unit)
+      continue;
+    select_handler *sh= ht->create_unit(thd, unit);
+    if (sh)
+      return sh;
+  }
+
+  for (SELECT_LEX_UNIT *un= sel_lex->first_inner_unit(); un;
+       un= un->next_unit())
+  {
+    for (SELECT_LEX *sl= un->first_select(); sl; sl= sl->next_select())
+    {
+      select_handler *uh= find_unit_handler_for_lex(thd, sl, unit);
+      if (uh)
+        return uh;
+    }
+  }
+  return nullptr;
+}
+
+
+/**
+  @brief
     Look for provision of the select_handler interface by a foreign engine.
     This interface must support processing UNITs (multiple SELECTs combined
     with UNION/EXCEPT/INTERSECT operators)
 
-  @param thd   The thread handler
+  @param
+    thd   The thread handler
+    unit  UNIT (one or more SELECTs combined with UNION/EXCEPT/INTERSECT
 
   @details
     The function checks that this is an upper level UNIT and if so looks
@@ -1313,31 +1360,26 @@ bool init_item_int(THD* thd, Item_int* &item)
     This is a responsibility of the create_unit call-back function to
     check whether the engine can execute the query.
 
+    The function recursively scans subqueries (see find_unit_handler_for_lex())
+    to get down to real tables and process queries like this:
+      (SELECT a FROM t1 UNION SELECT b FROM t2) UNION
+        (SELECT c FROM t3 UNION select d FROM t4)
+
   @retval the found select_handler if the search is successful
-          0  otherwise
+          nullptr  otherwise
 */
 
-select_handler *find_unit_handler(THD *thd,
-                                  SELECT_LEX_UNIT *unit)
+static select_handler *find_unit_handler(THD *thd,
+                                         SELECT_LEX_UNIT *unit)
 {
   if (unit->outer_select())
     return nullptr;
 
   for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select())
   {
-    if (!(sl->join))
-      continue;
-    for (TABLE_LIST *tbl= sl->join->tables_list; tbl; tbl= tbl->next_local)
-    {
-      if (!tbl->table)
-        continue;
-      handlerton *ht= tbl->table->file->partition_ht();
-      if (!ht->create_unit)
-        continue;
-      select_handler *sh= ht->create_unit(thd, unit);
-      if (sh)
-        return sh;
-    }
+    select_handler *uh= find_unit_handler_for_lex(thd, sl, unit);
+    if (uh)
+      return uh;
   }
   return nullptr;
 }
diff --git a/storage/federatedx/federatedx_pushdown.cc b/storage/federatedx/federatedx_pushdown.cc
index 5402a7e4f16..993167b275b 100644
--- a/storage/federatedx/federatedx_pushdown.cc
+++ b/storage/federatedx/federatedx_pushdown.cc
@@ -50,6 +50,15 @@ static TABLE *get_fed_table_for_pushdown(SELECT_LEX *sel_lex)
   {
     if (!tbl->table)
       return nullptr;
+    if (tbl->derived)
+    {
+      /*
+        Skip derived table for now as they will be checked
+        in the subsequent loop
+      */
+      continue;
+    }
+
     if (tbl->table->file->partition_ht() != federatedx_hton)
       return nullptr;
     if (!table)