List:Internals« Previous MessageNext Message »
From:sca Date:March 17 2010 8:59am
Subject:Contribution: Fix for BUG#31480
View as plain text  
Hello,

I'm pleased to offer the following fix under terms of SCA:

=== modified file 'mysql-test/r/subselect_sj.result'
--- mysql-test/r/subselect_sj.result	2009-03-19 17:03:58 +0000
+++ mysql-test/r/subselect_sj.result	2009-09-09 18:56:29 +0000
@@ -327,3 +327,48 @@ AND OUTR . varchar_nokey <= 'w' 
 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'
--- mysql-test/r/subselect_sj_jcl6.result	2009-03-19 17:03:58 +0000
+++ mysql-test/r/subselect_sj_jcl6.result	2009-09-09 18:56:29 +0000
@@ -331,6 +331,51 @@ AND OUTR . varchar_nokey <= 'w' 
 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'
--- mysql-test/t/subselect_sj.test	2009-03-19 17:03:58 +0000
+++ mysql-test/t/subselect_sj.test	2009-09-09 18:56:29 +0000
@@ -216,4 +216,39 @@ WHERE 
 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'
--- sql/item.cc	2009-07-23 12:21:41 +0000
+++ sql/item.cc	2009-09-09 18:56:29 +0000
@@ -2214,7 +2214,8 @@ table_map Item_field::used_tables() cons
 }
 
 
-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;
@@ -3813,16 +3814,17 @@ bool Item_ref_null_helper::get_date(MYSQ
 
 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)
   {
     push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
@@ -3880,21 +3882,26 @@ void mark_select_range_as_dependent(THD 
     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);
   }
 }
 
@@ -4175,6 +4182,7 @@ Item_field::fix_outer_field(THD *thd, Fi
   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)
@@ -4267,7 +4275,8 @@ Item_field::fix_outer_field(THD *thd, Fi
                               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;
           }
         }
@@ -4282,7 +4291,8 @@ Item_field::fix_outer_field(THD *thd, Fi
                             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
@@ -4316,6 +4326,7 @@ Item_field::fix_outer_field(THD *thd, Fi
     */
     prev_subselect_item->used_tables_cache|= OUTER_REF_TABLE_BIT;
     prev_subselect_item->const_item_cache= 0;
+    n_levels++;
   }
 
   DBUG_ASSERT(ref != 0);
@@ -4383,14 +4394,15 @@ Item_field::fix_outer_field(THD *thd, Fi
 
     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;
@@ -6106,7 +6118,8 @@ bool Item_ref::fix_fields(THD *thd, Item
                               ((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
@@ -6156,7 +6169,8 @@ bool Item_ref::fix_fields(THD *thd, Item
           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
@@ -6179,7 +6193,8 @@ bool Item_ref::fix_fields(THD *thd, Item
       /* 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
@@ -6590,20 +6605,22 @@ bool Item_outer_ref::fix_fields(THD *thd
   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'
--- sql/item.h	2009-06-24 17:00:59 +0000
+++ sql/item.h	2009-09-09 18:56:29 +0000
@@ -560,7 +560,8 @@ public:
     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
@@ -1489,7 +1490,8 @@ public:
   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);
@@ -2281,7 +2283,8 @@ public:
   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(); }
@@ -2451,7 +2454,8 @@ public:
     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'
--- sql/item_cmpfunc.cc	2009-07-03 10:39:01 +0000
+++ sql/item_cmpfunc.cc	2009-09-09 18:56:29 +0000
@@ -4005,7 +4005,8 @@ Item_cond::fix_fields(THD *thd, Item **r
 }
 
 
-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;
@@ -4019,7 +4020,7 @@ void Item_cond::fix_after_pullout(st_sel
   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'
--- sql/item_cmpfunc.h	2009-06-22 09:36:50 +0000
+++ sql/item_cmpfunc.h	2009-09-09 18:56:29 +0000
@@ -1478,7 +1478,8 @@ public:
   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'
--- sql/item_func.cc	2009-07-28 22:45:02 +0000
+++ sql/item_func.cc	2009-09-09 18:56:29 +0000
@@ -206,7 +206,8 @@ Item_func::fix_fields(THD *thd, Item **r
 }
 
 
-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 @@ void Item_func::fix_after_pullout(st_sel
   {
     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'
--- sql/item_func.h	2009-08-26 09:14:05 +0000
+++ sql/item_func.h	2009-09-09 18:56:29 +0000
@@ -120,7 +120,8 @@ public:
   // 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'
--- sql/item_row.cc	2008-02-22 11:11:25 +0000
+++ sql/item_row.cc	2009-09-09 18:56:29 +0000
@@ -124,13 +124,14 @@ void Item_row::update_used_tables()
   }
 }
 
-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'
--- sql/item_row.h	2009-06-22 09:36:50 +0000
+++ sql/item_row.h	2009-09-09 18:56:29 +0000
@@ -62,7 +62,8 @@ public:
     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'
--- sql/item_subselect.cc	2009-08-13 14:29:55 +0000
+++ sql/item_subselect.cc	2009-09-09 18:56:58 +0000
@@ -39,8 +39,8 @@ inline Item * and_items(Item* cond, Item
 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),
-  is_correlated(FALSE)
+  const_item_cache(1), inside_first_fix_fields(0), ancestor_used_tables(0), 
+  engine_changed(0), changed(0), is_correlated(FALSE)
 {
   with_subselect= 1;
   reset();
@@ -159,6 +159,15 @@ Item_subselect::select_transformer(JOIN 
 }
 
 
+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)
 {
   char const *save_where= thd_param->where;
@@ -168,13 +177,26 @@ bool Item_subselect::fix_fields(THD *thd
   DBUG_ASSERT(fixed == 0);
   engine->set_thd((thd= thd_param));
 
+  if (!ancestor_used_tables)
+  {
+    set_depth();
+    size_t size= (1+depth) * sizeof(table_map); 
+    if (!(ancestor_used_tables= (table_map*)
+                                alloc_root(thd->stmt_arena->mem_root, size)))
+      return TRUE;
+    bzero(ancestor_used_tables, size);
+    furthest_correlated_ancestor= 0;
+    inside_first_fix_fields= TRUE;
+  }
+
   if (check_stack_overrun(thd, STACK_MIN_SIZE, (uchar*)&res))
     return TRUE;
 
   res= engine->prepare();
-
+  
   // all transformation is done (used by prepared statements)
   changed= 1;
+  inside_first_fix_fields= FALSE;
 
   if (!res)
   {
@@ -230,6 +252,66 @@ err:
 }
 
 
+void Item_subselect::mark_as_dependent(uint n_levels, table_map dep_map)
+{
+  if (inside_first_fix_fields)
+  {
+    is_correlated= TRUE;
+    furthest_correlated_ancestor= max(furthest_correlated_ancestor, n_levels);
+    if (n_levels > 1)
+      ancestor_used_tables[n_levels - 2] |= dep_map;
+  }
+}
+
+
+/*
+  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;
+
+  /* 
+    Don't update const_tables_cache yet as we don't yet know which of the
+    parent's tables are constant. Parent will call update_used_tables() anyway,
+    and that will be our chance to update.
+  */
+}
+
+
 bool Item_subselect::walk(Item_processor processor, bool walk_subquery,
                           uchar *argument)
 {

=== modified file 'sql/item_subselect.h'
--- sql/item_subselect.h	2009-06-22 09:36:50 +0000
+++ sql/item_subselect.h	2009-09-09 18:56:55 +0000
@@ -69,9 +69,39 @@ protected:
   /* work with 'substitution' */
   bool have_to_be_excluded;
   /* cache of constant state */
-  bool const_item_cache;
 
+  bool const_item_cache;
+  int inside_first_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;
+
   /* changed engine indicator */
   bool engine_changed;
   /* subquery is transformed */
@@ -87,6 +117,7 @@ public:
   Item_subselect();
 
   virtual subs_type substype() { return UNKNOWN_SUBS; }
+  void set_depth();
 
   /*
     We need this method, because some compilers do not allow 'this'
@@ -112,6 +143,9 @@ public:
     return null_value;
   }
   bool fix_fields(THD *thd, Item **ref);
+  void mark_as_dependent(uint n_levels, table_map dep_map);
+  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'
--- sql/item_sum.cc	2009-07-10 14:13:29 +0000
+++ sql/item_sum.cc	2009-09-09 18:56:29 +0000
@@ -350,7 +350,7 @@ bool Item_sum::register_sum_func(THD *th
          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'
--- sql/sql_lex.cc	2009-07-28 14:16:37 +0000
+++ sql/sql_lex.cc	2009-09-09 18:56:55 +0000
@@ -1900,8 +1900,9 @@ void st_select_lex_unit::exclude_tree()
     '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)
@@ -1927,7 +1928,8 @@ void st_select_lex::mark_as_dependent(st
     }
     Item_subselect *subquery_predicate= s->master_unit()->item;
     if (subquery_predicate)
-      subquery_predicate->is_correlated= TRUE;
+      subquery_predicate->mark_as_dependent(n_levels, dep_map);
+    n_levels--;
   }
 }
 

=== modified file 'sql/sql_lex.h'
--- sql/sql_lex.h	2009-08-26 09:14:05 +0000
+++ sql/sql_lex.h	2009-09-09 18:56:29 +0000
@@ -758,7 +758,7 @@ public:
     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'
--- sql/sql_select.cc	2009-09-09 18:54:18 +0000
+++ sql/sql_select.cc	2009-09-09 18:56:29 +0000
@@ -3143,16 +3143,23 @@ static TABLE_LIST *alloc_join_nest(THD *
 }
 
 
-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);
+    }
   }
 }
 
@@ -3355,6 +3362,7 @@ bool convert_subq_to_sj(JOIN *parent_joi
   /*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++)
@@ -3431,8 +3439,10 @@ bool convert_subq_to_sj(JOIN *parent_joi
     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: */



Thread
Contribution: Fix for BUG#31480sca17 Mar
  • Re: Contribution: Fix for BUG#31480Jørgen Løland17 Mar