diff options
| -rw-r--r-- | mysql-test/r/view.result | 61 | ||||
| -rw-r--r-- | mysql-test/t/view.test | 52 | ||||
| -rw-r--r-- | sql/table.cc | 79 | ||||
| -rw-r--r-- | sql/table.h | 2 |
4 files changed, 177 insertions, 17 deletions
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 8d9d802949d..c31f6a4f8eb 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -3367,4 +3367,65 @@ 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, c INT DEFAULT 0); +CREATE TABLE t2 (a2 INT); +CREATE TABLE t3 (a3 INT); +CREATE TABLE t4 (a4 INT); +INSERT INTO t1 (a1) VALUES (1),(2); +INSERT INTO t2 (a2) VALUES (1),(2); +INSERT INTO t3 (a3) VALUES (1),(2); +INSERT INTO t4 (a4) VALUES (1),(2); +CREATE VIEW v1 AS +SELECT t1.a1, t1.c FROM t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3 +WITH CHECK OPTION; +SELECT * FROM v1; +a1 c +1 0 +2 0 +UPDATE v1 SET c=3; +ERROR HY000: CHECK OPTION failed 'test.v1' +PREPARE t FROM 'UPDATE v1 SET c=3'; +EXECUTE t; +ERROR HY000: CHECK OPTION failed 'test.v1' +EXECUTE t; +ERROR HY000: CHECK OPTION failed 'test.v1' +INSERT INTO v1(a1, c) VALUES (3, 3); +ERROR HY000: CHECK OPTION failed 'test.v1' +UPDATE v1 SET c=1 WHERE a1=1; +SELECT * FROM v1; +a1 c +1 1 +2 0 +SELECT * FROM t1; +a1 c +1 1 +2 0 +CREATE VIEW v2 AS SELECT t1.a1, t1.c +FROM (t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3) +JOIN (t3 JOIN t4 ON t3.a3=t4.a4) +ON t2.a2=t3.a3 WITH CHECK OPTION; +SELECT * FROM v2; +a1 c +1 1 +2 0 +UPDATE v2 SET c=3; +ERROR HY000: CHECK OPTION failed 'test.v2' +PREPARE t FROM 'UPDATE v2 SET c=3'; +EXECUTE t; +ERROR HY000: CHECK OPTION failed 'test.v2' +EXECUTE t; +ERROR HY000: CHECK OPTION failed 'test.v2' +INSERT INTO v2(a1, c) VALUES (3, 3); +ERROR HY000: CHECK OPTION failed 'test.v2' +UPDATE v2 SET c=2 WHERE a1=1; +SELECT * FROM v2; +a1 c +1 2 +2 0 +SELECT * FROM t1; +a1 c +1 2 +2 0 +DROP VIEW v1,v2; +DROP TABLE t1,t2,t3,t4; End of 5.0 tests. diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 3275ba0a687..60b10de01f0 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -3233,4 +3233,56 @@ CREATE VIEW v1 AS SELECT CAST(1.23456789 AS DECIMAL(8,0)) AS col; SHOW CREATE VIEW v1; DROP VIEW v1; +# +# Bug #27827: CHECK OPTION ignores ON conditions when updating +# a multi-table view with CHECK OPTION. +# + +CREATE TABLE t1 (a1 INT, c INT DEFAULT 0); +CREATE TABLE t2 (a2 INT); +CREATE TABLE t3 (a3 INT); +CREATE TABLE t4 (a4 INT); +INSERT INTO t1 (a1) VALUES (1),(2); +INSERT INTO t2 (a2) VALUES (1),(2); +INSERT INTO t3 (a3) VALUES (1),(2); +INSERT INTO t4 (a4) VALUES (1),(2); + +CREATE VIEW v1 AS + SELECT t1.a1, t1.c FROM t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3 + WITH CHECK OPTION; +SELECT * FROM v1; +--error 1369 +UPDATE v1 SET c=3; +PREPARE t FROM 'UPDATE v1 SET c=3'; +--error 1369 +EXECUTE t; +--error 1369 +EXECUTE t; +--error 1369 +INSERT INTO v1(a1, c) VALUES (3, 3); +UPDATE v1 SET c=1 WHERE a1=1; +SELECT * FROM v1; +SELECT * FROM t1; + +CREATE VIEW v2 AS SELECT t1.a1, t1.c + FROM (t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3) + JOIN (t3 JOIN t4 ON t3.a3=t4.a4) + ON t2.a2=t3.a3 WITH CHECK OPTION; +SELECT * FROM v2; +--error 1369 +UPDATE v2 SET c=3; +PREPARE t FROM 'UPDATE v2 SET c=3'; +--error 1369 +EXECUTE t; +--error 1369 +EXECUTE t; +--error 1369 +INSERT INTO v2(a1, c) VALUES (3, 3); +UPDATE v2 SET c=2 WHERE a1=1; +SELECT * FROM v2; +SELECT * FROM t1; + +DROP VIEW v1,v2; +DROP TABLE t1,t2,t3,t4; + --echo End of 5.0 tests. diff --git a/sql/table.cc b/sql/table.cc index fb2012a79d1..9868aa9b9c1 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -1986,6 +1986,47 @@ bool st_table_list::prep_where(THD *thd, Item **conds, /* + Merge ON expressions for a view + + SYNOPSIS + merge_on_conds() + thd thread handle + table table for the VIEW + is_cascaded TRUE <=> merge ON expressions from underlying views + + DESCRIPTION + This function returns the result of ANDing the ON expressions + of the given view and all underlying views. The ON expressions + of the underlying views are added only if is_cascaded is TRUE. + + RETURN + Pointer to the built expression if there is any. + Otherwise and in the case of a failure NULL is returned. +*/ + +static Item * +merge_on_conds(THD *thd, TABLE_LIST *table, bool is_cascaded) +{ + DBUG_ENTER("merge_on_conds"); + + Item *cond= NULL; + DBUG_PRINT("info", ("alias: %s", table->alias)); + if (table->on_expr) + cond= table->on_expr->copy_andor_structure(thd); + if (!table->nested_join) + DBUG_RETURN(cond); + List_iterator<TABLE_LIST> li(table->nested_join->join_list); + while (TABLE_LIST *tbl= li++) + { + if (tbl->view && !is_cascaded) + continue; + cond= and_conds(cond, merge_on_conds(thd, tbl, is_cascaded)); + } + DBUG_RETURN(cond); +} + + +/* Prepare check option expression of table SYNOPSIS @@ -2001,8 +2042,8 @@ bool st_table_list::prep_where(THD *thd, Item **conds, VIEW_CHECK_LOCAL option. NOTE - This method build check options for every call - (usual execution or every SP/PS call) + This method builds check option condition to use it later on + every call (usual execution or every SP/PS call). This method have to be called after WHERE preparation (st_table_list::prep_where) @@ -2014,38 +2055,42 @@ bool st_table_list::prep_where(THD *thd, Item **conds, bool st_table_list::prep_check_option(THD *thd, uint8 check_opt_type) { DBUG_ENTER("st_table_list::prep_check_option"); + bool is_cascaded= check_opt_type == VIEW_CHECK_CASCADED; 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) ? - VIEW_CHECK_CASCADED : - VIEW_CHECK_NONE))) - { + if (tbl->view && tbl->prep_check_option(thd, (is_cascaded ? + VIEW_CHECK_CASCADED : + VIEW_CHECK_NONE))) DBUG_RETURN(TRUE); - } } - if (check_opt_type) + if (check_opt_type && !check_option_processed) { - Item *item= 0; + Query_arena *arena= thd->stmt_arena, backup; + arena= thd->activate_stmt_arena_if_needed(&backup); // For easier test + if (where) { DBUG_ASSERT(where->fixed); - item= where->copy_andor_structure(thd); + check_option= where->copy_andor_structure(thd); } - if (check_opt_type == VIEW_CHECK_CASCADED) + if (is_cascaded) { for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local) { if (tbl->check_option) - item= and_conds(item, tbl->check_option); + check_option= and_conds(check_option, tbl->check_option); } } - if (item) - thd->change_item_tree(&check_option, item); + check_option= and_conds(check_option, + merge_on_conds(thd, this, is_cascaded)); + + if (arena) + thd->restore_active_arena(arena, &backup); + check_option_processed= TRUE; + } if (check_option) @@ -2150,7 +2195,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 diff --git a/sql/table.h b/sql/table.h index 952b575c00e..d4dc3e8c0b8 100644 --- a/sql/table.h +++ b/sql/table.h @@ -685,6 +685,8 @@ typedef struct st_table_list bool compact_view_format; /* Use compact format for SHOW CREATE VIEW */ /* view where processed */ bool where_processed; + /* TRUE <=> VIEW CHECK OPTION expression has been processed */ + bool check_option_processed; /* FRMTYPE_ERROR if any type is acceptable */ enum frm_type_enum required_type; char timestamp_buffer[20]; /* buffer for timestamp (19+1) */ |