Fixed LP bug #872735.

This bug happened because the maps of covering keys for mergeable derived
tables/views was not recalculated after the derived tables/vies had been
merged into the main query.

1 files changed, 33 insertions, 0 deletions

diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index 1c8cbec8a3e..9f7e24481a0 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -877,5 +877,38 @@ SELECT STRAIGHT_JOIN *
 DROP VIEW v2,v3;
 DROP TABLE t1,t2,t3,t4,t5;
 
+--echo #
+--echo # LP bug #872735: derived used in a NOT IN subquery
+--echo #                 
+
+CREATE TABLE t1 (b int NOT NULL);
+INSERT INTO t1 VALUES (9), (7);
+
+CREATE TABLE t2 (a int NOT NULL) ;
+INSERT INTO t2 VALUES (1), (2);
+
+CREATE TABLE t3 (
+  a int NOT NULL , c int NOT NULL, d varchar(1) NOT NULL, 
+  KEY (c,a) , PRIMARY KEY (a)
+);
+INSERT INTO t3 VALUES
+  (14,4,'a'), (15,7,'b'), (16,4,'c'), (17,1,'d'), (18,9,'e'),
+  (19,4,'f'), (20,8,'g');
+
+SET SESSION optimizer_switch='derived_merge=on,subquery_cache=off';
+
+--echo # The following two EXPLAINs must return the same execution plan
+EXPLAIN
+SELECT * FROM t1 , t2
+  WHERE (t2.a ,t1.b) NOT IN (SELECT DISTINCT c,a  FROM t3 t);
+EXPLAIN 
+SELECT * FROM t1 , t2
+  WHERE (t2.a ,t1.b) NOT IN (SELECT DISTINCT c,a  FROM (SELECT * FROM t3) t);
+
+SELECT * FROM t1 , t2
+  WHERE (t2.a ,t1.b) NOT IN (SELECT DISTINCT c,a  FROM (SELECT * FROM t3) t);
+
+DROP TABLE t1,t2,t3;
+
 # The following command must be the last one the file 
 set optimizer_switch=@exit_optimizer_switch;