← Back to team overview

maria-developers team mailing list archive

Rev 2697: BUG#31480: Incorrect result for nested subquery when executed via semi join in file:///home/psergey/dev/mysql-6.0-look/

 

At file:///home/psergey/dev/mysql-6.0-look/

------------------------------------------------------------
revno: 2697
revision-id: psergey@xxxxxxxxxxxx-20090704040131-bzcjcds3siutn6sc
parent: jperkin@xxxxxxx-20090423215644-h7ssug9w1hdgzn39
committer: Sergey Petrunya <psergey@xxxxxxxxxxxx>
branch nick: mysql-6.0-look
timestamp: Sat 2009-07-04 08:01:31 +0400
message:
  BUG#31480: Incorrect result for nested subquery when executed via semi join
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2009-03-19 17:03:58 +0000
+++ b/mysql-test/r/subselect_sj.result	2009-07-04 04:01:31 +0000
@@ -327,3 +327,48 @@
 HAVING X > '2012-12-12';
 X
 drop table t1, t2;
+#
+# BUG#31480: Incorrect result for nested subquery when executed via semi join
+#
+create table t1 (a int not null, b int not null);
+create table t2 (c int not null, d int not null);
+create table t3 (e int not null);
+insert into t1 values (1,10);
+insert into t1 values (2,10);
+insert into t1 values (1,20);
+insert into t1 values (2,20);
+insert into t1 values (3,20);
+insert into t1 values (2,30);
+insert into t1 values (4,40);
+insert into t2 values (2,10);
+insert into t2 values (2,20);
+insert into t2 values (4,10);
+insert into t2 values (5,10);
+insert into t2 values (3,20);
+insert into t2 values (2,40);
+insert into t3 values (10);
+insert into t3 values (30);
+insert into t3 values (10);
+insert into t3 values (20);
+explain extended
+select a from t1
+where a in (select c from t2 where d >= some(select e from t3 where b=e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Start temporary
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where; End temporary; Using join buffer
+3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select 1 AS `Not_used` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))
+show warnings;
+Level	Code	Message
+Note	1276	Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select 1 AS `Not_used` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))
+select a from t1
+where a in (select c from t2 where d >= some(select e from t3 where b=e));
+a
+2
+2
+3
+2
+drop table t1, t2, t3;

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2009-03-19 17:03:58 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2009-07-04 04:01:31 +0000
@@ -331,6 +331,51 @@
 HAVING X > '2012-12-12';
 X
 drop table t1, t2;
+#
+# BUG#31480: Incorrect result for nested subquery when executed via semi join
+#
+create table t1 (a int not null, b int not null);
+create table t2 (c int not null, d int not null);
+create table t3 (e int not null);
+insert into t1 values (1,10);
+insert into t1 values (2,10);
+insert into t1 values (1,20);
+insert into t1 values (2,20);
+insert into t1 values (3,20);
+insert into t1 values (2,30);
+insert into t1 values (4,40);
+insert into t2 values (2,10);
+insert into t2 values (2,20);
+insert into t2 values (4,10);
+insert into t2 values (5,10);
+insert into t2 values (3,20);
+insert into t2 values (2,40);
+insert into t3 values (10);
+insert into t3 values (30);
+insert into t3 values (10);
+insert into t3 values (20);
+explain extended
+select a from t1
+where a in (select c from t2 where d >= some(select e from t3 where b=e));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Start temporary
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	7	100.00	Using where; End temporary; Using join buffer
+3	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+Warnings:
+Note	1276	Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select 1 AS `Not_used` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))
+show warnings;
+Level	Code	Message
+Note	1276	Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select 1 AS `Not_used` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))
+select a from t1
+where a in (select c from t2 where d >= some(select e from t3 where b=e));
+a
+2
+2
+3
+2
+drop table t1, t2, t3;
 set join_cache_level=default;
 show variables like 'join_cache_level';
 Variable_name	Value

=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test	2009-03-19 17:03:58 +0000
+++ b/mysql-test/t/subselect_sj.test	2009-07-04 04:01:31 +0000
@@ -216,4 +216,39 @@
 HAVING X > '2012-12-12';
 drop table t1, t2;
 
-
+--echo #
+--echo # BUG#31480: Incorrect result for nested subquery when executed via semi join
+--echo #
+create table t1 (a int not null, b int not null);
+create table t2 (c int not null, d int not null);
+create table t3 (e int not null);
+
+insert into t1 values (1,10);
+insert into t1 values (2,10);
+insert into t1 values (1,20);
+insert into t1 values (2,20);
+insert into t1 values (3,20);
+insert into t1 values (2,30);
+insert into t1 values (4,40);
+
+insert into t2 values (2,10);
+insert into t2 values (2,20);
+insert into t2 values (4,10);
+insert into t2 values (5,10);
+insert into t2 values (3,20);
+insert into t2 values (2,40);
+
+insert into t3 values (10);
+insert into t3 values (30);
+insert into t3 values (10);
+insert into t3 values (20);
+
+explain extended
+select a from t1
+where a in (select c from t2 where d >= some(select e from t3 where b=e));
+show warnings;
+
+select a from t1
+where a in (select c from t2 where d >= some(select e from t3 where b=e));
+
+drop table t1, t2, t3;

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2009-04-03 15:14:49 +0000
+++ b/sql/item.cc	2009-07-04 04:01:31 +0000
@@ -2174,7 +2174,8 @@
 }
 
 
-void Item_field::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_field::fix_after_pullout(st_select_lex *new_parent, 
+                                   uint parent_tables, Item **ref)
 {
   if (new_parent == depended_from)
     depended_from= NULL;
@@ -3559,16 +3560,17 @@
 
 static void mark_as_dependent(THD *thd, SELECT_LEX *last, SELECT_LEX *current,
                               Item_ident *resolved_item,
-                              Item_ident *mark_item)
+                              Item_ident *mark_item, table_map dep_map)
 {
   const char *db_name= (resolved_item->db_name ?
                         resolved_item->db_name : "");
   const char *table_name= (resolved_item->table_name ?
                            resolved_item->table_name : "");
+  //table_map dep_map = resolved_item->used_tables();
   /* store pointer on SELECT_LEX from which item is dependent */
   if (mark_item)
     mark_item->depended_from= last;
-  current->mark_as_dependent(last);
+  current->mark_as_dependent(last, dep_map);
   if (thd->lex->describe & DESCRIBE_EXTENDED)
   {
     char warn_buff[MYSQL_ERRMSG_SIZE];
@@ -3628,21 +3630,26 @@
     Item_subselect *prev_subselect_item=
       previous_select->master_unit()->item;
     Item_ident *dependent= resolved_item;
+    table_map found_used_tables;
     if (found_field == view_ref_found)
     {
       Item::Type type= found_item->type();
+      found_used_tables= found_item->used_tables();
       prev_subselect_item->used_tables_cache|=
-        found_item->used_tables();
+        found_used_tables;
       dependent= ((type == Item::REF_ITEM || type == Item::FIELD_ITEM) ?
                   (Item_ident*) found_item :
                   0);
     }
     else
+    {
+      found_used_tables= found_field->table->map;
       prev_subselect_item->used_tables_cache|=
         found_field->table->map;
+    }
     prev_subselect_item->const_item_cache= 0;
     mark_as_dependent(thd, last_select, current_sel, resolved_item,
-                      dependent);
+                      dependent, found_used_tables);
   }
 }
 
@@ -3923,6 +3930,7 @@
   SELECT_LEX *current_sel= (SELECT_LEX *) thd->lex->current_select;
   Name_resolution_context *outer_context= 0;
   SELECT_LEX *select= 0;
+  uint n_levels= 0;
   /* Currently derived tables cannot be correlated */
   if (current_sel->master_unit()->first_select()->linkage !=
       DERIVED_TABLE_TYPE)
@@ -4015,7 +4023,8 @@
                               context->select_lex, this,
                               ((ref_type == REF_ITEM ||
                                 ref_type == FIELD_ITEM) ?
-                               (Item_ident*) (*reference) : 0));
+                               (Item_ident*) (*reference) : 0),
+                              (*from_field)->table->map);
             return 0;
           }
         }
@@ -4030,7 +4039,8 @@
                             context->select_lex, this,
                             ((ref_type == REF_ITEM || ref_type == FIELD_ITEM) ?
                              (Item_ident*) (*reference) :
-                             0));
+                             0),
+                            (*reference)->used_tables());
           /*
             A reference to a view field had been found and we
             substituted it instead of this Item (find_field_in_tables
@@ -4064,6 +4074,7 @@
     */
     prev_subselect_item->used_tables_cache|= OUTER_REF_TABLE_BIT;
     prev_subselect_item->const_item_cache= 0;
+    n_levels++;
   }
 
   DBUG_ASSERT(ref != 0);
@@ -4131,14 +4142,15 @@
 
     mark_as_dependent(thd, last_checked_context->select_lex,
                       context->select_lex, this,
-                      rf);
+                      rf, rf->used_tables());
     return 0;
   }
   else
   {
     mark_as_dependent(thd, last_checked_context->select_lex,
                       context->select_lex,
-                      this, (Item_ident*)*reference);
+                      this, (Item_ident*)*reference, 
+                      (*reference)->used_tables());
     if (last_checked_context->select_lex->having_fix_field)
     {
       Item_ref *rf;
@@ -5840,7 +5852,8 @@
                               ((refer_type == REF_ITEM ||
                                 refer_type == FIELD_ITEM) ?
                                (Item_ident*) (*reference) :
-                               0));
+                               0),
+                              (*reference)->used_tables());
             /*
               view reference found, we substituted it instead of this
               Item, so can quit
@@ -5890,7 +5903,8 @@
           goto error;
         thd->change_item_tree(reference, fld);
         mark_as_dependent(thd, last_checked_context->select_lex,
-                          thd->lex->current_select, this, fld);
+                          thd->lex->current_select, this, fld, 
+                          from_field->table->map);
         /*
           A reference is resolved to a nest level that's outer or the same as
           the nest level of the enclosing set function : adjust the value of
@@ -5913,7 +5927,8 @@
       /* Should be checked in resolve_ref_in_select_and_group(). */
       DBUG_ASSERT(*ref && (*ref)->fixed);
       mark_as_dependent(thd, last_checked_context->select_lex,
-                        context->select_lex, this, this);
+                        context->select_lex, this, this, 
+                        (*ref)->used_tables());
       /*
         A reference is resolved to a nest level that's outer or the same as
         the nest level of the enclosing set function : adjust the value of
@@ -6323,20 +6338,22 @@
   return err;
 }
 
-void Item_outer_ref::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_outer_ref::fix_after_pullout(st_select_lex *new_parent, 
+                                       uint parent_tables, Item **ref)
 {
   if (depended_from == new_parent)
   {
     *ref= outer_ref;
-    outer_ref->fix_after_pullout(new_parent, ref);
+    outer_ref->fix_after_pullout(new_parent, parent_tables, ref);
   }
 }
 
-void Item_ref::fix_after_pullout(st_select_lex *new_parent, Item **refptr)
+void Item_ref::fix_after_pullout(st_select_lex *new_parent, 
+                                 uint parent_tables, Item **refptr)
 {
   if (depended_from == new_parent)
   {
-    (*ref)->fix_after_pullout(new_parent, ref);
+    (*ref)->fix_after_pullout(new_parent, parent_tables, ref);
     depended_from= NULL;
   }
 }

=== modified file 'sql/item.h'
--- a/sql/item.h	2009-04-03 15:14:49 +0000
+++ b/sql/item.h	2009-07-04 04:01:31 +0000
@@ -557,7 +557,8 @@
     Fix after some tables has been pulled out. Basically re-calculate all
     attributes that are dependent on the tables.
   */
-  virtual void fix_after_pullout(st_select_lex *new_parent, Item **ref) {};
+  virtual void fix_after_pullout(st_select_lex *new_parent, uint parent_tables,
+                                 Item **ref) {};
 
   /*
     should be used in case where we are sure that we do not need
@@ -1486,7 +1487,8 @@
   bool send(Protocol *protocol, String *str_arg);
   void reset_field(Field *f);
   bool fix_fields(THD *, Item **);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *new_parent, uint parent_tables, 
+                         Item **ref);
   void make_field(Send_field *tmp_field);
   int save_in_field(Field *field,bool no_conversions);
   void save_org_in_field(Field *field);
@@ -2278,7 +2280,8 @@
   bool send(Protocol *prot, String *tmp);
   void make_field(Send_field *field);
   bool fix_fields(THD *, Item **);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *new_parent, uint parent_tables,
+                         Item **ref);
   int save_in_field(Field *field, bool no_conversions);
   void save_org_in_field(Field *field);
   enum Item_result result_type () const { return (*ref)->result_type(); }
@@ -2448,7 +2451,8 @@
     outer_ref->save_org_in_field(result_field);
   }
   bool fix_fields(THD *, Item **);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *new_parent, uint parent_tables, 
+                         Item **ref);
   table_map used_tables() const
   {
     return (*ref)->const_item() ? 0 : OUTER_REF_TABLE_BIT;

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2009-04-01 21:36:07 +0000
+++ b/sql/item_cmpfunc.cc	2009-07-04 04:01:31 +0000
@@ -4013,7 +4013,8 @@
 }
 
 
-void Item_cond::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_cond::fix_after_pullout(st_select_lex *new_parent, 
+                                  uint parent_tables, Item **ref)
 {
   List_iterator<Item> li(list);
   Item *item;
@@ -4027,7 +4028,7 @@
   while ((item=li++))
   {
     table_map tmp_table_map;
-    item->fix_after_pullout(new_parent, li.ref());
+    item->fix_after_pullout(new_parent, parent_tables, li.ref());
     item= *li.ref();
     used_tables_cache|= item->used_tables();
     const_item_cache&= item->const_item();

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2009-01-26 16:03:39 +0000
+++ b/sql/item_cmpfunc.h	2009-07-04 04:01:31 +0000
@@ -1475,7 +1475,8 @@
   bool add_at_head(Item *item) { return list.push_front(item); }
   void add_at_head(List<Item> *nlist) { list.prepand(nlist); }
   bool fix_fields(THD *, Item **ref);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *new_parent, uint parent_tables, 
+                         Item **ref);
 
   enum Type type() const { return COND_ITEM; }
   List<Item>* argument_list() { return &list; }

=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc	2009-04-13 13:24:28 +0000
+++ b/sql/item_func.cc	2009-07-04 04:01:31 +0000
@@ -206,7 +206,8 @@
 }
 
 
-void Item_func::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_func::fix_after_pullout(st_select_lex *new_parent, 
+                                  uint parent_tables, Item **ref)
 {
   Item **arg,**arg_end;
 
@@ -217,7 +218,7 @@
   {
     for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++)
     {
-      (*arg)->fix_after_pullout(new_parent, arg);
+      (*arg)->fix_after_pullout(new_parent, parent_tables, arg);
       Item *item= *arg;
 
       used_tables_cache|=     item->used_tables();

=== modified file 'sql/item_func.h'
--- a/sql/item_func.h	2009-02-13 16:30:54 +0000
+++ b/sql/item_func.h	2009-07-04 04:01:31 +0000
@@ -117,7 +117,8 @@
   // Constructor used for Item_cond_and/or (see Item comment)
   Item_func(THD *thd, Item_func *item);
   bool fix_fields(THD *, Item **ref);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *new_parent, uint parent_tables, 
+                         Item **ref);
   table_map used_tables() const;
   table_map not_null_tables() const;
   void update_used_tables();

=== modified file 'sql/item_row.cc'
--- a/sql/item_row.cc	2008-02-22 11:11:25 +0000
+++ b/sql/item_row.cc	2009-07-04 04:01:31 +0000
@@ -124,13 +124,14 @@
   }
 }
 
-void Item_row::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_row::fix_after_pullout(st_select_lex *new_parent, 
+                                 uint parent_tables, Item **ref)
 {
   used_tables_cache= 0;
   const_item_cache= 1;
   for (uint i= 0; i < arg_count; i++)
   {
-    items[i]->fix_after_pullout(new_parent, &items[i]);
+    items[i]->fix_after_pullout(new_parent, parent_tables, &items[i]);
     used_tables_cache|= items[i]->used_tables();
     const_item_cache&= items[i]->const_item();
   }

=== modified file 'sql/item_row.h'
--- a/sql/item_row.h	2008-02-22 11:11:25 +0000
+++ b/sql/item_row.h	2009-07-04 04:01:31 +0000
@@ -59,7 +59,8 @@
     return 0;
   };
   bool fix_fields(THD *thd, Item **ref);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *new_parent, uint parent_tables,
+                         Item **ref);
   void cleanup();
   void split_sum_func(THD *thd, Item **ref_pointer_array, List<Item> &fields);
   table_map used_tables() const { return used_tables_cache; };

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2009-01-08 19:06:44 +0000
+++ b/sql/item_subselect.cc	2009-07-04 04:01:31 +0000
@@ -39,7 +39,7 @@
 Item_subselect::Item_subselect():
   Item_result_field(), value_assigned(0), thd(0), substitution(0),
   engine(0), old_engine(0), used_tables_cache(0), have_to_be_excluded(0),
-  const_item_cache(1), engine_changed(0), changed(0),
+  const_item_cache(1), inside_fix_fields(0), engine_changed(0), changed(0),
   is_correlated(FALSE)
 {
   with_subselect= 1;
@@ -158,6 +158,13 @@
   DBUG_RETURN(RES_OK);
 }
 
+void Item_subselect::set_depth()
+{
+  uint n= 0;
+  for (SELECT_LEX *s= unit->first_select(); s; s= s->outer_select())
+    n++;
+  this->depth= n - 1;
+}
 
 bool Item_subselect::fix_fields(THD *thd_param, Item **ref)
 {
@@ -168,9 +175,19 @@
   DBUG_ASSERT(fixed == 0);
   engine->set_thd((thd= thd_param));
 
+  if (!inside_fix_fields)
+  {
+    set_depth();
+    if (!(ancestor_used_tables= (table_map*)thd->calloc((1+depth) * 
+                                                        sizeof(table_map))))
+      return TRUE;
+    furthest_correlated_ancestor= 0;
+  }
+
   if (check_stack_overrun(thd, STACK_MIN_SIZE, (uchar*)&res))
     return TRUE;
 
+  inside_fix_fields++;
   res= engine->prepare();
 
   // all transformation is done (used by prepared statements)
@@ -203,12 +220,14 @@
       if (!(*ref)->fixed)
 	ret= (*ref)->fix_fields(thd, ref);
       thd->where= save_where;
+      inside_fix_fields--;
       return ret;
     }
     // Is it one field subselect?
     if (engine->cols() > max_columns)
     {
       my_error(ER_OPERAND_COLUMNS, MYF(0), 1);
+      inside_fix_fields--;
       return TRUE;
     }
     fix_length_and_dec();
@@ -225,11 +244,56 @@
   fixed= 1;
 
 err:
+  inside_fix_fields--;
   thd->where= save_where;
   return res;
 }
 
 
+/*
+  Adjust attributes after our parent select has been merged into grandparent
+
+  DESCRIPTION
+    Subquery is a composite object which may be correlated, that is, it may
+    have
+    1. references to tables of the parent select (i.e. one that has the clause
+      with the subquery predicate)
+    2. references to tables of the grandparent select
+    3. references to tables of further ancestors.
+    
+    Before the pullout, this item indicates:
+    - #1 with table bits in used_tables()
+    - #2 and #3 with OUTER_REF_TABLE_BIT.
+
+    After parent has been merged with grandparent:
+    - references to parent and grandparent tables should be indicated with 
+      table bits.
+    - references to greatgrandparent and further ancestors - with
+      OUTER_REF_TABLE_BIT.
+   
+   This is exactly what this function does, based on pre-collected info in
+   ancestor_used_tables and furthest_correlated_ancestor.
+*/
+
+void Item_subselect::fix_after_pullout(st_select_lex *new_parent, 
+                                       uint parent_tables, Item **ref)
+{
+  used_tables_cache= (used_tables_cache << parent_tables) | 
+                     ancestor_used_tables[0];
+  for (uint i=0; i < depth; i++)
+    ancestor_used_tables[i]= ancestor_used_tables[i+1];
+  depth--;
+
+  if (furthest_correlated_ancestor)
+    furthest_correlated_ancestor--;
+  used_tables_cache &= ~OUTER_REF_TABLE_BIT;
+  if (furthest_correlated_ancestor > 1)
+    used_tables_cache |= OUTER_REF_TABLE_BIT;
+  const_item_cache &= test(!(used_tables_cache & 
+                           ~new_parent->join->const_table_map));
+}
+
+
 bool Item_subselect::walk(Item_processor processor, bool walk_subquery,
                           uchar *argument)
 {

=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h	2008-11-10 18:36:50 +0000
+++ b/sql/item_subselect.h	2009-07-04 04:01:31 +0000
@@ -66,9 +66,39 @@
   /* work with 'substitution' */
   bool have_to_be_excluded;
   /* cache of constant state */
+
   bool const_item_cache;
+  int inside_fix_fields;
+public:
+  /*
+    Depth of the subquery predicate. 
+    If the subquery predicate is attatched to some clause of the top-level
+      select, depth will be 1 
+    If it is attached to a clause in a subquery of the top-level select, depth
+      will be 2 and so forth.
+  */
+  uint depth;
+  
+  /*
+    Maximum correlation level of the select
+     - select that has no references to outside will have 0,
+     - select that references tables in the select it is located will have 1,
+     - select that has references to tables of its parent select will have 2,
+     - select that has references to tables of grandparent will have 3 
+     and so forth.
+  */
+  uint furthest_correlated_ancestor;
+  /*
+    This is used_tables() for non-direct ancestors. That is, 
+    - used_tables() shows which tables of the parent select are referred to
+      from within the subquery,
+    - ancestor_used_tables[0] shows which tables of the grandparent select are
+      referred to from within the subquery,
+    - ancestor_used_tables[1] shows which tables of the great grand parent
+      select... and so forth.
+  */
+  table_map *ancestor_used_tables;
 
-public:
   /* changed engine indicator */
   bool engine_changed;
   /* subquery is transformed */
@@ -84,6 +114,7 @@
   Item_subselect();
 
   virtual subs_type substype() { return UNKNOWN_SUBS; }
+  void set_depth();
 
   /*
     We need this method, because some compilers do not allow 'this'
@@ -109,6 +140,8 @@
     return null_value;
   }
   bool fix_fields(THD *thd, Item **ref);
+  void fix_after_pullout(st_select_lex *new_parent, uint parent_tables, 
+                         Item **ref);
   virtual bool exec();
   virtual void fix_length_and_dec();
   table_map used_tables() const;

=== modified file 'sql/item_sum.cc'
--- a/sql/item_sum.cc	2009-03-11 12:52:04 +0000
+++ b/sql/item_sum.cc	2009-07-04 04:01:31 +0000
@@ -350,7 +350,7 @@
          sl= sl->master_unit()->outer_select() )
       sl->master_unit()->item->with_sum_func= 1;
   }
-  thd->lex->current_select->mark_as_dependent(aggr_sel);
+  thd->lex->current_select->mark_as_dependent(aggr_sel, NULL);
   return FALSE;
 }
 

=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc	2009-04-01 09:34:34 +0000
+++ b/sql/sql_lex.cc	2009-07-04 04:01:31 +0000
@@ -1835,8 +1835,9 @@
     'last' should be reachable from this st_select_lex_node
 */
 
-void st_select_lex::mark_as_dependent(st_select_lex *last)
+void st_select_lex::mark_as_dependent(st_select_lex *last, table_map dep_map)
 {
+  uint n_levels= master_unit()->item->depth;
   /*
     Mark all selects from resolved to 1 before select where was
     found table as depended (of select where was found table)
@@ -1862,7 +1863,14 @@
     }
     Item_subselect *subquery_predicate= s->master_unit()->item;
     if (subquery_predicate)
+    {
       subquery_predicate->is_correlated= TRUE;
+      subquery_predicate->furthest_correlated_ancestor= 
+        max(subquery_predicate->furthest_correlated_ancestor, n_levels);
+      if (n_levels > 1)
+        subquery_predicate->ancestor_used_tables[n_levels - 2]= dep_map;
+    }
+    n_levels--;
   }
 }
 

=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h	2009-03-19 16:42:23 +0000
+++ b/sql/sql_lex.h	2009-07-04 04:01:31 +0000
@@ -754,7 +754,7 @@
     return master_unit()->return_after_parsing();
   }
 
-  void mark_as_dependent(st_select_lex *last);
+  void mark_as_dependent(st_select_lex *last, table_map dep_map);
 
   bool set_braces(bool value);
   bool inc_in_sum_expr();

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-04-13 09:51:24 +0000
+++ b/sql/sql_select.cc	2009-07-04 04:01:31 +0000
@@ -3119,16 +3119,23 @@
 }
 
 
-void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> *tlist)
+void fix_list_after_tbl_changes(SELECT_LEX *new_parent, uint parent_tables, 
+                                List<TABLE_LIST> *tlist)
 {
   List_iterator<TABLE_LIST> it(*tlist);
   TABLE_LIST *table;
   while ((table= it++))
   {
     if (table->on_expr)
-      table->on_expr->fix_after_pullout(new_parent, &table->on_expr);
+    {
+      table->on_expr->fix_after_pullout(new_parent, parent_tables,
+                                        &table->on_expr);
+    }
     if (table->nested_join)
-      fix_list_after_tbl_changes(new_parent, &table->nested_join->join_list);
+    {
+      fix_list_after_tbl_changes(new_parent, parent_tables, 
+                                 &table->nested_join->join_list);
+    }
   }
 }
 
@@ -3331,6 +3338,7 @@
   /*TODO: also reset the 'with_subselect' there. */
 
   /* n. Adjust the parent_join->tables counter */
+  uint parent_tables= parent_join->tables;
   uint table_no= parent_join->tables;
   /* n. Walk through child's tables and adjust table->map */
   for (tl= subq_lex->leaf_tables; tl; tl= tl->next_leaf, table_no++)
@@ -3407,8 +3415,10 @@
     Walk through sj nest's WHERE and ON expressions and call
     item->fix_table_changes() for all items.
   */
-  sj_nest->sj_on_expr->fix_after_pullout(parent_lex, &sj_nest->sj_on_expr);
-  fix_list_after_tbl_changes(parent_lex, &sj_nest->nested_join->join_list);
+  sj_nest->sj_on_expr->fix_after_pullout(parent_lex, parent_join->tables, 
+                                         &sj_nest->sj_on_expr);
+  fix_list_after_tbl_changes(parent_lex, parent_join->tables, 
+                             &sj_nest->nested_join->join_list);
 
 
   /* Unlink the child select_lex so it doesn't show up in EXPLAIN: */