MDEV-23160: SIGSEGV in Explain_node::print_explain_for_children on UNION SELECT or on EXPLAIN EXTENDED10.1-varun

The issue here was that the ORDER BY clause had a subquery and the
ORDER BY clause was defined for a UNION inside an IN subquery.
For IN/ALL/ANY subquery the ORDER BY clause makes no sense and should be removed.
But the removal of the ORDER BY clause happened before name resolution. So an invalid query
could also be parsed and would not return an error.

The reason for the crash is that for EXPLAIN of UNION, the fake_select_lex adds the
removed subquery as its child. The subquery being removed does not create a node for itself
for EXPLAIN. But when the EXPLAIN is printed for the fake_select_lex it tries to print the nodes
of its children.

The fix would be to mark the subquery to be eliminated so that it is not added as a child
to the fake_select_lex node. Then after name resolution is done and the query is valid
then remove the subquery so that it does not get executed.

6 files changed, 166 insertions, 22 deletions

diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index a4fd1123227..262170851af 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2632,7 +2632,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	SUBQUERY	A	ALL	NULL	NULL	NULL	NULL	2	
 3	UNION	B	ALL	NULL	NULL	NULL	NULL	2	
-NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	Using filesort
 SELECT t1.a FROM t1 WHERE EXISTS (SELECT A.a FROM t1 A UNION SELECT B.a FROM t2 B ORDER BY 1);
 a
 1
@@ -2643,6 +2643,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	DEPENDENT SUBQUERY	A	ALL	NULL	NULL	NULL	NULL	2	Using where
 3	DEPENDENT UNION	B	ALL	NULL	NULL	NULL	NULL	2	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
 SELECT t1.a FROM t1 WHERE t1.a IN ( SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t2 B ORDER BY 1);
 a
 1
@@ -2656,4 +2657,58 @@ INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5);
 SELECT a, b FROM t1 WHERE a IN (SELECT A.a FROM t1 A GROUP BY s.id);
 ERROR 42S22: Unknown column 's.id' in 'group statement'
 DROP TABLE t1;
+#
+# MDEV-23160: SIGSEGV in Explain_node::print_explain_for_children on UNION SELECT
+# or on EXPLAIN EXTENDED
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+EXPLAIN SELECT * FROM t1
+WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B 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	A	ALL	NULL	NULL	NULL	NULL	3	Using where
+3	DEPENDENT UNION	B	ALL	NULL	NULL	NULL	NULL	3	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+SELECT * FROM t1
+WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT a));
+a
+1
+2
+3
+EXPLAIN SELECT * FROM t1
+WHERE a IN (SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t1 B 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	A	ALL	NULL	NULL	NULL	NULL	3	Using where
+3	DEPENDENT UNION	B	ALL	NULL	NULL	NULL	NULL	3	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+SELECT * FROM t1
+WHERE a IN (SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t1 B ORDER BY (SELECT a));
+a
+1
+2
+3
+PREPARE stmt  FROM 'EXPLAIN SELECT * FROM t1
+WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT a))';
+EXECUTE stmt;
+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	A	ALL	NULL	NULL	NULL	NULL	3	Using where
+3	DEPENDENT UNION	B	ALL	NULL	NULL	NULL	NULL	3	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+EXECUTE stmt;
+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	A	ALL	NULL	NULL	NULL	NULL	3	Using where
+3	DEPENDENT UNION	B	ALL	NULL	NULL	NULL	NULL	3	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+DEALLOCATE PREPARE stmt;
+SELECT * FROM t1
+WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT id));
+ERROR 42S22: Unknown column 'id' in 'field list'
+SELECT * FROM t1
+WHERE a IN (SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t1 B ORDER BY (SELECT id));
+ERROR 42S22: Unknown column 'id' in 'field list'
+DROP TABLE t1;
 # end of 10.1 tests
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index 03929517126..8bd43661dbc 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -2173,4 +2173,40 @@ INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5);
 SELECT a, b FROM t1 WHERE a IN (SELECT A.a FROM t1 A GROUP BY s.id);
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-23160: SIGSEGV in Explain_node::print_explain_for_children on UNION SELECT
+--echo # or on EXPLAIN EXTENDED
+--echo #
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+EXPLAIN SELECT * FROM t1
+WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT a));
+
+SELECT * FROM t1
+WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT a));
+
+EXPLAIN SELECT * FROM t1
+WHERE a IN (SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t1 B ORDER BY (SELECT a));
+
+SELECT * FROM t1
+WHERE a IN (SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t1 B ORDER BY (SELECT a));
+
+PREPARE stmt  FROM 'EXPLAIN SELECT * FROM t1
+WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT a))';
+
+EXECUTE stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+--error ER_BAD_FIELD_ERROR
+SELECT * FROM t1
+WHERE a IN (SELECT A.a FROM t1 A UNION SELECT B.a FROM t1 B ORDER BY (SELECT id));
+
+--error ER_BAD_FIELD_ERROR
+SELECT * FROM t1
+WHERE a IN (SELECT A.a FROM t1 A UNION ALL SELECT B.a FROM t1 B ORDER BY (SELECT id));
+
+DROP TABLE t1;
+
 --echo # end of 10.1 tests
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index e863308159e..853b4d8cc1a 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -3170,7 +3170,41 @@ bool st_select_lex_unit::union_needs_tmp_table()
     global_parameters()->order_list.elements != 0 ||
     thd->lex->sql_command == SQLCOM_INSERT_SELECT ||
     thd->lex->sql_command == SQLCOM_REPLACE_SELECT;
-}  
+}
+
+
+/*
+  @brief
+    Check if the ORDER BY clause is redundant or empty
+
+  @retval
+    TRUE           order by is redundant or empty
+    FALSE          OTHERWISE
+*/
+
+bool st_select_lex_unit::is_order_by_clause_redundant()
+{
+  if (global_parameters()->order_list.elements == 0)
+    return true;
+  if (is_union_op_inside_in_predicate())
+    return true;
+  return false;
+}
+
+
+/*
+  @brief
+  Check if UNION is in side an IN/ALL/ANY subquery
+
+  @retval
+    TRUE     UNION inside in an IN/ALL/ANY subquery
+    FALSE    Otherwise
+*/
+bool st_select_lex_unit::is_union_op_inside_in_predicate()
+{
+  return is_union() && item && item->is_in_predicate();
+}
+
 
 /**
   @brief Set the initial purpose of this TABLE_LIST object in the list of used
@@ -4678,7 +4712,7 @@ int st_select_lex_unit::save_union_explain(Explain_query *output)
     eu->add_select(sl->select_number);
 
   eu->fake_select_type= "UNION RESULT";
-  eu->using_filesort= MY_TEST(global_parameters()->order_list.first);
+  eu->using_filesort= MY_TEST(!is_order_by_clause_redundant());
   eu->using_tmp= union_needs_tmp_table();
 
   // Save the UNION node
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index cdf80daa928..f18c9f72cce 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -699,6 +699,8 @@ public:
   void set_thd(THD *thd_arg) { thd= thd_arg; }
   inline bool is_union (); 
   bool union_needs_tmp_table();
+  bool is_order_by_clause_redundant();
+  bool is_union_op_inside_in_predicate();
 
   void set_unique_exclude();
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 7a1a7baaa1c..912f28d7168 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -590,6 +590,13 @@ void remove_redundant_subquery_clauses(st_select_lex *subq_select_lex)
     DBUG_PRINT("info", ("DISTINCT removed"));
   }
 
+  if (subq_select_lex->master_unit()->is_union_op_inside_in_predicate() &&
+      subq_select_lex->master_unit()->fake_select_lex == subq_select_lex &&
+      subq_select_lex->order_list.elements)
+  {
+    subq_select_lex->join->order= NULL;
+  }
+
   /*
     Remove GROUP BY if there are no aggregate functions and no HAVING
     clause
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 0e623777ef0..3f03baf73ec 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -331,6 +331,35 @@ st_select_lex_unit::init_prepare_fake_select_lex(THD *thd_arg,
     (*order->item)->walk(&Item::set_fake_select_as_master_processor, 0,
                          (uchar*) fake_select_lex);
   }
+
+  /*
+    If we are reading UNION output and the UNION is in the
+    IN/ANY/ALL subquery, then ORDER BY is redundant and hence should
+    be removed.
+    Example:
+     select ... col IN (select col2 FROM t1 union select col3 from t2 ORDER BY 1)
+
+    (as for ORDER BY ... LIMIT, it currently not supported inside
+     IN/ALL/ANY subqueries)
+     ORDER BY LIMIT is allowed in EXISTS subqueries, so we don't remove
+     the ORDER BY clause from EXISTS subqueries
+    (For non-UNION this removal of ORDER BY clause is done in
+     check_and_do_in_subquery_rewrites())
+     @see remove_redundant_subquery_clauses
+  */
+  if (is_union_op_inside_in_predicate())
+  {
+    for (ORDER *ord= global_parameters()->order_list.first; ord; ord= ord->next)
+    {
+      /*
+        The subqueries inside the ORDER BY clause are marked as eliminated.
+        This is done to avoid adding the subqueries as children of the
+        fake_select_lex in the EXPLAIN structures.
+      */
+      (*ord->item)->walk(&Item::mark_as_eliminated_processor, FALSE, NULL);
+    }
+  }
+
 }
 
 
@@ -388,25 +417,6 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
   found_rows_for_union= first_sl->options & OPTION_FOUND_ROWS;
   is_union_select= is_union() || fake_select_lex;
 
-  /*
-    If we are reading UNION output and the UNION is in the
-    IN/ANY/ALL/EXISTS subquery, then ORDER BY is redundant and hence should
-    be removed.
-    Example:
-     select ... col IN (select col2 FROM t1 union select col3 from t2 ORDER BY 1)
-
-    (as for ORDER BY ... LIMIT, it currently not supported inside
-     IN/ALL/ANY subqueries)
-    (For non-UNION this removal of ORDER BY clause is done in
-     check_and_do_in_subquery_rewrites())
-  */
-  if (is_union() && item &&
-      (item->is_in_predicate() || item->is_exists_predicate()))
-  {
-    global_parameters()->order_list.first= NULL;
-    global_parameters()->order_list.elements= 0;
-  }
-
   /* Global option */
 
   if (is_union_select)