maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #00483
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: */
