revision-id: d7ca6d56d9987d62ee848f08ad6f5b275fd32fe4 (mariadb-10.3.6-164-gd7ca6d5) parent(s): 5b4d6595d26aaaf0bf8bb3c9171cf1da96306a7c author: Igor Babaev committer: Igor Babaev timestamp: 2019-02-23 14:53:27 -0800 message: MDEV-18679 Server crashes in JOIN::optimize The bug manifested itself when executing a query with materialized view/derived/CTE whose specification was a SELECT query contained another materialized derived and impossible WHERE/HAVING condition was detected for this SELECT. As soon as such condition is detected the join structures of all derived tables used in the SELECT are destroyed. So optimization of the queries specifying these derived tables is impossible. Besides it's not needed. In 10.3 optimization of a materialized derived table is performed before detection of impossible WHERE/HAVING condition in the embedding SELECT. --- mysql-test/main/derived_cond_pushdown.result | 22 ++++++++++++++++++++++ mysql-test/main/derived_cond_pushdown.test | 23 +++++++++++++++++++++++ sql/sql_derived.cc | 9 +++++++++ 3 files changed, 54 insertions(+) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index e7e2df4..dae127b 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -16743,3 +16743,25 @@ id username id userid logindate set join_cache_level=default; DROP TABLE t1,t2; # End of 10.3 tests +# +# MDEV-18679: materialized view with SELECT S containing materialized +# derived when impossible WHERE has been detected for S +# +create table t1 (pk int, f varchar(1)); +insert into t1 values +(3,'y'), (1,'x'), (7,'z'); +create view v1 as +select t1.f +from t1, (select distinct * from t1) t +where t.f = t1.f and 1 = 0 +group by t1.f; +select * from v1; +f +explain select * from v1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 0 Const row not found +2 DERIVED NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +3 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary +drop view v1; +drop table t1; +# End of 10.4 tests diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index 076d39c..ef28dcf 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -3263,3 +3263,26 @@ set join_cache_level=default; DROP TABLE t1,t2; --echo # End of 10.3 tests + +--echo # +--echo # MDEV-18679: materialized view with SELECT S containing materialized +--echo # derived when impossible WHERE has been detected for S +--echo # + +create table t1 (pk int, f varchar(1)); +insert into t1 values + (3,'y'), (1,'x'), (7,'z'); + +create view v1 as +select t1.f + from t1, (select distinct * from t1) t + where t.f = t1.f and 1 = 0 +group by t1.f; + +select * from v1; +explain select * from v1; + +drop view v1; +drop table t1; + +--echo # End of 10.4 tests diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index c8a892a..9919c30 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1002,6 +1002,15 @@ bool mysql_derived_optimize(THD *thd, LEX *lex, TABLE_LIST *derived) if (unit->optimized) DBUG_RETURN(FALSE); unit->optimized= TRUE; + if (!join) + { + /* + This happens when derived is used in SELECT for which + zer_result_cause != 0. + In this case join is already destroyed. + */ + DBUG_RETURN(FALSE); + } } if ((res= join->optimize())) goto err;