MDEV-30052: SIGSEGV in subquery_types_allow_materialization, fix variant 2bb-10.3-MDEV-30052-variant2

remove_redundant_subquery_clauses() also removes elements from the
select list. It does so, because GROUP BY code might have added GROUP
BY columns there.

In this bug, it has tried to remove a select list element that was not
added, it was in the select list from the start.

Attempt to remove this element caused data corruption, as the current
select list (join->all_fields) shares elements with the original list
(join->fields_list).

Fixed by not attempting to remove elements that are in join->fields_list.

4 files changed, 144 insertions, 3 deletions

diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result
index c794aade773..4fad75ad1fe 100644
--- a/mysql-test/main/subselect4.result
+++ b/mysql-test/main/subselect4.result
@@ -3154,4 +3154,66 @@ group by (select a from t1 where a = 1) in
 b
 2
 drop table t1,t2,t3,t4;
+#
+# MDEV-30052: SIGSEGV in subquery_types_allow_materialization, AddressSanitizer: 
+# ... use-after-poison in base_list_iterator::next
+#
+CREATE TABLE t1 (c INT);
+insert into t1 values (1),(2);
+UPDATE t1 SET c=1 
+WHERE c=2 
+ORDER BY 
+(1 IN ((
+SELECT * 
+FROM (SELECT * FROM t1) AS v1 
+GROUP BY c 
+WINDOW v2 AS (ORDER BY 
+(SELECT * 
+FROM t1 
+GROUP BY c 
+WINDOW v3 AS (PARTITION BY c)
+)
+)
+))
+);
+drop table t1;
+#
+# MDEV-29359: Server crashed with heap-use-after-free in in Field::is_null(long long) const
+#  (Just testcase)
+CREATE TABLE t1 (id int);
+INSERT INTO t1 VALUES (-1),(0),(84);
+SELECT 
+id IN (SELECT id 
+FROM t1
+WINDOW w AS (ORDER BY (SELECT  1 
+FROM t1 
+WHERE 
+EXISTS ( SELECT id 
+FROM t1 
+GROUP BY id 
+WINDOW w2 AS (ORDER BY id)
+)
+) 
+)
+)  
+FROM t1;
+id IN (SELECT id 
+FROM t1
+WINDOW w AS (ORDER BY (SELECT  1 
+FROM t1 
+WHERE 
+EXISTS ( SELECT id 
+FROM t1 
+GROUP BY id 
+WINDOW w2 AS (ORDER BY id)
+)
+) 
+)
+)
+1
+1
+1
+Warnings:
+Warning	1052	Column 'id' in group statement is ambiguous
+DROP TABLE t1;
 # End of 10.3 tests
diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test
index 2982766c16d..f6874cf3e47 100644
--- a/mysql-test/main/subselect4.test
+++ b/mysql-test/main/subselect4.test
@@ -2554,4 +2554,52 @@ eval $q6;
 
 drop table t1,t2,t3,t4;
 
+--echo #
+--echo # MDEV-30052: SIGSEGV in subquery_types_allow_materialization, AddressSanitizer: 
+--echo # ... use-after-poison in base_list_iterator::next
+--echo #
+CREATE TABLE t1 (c INT);
+insert into t1 values (1),(2);
+UPDATE t1 SET c=1 
+WHERE c=2 
+ORDER BY 
+  (1 IN ((
+      SELECT * 
+      FROM (SELECT * FROM t1) AS v1 
+      GROUP BY c 
+      WINDOW v2 AS (ORDER BY 
+                      (SELECT * 
+                       FROM t1 
+                       GROUP BY c 
+                       WINDOW v3 AS (PARTITION BY c)
+                      )
+                    )
+    ))
+  );
+drop table t1;
+
+--echo #
+--echo # MDEV-29359: Server crashed with heap-use-after-free in in Field::is_null(long long) const
+--echo #  (Just testcase)
+CREATE TABLE t1 (id int);
+INSERT INTO t1 VALUES (-1),(0),(84);
+ 
+SELECT 
+  id IN (SELECT id 
+         FROM t1
+         WINDOW w AS (ORDER BY (SELECT  1 
+                                FROM t1 
+                                WHERE 
+                                  EXISTS ( SELECT id 
+                                           FROM t1 
+                                           GROUP BY id 
+                                           WINDOW w2 AS (ORDER BY id)
+                                          )
+                                ) 
+                      )
+         )  
+FROM t1;
+
+DROP TABLE t1;
+
 --echo # End of 10.3 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index a85dc71e9e4..a41d0c79e6c 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -609,7 +609,11 @@ void remove_redundant_subquery_clauses(st_select_lex *subq_select_lex)
         (*ord->item)->walk(&Item::eliminate_subselect_processor, FALSE, NULL);
         /*
           Remove from the JOIN::all_fields list any reference to the elements
-          of the eliminated GROUP BY list unless it is 'in_field_list'.
+          of the eliminated GROUP BY list unless:
+           - The GROUP BY entry has 'in_field_list' set.
+           - The GROUP BY entry refers to an item that was already present in
+             the original select list (which can be found in join->fields_list)
+
           This is needed in order not to confuse JOIN::make_aggr_tables_info()
           when it constructs different structure for execution phase.
 	*/
@@ -617,6 +621,15 @@ void remove_redundant_subquery_clauses(st_select_lex *subq_select_lex)
 	Item *item;
         while ((item= li++))
 	{
+          /*
+            Do not remove elements that are in the original join->fields_list.
+            That list is a suffix of this list. As soon as we've reached its
+            first element, stop.
+            (It's fine to compare element contents as the same item cannot be
+             in the list twice)
+          */
+          if (item == subq_select_lex->join->fields_list.head())
+            break;
           if (item == *ord->item)
 	    li.remove();
 	}
diff --git a/sql/sql_select.h b/sql/sql_select.h
index a953354abb0..63475ebcec6 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1396,12 +1396,30 @@ public:
     (set in make_join_statistics())
   */
   bool impossible_where; 
-  List<Item> all_fields; ///< to store all fields that used in query
+
+  /*
+    All fields used in the query processing.
+
+    Initially this is a list of fields from the SQL query.
+    Then, ORDER/GROUP BY and Window Function code add columns that need to
+    be saved to be available in the post-group-by context. These extra columns
+    are added to the front, because this->all_fields points to the suffix of
+    this list.
+  */
+  List<Item> all_fields;
+
   ///Above list changed to use temporary table
   List<Item> tmp_all_fields1, tmp_all_fields2, tmp_all_fields3;
   ///Part, shared with list above, emulate following list
   List<Item> tmp_fields_list1, tmp_fields_list2, tmp_fields_list3;
-  List<Item> &fields_list; ///< hold field list passed to mysql_select
+
+  /*
+    The original field list as it was passed to mysql_select(). This refers
+    to select_lex->item_list.
+    CAUTION: this list is a suffix of this->all_fields list, that is, it shares
+    elements with that list!
+  */
+  List<Item> &fields_list;
   List<Item> procedure_fields_list;
   int error;