diff options
| -rw-r--r-- | Docs/manual.texi | 6 | ||||
| -rw-r--r-- | mysql-test/r/multi_update.result | 35 | ||||
| -rw-r--r-- | mysql-test/t/multi_update.test | 35 | ||||
| -rw-r--r-- | sql/sql_update.cc | 32 |
4 files changed, 96 insertions, 12 deletions
diff --git a/Docs/manual.texi b/Docs/manual.texi index bd9b34936c3..4f9b91e4ad4 100644 --- a/Docs/manual.texi +++ b/Docs/manual.texi @@ -49984,6 +49984,12 @@ Our TODO section contains what we plan to have in 4.0. @xref{TODO MySQL 4.0}. @itemize @bullet @item +Fixed bug in multi-table update when updating a table confused do_select +in reading records from a cache +@item +Fixed bug in multi-table update when several fields are referenced +from a single table +@item Fixed bug in REVOKE that caused user resources to be randomly set @item Fixed bug with a new GRANT on TEMP TABLES diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index de955240de2..328eb9b394e 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -100,3 +100,38 @@ id mydate 6 2002-06-22 00:00:00 7 2002-07-22 00:00:00 DROP TABLE IF EXISTS a,b,c; +drop table if exists parent, child; +CREATE TABLE IF NOT EXISTS `parent` ( +`id` int(11) NOT NULL auto_increment, +`tst` text, +`tst1` text, +PRIMARY KEY (`id`) +) TYPE=MyISAM; +CREATE TABLE IF NOT EXISTS `child` ( +`ID` int(11) NOT NULL auto_increment, +`ParId` int(11) default NULL, +`tst` text, +`tst1` text, +PRIMARY KEY (`ID`), +KEY `IX_ParId_child` (`ParId`), +FOREIGN KEY (`ParId`) REFERENCES `test.parent` (`id`) +) TYPE=MyISAM; +INSERT INTO parent(tst,tst1) +VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE"); +INSERT INTO child(ParId) +VALUES(1), (2), (3); +select * from child; +ID ParId tst tst1 +1 1 NULL NULL +2 2 NULL NULL +3 3 NULL NULL +UPDATE child, parent +SET child.tst = parent.tst, +child.tst1 = parent.tst1 +WHERE child.ParId = parent.Id; +select * from child; +ID ParId tst tst1 +1 1 MySQL MySQL AB +2 2 MSSQL Microsoft +3 3 ORACLE ORACLE +drop table parent, child; diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index ab3d5a7fff6..205dbb10ccf 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -105,3 +105,38 @@ where to_days(now())-to_days(c.mydate)>=30 and c.id=a.id and c.id=b.id; select * from c; DROP TABLE IF EXISTS a,b,c; +drop table if exists parent, child; +CREATE TABLE IF NOT EXISTS `parent` ( + `id` int(11) NOT NULL auto_increment, + `tst` text, + `tst1` text, + PRIMARY KEY (`id`) +) TYPE=MyISAM; + +CREATE TABLE IF NOT EXISTS `child` ( + `ID` int(11) NOT NULL auto_increment, + `ParId` int(11) default NULL, + `tst` text, + `tst1` text, + PRIMARY KEY (`ID`), + KEY `IX_ParId_child` (`ParId`), + FOREIGN KEY (`ParId`) REFERENCES `test.parent` (`id`) +) TYPE=MyISAM; + +INSERT INTO parent(tst,tst1) +VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE"); + +INSERT INTO child(ParId) +VALUES(1), (2), (3); + +select * from child; + +UPDATE child, parent +SET child.tst = parent.tst, +child.tst1 = parent.tst1 +WHERE child.ParId = parent.Id; + +select * from child; + + +drop table parent, child;
\ No newline at end of file diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 5a7c0cb782f..a1a18560a9f 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -419,14 +419,19 @@ multi_update::prepare(List<Item> &values) for (table_ref=update_tables; table_ref; table_ref=table_ref->next, counter++) { - if (table_ref->table == item->field->table && !table_ref->shared) + if (table_ref->table == item->field->table) { - num_updated++; - table_ref->shared=1; - if (!not_trans_safe && !table_ref->table->file->has_transactions()) - not_trans_safe=true; - // to be moved if initialize_tables has to be used - table_ref->table->no_keyread=1; + if (!table_ref->shared) + { + TABLE *tbl=table_ref->table; + num_updated++; + table_ref->shared=1; + if (!not_trans_safe && !table_ref->table->file->has_transactions()) + not_trans_safe=true; + // to be moved if initialize_tables has to be used + tbl->no_keyread=1; + tbl->used_keys=0; + } break; } } @@ -440,7 +445,7 @@ multi_update::prepare(List<Item> &values) } if (!num_updated) { - error = 1; // A proper error message is due here + net_printf(&thd->net, ER_NOT_SUPPORTED_YET, "SET CLAUSE MUST CONTAIN TABLE.FIELD REFERENCE"); DBUG_RETURN(1); } @@ -498,6 +503,7 @@ multi_update::prepare(List<Item> &values) counter++; } } + init_ftfuncs(thd,1); error = 0; // Timestamps do not need to be restored, so far ... DBUG_RETURN(0); } @@ -520,7 +526,7 @@ multi_update::initialize_tables(JOIN *join) { if (tab->table->map & tables_to_update_from) { - We are going to update from this table +// We are going to update from this table TABLE *tbl=walk->table=tab->table; /* Don't use KEYREAD optimization on this table */ tbl->no_keyread=1; @@ -578,6 +584,7 @@ bool multi_update::send_data(List<Item> &values) if (/* compare_record(table, query_id) && */ !(error=table->file->update_row(table->record[1], table->record[0]))) updated++; + table->file->extra(HA_EXTRA_NO_CACHE); return error; } } @@ -615,7 +622,10 @@ bool multi_update::send_data(List<Item> &values) found++; if (/*compare_record(table, query_id) && */ !(error=table->file->update_row(table->record[1], table->record[0]))) + { updated++; + table->file->extra(HA_EXTRA_NO_CACHE); + } else { table->file->print_error(error,MYF(0)); @@ -670,7 +680,7 @@ void multi_update::send_error(uint errcode,const char *err) if ((table_being_updated->table->file->has_transactions() && table_being_updated == update_tables) || !not_trans_safe) ha_rollback_stmt(thd); - else if (do_update) + else if (do_update && num_updated > 1) VOID(do_updates(true)); } @@ -679,8 +689,6 @@ int multi_update::do_updates (bool from_send_error) { int error = 0, counter = 0; - if (num_updated == 1) - return 0; if (from_send_error) { /* Found out table number for 'table_being_updated' */ |