3 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 e7e2df4f582..dae127b12ea 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 076d39c1abd..ef28dcf1d97 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 c8a892a0c36..9919c30af6d 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;