MDEV-23160: SIGSEGV in Explain_node::print_explain_for_children on UNION SELECT

and also MDEV-25564, MDEV-18157.

Attempt to produce EXPLAIN output caused a crash in
Explain_node::print_explain_for_children. The cause of this was that an
Explain_node (actually a derived) had a link to child select#N, but
there was no query plan present for select#N.

The query plan wasn't present because the subquery was eliminated.
- Either it was a degenerate subquery like "(SELECT 1)" in MDEV-25564.
- Or it was a subquery in a UNION subquery's ORDER BY clause:
   col IN (SELECT ... UNION
           SELECT ... ORDER BY (SELECT FROM t1))

In such cases, legacy code structure in subquery/union processing code(*)
makes it hard to detect that the subquery was eliminated, so we end up
with EXPLAIN data structures (Explain_node::children) having dangling
links to child subqueries.
Do make the checks and don't follow the dangling links.

(In ideal world, we should not have these dangling links. But fixing
the code (*) would have high risk for the stable versions).

3 files changed, 90 insertions, 2 deletions

diff --git a/mysql-test/main/explain.result b/mysql-test/main/explain.result
index f593e0dfaba..3a55e2aaf42 100644
--- a/mysql-test/main/explain.result
+++ b/mysql-test/main/explain.result
@@ -407,3 +407,49 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	
 drop table t1, t2;
 # End of 10.1 tests
+#
+# End of 10.2 test
+#
+#
+# MDEV-25564: Server crashed on running some EXPLAIN statements
+#
+EXPLAIN (SELECT 1,3) UNION (SELECT 2,1) ORDER BY (SELECT 2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	Using filesort
+Warnings:
+Note	1249	Select 3 was reduced during optimization
+#
+# MDEV-23160: SIGSEGV in Explain_node::print_explain_for_children on UNION SELECT
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+EXPLAIN
+SELECT *
+FROM t1
+WHERE
+a IN (SELECT a FROM t1
+UNION
+SELECT a FROM t1 ORDER BY (SELECT a))
+UNION
+SELECT * FROM t1 ORDER BY (SELECT a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+3	DEPENDENT UNION	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+5	UNION	t1	ALL	NULL	NULL	NULL	NULL	3	
+NULL	UNION RESULT	<union1,5>	ALL	NULL	NULL	NULL	NULL	NULL	Using filesort
+6	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+drop table t1;
+explain
+VALUES ( (VALUES (2))) UNION VALUES ( (SELECT 3));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+5	SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1249	Select 4 was reduced during optimization
diff --git a/mysql-test/main/explain.test b/mysql-test/main/explain.test
index d5be354c852..973b5a7a87e 100644
--- a/mysql-test/main/explain.test
+++ b/mysql-test/main/explain.test
@@ -333,3 +333,34 @@ explain replace into t2 select 100, (select a from t1);
 drop table t1, t2;
 
 --echo # End of 10.1 tests
+
+--echo #
+--echo # End of 10.2 test
+--echo #
+
+--echo #
+--echo # MDEV-25564: Server crashed on running some EXPLAIN statements
+--echo #
+
+EXPLAIN (SELECT 1,3) UNION (SELECT 2,1) ORDER BY (SELECT 2);
+
+--echo #
+--echo # MDEV-23160: SIGSEGV in Explain_node::print_explain_for_children on UNION SELECT
+--echo #
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+
+EXPLAIN
+SELECT *
+FROM t1
+WHERE
+  a IN (SELECT a FROM t1
+        UNION
+        SELECT a FROM t1 ORDER BY (SELECT a))
+UNION
+  SELECT * FROM t1 ORDER BY (SELECT a);
+drop table t1;
+
+explain
+VALUES ( (VALUES (2))) UNION VALUES ( (SELECT 3));
diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc
index 8690a4a38bb..1ac17de2663 100644
--- a/sql/sql_explain.cc
+++ b/sql/sql_explain.cc
@@ -639,7 +639,11 @@ int Explain_node::print_explain_for_children(Explain_query *query,
   for (int i= 0; i < (int) children.elements(); i++)
   {
     Explain_node *node= query->get_node(children.at(i));
-    if (node->print_explain(query, output, explain_flags, is_analyze))
+    /*
+      Note: node may not be present because for certain kinds of subqueries,
+      the optimizer is not able to see that they were eliminated.
+    */
+    if (node && node->print_explain(query, output, explain_flags, is_analyze))
       return 1;
   }
   return 0;
@@ -683,8 +687,15 @@ void Explain_node::print_explain_json_for_children(Explain_query *query,
   for (int i= 0; i < (int) children.elements(); i++)
   {
     Explain_node *node= query->get_node(children.at(i));
-    /* Derived tables are printed inside Explain_table_access objects */
     
+    /*
+      Note: node may not be present because for certain kinds of subqueries,
+      the optimizer is not able to see that they were eliminated.
+    */
+    if (!node)
+      continue;
+
+    /* Derived tables are printed inside Explain_table_access objects */
     if (!is_connection_printable_in_json(node->connection_type))
       continue;