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-30 12:21:39+05:00, gshchepa@stripped +4 -0
  Fixed bug #28716.
  The result of the CHECK OPTION condition evaluation over an
  updated record and records of merged tables was arbitrary and
  dependant on the order of records in the merged tables during
  the execution of SELECT statement.
  
  The CHECK OPTION expression was evaluated over expired record
  buffers (with arbitrary data in the fields).
  
  Rowids of tables used in the CHECK OPTION expression were
  added to temporary table rows. The multi_update::do_updates()
  method was modified to restore necessary record buffers
  before evaluation of the CHECK OPTION condition.

  mysql-test/r/view.result@stripped, 2007-05-30 12:21:12+05:00, gshchepa@stripped +17 -0
    Updated test case for bug #28716.

  mysql-test/t/view.test@stripped, 2007-05-30 12:21:09+05:00, gshchepa@stripped +16 -0
    Updated test case for bug #28716.

  sql/sql_class.h@stripped, 2007-05-30 12:18:49+05:00, gshchepa@stripped +5 -0
    Fixed bug #29716.
    The multi_update::unupdatable_check_opt_tables variable
    has been added.

  sql/sql_update.cc@stripped, 2007-05-30 12:18:58+05:00, gshchepa@stripped +112 -29
    Fixed bug #29716.
    Rowids of tables used in the CHECK OPTION expression were
    added to temporary table rows. The multi_update::do_updates()
    method was modified to restore necessary record buffers
    before evaluation of the CHECK OPTION condition.

# 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-30 12:18:49 +05:00
@@ -2283,6 +2283,11 @@ class multi_update :public select_result
   List <Item> *fields, *values;
   List <Item> **fields_for_table, **values_for_table;
   uint table_count;
+  /*
+   List of tables referenced in the CHECK OPTION condition of
+   the updated view excluding the updated table. 
+  */
+  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-30 12:18:58 +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 leaf_table_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;
+    leaf_table_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 + leaf_table_count);
   copy_field= new Copy_field[max_fields];
   DBUG_RETURN(thd->is_fatal_error != 0);
 }
@@ -1160,13 +1162,22 @@ multi_update::initialize_tables(JOIN *jo
   trans_safe= transactional_tables= main_table->file->has_transactions();
   table_to_update= 0;
 
+  /* Any update has at least one pair (field, value) */
+  DBUG_ASSERT(fields->elements);
+  /*
+   Only one table may be modified by UPDATE of an updatable view.
+   For an updatable view first_table_for_update indicates this
+   table.
+   For a regular multi-update it refers to some updated table.
+  */ 
+  TABLE *first_table_for_update= ((Item_field *) fields->head())->field->table;
+
   /* 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,34 +1185,63 @@ 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 == first_table_for_update && table_ref->check_option)
+    {
+      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)
+      {
+        if (unupdated_tables & tbl_ref->table->map)
+          unupdated_tables&= ~tbl_ref->table->map;
+        else
+          continue;
+        if (unupdated_check_opt_tables.push_back(tbl_ref->table))
+          DBUG_RETURN(1);
+      }
+    }
+
     TMP_TABLE_PARAM *tmp_param= tmp_table_param+cnt;
 
     /*
       Create a temporary table to store all fields that are changed for this
       table. The first field in the temporary table is a pointer to the
-      original row so that we can find and update it
+      original row so that we can find and update it. For the updatable
+      VIEW a few following fields are rowids of tables used in the CHECK
+      OPTION condition.
     */
 
-    /* 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 +1390,26 @@ 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);
+      /* Store regular updated fields in the row. */
+      fill_record(thd,
+                  tmp_table->field + 1 + unupdated_check_opt_tables.elements,
+                  *values_for_table[offset], 1);
+      /*
+       For updatable VIEW store rowid of the updated table and
+       rowids of tables used in the CHECK OPTION condition.
+      */
+      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++));
+
       /* 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 +1459,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 +1470,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 +1482,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 +1510,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 +1522,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 +1584,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 +1601,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-30 12:21:12 +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-30 12:21:09 +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 updated 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.