MDEV-24925: Server crashes in Item_subselect::init_expr_cache_tracker

(trivial backport to 10.2)
The optimizer removes redundant GROUP BY operations. If GROUP BY element
is a subselect, it is "eliminated".

However one must not eliminate the item if it is used both in the select
list and in the GROUP BY, like so:

  select (select ... ) as SUBQ from ... group by SUBQ

Do not eliminate such items.

3 files changed, 92 insertions, 1 deletions

diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 2e24cbcb40c..ef75bd97fcc 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -2722,3 +2722,53 @@ SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
 a
 DROP TABLE t1,t2;
 # End of 10.2 tests
+#
+# MDEV-24925: Server crashes in Item_subselect::init_expr_cache_tracker
+#
+CREATE TABLE t1 (id INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+SELECT
+1 IN (
+SELECT
+(SELECT COUNT(id)
+FROM t1
+WHERE t1_outer.id <> id
+) AS f
+FROM
+t1 AS t1_outer
+GROUP BY f
+);
+1 IN (
+SELECT
+(SELECT COUNT(id)
+FROM t1
+WHERE t1_outer.id <> id
+) AS f
+FROM
+t1 AS t1_outer
+GROUP BY f
+)
+1
+SELECT
+1 IN (
+SELECT
+(SELECT COUNT(id)
+FROM t1
+WHERE t1_outer.id <> id
+) AS f
+FROM
+t1 AS t1_outer
+GROUP BY 1
+);
+1 IN (
+SELECT
+(SELECT COUNT(id)
+FROM t1
+WHERE t1_outer.id <> id
+) AS f
+FROM
+t1 AS t1_outer
+GROUP BY 1
+)
+1
+DROP TABLE t1;
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index f19a654de64..dae9e71fd92 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -2237,3 +2237,35 @@ SELECT a FROM t1 WHERE (a, a) IN (SELECT 1, 2) AND a = (SELECT MIN(b) FROM t2);
 DROP TABLE t1,t2;
 
 --echo # End of 10.2 tests
+
+--echo #
+--echo # MDEV-24925: Server crashes in Item_subselect::init_expr_cache_tracker
+--echo #
+CREATE TABLE t1 (id INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(2);
+
+SELECT
+  1 IN (
+    SELECT
+      (SELECT COUNT(id)
+       FROM t1
+       WHERE t1_outer.id <> id
+       ) AS f
+    FROM
+      t1 AS t1_outer
+    GROUP BY f
+  );
+
+SELECT
+  1 IN (
+    SELECT
+      (SELECT COUNT(id)
+       FROM t1
+       WHERE t1_outer.id <> id
+       ) AS f
+    FROM
+      t1 AS t1_outer
+    GROUP BY 1
+  );
+
+DROP TABLE t1;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 90c071803a1..b85bd31e23c 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -585,7 +585,16 @@ void remove_redundant_subquery_clauses(st_select_lex *subq_select_lex)
   {
     for (ORDER *ord= subq_select_lex->group_list.first; ord; ord= ord->next)
     {
-      (*ord->item)->walk(&Item::eliminate_subselect_processor, FALSE, NULL);
+      /*
+        Do not remove the item if it is used in select list and then referred
+        from GROUP BY clause by its name or number. Example:
+
+          select (select ... ) as SUBQ ...  group by SUBQ
+
+        Here SUBQ cannot be removed.
+      */
+      if (!ord->in_field_list)
+        (*ord->item)->walk(&Item::eliminate_subselect_processor, FALSE, NULL);
     }
     subq_select_lex->join->group_list= NULL;
     subq_select_lex->group_list.empty();