bk commit into 5.0 tree (gshchepa:1.2501) BUG#28716

[<gshchepa]

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 <Item> *fields, *values;
   List <Item> **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 <TABLE> 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<Item> &no
   List_iterator_fast<Item> field_it(*fields);
   List_iterator_fast<Item> 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<Item> &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<Item> &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<Item> temp_fields= *fields_for_table[cnt];
+    List<Item> 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<TABLE> 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<Item> 
     {
       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<TABLE> 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<Item> 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<TABLE> 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<Item> field_it(*fields_for_table[cur_table->shared]);
-    Field **field= tmp_table->field+1;		// Skip row pointer
+    List_iterator_fast<Item> 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.