Fix review comments, add testsbb-10.11-MDEV-25080

4 files changed, 133 insertions, 34 deletions

diff --git a/mysql-test/suite/federated/federatedx_create_handlers.result b/mysql-test/suite/federated/federatedx_create_handlers.result
index 357c8b21bd5..c20e81f1a4c 100644
--- a/mysql-test/suite/federated/federatedx_create_handlers.result
+++ b/mysql-test/suite/federated/federatedx_create_handlers.result
@@ -667,23 +667,100 @@ NULL	PUSHED UNIT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
 EXECUTE stmt;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 NULL	PUSHED UNIT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
-# UNION of mixed Federated/MyISAM tables
+# UNIONs of mixed Federated/MyISAM tables, pushing parts of UNIONs
 CREATE TABLE federated.t3 (a varchar(30)) ENGINE=MyISAM;
-INSERT INTO federated.t3 VALUES ('myisam1'), ('myisam2'), ('myisam3');
+CREATE TABLE federated.t4 (a varchar(30)) ENGINE=MyISAM;
+INSERT INTO federated.t3 VALUES ('t3_myisam1'), ('t3_myisam2'), ('t3_myisam3');
+INSERT INTO federated.t3 VALUES ('t4_myisam1'), ('t4_myisam2'), ('t4_myisam3');
 SELECT * FROM federated.t1 UNION SELECT * FROM federated.t3;
 a
 abc
 bcd
 cde
-myisam1
-myisam2
-myisam3
+t3_myisam1
+t3_myisam2
+t3_myisam3
+t4_myisam1
+t4_myisam2
+t4_myisam3
 EXPLAIN SELECT * FROM federated.t1 UNION SELECT * FROM federated.t3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
-2	UNION	t3	ALL	NULL	NULL	NULL	NULL	3	
+2	UNION	t3	ALL	NULL	NULL	NULL	NULL	6	
 NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
-DROP TABLES federated.t1, federated.t2, federated.t3;
+SELECT * FROM federated.t1 UNION ALL
+SELECT * FROM federated.t3 EXCEPT
+SELECT * FROM federated.t2;
+a
+t3_myisam1
+t3_myisam2
+t3_myisam3
+t4_myisam1
+t4_myisam2
+t4_myisam3
+EXPLAIN SELECT * FROM federated.t1 UNION ALL
+SELECT * FROM federated.t3 EXCEPT
+SELECT * FROM federated.t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+2	UNION	t3	ALL	NULL	NULL	NULL	NULL	6	
+3	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+NULL	UNIT RESULT	<unit1,2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+SELECT * FROM federated.t3 UNION ALL
+SELECT * FROM federated.t1 EXCEPT
+SELECT * FROM federated.t4 INTERSECT
+SELECT * FROM federated.t2;
+a
+t3_myisam1
+t3_myisam2
+t3_myisam3
+t4_myisam1
+t4_myisam2
+t4_myisam3
+abc
+bcd
+cde
+EXPLAIN SELECT * FROM federated.t3 UNION ALL
+SELECT * FROM federated.t1 EXCEPT
+SELECT * FROM federated.t4 INTERSECT
+SELECT * FROM federated.t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	6	
+2	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+5	EXCEPT	<derived3>	ALL	NULL	NULL	NULL	NULL	2	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+4	INTERSECT	t2	ALL	NULL	NULL	NULL	NULL	5	
+NULL	INTERSECT RESULT	<intersect3,4>	ALL	NULL	NULL	NULL	NULL	NULL	
+NULL	UNIT RESULT	<unit1,2,5>	ALL	NULL	NULL	NULL	NULL	NULL	
+SELECT * FROM federated.t2 UNION ALL
+SELECT * FROM federated.t3 EXCEPT
+SELECT * FROM federated.t4 INTERSECT
+SELECT * FROM federated.t1;
+a
+abc
+bcd
+cde
+def
+efg
+t3_myisam1
+t3_myisam2
+t3_myisam3
+t4_myisam1
+t4_myisam2
+t4_myisam3
+EXPLAIN SELECT * FROM federated.t2 UNION ALL
+SELECT * FROM federated.t3 EXCEPT
+SELECT * FROM federated.t4 INTERSECT
+SELECT * FROM federated.t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PUSHED SELECT	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+2	UNION	t3	ALL	NULL	NULL	NULL	NULL	6	
+5	EXCEPT	<derived3>	ALL	NULL	NULL	NULL	NULL	2	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+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	
+DROP TABLES federated.t1, federated.t2, federated.t3, federated.t4;
 connection slave;
 DROP TABLE federated.t1, federated.t2;
 connection default;
diff --git a/mysql-test/suite/federated/federatedx_create_handlers.test b/mysql-test/suite/federated/federatedx_create_handlers.test
index 59eb008411a..d9aaa5b6b77 100644
--- a/mysql-test/suite/federated/federatedx_create_handlers.test
+++ b/mysql-test/suite/federated/federatedx_create_handlers.test
@@ -383,14 +383,42 @@ PREPARE stmt FROM "EXPLAIN SELECT * from federated.t1 INTERSECT
 EXECUTE stmt;
 EXECUTE stmt;
 
---echo # UNION of mixed Federated/MyISAM tables
+--echo # UNIONs of mixed Federated/MyISAM tables, pushing parts of UNIONs
 CREATE TABLE federated.t3 (a varchar(30)) ENGINE=MyISAM;
+CREATE TABLE federated.t4 (a varchar(30)) ENGINE=MyISAM;
+
+INSERT INTO federated.t3 VALUES ('t3_myisam1'), ('t3_myisam2'), ('t3_myisam3');
+INSERT INTO federated.t3 VALUES ('t4_myisam1'), ('t4_myisam2'), ('t4_myisam3');
 
-INSERT INTO federated.t3 VALUES ('myisam1'), ('myisam2'), ('myisam3');
 SELECT * FROM federated.t1 UNION SELECT * FROM federated.t3;
 EXPLAIN SELECT * FROM federated.t1 UNION SELECT * FROM federated.t3;
 
-DROP TABLES federated.t1, federated.t2, federated.t3;
+SELECT * FROM federated.t1 UNION ALL
+  SELECT * FROM federated.t3 EXCEPT
+  SELECT * FROM federated.t2;
+EXPLAIN SELECT * FROM federated.t1 UNION ALL
+  SELECT * FROM federated.t3 EXCEPT
+  SELECT * FROM federated.t2;
+
+SELECT * FROM federated.t3 UNION ALL
+  SELECT * FROM federated.t1 EXCEPT
+  SELECT * FROM federated.t4 INTERSECT
+  SELECT * FROM federated.t2;
+EXPLAIN SELECT * FROM federated.t3 UNION ALL
+  SELECT * FROM federated.t1 EXCEPT
+  SELECT * FROM federated.t4 INTERSECT
+  SELECT * FROM federated.t2;
+
+SELECT * FROM federated.t2 UNION ALL
+  SELECT * FROM federated.t3 EXCEPT
+  SELECT * FROM federated.t4 INTERSECT
+  SELECT * FROM federated.t1;
+EXPLAIN SELECT * FROM federated.t2 UNION ALL
+  SELECT * FROM federated.t3 EXCEPT
+  SELECT * FROM federated.t4 INTERSECT
+  SELECT * FROM federated.t1;
+
+DROP TABLES federated.t1, federated.t2, federated.t3, federated.t4;
 
 connection slave;
 DROP TABLE federated.t1, federated.t2;
diff --git a/storage/federatedx/federatedx_pushdown.cc b/storage/federatedx/federatedx_pushdown.cc
index 33dc38d01a4..05247d5871d 100644
--- a/storage/federatedx/federatedx_pushdown.cc
+++ b/storage/federatedx/federatedx_pushdown.cc
@@ -34,8 +34,13 @@
     set global federated_pushdown=1;
 */
 
+/*
+  Check that all tables in the sel_lex use FederatedX storage engine.
 
-static std::pair<handlerton *, TABLE *> get_handlerton(SELECT_LEX *sel_lex)
+  @return
+    the storage engine's handlerton and an example table.
+*/
+static std::pair<handlerton *, TABLE *> get_fedx_handlerton(SELECT_LEX *sel_lex)
 {
   handlerton *hton= nullptr;
   TABLE *table= nullptr;
@@ -45,9 +50,12 @@ static std::pair<handlerton *, TABLE *> get_handlerton(SELECT_LEX *sel_lex)
   {
     if (!tbl->table)
       return {nullptr, nullptr};
+    auto next_hton= tbl->table->file->partition_ht();
+    if (next_hton->db_type != DB_TYPE_FEDERATED_DB)
+      return {nullptr, nullptr};
     if (!hton)
     {
-      hton= tbl->table->file->partition_ht();
+      hton= next_hton;
       table= tbl->table;
     }
     else if (hton != tbl->table->file->partition_ht())
@@ -59,7 +67,7 @@ static std::pair<handlerton *, TABLE *> get_handlerton(SELECT_LEX *sel_lex)
   {
     for (SELECT_LEX *sl= un->first_select(); sl; sl= sl->next_select())
     {
-      auto inner_ht= get_handlerton(sl);
+      auto inner_ht= get_fedx_handlerton(sl);
       if (!hton)
       {
         hton= inner_ht.first;
@@ -78,21 +86,18 @@ static std::pair<handlerton *, TABLE *> get_handlerton(SELECT_LEX *sel_lex)
 
   @return
     the storage engine's handlerton and an example table.
-
-  @todo
-    Why does this need to be so generic? We know we need
-    tables with hton == federatedx_hton, why not only look for
-    those tables?
 */
 static std::pair<handlerton *, TABLE *>
-get_handlerton_for_unit(SELECT_LEX_UNIT *lex_unit)
+get_fedx_handlerton_for_unit(SELECT_LEX_UNIT *lex_unit)
 {
   handlerton *hton= nullptr;
   TABLE *table= nullptr;
   for (auto sel_lex= lex_unit->first_select(); sel_lex;
        sel_lex= sel_lex->next_select())
   {
-    auto next_ht= get_handlerton(sel_lex);
+    auto next_ht= get_fedx_handlerton(sel_lex);
+    if (!next_ht.first)
+      return {nullptr, nullptr};
     if (!hton)
     {
       hton= next_ht.first;
@@ -113,7 +118,7 @@ create_federatedx_derived_handler(THD* thd, TABLE_LIST *derived)
 
   SELECT_LEX_UNIT *unit= derived->derived;
 
-  auto hton= get_handlerton_for_unit(unit);
+  auto hton= get_fedx_handlerton_for_unit(unit);
   if (!hton.first)
     return nullptr;
 
@@ -153,10 +158,6 @@ int federatedx_handler_base::end_scan_()
   DBUG_RETURN(0);
 }
 
-void ha_federatedx_derived_handler::print_error(int, unsigned long)
-{
-}
-
 
 static select_handler *create_federatedx_select_handler(
   THD *thd, SELECT_LEX *sel_lex)
@@ -164,7 +165,7 @@ static select_handler *create_federatedx_select_handler(
   if (!use_pushdown)
     return nullptr;
 
-  auto hton= get_handlerton(sel_lex);
+  auto hton= get_fedx_handlerton(sel_lex);
   if (!hton.first)
     return nullptr;
 
@@ -180,7 +181,7 @@ static select_handler *create_federatedx_unit_handler(
   if (!use_pushdown)
     return nullptr;
 
-  auto hton= get_handlerton_for_unit(sel_unit);
+  auto hton= get_fedx_handlerton_for_unit(sel_unit);
   if (!hton.first)
     return nullptr;
 
@@ -297,8 +298,3 @@ int ha_federatedx_select_handler::end_scan()
 
   return federatedx_handler_base::end_scan_();
 }
-
-void ha_federatedx_select_handler::print_error(int error, myf error_flag)
-{
-  select_handler::print_error(error, error_flag);
-}
diff --git a/storage/federatedx/federatedx_pushdown.h b/storage/federatedx/federatedx_pushdown.h
index 6c26d501d1f..d369039891f 100644
--- a/storage/federatedx/federatedx_pushdown.h
+++ b/storage/federatedx/federatedx_pushdown.h
@@ -51,7 +51,6 @@ public:
   int init_scan() { return federatedx_handler_base::init_scan_(); }
   int next_row() { return federatedx_handler_base::next_row_(table); }
   int end_scan() { return federatedx_handler_base::end_scan_(); }
-  void print_error(int, unsigned long);
 };
 
 
@@ -71,5 +70,4 @@ public:
   int init_scan() { return federatedx_handler_base::init_scan_(); }
   int next_row() { return federatedx_handler_base::next_row_(table); }
   int end_scan();
-  void print_error(int, unsigned long);
 };