bk commit into 5.0 tree (kroki:1.2116) BUG#14635

[kroki]

Below is the list of changes that have just been committed into a local
5.0 repository of tomash. When tomash 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
  1.2116 06/05/03 22:01:41 kroki@stripped +10 -0
  Bug#14635: Accept NEW.x as INOUT parameters to stored procedures
  from within triggers
  
  Add support for passing NEW.x as INOUT and OUT parameters to stored
  procedures.

  sql/sql_yacc.yy
    1.467 06/05/03 22:01:33 kroki@stripped +11 -3
    Set read_only flag in Item_trigger_field.  For NEW.x trigger variable
    used in left-hand-side of SET statement we set it to UPDATE_ACL,
    otherwise we set it to SELECT_ACL (but see
    Item_trigger_field::prepare_to_assign(), where it may be updated to
    different level).

  sql/sp_head.cc
    1.208 06/05/03 22:01:33 kroki@stripped +37 -41
    Use Routine_parameter interface for parameter updating.

  sql/item_func.h
    1.138 06/05/03 22:01:33 kroki@stripped +4 -1
    Add base class Routine_parameter.

  sql/item_func.cc
    1.283 06/05/03 22:01:33 kroki@stripped +11 -0
    Add Item_func_get_user_var::set() implementation.

  sql/item.h
    1.194 06/05/03 22:01:33 kroki@stripped +40 -8
    Add base class Routine_parameter.
    
    Add read_only attribute and is_read_only() method to
    Item_trigger_field.
    
    Remove Item_trigger_field::access_type and add
    Item_trigger_field::original_privilege and
    Item_trigger_field::want_privilege instead.

  sql/item.cc
    1.219 06/05/03 22:01:33 kroki@stripped +31 -2
    Use Item_trigger_field::want_privilege instead of
    Item_trigger_field::access_type.
    
    Add implementations for prepare_to_assign() and set() methods of
    Routine_parameter base class.  Item_trigger_field::prepare_to_assign()
    is called when this field is about to be assigned to, so we update
    want_privilege to required permissions.  After field update we reset
    privileges to original value.

  mysql-test/t/trigger.test
    1.44 06/05/03 22:01:33 kroki@stripped +74 -0
    Add test case for bug#14635.

  mysql-test/t/trigger-grant.test
    1.7 06/05/03 22:01:32 kroki@stripped +173 -0
    Add test case for bug#14635.

  mysql-test/r/trigger.result
    1.39 06/05/03 22:01:32 kroki@stripped +56 -0
    Add result for bug#14635.

  mysql-test/r/trigger-grant.result
    1.4 06/05/03 22:01:32 kroki@stripped +83 -0
    Add result for bug#14635.

# 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:	kroki
# Host:	moonlight.intranet
# Root:	/home/tomash/src/mysql_ab/mysql-5.0-bug14635

--- 1.218/sql/item.cc	2006-04-28 14:06:51 +04:00
+++ 1.219/sql/item.cc	2006-05-03 22:01:33 +04:00
@@ -958,6 +958,12 @@
 }
 
 
+bool Item_splocal::set(THD *thd, sp_rcontext *ctx, Item *it)
+{
+  return ctx->set_variable(thd, get_var_idx(), it);
+}
+
+
 /*****************************************************************************
   Item_case_expr methods
 *****************************************************************************/
@@ -5365,6 +5371,18 @@
 }
 
 
+bool Item_trigger_field::prepare_to_assign(const bool rw)
+{
+  if (!is_read_only())
+  {
+    /* We are about to assign to this field.  Set required privileges. */
+    want_privilege= (rw ? SELECT_ACL | UPDATE_ACL : UPDATE_ACL);
+    return TRUE;
+  }
+  return FALSE;
+}
+
+
 bool Item_trigger_field::fix_fields(THD *thd, Item **items)
 {
   /*
@@ -5387,8 +5405,7 @@
 
     if (table_grants)
     {
-      table_grants->want_privilege=
-        access_type == AT_READ ? SELECT_ACL : UPDATE_ACL;
+      table_grants->want_privilege= want_privilege;
 
       if (check_grant_column(thd, table_grants, triggers->table->s->db,
                              triggers->table->s->table_name, field_name,
@@ -5425,6 +5442,18 @@
     things from Item_field::cleanup() or Item_ident::cleanup() here.
   */
   Item::cleanup();
+}
+
+
+bool Item_trigger_field::set(THD *thd, sp_rcontext */*ctx*/, Item *it)
+{
+  Item *item= sp_prepare_func_item(thd, &it);
+
+  const bool res= (!item || (!fixed && fix_fields(thd, 0)) ||
+                   (item->save_in_field(field, 0) < 0));
+  /* Reset want_privilege to its original value. */
+  want_privilege= original_privilege;
+  return res;
 }
 
 

--- 1.193/sql/item.h	2006-04-28 14:06:51 +04:00
+++ 1.194/sql/item.h	2006-05-03 22:01:33 +04:00
@@ -748,6 +748,20 @@
 
 
 class sp_head;
+class sp_rcontext;
+
+
+class Routine_parameter
+{
+public:
+  /*
+    Return true if this routine parameter may be assigned to.
+    'rw' flag denotes whether this parameter will be read and updated
+    (true), or just updated (false).
+  */
+  virtual bool prepare_to_assign(const bool rw)= 0;
+  virtual bool set(THD *thd, sp_rcontext *ctx, Item *it)= 0;
+};
 
 
 /*****************************************************************************
@@ -842,7 +856,7 @@
   runtime.
 *****************************************************************************/
 
-class Item_splocal :public Item_sp_variable
+class Item_splocal :public Item_sp_variable, public Routine_parameter
 {
   uint m_var_idx;
 
@@ -880,6 +894,9 @@
 
   inline enum Type type() const;
   inline Item_result result_type() const;
+
+  bool prepare_to_assign(const bool /*rw*/) { return TRUE; };
+  bool set(THD *thd, sp_rcontext *ctx, Item *it);
 };
 
 /*****************************************************************************
@@ -2100,14 +2117,12 @@
         two Field instances representing either OLD or NEW version of this
         field.
 */
-class Item_trigger_field : public Item_field
+class Item_trigger_field : public Item_field, public Routine_parameter
 {
 public:
   /* Is this item represents row from NEW or OLD row ? */
   enum row_version_type {OLD_ROW, NEW_ROW};
   row_version_type row_version;
-  /* Is this item used for reading or updating the value? */
-  enum access_types { AT_READ = 0x1, AT_UPDATE = 0x2 };
   /* Next in list of all Item_trigger_field's in trigger */
   Item_trigger_field *next_trg_field;
   /* Index of the field in the TABLE::field array */
@@ -2118,11 +2133,11 @@
   Item_trigger_field(Name_resolution_context *context_arg,
                      row_version_type row_ver_arg,
                      const char *field_name_arg,
-                     access_types access_type_arg)
+                     ulong priv, const bool ro)
     :Item_field(context_arg,
                (const char *)NULL, (const char *)NULL, field_name_arg),
-     row_version(row_ver_arg), field_idx((uint)-1),
-     access_type(access_type_arg), table_grants(NULL)
+     row_version(row_ver_arg), field_idx((uint)-1), original_privilege(priv),
+     want_privilege(priv), table_grants(NULL), read_only (ro)
   {}
   void setup_field(THD *thd, TABLE *table, GRANT_INFO *table_grant_info);
   enum Type type() const { return TRIGGER_FIELD_ITEM; }
@@ -2131,10 +2146,27 @@
   void print(String *str);
   table_map used_tables() const { return (table_map)0L; }
   void cleanup();
+  bool is_read_only() const { return read_only; }
+
+  bool prepare_to_assign(const bool rw);
+  bool set(THD *thd, sp_rcontext *ctx, Item *it);
 
 private:
-  access_types access_type;
+  /*
+    At parse time we can't tell if this trigger field will be used as
+    OUT or INOUT parameter of stored routine.  Call to
+    prepare_to_assign() will update want_privilege to required level,
+    and call to set() will reset them to original_privilege after
+    value update.
+  */
+  ulong original_privilege;
+  ulong want_privilege;
   GRANT_INFO *table_grants;
+  /*
+    Trigger field is read-only unless it belongs to the NEW row in a
+    BEFORE INSERT of BEFORE UPDATE trigger.
+  */
+  bool read_only;
 };
 
 

--- 1.282/sql/item_func.cc	2006-04-26 22:38:46 +04:00
+++ 1.283/sql/item_func.cc	2006-05-03 22:01:33 +04:00
@@ -4103,6 +4103,17 @@
 }
 
 
+bool Item_func_get_user_var::set(THD *thd, sp_rcontext */*ctx*/, Item *it)
+{
+  Item_func_set_user_var *suv= new Item_func_set_user_var(get_name(), it);
+  /*
+    Item_func_set_user_var is not fixed after construction, call
+    fix_fields().
+  */
+  return (suv->fix_fields(thd, &it) || suv->check() || suv->update());
+}
+
+
 bool Item_user_var_as_out_param::fix_fields(THD *thd, Item **ref)
 {
   DBUG_ASSERT(fixed == 0);

--- 1.137/sql/item_func.h	2006-04-12 19:30:52 +04:00
+++ 1.138/sql/item_func.h	2006-05-03 22:01:33 +04:00
@@ -1178,7 +1178,7 @@
 };
 
 
-class Item_func_get_user_var :public Item_func
+class Item_func_get_user_var :public Item_func, public Routine_parameter
 {
   user_var_entry *var_entry;
 
@@ -1205,6 +1205,9 @@
   table_map used_tables() const
   { return const_item() ? 0 : RAND_TABLE_BIT; }
   bool eq(const Item *item, bool binary_cmp) const;
+
+  bool prepare_to_assign(const bool /*rw*/) { return TRUE; };
+  bool set(THD *thd, sp_rcontext *ctx, Item *it);
 };
 
 

--- 1.466/sql/sql_yacc.yy	2006-04-23 08:11:24 +04:00
+++ 1.467/sql/sql_yacc.yy	2006-05-03 22:01:33 +04:00
@@ -7206,12 +7206,18 @@
               YYABORT;
             }
 
+            DBUG_ASSERT(!new_row ||
+                        (lex->trg_chistics.event == TRG_EVENT_INSERT ||
+                         lex->trg_chistics.event == TRG_EVENT_UPDATE));
+            const bool read_only=
+              !(new_row && lex->trg_chistics.action_time ==
TRG_ACTION_BEFORE);
             if (!(trg_fld= new Item_trigger_field(Lex->current_context(),
                                                   new_row ?
                                                   Item_trigger_field::NEW_ROW:
                                                   Item_trigger_field::OLD_ROW,
                                                   $3.str,
-                                                  Item_trigger_field::AT_READ)))
+                                                  SELECT_ACL,
+                                                  read_only)))
               YYABORT;
 
             /*
@@ -7847,11 +7853,13 @@
               it= new Item_null();
             }
 
+            DBUG_ASSERT(lex->trg_chistics.action_time == TRG_ACTION_BEFORE &&
+                        (lex->trg_chistics.event == TRG_EVENT_INSERT ||
+                         lex->trg_chistics.event == TRG_EVENT_UPDATE));
             if (!(trg_fld= new Item_trigger_field(Lex->current_context(),
                                                   Item_trigger_field::NEW_ROW,
                                                   $2.base_name.str,
-                                                  Item_trigger_field::AT_UPDATE)
-                                                  ) ||
+                                                  UPDATE_ACL, false)) ||
                 !(sp_fld= new sp_instr_set_trigger_field(lex->sphead->
                           	                         instructions(),
                                 	                 lex->spcont,

--- 1.3/mysql-test/r/trigger-grant.result	2006-01-24 20:15:08 +03:00
+++ 1.4/mysql-test/r/trigger-grant.result	2006-05-03 22:01:32 +04:00
@@ -310,3 +310,86 @@
 Hello, world!
 DROP USER mysqltest_u1@localhost;
 DROP DATABASE mysqltest_db1;
+DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
+FLUSH PRIVILEGES;
+DROP DATABASE IF EXISTS mysqltest_db1;
+CREATE DATABASE mysqltest_db1;
+USE mysqltest_db1;
+CREATE TABLE t1 (i1 INT);
+CREATE TABLE t2 (i1 INT);
+CREATE USER mysqltest_dfn@localhost;
+CREATE USER mysqltest_inv@localhost;
+GRANT EXECUTE, CREATE ROUTINE, SUPER ON *.* TO mysqltest_dfn@localhost;
+GRANT INSERT ON mysqltest_db1.* TO mysqltest_inv@localhost;
+CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 3;
+CREATE PROCEDURE p2(INOUT i INT) DETERMINISTIC NO SQL SET i = i * 5;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+CALL p2(NEW.i1);
+INSERT INTO t1 VALUES (7);
+ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in
table 't1'
+INSERT INTO t2 VALUES (11);
+ERROR 42000: SELECT,UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column
'i1' in table 't2'
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+GRANT SELECT ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+CALL p2(NEW.i1);
+INSERT INTO t1 VALUES (13);
+ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in
table 't1'
+INSERT INTO t2 VALUES (17);
+ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in
table 't2'
+REVOKE SELECT ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+CALL p2(NEW.i1);
+INSERT INTO t1 VALUES (19);
+INSERT INTO t2 VALUES (23);
+ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in
table 't2'
+REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+GRANT SELECT, UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+CALL p2(NEW.i1);
+INSERT INTO t1 VALUES (29);
+INSERT INTO t2 VALUES (31);
+REVOKE SELECT, UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 37;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+CALL p1(NEW.i1);
+INSERT INTO t1 VALUES (41);
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1(IN i INT) DETERMINISTIC NO SQL SET @v1 = i + 43;
+INSERT INTO t1 VALUES (47);
+ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in
table 't1'
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1(INOUT i INT) DETERMINISTIC NO SQL SET i = i + 51;
+INSERT INTO t1 VALUES (53);
+ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in
table 't1'
+DROP PROCEDURE p1;
+REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+DROP TRIGGER t1_bi;
+DROP USER mysqltest_inv@localhost;
+DROP USER mysqltest_dfn@localhost;
+DROP TABLE t2;
+DROP TABLE t1;
+DROP DATABASE mysqltest_db1;
+USE test;

--- 1.6/mysql-test/t/trigger-grant.test	2006-03-06 23:45:06 +03:00
+++ 1.7/mysql-test/t/trigger-grant.test	2006-05-03 22:01:32 +04:00
@@ -564,3 +564,176 @@
 DROP USER mysqltest_u1@localhost;
 
 DROP DATABASE mysqltest_db1;
+
+
+#
+# Test for bug #14635 Accept NEW.x as INOUT parameters to stored
+# procedures from within triggers
+#
+# We require UPDATE privilege when NEW.x passed as OUT parameter, and
+# SELECT and UPDATE when NEW.x passed as INOUT parameter.
+#
+DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
+FLUSH PRIVILEGES;
+
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest_db1;
+--enable_warnings
+
+CREATE DATABASE mysqltest_db1;
+USE mysqltest_db1;
+
+CREATE TABLE t1 (i1 INT);
+CREATE TABLE t2 (i1 INT);
+
+CREATE USER mysqltest_dfn@localhost;
+CREATE USER mysqltest_inv@localhost;
+
+GRANT EXECUTE, CREATE ROUTINE, SUPER ON *.* TO mysqltest_dfn@localhost;
+GRANT INSERT ON mysqltest_db1.* TO mysqltest_inv@localhost;
+
+connect (definer,localhost,mysqltest_dfn,,mysqltest_db1);
+connect (invoker,localhost,mysqltest_inv,,mysqltest_db1);
+
+connection definer;
+CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 3;
+CREATE PROCEDURE p2(INOUT i INT) DETERMINISTIC NO SQL SET i = i * 5;
+
+# Check that having no privilege won't work.
+connection definer;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+  CALL p2(NEW.i1);
+
+connection invoker;
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES (7);
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t2 VALUES (11);
+
+connection definer;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+
+# Check that having only SELECT privilege is not enough.
+connection default;
+GRANT SELECT ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+
+connection definer;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+  CALL p2(NEW.i1);
+
+connection invoker;
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES (13);
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t2 VALUES (17);
+
+connection default;
+REVOKE SELECT ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+
+connection definer;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+
+# Check that having only UPDATE privilege is enough for OUT parameter,
+# but not for INOUT parameter.
+connection default;
+GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+
+connection definer;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+  CALL p2(NEW.i1);
+
+connection invoker;
+INSERT INTO t1 VALUES (19);
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t2 VALUES (23);
+
+connection default;
+REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+
+connection definer;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+
+# Check that having SELECT and UPDATE privileges is enough.
+connection default;
+GRANT SELECT, UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+
+connection definer;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+  CALL p2(NEW.i1);
+
+connection invoker;
+INSERT INTO t1 VALUES (29);
+INSERT INTO t2 VALUES (31);
+
+connection default;
+REVOKE SELECT, UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+
+connection definer;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+
+connection default;
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+
+# Check that late procedure redefining won't open a security hole.
+connection default;
+GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+
+connection definer;
+CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 37;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  CALL p1(NEW.i1);
+
+connection invoker;
+INSERT INTO t1 VALUES (41);
+
+connection definer;
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1(IN i INT) DETERMINISTIC NO SQL SET @v1 = i + 43;
+
+connection invoker;
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES (47);
+
+connection definer;
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1(INOUT i INT) DETERMINISTIC NO SQL SET i = i + 51;
+
+connection invoker;
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES (53);
+
+connection default;
+DROP PROCEDURE p1;
+REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+
+connection definer;
+DROP TRIGGER t1_bi;
+
+# Cleanup.
+disconnect definer;
+disconnect invoker;
+connection default;
+DROP USER mysqltest_inv@localhost;
+DROP USER mysqltest_dfn@localhost;
+DROP TABLE t2;
+DROP TABLE t1;
+DROP DATABASE mysqltest_db1;
+USE test;
+
+# End of 5.0 tests.

--- 1.38/mysql-test/r/trigger.result	2006-04-28 10:30:43 +04:00
+++ 1.39/mysql-test/r/trigger.result	2006-05-03 22:01:32 +04:00
@@ -998,3 +998,59 @@
 conn_id	trigger_conn_id
 DROP TRIGGER t1_bi;
 DROP TABLE t1;
+DROP TABLE IF EXISTS t1;
+DROP PROCEDURE IF EXISTS p1;
+DROP PROCEDURE IF EXISTS p2;
+CREATE TABLE t1 (i1 INT);
+INSERT INTO t1 VALUES (3);
+CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET i1 = 5;
+CREATE PROCEDURE p2(INOUT i1 INT) DETERMINISTIC NO SQL SET i1 = i1 * 7;
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+CALL p1(NEW.i1);
+CALL p2(NEW.i1);
+END//
+UPDATE t1 SET i1 = 11 WHERE i1 = 3;
+DROP TRIGGER t1_bu;
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+INSERT INTO t1 VALUES (13);
+CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 17;
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+CALL p1(OLD.i1);
+UPDATE t1 SET i1 = 19 WHERE i1 = 13;
+ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable
+DROP TRIGGER t1_bu;
+DROP PROCEDURE p1;
+INSERT INTO t1 VALUES (23);
+CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 29;
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+CALL p1(OLD.i1);
+UPDATE t1 SET i1 = 31 WHERE i1 = 23;
+ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable
+DROP TRIGGER t1_bu;
+DROP PROCEDURE p1;
+INSERT INTO t1 VALUES (37);
+CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 41;
+CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
+CALL p1(NEW.i1);
+UPDATE t1 SET i1 = 43 WHERE i1 = 37;
+ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable
+DROP TRIGGER t1_au;
+DROP PROCEDURE p1;
+INSERT INTO t1 VALUES (47);
+CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 49;
+CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
+CALL p1(NEW.i1);
+UPDATE t1 SET i1 = 51 WHERE i1 = 47;
+ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable
+DROP TRIGGER t1_au;
+DROP PROCEDURE p1;
+SELECT * FROM t1;
+i1
+35
+13
+23
+43
+51
+DROP TABLE t1;

--- 1.43/mysql-test/t/trigger.test	2006-04-28 10:30:43 +04:00
+++ 1.44/mysql-test/t/trigger.test	2006-05-03 22:01:33 +04:00
@@ -1165,4 +1165,78 @@
 DROP TRIGGER t1_bi;
 DROP TABLE t1;
 
+
+#
+# Test for bug #14635 Accept NEW.x as INOUT parameters to stored
+# procedures from within triggers
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP PROCEDURE IF EXISTS p1;
+DROP PROCEDURE IF EXISTS p2;
+--enable_warnings
+
+CREATE TABLE t1 (i1 INT);
+
+# Check that NEW.x pseudo variable is accepted as INOUT and OUT
+# parameter to stored routine.
+INSERT INTO t1 VALUES (3);
+CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET i1 = 5;
+CREATE PROCEDURE p2(INOUT i1 INT) DETERMINISTIC NO SQL SET i1 = i1 * 7;
+delimiter //;
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+BEGIN
+  CALL p1(NEW.i1);
+  CALL p2(NEW.i1);
+END//
+delimiter ;//
+UPDATE t1 SET i1 = 11 WHERE i1 = 3;
+DROP TRIGGER t1_bu;
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+
+# Check that OLD.x pseudo variable is not accepted as INOUT and OUT
+# parameter to stored routine.
+INSERT INTO t1 VALUES (13);
+CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 17;
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+  CALL p1(OLD.i1);
+--error ER_SP_NOT_VAR_ARG
+UPDATE t1 SET i1 = 19 WHERE i1 = 13;
+DROP TRIGGER t1_bu;
+DROP PROCEDURE p1;
+
+INSERT INTO t1 VALUES (23);
+CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 29;
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+  CALL p1(OLD.i1);
+--error ER_SP_NOT_VAR_ARG
+UPDATE t1 SET i1 = 31 WHERE i1 = 23;
+DROP TRIGGER t1_bu;
+DROP PROCEDURE p1;
+
+# Check that NEW.x pseudo variable is read-only in the AFTER TRIGGER.
+INSERT INTO t1 VALUES (37);
+CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 41;
+CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
+  CALL p1(NEW.i1);
+--error ER_SP_NOT_VAR_ARG
+UPDATE t1 SET i1 = 43 WHERE i1 = 37;
+DROP TRIGGER t1_au;
+DROP PROCEDURE p1;
+
+INSERT INTO t1 VALUES (47);
+CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 49;
+CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
+  CALL p1(NEW.i1);
+--error ER_SP_NOT_VAR_ARG
+UPDATE t1 SET i1 = 51 WHERE i1 = 47;
+DROP TRIGGER t1_au;
+DROP PROCEDURE p1;
+
+# Post requisite.
+SELECT * FROM t1;
+
+DROP TABLE t1;
+
 # End of 5.0 tests

--- 1.207/sql/sp_head.cc	2006-04-18 13:07:30 +04:00
+++ 1.208/sql/sp_head.cc	2006-05-03 22:01:33 +04:00
@@ -1355,15 +1355,36 @@
 }
 
 
-static Item_func_get_user_var *item_is_user_var(Item *it)
+static Routine_parameter *get_routine_parameter(Item *it)
 {
-  if (it->type() == Item::FUNC_ITEM)
+  if (it->is_splocal())
   {
-    Item_func *fi= static_cast<Item_func*>(it);
+    return static_cast<Item_splocal *>(it);
+  }
+  else
+  {
+    switch (it->type()) {
+    case Item::FUNC_ITEM:
+    {
+      Item_func *fi= static_cast<Item_func *>(it);
+ 
+      if (fi->functype() == Item_func::GUSERVAR_FUNC)
+      {
+        return static_cast<Item_func_get_user_var *>(fi);
+      }
+    }
+    break;
 
-    if (fi->functype() == Item_func::GUSERVAR_FUNC)
-      return static_cast<Item_func_get_user_var*>(fi);
+    case Item::TRIGGER_FIELD_ITEM:
+    {
+      return static_cast<Item_trigger_field *>(it);
+    }
+
+    default:
+      ;
+    }
   }
+
   return NULL;
 }
 
@@ -1443,17 +1464,20 @@
     for (uint i= 0 ; i < params ; i++)
     {
       Item *arg_item= it_args++;
-      sp_variable_t *spvar= m_pcont->find_variable(i);
 
       if (!arg_item)
         break;
 
+      sp_variable_t *spvar= m_pcont->find_variable(i);
+
       if (!spvar)
         continue;
 
       if (spvar->mode != sp_param_in)
       {
-        if (!arg_item->is_splocal() && !item_is_user_var(arg_item))
+        Routine_parameter *pi= get_routine_parameter(arg_item);
+
+        if (!(pi && pi->prepare_to_assign(spvar->mode == sp_param_inout)))
         {
           my_error(ER_SP_NOT_VAR_ARG, MYF(0), i+1, m_qname.str);
           err_status= TRUE;
@@ -1526,36 +1550,13 @@
       if (spvar->mode == sp_param_in)
         continue;
 
-      if (arg_item->is_splocal())
-      {
-        if (octx->set_variable(thd,
-                               ((Item_splocal*) arg_item)->get_var_idx(),
-                               nctx->get_item(i)))
-        {
-          err_status= TRUE;
-          break;
-        }
-      }
-      else
-      {
-        Item_func_get_user_var *guv= item_is_user_var(arg_item);
+      Routine_parameter *pi= get_routine_parameter(arg_item);
 
-	if (guv)
-	{
-	  Item *item= nctx->get_item(i);
-	  Item_func_set_user_var *suv;
-
-	  suv= new Item_func_set_user_var(guv->get_name(), item);
-	  /*
-            Item_func_set_user_var is not fixed after construction,
-            call fix_fields().
-	  */
-          if ((err_status= test(!suv || suv->fix_fields(thd, &item) ||
-                                suv->check() || suv->update())))
-            break;
-	}
+      if (pi->set(thd, octx, nctx->get_item(i)))
+      {
+        err_status= TRUE;
+        break;
       }
-
     }
   }
 
@@ -2389,12 +2390,7 @@
 int
 sp_instr_set_trigger_field::exec_core(THD *thd, uint *nextp)
 {
-  int res= 0;
-  Item *it= sp_prepare_func_item(thd, &value);
-  if (!it ||
-      !trigger_field->fixed && trigger_field->fix_fields(thd, 0) ||
-      (it->save_in_field(trigger_field->field, 0) < 0))
-    res= -1;
+  const int res= (!trigger_field->set(thd, NULL, value) ? 0 : -1);
   *nextp = m_ip+1;
   return res;
 }