MDEV-21883 Server crashes when joining a subselect with 32 tables and GROUP BY

This bug could cause a crash for any query that used a derived table/view/CTE
whose specification was a SELECT with a GROUP BY clause and a FROM list
containing 32 or more table references.
The problem appeared only in the cases when the splitting optimization
could be applied to such derived table/view/CTE.

1 files changed, 85 insertions, 0 deletions

diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index 1ae78aea875..532a6cc77ff 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -16918,4 +16918,89 @@ Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(`test`.`t2`.`ro_id` = `test`.`t1`.`id` and `test`.`t2`.`flag` = 1) where `test`.`t1`.`id` = `test`.`t1`.`id` group by `test`.`t1`.`id`) `dt`) where `dt`.`id` = `test`.`t1`.`id`
 drop view v1;
 drop table t1,t2;
+#
+# MDEV-21883: potentially splittable materialized derived
+#             that uses a join of 32 tables
+#
+CREATE TABLE t (id INT NOT NULL PRIMARY KEY);
+INSERT INTO t values (1),(2),(3);
+set statement optimizer_switch='split_materialized=off' for SELECT t.id FROM t
+LEFT JOIN (
+SELECT t0.id FROM t AS t0
+LEFT JOIN t AS t1 ON 0=1
+LEFT JOIN t AS t2 ON 0=1
+LEFT JOIN t AS t3 ON 0=1
+LEFT JOIN t AS t4 ON 0=1
+LEFT JOIN t AS t5 ON 0=1
+LEFT JOIN t AS t6 ON 0=1
+LEFT JOIN t AS t7 ON 0=1
+LEFT JOIN t AS t8 ON 0=1
+LEFT JOIN t AS t9 ON 0=1
+LEFT JOIN t AS t10 ON 0=1
+LEFT JOIN t AS t11 ON 0=1
+LEFT JOIN t AS t12 ON 0=1
+LEFT JOIN t AS t13 ON 0=1
+LEFT JOIN t AS t14 ON 0=1
+LEFT JOIN t AS t15 ON 0=1
+LEFT JOIN t AS t16 ON 0=1
+LEFT JOIN t AS t17 ON 0=1
+LEFT JOIN t AS t18 ON 0=1
+LEFT JOIN t AS t19 ON 0=1
+LEFT JOIN t AS t20 ON 0=1
+LEFT JOIN t AS t21 ON 0=1
+LEFT JOIN t AS t22 ON 0=1
+LEFT JOIN t AS t23 ON 0=1
+LEFT JOIN t AS t24 ON 0=1
+LEFT JOIN t AS t25 ON 0=1
+LEFT JOIN t AS t26 ON 0=1
+LEFT JOIN t AS t27 ON 0=1
+LEFT JOIN t AS t28 ON 0=1
+LEFT JOIN t AS t29 ON 0=1
+LEFT JOIN t AS t30 ON 0=1
+LEFT JOIN t AS t31 ON 0=1
+GROUP BY t0.id) AS dt ON dt.id = t.id;
+id
+1
+2
+3
+set statement optimizer_switch='split_materialized=on' for SELECT t.id FROM t
+LEFT JOIN (
+SELECT t0.id FROM t AS t0
+LEFT JOIN t AS t1 ON 0=1
+LEFT JOIN t AS t2 ON 0=1
+LEFT JOIN t AS t3 ON 0=1
+LEFT JOIN t AS t4 ON 0=1
+LEFT JOIN t AS t5 ON 0=1
+LEFT JOIN t AS t6 ON 0=1
+LEFT JOIN t AS t7 ON 0=1
+LEFT JOIN t AS t8 ON 0=1
+LEFT JOIN t AS t9 ON 0=1
+LEFT JOIN t AS t10 ON 0=1
+LEFT JOIN t AS t11 ON 0=1
+LEFT JOIN t AS t12 ON 0=1
+LEFT JOIN t AS t13 ON 0=1
+LEFT JOIN t AS t14 ON 0=1
+LEFT JOIN t AS t15 ON 0=1
+LEFT JOIN t AS t16 ON 0=1
+LEFT JOIN t AS t17 ON 0=1
+LEFT JOIN t AS t18 ON 0=1
+LEFT JOIN t AS t19 ON 0=1
+LEFT JOIN t AS t20 ON 0=1
+LEFT JOIN t AS t21 ON 0=1
+LEFT JOIN t AS t22 ON 0=1
+LEFT JOIN t AS t23 ON 0=1
+LEFT JOIN t AS t24 ON 0=1
+LEFT JOIN t AS t25 ON 0=1
+LEFT JOIN t AS t26 ON 0=1
+LEFT JOIN t AS t27 ON 0=1
+LEFT JOIN t AS t28 ON 0=1
+LEFT JOIN t AS t29 ON 0=1
+LEFT JOIN t AS t30 ON 0=1
+LEFT JOIN t AS t31 ON 0=1
+GROUP BY t0.id) AS dt ON dt.id = t.id;
+id
+1
+2
+3
+DROP TABLE t;
 # End of 10.3 tests