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-25 23:19:08+05:00, gshchepa@stripped +6 -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.
Also it was optimized to prevent unnecessary rebuilding
of check_option expression on every EXECUTE statement.
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-25 23:17:26+05:00, gshchepa@stripped +123 -0
Updated test case for bug #27827.
mysql-test/t/view.test@stripped, 2007-05-25 23:17:04+05:00, gshchepa@stripped +96 -0
Updated test case for bug #27827.
sql/sql_class.h@stripped, 2007-05-25 23:11:18+05:00, gshchepa@stripped +11 -0
Fixed bug #27827.
The multi_update::ref_keys_for_table variable has been added.
sql/sql_update.cc@stripped, 2007-05-25 23:11:35+05:00, gshchepa@stripped +122 -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-25 23:11:37+05:00, gshchepa@stripped +94 -15
Fixed bug #27827.
The st_table_list::prep_check_option() function has been improved
to include ON expression into check_option.
Also it was optimized to prevent unnecessary rebuilding
of check_option expression on every EXECUTE statement.
sql/table.h@stripped, 2007-05-25 23:11:30+05:00, gshchepa@stripped +1 -0
Fixed bug #27827.
Added st_table_list::prepd_check_option variable to hold
prepared check_option expression.
# 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-fresh
--- 1.331/sql/sql_class.h 2007-05-15 14:56:04 +05:00
+++ 1.332/sql/sql_class.h 2007-05-25 23:11:18 +05:00
@@ -2283,6 +2283,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.217/sql/sql_update.cc 2007-05-12 00:18:46 +05:00
+++ 1.218/sql/sql_update.cc 2007-05-25 23:11:35 +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,23 @@ multi_update::initialize_tables(JOIN *jo
}
}
+ 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)
+ {
+ 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 +1251,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 +1404,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 +1481,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 +1491,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 +1502,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 +1556,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 +1608,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 +1625,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-25 23:11:37 +05:00
@@ -1986,6 +1986,59 @@ bool st_table_list::prep_where(THD *thd,
/*
+ Collect and AND underlying ON expression.
+
+ SYNOPSIS
+ thd - thread handler
+ table - pointer to VIEW
+ cascade - collect ON expressions of underlying VIEWs or not
+
+ RETURN
+ Pointer to the newly allocated Item.
+*/
+
+static Item *
+collect_on_exprs(THD *thd, TABLE_LIST *table, bool cascade)
+{
+ DBUG_ENTER("collect_on_exprs");
+
+ Item *ret= NULL;
+ DBUG_PRINT("info", ("alias: %s", table->alias));
+ if (table->on_expr)
+ {
+#ifndef DBUG_OFF
+ String s;
+ table->on_expr->print(&s);
+ DBUG_PRINT("info", ("on_expr: [%s]", s.c_ptr()));
+#endif
+ ret= table->on_expr->copy_andor_structure(thd);
+ }
+ if (table->nested_join && (cascade || !table->embedding || !table->view))
+ {
+ List_iterator<TABLE_LIST> li(table->nested_join->join_list);
+ while (TABLE_LIST *tbl= li++)
+ ret= and_conds(ret, collect_on_exprs(thd, tbl, cascade));
+ }
+ DBUG_RETURN(ret);
+}
+
+
+static bool
+fix_check_option_fields(THD *thd, TABLE_LIST *table)
+{
+ DBUG_ENTER("fix_check_option_fields");
+ if (table->check_option &&
+ !table->check_option->fixed &&
+ table->check_option->fix_fields(thd, &table->check_option) ||
+ table->check_option->check_cols(1))
+ {
+ DBUG_RETURN(TRUE);
+ }
+ DBUG_RETURN(FALSE);
+}
+
+
+/*
Prepare check option expression of table
SYNOPSIS
@@ -2014,13 +2067,24 @@ bool st_table_list::prep_where(THD *thd,
bool st_table_list::prep_check_option(THD *thd, uint8 check_opt_type)
{
DBUG_ENTER("st_table_list::prep_check_option");
+ bool cascade= check_opt_type == VIEW_CHECK_CASCADED;
+
+ if (check_option)
+ DBUG_RETURN(fix_check_option_fields(thd, this));
+
+ if (prepd_check_option)
+ {
+ thd->change_item_tree(&check_option,
+ prepd_check_option->copy_andor_structure(thd));
+ DBUG_RETURN(fix_check_option_fields(thd, this));
+ }
for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local)
{
/* see comment of check_opt_type parameter */
if (tbl->view &&
tbl->prep_check_option(thd,
- ((check_opt_type == VIEW_CHECK_CASCADED) ?
+ (cascade ?
VIEW_CHECK_CASCADED :
VIEW_CHECK_NONE)))
{
@@ -2030,35 +2094,50 @@ bool st_table_list::prep_check_option(TH
if (check_opt_type)
{
+ /*
+ The following code will allocate the new items in a permanent
+ MEMROOT for prepared statements and stored procedures.
+ */
+
+ Query_arena *arena= thd->stmt_arena, backup;
+ if (arena->is_conventional())
+ arena= 0;
+ else
+ thd->set_n_backup_active_arena(arena, &backup);
+
Item *item= 0;
if (where)
{
DBUG_ASSERT(where->fixed);
item= where->copy_andor_structure(thd);
}
- if (check_opt_type == VIEW_CHECK_CASCADED)
+ if (cascade)
{
for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local)
{
if (tbl->check_option)
- item= and_conds(item, tbl->check_option);
+ item= and_conds(item, tbl->check_option->copy_andor_structure(thd));
}
}
+ item= and_conds(item, collect_on_exprs(thd, this, cascade));
+
if (item)
- thd->change_item_tree(&check_option, item);
+ prepd_check_option= item;
+
+ if (arena)
+ thd->restore_active_arena(arena, &backup);
}
- if (check_option)
+ if (prepd_check_option)
{
- const char *save_where= thd->where;
- thd->where= "check option";
- if (!check_option->fixed &&
- check_option->fix_fields(thd, &check_option) ||
- check_option->check_cols(1))
- {
- DBUG_RETURN(TRUE);
- }
- thd->where= save_where;
+#ifndef DBUG_OFF
+ String s;
+ prepd_check_option->print(&s);
+ DBUG_PRINT("info", ("check_option: %s", s.c_ptr()));
+#endif
+ thd->change_item_tree(&check_option,
+ prepd_check_option->copy_andor_structure(thd));
+ DBUG_RETURN(fix_check_option_fields(thd, this));
}
DBUG_RETURN(FALSE);
}
@@ -2150,7 +2229,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.143/sql/table.h 2007-05-22 19:05:31 +05:00
+++ 1.144/sql/table.h 2007-05-25 23:11:30 +05:00
@@ -636,6 +636,7 @@ typedef struct st_table_list
st_table_list *next_leaf;
Item *where; /* VIEW WHERE clause condition */
Item *check_option; /* WITH CHECK OPTION condition */
+ Item *prepd_check_option; /* prepared WITH CHECK OPTION condition */
LEX_STRING query; /* text of (CRETE/SELECT) statement */
LEX_STRING md5; /* md5 of query text */
LEX_STRING source; /* source of CREATE VIEW */
--- 1.200/mysql-test/r/view.result 2007-05-10 00:17:20 +05:00
+++ 1.201/mysql-test/r/view.result 2007-05-25 23:17:26 +05:00
@@ -3367,4 +3367,127 @@ 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);
+CREATE TABLE t4 (a4 INT);
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (1),(2);
+INSERT INTO t3 VALUES (1),(2);
+INSERT INTO t4 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 t4.a4 AS a
+FROM (t1 JOIN t2 ON t1.a1=t2.a2)
+JOIN (t3 JOIN t4 ON t3.a3=t4.a4)
+ON t2.a2=t3.a3 WHERE t4.a4 < 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-25 23:17:04 +05:00
@@ -3233,4 +3233,100 @@ 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);
+CREATE TABLE t4 (a4 INT);
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (1),(2);
+INSERT INTO t3 VALUES (1),(2);
+INSERT INTO t4 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 t4.a4 AS a
+ FROM (t1 JOIN t2 ON t1.a1=t2.a2)
+ JOIN (t3 JOIN t4 ON t3.a3=t4.a4)
+ ON t2.a2=t3.a3 WHERE t4.a4 < 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.
| Thread |
|---|
| • bk commit into 5.0 tree (gshchepa:1.2501) BUG#27827 | gshchepa | 25 May |
