Fixed LP bug #878199.

The function JOIN::drop_unused_derived_keys could erroneously set
the value of REF::key to 0 for a joined materialized view/derived table
in the case when no REF access to the table was used by the query
execution plan. This could cause a crash of the server.

1 files changed, 23 insertions, 0 deletions

diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index d3ddc17de3c..d6ec40670af 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -973,5 +973,28 @@ SELECT v1.a FROM v1
 DROP VIEW v1;
 DROP TABLE t1,t2;
 
+--echo #
+--echo # LP bug #878199: join of two materialized views
+--echo #
+
+CREATE TABLE t1 (a int, b varchar(1)) ;
+INSERT INTO t1 VALUES (7,'c'), (3,'h'), (7,'c');
+
+CREATE TABLE t2 (b varchar(1)) ;
+INSERT INTO t2 VALUES ('p'), ('c'), ('j'), ('c'), ('p');
+
+CREATE VIEW v1 AS SELECT * FROM t1 GROUP BY a,b;
+
+CREATE VIEW v2 AS SELECT * FROM t2 GROUP BY b;
+
+SET SESSION optimizer_switch = 'derived_with_keys=on';
+
+SELECT v1.a FROM v1,v2 WHERE v2.b = v1.b ORDER BY 1;
+EXPLAIN
+SELECT v1.a FROM v1,v2 WHERE v2.b = v1.b ORDER BY 1;
+
+DROP VIEW v1,v2;
+DROP TABLE t1,t2;
+
 # The following command must be the last one the file 
 set optimizer_switch=@exit_optimizer_switch;