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: */
