Fixed LP bug #809206.

The bitmap of used tables must be evaluated for the select list of every
materialized derived table / view and saved in a dedicated field.
This is also applied to materialized subqueries.

1 files changed, 47 insertions, 1 deletions

diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index 9434cccec0d..5e9eeb99732 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -946,7 +946,7 @@ DROP TABLE t1,t2,t3;
 # LP bug #806504: right join over a view/derived table           
 #
 CREATE TABLE t1 (a int, b int) ;
-INSERT IGNORE INTO t1 VALUES (0,0);
+INSERT INTO t1 VALUES (0,0);
 CREATE TABLE t2 (a int) ;
 INSERT INTO t2 VALUES (0), (0);
 CREATE VIEW v1 AS SELECT * FROM t1;
@@ -978,3 +978,49 @@ Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t1` left join `test`.`t2` on((0 <> 0)) where <expr_cache><0>(<in_optimizer>(0,<exists>(select 0 from `test`.`t1` where (<cache>(0) = 0))))
 DROP VIEW v1;
 DROP TABLE t1,t2;
+#
+# LP bug #809206: DISTINCT in derived table / view           
+#
+CREATE TABLE t1 (a int) ;
+INSERT INTO t1 VALUES (0);
+CREATE TABLE t2 (a  varchar(32), b int, KEY (a)) ;
+INSERT INTO t2 VALUES
+('j',28), ('c',29), ('i',26), ('c',29), ('k',27),
+('j',28), ('c',29), ('i',25), ('d',26), ('k',27);
+CREATE TABLE t3 (a varchar(32));
+INSERT INTO t3 VALUES ('j'), ('c');
+CREATE VIEW v1 AS SELECT DISTINCT t2.b FROM t1,t2,t3 WHERE t3.a = t2.a;
+SELECT DISTINCT t2.b FROM t1,t2,t3 WHERE t3.a = t2.a;
+b
+28
+29
+EXPLAIN 
+SELECT DISTINCT t2.b FROM t1,t2,t3 WHERE t3.a = t2.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	Using temporary
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	SIMPLE	t2	ref	a	a	35	test.t3.a	2	
+SELECT * FROM (SELECT DISTINCT t2.b FROM t1,t2,t3 WHERE t3.a = t2.a) t;
+b
+28
+29
+EXPLAIN
+SELECT * FROM (SELECT DISTINCT t2.b FROM t1,t2,t3 WHERE t3.a = t2.a) t;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	
+2	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	Using temporary
+2	DERIVED	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DERIVED	t2	ref	a	a	35	test.t3.a	2	
+SELECT * FROM v1;
+b
+28
+29
+EXPLAIN 
+SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	
+2	DERIVED	t1	system	NULL	NULL	NULL	NULL	1	Using temporary
+2	DERIVED	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DERIVED	t2	ref	a	a	35	test.t3.a	2	
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;