MDEV-30081: Crash with split_materialized over a VIEWbb-10.6-mdev30081-variant2

Consider a view and use of Split Materialized optimization to read it:

  create view v1 as (SELECT ... id1 FROM t1 GROUP BY id1);
  SELECT ... LEFT JOIN v1 ON v1.id1 = tbl2.col

Split Materialized will need to construct ref access inside the view:

  t1.id1= {outer reference to tbl2.col}

It will do so by cloning both parts of the equality (v1.id1 =tbl2.col)
and then "pushing it down" into subquery by calling

  item->walk(&Item::set_fields_as_dependent_processor, parent_join->select_lex)

for both parts of the equality.
The problem was that left part of the equality was "v1.id1", namely an
Item_direct_view_ref, which did not implement
set_fields_as_dependent_processor() function.

Failure to do that resulted in Item_direct_view_ref reporting invalid
used_tables() in some cases (the case we've hit required that
Item_direct_view_ref is inside an outer join, and the column it refers
to is marked as constant)

Fixed by implementing Item_direct_ref::set_fields_as_dependent_processor().

3 files changed, 183 insertions, 0 deletions

diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result
index 74876836a53..a2c56c1217b 100644
--- a/mysql-test/main/derived_split_innodb.result
+++ b/mysql-test/main/derived_split_innodb.result
@@ -287,4 +287,80 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DERIVED	t4	ALL	NULL	NULL	NULL	NULL	40	Using filesort
 drop table t3, t4;
 # End of 10.3 tests
+#
+# MDEV-30081: Crash with split_materialized over a VIEW
+#
+CREATE TABLE t1 ( id int, id1 int, PRIMARY KEY (id), KEY id1 (id1)) engine=innodb;
+insert into t1 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+CREATE TABLE t2 ( id int PRIMARY KEY) engine=innodb;
+insert into t2 values (3),(4),(7);
+CREATE TABLE t3 ( id int, abc int, PRIMARY KEY (id), KEY (abc)) engine=innodb;
+insert into t3 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+CREATE TABLE t4 ( id int, abc int, PRIMARY KEY (id)) engine=innodb;
+insert into t4 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+CREATE TABLE t5 ( id int, abc int, PRIMARY KEY (id), KEY abc (abc) ) engine=innodb;
+insert into t5 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+CREATE TABLE t6 ( id int, abc int, PRIMARY KEY (id), KEY abc (abc) ) engine=innodb;
+insert into t6 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+CREATE TABLE t7 ( id int, wid int, PRIMARY KEY (id), KEY (wid) ) engine=innodb;
+insert into t7 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+CREATE TABLE t8 ( id int, wid int, PRIMARY KEY (id), KEY wid (wid)) engine=innodb;
+insert into t8 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+CREATE TABLE t9 ( id int, abc int, wid int, PRIMARY KEY (id), KEY (wid), KEY id (id)) engine=innodb;
+insert into t9 values (4,4,6),(7,7,7);
+CREATE TABLE t10 ( id int, abc int, PRIMARY KEY (id), KEY (abc) ) engine=innodb;
+insert into t10 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+CREATE TABLE t11 ( id int, id1 int, PRIMARY KEY (id), KEY id1 (id1) ) engine=innodb;
+insert into t11 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+CREATE TABLE t12 ( id int PRIMARY KEY) engine=innodb;
+insert into t12 values (3),(4),(7);
+CREATE TABLE t13 ( id int,  id1 int, wid int, PRIMARY KEY (id), KEY (id1), KEY (wid)) engine=innodb;
+insert into t13 values (4,4,6),(7,7,7);
+CREATE TABLE t14 ( id int PRIMARY KEY ) engine=innodb;
+insert into t14 values (3),(4),(7);
+CREATE TABLE t15 ( id int, abc int, PRIMARY KEY (id), KEY (abc) ) engine=innodb;
+insert into t15 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+CREATE TABLE t16 ( id int, id1 int, PRIMARY KEY (id), KEY id1 (id1)) engine=innodb;
+insert into t16 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+CREATE TABLE t17 ( id int PRIMARY KEY) engine=innodb;
+insert into t17 values (3),(4),(7);
+CREATE TABLE t18 ( wtid int) engine=innodb;
+insert into t18 values (3),(4),(7);
+CREATE TABLE t19 ( wtid int PRIMARY KEY) engine=innodb;
+insert into t19 values (1),(2),(7);
+CREATE TABLE t20 ( wid int, KEY wid (wid)) engine=innodb;
+insert into t20 values (42),(13),(25),(5);
+CREATE TABLE t21 ( wid int, wtid int, otid int, oid int, PRIMARY KEY (wid), KEY (wtid), KEY (otid), KEY (oid) ) engine=innodb;
+insert into t21 values (6,30,6,6),(7,17,7,7);
+create view v1 as (SELECT id, id1 FROM t16 GROUP BY id1);
+select * from (
+SELECT t21.*
+FROM (((((((((((((((((((((t21
+JOIN t14 on(t21.oid = t14.id))
+JOIN t15 on(t21.oid = t15.abc))
+JOIN t17  on(t21.oid = t17.id))
+JOIN t19  on(t21.wtid = t19.wtid))
+JOIN t20 on(t21.wid = t20.wid))
+JOIN t6 on(t21.oid = t6.id))
+JOIN t5  on(t21.oid = t5.id))
+JOIN t10  on(t21.oid = t10.id))
+JOIN t8  on(t21.wid = t8.wid))
+JOIN t11  on(t21.oid = t11.id))
+JOIN t1  on(t21.oid = t1.id ))
+JOIN t13  on(t21.oid = t13.id))
+JOIN t9  on(t21.oid = t9.id))
+JOIN t4  on(t21.oid = t4.id))
+LEFT JOIN (SELECT t3.* FROM ((t3 JOIN t14 on(t3.abc = t14.id)) )) dt on(t21.oid = dt.id AND t21.otid = 14))
+JOIN t2  on(t21.oid = t2.id))
+JOIN t12  on(t21.oid = t12.id))
+JOIN t7  on(t7.wid = t21.wid))
+JOIN v1  on(v1.id1 = t14.id))
+LEFT JOIN v1 cte_1 on(cte_1.id1 = dt.abc))
+LEFT JOIN v1 cte_2 on(cte_2.id1 = t13.id1)) ) dt1
+WHERE dt1.wid = 7;
+wid	wtid	otid	oid
+drop view v1;
+drop table t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, t15,
+t16, t17, t18, t19, t20, t21;
+# End of 10.4 tests
 SET GLOBAL innodb_stats_persistent=@save_innodb_stats_persistent;
diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test
index 1ebe27cd12c..850ebbca4fc 100644
--- a/mysql-test/main/derived_split_innodb.test
+++ b/mysql-test/main/derived_split_innodb.test
@@ -233,4 +233,104 @@ drop table t3, t4;
 
 --echo # End of 10.3 tests
 
+--echo #
+--echo # MDEV-30081: Crash with split_materialized over a VIEW
+--echo #
+CREATE TABLE t1 ( id int, id1 int, PRIMARY KEY (id), KEY id1 (id1)) engine=innodb;
+insert into t1 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+
+CREATE TABLE t2 ( id int PRIMARY KEY) engine=innodb;
+insert into t2 values (3),(4),(7);
+
+CREATE TABLE t3 ( id int, abc int, PRIMARY KEY (id), KEY (abc)) engine=innodb;
+insert into t3 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+
+CREATE TABLE t4 ( id int, abc int, PRIMARY KEY (id)) engine=innodb;
+insert into t4 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+
+CREATE TABLE t5 ( id int, abc int, PRIMARY KEY (id), KEY abc (abc) ) engine=innodb;
+insert into t5 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+
+CREATE TABLE t6 ( id int, abc int, PRIMARY KEY (id), KEY abc (abc) ) engine=innodb;
+insert into t6 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+
+CREATE TABLE t7 ( id int, wid int, PRIMARY KEY (id), KEY (wid) ) engine=innodb;
+insert into t7 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+
+CREATE TABLE t8 ( id int, wid int, PRIMARY KEY (id), KEY wid (wid)) engine=innodb;
+insert into t8 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+
+CREATE TABLE t9 ( id int, abc int, wid int, PRIMARY KEY (id), KEY (wid), KEY id (id)) engine=innodb;
+insert into t9 values (4,4,6),(7,7,7);
+
+CREATE TABLE t10 ( id int, abc int, PRIMARY KEY (id), KEY (abc) ) engine=innodb;
+insert into t10 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+
+CREATE TABLE t11 ( id int, id1 int, PRIMARY KEY (id), KEY id1 (id1) ) engine=innodb;
+insert into t11 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+
+CREATE TABLE t12 ( id int PRIMARY KEY) engine=innodb;
+insert into t12 values (3),(4),(7);
+
+CREATE TABLE t13 ( id int,  id1 int, wid int, PRIMARY KEY (id), KEY (id1), KEY (wid)) engine=innodb;
+insert into t13 values (4,4,6),(7,7,7);
+
+CREATE TABLE t14 ( id int PRIMARY KEY ) engine=innodb;
+insert into t14 values (3),(4),(7);
+
+CREATE TABLE t15 ( id int, abc int, PRIMARY KEY (id), KEY (abc) ) engine=innodb;
+insert into t15 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+
+CREATE TABLE t16 ( id int, id1 int, PRIMARY KEY (id), KEY id1 (id1)) engine=innodb;
+insert into t16 values (1,17),(2,15),(3,49),(4,3),(5,45),(6,38),(7,17);
+
+CREATE TABLE t17 ( id int PRIMARY KEY) engine=innodb;
+insert into t17 values (3),(4),(7);
+
+CREATE TABLE t18 ( wtid int) engine=innodb;
+insert into t18 values (3),(4),(7);
+
+CREATE TABLE t19 ( wtid int PRIMARY KEY) engine=innodb;
+insert into t19 values (1),(2),(7);
+
+CREATE TABLE t20 ( wid int, KEY wid (wid)) engine=innodb;
+insert into t20 values (42),(13),(25),(5);
+
+CREATE TABLE t21 ( wid int, wtid int, otid int, oid int, PRIMARY KEY (wid), KEY (wtid), KEY (otid), KEY (oid) ) engine=innodb;
+insert into t21 values (6,30,6,6),(7,17,7,7);
+
+create view v1 as (SELECT id, id1 FROM t16 GROUP BY id1);
+
+select * from (
+ SELECT t21.*
+ FROM (((((((((((((((((((((t21
+                           JOIN t14 on(t21.oid = t14.id))
+                          JOIN t15 on(t21.oid = t15.abc))
+                         JOIN t17  on(t21.oid = t17.id))
+                        JOIN t19  on(t21.wtid = t19.wtid))
+                       JOIN t20 on(t21.wid = t20.wid))
+                      JOIN t6 on(t21.oid = t6.id))
+                     JOIN t5  on(t21.oid = t5.id))
+                    JOIN t10  on(t21.oid = t10.id))
+                   JOIN t8  on(t21.wid = t8.wid))
+                  JOIN t11  on(t21.oid = t11.id))
+                 JOIN t1  on(t21.oid = t1.id ))
+                JOIN t13  on(t21.oid = t13.id))
+               JOIN t9  on(t21.oid = t9.id))
+              JOIN t4  on(t21.oid = t4.id))
+            LEFT JOIN (SELECT t3.* FROM ((t3 JOIN t14 on(t3.abc = t14.id)) )) dt on(t21.oid = dt.id AND t21.otid = 14))
+            JOIN t2  on(t21.oid = t2.id))
+           JOIN t12  on(t21.oid = t12.id))
+          JOIN t7  on(t7.wid = t21.wid))
+         JOIN v1  on(v1.id1 = t14.id))
+       LEFT JOIN v1 cte_1 on(cte_1.id1 = dt.abc))
+      LEFT JOIN v1 cte_2 on(cte_2.id1 = t13.id1)) ) dt1
+WHERE dt1.wid = 7;
+
+drop view v1;
+drop table t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, t15,
+  t16, t17, t18, t19, t20, t21;
+
+--echo # End of 10.4 tests
+
 SET GLOBAL innodb_stats_persistent=@save_innodb_stats_persistent;
diff --git a/sql/item.h b/sql/item.h
index 87b0f93e7be..2d7460206a8 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -6194,6 +6194,13 @@ public:
   Item *field_transformer_for_having_pushdown(THD *, uchar *) override
   { return this; }
   Item *remove_item_direct_ref() override { return this; }
+  bool set_fields_as_dependent_processor(void *arg) override
+  {
+    DBUG_ASSERT(can_be_depended);
+    depended_from= (st_select_lex *) arg;
+    return 0;
+  }
+
 };