diff options
| -rw-r--r-- | mysql-test/r/myisam.result | 16 | ||||
| -rw-r--r-- | mysql-test/t/myisam.test | 18 | ||||
| -rw-r--r-- | sql/sql_update.cc | 135 |
3 files changed, 95 insertions, 74 deletions
diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 8a0273666b4..b4123a08556 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -1603,6 +1603,22 @@ select * from t1; a 42 drop table t1; +CREATE TABLE t1(a VARCHAR(16)); +INSERT INTO t1 VALUES('aaaaaaaa'),(NULL); +UPDATE t1 AS ta1, t1 AS ta2 SET ta1.a='aaaaaaaaaaaaaaaa'; +SELECT * FROM t1; +a +aaaaaaaaaaaaaaaa +aaaaaaaaaaaaaaaa +DROP TABLE t1; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(1),(2); +UPDATE t1,t1 AS t2 SET t1.a=t1.a+2 WHERE t1.a=t2.a-1; +SELECT * FROM t1 ORDER BY a; +a +2 +3 +DROP TABLE t1; End of 4.1 tests create table t1 (c1 int) engine=myisam pack_keys=0; create table t2 (c1 int) engine=myisam pack_keys=1; diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 2bb1984c91c..c2a949a7138 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -977,6 +977,24 @@ connection default; select * from t1; drop table t1; +# +# BUG#21310 - Trees in SQL causing a "crashed" table with MyISAM storage +# engine +# + +# A simplified test case that reflect crashed table issue. +CREATE TABLE t1(a VARCHAR(16)); +INSERT INTO t1 VALUES('aaaaaaaa'),(NULL); +UPDATE t1 AS ta1, t1 AS ta2 SET ta1.a='aaaaaaaaaaaaaaaa'; +SELECT * FROM t1; +DROP TABLE t1; + +# A test case that reflect wrong result set. +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(1),(2); +UPDATE t1,t1 AS t2 SET t1.a=t1.a+2 WHERE t1.a=t2.a-1; +SELECT * FROM t1 ORDER BY a; +DROP TABLE t1; --echo End of 4.1 tests # diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 3e6ce3a419c..48c96ddd495 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -24,8 +24,6 @@ #include "sp_head.h" #include "sql_trigger.h" -static bool safe_update_on_fly(JOIN_TAB *join_tab); - /* Return 0 if row hasn't changed */ static bool compare_record(TABLE *table) @@ -1160,27 +1158,71 @@ int multi_update::prepare(List<Item> ¬_used_values, for (i=0 ; i < table_count ; i++) set_if_bigger(max_fields, fields_for_table[i]->elements); copy_field= new Copy_field[max_fields]; + DBUG_RETURN(thd->is_fatal_error != 0); +} - /* - Mark all copies of tables that are updates to ensure that - init_read_record() will not try to enable a cache on them - The problem is that for queries like +/* + Check if table is safe to update on fly - UPDATE t1, t1 AS t2 SET t1.b=t2.c WHERE t1.a=t2.a; + SYNOPSIS + safe_update_on_fly() + thd Thread handler + join_tab How table is used in join + all_tables List of tables - the row buffer may contain things that doesn't match what is on disk - which will cause an error when reading a row. - (This issue is mostly relevent for MyISAM tables) - */ - for (table_ref= leaves; table_ref; table_ref= table_ref->next_leaf) - { - TABLE *table=table_ref->table; - if ((tables_to_update & table->map) && - unique_table(thd, table_ref, update_tables)) - table->no_cache= 1; // Disable row cache + NOTES + We can update the first table in join on the fly if we know that + a row in this table will never be read twice. This is true under + the following conditions: + + - We are doing a table scan and the data is in a separate file (MyISAM) or + if we don't update a clustered key. + + - We are doing a range scan and we don't update the scan key or + the primary key for a clustered table handler. + + - Table is not joined to itself. + + This function gets information about fields to be updated from + the TABLE::write_set bitmap. + + WARNING + This code is a bit dependent of how make_join_readinfo() works. + + RETURN + 0 Not safe to update + 1 Safe to update +*/ + +static bool safe_update_on_fly(THD *thd, JOIN_TAB *join_tab, + TABLE_LIST *table_ref, TABLE_LIST *all_tables) +{ + TABLE *table= join_tab->table; + if (unique_table(thd, table_ref, all_tables)) + return 0; + switch (join_tab->type) { + case JT_SYSTEM: + case JT_CONST: + case JT_EQ_REF: + return TRUE; // At most one matching row + case JT_REF: + case JT_REF_OR_NULL: + return !is_key_used(table, join_tab->ref.key, table->write_set); + case JT_ALL: + /* If range search on index */ + if (join_tab->quick) + return !join_tab->quick->is_keys_used(table->write_set); + /* If scanning in clustered key */ + if ((table->file->ha_table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX) && + table->s->primary_key < MAX_KEY) + return !is_key_used(table, table->s->primary_key, table->write_set); + return TRUE; + default: + break; // Avoid compler warning } - DBUG_RETURN(thd->is_fatal_error != 0); + return FALSE; + } @@ -1220,7 +1262,7 @@ multi_update::initialize_tables(JOIN *join) table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); if (table == main_table) // First table in join { - if (safe_update_on_fly(join->join_tab)) + if (safe_update_on_fly(thd, join->join_tab, table_ref, all_tables)) { table_to_update= main_table; // Update table on the fly continue; @@ -1272,61 +1314,6 @@ multi_update::initialize_tables(JOIN *join) DBUG_RETURN(0); } -/* - Check if table is safe to update on fly - - SYNOPSIS - safe_update_on_fly - join_tab How table is used in join - - NOTES - We can update the first table in join on the fly if we know that - a row in this table will never be read twice. This is true under - the following conditions: - - - We are doing a table scan and the data is in a separate file (MyISAM) or - if we don't update a clustered key. - - - We are doing a range scan and we don't update the scan key or - the primary key for a clustered table handler. - - This function gets information about fields to be updated from - the TABLE::write_set bitmap. - - WARNING - This code is a bit dependent of how make_join_readinfo() works. - - RETURN - 0 Not safe to update - 1 Safe to update -*/ - -static bool safe_update_on_fly(JOIN_TAB *join_tab) -{ - TABLE *table= join_tab->table; - switch (join_tab->type) { - case JT_SYSTEM: - case JT_CONST: - case JT_EQ_REF: - return TRUE; // At most one matching row - case JT_REF: - case JT_REF_OR_NULL: - return !is_key_used(table, join_tab->ref.key, table->write_set); - case JT_ALL: - /* If range search on index */ - if (join_tab->quick) - return !join_tab->quick->is_keys_used(table->write_set); - /* If scanning in clustered key */ - if ((table->file->ha_table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX) && - table->s->primary_key < MAX_KEY) - return !is_key_used(table, table->s->primary_key, table->write_set); - return TRUE; - default: - break; // Avoid compler warning - } - return FALSE; -} - multi_update::~multi_update() { |