bk commit into 5.0 tree (gshchepa:1.2491) BUG#27827

[<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-23 19:57:37+05:00, gshchepa@stripped +9 -0
  Fixed bug #27827.
  Actually there are 2 different bugs and bugfixes:
  1. ON expressions was never included into CHECK OPTION checks
  for UPDATE and INSERT statements.
  2. CHECK OPTION expression was checked over expired record
  buffers (with arbitrary data in the fields).
  
  The st_table_list::prep_check_option() function has been
  improved to include ON expression into check_option.
  The save_on_expr variable was used for saving of ON
  expressions for next executions of the query.
  
  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-23 19:55:19+05:00, gshchepa@stripped +120 -0
    Updated test case for bug #27827.

  mysql-test/t/view.test@stripped, 2007-05-23 19:51:03+05:00, gshchepa@stripped +93 -0
    Updated test case for bug #27827.

  sql/item.cc@stripped, 2007-05-23 19:27:27+05:00, gshchepa@stripped +32 -0
    Fixed bug #27827.
    The Item::collect_org_table_processor() virtual function added
    and overloaded in the Item_field class.

  sql/item.h@stripped, 2007-05-23 19:25:52+05:00, gshchepa@stripped +2 -0
    Fixed bug #27827.
    The Item::collect_org_table_processor() virtual function added
    and overloaded in the Item_field class.

  sql/sql_class.h@stripped, 2007-05-23 19:29:30+05:00, gshchepa@stripped +11 -0
    Fixed bug #27827.
    The multi_update::ref_keys_for_table variable has been added.

  sql/sql_select.cc@stripped, 2007-05-23 19:34:54+05:00, gshchepa@stripped +1 -0
    The save_on_expr variable has been used to save ON expressions
    between EXECUTE calls.

  sql/sql_update.cc@stripped, 2007-05-23 19:35:59+05:00, gshchepa@stripped +124 -15
    Fixed bug #27827.
    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.
    

  sql/table.cc@stripped, 2007-05-23 19:34:16+05:00, gshchepa@stripped +31 -1
    Fixed bug #27827.
    The st_table_list::prep_check_option() function has been improved
    to include ON expression into check_option.

  sql/table.h@stripped, 2007-05-23 19:32:52+05:00, gshchepa@stripped +10 -0
    Fixed bug #27827.
    The save_on_expr variable has been added to save ON expressions
    between EXECUTE calls.

# 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-27827

--- 1.266/sql/item.cc	2007-05-04 19:56:50 +05:00
+++ 1.267/sql/item.cc	2007-05-23 19:27:27 +05:00
@@ -604,6 +604,38 @@ bool Item_field::collect_item_field_proc
 
 
 /*
+  Add this field table `map' to the output bitmask.
+
+  SYNOPSIS
+    Item_field::collect_org_table_processor()
+    arg  pointer to table_map
+
+  DESCRIPTION
+    The method is used by Item::walk to collect all original (opposite to
+    result) table maps into single bitmask. 
+
+  IMPLEMENTATION
+    Item_cond::walk() and Item_func::walk() stop the evaluation of the
+    processor function for its arguments once the processor returns
+    true. Therefore in order to force this method being called for all item
+    arguments in a condition the method must return false.
+
+  RETURN
+    FALSE to force the evaluation of collect_org_table_processor()
+          for the subsequent items.
+*/
+
+bool Item_field::collect_org_table_processor(byte *arg)
+{
+  DBUG_ENTER("Item_field::collect_org_table_processor");
+  DBUG_PRINT("info", ("%s", field->field_name ? field->field_name : "noname"));
+  table_map *map= (table_map *) arg;
+  *map|= field->table->map;
+  DBUG_RETURN(FALSE);
+}
+
+
+/*
   Check if an Item_field references some field from a list of fields.
 
   SYNOPSIS

--- 1.229/sql/item.h	2007-05-04 19:56:44 +05:00
+++ 1.230/sql/item.h	2007-05-23 19:25:52 +05:00
@@ -779,6 +779,7 @@ public:
   virtual bool remove_fixed(byte * arg) { fixed= 0; return 0; }
   virtual bool cleanup_processor(byte *arg);
   virtual bool collect_item_field_processor(byte * arg) { return 0; }
+  virtual bool collect_org_table_processor(byte * arg) { return 0; }
   virtual bool find_item_in_field_list_processor(byte *arg) { return 0; }
   virtual bool change_context_processor(byte *context) { return 0; }
   virtual bool reset_query_id_processor(byte *query_id_arg) { return 0; }
@@ -1280,6 +1281,7 @@ public:
   void update_null_value();
   Item *get_tmp_table_item(THD *thd);
   bool collect_item_field_processor(byte * arg);
+  virtual bool collect_org_table_processor(byte * arg);
   bool find_item_in_field_list_processor(byte *arg);
   bool reset_query_id_processor(byte *arg)
   {

--- 1.329/sql/sql_class.h	2007-05-08 00:10:29 +05:00
+++ 1.330/sql/sql_class.h	2007-05-23 19:29:30 +05:00
@@ -2273,6 +2273,17 @@ class multi_update :public select_result
   List <Item> *fields, *values;
   List <Item> **fields_for_table, **values_for_table;
   uint table_count;
+  /*
+   ref_keys_for_table is a list of items describing
+   temporary table fields used to keep rowids of tables
+   necessary for view_check_option() calculation.
+   UPDATE of VIEW can update only one table, so only
+   one temporary table can be created, and only one list
+   of rowids is required.
+   For regular multi-update UPDATE statements this
+   list is empty.
+  */
+  List <Item> ref_keys_for_table;
   Copy_field *copy_field;
   enum enum_duplicates handle_duplicates;
   bool do_update, trans_safe;

--- 1.521/sql/sql_select.cc	2007-05-16 01:16:08 +05:00
+++ 1.522/sql/sql_select.cc	2007-05-23 19:34:54 +05:00
@@ -8147,6 +8147,7 @@ simplify_joins(JOIN *join, List<TABLE_LI
         }
         else
           conds= table->on_expr; 
+        table->save_on_expr= table->on_expr;
         table->prep_on_expr= table->on_expr= 0;
       }
     }

--- 1.217/sql/sql_update.cc	2007-05-12 00:18:46 +05:00
+++ 1.218/sql/sql_update.cc	2007-05-23 19:35:59 +05:00
@@ -1067,7 +1067,8 @@ 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 +
+                              sizeof(table_map) * 8); // max. number of tables
   copy_field= new Copy_field[max_fields];
   DBUG_RETURN(thd->is_fatal_error != 0);
 }
@@ -1140,6 +1141,39 @@ static bool safe_update_on_fly(THD *thd,
 
 
 /*
+ Create Item_field to store rowid as string.
+
+ SYNOPSIS
+   create_ref_key_field()
+   table                Table to obtain rowid info.
+   field_name           Name for the new field.
+
+ RETURN
+   Newly allocated Item_field.
+*/
+
+static Item_field *
+create_ref_key_field(TABLE *table, const char *field_name)
+{
+    Field_string *field= new Field_string(table->file->ref_length, 0,
+                                          field_name,
+                                          table, &my_charset_bin);
+    if (!field)
+      return NULL;
+    /*
+      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)
+      return NULL;
+    ifield->maybe_null= 0;
+    return ifield;
+}
+
+
+/*
   Initialize table for multi table
 
   IMPLEMENTATION
@@ -1160,6 +1194,17 @@ 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)
   {
@@ -1181,6 +1226,25 @@ multi_update::initialize_tables(JOIN *jo
       }
     }
 
+    if (table->map == first_table_for_update && table_ref->check_option)
+    {
+      table_map map= 0;
+      table_ref->check_option->walk(&Item::collect_org_table_processor,
+                                    (byte*) &map);
+      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)
+      {
+        TABLE *tbl= tbl_ref->table;
+        ifield= create_ref_key_field(tbl, tbl->alias);
+        if (!ifield)
+          DBUG_RETURN(1);
+        if (ref_keys_for_table.push_back(ifield))
+          DBUG_RETURN(1);
+      }
+    }
+
     TMP_TABLE_PARAM *tmp_param= tmp_table_param+cnt;
 
     /*
@@ -1189,20 +1253,12 @@ 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;
+    ifield= create_ref_key_field(table, "offset");
     if (temp_fields.push_front(ifield))
       DBUG_RETURN(1);
 
+    temp_fields.concat(&ref_keys_for_table);
+
     /* Make an unique key over the first field to avoid duplicated updates */
     bzero((char*) &group, sizeof(group));
     group.asc= 1;
@@ -1350,10 +1406,31 @@ 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 to row */
+      List_iterator_fast<Item> values_it(*values_for_table[offset]);
+      uint field_num = 1; // skip already stored rowid of updated table
+      for (uint elements = fields_for_table[offset]->elements;
+           field_num <= elements;
+           field_num++)
+      {
+        Item *value= values_it++;
+        if (value->save_in_field(tmp_table->field[field_num], 0) == -1)
+          DBUG_RETURN(1);
+      }
+      /* For updatable VIEW: store rowids of tables used in CHECK OPTION */
+      List_iterator_fast<Item> fi(ref_keys_for_table);
+      while (Item_field *field= (Item_field *) fi++)
+      {
+        TABLE *tbl= field->field->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++;
+      }
+
       /* 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)
@@ -1406,6 +1483,7 @@ int multi_update::do_updates(bool from_s
   int local_error;
   ha_rows org_updated;
   TABLE *table, *tmp_table;
+  List_iterator_fast<Item> ref_keys_it(ref_keys_for_table);
   DBUG_ENTER("do_updates");
 
   do_update= 0;					// Don't retry this function
@@ -1415,6 +1493,7 @@ int multi_update::do_updates(bool from_s
   {
     byte *ref_pos;
     bool can_compare_record;
+    uint offset= cur_table->shared;
 
     table = cur_table->table;
     if (table == table_to_update)
@@ -1425,13 +1504,24 @@ int multi_update::do_updates(bool from_s
     (void) table->file->ha_rnd_init(0);
     table->file->extra(HA_EXTRA_NO_CACHE);
 
+    ref_keys_it.rewind();
+    while (Item_field *item= (Item_field *) ref_keys_it++)
+    {
+      TABLE *tbl= item->field->table;
+      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]);
+    List_iterator_fast<Item> field_it(*fields_for_table[offset]);
     Field **field= tmp_table->field+1;		// Skip row pointer
     Copy_field *copy_field_ptr= copy_field, *copy_field_end;
-    for ( ; *field ; field++)
+    for (uint i= 0, elements= fields_for_table[offset]->elements;
+         *field && i < elements;
+         field++, i++)
     {
       Item_field *item= (Item_field* ) field_it++;
       (copy_field_ptr++)->set(item->field, *field, 0);
@@ -1468,6 +1558,18 @@ int multi_update::do_updates(bool from_s
 	   copy_field_ptr++)
 	(*copy_field_ptr->do_copy)(copy_field_ptr);
 
+      ref_keys_it.rewind();
+      Item_field *ifield;
+      for (uint fn= 1 + fields_for_table[offset]->elements;
+           (ifield= (Item_field *) ref_keys_it++);
+           fn++)
+      {
+        TABLE *tbl= ifield->field->table;
+        if((local_error= tbl->file->rnd_pos(tbl->record[0],
+                                            tmp_table->field[fn]->ptr)))
+          goto err;
+      }
+
       if (table->triggers &&
           table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
                                             TRG_ACTION_BEFORE, TRUE))
@@ -1508,6 +1610,10 @@ int multi_update::do_updates(bool from_s
     }
     (void) table->file->ha_rnd_end();
     (void) tmp_table->file->ha_rnd_end();
+    ref_keys_it.rewind();
+    while (Item_field *item= (Item_field *) ref_keys_it++)
+      item->field->table->file->ha_rnd_end();
+
   }
   DBUG_RETURN(0);
 
@@ -1521,6 +1627,9 @@ err:
 err2:
   (void) table->file->ha_rnd_end();
   (void) tmp_table->file->ha_rnd_end();
+  ref_keys_it.rewind();
+  while (Item_field *item= (Item_field *) ref_keys_it++)
+    item->field->table->file->ha_rnd_end();
 
   if (updated != org_updated)
   {

--- 1.250/sql/table.cc	2007-04-24 23:34:50 +05:00
+++ 1.251/sql/table.cc	2007-05-23 19:34:16 +05:00
@@ -2015,6 +2015,9 @@ bool st_table_list::prep_check_option(TH
 {
   DBUG_ENTER("st_table_list::prep_check_option");
 
+  if (check_option)
+    DBUG_RETURN(FALSE);
+
   for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local)
   {
     /* see comment of check_opt_type parameter */
@@ -2044,6 +2047,33 @@ bool st_table_list::prep_check_option(TH
           item= and_conds(item, tbl->check_option);
       }
     }
+    for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local)
+    {
+      if (!tbl->join_list)
+        continue;
+      List_iterator<TABLE_LIST> li(*tbl->join_list);
+      while (TABLE_LIST *table= li++)
+      {
+        /*
+          The prep_check_option() function may be called either before
+          the simplify_joins() function or after simplify_joins() (from
+          the second call to EXECUTE).
+          The simplify_joins() sets save_on_expr and clears on_expr,
+          thus it is necessary to choose one of them.
+        */
+        Item *on_expr= table->on_expr ? table->on_expr : table->save_on_expr;
+        if (on_expr)
+        {
+          /*
+           merge_underlying_list contains all underlying tables of this
+           VIEW (excluding tables used in subqueries), so we don't need
+           to walk through nested join tree recursively to collect all
+           required ON conditions for CHECK OPTION expression.
+          */
+          item= and_conds(item, on_expr->copy_andor_structure(thd));
+        }
+      }
+    }
     if (item)
       thd->change_item_tree(&check_option, item);
   }
@@ -2150,7 +2180,7 @@ void st_table_list::cleanup_items()
   check CHECK OPTION condition
 
   SYNOPSIS
-    check_option()
+    st_table_list::view_check_option()
     ignore_failure ignore check option fail
 
   RETURN

--- 1.141/sql/table.h	2007-03-31 15:36:46 +05:00
+++ 1.142/sql/table.h	2007-05-23 19:32:52 +05:00
@@ -481,6 +481,16 @@ typedef struct st_table_list
   char		*db, *alias, *table_name, *schema_table_name;
   char          *option;                /* Used by cache index  */
   Item		*on_expr;		/* Used with outer join */
+  /* 
+    Since the on_expr variable is zeroed for inner joins by the
+    simplify_joins() function, it is necessary to save a copy of
+    ON expression for the prep_check_option() function.
+    (prep_check_option() may be called during second EXECUTE call,
+    when on_expr and prep_on_expr are already NULLed).
+    Currently this field can be used only in the
+    st_table_list::prep_check_option() function.
+  */
+  Item          *save_on_expr;
   /*
     The structure of ON expression presented in the member above
     can be changed during certain optimizations. This member

--- 1.200/mysql-test/r/view.result	2007-05-10 00:17:20 +05:00
+++ 1.201/mysql-test/r/view.result	2007-05-23 19:55:19 +05:00
@@ -3367,4 +3367,124 @@ 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 (a1 INT);
+CREATE TABLE t2 (a2 INT);
+CREATE TABLE t3 (a3 INT);
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (1),(2);
+INSERT INTO t3 VALUES (1),(2);
+CREATE VIEW v1 AS SELECT t2.a2 AS a FROM t2
+JOIN t1 ON t1.a1=t2.a2 WHERE t2.a2 < 3 WITH CHECK OPTION;
+SELECT * FROM v1;
+a
+1
+2
+UPDATE v1 SET a=1;
+ERROR HY000: CHECK OPTION failed 'test.v1'
+UPDATE v1 SET a=3;
+ERROR HY000: CHECK OPTION failed 'test.v1'
+PREPARE t FROM 'UPDATE v1 SET a=3';
+EXECUTE t;
+ERROR HY000: CHECK OPTION failed 'test.v1'
+EXECUTE t;
+ERROR HY000: CHECK OPTION failed 'test.v1'
+INSERT INTO v1(a) VALUES (3);
+ERROR HY000: CHECK OPTION failed 'test.v1'
+UPDATE v1 SET a=1 WHERE a=1;
+SELECT * FROM v1;
+a
+1
+2
+SELECT * FROM t1;
+a1
+1
+2
+SELECT * FROM t2;
+a2
+1
+2
+CREATE VIEW v2 AS SELECT t2.a2 AS a FROM t1
+JOIN t2 ON t1.a1=t2.a2 WHERE t2.a2 < 3 WITH CHECK OPTION;
+SELECT * FROM v2;
+a
+1
+2
+UPDATE v2 SET a=1;
+ERROR HY000: CHECK OPTION failed 'test.v2'
+UPDATE v2 SET a=3;
+ERROR HY000: CHECK OPTION failed 'test.v2'
+PREPARE t FROM 'UPDATE v2 SET a=3';
+EXECUTE t;
+ERROR HY000: CHECK OPTION failed 'test.v2'
+EXECUTE t;
+ERROR HY000: CHECK OPTION failed 'test.v2'
+INSERT INTO v2(a) VALUES (3);
+ERROR HY000: CHECK OPTION failed 'test.v2'
+UPDATE v2 SET a=1 WHERE a=1;
+SELECT * FROM v2;
+a
+1
+2
+SELECT * FROM t1;
+a1
+1
+2
+SELECT * FROM t2;
+a2
+1
+2
+CREATE VIEW v3 AS SELECT t3.a3 AS a FROM t1
+JOIN (t2 JOIN t3 ON t2.a2=t3.a3) ON t1.a1=t2.a2
+WHERE t2.a2 < 3 WITH CHECK OPTION;
+SELECT * FROM v3;
+a
+1
+2
+UPDATE v3 SET a=1;
+ERROR HY000: CHECK OPTION failed 'test.v3'
+UPDATE v3 SET a=3;
+ERROR HY000: CHECK OPTION failed 'test.v3'
+PREPARE t FROM 'UPDATE v3 SET a=3';
+EXECUTE t;
+ERROR HY000: CHECK OPTION failed 'test.v3'
+EXECUTE t;
+ERROR HY000: CHECK OPTION failed 'test.v3'
+INSERT INTO v3(a) VALUES (3);
+ERROR HY000: CHECK OPTION failed 'test.v3'
+UPDATE v3 SET a=1 WHERE a=1;
+SELECT * FROM v2;
+a
+1
+2
+SELECT * FROM t1;
+a1
+1
+2
+SELECT * FROM t2;
+a2
+1
+2
+SELECT * FROM t3;
+a3
+1
+2
+DROP VIEW v1,v2,v3;
+DROP TABLE t1,t2,t3;
+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-23 19:51:03 +05:00
@@ -3233,4 +3233,97 @@ CREATE VIEW v1 AS SELECT CAST(1.23456789
 SHOW CREATE VIEW v1;
 DROP VIEW v1;
 
+#
+# Bug #27827: CHECK OPTION ignores ON expressions during UPDATES and
+# inserts, CHECK OPTION expression calculation over expired field
+# data buffers.
+#
+
+# Test case for ON condition:
+CREATE TABLE t1 (a1 INT);
+CREATE TABLE t2 (a2 INT);
+CREATE TABLE t3 (a3 INT);
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (1),(2);
+INSERT INTO t3 VALUES (1),(2);
+
+# UPDATE without using of temporary tables:
+CREATE VIEW v1 AS SELECT t2.a2 AS a FROM t2
+   JOIN t1 ON t1.a1=t2.a2 WHERE t2.a2 < 3 WITH CHECK OPTION;
+SELECT * FROM v1;
+--error 1369
+UPDATE v1 SET a=1;
+--error 1369
+UPDATE v1 SET a=3;
+PREPARE t FROM 'UPDATE v1 SET a=3';
+--error 1369
+EXECUTE t;
+--error 1369
+EXECUTE t;
+--error 1369
+INSERT INTO v1(a) VALUES (3);
+UPDATE v1 SET a=1 WHERE a=1;
+SELECT * FROM v1;
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+# UPDATE via temporary table:
+CREATE VIEW v2 AS SELECT t2.a2 AS a FROM t1
+   JOIN t2 ON t1.a1=t2.a2 WHERE t2.a2 < 3 WITH CHECK OPTION;
+SELECT * FROM v2;
+--error 1369
+UPDATE v2 SET a=1;
+--error 1369
+UPDATE v2 SET a=3;
+PREPARE t FROM 'UPDATE v2 SET a=3';
+--error 1369
+EXECUTE t;
+--error 1369
+EXECUTE t;
+--error 1369
+INSERT INTO v2(a) VALUES (3);
+UPDATE v2 SET a=1 WHERE a=1;
+SELECT * FROM v2;
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+# Nesting joins:
+CREATE VIEW v3 AS SELECT t3.a3 AS a FROM t1
+  JOIN (t2 JOIN t3 ON t2.a2=t3.a3) ON t1.a1=t2.a2
+  WHERE t2.a2 < 3 WITH CHECK OPTION;
+SELECT * FROM v3;
+--error 1369
+UPDATE v3 SET a=1;
+--error 1369
+UPDATE v3 SET a=3;
+PREPARE t FROM 'UPDATE v3 SET a=3';
+--error 1369
+EXECUTE t;
+--error 1369
+EXECUTE t;
+--error 1369
+INSERT INTO v3(a) VALUES (3);
+UPDATE v3 SET a=1 WHERE a=1;
+
+SELECT * FROM v2;
+SELECT * FROM t1;
+SELECT * FROM t2;
+SELECT * FROM t3;
+
+DROP VIEW v1,v2,v3;
+DROP TABLE t1,t2,t3;
+
+# Test case for calculation of CHECK OPTION expr. over expired buffers:
+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.