Merge.

3 files changed, 96 insertions, 1 deletions

diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index bc8fec82275..fed5b23dc7b 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -848,3 +848,55 @@ Warnings:
 Note	1003	select `test`.`t3`.`b` AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t3` left join (`test`.`t2` join `test`.`t1`) on((`test`.`t3`.`a` <> 0)) where ((`test`.`t1`.`a` = `test`.`t1`.`a`) or (`test`.`t3`.`b` <> 0))
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
+#
+# LP bug #806510: subquery with outer reference
+#                 to a derived_table/view                    
+#
+CREATE TABLE t1 (a int) ;
+INSERT INTO t1 VALUES (4), (NULL);
+CREATE TABLE t2 (a int) ;
+INSERT INTO t2 VALUES (8), (0);
+CREATE TABLE t3 (a int, b int) ;
+INSERT INTO t3 VALUES (7,8);
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT * FROM  t1 t
+WHERE EXISTS (SELECT t3.a FROM t3, t2
+WHERE t2.a = t3.b AND t.a != 0);
+a
+4
+EXPLAIN
+SELECT * FROM  t1 t
+WHERE EXISTS (SELECT t3.a FROM t3, t2
+WHERE t2.a = t3.b AND t.a != 0);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t3	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT * FROM (SELECT * FROM t1) t
+WHERE EXISTS (SELECT t3.a FROM t3, t2
+WHERE t2.a = t3.b AND t.a != 0);
+a
+4
+EXPLAIN
+SELECT * FROM (SELECT * FROM t1) t
+WHERE EXISTS (SELECT t3.a FROM t3, t2
+WHERE t2.a = t3.b AND t.a != 0);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+3	DEPENDENT SUBQUERY	t3	system	NULL	NULL	NULL	NULL	1	
+3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT * FROM v1 t
+WHERE EXISTS (SELECT t3.a FROM t3, t2
+WHERE t2.a = t3.b AND t.a != 0);
+a
+4
+EXPLAIN
+SELECT * FROM v1 t
+WHERE EXISTS (SELECT t3.a FROM t3, t2
+WHERE t2.a = t3.b AND t.a != 0);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t3	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index 7b890e4d4aa..e352d0b5888 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -3033,7 +3033,7 @@ t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND
 t1.metaid = t2.metaid AND t1.affiliateid = '2';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t6	system	PRIMARY	NULL	NULL	NULL	1	
-1	SIMPLE	t1	ref	t1_affiliateid,t1_metaid	t1_affiliateid	4	const	1	Using index condition
+1	SIMPLE	t1	ref	t1_affiliateid,t1_metaid	t1_affiliateid	4	const	1	
 1	SIMPLE	t4	ref	PRIMARY,t4_formatclassid,t4_formats_idx	t4_formats_idx	1	const	1	Using index condition; Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan
 1	SIMPLE	t5	eq_ref	PRIMARY,t5_formattypeid	PRIMARY	4	test.t4.formatclassid	1	Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index e5c64840d39..26f428571ee 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -460,3 +460,46 @@ SELECT t3.b, v1.a
 
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # LP bug #806510: subquery with outer reference
+--echo #                 to a derived_table/view                    
+--echo #
+
+CREATE TABLE t1 (a int) ;
+INSERT INTO t1 VALUES (4), (NULL);
+
+CREATE TABLE t2 (a int) ;
+INSERT INTO t2 VALUES (8), (0);
+
+CREATE TABLE t3 (a int, b int) ;
+INSERT INTO t3 VALUES (7,8);
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+SELECT * FROM  t1 t
+  WHERE EXISTS (SELECT t3.a FROM t3, t2
+                  WHERE t2.a = t3.b AND t.a != 0);
+EXPLAIN
+SELECT * FROM  t1 t
+  WHERE EXISTS (SELECT t3.a FROM t3, t2
+                  WHERE t2.a = t3.b AND t.a != 0);
+
+SELECT * FROM (SELECT * FROM t1) t
+  WHERE EXISTS (SELECT t3.a FROM t3, t2
+                  WHERE t2.a = t3.b AND t.a != 0);
+EXPLAIN
+SELECT * FROM (SELECT * FROM t1) t
+  WHERE EXISTS (SELECT t3.a FROM t3, t2
+                  WHERE t2.a = t3.b AND t.a != 0);
+
+SELECT * FROM v1 t
+  WHERE EXISTS (SELECT t3.a FROM t3, t2
+                  WHERE t2.a = t3.b AND t.a != 0);
+EXPLAIN
+SELECT * FROM v1 t
+  WHERE EXISTS (SELECT t3.a FROM t3, t2
+                  WHERE t2.a = t3.b AND t.a != 0);
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;