From c8e7288e7332962bdebc5ae658bbd839d0b26254 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Thu, 12 Jan 2023 15:55:40 +0300 Subject: MDEV-30081: Crash with split_materialized over a VIEW 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(). --- mysql-test/main/derived_split_innodb.result | 76 +++++++++++++++++++++ mysql-test/main/derived_split_innodb.test | 100 ++++++++++++++++++++++++++++ sql/item.h | 7 ++ 3 files changed, 183 insertions(+) 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; + } + }; -- cgit v1.2.1