Added the reported test case for LP bug #823237 (a duplicate of bug #823189).

2 files changed, 89 insertions, 0 deletions

diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index fc60d70317e..c8546f92bf9 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -4414,6 +4414,55 @@ a	a
 DROP VIEW v1;
 DROP TABLE t1,t2,t3,t4;
 #
+# LP bug #823237: dependent subquery with LEFT JOIN
+#                 referencing view in WHERE
+# (duplicate of LP bug #823189)
+# 
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 ( b int, d int, e int);
+INSERT INTO t2 VALUES (7,8,0);
+CREATE TABLE t3 ( c int);
+INSERT INTO t3 VALUES (0);
+CREATE TABLE t4 (a int, b int, c int);
+INSERT INTO t4 VALUES (93,1,0), (95,NULL,0);
+CREATE VIEW v4 AS SELECT * FROM t4;
+EXPLAIN EXTENDED
+SELECT * FROM t3 , t4 
+WHERE t4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d )
+WHERE t2.b > t4.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	100.00	
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t2	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
+Warnings:
+Note	1276	Field or reference 'test.t4.b' of SELECT #2 was resolved in SELECT #1
+Note	1003	select 0 AS `c`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c` from `test`.`t3` join `test`.`t4` where (`test`.`t4`.`c` <= <expr_cache><`test`.`t4`.`b`>((select 0 from `test`.`t2` left join `test`.`t1` on(0) where (7 > `test`.`t4`.`b`))))
+SELECT * FROM t3 , t4 
+WHERE t4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d )
+WHERE t2.b > t4.b);
+c	a	b	c
+0	93	1	0
+EXPLAIN EXTENDED
+SELECT * FROM t3, v4 
+WHERE v4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d )
+WHERE t2.b > v4.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	100.00	
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t2	system	NULL	NULL	NULL	NULL	1	100.00	
+2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
+Warnings:
+Note	1276	Field or reference 'v4.b' of SELECT #2 was resolved in SELECT #1
+Note	1003	select 0 AS `c`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t4`.`c` AS `c` from `test`.`t3` join `test`.`t4` where (`test`.`t4`.`c` <= <expr_cache><`test`.`t4`.`b`>((select 0 from `test`.`t2` left join `test`.`t1` on(0) where (7 > `test`.`t4`.`b`))))
+SELECT * FROM t3, v4 
+WHERE v4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d )
+WHERE t2.b > v4.b);
+c	a	b	c
+0	93	1	0
+DROP VIEW v4;
+DROP TABLE t1,t2,t3,t4;
+#
 # BUG#833600: Wrong result with view + outer join + uncorrelated subquery (non-semijoin) 
 #
 CREATE TABLE t1 ( a int, b int );
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index bd3b485034d..3b590140d56 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -4345,6 +4345,46 @@ DROP TABLE t1,t2,t3,t4;
 
 
 --echo #
+--echo # LP bug #823237: dependent subquery with LEFT JOIN
+--echo #                 referencing view in WHERE
+--echo # (duplicate of LP bug #823189)
+--echo # 
+
+CREATE TABLE t1 (a int);
+
+CREATE TABLE t2 ( b int, d int, e int);
+INSERT INTO t2 VALUES (7,8,0);
+
+CREATE TABLE t3 ( c int);
+INSERT INTO t3 VALUES (0);
+
+CREATE TABLE t4 (a int, b int, c int);
+INSERT INTO t4 VALUES (93,1,0), (95,NULL,0);
+
+CREATE VIEW v4 AS SELECT * FROM t4;
+
+EXPLAIN EXTENDED
+SELECT * FROM t3 , t4 
+  WHERE t4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d )
+                   WHERE t2.b > t4.b);
+SELECT * FROM t3 , t4 
+  WHERE t4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d )
+                   WHERE t2.b > t4.b);
+
+
+EXPLAIN EXTENDED
+SELECT * FROM t3, v4 
+  WHERE v4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d )
+                   WHERE t2.b > v4.b);
+
+SELECT * FROM t3, v4 
+  WHERE v4.c <= (SELECT t2.e FROM t2 LEFT JOIN t1 ON ( t1.a = t2.d )
+                   WHERE t2.b > v4.b);
+
+DROP VIEW v4;
+DROP TABLE t1,t2,t3,t4;
+
+--echo #
 --echo # BUG#833600: Wrong result with view + outer join + uncorrelated subquery (non-semijoin) 
 --echo #