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-18 05:54:56+05:00, gshchepa@stripped +8 -0
  Fixed bug #27827.
  ON expressions was not included into CHECK OPTION checks 
  for UPDATE and INSERT statements.
  CHECK OPTION was checked too late for non-driving table
  UPDATE calls.
  
  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 between EXECUTE calls.
  The view_check_option() check has been moved from
  multi_update::do_updates (too late to check here)
  to multi_update::send_data().

  mysql-test/r/view.result@stripped, 2007-05-18 05:53:28+05:00, gshchepa@stripped +103 -0
    Updated test case for bug #27827.

  mysql-test/t/view.test@stripped, 2007-05-18 05:53:14+05:00, gshchepa@stripped +74 -0
    Updated test case for bug #27827.

  sql/mysql_priv.h@stripped, 2007-05-18 05:14:01+05:00, gshchepa@stripped +2 -0
    Fixed bug #27827.
    The fill_record() function (one of) has been moved from static
    to extern scope.

  sql/sql_base.cc@stripped, 2007-05-18 05:16:57+05:00, gshchepa@stripped +1 -1
    Fixed bug #27827.
    The fill_record() function (one of) moved from static to extern scope.

  sql/sql_select.cc@stripped, 2007-05-18 05:17:28+05:00, gshchepa@stripped +1 -0
    The save_on_expr variable used for saving of ON expressions between
    EXECUTE calls.

  sql/sql_update.cc@stripped, 2007-05-18 05:21:21+05:00, gshchepa@stripped +12 -9
    Fixed bug #27827.
    The view_check_option() check has been moved from multi_update::do_updates
    (too late to check here) to multi_update::send_data().

  sql/table.cc@stripped, 2007-05-18 05:24:35+05:00, gshchepa@stripped +20 -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-18 05:15:29+05:00, gshchepa@stripped +8 -0
    Fixed bug #27827.
    The save_on_expr variable 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.448/sql/mysql_priv.h	2007-05-07 16:08:26 +05:00
+++ 1.449/sql/mysql_priv.h	2007-05-18 05:14:01 +05:00
@@ -1122,6 +1122,8 @@ bool remove_table_from_cache(THD *thd, c
 
 bool close_cached_tables(THD *thd, bool wait_for_refresh, TABLE_LIST *tables);
 void copy_field_from_tmp_record(Field *field,int offset);
+bool fill_record(THD * thd, List<Item> &fields, List<Item> &values,
+                 bool ignore_errors);
 bool fill_record(THD *thd, Field **field, List<Item> &values,
                  bool ignore_errors);
 bool fill_record_n_invoke_before_triggers(THD *thd, List<Item> &fields,

--- 1.377/sql/sql_base.cc	2007-04-24 23:34:26 +05:00
+++ 1.378/sql/sql_base.cc	2007-05-18 05:16:57 +05:00
@@ -5311,7 +5311,7 @@ err_no_arena:
     TRUE    error occured
 */
 
-static bool
+bool
 fill_record(THD * thd, List<Item> &fields, List<Item> &values,
             bool ignore_errors)
 {

--- 1.521/sql/sql_select.cc	2007-05-16 01:16:08 +05:00
+++ 1.522/sql/sql_select.cc	2007-05-18 05:17:28 +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-18 05:21:21 +05:00
@@ -1349,6 +1349,18 @@ bool multi_update::send_data(List<Item> 
     else
     {
       int error;
+      if (cur_table->check_option)
+      {
+        store_record(table, record[1]);
+        fill_record(thd, *fields_for_table[offset], *values_for_table[offset], 0);
+        error= cur_table->view_check_option(thd, ignore);
+        restore_record(table, record[1]);
+        if (error == VIEW_CHECK_SKIP)
+          continue;
+        else if (error == VIEW_CHECK_ERROR)
+          DBUG_RETURN(1);
+        DBUG_ASSERT(error == VIEW_CHECK_OK);
+      }
       TABLE *tmp_table= tmp_tables[offset];
       fill_record(thd, tmp_table->field+1, *values_for_table[offset], 1);
       /* Store pointer to row */
@@ -1475,15 +1487,6 @@ int multi_update::do_updates(bool from_s
 
       if (!can_compare_record || compare_record(table, thd->query_id))
       {
-        int error;
-        if ((error= cur_table->view_check_option(thd, ignore)) !=
-            VIEW_CHECK_OK)
-        {
-          if (error == VIEW_CHECK_SKIP)
-            continue;
-          else if (error == VIEW_CHECK_ERROR)
-            goto err;
-        }
 	if ((local_error=table->file->update_row(table->record[1],
 						 table->record[0])))
 	{

--- 1.250/sql/table.cc	2007-04-24 23:34:50 +05:00
+++ 1.251/sql/table.cc	2007-05-18 05:24:35 +05:00
@@ -2040,8 +2040,27 @@ bool st_table_list::prep_check_option(TH
     {
       for (TABLE_LIST *tbl= merge_underlying_list; tbl; tbl= tbl->next_local)
       {
-        if (tbl->check_option)
+        if (tbl->view && tbl->check_option)
           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= table->on_expr ? table->on_expr : table->save_on_expr;
+        if (on)
+          item= and_conds(item, on->copy_andor_structure(thd));
       }
     }
     if (item)

--- 1.141/sql/table.h	2007-03-31 15:36:46 +05:00
+++ 1.142/sql/table.h	2007-05-18 05:15:29 +05:00
@@ -481,6 +481,14 @@ 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).
+  */
+  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-18 05:53:28 +05:00
@@ -3367,4 +3367,107 @@ 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;
 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-18 05:53:14 +05:00
@@ -3233,4 +3233,78 @@ 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 check is too late for non-driven table UPDATES.
+#
+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;
+--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;
+
+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;
+
+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;
+
 --echo End of 5.0 tests.