MDEV-17096 Pushdown of simple derived tables to storage engines

Resolved the problem of forming a proper query string for FEDERATEDX.
Added test cases.

Cleanup of extra spaces.

2 files changed, 162 insertions, 17 deletions

diff --git a/mysql-test/suite/federated/federatedx_create_handlers.result b/mysql-test/suite/federated/federatedx_create_handlers.result
index fdad44c1b95..a25b0191e55 100644
--- a/mysql-test/suite/federated/federatedx_create_handlers.result
+++ b/mysql-test/suite/federated/federatedx_create_handlers.result
@@ -10,7 +10,7 @@ Warnings:
 Note	1051	Unknown table 'federated.t1'
 CREATE TABLE federated.t1 (
 id int(20) NOT NULL,
-name varchar(16) NOT NULL default ''  
+name varchar(16) NOT NULL default ''
 )
 DEFAULT CHARSET=latin1;
 INSERT INTO federated.t1 VALUES
@@ -19,7 +19,7 @@ DROP TABLE IF EXISTS federated.t2;
 Warnings:
 Note	1051	Unknown table 'federated.t2'
 CREATE TABLE federated.t2 (
-name varchar(16) NOT NULL default ''  
+name varchar(16) NOT NULL default ''
 )
 DEFAULT CHARSET=latin1;
 INSERT INTO federated.t2 VALUES
@@ -30,7 +30,7 @@ Warnings:
 Note	1051	Unknown table 'federated.t1'
 CREATE TABLE federated.t1 (
 id int(20) NOT NULL,
-name varchar(16) NOT NULL default ''  
+name varchar(16) NOT NULL default ''
 )
 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
 CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1';
@@ -38,7 +38,7 @@ DROP TABLE IF EXISTS federated.t2;
 Warnings:
 Note	1051	Unknown table 'federated.t2'
 CREATE TABLE federated.t2 (
-name varchar(16) NOT NULL default ''  
+name varchar(16) NOT NULL default ''
 )
 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
 CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t2';
@@ -123,7 +123,7 @@ ANALYZE
   }
 }
 CREATE TABLE federated.t3 (
-name varchar(16) NOT NULL default ''  
+name varchar(16) NOT NULL default ''
 )
 DEFAULT CHARSET=latin1;
 INSERT INTO federated.t3 VALUES
@@ -190,7 +190,114 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_f
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	7	7.00	100.00	100.00	
 1	PRIMARY	<derived2>	ref	key0	key0	18	federated.t3.name	2	0.00	100.00	100.00	
 2	PUSHED DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
-DROP TABLE federated.t1, federated.t2;
+SELECT *
+FROM federated.t3, (SELECT t1.name FROM federated.t1
+WHERE id IN (SELECT count(*)
+FROM federated.t2 GROUP BY name)) t
+WHERE federated.t3.name=t.name;
+name	name
+xxx	xxx
+EXPLAIN
+SELECT *
+FROM federated.t3, (SELECT t1.name FROM federated.t1
+WHERE id IN (SELECT count(*)
+FROM federated.t2 GROUP BY name)) t
+WHERE federated.t3.name=t.name;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	7	
+1	PRIMARY	<derived2>	ref	key0	key0	18	federated.t3.name	2	
+2	PUSHED DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	7	Using temporary
+ANALYZE FORMAT=JSON
+SELECT *
+FROM federated.t3, (SELECT t1.name FROM federated.t1
+WHERE id IN (SELECT count(*)
+FROM federated.t2 GROUP BY name)) t
+WHERE federated.t3.name=t.name;
+ANALYZE
+{
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "table": {
+      "table_name": "t3",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "rows": 7,
+      "r_rows": 7,
+      "r_total_time_ms": "REPLACED",
+      "filtered": 100,
+      "r_filtered": 100
+    },
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ref",
+      "possible_keys": ["key0"],
+      "key": "key0",
+      "key_length": "18",
+      "used_key_parts": ["name"],
+      "ref": ["federated.t3.name"],
+      "r_loops": 7,
+      "rows": 2,
+      "r_rows": 0,
+      "r_total_time_ms": "REPLACED",
+      "filtered": 100,
+      "r_filtered": 100,
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "table": {
+            "message": "Pushed derived"
+          },
+          "subqueries": [
+            {
+              "query_block": {
+                "select_id": 3,
+                "temporary_table": {
+                  "table": {
+                    "table_name": "t2",
+                    "access_type": "ALL",
+                    "r_loops": 0,
+                    "rows": 7,
+                    "r_rows": null,
+                    "filtered": 100,
+                    "r_filtered": null
+                  }
+                }
+              }
+            }
+          ]
+        }
+      }
+    }
+  }
+}
+SELECT t.id, federated.t3.name
+FROM federated.t3,
+( SELECT * FROM federated.t1 WHERE id < 3
+UNION
+SELECT * FROM federated.t1 WHERE id >= 5) t
+WHERE federated.t3.name=t.name;
+id	name
+5	yyy
+7	yyy
+5	yyy
+7	yyy
+5	yyy
+7	yyy
+EXPLAIN
+SELECT t.id, federated.t3.name
+FROM federated.t3,
+( SELECT * FROM federated.t1 WHERE id < 3
+UNION
+SELECT * FROM federated.t1 WHERE id >= 5) t
+WHERE federated.t3.name=t.name;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	7	
+1	PRIMARY	<derived2>	ref	key0	key0	18	federated.t3.name	2	
+2	PUSHED DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+DROP TABLE federated.t1, federated.t2, federated.t3;
 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 0e586dad0c1..21539b61c8a 100644
--- a/mysql-test/suite/federated/federatedx_create_handlers.test
+++ b/mysql-test/suite/federated/federatedx_create_handlers.test
@@ -7,7 +7,7 @@ DROP TABLE IF EXISTS federated.t1;
 
 CREATE TABLE federated.t1 (
   id int(20) NOT NULL,
-  name varchar(16) NOT NULL default ''  
+  name varchar(16) NOT NULL default ''
 )
 DEFAULT CHARSET=latin1;
 
@@ -17,7 +17,7 @@ INSERT INTO federated.t1 VALUES
 DROP TABLE IF EXISTS federated.t2;
 
 CREATE TABLE federated.t2 (
-  name varchar(16) NOT NULL default ''  
+  name varchar(16) NOT NULL default ''
 )
 DEFAULT CHARSET=latin1;
 
@@ -33,7 +33,7 @@ DROP TABLE IF EXISTS federated.t1;
 eval
 CREATE TABLE federated.t1 (
   id int(20) NOT NULL,
-  name varchar(16) NOT NULL default ''  
+  name varchar(16) NOT NULL default ''
 )
 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
 CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';
@@ -43,7 +43,7 @@ DROP TABLE IF EXISTS federated.t2;
 --replace_result $SLAVE_MYPORT SLAVE_PORT
 eval
 CREATE TABLE federated.t2 (
-  name varchar(16) NOT NULL default ''  
+  name varchar(16) NOT NULL default ''
 )
 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
 CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t2';
@@ -76,11 +76,12 @@ SELECT id FROM federated.t1 WHERE id < 5;
 ANALYZE
 SELECT id FROM federated.t1 WHERE id < 5;
 
+--source include/analyze-format.inc
 ANALYZE FORMAT=JSON
 SELECT id FROM federated.t1 WHERE id < 5;
 
 CREATE TABLE federated.t3 (
-  name varchar(16) NOT NULL default ''  
+  name varchar(16) NOT NULL default ''
 )
 DEFAULT CHARSET=latin1;
 
@@ -89,26 +90,63 @@ INSERT INTO federated.t3 VALUES
 
 SELECT *
 FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
-WHERE federated.t3.name=t.name; 
+WHERE federated.t3.name=t.name;
 
 EXPLAIN
 SELECT *
 FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
-WHERE federated.t3.name=t.name; 
+WHERE federated.t3.name=t.name;
 
 EXPLAIN FORMAT=JSON
 SELECT *
 FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
-WHERE federated.t3.name=t.name; 
+WHERE federated.t3.name=t.name;
 
 ANALYZE
 SELECT *
 FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
-WHERE federated.t3.name=t.name; 
+WHERE federated.t3.name=t.name;
 
-DROP TABLE federated.t1, federated.t2;
+SELECT *
+FROM federated.t3, (SELECT t1.name FROM federated.t1
+                    WHERE id IN (SELECT count(*)
+                                 FROM federated.t2 GROUP BY name)) t
+WHERE federated.t3.name=t.name;
 
-connection slave; 
+EXPLAIN
+SELECT *
+FROM federated.t3, (SELECT t1.name FROM federated.t1
+                    WHERE id IN (SELECT count(*)
+                                 FROM federated.t2 GROUP BY name)) t
+WHERE federated.t3.name=t.name;
+
+--source include/analyze-format.inc
+ANALYZE FORMAT=JSON
+SELECT *
+FROM federated.t3, (SELECT t1.name FROM federated.t1
+                    WHERE id IN (SELECT count(*)
+                                 FROM federated.t2 GROUP BY name)) t
+WHERE federated.t3.name=t.name;
+
+SELECT t.id, federated.t3.name
+FROM federated.t3,
+     ( SELECT * FROM federated.t1 WHERE id < 3
+       UNION
+       SELECT * FROM federated.t1 WHERE id >= 5) t
+WHERE federated.t3.name=t.name;
+
+EXPLAIN
+SELECT t.id, federated.t3.name
+FROM federated.t3,
+     ( SELECT * FROM federated.t1 WHERE id < 3
+       UNION
+       SELECT * FROM federated.t1 WHERE id >= 5) t
+WHERE federated.t3.name=t.name;
+
+
+DROP TABLE federated.t1, federated.t2, federated.t3;
+
+connection slave;
 DROP TABLE federated.t1, federated.t2;
 
 connection default;