Below is the list of changes that have just been committed into a local
5.1 repository of gluh. When gluh 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-26 12:43:58+04:00, gluh@eagle.(none) +31 -0
Merge mysql.com:/home/gluh/MySQL/Merge/5.0-opt
into mysql.com:/home/gluh/MySQL/Merge/5.1-opt
MERGE: 1.1810.2372.98
BitKeeper/deleted/.del-bdb.result@stripped, 2007-02-26 12:16:48+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.48.3.3
BitKeeper/deleted/.del-bdb.result@stripped, 2007-02-26 12:16:48+04:00, gluh@eagle.(none) +0 -0
Merge rename: mysql-test/r/bdb.result -> BitKeeper/deleted/.del-bdb.result
mysql-test/extra/binlog_tests/blackhole.test@stripped, 2007-02-26 12:16:48+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.4.5.2
mysql-test/extra/binlog_tests/blackhole.test@stripped, 2007-02-26 12:16:48+04:00, gluh@eagle.(none) +0 -0
Merge rename: mysql-test/t/blackhole.test -> mysql-test/extra/binlog_tests/blackhole.test
mysql-test/r/binlog_stm_blackhole.result@stripped, 2007-02-26 12:43:56+04:00, gluh@eagle.(none) +0 -0
manual merge
MERGE: 1.5.5.2
mysql-test/r/binlog_stm_blackhole.result@stripped, 2007-02-26 12:16:48+04:00, gluh@eagle.(none) +0 -0
Merge rename: mysql-test/r/blackhole.result -> mysql-test/r/binlog_stm_blackhole.result
mysql-test/r/innodb.result@stripped, 2007-02-26 12:16:48+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.145.1.19
mysql-test/r/insert_select.result@stripped, 2007-02-26 12:43:56+04:00, gluh@eagle.(none) +14 -0
manual merge
MERGE: 1.35.1.10
mysql-test/r/insert_update.result@stripped, 2007-02-26 12:16:48+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.18.1.3
mysql-test/r/myisam.result@stripped, 2007-02-26 12:16:48+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.71.1.27
mysql-test/r/select.result@stripped, 2007-02-26 12:16:48+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.121.1.30
mysql-test/r/subselect.result@stripped, 2007-02-26 12:43:56+04:00, gluh@eagle.(none) +10 -12
manual merge
MERGE: 1.134.1.39
mysql-test/r/subselect3.result@stripped, 2007-02-26 12:16:49+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.1.1.4
mysql-test/r/type_blob.result@stripped, 2007-02-26 12:16:49+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.49.2.5
mysql-test/r/update.result@stripped, 2007-02-26 12:43:56+04:00, gluh@eagle.(none) +0 -0
manual merge
MERGE: 1.31.1.1
mysql-test/t/insert_select.test@stripped, 2007-02-26 12:43:56+04:00, gluh@eagle.(none) +13 -0
manual merge
MERGE: 1.34.1.3
mysql-test/t/insert_update.test@stripped, 2007-02-26 12:16:49+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.20.1.1
mysql-test/t/select.test@stripped, 2007-02-26 12:16:49+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.102.1.23
mysql-test/t/subselect.test@stripped, 2007-02-26 12:43:56+04:00, gluh@eagle.(none) +0 -0
manual merge
MERGE: 1.129.1.11
sql/item.cc@stripped, 2007-02-26 12:16:49+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.113.62.15
sql/item.h@stripped, 2007-02-26 12:16:49+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.183.16.11
sql/mysql_priv.h@stripped, 2007-02-26 12:16:49+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.290.85.7
sql/sql_base.cc@stripped, 2007-02-26 12:16:49+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.235.1.131
sql/sql_delete.cc@stripped, 2007-02-26 12:16:49+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.144.1.48
sql/sql_help.cc@stripped, 2007-02-26 12:43:56+04:00, gluh@eagle.(none) +1 -1
manual merge
MERGE: 1.47.1.7
sql/sql_insert.cc@stripped, 2007-02-26 12:43:56+04:00, gluh@eagle.(none) +2 -3
manual merge
MERGE: 1.146.45.5
sql/sql_lex.cc@stripped, 2007-02-26 12:16:49+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.142.35.4
sql/sql_lex.h@stripped, 2007-02-26 12:16:49+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.175.31.9
sql/sql_prepare.cc@stripped, 2007-02-26 12:16:49+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.142.21.8
sql/sql_select.cc@stripped, 2007-02-26 12:16:50+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.312.1.181
sql/sql_union.cc@stripped, 2007-02-26 12:16:50+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.130.3.3
sql/sql_update.cc@stripped, 2007-02-26 12:43:56+04:00, gluh@eagle.(none) +1 -1
manual merge
MERGE: 1.154.26.8
sql/sql_yacc.yy@stripped, 2007-02-26 12:16:50+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.371.91.8
storage/blackhole/ha_blackhole.cc@stripped, 2007-02-26 12:16:50+04:00, gluh@eagle.(none) +0 -0
Auto merged
MERGE: 1.21.5.2
storage/blackhole/ha_blackhole.cc@stripped, 2007-02-26 12:16:48+04:00, gluh@eagle.(none) +0 -0
Merge rename: sql/ha_blackhole.cc -> storage/blackhole/ha_blackhole.cc
# 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: gluh
# Host: eagle.(none)
# Root: /home/gluh/MySQL/Merge/5.1-opt/RESYNC
--- 1.241/sql/item.cc 2007-02-14 04:09:33 +04:00
+++ 1.242/sql/item.cc 2007-02-26 12:16:49 +04:00
@@ -1637,7 +1637,7 @@ void Item_ident_for_show::make_field(Sen
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), fixed_as_field(0)
{
set_field(f);
/*
@@ -1652,7 +1652,7 @@ Item_field::Item_field(THD *thd, Name_re
Field *f)
:Item_ident(context_arg, f->table->s->db.str, *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), fixed_as_field(0)
{
/*
We always need to provide Item_field with a fully qualified field
@@ -1691,9 +1691,12 @@ Item_field::Item_field(Name_resolution_c
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), fixed_as_field(0)
{
+ SELECT_LEX *select= current_thd->lex->current_select;
collation.set(DERIVATION_IMPLICIT);
+ if (select && select->parsing_place != IN_HAVING)
+ select->select_n_where_fields++;
}
// Constructor need to process subselect with temporary tables (see Item)
@@ -1704,7 +1707,8 @@ Item_field::Item_field(THD *thd, Item_fi
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),
+ fixed_as_field(item->fixed_as_field)
{
collation.set(DERIVATION_IMPLICIT);
}
@@ -3515,8 +3519,46 @@ Item_field::fix_outer_field(THD *thd, Fi
}
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 &&
+ !fixed_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);
+ fixed_as_field= 1;
+ }
if (thd->lex->in_sum_func &&
thd->lex->in_sum_func->nest_level ==
thd->lex->current_select->nest_level)
@@ -3644,7 +3686,7 @@ Item_field::fix_outer_field(THD *thd, Fi
{
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;
@@ -4893,6 +4935,51 @@ void Item_field::update_null_value()
}
+/*
+ Add the field to the select list and substitute it for the reference to
+ the field.
+
+ SYNOPSIS
+ Item_field::update_value_transformer()
+ select_arg current select
+
+ DESCRIPTION
+ If the field doesn't belong to the table being inserted into then it is
+ added to the select list, pointer to it is stored in the ref_pointer_array
+ of the select and the field itself is substituted for the Item_ref object.
+ This is done in order to get correct values from update fields that
+ belongs to the SELECT part in the INSERT .. SELECT .. ON DUPLICATE KEY
+ UPDATE statement.
+
+ RETURN
+ 0 if error occured
+ ref if all conditions are met
+ this field otherwise
+*/
+
+Item *Item_field::update_value_transformer(byte *select_arg)
+{
+ SELECT_LEX *select= (SELECT_LEX*)select_arg;
+ DBUG_ASSERT(fixed);
+
+ if (field->table != select->context.table_list->table &&
+ type() != Item::TRIGGER_FIELD_ITEM)
+ {
+ List<Item> *all_fields= &select->join->all_fields;
+ Item **ref_pointer_array= select->ref_pointer_array;
+ int el= all_fields->elements;
+ Item_ref *ref;
+
+ ref_pointer_array[el]= (Item*)this;
+ all_fields->push_front((Item*)this);
+ ref= new Item_ref(&select->context, ref_pointer_array + el,
+ table_name, field_name);
+ return ref;
+ }
+ return this;
+}
+
+
Item_ref::Item_ref(Name_resolution_context *context_arg,
Item **item, const char *table_name_arg,
const char *field_name_arg)
@@ -4902,8 +4989,7 @@ Item_ref::Item_ref(Name_resolution_conte
/*
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();
}
@@ -5204,7 +5290,7 @@ void Item_ref::print(String *str)
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));
@@ -5452,7 +5538,7 @@ bool Item_direct_ref::get_date(TIME *lti
/*
- Prepare referenced view viewld then call usual Item_direct_ref::fix_fields
+ Prepare referenced field then call usual Item_direct_ref::fix_fields
SYNOPSIS
Item_direct_view_ref::fix_fields()
@@ -5472,6 +5558,31 @@ bool Item_direct_view_ref::fix_fields(TH
if (!(*ref)->fixed &&
((*ref)->fix_fields(thd, ref)))
return TRUE;
+ return Item_direct_ref::fix_fields(thd, reference);
+}
+
+/*
+ Prepare referenced outer 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)
+{
+ DBUG_ASSERT(*ref);
+ /* outer_field->check_cols() will be made in Item_direct_ref::fix_fields */
+ outer_field->fixed_as_field= 1;
+ if (!outer_field->fixed &&
+ (outer_field->fix_fields(thd, reference)))
+ return TRUE;
+ table_name= outer_field->table_name;
return Item_direct_ref::fix_fields(thd, reference);
}
--- 1.224/sql/item.h 2007-02-03 02:22:13 +04:00
+++ 1.225/sql/item.h 2007-02-26 12:16:49 +04:00
@@ -325,10 +325,10 @@ private:
TABLE_LIST *save_first_name_resolution_table;
TABLE_LIST *save_next_name_resolution_table;
bool save_resolve_in_select_list;
+ TABLE_LIST *save_next_local;
public:
Name_resolution_context_state() {} /* Remove gcc warning */
- TABLE_LIST *save_next_local;
public:
/* Save the state of a name resolution context. */
@@ -350,6 +350,11 @@ public:
context->first_name_resolution_table= save_first_name_resolution_table;
context->resolve_in_select_list= save_resolve_in_select_list;
}
+
+ TABLE_LIST *get_first_name_resolution_table()
+ {
+ return save_first_name_resolution_table;
+ }
};
@@ -912,6 +917,7 @@ public:
virtual Item_field *filed_for_view_update() { return 0; }
virtual Item *neg_transformer(THD *thd) { return NULL; }
+ virtual Item *update_value_transformer(byte *select_arg) { return this; }
virtual Item *safe_charset_converter(CHARSET_INFO *tocs);
void delete_self()
{
@@ -1311,7 +1317,7 @@ public:
uint have_privileges;
/* field need any privileges (for VIEW creation) */
bool any_privileges;
-
+ bool fixed_as_field;
Item_field(Name_resolution_context *context_arg,
const char *db_arg,const char *table_name_arg,
const char *field_name_arg);
@@ -1391,6 +1397,7 @@ public:
Item_field *filed_for_view_update() { return this; }
Item *safe_charset_converter(CHARSET_INFO *tocs);
int fix_outer_field(THD *thd, Field **field, Item **reference);
+ virtual Item *update_value_transformer(byte *select_arg);
friend class Item_default_value;
friend class Item_insert_value;
friend class st_select_lex_unit;
@@ -1921,7 +1928,7 @@ class Item_ref :public Item_ident
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;
Item_ref(Name_resolution_context *context_arg,
@@ -1982,7 +1989,7 @@ public:
(*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(); }
@@ -2052,6 +2059,40 @@ public:
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.481/sql/mysql_priv.h 2007-02-14 04:09:33 +04:00
+++ 1.482/sql/mysql_priv.h 2007-02-26 12:16:49 +04:00
@@ -901,6 +901,8 @@ int setup_order(THD *thd, Item **ref_poi
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.374/sql/sql_base.cc 2007-02-02 10:19:32 +04:00
+++ 1.375/sql/sql_base.cc 2007-02-26 12:16:49 +04:00
@@ -5443,21 +5443,8 @@ bool setup_tables(THD *thd, Name_resolut
uint tablenr= 0;
DBUG_ENTER("setup_tables");
- /*
- Due to the various call paths that lead to setup_tables() it may happen
- that context->table_list and context->first_name_resolution_table can be
- NULL (this is typically done when creating TABLE_LISTs internally).
- TODO:
- Investigate all cases when this my happen, initialize the name resolution
- context correctly in all those places, and remove the context reset below.
- */
- if (!context->table_list || !context->first_name_resolution_table)
- {
- /* Test whether the context is in a consistent state. */
- DBUG_ASSERT(!context->first_name_resolution_table && !context->table_list);
- context->table_list= context->first_name_resolution_table= tables;
- }
-
+ DBUG_ASSERT ((select_insert && !tables->next_name_resolution_table) || !tables ||
+ (context->table_list && context->first_name_resolution_table));
/*
this is used for INSERT ... SELECT.
For select we setup tables except first (and its underlying tables)
--- 1.208/sql/sql_delete.cc 2007-02-02 14:26:51 +04:00
+++ 1.209/sql/sql_delete.cc 2007-02-26 12:16:49 +04:00
@@ -377,6 +377,7 @@ bool mysql_prepare_delete(THD *thd, TABL
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,
@@ -400,6 +401,11 @@ bool mysql_prepare_delete(THD *thd, TABL
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.246/sql/sql_insert.cc 2007-02-14 01:15:19 +04:00
+++ 1.247/sql/sql_insert.cc 2007-02-26 12:43:56 +04:00
@@ -946,6 +946,8 @@ bool mysql_prepare_insert(THD *thd, TABL
DBUG_PRINT("enter", ("table_list 0x%lx, table 0x%lx, view %d",
(ulong)table_list, (ulong)table,
(int)insert_into_view));
+ /* INSERT should have a SELECT or VALUES clause */
+ DBUG_ASSERT (!select_insert || !values);
/*
For subqueries in VALUES() we should not see the table in which we are
@@ -977,44 +979,40 @@ bool mysql_prepare_insert(THD *thd, TABL
if (mysql_prepare_insert_check_table(thd, table_list, fields, select_insert))
DBUG_RETURN(TRUE);
- /* Save the state of the current name resolution context. */
- ctx_state.save_state(context, table_list);
-
- /*
- Perform name resolution only in the first table - 'table_list',
- which is the table that is inserted into.
- */
- table_list->next_local= 0;
- context->resolve_in_table_list_only(table_list);
/* Prepare the fields in the statement. */
- if (values &&
- !(res= check_insert_fields(thd, context->table_list, fields, *values,
- !insert_into_view, &map) ||
- setup_fields(thd, 0, *values, MARK_COLUMNS_READ, 0, 0)) &&
- duplic == DUP_UPDATE)
+ if (values)
{
- select_lex->no_wrap_view_item= TRUE;
- res= check_update_fields(thd, context->table_list, update_fields, &map);
- select_lex->no_wrap_view_item= FALSE;
+ /* if we have INSERT ... VALUES () we cannot have a GROUP BY clause */
+ DBUG_ASSERT (!select_lex->group_list.elements);
+
+ /* Save the state of the current name resolution context. */
+ ctx_state.save_state(context, table_list);
+
/*
- When we are not using GROUP BY we can refer to other tables in the
- ON DUPLICATE KEY part.
- */
- if (select_lex->group_list.elements == 0)
- {
- context->table_list->next_local= ctx_state.save_next_local;
- /* first_name_resolution_table was set by resolve_in_table_list_only() */
- context->first_name_resolution_table->
- next_name_resolution_table= ctx_state.save_next_local;
+ Perform name resolution only in the first table - 'table_list',
+ which is the table that is inserted into.
+ */
+ table_list->next_local= 0;
+ context->resolve_in_table_list_only(table_list);
+
+ if (!(res= check_insert_fields(thd, context->table_list, fields, *values,
+ !insert_into_view, &map) ||
+ setup_fields(thd, 0, *values, 0, 0, 0))
+ && duplic == DUP_UPDATE)
+ {
+ select_lex->no_wrap_view_item= TRUE;
+ res= check_update_fields(thd, context->table_list, update_fields, &map);
+ select_lex->no_wrap_view_item= FALSE;
}
+
+ /* Restore the current context. */
+ ctx_state.restore_state(context, table_list);
+
if (!res)
res= setup_fields(thd, 0, update_values, MARK_COLUMNS_READ, 0, 0);
}
- /* Restore the current context. */
- ctx_state.restore_state(context, table_list);
-
if (res)
DBUG_RETURN(res);
@@ -2477,7 +2475,6 @@ select_insert::prepare(List<Item> &value
if (info.handle_duplicates == DUP_UPDATE)
{
- /* Save the state of the current name resolution context. */
Name_resolution_context *context= &lex->select_lex.context;
Name_resolution_context_state ctx_state;
@@ -2493,19 +2490,38 @@ select_insert::prepare(List<Item> &value
*info.update_fields, &map);
lex->select_lex.no_wrap_view_item= FALSE;
/*
- When we are not using GROUP BY we can refer to other tables in the
- ON DUPLICATE KEY part
- */
- if (lex->select_lex.group_list.elements == 0)
- {
- context->table_list->next_local= ctx_state.save_next_local;
- /* first_name_resolution_table was set by resolve_in_table_list_only() */
- context->first_name_resolution_table->
- next_name_resolution_table= ctx_state.save_next_local;
- }
- res= res || setup_fields(thd, 0, *info.update_values, MARK_COLUMNS_READ,
- 0, 0);
+ When we are not using GROUP BY and there are no ungrouped aggregate functions
+ we can refer to other tables in the ON DUPLICATE KEY part.
+ We use next_name_resolution_table descructively, so check it first (views?)
+ */
+ DBUG_ASSERT (!table_list->next_name_resolution_table);
+ if (lex->select_lex.group_list.elements == 0 &&
+ !lex->select_lex.with_sum_func)
+ /*
+ We must make a single context out of the two separate name resolution contexts :
+ the INSERT table and the tables in the SELECT part of INSERT ... SELECT.
+ To do that we must concatenate the two lists
+ */
+ table_list->next_name_resolution_table= ctx_state.get_first_name_resolution_table();
+
+ res= res || setup_fields(thd, 0, *info.update_values, 1, 0, 0);
+ if (!res)
+ {
+ /*
+ Traverse the update values list and substitute fields from the
+ select for references (Item_ref objects) to them. This is done in
+ order to get correct values from those fields when the select
+ employs a temporary table.
+ */
+ List_iterator<Item> li(*info.update_values);
+ Item *item;
+ while ((item= li++))
+ {
+ item->transform(&Item::update_value_transformer,
+ (byte*)lex->current_select);
+ }
+ }
/* Restore the current context. */
ctx_state.restore_state(context, table_list);
}
--- 1.224/sql/sql_lex.cc 2007-02-13 12:30:00 +04:00
+++ 1.225/sql/sql_lex.cc 2007-02-26 12:16:49 +04:00
@@ -1172,6 +1172,7 @@ void st_select_lex::init_query()
cond_count= between_count= with_wild= 0;
conds_processed_with_permanent_arena= 0;
ref_pointer_array= 0;
+ select_n_where_fields= 0;
select_n_having_items= 0;
subquery_in_having= explicit_limit= 0;
is_item_list_lookup= 0;
@@ -1212,6 +1213,7 @@ void st_select_lex::init_select()
is_correlated= 0;
cur_pos_in_select_list= UNDEF_POS;
non_agg_fields.empty();
+ inner_refs_list.empty();
}
/*
@@ -1569,6 +1571,7 @@ bool st_select_lex::setup_ref_array(THD
(Item **)arena->alloc(sizeof(Item*) * (n_child_sum_items +
item_list.elements +
select_n_having_items +
+ select_n_where_fields +
order_group_num)*5)) == 0;
}
--- 1.260/sql/sql_lex.h 2007-02-03 02:22:14 +04:00
+++ 1.261/sql/sql_lex.h 2007-02-26 12:16:49 +04:00
@@ -566,6 +566,11 @@ public:
uint select_n_having_items;
uint cond_count; /* number of arguments of and/or/xor in where/having/on */
uint between_count; /* number of between predicates in where/having/on */
+ /*
+ Number of fields used in select list or where clause of current select
+ and all inner subselects.
+ */
+ uint select_n_where_fields;
enum_parsing_place parsing_place; /* where we are parsing expression */
bool with_sum_func; /* sum function indicator */
/*
@@ -583,7 +588,8 @@ public:
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.491/sql/sql_select.cc 2007-02-14 04:09:34 +04:00
+++ 1.492/sql/sql_select.cc 2007-02-26 12:16:50 +04:00
@@ -268,6 +268,70 @@ bool handle_select(THD *thd, LEX *lex, s
/*
+ 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 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,
@@ -394,6 +458,10 @@ JOIN::prepare(Item ***rref_pointer_array
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;
@@ -474,6 +542,9 @@ JOIN::prepare(Item ***rref_pointer_array
}
}
+ if (!procedure && result && result->prepare(fields_list, unit_arg))
+ goto err; /* purecov: inspected */
+
/* Init join struct */
count_field_types(&tmp_table_param, all_fields, 0);
ref_pointer_array_size= all_fields.elements*sizeof(Item*);
@@ -487,9 +558,6 @@ JOIN::prepare(Item ***rref_pointer_array
goto err;
}
#endif
- if (!procedure && result && result->prepare(fields_list, unit_arg))
- goto err; /* purecov: inspected */
-
if (select_lex->olap == ROLLUP_TYPE && rollup_init())
goto err;
if (alloc_func_list())
@@ -2884,15 +2952,9 @@ add_key_field(KEY_FIELD **key_fields,uin
/*
We can't use indexes if the effective collation
of the operation differ from the field collation.
-
- We also cannot use index on a text column, as the column may
- contain 'x' 'x\t' 'x ' and 'read_next_same' will stop after
- 'x' when searching for WHERE col='x '
*/
if (field->cmp_type() == STRING_RESULT &&
- (((Field_str*)field)->charset() != cond->compare_collation() ||
- ((*value)->type() != Item::NULL_ITEM &&
- (field->flags & BLOB_FLAG) && !field->binary())))
+ ((Field_str*)field)->charset() != cond->compare_collation())
return;
}
}
@@ -5287,13 +5349,15 @@ get_store_key(THD *thd, KEYUSE *keyuse,
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,
@@ -8887,7 +8951,8 @@ static Field *create_tmp_field_from_item
type they needed to be handled separately.
*/
if ((type= item->field_type()) == MYSQL_TYPE_DATETIME ||
- type == MYSQL_TYPE_TIME || type == MYSQL_TYPE_DATE)
+ type == MYSQL_TYPE_TIME || type == MYSQL_TYPE_DATE ||
+ type == MYSQL_TYPE_TIMESTAMP)
new_field= item->tmp_table_field_from_field_type(table, 1);
/*
Make sure that the blob fits into a Field_varstring which has
@@ -9003,8 +9068,7 @@ Field *create_tmp_field(THD *thd, TABLE
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();
@@ -13721,8 +13785,7 @@ count_field_types(TMP_TABLE_PARAM *param
{
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.224/sql/sql_update.cc 2007-02-14 01:15:19 +04:00
+++ 1.225/sql/sql_update.cc 2007-02-26 12:43:56 +04:00
@@ -133,6 +133,7 @@ int mysql_update(THD *thd,
SELECT_LEX *select_lex= &thd->lex->select_lex;
bool need_reopen;
ulonglong id;
+ List<Item> all_fields;
DBUG_ENTER("mysql_update");
for ( ; ; )
@@ -215,6 +216,10 @@ int mysql_update(THD *thd,
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.537/sql/sql_yacc.yy 2007-02-03 02:22:17 +04:00
+++ 1.538/sql/sql_yacc.yy 2007-02-26 12:16:50 +04:00
@@ -5941,8 +5941,13 @@ select_into:
| select_from into;
select_from:
- FROM join_table_list where_clause group_clause having_clause
+ FROM join_table_list where_clause group_clause having_clause
opt_order_clause opt_limit_clause procedure_clause
+ {
+ Select->context.table_list=
+ Select->context.first_name_resolution_table=
+ (TABLE_LIST *) Select->table_list.first;
+ }
| FROM DUAL_SYM where_clause opt_limit_clause
/* oracle compatibility: oracle always requires FROM clause,
and DUAL is system table without fields.
@@ -11044,6 +11049,12 @@ subselect_end:
lex->current_select = lex->current_select->return_after_parsing();
lex->nest_level--;
lex->current_select->n_child_sum_items += child->n_sum_items;
+ /*
+ A subselect can add fields to an outer select. Reserve space for
+ them.
+ */
+ lex->current_select->select_n_where_fields+=
+ child->select_n_where_fields;
};
/**************************************************************************
--- 1.143/sql/sql_union.cc 2007-01-29 03:51:40 +04:00
+++ 1.144/sql/sql_union.cc 2007-02-26 12:16:50 +04:00
@@ -147,6 +147,8 @@ st_select_lex_unit::init_prepare_fake_se
fake_select_lex->table_list.link_in_list((byte *)&result_table_list,
(byte **)
&result_table_list.next_local);
+ fake_select_lex->context.table_list= fake_select_lex->context.first_name_resolution_table=
+ fake_select_lex->get_table_list();
for (ORDER *order= (ORDER *)global_parameters->order_list.first;
order;
order=order->next)
--- 1.164/mysql-test/r/subselect.result 2007-02-02 10:19:31 +04:00
+++ 1.165/mysql-test/r/subselect.result 2007-02-26 12:43:56 +04:00
@@ -224,7 +224,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 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 @@ id select_type table type possible_keys
3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 100.00 Using where
NULL UNION RESULT <union2,3> ALL NULL 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
@@ -328,11 +328,11 @@ patient_uq clinic_uq
1 2
2 2
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 filtered Extra
-1 PRIMARY t6 ALL NULL NULL NULL NULL 4 100.00 Using where
-2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 100.00 Using index
+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 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 @@ explain extended select (select a+1) fro
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
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;
@@ -1739,11 +1739,11 @@ id select_type table type possible_keys
Warnings:
Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`) in t1 on PRIMARY where (`test`.`t1`.`id` < 8))))))
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 filtered Extra
-1 PRIMARY tt ALL NULL NULL NULL NULL 12 100.00 Using where
-2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 100.00 Using where; Using index
+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 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 @@ id select_type table type possible_keys
1 PRIMARY up ALL NULL NULL NULL NULL 2 100.00 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 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);
@@ -3717,4 +3717,46 @@ INSERT INTO t1 VALUES ('a');
SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
s1
a
+DROP TABLE t1;
+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.143/mysql-test/t/subselect.test 2007-02-02 10:19:31 +04:00
+++ 1.144/mysql-test/t/subselect.test 2007-02-26 12:43:56 +04:00
@@ -2600,7 +2600,34 @@ SELECT * FROM t1
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;
#
# Bug#20835 (literal string with =any values)
--- 1.5.5.1/mysql-test/r/blackhole.result 2007-02-14 20:35:52 +04:00
+++ 1.18/mysql-test/r/binlog_stm_blackhole.result 2007-02-26 12:43:56 +04:00
@@ -70,8 +70,8 @@ a b
Only MyISAM tables support collections
Full-text indexes are called collections
explain extended select * from t1 where MATCH(a,b) AGAINST ("collections");
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 fulltext a a 0 1 Using where
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 fulltext a a 0 1 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against (_latin1'collections'))
select * from t1 where MATCH(a,b) AGAINST ("indexes");
@@ -134,3 +134,21 @@ ALTER TABLE t1 DROP INDEX a;
ALTER TABLE t1 ADD PRIMARY KEY(a);
DELETE FROM t1 WHERE a=10;
DROP TABLE t1;
+reset master;
+create table t1 (a int) engine=blackhole;
+set autocommit=0;
+start transaction;
+insert into t1 values(1);
+commit;
+start transaction;
+insert into t1 values(2);
+rollback;
+set autocommit=1;
+show binlog events;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Format_desc 1 # Server ver: VERSION, Binlog ver: 4
+master-bin.000001 # Query 1 # use `test`; create table t1 (a int) engine=blackhole
+master-bin.000001 # Query 1 # use `test`; BEGIN
+master-bin.000001 # Query 1 # use `test`; insert into t1 values(1)
+master-bin.000001 # Query 1 # use `test`; COMMIT
+drop table if exists t1;
--- 1.4.5.1/mysql-test/t/blackhole.test 2007-02-14 20:35:53 +04:00
+++ 1.14/mysql-test/extra/binlog_tests/blackhole.test 2007-02-26 12:16:48 +04:00
@@ -123,6 +123,7 @@ select * from t3;
let $VERSION=`select version()`;
--replace_result $VERSION VERSION
--replace_column 2 # 5 #
+--replace_regex /table_id: [0-9]+/table_id: #/
show binlog events;
drop table t1,t2,t3;
@@ -143,3 +144,23 @@ DELETE FROM t1 WHERE a=10;
DROP TABLE t1;
# End of 4.1 tests
+# Test that a transaction which is rolled back does not go into binlog
+# and that a transaction which is committed does
+
+reset master;
+create table t1 (a int) engine=blackhole;
+set autocommit=0;
+start transaction;
+insert into t1 values(1);
+commit;
+start transaction;
+insert into t1 values(2);
+rollback;
+set autocommit=1;
+--replace_result $VERSION VERSION
+--replace_column 2 # 5 #
+--replace_regex /table_id: [0-9]+/table_id: #/
+show binlog events;
+drop table if exists t1;
+
+# End of 5.0 tests
--- 1.21.5.1/sql/ha_blackhole.cc 2007-02-14 20:35:54 +04:00
+++ 1.47/storage/blackhole/ha_blackhole.cc 2007-02-26 12:16:50 +04:00
@@ -19,42 +19,25 @@
#endif
#include "mysql_priv.h"
-#ifdef HAVE_BLACKHOLE_DB
#include "ha_blackhole.h"
+/* Static declarations for handlerton */
-/* Blackhole storage engine handlerton */
+static handler *blackhole_create_handler(handlerton *hton,
+ TABLE_SHARE *table,
+ MEM_ROOT *mem_root)
+{
+ return new (mem_root) ha_blackhole(hton, table);
+}
-handlerton blackhole_hton= {
- "BLACKHOLE",
- SHOW_OPTION_YES,
- "/dev/null storage engine (anything you write to it disappears)",
- DB_TYPE_BLACKHOLE_DB,
- NULL,
- 0, /* slot */
- 0, /* savepoint size. */
- NULL, /* close_connection */
- NULL, /* savepoint */
- NULL, /* rollback to savepoint */
- NULL, /* release savepoint */
- NULL, /* commit */
- NULL, /* rollback */
- NULL, /* prepare */
- NULL, /* recover */
- NULL, /* commit_by_xid */
- NULL, /* rollback_by_xid */
- NULL, /* create_cursor_read_view */
- NULL, /* set_cursor_read_view */
- NULL, /* close_cursor_read_view */
- HTON_CAN_RECREATE
-};
/*****************************************************************************
** BLACKHOLE tables
*****************************************************************************/
-ha_blackhole::ha_blackhole(TABLE *table_arg)
- :handler(&blackhole_hton, table_arg)
+ha_blackhole::ha_blackhole(handlerton *hton,
+ TABLE_SHARE *table_arg)
+ :handler(hton, table_arg)
{}
@@ -92,13 +75,12 @@ int ha_blackhole::create(const char *nam
const char *ha_blackhole::index_type(uint key_number)
{
DBUG_ENTER("ha_blackhole::index_type");
- DBUG_RETURN((table->key_info[key_number].flags & HA_FULLTEXT) ?
+ DBUG_RETURN((table_share->key_info[key_number].flags & HA_FULLTEXT) ?
"FULLTEXT" :
- (table->key_info[key_number].flags & HA_SPATIAL) ?
+ (table_share->key_info[key_number].flags & HA_SPATIAL) ?
"SPATIAL" :
- (table->key_info[key_number].algorithm == HA_KEY_ALG_RTREE) ?
- "RTREE" :
- "BTREE");
+ (table_share->key_info[key_number].algorithm ==
+ HA_KEY_ALG_RTREE) ? "RTREE" : "BTREE");
}
int ha_blackhole::write_row(byte * buf)
@@ -141,16 +123,9 @@ int ha_blackhole::info(uint flag)
{
DBUG_ENTER("ha_blackhole::info");
- records= 0;
- deleted= 0;
- errkey= 0;
- mean_rec_length= 0;
- data_file_length= 0;
- index_file_length= 0;
- max_data_file_length= 0;
- delete_length= 0;
+ bzero((char*) &stats, sizeof(stats));
if (flag & HA_STATUS_AUTO)
- auto_increment_value= 1;
+ stats.auto_increment_value= 1;
DBUG_RETURN(0);
}
@@ -226,4 +201,33 @@ int ha_blackhole::index_last(byte * buf)
DBUG_RETURN(HA_ERR_END_OF_FILE);
}
-#endif /* HAVE_BLACKHOLE_DB */
+static int blackhole_init(void *p)
+{
+ handlerton *blackhole_hton;
+ blackhole_hton= (handlerton *)p;
+ blackhole_hton->state= SHOW_OPTION_YES;
+ blackhole_hton->db_type= DB_TYPE_BLACKHOLE_DB;
+ blackhole_hton->create= blackhole_create_handler;
+ blackhole_hton->flags= HTON_CAN_RECREATE;
+ return 0;
+}
+
+struct st_mysql_storage_engine blackhole_storage_engine=
+{ MYSQL_HANDLERTON_INTERFACE_VERSION };
+
+mysql_declare_plugin(blackhole)
+{
+ MYSQL_STORAGE_ENGINE_PLUGIN,
+ &blackhole_storage_engine,
+ "BLACKHOLE",
+ "MySQL AB",
+ "/dev/null storage engine (anything you write to it disappears)",
+ PLUGIN_LICENSE_GPL,
+ blackhole_init, /* Plugin Init */
+ NULL, /* Plugin Deinit */
+ 0x0100 /* 1.0 */,
+ NULL, /* status variables */
+ NULL, /* system variables */
+ NULL /* config options */
+}
+mysql_declare_plugin_end;
--- 1.185/mysql-test/r/innodb.result 2007-02-01 01:24:14 +04:00
+++ 1.186/mysql-test/r/innodb.result 2007-02-26 12:16:48 +04:00
@@ -1984,7 +1984,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ref c c 11 const # Using where; Using index
explain select count(*) from t1 where t='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range t t 13 NULL # Using where
+1 SIMPLE t1 ref t t 13 const # Using where
explain select count(*) from t1 where v like 'a%';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range v v 13 NULL # Using where; Using index
--- 1.46/mysql-test/r/insert_select.result 2007-02-14 01:15:19 +04:00
+++ 1.47/mysql-test/r/insert_select.result 2007-02-26 12:43:56 +04:00
@@ -717,3 +717,30 @@ select * from t1;
f1 f2
1 2
drop table t1;
+create table t1(f1 int primary key auto_increment, f2 int unique);
+insert into t1(f2) values(1);
+select @@identity;
+@@identity
+1
+insert ignore t1(f2) values(1);
+select @@identity;
+@@identity
+0
+insert ignore t1(f2) select 1;
+select @@identity;
+@@identity
+0
+drop table t1;
+CREATE TABLE t1 (f1 INT, f2 INT );
+CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT);
+INSERT INTO t1 VALUES (1,1),(2,2),(10,10);
+INSERT INTO t2 (f1, f2) SELECT f1, f2 FROM t1;
+INSERT INTO t2 (f1, f2)
+SELECT f1, f1 FROM t2 src WHERE f1 < 2
+ON DUPLICATE KEY UPDATE f1 = 100 + src.f1;
+SELECT * FROM t2;
+f1 f2
+101 1
+2 2
+10 10
+DROP TABLE t1, t2;
--- 1.152/mysql-test/r/select.result 2007-02-14 04:09:32 +04:00
+++ 1.153/mysql-test/r/select.result 2007-02-26 12:16:48 +04:00
@@ -3785,4 +3785,152 @@ case when 1 then cast(111111111111111111
coalesce(cast(1111111111111111111 as unsigned), 1) co;
i c co
1111111111111111111 1111111111111111111 1111111111111111111
+CREATE TABLE t1 (name varchar(255));
+CREATE TABLE t2 (name varchar(255), n int, KEY (name(3)));
+INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa');
+INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3);
+INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
+INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
+SELECT * FROM t2;
+name n
+bb 1
+aa 2
+cc 3
+cc 4
+cc 5
+bb 6
+cc 7
+SELECT * FROM t2 ORDER BY name;
+name n
+aa 2
+bb 1
+bb 6
+cc 4
+cc 3
+cc 5
+cc 7
+SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
+name LENGTH(name) n
+aa 2 2
+bb 2 1
+bb 3 6
+cc 4 4
+cc 5 3
+cc 2 5
+cc 3 7
+EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref name name 6 const 3 Using where
+SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
+name LENGTH(name) n
+cc 5 3
+cc 2 5
+cc 3 7
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range name name 6 NULL 3 Using where
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+name LENGTH(name) n
+cc 5 3
+cc 4 4
+cc 2 5
+cc 3 7
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+name LENGTH(name) n
+cc 4 4
+cc 5 3
+cc 2 5
+cc 3 7
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5
+1 SIMPLE t2 ref name name 6 test.t1.name 2
+SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+name name n
+ccc NULL NULL
+bb bb 1
+bb bb 6
+cc cc 3
+cc cc 5
+cc cc 7
+aa aa 2
+aa aa 2
+DROP TABLE t1,t2;
+CREATE TABLE t1 (name text);
+CREATE TABLE t2 (name text, n int, KEY (name(3)));
+INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa');
+INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3);
+INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
+INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
+SELECT * FROM t2;
+name n
+bb 1
+aa 2
+cc 3
+cc 4
+cc 5
+bb 6
+cc 7
+SELECT * FROM t2 ORDER BY name;
+name n
+aa 2
+bb 1
+bb 6
+cc 4
+cc 3
+cc 5
+cc 7
+SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
+name LENGTH(name) n
+aa 2 2
+bb 2 1
+bb 3 6
+cc 4 4
+cc 5 3
+cc 2 5
+cc 3 7
+EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref name name 6 const 3 Using where
+SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
+name LENGTH(name) n
+cc 5 3
+cc 2 5
+cc 3 7
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range name name 6 NULL 3 Using where
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+name LENGTH(name) n
+cc 5 3
+cc 4 4
+cc 2 5
+cc 3 7
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+name LENGTH(name) n
+cc 4 4
+cc 5 3
+cc 2 5
+cc 3 7
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5
+1 SIMPLE t2 ref name name 6 test.t1.name 2
+SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+name name n
+ccc NULL NULL
+bb bb 1
+bb bb 6
+cc cc 3
+cc cc 5
+cc cc 7
+aa aa 2
+aa aa 2
+DROP TABLE t1,t2;
End of 5.0 tests
--- 1.56/mysql-test/r/type_blob.result 2006-10-04 19:33:24 +05:00
+++ 1.57/mysql-test/r/type_blob.result 2007-02-26 12:16:49 +04:00
@@ -610,12 +610,12 @@ create table t1 (id integer primary key
insert into t1 (txt) values ('Chevy'), ('Chevy '), (NULL);
select * from t1 where txt='Chevy' or txt is NULL;
id txt
-3 NULL
1 Chevy
2 Chevy
+3 NULL
explain select * from t1 where txt='Chevy' or txt is NULL;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range txt_index txt_index 23 NULL 2 Using where
+1 SIMPLE t1 ref_or_null txt_index txt_index 23 const 2 Using where
select * from t1 where txt='Chevy ';
id txt
1 Chevy
--- 1.32/mysql-test/r/update.result 2007-02-14 01:15:19 +04:00
+++ 1.33/mysql-test/r/update.result 2007-02-26 12:43:56 +04:00
@@ -434,3 +434,22 @@ Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
DROP TABLE t1;
+CREATE TABLE t1 (
+a INT(11),
+quux decimal( 31, 30 ),
+UNIQUE KEY bar (a),
+KEY quux (quux)
+);
+INSERT INTO
+t1 ( a, quux )
+VALUES
+( 1, 1 ),
+( 2, 0.1 );
+INSERT INTO t1( a )
+SELECT @newA := 1 + a FROM t1 WHERE quux <= 0.1;
+SELECT * FROM t1;
+a quux
+1 1.000000000000000000000000000000
+2 0.100000000000000000000000000000
+3 NULL
+DROP TABLE t1;
--- 1.37/mysql-test/t/insert_select.test 2007-02-14 01:15:19 +04:00
+++ 1.38/mysql-test/t/insert_select.test 2007-02-26 12:43:56 +04:00
@@ -279,3 +279,31 @@ insert into t1 values (1,1) on duplicate
--disable_info
select * from t1;
drop table t1;
+
+#
+# Bug#23170: LAST_INSERT_ID isn't reset to 0 in INSERT .. SELECT if no rows
+# were inserted.
+#
+create table t1(f1 int primary key auto_increment, f2 int unique);
+insert into t1(f2) values(1);
+select @@identity;
+insert ignore t1(f2) values(1);
+select @@identity;
+insert ignore t1(f2) select 1;
+select @@identity;
+drop table t1;
+
+#
+# Bug#16630: wrong result, when INSERT t1 SELECT ... FROM t1 ON DUPLICATE
+#
+CREATE TABLE t1 (f1 INT, f2 INT );
+CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT);
+INSERT INTO t1 VALUES (1,1),(2,2),(10,10);
+INSERT INTO t2 (f1, f2) SELECT f1, f2 FROM t1;
+INSERT INTO t2 (f1, f2)
+ SELECT f1, f1 FROM t2 src WHERE f1 < 2
+ ON DUPLICATE KEY UPDATE f1 = 100 + src.f1;
+SELECT * FROM t2;
+DROP TABLE t1, t2;
+
+
--- 1.122/mysql-test/t/select.test 2007-02-14 04:09:32 +04:00
+++ 1.123/mysql-test/t/select.test 2007-02-26 12:16:49 +04:00
@@ -3253,4 +3253,50 @@ select
case when 1 then cast(1111111111111111111 as unsigned) else 1 end c,
coalesce(cast(1111111111111111111 as unsigned), 1) co;
+#
+# Bug #22971: indexes on text columns are ignored for ref accesses
+#
+
+CREATE TABLE t1 (name varchar(255));
+CREATE TABLE t2 (name varchar(255), n int, KEY (name(3)));
+INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa');
+INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3);
+INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
+INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
+SELECT * FROM t2;
+SELECT * FROM t2 ORDER BY name;
+SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
+
+EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
+SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (name text);
+CREATE TABLE t2 (name text, n int, KEY (name(3)));
+INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa');
+INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3);
+INSERT INTO t2 VALUES (concat('cc ', 0x06), 4);
+INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7);
+SELECT * FROM t2;
+SELECT * FROM t2 ORDER BY name;
+SELECT name, LENGTH(name), n FROM t2 ORDER BY name;
+
+EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
+SELECT name, LENGTH(name), n FROM t2 WHERE name='cc ';
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%';
+EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name;
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;
+
+DROP TABLE t1,t2;
+
--echo End of 5.0 tests
--- 1.107/mysql-test/r/myisam.result 2007-02-01 22:16:58 +04:00
+++ 1.108/mysql-test/r/myisam.result 2007-02-26 12:16:48 +04:00
@@ -1071,7 +1071,7 @@ id select_type table type possible_keys
1 SIMPLE t1 ref c c 11 const # Using where; Using index
explain select count(*) from t1 where t='a ';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range t t 13 NULL # Using where
+1 SIMPLE t1 ref t t 13 const # Using where
explain select count(*) from t1 where v like 'a%';
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range v v 13 NULL # Using where; Using index
--- 1.23/mysql-test/r/insert_update.result 2006-09-18 19:30:07 +05:00
+++ 1.24/mysql-test/r/insert_update.result 2007-02-26 12:16:48 +04:00
@@ -219,3 +219,20 @@ SELECT * FROM t1;
a b
45 2
DROP TABLE t1;
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+INSERT INTO t1 SELECT 1, j;
+ERROR 42S22: Unknown column 'j' in 'field list'
+DROP TABLE t1;
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+CREATE TABLE t2 (a INT, b INT);
+CREATE TABLE t3 (a INT, c INT);
+INSERT INTO t1 SELECT 1, a FROM t2 NATURAL JOIN t3
+ON DUPLICATE KEY UPDATE j= a;
+DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t2 VALUES (1), (3);
+INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a;
+ERROR 42S22: Unknown column 'a' in 'field list'
+DROP TABLE t1,t2;
--- 1.21/mysql-test/t/insert_update.test 2007-01-22 20:42:49 +04:00
+++ 1.22/mysql-test/t/insert_update.test 2007-02-26 12:16:49 +04:00
@@ -139,3 +139,26 @@ INSERT INTO t1 VALUES (45, 1) ON DUPLICA
SELECT * FROM t1;
DROP TABLE t1;
+
+#
+# Bug#25831: Deficiencies in INSERT ... SELECT ... field name resolving.
+#
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+--error ER_BAD_FIELD_ERROR
+INSERT INTO t1 SELECT 1, j;
+DROP TABLE t1;
+
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+CREATE TABLE t2 (a INT, b INT);
+CREATE TABLE t3 (a INT, c INT);
+INSERT INTO t1 SELECT 1, a FROM t2 NATURAL JOIN t3
+ ON DUPLICATE KEY UPDATE j= a;
+DROP TABLE t1,t2,t3;
+
+CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t2 VALUES (1), (3);
+--error ER_BAD_FIELD_ERROR
+INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a;
+DROP TABLE t1,t2;
--- 1.5/mysql-test/r/subselect3.result 2007-02-02 10:19:31 +04:00
+++ 1.6/mysql-test/r/subselect3.result 2007-02-26 12:16:49 +04:00
@@ -29,7 +29,7 @@ id select_type table type possible_keys
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 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 @@ id select_type table type possible_keys
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 100.00 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 @@ id select_type table type possible_keys
1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 100.00 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 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 100.00 Using where; Full scan on NULL key
2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 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 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY t1 ref a a 4 func 2 100.00 Using where; Full scan on NULL key
2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 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 @@ id select_type table type possible_keys
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 100.00 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 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key
2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 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 @@ id select_type table type possible_keys
1 PRIMARY t2 ALL NULL NULL NULL NULL 8 100.00 Using where
2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 100.00 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 @@ alter table t1 add index idx(oref,ie);
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 @@ group by grp having min(ie) > 1) 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 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 @@ id select_type table type possible_keys
1 PRIMARY t2 ALL NULL NULL NULL NULL 7 100.00
2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 100.00 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);
--- 1.48.3.2/mysql-test/r/bdb.result 2007-02-17 03:43:49 +04:00
+++ 1.61/BitKeeper/deleted/.del-bdb.result 2007-02-26 12:16:48 +04:00
@@ -48,7 +48,7 @@ id parent_id level
15 102 2
update t1 set id=id+1000;
update t1 set id=1024 where id=1009;
-ERROR 23000: Duplicate entry '1024' for key 1
+ERROR 23000: Duplicate entry '1024' for key 'PRIMARY'
select * from t1;
id parent_id level
1001 100 0
@@ -270,7 +270,7 @@ n after commit
commit;
insert into t1 values (5);
insert into t1 values (4);
-ERROR 23000: Duplicate entry '4' for key 1
+ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
commit;
select n, "after commit" from t1;
n after commit
@@ -279,12 +279,46 @@ n after commit
set autocommit=1;
insert into t1 values (6);
insert into t1 values (4);
-ERROR 23000: Duplicate entry '4' for key 1
+ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
select n from t1;
n
4
5
6
+set autocommit=0;
+begin;
+savepoint `my_savepoint`;
+insert into t1 values (7);
+savepoint `savept2`;
+insert into t1 values (3);
+select n from t1;
+n
+3
+4
+5
+6
+7
+savepoint savept3;
+rollback to savepoint savept2;
+rollback to savepoint savept3;
+ERROR 42000: SAVEPOINT savept3 does not exist
+rollback to savepoint savept2;
+release savepoint `my_savepoint`;
+select n from t1;
+n
+4
+5
+6
+7
+rollback to savepoint `my_savepoint`;
+ERROR 42000: SAVEPOINT my_savepoint does not exist
+rollback to savepoint savept2;
+ERROR 42000: SAVEPOINT savept2 does not exist
+insert into t1 values (8);
+savepoint sv;
+commit;
+savepoint sv;
+set autocommit=1;
rollback;
drop table t1;
create table t1 ( id int NOT NULL PRIMARY KEY, nom varchar(64)) engine=BDB;
@@ -309,7 +343,7 @@ drop table t1;
CREATE TABLE t1 (id char(8) not null primary key, val int not null) engine=bdb;
insert into t1 values ('pippo', 12);
insert into t1 values ('pippo', 12);
-ERROR 23000: Duplicate entry 'pippo' for key 1
+ERROR 23000: Duplicate entry 'pippo' for key 'PRIMARY'
delete from t1;
delete from t1 where id = 'pippo';
select * from t1;
@@ -464,9 +498,9 @@ UNIQUE ggid (ggid)
insert into t1 (ggid,passwd) values ('test1','xxx');
insert into t1 (ggid,passwd) values ('test2','yyy');
insert into t1 (ggid,passwd) values ('test2','this will fail');
-ERROR 23000: Duplicate entry 'test2' for key 2
+ERROR 23000: Duplicate entry 'test2' for key 'ggid'
insert into t1 (ggid,id) values ('this will fail',1);
-ERROR 23000: Duplicate entry '1' for key 1
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
select * from t1 where ggid='test1';
id ggid email passwd
1 test1 xxx
@@ -479,7 +513,7 @@ id ggid email passwd
replace into t1 (ggid,id) values ('this will work',1);
replace into t1 (ggid,passwd) values ('test2','this will work');
update t1 set id=100,ggid='test2' where id=1;
-ERROR 23000: Duplicate entry 'test2' for key 2
+ERROR 23000: Duplicate entry 'test2' for key 'ggid'
select * from t1;
id ggid email passwd
1 this will work
@@ -1013,7 +1047,7 @@ create table t1 (id int NOT NULL,id2 int
insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');
LOCK TABLES t1 WRITE;
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
-ERROR 23000: Duplicate entry '1-1' for key 1
+ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'
select id from t1;
id
0
@@ -1031,7 +1065,7 @@ insert into t1 values (0,0,0,'ABCDEFGHIJ
LOCK TABLES t1 WRITE;
begin;
insert into t1 values (99,1,2,'D'),(1,1,2,'D');
-ERROR 23000: Duplicate entry '1-1' for key 1
+ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'
select id from t1;
id
0
@@ -1332,40 +1366,40 @@ concat('*',v,'*',c,'*',t,'*')
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` varchar(10) default NULL,
- `c` char(10) default NULL,
+ `v` varchar(10) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
`t` text
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1
create table t2 like t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
- `v` varchar(10) default NULL,
- `c` char(10) default NULL,
+ `v` varchar(10) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
`t` text
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1
create table t3 select * from t1;
show create table t3;
Table Create Table
t3 CREATE TABLE `t3` (
- `v` varchar(10) default NULL,
- `c` char(10) default NULL,
+ `v` varchar(10) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
`t` text
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1
alter table t1 modify c varchar(10);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` varchar(10) default NULL,
- `c` varchar(10) default NULL,
+ `v` varchar(10) DEFAULT NULL,
+ `c` varchar(10) DEFAULT NULL,
`t` text
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1
alter table t1 modify v char(10);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` char(10) default NULL,
- `c` varchar(10) default NULL,
+ `v` char(10) DEFAULT NULL,
+ `c` varchar(10) DEFAULT NULL,
`t` text
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1
alter table t1 modify t varchar(10);
@@ -1374,9 +1408,9 @@ Note 1265 Data truncated for column 't'
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` char(10) default NULL,
- `c` varchar(10) default NULL,
- `t` varchar(10) default NULL
+ `v` char(10) DEFAULT NULL,
+ `c` varchar(10) DEFAULT NULL,
+ `t` varchar(10) DEFAULT NULL
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1
select concat('*',v,'*',c,'*',t,'*') from t1;
concat('*',v,'*',c,'*',t,'*')
@@ -1387,8 +1421,8 @@ create table t1 (v varchar(10), c char(1
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` varchar(10) default NULL,
- `c` char(10) default NULL,
+ `v` varchar(10) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
`t` text,
KEY `v` (`v`),
KEY `c` (`c`),
@@ -1453,7 +1487,7 @@ explain select count(*) from t1 where v
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref v v 13 const # Using where
alter table t1 add unique(v);
-ERROR 23000: Duplicate entry '{ ' for key 1
+ERROR 23000: Duplicate entry '{ ' for key 'v_2'
alter table t1 add key(v);
select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';
qq
@@ -1606,8 +1640,8 @@ alter table t1 modify v varchar(300), dr
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` varchar(300) default NULL,
- `c` char(10) default NULL,
+ `v` varchar(300) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
`t` text,
KEY `c` (`c`),
KEY `t` (`t`(10)),
@@ -1686,8 +1720,8 @@ alter table t1 drop key v, add key v (v(
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` varchar(300) default NULL,
- `c` char(10) default NULL,
+ `v` varchar(300) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
`t` text,
KEY `c` (`c`),
KEY `t` (`t`(10)),
@@ -1766,8 +1800,8 @@ alter table t1 modify v varchar(600), dr
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` varchar(600) default NULL,
- `c` char(10) default NULL,
+ `v` varchar(600) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
`t` text,
KEY `c` (`c`),
KEY `t` (`t`(10)),
@@ -1813,16 +1847,16 @@ drop table t1;
create table t1 (a char(10), unique (a));
insert into t1 values ('a ');
insert into t1 values ('a ');
-ERROR 23000: Duplicate entry 'a' for key 1
+ERROR 23000: Duplicate entry 'a' for key 'a'
alter table t1 modify a varchar(10);
insert into t1 values ('a '),('a '),('a '),('a ');
-ERROR 23000: Duplicate entry 'a ' for key 1
+ERROR 23000: Duplicate entry 'a ' for key 'a'
insert into t1 values ('a ');
-ERROR 23000: Duplicate entry 'a ' for key 1
+ERROR 23000: Duplicate entry 'a ' for key 'a'
insert into t1 values ('a ');
-ERROR 23000: Duplicate entry 'a ' for key 1
+ERROR 23000: Duplicate entry 'a ' for key 'a'
insert into t1 values ('a ');
-ERROR 23000: Duplicate entry 'a ' for key 1
+ERROR 23000: Duplicate entry 'a ' for key 'a'
update t1 set a='a ' where a like 'a%';
select concat(a,'.') from t1;
concat(a,'.')
@@ -1844,8 +1878,8 @@ create table t1 (v varchar(10), c char(1
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` varchar(10) default NULL,
- `c` char(10) default NULL,
+ `v` varchar(10) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL,
`t` text,
KEY `v` (`v`(5)),
KEY `c` (`c`(5)),
@@ -1856,15 +1890,15 @@ create table t1 (v char(10) character se
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` char(10) character set utf8 default NULL
+ `v` char(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1
drop table t1;
create table t1 (v varchar(10), c char(10)) row_format=fixed;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` varchar(10) default NULL,
- `c` char(10) default NULL
+ `v` varchar(10) DEFAULT NULL,
+ `c` char(10) DEFAULT NULL
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
insert into t1 values('a','a'),('a ','a ');
select concat('*',v,'*',c,'*') from t1;
@@ -1906,7 +1940,7 @@ Note 1246 Converting column 'v' from VAR
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `v` mediumtext character set utf8
+ `v` mediumtext CHARACTER SET utf8
) ENGINE=BerkeleyDB DEFAULT CHARSET=latin1
drop table t1;
set storage_engine=MyISAM;
@@ -1929,3 +1963,22 @@ commit;
alter table t1 add primary key(a);
drop table t1;
End of 5.0 tests
+create table t1 (a int) engine=bdb;
+set session transaction isolation level repeatable read;
+set transaction isolation level serializable;
+begin;
+select @@tx_isolation;
+@@tx_isolation
+SERIALIZABLE
+insert into t1 values (1);
+set transaction isolation level read committed;
+ERROR 25001: Transaction isolation level can't be changed while a transaction is in progress
+rollback;
+begin;
+select @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+insert into t1 values (1);
+rollback;
+drop table t1;
+End of 5.1 tests
--- 1.197/sql/sql_prepare.cc 2007-02-14 04:09:33 +04:00
+++ 1.198/sql/sql_prepare.cc 2007-02-26 12:16:49 +04:00
@@ -1571,21 +1571,16 @@ error:
static bool mysql_insert_select_prepare_tester(THD *thd)
{
- TABLE_LIST *first;
- bool res;
SELECT_LEX *first_select= &thd->lex->select_lex;
+ TABLE_LIST *second_table= ((TABLE_LIST*)first_select->table_list.first)->
+ next_local;
+
/* Skip first table, which is the table we are inserting in */
- first_select->table_list.first= (byte*)(first=
- ((TABLE_LIST*)first_select->
- table_list.first)->next_local);
- res= mysql_insert_select_prepare(thd);
- /*
- insert/replace from SELECT give its SELECT_LEX for SELECT,
- and item_list belong to SELECT
- */
- thd->lex->select_lex.context.resolve_in_select_list= TRUE;
- thd->lex->select_lex.context.table_list= first;
- return res;
+ first_select->table_list.first= (byte *) second_table;
+ thd->lex->select_lex.context.table_list=
+ thd->lex->select_lex.context.first_name_resolution_table= second_table;
+
+ return mysql_insert_select_prepare(thd);
}
--- 1.54/sql/sql_help.cc 2006-12-23 23:19:53 +04:00
+++ 1.55/sql/sql_help.cc 2007-02-26 12:43:56 +04:00
@@ -662,6 +662,8 @@ bool mysqld_help(THD *thd, const char *m
Init tables and fields to be usable from items
tables do not contain VIEWs => we can pass 0 as conds
*/
+ thd->lex->select_lex.context.table_list=
+ thd->lex->select_lex.context.first_name_resolution_table= &tables[0];
if (setup_tables(thd, &thd->lex->select_lex.context,
&thd->lex->select_lex.top_join_list,
tables, &leaves, FALSE))
| Thread |
|---|
| • bk commit into 5.1 tree (gluh:1.2436) | gluh | 26 Feb |
