From: Date: May 28 2007 11:29pm Subject: bk commit into 5.0 tree (gshchepa:1.2501) BUG#28716 List-Archive: http://lists.mysql.com/commits/27516 X-Bug: 28716 Message-Id: <20070528212900.D52423D450D@localhost.localdomain> Below is the list of changes that have just been committed into a local 5.0 repository of uchum. When uchum 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-05-29 02:28:54+05:00, gshchepa@stripped +4 -0 Fixed bug #28716. CHECK OPTION expression was evaluated over expired record buffers (with arbitrary data in the fields). Rowids of tables used in CHECK OPTION expression was added to temporary table rows. The multi_update::do_updates() method was improved to restore necessary record buffers before view_check_option() calculation. mysql-test/r/view.result@stripped, 2007-05-29 02:28:47+05:00, gshchepa@stripped +17 -0 Updated test case for bug #28716. mysql-test/t/view.test@stripped, 2007-05-29 02:28:45+05:00, gshchepa@stripped +16 -0 Updated test case for bug #28716. sql/sql_class.h@stripped, 2007-05-29 02:28:09+05:00, gshchepa@stripped +10 -0 Fixed bug #29716. The multi_update::unupdatable_check_opt_tables variable has been added. sql/sql_update.cc@stripped, 2007-05-29 02:28:24+05:00, gshchepa@stripped +112 -28 Fixed bug #29716. Rowids of tables used in CHECK OPTION expression was added to temporary table rows. The multi_update::do_updates() method was improved to restore necessary record buffers before view_check_option() calculation. # 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: gshchepa # Host: gleb.loc # Root: /home/uchum/work/bk/mysql-5.0-opt-28716 --- 1.331/sql/sql_class.h 2007-05-15 14:56:04 +05:00 +++ 1.332/sql/sql_class.h 2007-05-29 02:28:09 +05:00 @@ -2283,6 +2283,16 @@ class multi_update :public select_result List *fields, *values; List **fields_for_table, **values_for_table; uint table_count; + /* + unupdated_check_opt_tables is an ordered list of tables whos join + combination with an updating table is used to satisfy secondary + evaluation of CHECK OPTION condition before an UPDATE with values + from the temporary table. + This list doesn't contain the updating table (UPDATE of VIEW can + update only one table). + For a regular multi-update UPDATE statements this list is empty. + */ + List unupdated_check_opt_tables; Copy_field *copy_field; enum enum_duplicates handle_duplicates; bool do_update, trans_safe; --- 1.217/sql/sql_update.cc 2007-05-12 00:18:46 +05:00 +++ 1.218/sql/sql_update.cc 2007-05-29 02:28:24 +05:00 @@ -970,6 +970,7 @@ int multi_update::prepare(List &no List_iterator_fast field_it(*fields); List_iterator_fast value_it(*values); uint i, max_fields; + uint leaves_count= 0; DBUG_ENTER("multi_update::prepare"); thd->count_cuted_fields= CHECK_FIELD_WARN; @@ -1003,6 +1004,7 @@ int multi_update::prepare(List &no { /* TODO: add support of view of join support */ TABLE *table=table_ref->table; + leaves_count++; if (tables_to_update & table->map) { TABLE_LIST *tl= (TABLE_LIST*) thd->memdup((char*) table_ref, @@ -1067,7 +1069,7 @@ int multi_update::prepare(List &no /* Allocate copy fields */ max_fields=0; for (i=0 ; i < table_count ; i++) - set_if_bigger(max_fields, fields_for_table[i]->elements); + set_if_bigger(max_fields, fields_for_table[i]->elements + leaves_count); copy_field= new Copy_field[max_fields]; DBUG_RETURN(thd->is_fatal_error != 0); } @@ -1160,13 +1162,23 @@ multi_update::initialize_tables(JOIN *jo trans_safe= transactional_tables= main_table->file->has_transactions(); table_to_update= 0; + /* + UPDATE has at least one field&value pair, OTOH only one table fields may + be updated in the updatable VIEW. + For updatable VIEW the first_table_for_update variable is a saved pointer + to the single updatable table. For regular multi-UPDATE this variable + may points to an arbitrary updatable table. + */ + DBUG_ASSERT(fields->elements); + table_map first_table_for_update= + ((Item_field *) fields->head())->field->table->map; + /* Create a temporary table for keys to all tables, except main table */ for (table_ref= update_tables; table_ref; table_ref= table_ref->next_local) { TABLE *table=table_ref->table; uint cnt= table_ref->shared; - Item_field *ifield; - List temp_fields= *fields_for_table[cnt]; + List temp_fields; ORDER group; if (ignore) @@ -1174,13 +1186,28 @@ multi_update::initialize_tables(JOIN *jo if (table == main_table) // First table in join { if (safe_update_on_fly(thd, join->join_tab, table_ref, all_tables, - &temp_fields)) + fields_for_table[cnt])) { table_to_update= main_table; // Update table on the fly continue; } } + if (table->map == first_table_for_update && table_ref->check_option) + { + table_map map= table_ref->check_option->used_tables(); + map&= ~first_table_for_update; + for (TABLE_LIST *tbl_ref =leaves; + map && tbl_ref; + map&= ~tbl_ref->table->map, tbl_ref= tbl_ref->next_leaf) + { + if (!(map & tbl_ref->table->map)) + continue; + if (unupdated_check_opt_tables.push_back(tbl_ref->table)) + DBUG_RETURN(1); + } + } + TMP_TABLE_PARAM *tmp_param= tmp_table_param+cnt; /* @@ -1189,19 +1216,29 @@ multi_update::initialize_tables(JOIN *jo original row so that we can find and update it */ - /* ok to be on stack as this is not referenced outside of this func */ - Field_string offset(table->file->ref_length, 0, "offset", - table, &my_charset_bin); - /* - The field will be converted to varstring when creating tmp table if - table to be updated was created by mysql 4.1. Deny this. - */ - offset.can_alter_field_type= 0; - if (!(ifield= new Item_field(((Field *) &offset)))) - DBUG_RETURN(1); - ifield->maybe_null= 0; - if (temp_fields.push_front(ifield)) - DBUG_RETURN(1); + List_iterator_fast
tbl_it(unupdated_check_opt_tables); + TABLE *tbl= table; + do + { + Field_string *field= new Field_string(tbl->file->ref_length, 0, + tbl->alias, + tbl, &my_charset_bin); + if (!field) + DBUG_RETURN(1); + /* + The field will be converted to varstring when creating tmp table if + table to be updated was created by mysql 4.1. Deny this. + */ + field->can_alter_field_type= 0; + Item_field *ifield= new Item_field((Field *) field); + if (!ifield) + DBUG_RETURN(1); + ifield->maybe_null= 0; + if (temp_fields.push_back(ifield)) + DBUG_RETURN(1); + } while ((tbl= tbl_it++)); + + temp_fields.concat(fields_for_table[cnt]); /* Make an unique key over the first field to avoid duplicated updates */ bzero((char*) &group, sizeof(group)); @@ -1350,10 +1387,30 @@ bool multi_update::send_data(List { int error; TABLE *tmp_table= tmp_tables[offset]; - fill_record(thd, tmp_table->field+1, *values_for_table[offset], 1); - /* Store pointer to row */ - memcpy((char*) tmp_table->field[0]->ptr, - (char*) table->file->ref, table->file->ref_length); + /* + For updatable VIEW: store rowid of the updating table and + rowids of tables used in CHECK OPTION. + */ + uint field_num= 0; + List_iterator_fast
tbl_it(unupdated_check_opt_tables); + TABLE *tbl= table; + do + { + if (tbl != table) + tbl->file->position(tbl->record[0]); + memcpy((char*) tmp_table->field[field_num]->ptr, + (char*) tbl->file->ref, tbl->file->ref_length); + field_num++; + } while ((tbl= tbl_it++)); + + /* Store regular updated fields to row */ + List_iterator_fast values_it(*values_for_table[offset]); + while (Item_field *value= (Item_field *) values_it++) + { + value->save_org_in_field(tmp_table->field[field_num]); + field_num++; + } + /* Write row, ignoring duplicated updates to a row */ error= tmp_table->file->write_row(tmp_table->record[0]); if (error != HA_ERR_FOUND_DUPP_KEY && error != HA_ERR_FOUND_DUPP_UNIQUE) @@ -1403,9 +1460,10 @@ void multi_update::send_error(uint errco int multi_update::do_updates(bool from_send_error) { TABLE_LIST *cur_table; - int local_error; + int local_error= 0; ha_rows org_updated; TABLE *table, *tmp_table; + List_iterator_fast
check_opt_it(unupdated_check_opt_tables); DBUG_ENTER("do_updates"); do_update= 0; // Don't retry this function @@ -1413,8 +1471,8 @@ int multi_update::do_updates(bool from_s DBUG_RETURN(0); for (cur_table= update_tables; cur_table; cur_table= cur_table->next_local) { - byte *ref_pos; bool can_compare_record; + uint offset= cur_table->shared; table = cur_table->table; if (table == table_to_update) @@ -1425,11 +1483,20 @@ int multi_update::do_updates(bool from_s (void) table->file->ha_rnd_init(0); table->file->extra(HA_EXTRA_NO_CACHE); + check_opt_it.rewind(); + while(TABLE *tbl= check_opt_it++) + { + if (tbl->file->ha_rnd_init(1)) + goto err; + tbl->file->extra(HA_EXTRA_CACHE); + } + /* Setup copy functions to copy fields from temporary table */ - List_iterator_fast field_it(*fields_for_table[cur_table->shared]); - Field **field= tmp_table->field+1; // Skip row pointer + List_iterator_fast field_it(*fields_for_table[offset]); + Field **field= tmp_table->field + + 1 + unupdated_check_opt_tables.elements; // Skip row pointers Copy_field *copy_field_ptr= copy_field, *copy_field_end; for ( ; *field ; field++) { @@ -1444,7 +1511,6 @@ int multi_update::do_updates(bool from_s can_compare_record= !(table->file->table_flags() & HA_PARTIAL_COLUMN_READ); - ref_pos= (byte*) tmp_table->field[0]->ptr; for (;;) { if (thd->killed && trans_safe) @@ -1457,8 +1523,19 @@ int multi_update::do_updates(bool from_s continue; // May happen on dup key goto err; } - if ((local_error= table->file->rnd_pos(table->record[0], ref_pos))) - goto err; + + /* call rnd_pos() using rowids from temporary table */ + check_opt_it.rewind(); + TABLE *tbl= table; + uint field_num= 0; + do + { + if((local_error= tbl->file->rnd_pos(tbl->record[0], + tmp_table->field[field_num]->ptr))) + goto err; + field_num++; + } while((tbl= check_opt_it++)); + table->status|= STATUS_UPDATED; store_record(table,record[1]); @@ -1508,6 +1585,10 @@ int multi_update::do_updates(bool from_s } (void) table->file->ha_rnd_end(); (void) tmp_table->file->ha_rnd_end(); + check_opt_it.rewind(); + while (TABLE *tbl= check_opt_it++) + tbl->file->ha_rnd_end(); + } DBUG_RETURN(0); @@ -1521,6 +1602,9 @@ err: err2: (void) table->file->ha_rnd_end(); (void) tmp_table->file->ha_rnd_end(); + check_opt_it.rewind(); + while (TABLE *tbl= check_opt_it++) + tbl->file->ha_rnd_end(); if (updated != org_updated) { --- 1.200/mysql-test/r/view.result 2007-05-10 00:17:20 +05:00 +++ 1.201/mysql-test/r/view.result 2007-05-29 02:28:47 +05:00 @@ -3367,4 +3367,21 @@ SHOW CREATE VIEW v1; View Create View v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(1.23456789 as decimal(8,0)) AS `col` DROP VIEW v1; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT, c INT DEFAULT 0); +INSERT INTO t1 (a) VALUES (1), (2); +INSERT INTO t2 (b) VALUES (1), (2); +CREATE VIEW v1 AS SELECT t2.b,t2.c FROM t1, t2 +WHERE t1.a=t2.b AND t2.b < 3 WITH CHECK OPTION; +SELECT * FROM v1; +b c +1 0 +2 0 +UPDATE v1 SET c=1 WHERE b=1; +SELECT * FROM v1; +b c +1 1 +2 0 +DROP VIEW v1; +DROP TABLE t1,t2; End of 5.0 tests. --- 1.183/mysql-test/t/view.test 2007-05-10 00:17:20 +05:00 +++ 1.184/mysql-test/t/view.test 2007-05-29 02:28:45 +05:00 @@ -3233,4 +3233,20 @@ CREATE VIEW v1 AS SELECT CAST(1.23456789 SHOW CREATE VIEW v1; DROP VIEW v1; +# +# Bug #28716: CHECK OPTION expression is evaluated over expired record buffers +# when VIEW is updatable via temporary tables +# +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT, c INT DEFAULT 0); +INSERT INTO t1 (a) VALUES (1), (2); +INSERT INTO t2 (b) VALUES (1), (2); +CREATE VIEW v1 AS SELECT t2.b,t2.c FROM t1, t2 + WHERE t1.a=t2.b AND t2.b < 3 WITH CHECK OPTION; +SELECT * FROM v1; +UPDATE v1 SET c=1 WHERE b=1; +SELECT * FROM v1; +DROP VIEW v1; +DROP TABLE t1,t2; + --echo End of 5.0 tests.