summaryrefslogtreecommitdiff
path: root/sql/sql_update.cc
diff options
context:
space:
mode:
Diffstat (limited to 'sql/sql_update.cc')
-rw-r--r--sql/sql_update.cc167
1 files changed, 104 insertions, 63 deletions
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 0f470062981..5cb8d085c33 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2010, Oracle and/or its affiliates.
Copyright (c) 2011 Monty Program Ab
This program is free software; you can redistribute it and/or modify
@@ -46,7 +46,7 @@
/**
True if the table's input and output record buffers are comparable using
- compare_records(TABLE*).
+ compare_record(TABLE*).
*/
bool records_are_comparable(const TABLE *table) {
return ((table->file->ha_table_flags() & HA_PARTIAL_COLUMN_READ) == 0) ||
@@ -56,18 +56,13 @@ bool records_are_comparable(const TABLE *table) {
/**
Compares the input and outbut record buffers of the table to see if a row
- has changed. The algorithm iterates over updated columns and if they are
- nullable compares NULL bits in the buffer before comparing actual
- data. Special care must be taken to compare only the relevant NULL bits and
- mask out all others as they may be undefined. The storage engine will not
- and should not touch them.
-
- @param table The table to evaluate.
+ has changed.
@return true if row has changed.
@return false otherwise.
*/
-bool compare_records(const TABLE *table)
+
+bool compare_record(const TABLE *table)
{
DBUG_ASSERT(records_are_comparable(table));
@@ -104,7 +99,6 @@ bool compare_records(const TABLE *table)
comparison done above.
*/
if (table->s->can_cmp_whole_record)
- // Fixed-size record: do bitwise comparison of the records
return cmp_record(table,record[1]);
/* Compare null bits */
if (memcmp(table->null_flags,
@@ -262,6 +256,7 @@ int mysql_update(THD *thd,
bool using_limit= limit != HA_POS_ERROR;
bool safe_update= test(thd->variables.option_bits & OPTION_SAFE_UPDATES);
bool used_key_is_modified= FALSE, transactional_table, will_batch;
+ bool can_compare_record;
int res;
int error, loc_error;
uint used_index, dup_key_found;
@@ -285,7 +280,11 @@ int mysql_update(THD *thd,
if (open_tables(thd, &table_list, &table_count, 0))
DBUG_RETURN(1);
- if (table_list->multitable_view)
+ //Prepare views so they are handled correctly.
+ if (mysql_handle_derived(thd->lex, DT_INIT))
+ DBUG_RETURN(1);
+
+ if (table_list->is_multitable())
{
DBUG_ASSERT(table_list->view != 0);
DBUG_PRINT("info", ("Switch to multi-update"));
@@ -297,20 +296,19 @@ int mysql_update(THD *thd,
if (lock_tables(thd, table_list, table_count, 0))
DBUG_RETURN(1);
- if (mysql_handle_derived(thd->lex, &mysql_derived_prepare))
+ if (table_list->handle_derived(thd->lex, DT_MERGE_FOR_INSERT))
DBUG_RETURN(1);
-
- if (thd->fill_derived_tables() &&
- mysql_handle_derived(thd->lex, &mysql_derived_filling))
- {
- mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
+ if (table_list->handle_derived(thd->lex, DT_PREPARE))
DBUG_RETURN(1);
- }
- mysql_handle_derived(thd->lex, &mysql_derived_cleanup);
thd_proc_info(thd, "init");
table= table_list->table;
+ if (!table_list->updatable)
+ {
+ my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "UPDATE");
+ DBUG_RETURN(1);
+ }
/* Calculate "table->covering_keys" based on the WHERE */
table->covering_keys= table->s->keys_in_use;
table->quick_keys.clear_all();
@@ -329,13 +327,17 @@ int mysql_update(THD *thd,
table_list->grant.want_privilege= table->grant.want_privilege= want_privilege;
table_list->register_want_access(want_privilege);
#endif
+ /* 'Unfix' fields to allow correct marking by the setup_fields function. */
+ if (table_list->is_view())
+ unfix_fields(fields);
+
if (setup_fields_with_no_wrap(thd, 0, fields, MARK_COLUMNS_WRITE, 0, 0))
DBUG_RETURN(1); /* purecov: inspected */
if (table_list->view && check_fields(thd, fields))
{
DBUG_RETURN(1);
}
- if (!table_list->updatable || check_key_in_view(thd, table_list))
+ if (check_key_in_view(thd, table_list))
{
my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "UPDATE");
DBUG_RETURN(1);
@@ -365,6 +367,10 @@ int mysql_update(THD *thd,
DBUG_RETURN(1); /* purecov: inspected */
}
+ /* Apply the IN=>EXISTS transformation to all subqueries and optimize them. */
+ if (select_lex->optimize_unflattened_subqueries())
+ 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);
@@ -659,6 +665,13 @@ int mysql_update(THD *thd,
if (table->file->ha_table_flags() & HA_PARTIAL_COLUMN_READ)
table->prepare_for_position();
+ /*
+ We can use compare_record() to optimize away updates if
+ the table handler is returning all columns OR if
+ if all updated columns are read
+ */
+ can_compare_record= records_are_comparable(table);
+
while (!(error=info.read_record(&info)) && !thd->killed)
{
update_virtual_fields(thd, table);
@@ -676,7 +689,7 @@ int mysql_update(THD *thd,
found++;
- if (!records_are_comparable(table) || compare_records(table))
+ if (!can_compare_record || compare_record(table))
{
if ((res= table_list->view_check_option(thd, ignore)) !=
VIEW_CHECK_OK)
@@ -924,6 +937,11 @@ int mysql_update(THD *thd,
}
thd->count_cuted_fields= CHECK_FIELD_IGNORE; /* calc cuted fields */
thd->abort_on_warning= 0;
+ if (thd->lex->current_select->first_cond_optimization)
+ {
+ thd->lex->current_select->save_leaf_tables(thd);
+ thd->lex->current_select->first_cond_optimization= 0;
+ }
*found_return= found;
*updated_return= updated;
DBUG_RETURN((error >= 0 || thd->is_error()) ? 1 : 0);
@@ -973,8 +991,8 @@ bool mysql_prepare_update(THD *thd, TABLE_LIST *table_list,
if (setup_tables_and_check_access(thd, &select_lex->context,
&select_lex->top_join_list,
table_list,
- &select_lex->leaf_tables,
- FALSE, UPDATE_ACL, SELECT_ACL) ||
+ select_lex->leaf_tables,
+ FALSE, UPDATE_ACL, SELECT_ACL, TRUE) ||
setup_conds(thd, table_list, select_lex->leaf_tables, conds) ||
select_lex->setup_ref_array(thd, order_num) ||
setup_order(thd, select_lex->ref_pointer_array,
@@ -1010,8 +1028,8 @@ static table_map get_table_map(List<Item> *items)
Item_field *item;
table_map map= 0;
- while ((item= (Item_field *) item_it++))
- map|= item->used_tables();
+ while ((item= (Item_field *) item_it++))
+ map|= item->all_used_tables();
DBUG_PRINT("info", ("table_map: 0x%08lx", (long) map));
return map;
}
@@ -1046,11 +1064,12 @@ static table_map get_table_map(List<Item> *items)
false otherwise.
*/
static
-bool unsafe_key_update(TABLE_LIST *leaves, table_map tables_for_update)
+bool unsafe_key_update(List<TABLE_LIST> leaves, table_map tables_for_update)
{
- TABLE_LIST *tl= leaves;
+ List_iterator_fast<TABLE_LIST> it(leaves), it2(leaves);
+ TABLE_LIST *tl, *tl2;
- for (tl= leaves; tl ; tl= tl->next_leaf)
+ while ((tl= it++))
{
if (tl->table->map & tables_for_update)
{
@@ -1066,14 +1085,16 @@ bool unsafe_key_update(TABLE_LIST *leaves, table_map tables_for_update)
if (!table_partitioned && !primkey_clustered)
continue;
- for (TABLE_LIST* tl2= tl->next_leaf; tl2 ; tl2= tl2->next_leaf)
+ it2.rewind();
+ while ((tl2= it2++))
{
/*
Look at "next" tables only since all previous tables have
already been checked
*/
TABLE *table2= tl2->table;
- if (table2->map & tables_for_update && table1->s == table2->s)
+ if (tl2 != tl &&
+ table2->map & tables_for_update && table1->s == table2->s)
{
// A table is updated through two aliases
if (table_partitioned &&
@@ -1135,7 +1156,7 @@ int mysql_multi_update_prepare(THD *thd)
{
LEX *lex= thd->lex;
TABLE_LIST *table_list= lex->query_tables;
- TABLE_LIST *tl, *leaves;
+ TABLE_LIST *tl;
List<Item> *fields= &lex->select_lex.item_list;
table_map tables_for_update;
bool update_view= 0;
@@ -1163,7 +1184,7 @@ int mysql_multi_update_prepare(THD *thd)
open_tables(thd, &table_list, &table_count,
(thd->stmt_arena->is_stmt_prepare() ?
MYSQL_OPEN_FORCE_SHARED_MDL : 0))) ||
- mysql_handle_derived(lex, &mysql_derived_prepare))
+ mysql_handle_derived(lex, DT_INIT))
DBUG_RETURN(TRUE);
/*
setup_tables() need for VIEWs. JOIN::prepare() will call setup_tables()
@@ -1171,11 +1192,20 @@ int mysql_multi_update_prepare(THD *thd)
call in setup_tables()).
*/
+ //We need to merge for insert prior to prepare.
+ if (mysql_handle_derived(lex, DT_MERGE_FOR_INSERT))
+ DBUG_RETURN(TRUE);
+ if (mysql_handle_derived(lex, DT_PREPARE))
+ DBUG_RETURN(TRUE);
+
if (setup_tables_and_check_access(thd, &lex->select_lex.context,
&lex->select_lex.top_join_list,
table_list,
- &lex->select_lex.leaf_tables, FALSE,
- UPDATE_ACL, SELECT_ACL))
+ lex->select_lex.leaf_tables, FALSE,
+ UPDATE_ACL, SELECT_ACL, FALSE))
+ DBUG_RETURN(TRUE);
+
+ if (lex->select_lex.handle_derived(thd->lex, DT_MERGE))
DBUG_RETURN(TRUE);
if (setup_fields_with_no_wrap(thd, 0, *fields, MARK_COLUMNS_WRITE, 0, 0))
@@ -1197,15 +1227,14 @@ int mysql_multi_update_prepare(THD *thd)
thd->table_map_for_update= tables_for_update= get_table_map(fields);
- leaves= lex->select_lex.leaf_tables;
-
- if (unsafe_key_update(leaves, tables_for_update))
+ if (unsafe_key_update(lex->select_lex.leaf_tables, tables_for_update))
DBUG_RETURN(true);
/*
Setup timestamp handling and locking mode
*/
- for (tl= leaves; tl; tl= tl->next_leaf)
+ List_iterator<TABLE_LIST> ti(lex->select_lex.leaf_tables);
+ while ((tl= ti++))
{
TABLE *table= tl->table;
/* Only set timestamp column if this is not modified */
@@ -1252,7 +1281,7 @@ int mysql_multi_update_prepare(THD *thd)
for (tl= table_list; tl; tl= tl->next_local)
{
/* Check access privileges for table */
- if (!tl->derived)
+ if (!tl->is_derived())
{
uint want_privilege= tl->updating ? UPDATE_ACL : SELECT_ACL;
if (check_access(thd, want_privilege, tl->db,
@@ -1267,7 +1296,7 @@ int mysql_multi_update_prepare(THD *thd)
/* check single table update for view compound from several tables */
for (tl= table_list; tl; tl= tl->next_local)
{
- if (tl->effective_algorithm == VIEW_ALGORITHM_MERGE)
+ if (tl->is_merged_derived())
{
TABLE_LIST *for_update= 0;
if (tl->check_single_table(&for_update, tables_for_update, tl))
@@ -1293,7 +1322,8 @@ int mysql_multi_update_prepare(THD *thd)
*/
lex->select_lex.exclude_from_table_unique_test= TRUE;
/* We only need SELECT privilege for columns in the values list */
- for (tl= leaves; tl; tl= tl->next_leaf)
+ ti.rewind();
+ while ((tl= ti++))
{
TABLE *table= tl->table;
TABLE_LIST *tlist;
@@ -1321,15 +1351,10 @@ int mysql_multi_update_prepare(THD *thd)
further check in multi_update::prepare whether to use record cache.
*/
lex->select_lex.exclude_from_table_unique_test= FALSE;
-
- if (thd->fill_derived_tables() &&
- mysql_handle_derived(lex, &mysql_derived_filling))
- {
- mysql_handle_derived(lex, &mysql_derived_cleanup);
- DBUG_RETURN(TRUE);
- }
- mysql_handle_derived(lex, &mysql_derived_cleanup);
+ if (lex->select_lex.save_prep_leaf_tables(thd))
+ DBUG_RETURN(TRUE);
+
DBUG_RETURN (FALSE);
}
@@ -1354,7 +1379,7 @@ bool mysql_multi_update(THD *thd,
DBUG_ENTER("mysql_multi_update");
if (!(*result= new multi_update(table_list,
- thd->lex->select_lex.leaf_tables,
+ &thd->lex->select_lex.leaf_tables,
fields, values,
handle_duplicates, ignore)))
{
@@ -1390,7 +1415,7 @@ bool mysql_multi_update(THD *thd,
multi_update::multi_update(TABLE_LIST *table_list,
- TABLE_LIST *leaves_list,
+ List<TABLE_LIST> *leaves_list,
List<Item> *field_list, List<Item> *value_list,
enum enum_duplicates handle_duplicates_arg,
bool ignore_arg)
@@ -1408,6 +1433,7 @@ multi_update::multi_update(TABLE_LIST *table_list,
int multi_update::prepare(List<Item> &not_used_values,
SELECT_LEX_UNIT *lex_unit)
+
{
TABLE_LIST *table_ref;
SQL_I_List<TABLE_LIST> update;
@@ -1417,6 +1443,7 @@ int multi_update::prepare(List<Item> &not_used_values,
List_iterator_fast<Item> value_it(*values);
uint i, max_fields;
uint leaf_table_count= 0;
+ List_iterator<TABLE_LIST> ti(*leaves);
DBUG_ENTER("multi_update::prepare");
thd->count_cuted_fields= CHECK_FIELD_WARN;
@@ -1436,7 +1463,7 @@ int multi_update::prepare(List<Item> &not_used_values,
TABLE::tmp_set by pointing TABLE::read_set to it and then restore it after
setup_fields().
*/
- for (table_ref= leaves; table_ref; table_ref= table_ref->next_leaf)
+ while ((table_ref= ti++))
{
TABLE *table= table_ref->table;
if (tables_to_update & table->map)
@@ -1454,7 +1481,8 @@ int multi_update::prepare(List<Item> &not_used_values,
int error= setup_fields(thd, 0, *values, MARK_COLUMNS_READ, 0, 0);
- for (table_ref= leaves; table_ref; table_ref= table_ref->next_leaf)
+ ti.rewind();
+ while ((table_ref= ti++))
{
TABLE *table= table_ref->table;
if (tables_to_update & table->map)
@@ -1483,7 +1511,8 @@ int multi_update::prepare(List<Item> &not_used_values,
*/
update.empty();
- for (table_ref= leaves; table_ref; table_ref= table_ref->next_leaf)
+ ti.rewind();
+ while ((table_ref= ti++))
{
/* TODO: add support of view of join support */
TABLE *table=table_ref->table;
@@ -1709,9 +1738,9 @@ loop_end:
{
table_map unupdated_tables= table_ref->check_option->used_tables() &
~first_table_for_update->map;
- for (TABLE_LIST *tbl_ref =leaves;
- unupdated_tables && tbl_ref;
- tbl_ref= tbl_ref->next_leaf)
+ List_iterator<TABLE_LIST> ti(*leaves);
+ TABLE_LIST *tbl_ref;
+ while ((tbl_ref= ti++) && unupdated_tables)
{
if (unupdated_tables & tbl_ref->table->map)
unupdated_tables&= ~tbl_ref->table->map;
@@ -1737,7 +1766,8 @@ loop_end:
do
{
Field_string *field= new Field_string(tbl->file->ref_length, 0,
- tbl->alias, &my_charset_bin);
+ tbl->alias.c_ptr(),
+ &my_charset_bin);
if (!field)
DBUG_RETURN(1);
field->init(tbl);
@@ -1809,7 +1839,7 @@ multi_update::~multi_update()
}
-bool multi_update::send_data(List<Item> &not_used_values)
+int multi_update::send_data(List<Item> &not_used_values)
{
TABLE_LIST *cur_table;
DBUG_ENTER("multi_update::send_data");
@@ -1835,6 +1865,14 @@ bool multi_update::send_data(List<Item> &not_used_values)
if (table == table_to_update)
{
+ /*
+ We can use compare_record() to optimize away updates if
+ the table handler is returning all columns OR if
+ if all updated columns are read
+ */
+ bool can_compare_record;
+ can_compare_record= records_are_comparable(table);
+
table->status|= STATUS_UPDATED;
store_record(table,record[1]);
if (fill_record_n_invoke_before_triggers(thd, *fields_for_table[offset],
@@ -1849,7 +1887,7 @@ bool multi_update::send_data(List<Item> &not_used_values)
*/
table->auto_increment_field_not_null= FALSE;
found++;
- if (!records_are_comparable(table) || compare_records(table))
+ if (!can_compare_record || compare_record(table))
{
int error;
if ((error= cur_table->view_check_option(thd, ignore)) !=
@@ -1946,7 +1984,7 @@ bool multi_update::send_data(List<Item> &not_used_values)
*values_for_table[offset], TRUE, FALSE);
/* Write row, ignoring duplicated updates to a row */
- error= tmp_table->file->ha_write_row(tmp_table->record[0]);
+ error= tmp_table->file->ha_write_tmp_row(tmp_table->record[0]);
if (error != HA_ERR_FOUND_DUPP_KEY && error != HA_ERR_FOUND_DUPP_UNIQUE)
{
if (error &&
@@ -2040,6 +2078,7 @@ int multi_update::do_updates()
DBUG_RETURN(0);
for (cur_table= update_tables; cur_table; cur_table= cur_table->next_local)
{
+ bool can_compare_record;
uint offset= cur_table->shared;
table = cur_table->table;
@@ -2088,6 +2127,8 @@ int multi_update::do_updates()
goto err;
}
+ can_compare_record= records_are_comparable(table);
+
for (;;)
{
if (thd->killed && trans_safe)
@@ -2135,7 +2176,7 @@ int multi_update::do_updates()
TRG_ACTION_BEFORE, TRUE))
goto err2;
- if (!records_are_comparable(table) || compare_records(table))
+ if (!can_compare_record || compare_record(table))
{
int error;
if ((error= cur_table->view_check_option(thd, ignore)) !=
View Lorry Controller Status