maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #02035
Rev 2750: BUG#31480: Incorrect result for nested subquery when executed via semi join in file:///home/psergey/dev/maria-5.3-subqueries-r3/
At file:///home/psergey/dev/maria-5.3-subqueries-r3/
------------------------------------------------------------
revno: 2750
revision-id: psergey@xxxxxxxxxxxx-20100128152533-q0dicbcuu9fvol5p
parent: psergey@xxxxxxxxxxxx-20100128134833-9000udjp5wa3tsff
committer: Sergey Petrunya <psergey@xxxxxxxxxxxx>
branch nick: maria-5.3-subqueries-r3
timestamp: Thu 2010-01-28 18:25:33 +0300
message:
BUG#31480: Incorrect result for nested subquery when executed via semi join
- Make Item_subselect store its "depth", distance to
furthest_correlated_ancestor, and bitmaps of tables that it depends on in
ancestor tables. This allows to have Item_subselect to update its attributes
when it has been pulled out from a select to its parent.
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result 2010-01-17 14:51:10 +0000
+++ b/mysql-test/r/subselect_sj.result 2010-01-28 15:25:33 +0000
@@ -779,3 +779,48 @@
1 PRIMARY it2 ALL NULL NULL NULL NULL 20 Using where; End temporary
DROP TABLE ot1, it1, it2;
# End of BUG#38075
+#
+# 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 2010-01-17 14:51:10 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result 2010-01-28 15:25:33 +0000
@@ -783,6 +783,51 @@
1 PRIMARY it2 ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer
DROP TABLE ot1, it1, it2;
# End of BUG#38075
+#
+# 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 2010-01-17 14:51:10 +0000
+++ b/mysql-test/t/subselect_sj.test 2010-01-28 15:25:33 +0000
@@ -681,3 +681,41 @@
DROP TABLE ot1, it1, it2;
--echo # End of BUG#38075
+
+--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 2010-01-17 14:55:08 +0000
+++ b/sql/item.cc 2010-01-28 15:25:33 +0000
@@ -2211,7 +2211,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;
@@ -3648,7 +3649,7 @@
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 : "");
@@ -3657,7 +3658,7 @@
/* store pointer on SELECT_LEX from which item is dependent */
if (mark_item)
mark_item->depended_from= last;
- current->mark_as_dependent(last, resolved_item);
+ current->mark_as_dependent(last, resolved_item, dep_map);
if (thd->lex->describe & DESCRIBE_EXTENDED)
{
push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
@@ -3715,21 +3716,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);
}
}
@@ -4010,6 +4016,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)
@@ -4102,7 +4109,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;
}
}
@@ -4117,7 +4125,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
@@ -4151,6 +4160,7 @@
*/
prev_subselect_item->used_tables_cache|= OUTER_REF_TABLE_BIT;
prev_subselect_item->const_item_cache= 0;
+ n_levels++;
}
DBUG_ASSERT(ref != 0);
@@ -4218,14 +4228,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;
@@ -5947,7 +5958,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
@@ -5997,7 +6009,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
@@ -6020,7 +6033,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
@@ -6432,20 +6446,22 @@
}
-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);
+ (*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 2010-01-28 13:48:33 +0000
+++ b/sql/item.h 2010-01-28 15:25:33 +0000
@@ -569,7 +569,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
@@ -1574,7 +1575,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);
@@ -2343,7 +2345,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(); }
@@ -2520,7 +2523,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 2010-01-17 14:55:08 +0000
+++ b/sql/item_cmpfunc.cc 2010-01-28 15:25:33 +0000
@@ -3959,7 +3959,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;
@@ -3973,7 +3974,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 2010-01-17 14:55:08 +0000
+++ b/sql/item_cmpfunc.h 2010-01-28 15:25:33 +0000
@@ -1472,7 +1472,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 2010-01-28 13:48:33 +0000
+++ b/sql/item_func.cc 2010-01-28 15:25:33 +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 2010-01-28 13:48:33 +0000
+++ b/sql/item_func.h 2010-01-28 15:25:33 +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 2010-01-17 14:55:08 +0000
+++ b/sql/item_row.cc 2010-01-28 15:25:33 +0000
@@ -125,13 +125,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 2010-01-17 14:55:08 +0000
+++ b/sql/item_row.h 2010-01-28 15:25:33 +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 2010-01-28 13:48:33 +0000
+++ b/sql/item_subselect.cc 2010-01-28 15:25:33 +0000
@@ -39,8 +39,8 @@
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), in_fix_fields(0), engine_changed(0), changed(0),
- is_correlated(FALSE)
+ const_item_cache(1), inside_first_fix_fields(0), in_fix_fields(0),
+ ancestor_used_tables(0), engine_changed(0), changed(0), is_correlated(FALSE)
{
with_subselect= 1;
reset();
@@ -159,6 +159,15 @@
}
+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;
@@ -169,6 +178,18 @@
engine->set_thd((thd= thd_param));
if (!in_fix_fields)
refers_to.empty();
+ 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;
+ }
+
eliminated= FALSE;
if (check_stack_overrun(thd, STACK_MIN_SIZE, (uchar*)&res))
@@ -179,6 +200,7 @@
// all transformation is done (used by prepared statements)
changed= 1;
+ inside_first_fix_fields= FALSE;
if (!res)
{
@@ -258,6 +280,65 @@
return FALSE;
}
+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'
--- a/sql/item_subselect.h 2010-01-28 13:48:33 +0000
+++ b/sql/item_subselect.h 2010-01-28 15:25:33 +0000
@@ -68,6 +68,37 @@
/* cache of constant state */
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;
+
public:
/*
References from inside the subquery to the select that this predicate is
@@ -92,6 +123,7 @@
Item_subselect();
virtual subs_type substype() { return UNKNOWN_SUBS; }
+ void set_depth();
/*
We need this method, because some compilers do not allow 'this'
@@ -117,6 +149,9 @@
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'
--- a/sql/item_sum.cc 2009-10-15 21:38:29 +0000
+++ b/sql/item_sum.cc 2010-01-28 15:25:33 +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, NULL);
+ thd->lex->current_select->mark_as_dependent(aggr_sel, NULL, 0);
return FALSE;
}
=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc 2010-01-28 13:48:33 +0000
+++ b/sql/sql_lex.cc 2010-01-28 15:25:33 +0000
@@ -1841,8 +1841,10 @@
'last' should be reachable from this st_select_lex_node
*/
-void st_select_lex::mark_as_dependent(st_select_lex *last, Item *dependency)
+void st_select_lex::mark_as_dependent(st_select_lex *last, Item *dependency,
+ table_map dep_map)
{
+ uint n_levels= master_unit()->item->depth;
SELECT_LEX *next_to_last;
/*
Mark all selects from resolved to 1 before select where was
@@ -1868,6 +1870,11 @@
}
}
next_to_last= s;
+ Item_subselect *subquery_predicate= s->master_unit()->item;
+ if (subquery_predicate)
+ subquery_predicate->mark_as_dependent(n_levels, dep_map);
+ n_levels--;
+ //psergey-merge-todo: next_to_last should be removed??
}
is_correlated= TRUE;
this->master_unit()->item->is_correlated= TRUE;
=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h 2010-01-28 13:48:33 +0000
+++ b/sql/sql_lex.h 2010-01-28 15:25:33 +0000
@@ -747,7 +747,8 @@
return master_unit()->return_after_parsing();
}
- void mark_as_dependent(st_select_lex *last, Item *dependency);
+ void mark_as_dependent(st_select_lex *last, Item *dependency,
+ 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 2010-01-28 13:48:33 +0000
+++ b/sql/sql_select.cc 2010-01-28 15:25:33 +0000
@@ -3144,16 +3144,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);
+ }
}
}
@@ -3357,6 +3364,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++)
@@ -3434,8 +3442,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: */
Follow ups
