bzr commit into mysql-6.0 branch (davi:2693)

[Davi Arnaut]

# At a local mysql-6.0 repository of davi

 2693 Davi Arnaut	2008-07-03 [merge]
      Auto merge.
modified:
  mysql-test/r/ps_ddl.result
  mysql-test/r/ps_ddl1.result
  mysql-test/r/sp-error.result
  mysql-test/t/ps_ddl.test
  mysql-test/t/ps_ddl1.test
  mysql-test/t/sp-error.test
  sql/sp_cache.cc
  sql/sp_cache.h
  sql/sql_prepare.cc

=== modified file 'mysql-test/r/ps_ddl.result'
--- a/mysql-test/r/ps_ddl.result	2008-05-29 12:56:51 +0000
+++ b/mysql-test/r/ps_ddl.result	2008-07-03 19:42:27 +0000
@@ -290,7 +290,7 @@ SUCCESS
 
 # Test 7-b: dependent FUNCTION has changed
 #
-# Note, this scenario is not supported, subject of Bug#12093
+# Note, this scenario is supported, subject of Bug#12093
 #
 drop trigger t1_ai;
 create trigger t1_ai after insert on t1 for  each row
@@ -305,8 +305,7 @@ select @var;
 drop function f1;
 create function f1 (a int) returns int return 0;
 execute stmt using @var;
-ERROR 42000: FUNCTION test.f1 does not exist
-call p_verify_reprepare_count(0);
+call p_verify_reprepare_count(1);
 SUCCESS
 
 drop function f1;
@@ -359,8 +358,14 @@ a
 drop view v1;
 create view v1 as select a from t2;
 set @var=8;
+# XXX: bug, the SQL statement in the trigger is still
+# pointing at table 't3', since the view was expanded
+# at first statement execution.
+# Repreparation of the main statement doesn't cause repreparation
+# of trigger statements.
 execute stmt using @var;
-call p_verify_reprepare_count(0);
+ERROR 42S02: Table 'test.t3' doesn't exist
+call p_verify_reprepare_count(1);
 SUCCESS
 
 #
@@ -377,7 +382,6 @@ select * from t3;
 a
 6
 7
-8
 flush table t1;
 set @var=9;
 execute stmt using @var;
@@ -392,7 +396,6 @@ select * from t3;
 a
 6
 7
-8
 drop view v1;
 drop table t1,t2,t3;
 # Test 7-d: dependent TABLE has changed
@@ -798,14 +801,17 @@ SUCCESS
 
 drop function f1;
 create function f1() returns int return 2;
-# XXX: Bug#12093. We only get a different error
+# XXX: Used to be another manifestation of Bug#12093.
+# We only used to get a different error
 # message because the non-existing procedure error is masked
 # by the view.
 execute stmt;
-ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or
definer/invoker of view lack rights to use them
+f1()
+2
 execute stmt;
-ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or
definer/invoker of view lack rights to use them
-call p_verify_reprepare_count(0);
+f1()
+2
+call p_verify_reprepare_count(1);
 SUCCESS
 
 # Part 18b: dependent procedure has changed (referred to via a function)
@@ -831,19 +837,20 @@ SUCCESS
 
 drop procedure p1;
 create procedure p1(out x int) select max(a) from t2 into x;
-# XXX: bug. The prelocked list is not invalidated
-# and we keep opening table t1, whereas the procedure
+# XXX: used to be a bug. The prelocked list was not invalidated
+# and we kept opening table t1, whereas the procedure
 # is now referring to table t2
 execute stmt;
-ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or
definer/invoker of view lack rights to use them
-call p_verify_reprepare_count(0);
+f1()
+6
+call p_verify_reprepare_count(1);
 SUCCESS
 
 flush table t1;
 execute stmt;
 f1()
 6
-call p_verify_reprepare_count(1);
+call p_verify_reprepare_count(0);
 SUCCESS
 
 execute stmt;
@@ -1528,7 +1535,6 @@ drop view v_27690_1;
 drop table v_27690_2;
 deallocate prepare stmt;
 #=====================================================================
-# TODO: fix the below two bugs and modify their tests
 #
 # Bug#21294 Executing a prepared statement that executes
 # a stored function which was recreat
@@ -1541,12 +1547,14 @@ f1()
 drop function f1;
 create function f1() returns int return 10;
 execute stmt;
-ERROR 42000: FUNCTION test.f1 does not exist
+f1()
+10
 drop function f1;
 create function f1() returns int return 20;
 execute stmt;
-ERROR 42000: FUNCTION test.f1 does not exist
-call p_verify_reprepare_count(0);
+f1()
+20
+call p_verify_reprepare_count(2);
 SUCCESS
 
 drop function f1;
@@ -1573,19 +1581,21 @@ execute stmt_sp;
 a
 drop function f_12093_unrelated;
 drop procedure p_12093_unrelated;
-# XXX: bug
+# XXX: used to be a bug
 execute stmt_sf;
-ERROR 42000: FUNCTION test.f_12093 does not exist
-# XXX: bug
+f_12093()
+0
+# XXX: used to be a bug
 execute stmt_sp;
-ERROR 42000: PROCEDURE test.p_12093 does not exist
-# XXX: bug
+a
+# XXX: used to be a bug
 execute stmt_sf;
-ERROR 42000: FUNCTION test.f_12093 does not exist
-# XXX: bug
+f_12093()
+0
+# XXX: used to be a bug
 execute stmt_sp;
-ERROR 42000: PROCEDURE test.p_12093 does not exist
-call p_verify_reprepare_count(0);
+a
+call p_verify_reprepare_count(2);
 SUCCESS
 
 drop table t_12093;

=== modified file 'mysql-test/r/ps_ddl1.result'
--- a/mysql-test/r/ps_ddl1.result	2008-04-18 19:18:53 +0000
+++ b/mysql-test/r/ps_ddl1.result	2008-07-03 19:41:22 +0000
@@ -460,7 +460,7 @@ create schema mysqltest;
 end|
 execute stmt;
 ERROR 42000: PROCEDURE test.p1 does not exist
-call p_verify_reprepare_count(0);
+call p_verify_reprepare_count(1);
 SUCCESS
 
 execute stmt;

=== modified file 'mysql-test/r/sp-error.result'
--- a/mysql-test/r/sp-error.result	2008-04-21 07:30:39 +0000
+++ b/mysql-test/r/sp-error.result	2008-07-03 19:42:27 +0000
@@ -796,7 +796,7 @@ bug11834_2()
 10
 drop function bug11834_1;
 execute stmt;
-ERROR 42000: FUNCTION test.bug11834_2 does not exist
+ERROR 42000: FUNCTION test.bug11834_1 does not exist
 deallocate prepare stmt;
 drop function bug11834_2;
 DROP FUNCTION IF EXISTS bug12953|
@@ -1041,7 +1041,8 @@ select bug12329();
 bug12329()
 101
 execute stmt1;
-ERROR 42S02: Table 'test.t2' doesn't exist
+bug12329()
+101
 deallocate prepare stmt1;
 drop function bug12329;
 drop table t1, t2;

=== modified file 'mysql-test/t/ps_ddl.test'
--- a/mysql-test/t/ps_ddl.test	2008-05-29 12:56:51 +0000
+++ b/mysql-test/t/ps_ddl.test	2008-07-03 19:42:27 +0000
@@ -299,7 +299,7 @@ call p_verify_reprepare_count(0);
 
 --echo # Test 7-b: dependent FUNCTION has changed
 --echo #
---echo # Note, this scenario is not supported, subject of Bug#12093
+--echo # Note, this scenario is supported, subject of Bug#12093
 --echo #
 drop trigger t1_ai;
 create trigger t1_ai after insert on t1 for  each row
@@ -311,9 +311,8 @@ execute stmt using @var;
 select @var;
 drop function f1;
 create function f1 (a int) returns int return 0;
---error ER_SP_DOES_NOT_EXIST
 execute stmt using @var;
-call p_verify_reprepare_count(0);
+call p_verify_reprepare_count(1);
 drop function f1;
 deallocate prepare stmt;
 
@@ -353,8 +352,14 @@ select * from t2;
 drop view v1;
 create view v1 as select a from t2;
 set @var=8;
+--echo # XXX: bug, the SQL statement in the trigger is still
+--echo # pointing at table 't3', since the view was expanded
+--echo # at first statement execution.
+--echo # Repreparation of the main statement doesn't cause repreparation
+--echo # of trigger statements.
+--error ER_NO_SUCH_TABLE
 execute stmt using @var;
-call p_verify_reprepare_count(0);
+call p_verify_reprepare_count(1);
 --echo #
 --echo # Sic: the insert went into t3, even though the view now
 --echo # points at t2. This is because neither the merged view
@@ -703,14 +708,13 @@ execute stmt;
 call p_verify_reprepare_count(0);
 drop function f1;
 create function f1() returns int return 2;
---echo # XXX: Bug#12093. We only get a different error
+--echo # XXX: Used to be another manifestation of Bug#12093.
+--echo # We only used to get a different error
 --echo # message because the non-existing procedure error is masked
 --echo # by the view.
---error ER_VIEW_INVALID
 execute stmt;
---error ER_VIEW_INVALID
 execute stmt;
-call p_verify_reprepare_count(0);
+call p_verify_reprepare_count(1);
 
 --echo # Part 18b: dependent procedure has changed (referred to via a function)
 
@@ -734,15 +738,14 @@ execute stmt;
 call p_verify_reprepare_count(0);
 drop procedure p1;
 create procedure p1(out x int) select max(a) from t2 into x;
---echo # XXX: bug. The prelocked list is not invalidated
---echo # and we keep opening table t1, whereas the procedure
+--echo # XXX: used to be a bug. The prelocked list was not invalidated
+--echo # and we kept opening table t1, whereas the procedure
 --echo # is now referring to table t2
---error ER_VIEW_INVALID
 execute stmt;
-call p_verify_reprepare_count(0);
+call p_verify_reprepare_count(1);
 flush table t1;
 execute stmt;
-call p_verify_reprepare_count(1);
+call p_verify_reprepare_count(0);
 execute stmt;
 
 --echo # Test 18-c: dependent VIEW has changed
@@ -1326,7 +1329,6 @@ drop table v_27690_2;
 deallocate prepare stmt;
 
 --echo #=====================================================================
---echo # TODO: fix the below two bugs and modify their tests
 --echo #
 --echo # Bug#21294 Executing a prepared statement that executes
 --echo # a stored function which was recreat
@@ -1341,15 +1343,13 @@ drop function f1;
 create function f1() returns int return 10;
 
 # might pass or fail, implementation dependent
---error ER_SP_DOES_NOT_EXIST
 execute stmt;
 
 drop function f1;
 create function f1() returns int return 20;
 
---error ER_SP_DOES_NOT_EXIST
 execute stmt;
-call p_verify_reprepare_count(0);
+call p_verify_reprepare_count(2);
 
 drop function f1;
 deallocate prepare stmt;
@@ -1388,20 +1388,16 @@ drop procedure p_12093_unrelated;
 
 connection default;
 
---echo # XXX: bug
---error ER_SP_DOES_NOT_EXIST
+--echo # XXX: used to be a bug
 execute stmt_sf;
---echo # XXX: bug
---error ER_SP_DOES_NOT_EXIST
+--echo # XXX: used to be a bug
 execute stmt_sp;
 
---echo # XXX: bug
---error ER_SP_DOES_NOT_EXIST
+--echo # XXX: used to be a bug
 execute stmt_sf;
---echo # XXX: bug
---error ER_SP_DOES_NOT_EXIST
+--echo # XXX: used to be a bug
 execute stmt_sp;
-call p_verify_reprepare_count(0);
+call p_verify_reprepare_count(2);
 
 disconnect con1;
 

=== modified file 'mysql-test/t/ps_ddl1.test'
--- a/mysql-test/t/ps_ddl1.test	2008-04-18 19:18:53 +0000
+++ b/mysql-test/t/ps_ddl1.test	2008-07-03 19:41:22 +0000
@@ -363,7 +363,7 @@ end|
 delimiter ;|
 --error ER_SP_DOES_NOT_EXIST
 execute stmt;
-call p_verify_reprepare_count(0);
+call p_verify_reprepare_count(1);
 --error ER_SP_DOES_NOT_EXIST
 execute stmt;
 call p_verify_reprepare_count(0);

=== modified file 'mysql-test/t/sp-error.test'
--- a/mysql-test/t/sp-error.test	2008-04-14 23:28:17 +0000
+++ b/mysql-test/t/sp-error.test	2008-07-03 19:42:27 +0000
@@ -1465,10 +1465,6 @@ execute stmt1;
 drop function bug12329;
 create function bug12329() returns int return (select a+100 from t2);
 select bug12329();
-# Until we implement proper mechanism for invalidation of PS/SP when table
-# or SP's are changed the following statement will fail with 'Table ... was
-# not locked' error (this mechanism should be based on the new TDC).
---error ER_NO_SUCH_TABLE 
 execute stmt1;
 deallocate prepare stmt1;
 drop function bug12329; 

=== modified file 'sql/sp_cache.cc'
--- a/sql/sp_cache.cc	2007-08-13 13:11:25 +0000
+++ b/sql/sp_cache.cc	2008-07-03 19:41:22 +0000
@@ -210,6 +210,19 @@ void sp_cache_flush_obsolete(sp_cache **
 }
 
 
+/**
+  Return the current version of the cache.
+*/
+
+ulong sp_cache_version(sp_cache **cp)
+{
+  sp_cache *c= *cp;
+  if (c)
+    return c->version;
+  return 0;
+}
+
+
 /*************************************************************************
   Internal functions 
  *************************************************************************/

=== modified file 'sql/sp_cache.h'
--- a/sql/sp_cache.h	2006-12-23 19:17:15 +0000
+++ b/sql/sp_cache.h	2008-07-03 19:41:22 +0000
@@ -58,5 +58,6 @@ void sp_cache_insert(sp_cache **cp, sp_h
 sp_head *sp_cache_lookup(sp_cache **cp, sp_name *name);
 void sp_cache_invalidate();
 void sp_cache_flush_obsolete(sp_cache **cp);
+ulong sp_cache_version(sp_cache **cp);
 
 #endif /* _SP_CACHE_H_ */

=== modified file 'sql/sql_prepare.cc'
--- a/sql/sql_prepare.cc	2008-06-17 14:22:19 +0000
+++ b/sql/sql_prepare.cc	2008-07-03 19:42:27 +0000
@@ -169,6 +169,8 @@ private:
     SELECT_LEX and other classes).
   */
   MEM_ROOT main_mem_root;
+  /* Version of the stored functions cache at the time of prepare. */
+  ulong m_sp_cache_version;
 private:
   bool set_db(const char *db, uint db_length);
   bool set_parameters(String *expanded_query,
@@ -2822,7 +2824,8 @@ Prepared_statement::Prepared_statement(T
   param_array(0),
   param_count(0),
   last_errno(0),
-  flags((uint) IS_IN_USE)
+  flags((uint) IS_IN_USE),
+  m_sp_cache_version(0)
 {
   init_sql_alloc(&main_mem_root, thd_arg->variables.query_alloc_block_size,
                   thd_arg->variables.query_prealloc_size);
@@ -3075,6 +3078,20 @@ bool Prepared_statement::prepare(const c
     init_stmt_after_parse(lex);
     state= Query_arena::PREPARED;
     flags&= ~ (uint) IS_IN_USE;
+    /*
+      This is for prepared statement validation purposes.
+      A statement looks up and pre-loads all its stored functions
+      at prepare. Later on, if a function is gone from the cache,
+      execute may fail.
+      Remember the cache version to be able to invalidate the prepared
+      statement at execute if it changes.
+      We only need to care about version of the stored functions cache:
+      if a prepared statement uses a stored procedure, it's indirect,
+      via a stored function. The only exception is SQLCOM_CALL,
+      but the latter one looks up the stored procedure each time
+      it's invoked, rather than once at prepare.
+    */
+    m_sp_cache_version= sp_cache_version(&thd->sp_func_cache);
 
     /* 
       Log COM_EXECUTE to the general log. Note, that in case of SQL
@@ -3386,6 +3403,7 @@ Prepared_statement::swap_prepared_statem
   swap_variables(LEX_STRING, name, copy->name);
   /* Ditto */
   swap_variables(char *, db, copy->db);
+  swap_variables(ulong, m_sp_cache_version, copy->m_sp_cache_version);
 
   DBUG_ASSERT(db_length == copy->db_length);
   DBUG_ASSERT(param_count == copy->param_count);
@@ -3446,6 +3464,19 @@ bool Prepared_statement::execute(String 
   }
 
   /*
+    Reprepare the statement if we're using stored functions
+    and the version of the stored routines cache has changed.
+  */
+  if (lex->uses_stored_routines() &&
+      m_sp_cache_version != sp_cache_version(&thd->sp_func_cache) &&
+      thd->m_reprepare_observer &&
+      thd->m_reprepare_observer->report_error(thd))
+  {
+    return TRUE;
+  }
+
+
+  /*
     For SHOW VARIABLES lex->result is NULL, as it's a non-SELECT
     command. For such queries we don't return an error and don't
     open a cursor -- the client library will recognize this case and