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 01:05:46+05:00, gshchepa@stripped +4 -0
Fixed bug #27827.
ON expressions was never included into CHECK OPTION checks
for UPDATE and INSERT statements.
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.
mysql-test/r/view.result@stripped, 2007-05-29 01:01:09+05:00, gshchepa@stripped +106 -0
Updated test case for bug #27827.
mysql-test/t/view.test@stripped, 2007-05-29 01:01:07+05:00, gshchepa@stripped +83 -0
Updated test case for bug #27827.
sql/table.cc@stripped, 2007-05-29 01:00:37+05:00, gshchepa@stripped +67 -20
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-29 01:00:24+05:00, gshchepa@stripped +2 -0
Fixed bug #27827.
The st_table_list::check_option_processed variable has beed added
to prevent double building of check_option expression item tree.
# 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.250/sql/table.cc 2007-04-24 23:34:50 +05:00
+++ 1.251/sql/table.cc 2007-05-29 01:00:37 +05:00
@@ -1986,6 +1986,47 @@ bool st_table_list::prep_where(THD *thd,
/*
+ 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
+ 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,
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,51 +2055,57 @@ 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 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)
{
- const char *save_where= thd->where;
- thd->where= "check option";
+#ifndef DBUG_OFF
+ String s;
+ check_option->print(&s);
+ DBUG_PRINT("info", ("check_option: %s", s.c_ptr()));
+#endif
if (!check_option->fixed &&
check_option->fix_fields(thd, &check_option) ||
check_option->check_cols(1))
{
DBUG_RETURN(TRUE);
}
- thd->where= save_where;
}
DBUG_RETURN(FALSE);
}
@@ -2150,7 +2197,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-29 01:00:24 +05:00
@@ -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) */
--- 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 01:01:09 +05:00
@@ -3367,4 +3367,110 @@ 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;
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 01:01:07 +05:00
@@ -3233,4 +3233,87 @@ 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;
+
--echo End of 5.0 tests.
| Thread |
|---|
| • bk commit into 5.0 tree (gshchepa:1.2501) BUG#27827 | gshchepa | 28 May |
