MDEV-16801 seg_fault on a query

The bug was in the in the code of JOIN::check_for_splittable_materialized()
where the structures describing the fields of a materialized derived
table that potentially could be used in split optimization were build.
As a result of this bug some fields that were not usable for splitting
were detected as usable. This could trigger crashes further in
st_join_table::choose_best_splitting().

1 files changed, 54 insertions, 0 deletions

diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index a2268ced84f..832b9a46289 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -16026,3 +16026,57 @@ SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, (SELECT MAX('aa') FROM DUAL LIMIT
 a
 aa
 DROP FUNCTION f1;
+#
+# MDEV-16801: splittable materialized derived/views with
+#             one grouping field from table without keys
+#
+CREATE TABLE t1 (a int, b int, INDEX idx_a(a), INDEX idx_b(b)) ENGINE=MYISAM;
+CREATE TABLE t2 (c int) ENGINE=MYISAM;
+CREATE TABLE t3 (d int) ENGINE=MYISAM;
+INSERT INTO t1 VALUES
+(77,7), (11,1), (33,3), (44,4), (8,88),
+(78,7), (98,9), (38,3), (28,2), (79,7),
+(58,5), (42,4), (71,7), (27,2), (91,9);
+INSERT INTO t1 SELECT a+100, b+10 FROM t1;
+INSERT INTO t2 VALUES
+(100), (700), (200), (100), (200);
+INSERT INTO t3 VALUES
+(3), (4), (1), (8), (3);
+ANALYZE tables t1,t2,t3;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	OK
+test.t3	analyze	status	OK
+SELECT *
+FROM t3,
+(SELECT t1.b, t2.c
+FROM  t1, t2
+GROUP BY t1.b,t2.c) dt
+WHERE t3.d = dt.b;
+d	b	c
+3	3	700
+3	3	200
+3	3	100
+4	4	700
+4	4	200
+4	4	100
+1	1	700
+1	1	200
+1	1	100
+3	3	700
+3	3	200
+3	3	100
+EXPLAIN EXTENDED SELECT *
+FROM t3,
+(SELECT t1.b, t2.c
+FROM  t1, t2
+GROUP BY t1.b,t2.c) dt
+WHERE t3.d = dt.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t3.d	2	100.00	
+2	LATERAL DERIVED	t1	ref	idx_b	idx_b	5	test.t3.d	2	100.00	Using index; Using temporary; Using filesort
+2	LATERAL DERIVED	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t3`.`d` AS `d`,`dt`.`b` AS `b`,`dt`.`c` AS `c` from `test`.`t3` join (/* select#2 */ select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`b` = `test`.`t3`.`d` group by `test`.`t1`.`b`,`test`.`t2`.`c`) `dt` where `dt`.`b` = `test`.`t3`.`d`
+DROP TABLE t1,t2,t3;