Merge 10.4 into 10.5

2 files changed, 107 insertions, 20 deletions

diff --git a/mysql-test/suite/federated/federatedx_create_handlers.result b/mysql-test/suite/federated/federatedx_create_handlers.result
index 90a5234a02f..7a407d8aeef 100644
--- a/mysql-test/suite/federated/federatedx_create_handlers.result
+++ b/mysql-test/suite/federated/federatedx_create_handlers.result
@@ -209,7 +209,6 @@ 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
@@ -253,25 +252,7 @@ ANALYZE
           "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
-                  }
-                }
-              }
-            }
-          ]
+          }
         }
       }
     }
@@ -321,6 +302,78 @@ select @var;
 @var
 xxx
 select name into outfile 'tmp.txt' from federated.t1;
+#
+# MDEV-22993: Crash on EXPLAIN with PUSHED DOWN SELECT and subquery
+#
+explain
+select * from federated.t1
+where name in (select name 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
+explain format=json
+select * from federated.t1
+where name in (select name from federated.t2);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "message": "Pushed select"
+    }
+  }
+}
+#
+# MDEV-22993, testcase #2: EXPLAIN output doesn't make sense when
+#   derived table pushdown is used.
+#
+create table t5 (a int) engine=myisam;
+insert into t5 values (1),(2);
+# Must not show lines with id=3
+explain 
+select * from t5, 
+(select id from federated.t1
+where name in (select name from federated.t2) or name like 'foo%') as TQ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t5	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
+2	PUSHED DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+# Must not show elements with select_id=3
+explain format=json
+select * from t5, 
+(select id from federated.t1
+where name in (select name from federated.t2) or name like 'foo%') as TQ;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t5",
+      "access_type": "ALL",
+      "rows": 2,
+      "filtered": 100
+    },
+    "block-nl-join": {
+      "table": {
+        "table_name": "<derived2>",
+        "access_type": "ALL",
+        "rows": 5,
+        "filtered": 100
+      },
+      "buffer_type": "flat",
+      "buffer_size": "65",
+      "join_type": "BNL",
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "table": {
+            "message": "Pushed derived"
+          }
+        }
+      }
+    }
+  }
+}
+drop table t5;
 DROP TABLE federated.t1, federated.t2, federated.t3, federated.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 42a03e60d67..d765588b79b 100644
--- a/mysql-test/suite/federated/federatedx_create_handlers.test
+++ b/mysql-test/suite/federated/federatedx_create_handlers.test
@@ -167,6 +167,40 @@ select name into outfile 'tmp.txt' from federated.t1;
 let $path=`select concat(@@datadir, 'test/tmp.txt')`;
 remove_file $path;
 
+--echo #
+--echo # MDEV-22993: Crash on EXPLAIN with PUSHED DOWN SELECT and subquery
+--echo #
+
+explain
+select * from federated.t1
+where name in (select name from federated.t2);
+
+explain format=json
+select * from federated.t1
+where name in (select name from federated.t2);
+
+--echo #
+--echo # MDEV-22993, testcase #2: EXPLAIN output doesn't make sense when
+--echo #   derived table pushdown is used.
+--echo #
+
+create table t5 (a int) engine=myisam;
+insert into t5 values (1),(2);
+
+--echo # Must not show lines with id=3
+explain 
+select * from t5, 
+(select id from federated.t1
+ where name in (select name from federated.t2) or name like 'foo%') as TQ;
+
+--echo # Must not show elements with select_id=3
+explain format=json
+select * from t5, 
+(select id from federated.t1
+ where name in (select name from federated.t2) or name like 'foo%') as TQ;
+
+drop table t5;
+
 DROP TABLE federated.t1, federated.t2, federated.t3, federated.t4;
 
 connection slave;