Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-02-20 16:12:14+03:00, evgen@stripped +11 -0
Bug#23800: Outer fields in correlated subqueries is used in a temporary table
created for sorting.
Any outer reference in a subquery was represented by an Item_field object.
If the outer select employs a temporary table all such fields should be
replaced with fields from that temporary table in order to point to the
actual data. This replacement wasn't done and that resulted in a wrong
subquery evaluation and a wrong result of the whole query.
Now any outer field is represented by two objects - Item_field placed in the
outer select and Item_outer_ref in the subquery. Item_field object is
processed as a normal field and the reference to it is saved in the
ref_pointer_array. Thus the Item_outer_ref is always references the correct
field. The original field is substituted for a reference in the
Item_field::fix_outer_field() function.
New function called fix_inner_refs() is added to fix fields referenced from
inner selects and to fix references (Item_ref objects) to these fields.
The new Item_outer_ref class is a descendant of the Item_direct_ref class.
It additionally stores a reference to the original field and designed to
behave more like a field.
mysql-test/r/subselect.result@stripped, 2007-02-20 16:01:23+03:00, evgen@stripped +51 -9
Added a test case for bug#23800: Correlated sub query returning incorrect results when
operated upon.
mysql-test/r/subselect3.result@stripped, 2007-02-20 16:01:41+03:00, evgen@stripped +11 -11
Corrected test cases result after fix for bug#23800: Correlated sub query returning
incorrect results when operated upon.
mysql-test/t/subselect.test@stripped, 2007-02-20 16:02:07+03:00, evgen@stripped +28 -1
Added a test case for bug#23800: Correlated sub query returning incorrect results when
operated upon.
sql/item.cc@stripped, 2007-02-20 16:09:54+03:00, evgen@stripped +72 -9
Bug#23800: Correlated sub query returning incorrect results when operated upon.
Now all outer fields are substituted with references to them (Item_outer_ref objects)
in the Item_field::fix_outer_field() function.
The original field is saved in the Item_outer_ref object.
sql/item.h@stripped, 2007-02-20 16:07:34+03:00, evgen@stripped +38 -3
Bug#23800: Correlated sub query returning incorrect results when operated upon.
Added the Item_outer_ref class.
sql/mysql_priv.h@stripped, 2007-02-20 16:06:35+03:00, evgen@stripped +2 -0
Bug#23800: Correlated sub query returning incorrect results when operated upon.
Added the fix_inner_refs() function prototype.
sql/sql_delete.cc@stripped, 2007-02-20 16:06:25+03:00, evgen@stripped +6 -0
Bug#23800: Correlated sub query returning incorrect results when operated upon.
Added call to the fix_inner_refs() function.
sql/sql_lex.cc@stripped, 2007-02-20 16:05:12+03:00, evgen@stripped +1 -0
Bug#23800: Correlated sub query returning incorrect results when operated upon.
Added cleanup of the inner_refs_list.
sql/sql_lex.h@stripped, 2007-02-20 16:04:47+03:00, evgen@stripped +2 -1
Bug#23800: Correlated sub query returning incorrect results when operated upon.
The inner_refs_list is added to the SELECT_LEX class.
sql/sql_select.cc@stripped, 2007-02-20 16:08:13+03:00, evgen@stripped +74 -6
Bug#23800: Correlated sub query returning incorrect results when operated upon.
The new function called fix_inner_refs() is added.
sql/sql_update.cc@stripped, 2007-02-20 16:03:51+03:00, evgen@stripped +5 -0
Bug#23800: Correlated sub query returning incorrect results when operated upon.
Added call to the fix_inner_refs() function.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: evgen
# Host: moonbone.local
# Root: /mnt/gentoo64/work/23800-bug1-5.0-opt-mysql
--- 1.254/sql/item.cc 2007-02-09 12:05:17 +03:00
+++ 1.255/sql/item.cc 2007-02-20 16:09:54 +03:00
@@ -1609,7 +1609,7 @@
Item_field::Item_field(Field *f)
:Item_ident(0, NullS, *f->table_name, f->field_name),
item_equal(0), no_const_subst(0),
- have_privileges(0), any_privileges(0)
+ have_privileges(0), any_privileges(0), fix_as_field(0)
{
set_field(f);
/*
@@ -1623,7 +1623,7 @@
Field *f)
:Item_ident(context_arg, f->table->s->db, *f->table_name, f->field_name),
item_equal(0), no_const_subst(0),
- have_privileges(0), any_privileges(0)
+ have_privileges(0), any_privileges(0), fix_as_field(0)
{
/*
We always need to provide Item_field with a fully qualified field
@@ -1662,7 +1662,7 @@
const char *field_name_arg)
:Item_ident(context_arg, db_arg,table_name_arg,field_name_arg),
field(0), result_field(0), item_equal(0), no_const_subst(0),
- have_privileges(0), any_privileges(0)
+ have_privileges(0), any_privileges(0), fix_as_field(0)
{
collation.set(DERIVATION_IMPLICIT);
}
@@ -1675,7 +1675,8 @@
item_equal(item->item_equal),
no_const_subst(item->no_const_subst),
have_privileges(item->have_privileges),
- any_privileges(item->any_privileges)
+ any_privileges(item->any_privileges),
+ fix_as_field(item->fix_as_field)
{
collation.set(DERIVATION_IMPLICIT);
}
@@ -3484,8 +3485,46 @@
}
if (*from_field != view_ref_found)
{
+
prev_subselect_item->used_tables_cache|= (*from_field)->table->map;
prev_subselect_item->const_item_cache= 0;
+ if (!last_checked_context->select_lex->having_fix_field &&
+ !fix_as_field)
+ {
+ Item_outer_ref *rf;
+ Query_arena *arena= 0, backup;
+ /*
+ Each outer field is replaced for an Item_outer_ref object.
+ This is done in order to get correct results when the outer
+ select employs a temporary table.
+ The original fields are saved in the inner_fields_list of the
+ outer select. This list is created by the following reasons:
+ 1. We can't add field items to the outer select list directly
+ because the outer select hasn't been fully fixed yet.
+ 2. We need a location to refer to in the Item_ref object
+ so the inner_fields_list is used as such temporary
+ reference storage.
+ The new Item_outer_ref object replaces the original field and is
+ also saved in the inner_refs_list of the outer select. Here
+ it is only created. It can be fixed only after the original
+ field has been fixed and this is done in the fix_inner_refs()
+ function.
+ */
+ set_field(*from_field);
+ arena= thd->activate_stmt_arena_if_needed(&backup);
+ rf= new Item_outer_ref(context, this);
+ if (!rf)
+ {
+ if (arena)
+ thd->restore_active_arena(arena, &backup);
+ return -1;
+ }
+ *reference= rf;
+ select->inner_refs_list.push_back(rf);
+ if (arena)
+ thd->restore_active_arena(arena, &backup);
+ fix_as_field= 1;
+ }
if (thd->lex->in_sum_func &&
thd->lex->in_sum_func->nest_level ==
thd->lex->current_select->nest_level)
@@ -3612,7 +3651,7 @@
{
mark_as_dependent(thd, last_checked_context->select_lex,
context->select_lex,
- this, this);
+ this, (Item_ident*)*reference);
if (last_checked_context->select_lex->having_fix_field)
{
Item_ref *rf;
@@ -4818,8 +4857,7 @@
/*
This constructor used to create some internals references over fixed items
*/
- DBUG_ASSERT(ref != 0);
- if (*ref && (*ref)->fixed)
+ if (ref && *ref && (*ref)->fixed)
set_properties();
}
@@ -5119,7 +5157,7 @@
if (ref)
{
if ((*ref)->type() != Item::CACHE_ITEM && ref_type() != VIEW_REF &&
- name && alias_name_used)
+ ref_type() != OUTER_REF && name && alias_name_used)
{
THD *thd= current_thd;
append_identifier(thd, str, name, (uint) strlen(name));
@@ -5367,7 +5405,7 @@
/*
- Prepare referenced view viewld then call usual Item_direct_ref::fix_fields
+ Prepare referenced view field then call usual Item_direct_ref::fix_fields
SYNOPSIS
Item_direct_view_ref::fix_fields()
@@ -5386,6 +5424,31 @@
/* (*ref)->check_cols() will be made in Item_direct_ref::fix_fields */
if (!(*ref)->fixed &&
((*ref)->fix_fields(thd, ref)))
+ return TRUE;
+ return Item_direct_ref::fix_fields(thd, reference);
+}
+
+/*
+ Prepare referenced view field then call usual Item_direct_ref::fix_fields
+
+ SYNOPSIS
+ Item_outer_ref::fix_fields()
+ thd thread handler
+ reference reference on reference where this item stored
+
+ RETURN
+ FALSE OK
+ TRUE Error
+*/
+
+bool Item_outer_ref::fix_fields(THD *thd, Item **reference)
+{
+ /* view fild reference must be defined */
+ DBUG_ASSERT(*ref);
+ /* (*ref)->check_cols() will be made in Item_direct_ref::fix_fields */
+ outer_field->fix_as_field= 1;
+ if (!outer_field->fixed &&
+ (outer_field->fix_fields(thd, reference)))
return TRUE;
return Item_direct_ref::fix_fields(thd, reference);
}
--- 1.219/sql/item.h 2007-01-26 20:33:16 +03:00
+++ 1.220/sql/item.h 2007-02-20 16:07:34 +03:00
@@ -1214,7 +1214,7 @@
uint have_privileges;
/* field need any privileges (for VIEW creation) */
bool any_privileges;
-
+ bool fix_as_field;
Item_field(Name_resolution_context *context_arg,
const char *db_arg,const char *table_name_arg,
const char *field_name_arg);
@@ -1817,9 +1817,10 @@
protected:
void set_properties();
public:
- enum Ref_Type { REF, DIRECT_REF, VIEW_REF };
+ enum Ref_Type { REF, DIRECT_REF, VIEW_REF, OUTER_REF };
Field *result_field; /* Save result here */
Item **ref;
+ /* Indicates whether this Item_ref is created for an outer field. */
Item_ref(Name_resolution_context *context_arg,
const char *db_arg, const char *table_name_arg,
const char *field_name_arg)
@@ -1878,7 +1879,7 @@
(*ref)->get_tmp_table_item(thd));
}
table_map used_tables() const
- {
+ {
return depended_from ? OUTER_REF_TABLE_BIT : (*ref)->used_tables();
}
table_map not_null_tables() const { return (*ref)->not_null_tables(); }
@@ -1948,6 +1949,40 @@
bool fix_fields(THD *, Item **);
bool eq(const Item *item, bool binary_cmp) const;
virtual Ref_Type ref_type() { return VIEW_REF; }
+};
+
+
+class Item_outer_ref :public Item_direct_ref
+{
+public:
+ Item_field *outer_field;
+ Item_outer_ref(Name_resolution_context *context_arg,
+ Item_field *outer_field_arg)
+ :Item_direct_ref(context_arg, 0, outer_field_arg->table_name,
+ outer_field_arg->field_name),
+ outer_field(outer_field_arg)
+ {
+ ref= (Item**)&outer_field;
+ set_properties();
+ fixed= 0;
+ }
+ void cleanup()
+ {
+ ref= (Item**)&outer_field;
+ fixed= 0;
+ Item_direct_ref::cleanup();
+ outer_field->cleanup();
+ }
+ void save_in_result_field(bool no_conversions)
+ {
+ outer_field->save_org_in_field(result_field);
+ }
+ bool fix_fields(THD *, Item **);
+ table_map used_tables() const
+ {
+ return (*ref)->const_item() ? 0 : OUTER_REF_TABLE_BIT;
+ }
+ virtual Ref_Type ref_type() { return OUTER_REF; }
};
--- 1.434/sql/mysql_priv.h 2007-02-12 15:06:12 +03:00
+++ 1.435/sql/mysql_priv.h 2007-02-20 16:06:35 +03:00
@@ -747,6 +747,8 @@
int setup_group(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
List<Item> &fields, List<Item> &all_fields, ORDER *order,
bool *hidden_group_fields);
+bool fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select,
+ Item **ref_pointer_array);
bool handle_select(THD *thd, LEX *lex, select_result *result,
ulong setup_tables_done_option);
--- 1.191/sql/sql_delete.cc 2007-02-01 11:54:43 +03:00
+++ 1.192/sql/sql_delete.cc 2007-02-20 16:06:25 +03:00
@@ -353,6 +353,7 @@
Item *fake_conds= 0;
SELECT_LEX *select_lex= &thd->lex->select_lex;
DBUG_ENTER("mysql_prepare_delete");
+ List<Item> all_fields;
thd->lex->allow_sum_func= 0;
if (setup_tables_and_check_access(thd, &thd->lex->select_lex.context,
@@ -376,6 +377,11 @@
DBUG_RETURN(TRUE);
}
}
+
+ if (select_lex->inner_refs_list.elements &&
+ fix_inner_refs(thd, all_fields, select_lex, select_lex->ref_pointer_array))
+ DBUG_RETURN(-1);
+
select_lex->fix_prepare_information(thd, conds, &fake_conds);
DBUG_RETURN(FALSE);
}
--- 1.211/sql/sql_lex.cc 2007-02-09 23:59:09 +03:00
+++ 1.212/sql/sql_lex.cc 2007-02-20 16:05:12 +03:00
@@ -1192,6 +1192,7 @@
is_correlated= 0;
cur_pos_in_select_list= UNDEF_POS;
non_agg_fields.empty();
+ inner_refs_list.empty();
}
/*
--- 1.240/sql/sql_lex.h 2007-01-31 17:12:41 +03:00
+++ 1.241/sql/sql_lex.h 2007-02-20 16:04:47 +03:00
@@ -547,7 +547,8 @@
bool braces; /* SELECT ... UNION (SELECT ... ) <- this braces */
/* TRUE when having fix field called in processing of this SELECT */
bool having_fix_field;
-
+ /* List of references to fields referenced from inner selects */
+ List<Item_outer_ref> inner_refs_list;
/* Number of Item_sum-derived objects in this SELECT */
uint n_sum_items;
/* Number of Item_sum-derived objects in children and descendant SELECTs */
--- 1.489/sql/sql_select.cc 2007-02-12 15:06:12 +03:00
+++ 1.490/sql/sql_select.cc 2007-02-20 16:08:13 +03:00
@@ -269,6 +269,70 @@
/*
+ Fix fields referenced from inner selects.
+
+ SYNOPSIS
+ fix_inner_refs()
+ thd Thread handle
+ all_fields List of all fields used in select
+ select Current select
+ ref_pointer_array Array of references to Items used in current select
+
+ DESCRIPTION
+ The function fixes fix fields referenced from inner selects and
+ also fixes references (Item_ref objects) to these fields. Each field
+ is fixed as a usual hidden field of the current select - it is added
+ to the all_fields list and the pointer to it is saved in the
+ ref_pointer_array if latter is provided.
+ After the field has been fixed we proceed with fixing references
+ (Item_ref objects) to this field from inner subqueries. If the
+ ref_pointer_array is provided then Item_ref objects is set to
+ reference element in that array with the pointer to the field.
+
+ RETURN
+ TRUE an error occured
+ FALSE ok
+*/
+
+bool
+fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select,
+ Item **ref_pointer_array)
+{
+ Item_outer_ref *ref;
+ bool res= FALSE;
+ List_iterator<Item_outer_ref> ref_it(select->inner_refs_list);
+ while ((ref= ref_it++))
+ {
+ Item_field *item= ref->outer_field;
+ /*
+ TODO: this field item already might be present in the select list.
+ In this case instead of adding new field item we could use an
+ existing one. The change will lead to less operations for copying fields,
+ smaller temporary tables and less data passed through filesort.
+ */
+ if (ref_pointer_array)
+ {
+ int el= all_fields.elements;
+ ref_pointer_array[el]= (Item*)item;
+ /* Add the field item to the select list of the current select. */
+ all_fields.push_front((Item*)item);
+ /*
+ If it's needed reset each Item_ref item that refers this field with
+ a new reference taken from ref_pointer_array.
+ */
+ ref->ref= ref_pointer_array + el;
+ }
+ if (!ref->fixed && ref->fix_fields(thd, 0))
+ {
+ res= TRUE;
+ break;
+ }
+ thd->used_tables|= item->used_tables();
+ }
+ return res;
+}
+
+/*
Function to setup clauses without sum functions
*/
inline int setup_without_group(THD *thd, Item **ref_pointer_array,
@@ -395,6 +459,10 @@
if (having && having->with_sum_func)
having->split_sum_func2(thd, ref_pointer_array, all_fields,
&having, TRUE);
+ if (select_lex->inner_refs_list.elements &&
+ fix_inner_refs(thd, all_fields, select_lex, ref_pointer_array))
+ DBUG_RETURN(-1);
+
if (select_lex->inner_sum_func_list)
{
Item_sum *end=select_lex->inner_sum_func_list;
@@ -5133,13 +5201,15 @@
key_part->length,
keyuse->val);
}
- else if (keyuse->val->type() == Item::FIELD_ITEM)
+ else if (keyuse->val->type() == Item::FIELD_ITEM ||
+ (keyuse->val->type() == Item::REF_ITEM &&
+ ((Item_ref*)keyuse->val)->ref_type() == Item_ref::OUTER_REF) )
return new store_key_field(thd,
key_part->field,
key_buff + maybe_null,
maybe_null ? key_buff : 0,
key_part->length,
- ((Item_field*) keyuse->val)->field,
+ ((Item_field*) keyuse->val->real_item())->field,
keyuse->val->full_name());
return new store_key_item(thd,
key_part->field,
@@ -8804,8 +8874,7 @@
Item *orig_item= 0;
if (type != Item::FIELD_ITEM &&
- item->real_item()->type() == Item::FIELD_ITEM &&
- !((Item_ref *) item)->depended_from)
+ item->real_item()->type() == Item::FIELD_ITEM)
{
orig_item= item;
item= item->real_item();
@@ -13465,8 +13534,7 @@
{
Item::Type type=field->type();
Item::Type real_type= field->real_item()->type();
- if (type == Item::FIELD_ITEM || (real_type == Item::FIELD_ITEM &&
- !((Item_ref *) field)->depended_from))
+ if (real_type == Item::FIELD_ITEM)
param->field_count++;
else if (real_type == Item::SUM_FUNC_ITEM)
{
--- 1.212/sql/sql_update.cc 2007-02-08 01:41:54 +03:00
+++ 1.213/sql/sql_update.cc 2007-02-20 16:03:51 +03:00
@@ -134,6 +134,7 @@
READ_RECORD info;
SELECT_LEX *select_lex= &thd->lex->select_lex;
bool need_reopen;
+ List<Item> all_fields;
DBUG_ENTER("mysql_update");
LINT_INIT(timestamp_query_id);
@@ -225,6 +226,10 @@
free_underlaid_joins(thd, select_lex);
DBUG_RETURN(1); /* purecov: inspected */
}
+
+ if (select_lex->inner_refs_list.elements &&
+ fix_inner_refs(thd, all_fields, select_lex, select_lex->ref_pointer_array))
+ DBUG_RETURN(-1);
if (conds)
{
--- 1.172/mysql-test/r/subselect.result 2007-01-26 08:01:25 +03:00
+++ 1.173/mysql-test/r/subselect.result 2007-02-20 16:01:23 +03:00
@@ -224,7 +224,7 @@
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where
Warnings:
-Note 1276 Field or reference 't4.a' of SELECT #3 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1
Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) AS `min(t3.a)` from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4`
select * from t3 where exists (select * from t2 where t2.b=t3.a);
a
@@ -313,8 +313,8 @@
3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 Using where
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
Warnings:
-Note 1276 Field or reference 't2.a' of SELECT #2 was resolved in SELECT #1
-Note 1276 Field or reference 't2.a' of SELECT #3 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1
Note 1003 select (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2`
select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2;
ERROR 21000: Subquery returns more than 1 row
@@ -330,9 +330,9 @@
explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t6 ALL NULL NULL NULL NULL 4 Using where
-2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 Using index
+2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 t6.clinic_uq 1 Using where; Using index
Warnings:
-Note 1276 Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`))
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
ERROR 23000: Column 'a' in field list is ambiguous
@@ -868,7 +868,7 @@
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
Warnings:
-Note 1276 Field or reference 'a' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1249 Select 2 was reduced during optimization
Note 1003 select (`test`.`t1`.`a` + 1) AS `(select a+1)` from `test`.`t1`
select (select a+1) from t1;
@@ -1741,9 +1741,9 @@
explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY tt ALL NULL NULL NULL NULL 12 Using where
-2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 Using where; Using index
+2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 tt.id 1 Using where; Using index
Warnings:
-Note 1276 Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null))))
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
@@ -2279,7 +2279,7 @@
1 PRIMARY up ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
Warnings:
-Note 1276 Field or reference 'up.a' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`))
drop table t1;
CREATE TABLE t1 (t1_a int);
@@ -3712,3 +3712,45 @@
cc 3
dd 1
DROP TABLE t1,t2,t3;
+CREATE TABLE t1(f1 int);
+CREATE TABLE t2(f2 int, f21 int, f3 timestamp);
+INSERT INTO t1 VALUES (1),(1),(2),(2);
+INSERT INTO t2 VALUES (1,1,"2004-02-29 11:11:11"), (2,2,"2004-02-29 11:11:11");
+SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1;
+sq
+2
+4
+SELECT (SELECT SUM(1) FROM t2 ttt GROUP BY t2.f3 LIMIT 1) AS tt FROM t2;
+tt
+2
+2
+PREPARE stmt1 FROM 'SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1';
+EXECUTE stmt1;
+sq
+2
+4
+EXECUTE stmt1;
+sq
+2
+4
+DEALLOCATE PREPARE stmt1;
+SELECT f2, AVG(f21),
+(SELECT t.f3 FROM t2 AS t WHERE t2.f2=t.f2 AND t.f3=MAX(t2.f3)) AS test
+FROM t2 GROUP BY f2;
+f2 AVG(f21) test
+1 1.0000 2004-02-29 11:11:11
+2 2.0000 2004-02-29 11:11:11
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL);
+INSERT INTO t1 VALUES
+(1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),
+(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'),
+(3,2,'k'), (3,1,'l'), (1,9,'m');
+SELECT a, MAX(b),
+(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test
+FROM t1 GROUP BY a;
+a MAX(b) test
+1 9 m
+2 3 h
+3 4 i
+DROP TABLE t1;
--- 1.139/mysql-test/t/subselect.test 2007-01-26 08:01:25 +03:00
+++ 1.140/mysql-test/t/subselect.test 2007-02-20 16:02:07 +03:00
@@ -2600,4 +2600,31 @@
UNION
SELECT c from t2 WHERE c=t1.c);
-DROP TABLE t1,t2,t3;
+DROP TABLE t1,t2,t3;
+#
+# Bug#23800: Outer fields in correlated subqueries is used in a temporary
+# table created for sorting.
+#
+CREATE TABLE t1(f1 int);
+CREATE TABLE t2(f2 int, f21 int, f3 timestamp);
+INSERT INTO t1 VALUES (1),(1),(2),(2);
+INSERT INTO t2 VALUES (1,1,"2004-02-29 11:11:11"), (2,2,"2004-02-29 11:11:11");
+SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1;
+SELECT (SELECT SUM(1) FROM t2 ttt GROUP BY t2.f3 LIMIT 1) AS tt FROM t2;
+PREPARE stmt1 FROM 'SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1';
+EXECUTE stmt1;
+EXECUTE stmt1;
+DEALLOCATE PREPARE stmt1;
+SELECT f2, AVG(f21),
+ (SELECT t.f3 FROM t2 AS t WHERE t2.f2=t.f2 AND t.f3=MAX(t2.f3)) AS test
+ FROM t2 GROUP BY f2;
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL);
+INSERT INTO t1 VALUES
+ (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),
+ (2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'),
+ (3,2,'k'), (3,1,'l'), (1,9,'m');
+SELECT a, MAX(b),
+ (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test
+ FROM t1 GROUP BY a;
+DROP TABLE t1;
--- 1.4/mysql-test/r/subselect3.result 2007-01-27 04:10:42 +03:00
+++ 1.5/mysql-test/r/subselect3.result 2007-02-20 16:01:41 +03:00
@@ -29,7 +29,7 @@
1 PRIMARY t2 ALL NULL NULL NULL NULL 5
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort
Warnings:
-Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2`
explain extended
select a, oref from t2
@@ -38,7 +38,7 @@
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort
Warnings:
-Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))
select a, oref, a in (
select max(ie) from t1 where oref=t2.oref group by grp union
@@ -91,7 +91,7 @@
1 PRIMARY t2 ALL NULL NULL NULL NULL 4
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using where; Full scan on NULL key
Warnings:
-Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) having trigcond(<is_not_null_test>(`test`.`t1`.`a`))))) AS `Z` from `test`.`t2`
flush status;
select oref, a from t2 where a in (select a from t1 where oref=t2.oref);
@@ -156,7 +156,7 @@
2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 Using where; Full scan on NULL key
2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 Using where
Warnings:
-Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))) AS `Z` from `test`.`t3`
drop table t1, t2, t3;
create table t1 (a int NOT NULL, b int NOT NULL, key(a));
@@ -184,7 +184,7 @@
2 DEPENDENT SUBQUERY t1 ref a a 4 func 2 Using where; Full scan on NULL key
2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 Using where
Warnings:
-Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`))))) AS `Z` from `test`.`t3`
drop table t1,t2,t3;
create table t1 (oref int, grp int);
@@ -240,7 +240,7 @@
1 PRIMARY t2 ALL NULL NULL NULL NULL 2
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using where; Full scan on NULL key
Warnings:
-Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`)))))) AS `Z` from `test`.`t2`
select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
a b oref Z
@@ -257,7 +257,7 @@
2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 Using where; Full scan on NULL key
2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 Using where
Warnings:
-Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2`
select a,b, oref,
(a,b) in (select a,b from t1,t4 where c=t2.oref) Z
@@ -302,7 +302,7 @@
1 PRIMARY t2 ALL NULL NULL NULL NULL 8 Using where
2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key
Warnings:
-Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2` where ((`test`.`t2`.`b` = 10) and (`test`.`t2`.`a` = 10))
drop table t1, t2;
create table t1 (oref char(4), grp int, ie int);
@@ -432,7 +432,7 @@
explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ref_or_null idx idx 10 test.t2.oref,func 4 Using where; Using index; Full scan on NULL key
+2 DEPENDENT SUBQUERY t1 ref_or_null idx idx 10 t2.oref,func 4 Using where; Using index; Full scan on NULL key
select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
oref a Z
ee NULL NULL
@@ -457,7 +457,7 @@
from t2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
-2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using temporary; Using filesort
+2 DEPENDENT SUBQUERY t1 ref idx idx 5 t2.oref 2 Using where; Using temporary; Using filesort
select oref, a,
a in (select min(ie) from t1 where oref=t2.oref
group by grp having min(ie) > 1) Z
@@ -572,7 +572,7 @@
1 PRIMARY t2 ALL NULL NULL NULL NULL 7
2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key
Warnings:
-Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2`
drop table t1,t2;
create table t1 (oref char(4), grp int, ie int primary key);
| Thread |
|---|
| • bk commit into 5.0 tree (evgen:1.2408) BUG#23800 | eugene | 20 Feb |
