Fix of LP BUG#872775.

The problem was that merged views has its own nest_level numbering =>
when we compare nest levels we should take into considiration basis (i.e. 0 level),
if it is different then nest levels are not comparable.

9 files changed, 84 insertions, 4 deletions

diff --git a/mysql-test/r/subselect_cache.result b/mysql-test/r/subselect_cache.result
index c4826eeb50f..8758497f7d7 100644
--- a/mysql-test/r/subselect_cache.result
+++ b/mysql-test/r/subselect_cache.result
@@ -1,3 +1,5 @@
+drop table if exists t1,t2,t3,t4,t5;
+drop view if exists v1;
 set optimizer_switch='subquery_cache=on';
 create table t1 (a int, b int);
 insert into t1 values (1,2),(3,4),(1,2),(3,4),(3,4),(4,5),(4,5),(5,6),(5,6),(4,5);
@@ -3354,5 +3356,31 @@ f1	f2	f3	f3
 7	0	0	0
 7	0	0	0
 drop tables t1, t2, t3;
+#
+# Test of LP BUG#872775 view with "outer references" bug
+#
+set @@optimizer_switch= default;
+set optimizer_switch='subquery_cache=on';
+CREATE TABLE t1 (a int) ;
+CREATE TABLE t2 (b int, c varchar(1) NOT NULL ) ;
+INSERT INTO t2 VALUES (1,'x'),(2,'y');
+CREATE TABLE t3 (a int) ;
+CREATE TABLE t4 ( pk int(11) NOT NULL , b int(11) NOT NULL ) ;
+INSERT INTO t4 VALUES (26,9),(27,5),(28,0),(29,3);
+CREATE OR REPLACE VIEW v1 AS
+SELECT t2.b
+FROM t1
+JOIN t2
+WHERE t2 .c > (
+SELECT t2.c FROM t3
+);
+SELECT * FROM t4 WHERE b NOT IN ( SELECT * FROM v1 );
+pk	b
+26	9
+27	5
+28	0
+29	3
+drop view v1;
+drop table t1,t2,t3,t4;
 # restore default
 set @@optimizer_switch= default;
diff --git a/mysql-test/t/subselect_cache.test b/mysql-test/t/subselect_cache.test
index 3d8de4e4808..6bf5028b6cf 100644
--- a/mysql-test/t/subselect_cache.test
+++ b/mysql-test/t/subselect_cache.test
@@ -1,4 +1,10 @@
 
+--disable_warnings
+drop table if exists t1,t2,t3,t4,t5;
+drop view if exists v1;
+--enable_warnings
+
+
 set optimizer_switch='subquery_cache=on';
 
 create table t1 (a int, b int);
@@ -1664,5 +1670,33 @@ FROM t1
 WHERE t2.f1 OR t2.f3 );
 drop tables t1, t2, t3;
 
+--echo #
+--echo # Test of LP BUG#872775 view with "outer references" bug
+--echo #
+set @@optimizer_switch= default;
+set optimizer_switch='subquery_cache=on';
+CREATE TABLE t1 (a int) ;
+
+CREATE TABLE t2 (b int, c varchar(1) NOT NULL ) ;
+INSERT INTO t2 VALUES (1,'x'),(2,'y');
+
+CREATE TABLE t3 (a int) ;
+
+CREATE TABLE t4 ( pk int(11) NOT NULL , b int(11) NOT NULL ) ;
+INSERT INTO t4 VALUES (26,9),(27,5),(28,0),(29,3);
+
+CREATE OR REPLACE VIEW v1 AS
+SELECT t2.b
+FROM t1
+JOIN t2
+WHERE t2 .c > (
+        SELECT t2.c FROM t3
+        );
+
+SELECT * FROM t4 WHERE b NOT IN ( SELECT * FROM v1 );
+
+drop view v1;
+drop table t1,t2,t3,t4;
+
 --echo # restore default
 set @@optimizer_switch= default;
diff --git a/sql/item.cc b/sql/item.cc
index f5ce0fbc7a7..7056e1dd5d2 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -755,7 +755,9 @@ bool Item_ident::remove_dependence_processor(uchar * arg)
 bool Item_ident::collect_outer_ref_processor(uchar *param)
 {
   Collect_deps_prm *prm= (Collect_deps_prm *)param;
-  if (depended_from && depended_from->nest_level < prm->nest_level)
+  if (depended_from && 
+      depended_from->nest_level_base == prm->nest_level_base &&
+      depended_from->nest_level < prm->nest_level)
     prm->parameters->add_unique(this, &cmp_items);
   return FALSE;
 }
diff --git a/sql/item.h b/sql/item.h
index bfe0d78ed29..fa6918d8484 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -505,6 +505,7 @@ typedef void (*Cond_traverser) (const Item *item, void *arg);
 class Item_equal;
 class COND_EQUAL;
 
+class st_select_lex_unit;
 
 class Item {
   Item(const Item &);			/* Prevent use of these */
@@ -1159,8 +1160,10 @@ public:
   }
   struct Collect_deps_prm
   {
-    int nest_level;
     List<Item> *parameters;
+    /* unit from which we count nest_level */
+    st_select_lex_unit *nest_level_base;
+    int nest_level;
   };
   /**
     Collect outer references
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 22c62f0a6d7..4ab2e2e2c69 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -599,7 +599,9 @@ bool Item_subselect::exec()
 
 void Item_subselect::get_cache_parameters(List<Item> &parameters)
 {
-  Collect_deps_prm prm= { unit->first_select()->nest_level, &parameters };
+  Collect_deps_prm prm= {&parameters,
+    unit->first_select()->nest_level_base,
+    unit->first_select()->nest_level};
   walk(&Item::collect_outer_ref_processor, TRUE, (uchar*)&prm);
 }
 
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 65314a0ff91..063406990cb 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -359,7 +359,9 @@ bool Item_sum::collect_outer_ref_processor(uchar *param)
 {
   Collect_deps_prm *prm= (Collect_deps_prm *)param;
   SELECT_LEX *ds;
-  if ((ds= depended_from()) && ds->nest_level < prm->nest_level)
+  if ((ds= depended_from()) &&
+      ds->nest_level_base == prm->nest_level_base &&
+      ds->nest_level < prm->nest_level)
     prm->parameters->add_unique(this, &cmp_items);
   return FALSE;
 }
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index cc10cd5dee3..8513f73b90c 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -339,6 +339,7 @@ void lex_start(THD *thd)
   lex->event_parse_data= NULL;
   lex->profile_options= PROFILE_NONE;
   lex->nest_level=0 ;
+  lex->select_lex.nest_level_base= &lex->unit;
   lex->allow_sum_func= 0;
   lex->in_sum_func= NULL;
   lex->protect_against_global_read_lock= FALSE;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 32ad4f414c9..4caf2a1dbb3 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -684,6 +684,13 @@ public:
   ulong table_join_options;
   uint in_sum_expr;
   uint select_number; /* number of select (used for EXPLAIN) */
+
+  /*
+    nest_levels are local to the query or VIEW,
+    and that view merge procedure does not re-calculate them.
+    So we also have to remember unit against which we count levels.
+  */
+  SELECT_LEX_UNIT *nest_level_base;
   int nest_level;     /* nesting level of select */
   Item_sum *inner_sum_func_list; /* list of sum func in nested selects */ 
   uint with_wild; /* item list contain '*' */
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 6202b9d888a..a1c373ce426 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -5917,6 +5917,7 @@ mysql_new_select(LEX *lex, bool move_down)
     DBUG_RETURN(1);
   }
   select_lex->nest_level= lex->nest_level;
+  select_lex->nest_level_base= &thd->lex->unit;
   if (move_down)
   {
     SELECT_LEX_UNIT *unit;