List:Commits« Previous MessageNext Message »
From:marc.alff Date:November 26 2007 4:15pm
Subject:bk commit into 5.1 tree (malff:1.2616) BUG#12093
View as plain text  
Below is the list of changes that have just been committed into a local
5.1 repository of malff. When malff 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-11-26 09:15:03-07:00, malff@stripped. +29 -0
  Bug#12093 (SP not found on second PS execution if another thread drops other
  SP in between)
  
  TEMPORARY PATCH FOR INTERNAL DISCUSSION
  
  This patch is the 3rd iteration for this fix.
  Remaining issues:
  - Using TABLE_SHARE::table_map_id as version does not resist FLUSH TABLES
  - Dependency on Bug#26379 (to be merged) in table.cc
  - Re validation of views under LOCK_open / view definition cache
  
  The following bugs are fixed and tested:
  - Bug 12093 (SP not found on second PS execution if another thread drops other
    SP in between)
  - Bug 21294 (executing a prepared statement that executes a stored function
    which was recreat)
  - Bug 27420 (A combination of PS and view operations cause error + assertion
    on shutdown)
  - Bug 27430 Crash in subquery code when in PS and table DDL changed after
    PREPARE()
  - Bug 27690 (Re-execution of prepared statement after table was replaced with
    a view crashes)

  mysql-test/r/ps.result@stripped, 2007-11-26 09:14:49-07:00, malff@stripped. +14 -27
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/r/ps_1general.result@stripped, 2007-11-26 09:14:49-07:00, malff@stripped. +6 -11
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/r/ps_ddl.result@stripped, 2007-11-26 09:14:52-07:00, malff@stripped. +1003 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION
    

  mysql-test/r/ps_ddl.result@stripped, 2007-11-26 09:14:52-07:00, malff@stripped. +0 -0

  mysql-test/r/sp-error.result@stripped, 2007-11-26 09:14:49-07:00, malff@stripped. +1 -1
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/r/sp.result@stripped, 2007-11-26 09:14:49-07:00, malff@stripped. +6 -6
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/r/sp_notembedded.result@stripped, 2007-11-26 09:14:49-07:00, malff@stripped. +6 -6
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/r/trigger.result@stripped, 2007-11-26 09:14:49-07:00, malff@stripped. +2 -1
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/t/ps.test@stripped, 2007-11-26 09:14:49-07:00, malff@stripped. +22 -37
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/t/ps_1general.test@stripped, 2007-11-26 09:14:50-07:00, malff@stripped. +12 -1
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/t/ps_ddl.test@stripped, 2007-11-26 09:14:52-07:00, malff@stripped. +1270 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION
    

  mysql-test/t/ps_ddl.test@stripped, 2007-11-26 09:14:52-07:00, malff@stripped. +0 -0

  mysql-test/t/sp-error.test@stripped, 2007-11-26 09:14:50-07:00, malff@stripped. +1 -5
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/t/sp.test@stripped, 2007-11-26 09:14:50-07:00, malff@stripped. +6 -6
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/t/sp_notembedded.test@stripped, 2007-11-26 09:14:50-07:00, malff@stripped. +6 -6
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  mysql-test/t/trigger.test@stripped, 2007-11-26 09:14:50-07:00, malff@stripped. +2 -4
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/parse_file.cc@stripped, 2007-11-26 09:14:50-07:00, malff@stripped. +1 -1
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/share/errmsg.txt@stripped, 2007-11-26 09:14:52-07:00, malff@stripped. +4 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sp.cc@stripped, 2007-11-26 09:14:50-07:00, malff@stripped. +166 -50
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sp.h@stripped, 2007-11-26 09:14:50-07:00, malff@stripped. +1 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sp_head.cc@stripped, 2007-11-26 09:14:51-07:00, malff@stripped. +60 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sp_head.h@stripped, 2007-11-26 09:14:51-07:00, malff@stripped. +7 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sql_base.cc@stripped, 2007-11-26 09:14:51-07:00, malff@stripped. +39 -14
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sql_class.cc@stripped, 2007-11-26 09:14:51-07:00, malff@stripped. +307 -1
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sql_class.h@stripped, 2007-11-26 09:14:51-07:00, malff@stripped. +223 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sql_prepare.cc@stripped, 2007-11-26 09:14:51-07:00, malff@stripped. +399 -14
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sql_view.cc@stripped, 2007-11-26 09:14:51-07:00, malff@stripped. +51 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/sql_view.h@stripped, 2007-11-26 09:14:52-07:00, malff@stripped. +2 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/table.cc@stripped, 2007-11-26 09:14:52-07:00, malff@stripped. +74 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  sql/table.h@stripped, 2007-11-26 09:14:52-07:00, malff@stripped. +10 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

  tests/mysql_client_test.c@stripped, 2007-11-26 09:14:52-07:00, malff@stripped. +91 -18
    TEMPORARY PATCH FOR INTERNAL DISCUSSION

diff -Nrup a/mysql-test/r/ps.result b/mysql-test/r/ps.result
--- a/mysql-test/r/ps.result	2007-11-02 05:47:15 -06:00
+++ b/mysql-test/r/ps.result	2007-11-26 09:14:49 -07:00
@@ -491,12 +491,15 @@ prepare stmt from "delete t1 from t1 whe
 drop temporary table t1;
 create temporary table if not exists t1 (a1 int);
 execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t1' has changed
 drop temporary table t1;
 create temporary table if not exists t1 (a1 int);
 execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t1' has changed
 drop temporary table t1;
 create temporary table if not exists t1 (a1 int);
 execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t1' has changed
 drop temporary table t1;
 deallocate prepare stmt;
 create table t1 (a varchar(20));
@@ -905,20 +908,6 @@ show status like 'prepared_stmt_count';
 Variable_name	Value
 Prepared_stmt_count	0
 set global max_prepared_stmt_count= @old_max_prepared_stmt_count;
-drop table if exists t1;
-create temporary table if not exists t1 (a1 int);
-prepare stmt from "delete t1 from t1 where (cast(a1/3 as unsigned) * 3) = a1";
-drop temporary table t1;
-create temporary table if not exists t1 (a1 int);
-execute stmt;
-drop temporary table t1;
-create temporary table if not exists t1 (a1 int);
-execute stmt;
-drop temporary table t1;
-create temporary table if not exists t1 (a1 int);
-execute stmt;
-drop temporary table t1;
-deallocate prepare stmt;
 CREATE TABLE t1(
 ID int(10) unsigned NOT NULL auto_increment,
 Member_ID varchar(15) NOT NULL default '',
@@ -1452,15 +1441,6 @@ prepare stmt_optimize from "optimize tab
 prepare stmt_show from "show tables like 't1'";
 prepare stmt_truncate from "truncate table t1";
 prepare stmt_drop from "drop table t1";
-drop table t1;
-use test;
-execute stmt_create;
-show tables like 't1';
-Tables_in_test (t1)
-use mysqltest_long_database_name_to_thrash_heap;
-show tables like 't1';
-Tables_in_mysqltest_long_database_name_to_thrash_heap (t1)
-t1
 use test;
 execute stmt_insert;
 select * from mysqltest_long_database_name_to_thrash_heap.t1;
@@ -1498,6 +1478,15 @@ Tables_in_test (t1)
 use mysqltest_long_database_name_to_thrash_heap;
 show tables like 't1';
 Tables_in_mysqltest_long_database_name_to_thrash_heap (t1)
+use test;
+execute stmt_create;
+show tables like 't1';
+Tables_in_test (t1)
+use mysqltest_long_database_name_to_thrash_heap;
+show tables like 't1';
+Tables_in_mysqltest_long_database_name_to_thrash_heap (t1)
+t1
+use mysqltest_long_database_name_to_thrash_heap;
 drop database mysqltest_long_database_name_to_thrash_heap;
 prepare stmt_create from "create table t1 (i int)";
 ERROR 3D000: No database selected
@@ -2648,16 +2637,14 @@ ALTER TABLE t2 ADD COLUMN j INT;
 INSERT INTO t1 VALUES (4, 5);
 INSERT INTO t2 VALUES (4, 5);
 EXECUTE stmt1;
-i
-1
-4
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t1' has changed
 EXECUTE stmt2;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t2' has changed
 SELECT * FROM t2;
 i	j
 2	NULL
 3	NULL
 4	5
-3	NULL
 DROP TABLE t1, t2;
 drop table if exists t1;
 Warnings:
diff -Nrup a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result
--- a/mysql-test/r/ps_1general.result	2007-09-20 02:56:25 -06:00
+++ b/mysql-test/r/ps_1general.result	2007-11-26 09:14:49 -07:00
@@ -132,8 +132,7 @@ c int
 );
 insert into t5( a, b, c) values( 9, 'recreated table', 9);
 execute stmt2 ;
-a	b	c
-9	recreated table	9
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t5' has changed
 drop table t5 ;
 create table t5
 (
@@ -143,8 +142,7 @@ b char(30)
 );
 insert into t5( a, b, c) values( 9, 'recreated table', 9);
 execute stmt2 ;
-a	b	c
-9	recreated table	9
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t5' has changed
 drop table t5 ;
 create table t5
 (
@@ -155,8 +153,7 @@ d timestamp default current_timestamp
 );
 insert into t5( a, b, c) values( 9, 'recreated table', 9);
 execute stmt2 ;
-a	b	c
-9	recreated table	9
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t5' has changed
 drop table t5 ;
 create table t5
 (
@@ -167,8 +164,7 @@ c int
 );
 insert into t5( a, b, c) values( 9, 'recreated table', 9);
 execute stmt2 ;
-a	b	c
-9	recreated table	9
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t5' has changed
 drop table t5 ;
 create table t5
 (
@@ -178,8 +174,7 @@ c int
 );
 insert into t5( b, c) values( 'recreated table', 9);
 execute stmt2 ;
-a	b	c
-2004-02-29 18:01:59	recreated table	9
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t5' has changed
 drop table t5 ;
 create table t5
 (
@@ -189,7 +184,7 @@ f3 int
 );
 insert into t5( f1, f2, f3) values( 9, 'recreated table', 9);
 execute stmt2 ;
-ERROR 42S22: Unknown column 'test.t5.a' in 'field list'
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t5' has changed
 drop table t5 ;
 prepare stmt1 from ' select * from t1 where a <= 2 ' ;
 execute stmt1 ;
diff -Nrup a/mysql-test/r/ps_ddl.result b/mysql-test/r/ps_ddl.result
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/r/ps_ddl.result	2007-11-26 09:14:52 -07:00
@@ -0,0 +1,1003 @@
+=====================================================================
+Testing 1: NOTHING -> TABLE transitions
+=====================================================================
+drop table if exists t1;
+prepare stmt from 'select * from t1';
+ERROR 42S02: Table 'test.t1' doesn't exist
+=====================================================================
+Testing 2: NOTHING -> TEMPORARY TABLE transitions
+=====================================================================
+=====================================================================
+Testing 3: NOTHING -> VIEW transitions
+=====================================================================
+=====================================================================
+Testing 4: TABLE -> NOTHING transitions
+=====================================================================
+drop table if exists t4;
+create table t4(a int);
+prepare stmt from 'select * from t4';
+execute stmt;
+a
+execute stmt;
+a
+drop table t4;
+execute stmt;
+ERROR 42S02: Table 'test.t4' doesn't exist
+execute stmt;
+ERROR 42S02: Table 'test.t4' doesn't exist
+=====================================================================
+Testing 5: TABLE -> TABLE (DDL) transitions
+=====================================================================
+drop table if exists t5;
+create table t5(a int);
+prepare stmt from 'select * from t5';
+execute stmt;
+a
+execute stmt;
+a
+alter table t5 add column (b int);
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t5' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t5' has changed
+drop table t5;
+=====================================================================
+Testing 6: TABLE -> TABLE (TRIGGER) transitions
+=====================================================================
+drop table if exists t6;
+create table t6(a int);
+prepare stmt from 'insert into t6(a) value (?)';
+set @val=1;
+execute stmt using @val;
+set @val=2;
+execute stmt using @val;
+create trigger t6_bi before insert on t6 for each row
+begin end;
+set @val=3;
+execute stmt using @val;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t6' has changed
+set @val=4;
+execute stmt using @val;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t6' has changed
+prepare stmt from 'insert into t6(a) value (?)';
+set @val=5;
+execute stmt using @val;
+set @val=6;
+execute stmt using @val;
+create trigger t6_bd before delete on t6 for each row
+begin end;
+set @val=7;
+execute stmt using @val;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t6' has changed
+set @val=8;
+execute stmt using @val;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t6' has changed
+prepare stmt from 'insert into t6(a) value (?)';
+set @val=9;
+execute stmt using @val;
+set @val=10;
+execute stmt using @val;
+drop trigger t6_bi;
+set @val=11;
+execute stmt using @val;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t6' has changed
+set @val=12;
+execute stmt using @val;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t6' has changed
+prepare stmt from 'insert into t6(a) value (?)';
+set @val=13;
+execute stmt using @val;
+set @val=14;
+execute stmt using @val;
+drop trigger t6_bd;
+set @val=15;
+execute stmt using @val;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t6' has changed
+set @val=16;
+execute stmt using @val;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t6' has changed
+select * from t6 order by a;
+a
+1
+2
+5
+6
+9
+10
+13
+14
+drop table t6;
+=====================================================================
+Testing 7: TABLE -> TABLE (TRIGGER dependencies) transitions
+=====================================================================
+drop table if exists t7_proc;
+drop table if exists t7_func;
+drop table if exists t7_view;
+drop table if exists t7_table;
+drop table if exists t7_dependent_table;
+drop table if exists t7_table_trigger;
+drop table if exists t7_audit;
+drop procedure if exists audit_proc;
+drop function if exists audit_func;
+drop view if exists audit_view;
+create table t7_proc(a int);
+create table t7_func(a int);
+create table t7_view(a int);
+create table t7_table(a int);
+create table t7_table_trigger(a int);
+create table t7_audit(old_a int, new_a int, reason varchar(50));
+create table t7_dependent_table(old_a int, new_a int, reason varchar(50));
+create procedure audit_proc(a int)
+insert into t7_audit values (NULL, a, "proc v1");
+create function audit_func() returns varchar(50)
+return "func v1";
+create view audit_view as select "view v1" as reason from dual;
+create trigger t7_proc_bi before insert on t7_proc for each row
+call audit_proc(NEW.a);
+create trigger t7_func_bi before insert on t7_func for each row
+insert into t7_audit values (NULL, NEW.a, audit_func());
+create trigger t7_view_bi before insert on t7_view for each row
+insert into t7_audit values (NULL, NEW.a, (select reason from audit_view));
+create trigger t7_table_bi before insert on t7_table for each row
+insert into t7_dependent_table values (NULL, NEW.a, "dependent table");
+create trigger t7_table_trigger_bi before insert on t7_dependent_table
+for each row set @nested="trigger v1";
+prepare stmt_proc from 'insert into t7_proc(a) value (?)';
+set @val=101;
+execute stmt_proc using @val;
+set @val=102;
+execute stmt_proc using @val;
+drop procedure audit_proc;
+create procedure audit_proc(a int)
+insert into t7_audit values (NULL, a, "proc v2");
+set @val=103;
+execute stmt_proc using @val;
+ERROR HY000: Prepared statement has been invalidated, because PROCEDURE 'test.audit_proc' has changed
+set @val=104;
+execute stmt_proc using @val;
+ERROR HY000: Prepared statement has been invalidated, because PROCEDURE 'test.audit_proc' has changed
+prepare stmt_func from 'insert into t7_func(a) value (?)';
+set @val=201;
+execute stmt_func using @val;
+set @val=202;
+execute stmt_func using @val;
+drop function audit_func;
+create function audit_func() returns varchar(50)
+return "func v2";
+set @val=203;
+execute stmt_func using @val;
+ERROR HY000: Prepared statement has been invalidated, because FUNCTION 'test.audit_func' has changed
+set @val=204;
+execute stmt_func using @val;
+ERROR HY000: Prepared statement has been invalidated, because FUNCTION 'test.audit_func' has changed
+prepare stmt_view from 'insert into t7_view(a) value (?)';
+set @val=301;
+execute stmt_view using @val;
+set @val=302;
+execute stmt_view using @val;
+drop view audit_view;
+create view audit_view as select "view v2" as reason from dual;
+set @val=303;
+execute stmt_view using @val;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.audit_view' has changed
+set @val=304;
+execute stmt_view using @val;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.audit_view' has changed
+prepare stmt_table from 'insert into t7_table(a) value (?)';
+set @val=401;
+execute stmt_table using @val;
+set @val=402;
+execute stmt_table using @val;
+alter table t7_dependent_table add column comments varchar(100) default NULL;
+set @val=403;
+execute stmt_table using @val;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t7_dependent_table' has changed
+set @val=404;
+execute stmt_table using @val;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t7_dependent_table' has changed
+alter table t7_dependent_table drop column comments;
+prepare stmt_table_trigger from 'insert into t7_table(a) value (?)';
+set @val=501;
+execute stmt_table_trigger using @val;
+select @nested;
+@nested
+trigger v1
+set @val=502;
+execute stmt_table_trigger using @val;
+select @nested;
+@nested
+trigger v1
+drop trigger t7_table_trigger_bi;
+create trigger t7_table_trigger_bi before insert on t7_dependent_table
+for each row set @nested="trigger v2";
+set @nested="not executed";
+set @val=503;
+execute stmt_table_trigger using @val;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t7_dependent_table' has changed
+select @nested;
+@nested
+not executed
+set @val=504;
+execute stmt_table_trigger using @val;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t7_dependent_table' has changed
+select @nested;
+@nested
+not executed
+select * from t7_audit order by new_a;
+old_a	new_a	reason
+NULL	101	proc v1
+NULL	102	proc v1
+NULL	201	func v1
+NULL	202	func v1
+NULL	301	view v1
+NULL	302	view v1
+drop table t7_proc;
+drop table t7_func;
+drop table t7_view;
+drop table t7_table;
+drop table t7_dependent_table;
+drop table t7_table_trigger;
+drop table t7_audit;
+drop procedure audit_proc;
+drop function audit_func;
+drop view audit_view;
+=====================================================================
+Testing 8: TABLE -> TEMPORARY TABLE transitions
+=====================================================================
+drop table if exists t8;
+create table t8(a int);
+prepare stmt from 'select * from t8';
+execute stmt;
+a
+execute stmt;
+a
+drop table t8;
+create temporary table t8(a int);
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t8' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t8' has changed
+drop table t8;
+=====================================================================
+Testing 9: TABLE -> VIEW transitions
+=====================================================================
+drop table if exists t9;
+drop table if exists t9_b;
+create table t9(a int);
+create table t9_b(a int);
+prepare stmt from 'select * from t9';
+execute stmt;
+a
+execute stmt;
+a
+drop table t9;
+create view t9 as select * from t9_b;
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t9' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t9' has changed
+drop view t9;
+drop table t9_b;
+=====================================================================
+Testing 10: TEMPORARY TABLE -> NOTHING transitions
+=====================================================================
+drop temporary table if exists t10;
+create temporary table t10(a int);
+prepare stmt from 'select * from t10';
+execute stmt;
+a
+execute stmt;
+a
+drop temporary table t10;
+execute stmt;
+ERROR 42S02: Table 'test.t10' doesn't exist
+execute stmt;
+ERROR 42S02: Table 'test.t10' doesn't exist
+=====================================================================
+Testing 11: TEMPORARY TABLE -> TABLE transitions
+=====================================================================
+drop table if exists t11;
+drop temporary table if exists t11;
+create table t11(a int);
+insert into t11(a) value (1);
+create temporary table t11(a int);
+prepare stmt from 'select * from t11';
+execute stmt;
+a
+execute stmt;
+a
+drop temporary table t11;
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t11' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t11' has changed
+select * from t11;
+a
+1
+drop table t11;
+=====================================================================
+Testing 12: TEMPORARY TABLE -> TEMPORARY TABLE (DDL) transitions
+=====================================================================
+drop temporary table if exists t12;
+create temporary table t12(a int);
+prepare stmt from 'select * from t12';
+execute stmt;
+a
+execute stmt;
+a
+drop temporary table t12;
+create temporary table t12(a int, b int);
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t12' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t12' has changed
+select * from t12;
+a	b
+drop table t12;
+=====================================================================
+Testing 13: TEMPORARY TABLE -> VIEW transitions
+=====================================================================
+drop temporary table if exists t13;
+drop table if exists t13_b;
+create temporary table t13(a int);
+create table t13_b(a int);
+prepare stmt from 'select * from t13';
+execute stmt;
+a
+execute stmt;
+a
+drop temporary table t13;
+create view t13 as select * from t13_b;
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t13' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t13' has changed
+drop view t13;
+drop table t13_b;
+=====================================================================
+Testing 14: VIEW -> NOTHING transitions
+=====================================================================
+drop view if exists t14;
+drop table if exists t14_b;
+create table t14_b(a int);
+create view t14 as select * from t14_b;
+prepare stmt from 'select * from t14';
+execute stmt;
+a
+execute stmt;
+a
+drop view t14;
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.t14' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.t14' has changed
+drop table t14_b;
+=====================================================================
+Testing 15: VIEW -> TABLE transitions
+=====================================================================
+drop view if exists t15;
+drop table if exists t15_b;
+create table t15_b(a int);
+create view t15 as select * from t15_b;
+prepare stmt from 'select * from t15';
+execute stmt;
+a
+execute stmt;
+a
+drop view t15;
+create table t15(a int);
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.t15' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.t15' has changed
+drop table t15_b;
+drop table t15;
+=====================================================================
+Testing 16: VIEW -> TEMPORARY TABLE transitions
+=====================================================================
+drop view if exists t16;
+drop table if exists t16_b;
+create table t16_b(a int);
+create view t16 as select * from t16_b;
+prepare stmt from 'select * from t16';
+execute stmt;
+a
+execute stmt;
+a
+drop view t16;
+create temporary table t16(a int);
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.t16' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.t16' has changed
+drop table t16_b;
+drop temporary table t16;
+=====================================================================
+Testing 17: VIEW -> VIEW (DDL) transitions
+=====================================================================
+drop view if exists t17;
+drop table if exists t17_b;
+create table t17_b(a int);
+insert into t17_b values (10), (20), (30);
+create view t17 as select a, 2*a as b, 3*a as c from t17_b;
+select * from t17;
+a	b	c
+10	20	30
+20	40	60
+30	60	90
+prepare stmt from 'select * from t17';
+execute stmt;
+a	b	c
+10	20	30
+20	40	60
+30	60	90
+execute stmt;
+a	b	c
+10	20	30
+20	40	60
+30	60	90
+drop view t17;
+create view t17 as select a, 2*a as b, 10*a as c from t17_b;
+select * from t17;
+a	b	c
+10	20	100
+20	40	200
+30	60	300
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.t17' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.t17' has changed
+drop table t17_b;
+drop view t17;
+=====================================================================
+Testing 18: VIEW -> VIEW (VIEW dependencies) transitions
+=====================================================================
+drop table if exists t18;
+drop table if exists t18_dependent_table;
+drop view if exists t18_func;
+drop view if exists t18_view;
+drop view if exists t18_table;
+drop function if exists view_func;
+drop view if exists view_view;
+create table t18(a int);
+insert into t18 values (1), (2), (3);
+create function view_func(x int) returns int
+return x+1;
+create view view_view as select "view v1" as reason from dual;
+create table t18_dependent_table(a int);
+create view t18_func as select a, view_func(a) as b from t18;
+create view t18_view as select a, reason as b from t18, view_view;
+create view t18_table as select * from t18;
+prepare stmt_func from 'select * from t18_func';
+execute stmt_func;
+a	b
+1	2
+2	3
+3	4
+execute stmt_func;
+a	b
+1	2
+2	3
+3	4
+drop function view_func;
+create function view_func(x int) returns int
+return x*x;
+execute stmt_func;
+ERROR HY000: Prepared statement has been invalidated, because FUNCTION 'test.view_func' has changed
+execute stmt_func;
+ERROR HY000: Prepared statement has been invalidated, because FUNCTION 'test.view_func' has changed
+prepare stmt_view from 'select * from t18_view';
+execute stmt_view;
+a	b
+1	view v1
+2	view v1
+3	view v1
+execute stmt_view;
+a	b
+1	view v1
+2	view v1
+3	view v1
+drop view view_view;
+create view view_view as select "view v2" as reason from dual;
+execute stmt_view;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.view_view' has changed
+execute stmt_view;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.view_view' has changed
+prepare stmt_table from 'select * from t18_table';
+execute stmt_table;
+a
+1
+2
+3
+execute stmt_table;
+a
+1
+2
+3
+alter table t18 add column comments varchar(50) default NULL;
+execute stmt_table;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t18' has changed
+execute stmt_table;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t18' has changed
+drop table t18;
+drop table t18_dependent_table;
+drop view t18_func;
+drop view t18_view;
+drop view t18_table;
+drop function view_func;
+drop view view_view;
+=====================================================================
+Testing 19: Special tables (INFORMATION_SCHEMA)
+=====================================================================
+drop procedure if exists proc_19;
+prepare stmt from
+'select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
+ from INFORMATION_SCHEMA.ROUTINES where
+ routine_name=\'proc_19\'';
+create procedure proc_19() select "hi there";
+execute stmt;
+ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE
+test	proc_19	PROCEDURE
+execute stmt;
+ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE
+test	proc_19	PROCEDURE
+drop procedure proc_19;
+create procedure proc_19() select "hi there, again";
+execute stmt;
+ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE
+test	proc_19	PROCEDURE
+execute stmt;
+ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE
+test	proc_19	PROCEDURE
+drop procedure proc_19;
+=====================================================================
+Testing 20: Special tables (log tables)
+=====================================================================
+prepare stmt from
+'select * from mysql.general_log where argument=\'IMPOSSIBLE QUERY STRING\'';
+execute stmt;
+event_time	user_host	thread_id	server_id	command_type	argument
+execute stmt;
+event_time	user_host	thread_id	server_id	command_type	argument
+execute stmt;
+event_time	user_host	thread_id	server_id	command_type	argument
+execute stmt;
+event_time	user_host	thread_id	server_id	command_type	argument
+=====================================================================
+Testing 21: Special tables (system tables)
+=====================================================================
+drop procedure if exists proc_21;
+prepare stmt from
+'select type, db, name from mysql.proc where name=\'proc_21\'';
+create procedure proc_21() select "hi there";
+execute stmt;
+type	db	name
+PROCEDURE	test	proc_21
+execute stmt;
+type	db	name
+PROCEDURE	test	proc_21
+drop procedure proc_21;
+create procedure proc_21() select "hi there, again";
+execute stmt;
+type	db	name
+PROCEDURE	test	proc_21
+execute stmt;
+type	db	name
+PROCEDURE	test	proc_21
+drop procedure proc_21;
+=====================================================================
+Testing 22: Special tables (views temp tables)
+=====================================================================
+drop table if exists t22_b;
+drop view if exists t22;
+create table t22_b(a int);
+create algorithm=temptable view t22 as select a*a as a2 from t22_b;
+show create view t22;
+View	Create View	character_set_client	collation_connection
+t22	CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `t22` AS select (`t22_b`.`a` * `t22_b`.`a`) AS `a2` from `t22_b`	latin1	latin1_swedish_ci
+prepare stmt from 'select * from t22';
+insert into t22_b values (1), (2), (3);
+execute stmt;
+a2
+1
+4
+9
+execute stmt;
+a2
+1
+4
+9
+insert into t22_b values (4), (5), (6);
+execute stmt;
+a2
+1
+4
+9
+16
+25
+36
+execute stmt;
+a2
+1
+4
+9
+16
+25
+36
+drop table t22_b;
+drop view t22;
+=====================================================================
+Testing 23: Special tables (internal join tables)
+=====================================================================
+drop table if exists t23_a;
+drop table if exists t23_b;
+create table t23_a(a int);
+create table t23_b(b int);
+prepare stmt from 'select * from t23_a join t23_b';
+insert into t23_a values (1), (2), (3);
+insert into t23_b values (10), (20), (30);
+execute stmt;
+a	b
+1	10
+2	10
+3	10
+1	20
+2	20
+3	20
+1	30
+2	30
+3	30
+execute stmt;
+a	b
+1	10
+2	10
+3	10
+1	20
+2	20
+3	20
+1	30
+2	30
+3	30
+insert into t23_a values (4);
+insert into t23_b values (40);
+execute stmt;
+a	b
+1	10
+2	10
+3	10
+4	10
+1	20
+2	20
+3	20
+4	20
+1	30
+2	30
+3	30
+4	30
+1	40
+2	40
+3	40
+4	40
+execute stmt;
+a	b
+1	10
+2	10
+3	10
+4	10
+1	20
+2	20
+3	20
+4	20
+1	30
+2	30
+3	30
+4	30
+1	40
+2	40
+3	40
+4	40
+drop table t23_a;
+drop table t23_b;
+=====================================================================
+Testing 24: Special statements
+=====================================================================
+drop table if exists t24_alter;
+create table t24_alter(a int);
+prepare stmt from 'alter table t24_alter add column b int';
+execute stmt;
+drop table t24_alter;
+create table t24_alter(a1 int, a2 int);
+execute stmt;
+alter table t24_alter drop column b;
+execute stmt;
+alter table t24_alter drop column b;
+execute stmt;
+drop table t24_alter;
+drop table if exists t24_repair;
+create table t24_repair(a int);
+insert into t24_repair values (1), (2), (3);
+prepare stmt from 'repair table t24_repair';
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_repair	repair	status	OK
+drop table t24_repair;
+create table t24_repair(a1 int, a2 int);
+insert into t24_repair values (1, 10), (2, 20), (3, 30);
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_repair	repair	status	OK
+alter table t24_repair add column b varchar(50) default NULL;
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_repair	repair	status	OK
+alter table t24_repair drop column b;
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_repair	repair	status	OK
+drop table t24_repair;
+drop table if exists t24_analyze;
+create table t24_analyze(a int);
+insert into t24_analyze values (1), (2), (3);
+prepare stmt from 'analyze table t24_analyze';
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_analyze	analyze	status	OK
+drop table t24_analyze;
+create table t24_analyze(a1 int, a2 int);
+insert into t24_analyze values (1, 10), (2, 20), (3, 30);
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_analyze	analyze	status	OK
+alter table t24_analyze add column b varchar(50) default NULL;
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_analyze	analyze	status	OK
+alter table t24_analyze drop column b;
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_analyze	analyze	status	OK
+drop table t24_analyze;
+drop table if exists t24_optimize;
+create table t24_optimize(a int);
+insert into t24_optimize values (1), (2), (3);
+prepare stmt from 'optimize table t24_optimize';
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_optimize	optimize	status	OK
+drop table t24_optimize;
+create table t24_optimize(a1 int, a2 int);
+insert into t24_optimize values (1, 10), (2, 20), (3, 30);
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_optimize	optimize	status	OK
+alter table t24_optimize add column b varchar(50) default NULL;
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_optimize	optimize	status	OK
+alter table t24_optimize drop column b;
+execute stmt;
+Table	Op	Msg_type	Msg_text
+test.t24_optimize	optimize	status	OK
+drop table t24_optimize;
+drop procedure if exists changing_proc;
+prepare stmt from 'show create procedure changing_proc';
+execute stmt;
+ERROR 42000: PROCEDURE changing_proc does not exist
+execute stmt;
+ERROR 42000: PROCEDURE changing_proc does not exist
+create procedure changing_proc() begin end;
+execute stmt;
+Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
+changing_proc		CREATE DEFINER=`root`@`localhost` PROCEDURE `changing_proc`()
+begin end	latin1	latin1_swedish_ci	latin1_swedish_ci
+execute stmt;
+Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
+changing_proc		CREATE DEFINER=`root`@`localhost` PROCEDURE `changing_proc`()
+begin end	latin1	latin1_swedish_ci	latin1_swedish_ci
+drop procedure changing_proc;
+create procedure changing_proc(x int, y int) begin end;
+execute stmt;
+Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
+changing_proc		CREATE DEFINER=`root`@`localhost` PROCEDURE `changing_proc`(x int, y int)
+begin end	latin1	latin1_swedish_ci	latin1_swedish_ci
+execute stmt;
+Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
+changing_proc		CREATE DEFINER=`root`@`localhost` PROCEDURE `changing_proc`(x int, y int)
+begin end	latin1	latin1_swedish_ci	latin1_swedish_ci
+drop procedure changing_proc;
+execute stmt;
+ERROR 42000: PROCEDURE changing_proc does not exist
+execute stmt;
+ERROR 42000: PROCEDURE changing_proc does not exist
+drop function if exists changing_func;
+prepare stmt from 'show create function changing_func';
+execute stmt;
+ERROR 42000: FUNCTION changing_func does not exist
+execute stmt;
+ERROR 42000: FUNCTION changing_func does not exist
+create function changing_func() returns int return 0;
+execute stmt;
+Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
+changing_func		CREATE DEFINER=`root`@`localhost` FUNCTION `changing_func`() RETURNS int(11)
+return 0	latin1	latin1_swedish_ci	latin1_swedish_ci
+execute stmt;
+Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
+changing_func		CREATE DEFINER=`root`@`localhost` FUNCTION `changing_func`() RETURNS int(11)
+return 0	latin1	latin1_swedish_ci	latin1_swedish_ci
+drop function changing_func;
+create function changing_func(x int, y int) returns int return x+y;
+execute stmt;
+Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
+changing_func		CREATE DEFINER=`root`@`localhost` FUNCTION `changing_func`(x int, y int) RETURNS int(11)
+return x+y	latin1	latin1_swedish_ci	latin1_swedish_ci
+execute stmt;
+Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
+changing_func		CREATE DEFINER=`root`@`localhost` FUNCTION `changing_func`(x int, y int) RETURNS int(11)
+return x+y	latin1	latin1_swedish_ci	latin1_swedish_ci
+drop function changing_func;
+execute stmt;
+ERROR 42000: FUNCTION changing_func does not exist
+execute stmt;
+ERROR 42000: FUNCTION changing_func does not exist
+=====================================================================
+Testing reported bugs
+=====================================================================
+drop table if exists table_12093;
+drop function if exists func_12093;
+drop function if exists func_12093_unrelated;
+drop procedure if exists proc_12093;
+create table table_12093(a int);
+create function func_12093()
+returns int
+begin
+return (select count(*) from table_12093);
+end//
+create procedure proc_12093(a int)
+begin
+select * from table_12093;
+end//
+create function func_12093_unrelated() returns int return 2;
+create procedure proc_12093_unrelated() begin end;
+prepare stmt_sf from 'select func_12093();';
+prepare stmt_sp from 'call proc_12093(func_12093())';
+execute stmt_sf;
+func_12093()
+0
+execute stmt_sp;
+a
+drop function func_12093_unrelated;
+drop procedure proc_12093_unrelated;
+execute stmt_sf;
+func_12093()
+0
+execute stmt_sp;
+a
+execute stmt_sf;
+func_12093()
+0
+execute stmt_sp;
+a
+deallocate prepare stmt_sf;
+deallocate prepare stmt_sp;
+drop table table_12093;
+drop function func_12093;
+drop procedure proc_12093;
+drop function if exists func_21294;
+create function func_21294() returns int return 10;
+prepare stmt from "select func_21294()";
+execute stmt;
+func_21294()
+10
+drop function func_21294;
+create function func_21294() returns int return 10;
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because FUNCTION 'test.func_21294' has changed
+drop function func_21294;
+create function func_21294() returns int return 20;
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because FUNCTION 'test.func_21294' has changed
+deallocate prepare stmt;
+drop function func_21294;
+drop table if exists t_27420_100;
+drop table if exists t_27420_101;
+drop view if exists v_27420;
+create table t_27420_100(a int);
+insert into t_27420_100 values (1), (2);
+create table t_27420_101(a int);
+insert into t_27420_101 values (1), (2);
+create view v_27420 as select t_27420_100.a X, t_27420_101.a Y
+from t_27420_100, t_27420_101
+where t_27420_100.a=t_27420_101.a;
+prepare stmt from 'select * from v_27420';
+execute stmt;
+X	Y
+1	1
+2	2
+drop view v_27420;
+create table v_27420(X int, Y int);
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.v_27420' has changed
+drop table v_27420;
+create table v_27420 (a int, b int, filler char(200));
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because VIEW 'test.v_27420' has changed
+deallocate prepare stmt;
+drop table t_27420_100;
+drop table t_27420_101;
+drop table v_27420;
+drop table if exists t_27430_1;
+drop table if exists t_27430_2;
+create table t_27430_1 (a int not null, oref int not null, key(a));
+insert into t_27430_1 values
+(1, 1),
+(1, 1234),
+(2, 3),
+(2, 1234),
+(3, 1234);
+create table t_27430_2 (a int not null, oref int not null);
+insert into t_27430_2 values
+(1, 1),
+(2, 2),
+(1234, 3),
+(1234, 4);
+prepare stmt from 
+'select oref, a, a in (select a from t_27430_1 where oref=t_27430_2.oref) Z from t_27430_2';
+execute stmt;
+oref	a	Z
+1	1	1
+2	2	0
+3	1234	0
+4	1234	0
+drop table t_27430_1, t_27430_2;
+create table t_27430_1 (a int, oref int, key(a));
+insert into t_27430_1 values 
+(1, 1),
+(1, NULL),
+(2, 3),
+(2, NULL),
+(3, NULL);
+create table t_27430_2 (a int, oref int);
+insert into t_27430_2 values
+(1, 1),
+(2,2),
+(NULL, 3),
+(NULL, 4);
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t_27430_2' has changed
+deallocate prepare stmt;
+drop table t_27430_1;
+drop table t_27430_2;
+drop table if exists t_27690_1;
+drop view if exists v_27690_1;
+drop table if exists v_27690_2;
+create table t_27690_1 (a int, b int);
+insert into t_27690_1 values (1,1),(2,2);
+create table v_27690_1 as select * from t_27690_1;
+create table v_27690_2 as select * from t_27690_1;
+prepare stmt from 'select * from v_27690_1, v_27690_2';
+execute stmt;
+a	b	a	b
+1	1	1	1
+2	2	1	1
+1	1	2	2
+2	2	2	2
+execute stmt;
+a	b	a	b
+1	1	1	1
+2	2	1	1
+1	1	2	2
+2	2	2	2
+drop table v_27690_1;
+execute stmt;
+ERROR 42S02: Table 'test.v_27690_1' doesn't exist
+execute stmt;
+ERROR 42S02: Table 'test.v_27690_1' doesn't exist
+create view v_27690_1 as select A.a, A.b from t_27690_1 A, t_27690_1 B;
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.v_27690_1' has changed
+execute stmt;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.v_27690_1' has changed
+deallocate prepare stmt;
+drop table t_27690_1;
+drop view v_27690_1;
+drop table v_27690_2;
diff -Nrup a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
--- a/mysql-test/r/sp-error.result	2007-11-01 14:52:50 -06:00
+++ b/mysql-test/r/sp-error.result	2007-11-26 09:14:49 -07:00
@@ -796,7 +796,7 @@ bug11834_2()
 10
 drop function bug11834_1;
 execute stmt;
-ERROR 42000: FUNCTION test.bug11834_2 does not exist
+ERROR HY000: Prepared statement has been invalidated, because FUNCTION 'test.bug11834_1' has changed
 deallocate prepare stmt;
 drop function bug11834_2;
 DROP FUNCTION IF EXISTS bug12953|
diff -Nrup a/mysql-test/r/sp.result b/mysql-test/r/sp.result
--- a/mysql-test/r/sp.result	2007-10-29 08:37:17 -06:00
+++ b/mysql-test/r/sp.result	2007-11-26 09:14:49 -07:00
@@ -4095,7 +4095,7 @@ create procedure bug10100pt(level int, l
 begin
 if level < lim then
 update t3 set a=level;
-FLUSH TABLES;
+## FLUSH TABLES;
 call bug10100pt(level+1, lim);
 else
 select * from t3;
@@ -4105,7 +4105,7 @@ create procedure bug10100pv(level int, l
 begin
 if level < lim then
 update v1 set a=level;
-FLUSH TABLES;
+## FLUSH TABLES;
 call bug10100pv(level+1, lim);
 else
 select * from v1;
@@ -4118,11 +4118,11 @@ if level < lim then
 select level;
 prepare stmt1 from "update t3 set a=a+2";
 execute stmt1;
-FLUSH TABLES;
+## FLUSH TABLES;
 execute stmt1;
-FLUSH TABLES;
+## FLUSH TABLES;
 execute stmt1;
-FLUSH TABLES;
+## FLUSH TABLES;
 deallocate prepare stmt1;
 execute stmt2;
 select * from t3;
@@ -4141,7 +4141,7 @@ select level;
 fetch c into lv;
 select lv;
 update t3 set a=level+lv;
-FLUSH TABLES;
+## FLUSH TABLES;
 call bug10100pc(level+1, lim);
 else
 select * from t3;
diff -Nrup a/mysql-test/r/sp_notembedded.result b/mysql-test/r/sp_notembedded.result
--- a/mysql-test/r/sp_notembedded.result	2007-09-05 12:02:59 -06:00
+++ b/mysql-test/r/sp_notembedded.result	2007-11-26 09:14:49 -07:00
@@ -123,7 +123,7 @@ create procedure bug10100pt(level int, l
 begin
 if level < lim then
 update t3 set a=level;
-FLUSH TABLES;
+## FLUSH TABLES;
 call bug10100pt(level+1, lim);
 else
 select * from t3;
@@ -133,7 +133,7 @@ create procedure bug10100pv(level int, l
 begin
 if level < lim then
 update v1 set a=level;
-FLUSH TABLES;
+## FLUSH TABLES;
 call bug10100pv(level+1, lim);
 else
 select * from v1;
@@ -146,11 +146,11 @@ if level < lim then
 select level;
 prepare stmt1 from "update t3 set a=a+2";
 execute stmt1;
-FLUSH TABLES;
+## FLUSH TABLES;
 execute stmt1;
-FLUSH TABLES;
+## FLUSH TABLES;
 execute stmt1;
-FLUSH TABLES;
+## FLUSH TABLES;
 deallocate prepare stmt1;
 execute stmt2;
 select * from t3;
@@ -169,7 +169,7 @@ select level;
 fetch c into lv;
 select lv;
 update t3 set a=level+lv;
-FLUSH TABLES;
+## FLUSH TABLES;
 call bug10100pc(level+1, lim);
 else
 select * from t3;
diff -Nrup a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
--- a/mysql-test/r/trigger.result	2007-07-19 09:40:42 -06:00
+++ b/mysql-test/r/trigger.result	2007-11-26 09:14:49 -07:00
@@ -809,6 +809,7 @@ create procedure p1() insert into t1 val
 call p1();
 drop trigger t1_bi;
 execute stmt1;
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t1' has changed
 call p1();
 deallocate prepare stmt1;
 drop procedure p1;
@@ -820,7 +821,7 @@ call p1();
 drop trigger t1_bi;
 create trigger t1_bi after insert on t1 for each row insert into t3 values (new.id);
 execute stmt1;
-ERROR 42S02: Table 'test.t3' doesn't exist
+ERROR HY000: Prepared statement has been invalidated, because TABLE 'test.t1' has changed
 call p1();
 ERROR 42S02: Table 'test.t3' doesn't exist
 deallocate prepare stmt1;
diff -Nrup a/mysql-test/t/ps.test b/mysql-test/t/ps.test
--- a/mysql-test/t/ps.test	2007-11-02 05:47:15 -06:00
+++ b/mysql-test/t/ps.test	2007-11-26 09:14:49 -07:00
@@ -497,6 +497,7 @@ drop table t1, t2;
 # Bug#19399 "Stored Procedures 'Lost Connection' when dropping/creating
 #            tables"
 # Check that multi-delete tables are also cleaned up before re-execution.
+# Update: can not be tested anymore due to ER_PS_INVALIDATED
 # 
 --disable_warnings
 drop table if exists t1;
@@ -507,13 +508,16 @@ prepare stmt from "delete t1 from t1 whe
 drop temporary table t1;
 create temporary table if not exists t1 (a1 int);
 # the server crashed on  the next statement without the fix
+--error ER_PS_INVALIDATED
 execute stmt;
 drop temporary table t1;
 create temporary table if not exists t1 (a1 int);
 # the problem was in memory corruption: repeat the test just in case
+--error ER_PS_INVALIDATED
 execute stmt;
 drop temporary table t1;
 create temporary table if not exists t1 (a1 int);
+--error ER_PS_INVALIDATED
 execute stmt;
 drop temporary table t1;
 deallocate prepare stmt;
@@ -970,33 +974,6 @@ show status like 'prepared_stmt_count';
 set global max_prepared_stmt_count= @old_max_prepared_stmt_count;
 --enable_ps_protocol
 
-
-#
-# Bug#19399 "Stored Procedures 'Lost Connection' when dropping/creating
-#            tables"
-# Check that multi-delete tables are also cleaned up before re-execution.
-# 
---disable_warnings
-drop table if exists t1;
-create temporary table if not exists t1 (a1 int);
---enable_warnings
-# exact delete syntax is essential
-prepare stmt from "delete t1 from t1 where (cast(a1/3 as unsigned) * 3) = a1";
-drop temporary table t1;
-create temporary table if not exists t1 (a1 int);
-# the server crashed on  the next statement without the fix
-execute stmt;
-drop temporary table t1;
-create temporary table if not exists t1 (a1 int);
-# the problem was in memory corruption: repeat the test just in case
-execute stmt;
-drop temporary table t1;
-create temporary table if not exists t1 (a1 int);
-execute stmt;
-drop temporary table t1;
-deallocate prepare stmt;
-
-
 #
 # BUG#22085: Crash on the execution of a prepared statement that
 #            uses an IN subquery with aggregate functions in HAVING 
@@ -1493,19 +1470,15 @@ prepare stmt_optimize from "optimize tab
 prepare stmt_show from "show tables like 't1'";
 prepare stmt_truncate from "truncate table t1";
 prepare stmt_drop from "drop table t1";
-# Drop the table that was used to prepare INSERT/UPDATE/DELETE: we will
-# create a new one by executing stmt_create
-drop table t1;
-# Switch the current database
-use test;
+
 # Check that all prepared statements operate on the database that was
 # active at PREPARE
-execute stmt_create;
-# should return empty set
-show tables like 't1';
-use mysqltest_long_database_name_to_thrash_heap;
-show tables like 't1';
+
+# Switch the current database
 use test;
+
+# We can't test stmt_create now,
+# since this would cause ER_PS_INVALIDATED errors
 execute stmt_insert;
 select * from mysqltest_long_database_name_to_thrash_heap.t1;
 execute stmt_update;
@@ -1524,10 +1497,20 @@ execute stmt_drop;
 show tables like 't1';
 use mysqltest_long_database_name_to_thrash_heap;
 show tables like 't1';
+
+# Switch the current database
+use test;
+execute stmt_create;
+# should return empty set
+show tables like 't1';
+use mysqltest_long_database_name_to_thrash_heap;
+show tables like 't1';
+
 #
 # Attempt a statement PREPARE when there is no current database:
 # is expected to return an error.
 #
+use mysqltest_long_database_name_to_thrash_heap;
 drop database mysqltest_long_database_name_to_thrash_heap;
 --error ER_NO_DB_ERROR
 prepare stmt_create from "create table t1 (i int)";
@@ -2751,7 +2734,9 @@ INSERT INTO t2 VALUES (4, 5);
 connection conn1;
 
 reap;
+--error ER_PS_INVALIDATED
 EXECUTE stmt1;
+--error ER_PS_INVALIDATED
 EXECUTE stmt2;
 SELECT * FROM t2;
 
diff -Nrup a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test
--- a/mysql-test/t/ps_1general.test	2007-08-15 02:18:40 -06:00
+++ b/mysql-test/t/ps_1general.test	2007-11-26 09:14:50 -07:00
@@ -161,6 +161,8 @@ create table t5
   c int
 );
 insert into t5( a, b, c) values( 9, 'recreated table', 9);
+# table is the same: pass or fail is implementation dependent
+--error ER_PS_INVALIDATED
 execute stmt2 ;
 drop table t5 ;
 # 2. drop + create table (same column names/types but different order)
@@ -172,6 +174,8 @@ create table t5
   b char(30)
 );
 insert into t5( a, b, c) values( 9, 'recreated table', 9);
+# Table is different, should fail
+--error ER_PS_INVALIDATED
 execute stmt2 ;
 drop table t5 ;
 # 3. drop + create table (same column names/types/order+extra column) 
@@ -184,6 +188,8 @@ create table t5
   d timestamp default current_timestamp
 );
 insert into t5( a, b, c) values( 9, 'recreated table', 9);
+# Table is different, should fail
+--error ER_PS_INVALIDATED
 execute stmt2 ;
 drop table t5 ;
 # 4. drop + create table (same column names/types, different order +
@@ -196,6 +202,8 @@ create table t5
   c int
 );
 insert into t5( a, b, c) values( 9, 'recreated table', 9);
+# Table is different, should fail
+--error ER_PS_INVALIDATED
 execute stmt2 ;
 drop table t5 ;
 # 5. drop + create table (same column names/order, different types)
@@ -207,6 +215,8 @@ create table t5
   c int
 );
 insert into t5( b, c) values( 'recreated table', 9);
+# Table is different, should fail
+--error ER_PS_INVALIDATED
 execute stmt2 ;
 drop table t5 ;
 # 6. drop + create table (same column types/order, different names) 
@@ -218,7 +228,8 @@ create table t5
   f3 int
 );
 insert into t5( f1, f2, f3) values( 9, 'recreated table', 9);
---error 1054
+# Table is different, should fail
+--error ER_PS_INVALIDATED
 execute stmt2 ;
 drop table t5 ;
 
diff -Nrup a/mysql-test/t/ps_ddl.test b/mysql-test/t/ps_ddl.test
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/t/ps_ddl.test	2007-11-26 09:14:52 -07:00
@@ -0,0 +1,1270 @@
+#
+# Testing the behavior of 'PREPARE', 'DDL', 'EXECUTE' scenarios
+#
+# Background:
+# In a statement like "select * from t1", t1 can be:
+# - nothing (the table does not exist)
+# - a real table
+# - a temporary table
+# - a view
+#
+# Changing the nature of "t1" between a PREPARE and an EXECUTE
+# can invalidate the internal state of a prepared statement, so that,
+# during the execute, the server should:
+# - at the very minimum, detect state changes and fail to execute a
+# statement, instead of crashing the server or returning wrong results
+# - optionally, "RE-PREPARE" the statement to restore a valid internal
+# state.
+#
+# Also, changing the physical structure of "t1", by:
+# - changing the definition of t1 itself (DDL on tables, views)
+# - changing TRIGGERs associated with a table
+# - changing PROCEDURE, FUNCTION referenced by a TRIGGER body,
+# - changing PROCEDURE, FUNCTION referenced by a VIEW body,
+# impacts the internal structure of a prepared statement, and should
+# cause the same verifications at execute time to be performed.
+#
+# This test provided in this file cover the different state transitions
+# between a PREPARE and an EXECUTE, and are organized as follows:
+# - Part  1: NOTHING -> TABLE
+# - Part  2: NOTHING -> TEMPORARY TABLE
+# - Part  3: NOTHING -> VIEW
+# - Part  4: TABLE -> NOTHING
+# - Part  5: TABLE -> TABLE (DDL)
+# - Part  6: TABLE -> TABLE (TRIGGER)
+# - Part  7: TABLE -> TABLE (TRIGGER dependencies)
+# - Part  8: TABLE -> TEMPORARY TABLE
+# - Part  9: TABLE -> VIEW
+# - Part 10: TEMPORARY TABLE -> NOTHING
+# - Part 11: TEMPORARY TABLE -> TABLE
+# - Part 12: TEMPORARY TABLE -> TEMPORARY TABLE (DDL)
+# - Part 13: TEMPORARY TABLE -> VIEW
+# - Part 14: VIEW -> NOTHING
+# - Part 15: VIEW -> TABLE
+# - Part 16: VIEW -> TEMPORARY TABLE
+# - Part 17: VIEW -> VIEW (DDL)
+# - Part 18: VIEW -> VIEW (VIEW dependencies)
+# - Part 19: Special tables (INFORMATION_SCHEMA)
+# - Part 20: Special tables (log tables)
+# - Part 21: Special tables (system tables)
+# - Part 22: Special tables (views temp tables)
+# - Part 23: Special tables (internal join tables)
+# - Part 24: Special statements
+
+--echo =====================================================================
+--echo Testing 1: NOTHING -> TABLE transitions
+--echo =====================================================================
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+# can not be tested since prepare failed
+--error ER_NO_SUCH_TABLE
+prepare stmt from 'select * from t1';
+
+--echo =====================================================================
+--echo Testing 2: NOTHING -> TEMPORARY TABLE transitions
+--echo =====================================================================
+
+# can not be tested
+
+--echo =====================================================================
+--echo Testing 3: NOTHING -> VIEW transitions
+--echo =====================================================================
+
+# can not be tested
+
+--echo =====================================================================
+--echo Testing 4: TABLE -> NOTHING transitions
+--echo =====================================================================
+
+--disable_warnings
+drop table if exists t4;
+--enable_warnings
+
+create table t4(a int);
+
+prepare stmt from 'select * from t4';
+execute stmt;
+execute stmt;
+
+drop table t4;
+--error ER_NO_SUCH_TABLE
+execute stmt;
+--error ER_NO_SUCH_TABLE
+execute stmt;
+
+--echo =====================================================================
+--echo Testing 5: TABLE -> TABLE (DDL) transitions
+--echo =====================================================================
+
+--disable_warnings
+drop table if exists t5;
+--enable_warnings
+
+create table t5(a int);
+
+prepare stmt from 'select * from t5';
+execute stmt;
+execute stmt;
+
+alter table t5 add column (b int);
+
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop table t5;
+
+--echo =====================================================================
+--echo Testing 6: TABLE -> TABLE (TRIGGER) transitions
+--echo =====================================================================
+
+#
+# Test 6-a: adding a relevant trigger
+# Test 6-b: adding an irrelevant trigger
+# Test 6-c: removing a relevant trigger
+# Test 6-d: removing an irrelevant trigger
+#
+
+--disable_warnings
+drop table if exists t6;
+--enable_warnings
+
+create table t6(a int);
+
+prepare stmt from 'insert into t6(a) value (?)';
+set @val=1;
+execute stmt using @val;
+set @val=2;
+execute stmt using @val;
+
+# Relevant trigger: execute should fail
+create trigger t6_bi before insert on t6 for each row
+  begin end;
+
+set @val=3;
+--error ER_PS_INVALIDATED
+execute stmt using @val;
+set @val=4;
+--error ER_PS_INVALIDATED
+execute stmt using @val;
+
+prepare stmt from 'insert into t6(a) value (?)';
+set @val=5;
+execute stmt using @val;
+set @val=6;
+execute stmt using @val;
+
+# Unrelated trigger: execute can pass of fail, implementation dependent
+create trigger t6_bd before delete on t6 for each row
+  begin end;
+
+set @val=7;
+--error ER_PS_INVALIDATED
+execute stmt using @val;
+set @val=8;
+--error ER_PS_INVALIDATED
+execute stmt using @val;
+
+prepare stmt from 'insert into t6(a) value (?)';
+set @val=9;
+execute stmt using @val;
+set @val=10;
+execute stmt using @val;
+
+drop trigger t6_bi;
+
+set @val=11;
+--error ER_PS_INVALIDATED
+execute stmt using @val;
+set @val=12;
+--error ER_PS_INVALIDATED
+execute stmt using @val;
+
+prepare stmt from 'insert into t6(a) value (?)';
+set @val=13;
+execute stmt using @val;
+set @val=14;
+execute stmt using @val;
+
+drop trigger t6_bd;
+
+set @val=15;
+--error ER_PS_INVALIDATED
+execute stmt using @val;
+set @val=16;
+--error ER_PS_INVALIDATED
+execute stmt using @val;
+
+select * from t6 order by a;
+drop table t6;
+
+--echo =====================================================================
+--echo Testing 7: TABLE -> TABLE (TRIGGER dependencies) transitions
+--echo =====================================================================
+
+#
+# Test 7-a: dependent PROCEDURE has changed
+# Test 7-b: dependent FUNCTION has changed
+# Test 7-c: dependent VIEW has changed
+# Test 7-d: dependent TABLE has changed
+# Test 7-e: dependent TABLE TRIGGER has changed
+#
+
+--disable_warnings
+drop table if exists t7_proc;
+drop table if exists t7_func;
+drop table if exists t7_view;
+drop table if exists t7_table;
+drop table if exists t7_dependent_table;
+drop table if exists t7_table_trigger;
+drop table if exists t7_audit;
+drop procedure if exists audit_proc;
+drop function if exists audit_func;
+drop view if exists audit_view;
+--enable_warnings
+
+create table t7_proc(a int);
+create table t7_func(a int);
+create table t7_view(a int);
+create table t7_table(a int);
+create table t7_table_trigger(a int);
+
+create table t7_audit(old_a int, new_a int, reason varchar(50));
+create table t7_dependent_table(old_a int, new_a int, reason varchar(50));
+
+create procedure audit_proc(a int)
+  insert into t7_audit values (NULL, a, "proc v1");
+
+create function audit_func() returns varchar(50)
+  return "func v1";
+
+create view audit_view as select "view v1" as reason from dual;
+
+create trigger t7_proc_bi before insert on t7_proc for each row
+  call audit_proc(NEW.a);
+
+create trigger t7_func_bi before insert on t7_func for each row
+  insert into t7_audit values (NULL, NEW.a, audit_func());
+
+create trigger t7_view_bi before insert on t7_view for each row
+  insert into t7_audit values (NULL, NEW.a, (select reason from audit_view));
+
+create trigger t7_table_bi before insert on t7_table for each row
+  insert into t7_dependent_table values (NULL, NEW.a, "dependent table");
+
+create trigger t7_table_trigger_bi before insert on t7_dependent_table
+  for each row set @nested="trigger v1";
+
+# Limitation: Stored procedures versions based on a timestamp
+sleep 2;
+
+prepare stmt_proc from 'insert into t7_proc(a) value (?)';
+set @val=101;
+execute stmt_proc using @val;
+set @val=102;
+execute stmt_proc using @val;
+
+drop procedure audit_proc;
+
+create procedure audit_proc(a int)
+  insert into t7_audit values (NULL, a, "proc v2");
+
+set @val=103;
+--error ER_PS_INVALIDATED
+execute stmt_proc using @val;
+set @val=104;
+--error ER_PS_INVALIDATED
+execute stmt_proc using @val;
+
+
+prepare stmt_func from 'insert into t7_func(a) value (?)';
+set @val=201;
+execute stmt_func using @val;
+set @val=202;
+execute stmt_func using @val;
+
+drop function audit_func;
+
+create function audit_func() returns varchar(50)
+  return "func v2";
+
+set @val=203;
+--error ER_PS_INVALIDATED
+execute stmt_func using @val;
+set @val=204;
+--error ER_PS_INVALIDATED
+execute stmt_func using @val;
+
+prepare stmt_view from 'insert into t7_view(a) value (?)';
+set @val=301;
+execute stmt_view using @val;
+set @val=302;
+execute stmt_view using @val;
+
+drop view audit_view;
+
+create view audit_view as select "view v2" as reason from dual;
+
+set @val=303;
+--error ER_PS_INVALIDATED
+execute stmt_view using @val;
+set @val=304;
+--error ER_PS_INVALIDATED
+execute stmt_view using @val;
+
+
+prepare stmt_table from 'insert into t7_table(a) value (?)';
+set @val=401;
+execute stmt_table using @val;
+set @val=402;
+execute stmt_table using @val;
+
+alter table t7_dependent_table add column comments varchar(100) default NULL;
+
+set @val=403;
+--error ER_PS_INVALIDATED
+execute stmt_table using @val;
+set @val=404;
+--error ER_PS_INVALIDATED
+execute stmt_table using @val;
+
+alter table t7_dependent_table drop column comments;
+
+prepare stmt_table_trigger from 'insert into t7_table(a) value (?)';
+set @val=501;
+execute stmt_table_trigger using @val;
+select @nested;
+set @val=502;
+execute stmt_table_trigger using @val;
+select @nested;
+
+drop trigger t7_table_trigger_bi;
+
+create trigger t7_table_trigger_bi before insert on t7_dependent_table
+  for each row set @nested="trigger v2";
+
+set @nested="not executed";
+
+set @val=503;
+--error ER_PS_INVALIDATED
+execute stmt_table_trigger using @val;
+select @nested;
+set @val=504;
+--error ER_PS_INVALIDATED
+execute stmt_table_trigger using @val;
+select @nested;
+
+select * from t7_audit order by new_a;
+
+drop table t7_proc;
+drop table t7_func;
+drop table t7_view;
+drop table t7_table;
+drop table t7_dependent_table;
+drop table t7_table_trigger;
+drop table t7_audit;
+drop procedure audit_proc;
+drop function audit_func;
+drop view audit_view;
+
+--echo =====================================================================
+--echo Testing 8: TABLE -> TEMPORARY TABLE transitions
+--echo =====================================================================
+
+--disable_warnings
+drop table if exists t8;
+--enable_warnings
+
+create table t8(a int);
+
+prepare stmt from 'select * from t8';
+execute stmt;
+execute stmt;
+
+drop table t8;
+create temporary table t8(a int);
+
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop table t8;
+
+--echo =====================================================================
+--echo Testing 9: TABLE -> VIEW transitions
+--echo =====================================================================
+
+--disable_warnings
+drop table if exists t9;
+drop table if exists t9_b;
+--enable_warnings
+
+create table t9(a int);
+create table t9_b(a int);
+
+prepare stmt from 'select * from t9';
+execute stmt;
+execute stmt;
+
+drop table t9;
+create view t9 as select * from t9_b;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop view t9;
+drop table t9_b;
+
+--echo =====================================================================
+--echo Testing 10: TEMPORARY TABLE -> NOTHING transitions
+--echo =====================================================================
+
+--disable_warnings
+drop temporary table if exists t10;
+--enable_warnings
+
+create temporary table t10(a int);
+
+prepare stmt from 'select * from t10';
+execute stmt;
+execute stmt;
+
+drop temporary table t10;
+--error ER_NO_SUCH_TABLE
+execute stmt;
+--error ER_NO_SUCH_TABLE
+execute stmt;
+
+--echo =====================================================================
+--echo Testing 11: TEMPORARY TABLE -> TABLE transitions
+--echo =====================================================================
+
+--disable_warnings
+drop table if exists t11;
+drop temporary table if exists t11;
+--enable_warnings
+
+create table t11(a int);
+insert into t11(a) value (1);
+create temporary table t11(a int);
+
+prepare stmt from 'select * from t11';
+execute stmt;
+execute stmt;
+
+drop temporary table t11;
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+select * from t11;
+drop table t11;
+
+--echo =====================================================================
+--echo Testing 12: TEMPORARY TABLE -> TEMPORARY TABLE (DDL) transitions
+--echo =====================================================================
+
+--disable_warnings
+drop temporary table if exists t12;
+--enable_warnings
+
+create temporary table t12(a int);
+
+prepare stmt from 'select * from t12';
+execute stmt;
+execute stmt;
+
+drop temporary table t12;
+create temporary table t12(a int, b int);
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+select * from t12;
+drop table t12;
+
+--echo =====================================================================
+--echo Testing 13: TEMPORARY TABLE -> VIEW transitions
+--echo =====================================================================
+
+--disable_warnings
+drop temporary table if exists t13;
+drop table if exists t13_b;
+--enable_warnings
+
+create temporary table t13(a int);
+create table t13_b(a int);
+
+prepare stmt from 'select * from t13';
+execute stmt;
+execute stmt;
+
+drop temporary table t13;
+create view t13 as select * from t13_b;
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop view t13;
+drop table t13_b;
+
+--echo =====================================================================
+--echo Testing 14: VIEW -> NOTHING transitions
+--echo =====================================================================
+
+--disable_warnings
+drop view if exists t14;
+drop table if exists t14_b;
+--enable_warnings
+
+create table t14_b(a int);
+create view t14 as select * from t14_b;
+
+prepare stmt from 'select * from t14';
+execute stmt;
+execute stmt;
+
+drop view t14;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop table t14_b;
+
+--echo =====================================================================
+--echo Testing 15: VIEW -> TABLE transitions
+--echo =====================================================================
+
+--disable_warnings
+drop view if exists t15;
+drop table if exists t15_b;
+--enable_warnings
+
+create table t15_b(a int);
+create view t15 as select * from t15_b;
+
+prepare stmt from 'select * from t15';
+execute stmt;
+execute stmt;
+
+drop view t15;
+create table t15(a int);
+
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop table t15_b;
+drop table t15;
+
+--echo =====================================================================
+--echo Testing 16: VIEW -> TEMPORARY TABLE transitions
+--echo =====================================================================
+
+--disable_warnings
+drop view if exists t16;
+drop table if exists t16_b;
+--enable_warnings
+
+create table t16_b(a int);
+create view t16 as select * from t16_b;
+
+prepare stmt from 'select * from t16';
+execute stmt;
+execute stmt;
+
+drop view t16;
+create temporary table t16(a int);
+
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop table t16_b;
+drop temporary table t16;
+
+--echo =====================================================================
+--echo Testing 17: VIEW -> VIEW (DDL) transitions
+--echo =====================================================================
+
+--disable_warnings
+drop view if exists t17;
+drop table if exists t17_b;
+--enable_warnings
+
+create table t17_b(a int);
+insert into t17_b values (10), (20), (30);
+
+create view t17 as select a, 2*a as b, 3*a as c from t17_b;
+select * from t17;
+
+prepare stmt from 'select * from t17';
+execute stmt;
+execute stmt;
+
+drop view t17;
+create view t17 as select a, 2*a as b, 10*a as c from t17_b;
+select * from t17;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop table t17_b;
+drop view t17;
+
+--echo =====================================================================
+--echo Testing 18: VIEW -> VIEW (VIEW dependencies) transitions
+--echo =====================================================================
+
+#
+# Test 18-a: dependent PROCEDURE has changed (via a trigger)
+# Test 18-b: dependent FUNCTION has changed
+# Test 18-c: dependent VIEW has changed
+# Test 18-d: dependent TABLE has changed
+# Test 18-e: dependent TABLE TRIGGER has changed
+#
+
+--disable_warnings
+drop table if exists t18;
+drop table if exists t18_dependent_table;
+drop view if exists t18_func;
+drop view if exists t18_view;
+drop view if exists t18_table;
+drop function if exists view_func;
+drop view if exists view_view;
+--enable_warnings
+
+# TODO: insertable view -> trigger
+# TODO: insertable view -> trigger -> proc ?
+
+create table t18(a int);
+insert into t18 values (1), (2), (3);
+
+create function view_func(x int) returns int
+  return x+1;
+
+create view view_view as select "view v1" as reason from dual;
+
+create table t18_dependent_table(a int);
+
+create view t18_func as select a, view_func(a) as b from t18;
+create view t18_view as select a, reason as b from t18, view_view;
+create view t18_table as select * from t18;
+
+# Limitation: Stored procedures versions based on a timestamp
+sleep 2;
+
+prepare stmt_func from 'select * from t18_func';
+execute stmt_func;
+execute stmt_func;
+
+drop function view_func;
+create function view_func(x int) returns int
+  return x*x;
+
+--error ER_PS_INVALIDATED
+execute stmt_func;
+--error ER_PS_INVALIDATED
+execute stmt_func;
+
+prepare stmt_view from 'select * from t18_view';
+execute stmt_view;
+execute stmt_view;
+
+drop view view_view;
+create view view_view as select "view v2" as reason from dual;
+
+--error ER_PS_INVALIDATED
+execute stmt_view;
+--error ER_PS_INVALIDATED
+execute stmt_view;
+
+prepare stmt_table from 'select * from t18_table';
+execute stmt_table;
+execute stmt_table;
+
+alter table t18 add column comments varchar(50) default NULL;
+
+--error ER_PS_INVALIDATED
+execute stmt_table;
+--error ER_PS_INVALIDATED
+execute stmt_table;
+
+drop table t18;
+drop table t18_dependent_table;
+drop view t18_func;
+drop view t18_view;
+drop view t18_table;
+drop function view_func;
+drop view view_view;
+
+--echo =====================================================================
+--echo Testing 19: Special tables (INFORMATION_SCHEMA)
+--echo =====================================================================
+
+--disable_warnings
+drop procedure if exists proc_19;
+--enable_warnings
+
+# Using a temporary table internally should not confuse the prepared
+# statement code, and should not raise ER_PS_INVALIDATED errors
+prepare stmt from
+ 'select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
+ from INFORMATION_SCHEMA.ROUTINES where
+ routine_name=\'proc_19\'';
+
+create procedure proc_19() select "hi there";
+
+execute stmt;
+execute stmt;
+
+drop procedure proc_19;
+create procedure proc_19() select "hi there, again";
+
+execute stmt;
+execute stmt;
+
+drop procedure proc_19;
+
+--echo =====================================================================
+--echo Testing 20: Special tables (log tables)
+--echo =====================================================================
+
+prepare stmt from
+ 'select * from mysql.general_log where argument=\'IMPOSSIBLE QUERY STRING\'';
+
+execute stmt;
+execute stmt;
+execute stmt;
+execute stmt;
+
+--echo =====================================================================
+--echo Testing 21: Special tables (system tables)
+--echo =====================================================================
+
+--disable_warnings
+drop procedure if exists proc_21;
+--enable_warnings
+
+prepare stmt from
+ 'select type, db, name from mysql.proc where name=\'proc_21\'';
+
+create procedure proc_21() select "hi there";
+
+execute stmt;
+execute stmt;
+
+drop procedure proc_21;
+create procedure proc_21() select "hi there, again";
+
+execute stmt;
+execute stmt;
+
+drop procedure proc_21;
+
+--echo =====================================================================
+--echo Testing 22: Special tables (views temp tables)
+--echo =====================================================================
+
+--disable_warnings
+drop table if exists t22_b;
+drop view if exists t22;
+--enable_warnings
+
+create table t22_b(a int);
+
+create algorithm=temptable view t22 as select a*a as a2 from t22_b;
+
+# Using a temporary table internally should not confuse the prepared
+# statement code, and should not raise ER_PS_INVALIDATED errors
+show create view t22;
+
+prepare stmt from 'select * from t22';
+
+insert into t22_b values (1), (2), (3);
+execute stmt;
+execute stmt;
+
+insert into t22_b values (4), (5), (6);
+execute stmt;
+execute stmt;
+
+drop table t22_b;
+drop view t22;
+
+--echo =====================================================================
+--echo Testing 23: Special tables (internal join tables)
+--echo =====================================================================
+
+--disable_warnings
+drop table if exists t23_a;
+drop table if exists t23_b;
+--enable_warnings
+
+create table t23_a(a int);
+create table t23_b(b int);
+
+# Using a temporary table internally should not confuse the prepared
+# statement code, and should not raise ER_PS_INVALIDATED errors
+prepare stmt from 'select * from t23_a join t23_b';
+
+insert into t23_a values (1), (2), (3);
+insert into t23_b values (10), (20), (30);
+execute stmt;
+execute stmt;
+
+insert into t23_a values (4);
+insert into t23_b values (40);
+execute stmt;
+execute stmt;
+
+drop table t23_a;
+drop table t23_b;
+
+--echo =====================================================================
+--echo Testing 24: Special statements
+--echo =====================================================================
+
+# SQLCOM_ALTER_TABLE:
+
+--disable_warnings
+drop table if exists t24_alter;
+--enable_warnings
+
+create table t24_alter(a int);
+
+prepare stmt from 'alter table t24_alter add column b int';
+execute stmt;
+
+drop table t24_alter;
+create table t24_alter(a1 int, a2 int);
+
+# t24_alter has changed, and it's not a problem
+execute stmt;
+
+alter table t24_alter drop column b;
+execute stmt;
+
+alter table t24_alter drop column b;
+execute stmt;
+
+drop table t24_alter;
+
+# SQLCOM_REPAIR:
+
+--disable_warnings
+drop table if exists t24_repair;
+--enable_warnings
+
+create table t24_repair(a int);
+insert into t24_repair values (1), (2), (3);
+
+prepare stmt from 'repair table t24_repair';
+execute stmt;
+
+drop table t24_repair;
+create table t24_repair(a1 int, a2 int);
+insert into t24_repair values (1, 10), (2, 20), (3, 30);
+
+# t24_repair has changed, and it's not a problem
+execute stmt;
+
+alter table t24_repair add column b varchar(50) default NULL;
+execute stmt;
+
+alter table t24_repair drop column b;
+execute stmt;
+
+drop table t24_repair;
+
+# SQLCOM_ANALYZE:
+
+--disable_warnings
+drop table if exists t24_analyze;
+--enable_warnings
+
+create table t24_analyze(a int);
+insert into t24_analyze values (1), (2), (3);
+
+prepare stmt from 'analyze table t24_analyze';
+execute stmt;
+
+drop table t24_analyze;
+create table t24_analyze(a1 int, a2 int);
+insert into t24_analyze values (1, 10), (2, 20), (3, 30);
+
+# t24_analyze has changed, and it's not a problem
+execute stmt;
+
+alter table t24_analyze add column b varchar(50) default NULL;
+execute stmt;
+
+alter table t24_analyze drop column b;
+execute stmt;
+
+drop table t24_analyze;
+
+# SQLCOM_OPTIMIZE:
+
+--disable_warnings
+drop table if exists t24_optimize;
+--enable_warnings
+
+create table t24_optimize(a int);
+insert into t24_optimize values (1), (2), (3);
+
+prepare stmt from 'optimize table t24_optimize';
+execute stmt;
+
+drop table t24_optimize;
+create table t24_optimize(a1 int, a2 int);
+insert into t24_optimize values (1, 10), (2, 20), (3, 30);
+
+# t24_optimize has changed, and it's not a problem
+execute stmt;
+
+alter table t24_optimize add column b varchar(50) default NULL;
+execute stmt;
+
+alter table t24_optimize drop column b;
+execute stmt;
+
+drop table t24_optimize;
+
+# SQLCOM_SHOW_CREATE_PROC:
+
+--disable_warnings
+drop procedure if exists changing_proc;
+--enable_warnings
+
+prepare stmt from 'show create procedure changing_proc';
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+
+create procedure changing_proc() begin end;
+
+# changing_proc has changed, and it's not a problem
+execute stmt;
+execute stmt;
+
+drop procedure changing_proc;
+create procedure changing_proc(x int, y int) begin end;
+
+execute stmt;
+execute stmt;
+
+drop procedure changing_proc;
+
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+
+# SQLCOM_SHOW_CREATE_FUNC:
+
+--disable_warnings
+drop function if exists changing_func;
+--enable_warnings
+
+prepare stmt from 'show create function changing_func';
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+
+create function changing_func() returns int return 0;
+
+# changing_proc has changed, and it's not a problem
+execute stmt;
+execute stmt;
+
+drop function changing_func;
+create function changing_func(x int, y int) returns int return x+y;
+
+execute stmt;
+execute stmt;
+
+drop function changing_func;
+
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+
+--echo =====================================================================
+--echo Testing reported bugs
+--echo =====================================================================
+
+#
+# Bug#12093 (SP not found on second PS execution if another thread drops
+# other SP in between)
+#
+
+--disable_warnings
+drop table if exists table_12093;
+drop function if exists func_12093;
+drop function if exists func_12093_unrelated;
+drop procedure if exists proc_12093;
+--enable_warnings
+
+connect (con1,localhost,root,,);
+
+connection default;
+
+create table table_12093(a int);
+
+delimiter //;
+
+create function func_12093()
+returns int
+begin
+  return (select count(*) from table_12093);
+end//
+
+create procedure proc_12093(a int)
+begin
+  select * from table_12093;
+end//
+
+delimiter ;//
+
+create function func_12093_unrelated() returns int return 2;
+create procedure proc_12093_unrelated() begin end;
+
+prepare stmt_sf from 'select func_12093();';
+prepare stmt_sp from 'call proc_12093(func_12093())';
+
+execute stmt_sf;
+execute stmt_sp;
+
+connection con1;
+
+drop function func_12093_unrelated;
+drop procedure proc_12093_unrelated;
+
+connection default;
+
+# previously, failed with --error 1305
+execute stmt_sf;
+# previously, failed with --error 1305
+execute stmt_sp;
+
+# previously, failed with --error 1305
+execute stmt_sf;
+# previously, failed with --error 1305
+execute stmt_sp;
+
+deallocate prepare stmt_sf;
+deallocate prepare stmt_sp;
+
+disconnect con1;
+
+drop table table_12093;
+drop function func_12093;
+drop procedure proc_12093;
+
+#
+# Bug#21294 (executing a prepared statement that executes a stored function
+# which was recreat)
+#
+
+--disable_warnings
+drop function if exists func_21294;
+--enable_warnings
+
+create function func_21294() returns int return 10;
+
+# sp_head::m_version_id is based on time
+--sleep 2
+
+prepare stmt from "select func_21294()";
+execute stmt;
+
+drop function func_21294;
+create function func_21294() returns int return 10;
+
+# might pass or fail, implementation dependent
+--error ER_PS_INVALIDATED
+execute stmt;
+
+drop function func_21294;
+create function func_21294() returns int return 20;
+
+# should fail
+--error ER_PS_INVALIDATED
+execute stmt;
+
+deallocate prepare stmt;
+drop function func_21294;
+
+#
+# Bug#27420 (A combination of PS and view operations cause error + assertion
+# on shutdown)
+#
+
+--disable_warnings
+drop table if exists t_27420_100;
+drop table if exists t_27420_101;
+drop view if exists v_27420;
+--enable_warnings
+
+connect (con1,localhost,root,,);
+
+connection default;
+
+create table t_27420_100(a int);
+insert into t_27420_100 values (1), (2);
+
+create table t_27420_101(a int);
+insert into t_27420_101 values (1), (2);
+
+create view v_27420 as select t_27420_100.a X, t_27420_101.a Y
+  from t_27420_100, t_27420_101
+  where t_27420_100.a=t_27420_101.a;
+
+prepare stmt from 'select * from v_27420';
+
+execute stmt;
+
+connection con1;
+
+drop view v_27420;
+create table v_27420(X int, Y int);
+
+connection default;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+connection con1;
+
+drop table v_27420;
+# passes in 5.0, fails in 5.1, should pass
+create table v_27420 (a int, b int, filler char(200));
+
+connection default;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+disconnect con1;
+
+deallocate prepare stmt;
+drop table t_27420_100;
+drop table t_27420_101;
+drop table v_27420;
+
+#
+# Bug#27430 (Crash in subquery code when in PS and table DDL changed after
+# PREPARE)
+#
+
+--disable_warnings
+drop table if exists t_27430_1;
+drop table if exists t_27430_2;
+--enable_warnings
+
+create table t_27430_1 (a int not null, oref int not null, key(a));
+insert into t_27430_1 values
+  (1, 1),
+  (1, 1234),
+  (2, 3),
+  (2, 1234),
+  (3, 1234);
+
+create table t_27430_2 (a int not null, oref int not null);
+insert into t_27430_2 values
+  (1, 1),
+  (2, 2),
+  (1234, 3),
+  (1234, 4);
+
+prepare stmt from 
+ 'select oref, a, a in (select a from t_27430_1 where oref=t_27430_2.oref) Z from t_27430_2';
+
+execute stmt; 
+
+drop table t_27430_1, t_27430_2;
+
+create table t_27430_1 (a int, oref int, key(a));
+insert into t_27430_1 values 
+  (1, 1),
+  (1, NULL),
+  (2, 3),
+  (2, NULL),
+  (3, NULL);
+
+create table t_27430_2 (a int, oref int);
+insert into t_27430_2 values
+  (1, 1),
+  (2,2),
+  (NULL, 3),
+  (NULL, 4);
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+deallocate prepare stmt;
+drop table t_27430_1;
+drop table t_27430_2;
+
+#
+# Bug#27690 (Re-execution of prepared statement after table was replaced
+# with a view crashes)
+#
+
+--disable_warnings
+drop table if exists t_27690_1;
+drop view if exists v_27690_1;
+drop table if exists v_27690_2;
+--enable_warnings
+
+create table t_27690_1 (a int, b int);
+insert into t_27690_1 values (1,1),(2,2);
+
+create table v_27690_1 as select * from t_27690_1;
+create table v_27690_2 as select * from t_27690_1;
+
+prepare stmt from 'select * from v_27690_1, v_27690_2'; 
+execute stmt;
+execute stmt;
+
+drop table v_27690_1;
+
+--error ER_NO_SUCH_TABLE
+execute stmt;
+
+--error ER_NO_SUCH_TABLE
+execute stmt;
+
+create view v_27690_1 as select A.a, A.b from t_27690_1 A, t_27690_1 B;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+--error ER_PS_INVALIDATED
+execute stmt;
+
+deallocate prepare stmt;
+drop table t_27690_1;
+drop view v_27690_1;
+drop table v_27690_2;
+
diff -Nrup a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test
--- a/mysql-test/t/sp-error.test	2007-11-01 14:52:50 -06:00
+++ b/mysql-test/t/sp-error.test	2007-11-26 09:14:50 -07:00
@@ -1114,11 +1114,7 @@ execute stmt;
 drop function bug11834_1;
 # Attempt to execute statement should return proper error and 
 # should not crash server.
-
-# NOTE! The error we get from the below query indicates that the sp bug11834_2
-# does not exist(this is wrong but can be accepted)
-# This behaviour has been reported as bug#21294
---error ER_SP_DOES_NOT_EXIST
+--error ER_PS_INVALIDATED
 execute stmt;
 deallocate prepare stmt;
 drop function bug11834_2;
diff -Nrup a/mysql-test/t/sp.test b/mysql-test/t/sp.test
--- a/mysql-test/t/sp.test	2007-10-29 08:37:17 -06:00
+++ b/mysql-test/t/sp.test	2007-11-26 09:14:50 -07:00
@@ -4965,7 +4965,7 @@ create procedure bug10100pt(level int, l
 begin
   if level < lim then
     update t3 set a=level;
-    FLUSH TABLES;
+    ## FLUSH TABLES;
     call bug10100pt(level+1, lim);
   else
     select * from t3;
@@ -4976,7 +4976,7 @@ create procedure bug10100pv(level int, l
 begin
   if level < lim then
     update v1 set a=level;
-    FLUSH TABLES;
+    ## FLUSH TABLES;
     call bug10100pv(level+1, lim);
   else
     select * from v1;
@@ -4990,11 +4990,11 @@ begin
     select level;
     prepare stmt1 from "update t3 set a=a+2";
     execute stmt1;
-    FLUSH TABLES;
+    ## FLUSH TABLES;
     execute stmt1;
-    FLUSH TABLES;
+    ## FLUSH TABLES;
     execute stmt1;
-    FLUSH TABLES;
+    ## FLUSH TABLES;
     deallocate prepare stmt1;
     execute stmt2;
     select * from t3;
@@ -5014,7 +5014,7 @@ begin
     fetch c into lv;
     select lv;
     update t3 set a=level+lv;
-    FLUSH TABLES;
+    ## FLUSH TABLES;
     call bug10100pc(level+1, lim);
   else
     select * from t3;
diff -Nrup a/mysql-test/t/sp_notembedded.test b/mysql-test/t/sp_notembedded.test
--- a/mysql-test/t/sp_notembedded.test	2007-09-05 12:02:59 -06:00
+++ b/mysql-test/t/sp_notembedded.test	2007-11-26 09:14:50 -07:00
@@ -178,7 +178,7 @@ create procedure bug10100pt(level int, l
 begin
   if level < lim then
     update t3 set a=level;
-    FLUSH TABLES;
+    ## FLUSH TABLES;
     call bug10100pt(level+1, lim);
   else
     select * from t3;
@@ -189,7 +189,7 @@ create procedure bug10100pv(level int, l
 begin
   if level < lim then
     update v1 set a=level;
-    FLUSH TABLES;
+    ## FLUSH TABLES;
     call bug10100pv(level+1, lim);
   else
     select * from v1;
@@ -203,11 +203,11 @@ begin
     select level;
     prepare stmt1 from "update t3 set a=a+2";
     execute stmt1;
-    FLUSH TABLES;
+    ## FLUSH TABLES;
     execute stmt1;
-    FLUSH TABLES;
+    ## FLUSH TABLES;
     execute stmt1;
-    FLUSH TABLES;
+    ## FLUSH TABLES;
     deallocate prepare stmt1;
     execute stmt2;
     select * from t3;
@@ -227,7 +227,7 @@ begin
     fetch c into lv;
     select lv;
     update t3 set a=level+lv;
-    FLUSH TABLES;
+    ## FLUSH TABLES;
     call bug10100pc(level+1, lim);
   else
     select * from t3;
diff -Nrup a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
--- a/mysql-test/t/trigger.test	2007-08-15 02:18:40 -06:00
+++ b/mysql-test/t/trigger.test	2007-11-26 09:14:50 -07:00
@@ -982,6 +982,7 @@ call p1();
 # Actually it is enough to do FLUSH TABLES instead of DROP TRIGGER
 drop trigger t1_bi;
 # Server should not crash on these two statements
+--error ER_PS_INVALIDATED
 execute stmt1;
 call p1();
 deallocate prepare stmt1;
@@ -997,10 +998,7 @@ call p1();
 # Altering trigger forcing it use different set of tables
 drop trigger t1_bi;
 create trigger t1_bi after insert on t1 for each row insert into t3 values (new.id);
-# Until we implement proper mechanism for invalidation of PS/SP when table
-# or SP's are changed these two statements will fail with 'Table ... was
-# not locked' error (this mechanism should be based on the new TDC).
---error ER_NO_SUCH_TABLE 
+--error ER_PS_INVALIDATED 
 execute stmt1;
 --error ER_NO_SUCH_TABLE 
 call p1();
diff -Nrup a/sql/parse_file.cc b/sql/parse_file.cc
--- a/sql/parse_file.cc	2007-07-30 02:33:44 -06:00
+++ b/sql/parse_file.cc	2007-11-26 09:14:50 -07:00
@@ -429,7 +429,7 @@ sql_parse_prepare(const LEX_STRING *file
   File file;
   DBUG_ENTER("sql_parse_prepare");
 
-  if (!my_stat(file_name->str, &stat_info, MYF(MY_WME)))
+  if (!my_stat(file_name->str, &stat_info, MYF(0)))
   {
     DBUG_RETURN(0);
   }
diff -Nrup a/sql/share/errmsg.txt b/sql/share/errmsg.txt
--- a/sql/share/errmsg.txt	2007-11-10 05:09:16 -07:00
+++ b/sql/share/errmsg.txt	2007-11-26 09:14:52 -07:00
@@ -6112,3 +6112,7 @@ ER_TRG_CANT_OPEN_TABLE
 
 ER_CANT_CREATE_SROUTINE
   eng "Cannot create stored routine `%-.64s`. Check warnings"
+
+ER_PS_INVALIDATED
+  eng "Prepared statement has been invalidated, because %s '%s' has changed"
+
diff -Nrup a/sql/sp.cc b/sql/sp.cc
--- a/sql/sp.cc	2007-10-30 11:08:11 -06:00
+++ b/sql/sp.cc	2007-11-26 09:14:50 -07:00
@@ -1798,6 +1798,167 @@ static void sp_update_stmt_used_routines
 }
 
 
+/**
+  This helper ensures that a given Stored Function or Stored Procedure
+  is present in the corresponding per session cache,
+  and loads the Stored Function/Procedure from storage into the cache,
+  if necessary.
+  @param [in] thd Current thread
+  @param [in] type Type of stored object to lookup/load
+  @param [in] name Name of the stored object
+  @param [out] sphp Stored object refreshed in the cache, if found
+  @return An error status
+    @retval 0 Success, sphp will contain the stored object fetched, if any
+*/
+
+static int
+sp_cache_load(THD *thd, int type, sp_name *name, sp_head **sphp)
+{
+  sp_cache **cp;
+  sp_head *sp;
+  int ret;
+  Object_version sp_version;
+
+  DBUG_ENTER("sp_cache_load");
+
+  DBUG_ASSERT(name);
+  DBUG_ASSERT(sphp);
+  DBUG_ASSERT(type == TYPE_ENUM_PROCEDURE ||
+              type == TYPE_ENUM_TRIGGER ||
+              type == TYPE_ENUM_FUNCTION);
+
+  /*
+    Note about triggers:
+    - triggers are cached in the stored procedure cache.
+      This needs some revision, since before/after insert/update/delete
+      triggers all collide with the same key (the table name is used).
+      The cache itself is not used when looking up triggers.
+    - for object dependency tracking (Object_observer), triggers are
+      not tracked individually, since they are part of the table definition
+      itself. When a trigger changes, the table (container) will change.
+    TODO: revise this.
+  */
+  cp= (type == TYPE_ENUM_FUNCTION ? &thd->sp_func_cache : &thd->sp_proc_cache);
+
+  sp= sp_cache_lookup(cp, name);
+
+  if (! sp)
+  {
+    switch ((ret= db_find_routine(thd, type, name, &sp)))
+    {
+    case SP_OK:
+      sp_cache_insert(cp, sp);
+
+      if (thd->m_object_observer && type != TYPE_ENUM_TRIGGER)
+      {
+        sp->make_object_version(& sp_version);
+
+        if (thd->m_object_observer->notify(thd, sp_version))
+          DBUG_RETURN(1);
+      }
+
+      *sphp= sp;
+      ret= 0;
+      break;
+    case SP_KEY_NOT_FOUND:
+      if (thd->m_object_observer && type != TYPE_ENUM_TRIGGER)
+      {
+        sp_head::make_object_null_version(& sp_version,
+                                          type,
+                                          name->m_db,
+                                          name->m_name);
+        if (thd->m_object_observer->notify(thd, sp_version))
+          DBUG_RETURN(1);
+      }
+
+      *sphp= NULL;
+      ret= 0;
+      break;
+    default:
+      /*
+        Any error when loading an existing routine is either some problem
+        with the mysql.proc table, or a parse error because the contents
+        has been tampered with (in which case we clear that error).
+      */
+      if (ret == SP_PARSE_ERROR)
+        thd->clear_error();
+      /*
+        If we cleared the parse error, or when db_find_routine() flagged
+        an error with it's return value without calling my_error(), we
+        set the generic "mysql.proc table corrupt" error here.
+       */
+      if (! thd->is_error())
+      {
+        /*
+          SP allows full NAME_LEN chars thus he have to allocate enough
+          size in bytes. Otherwise there is stack overrun could happen
+          if multibyte sequence is `name`. `db` is still safe because the
+          rest of the server checks against NAME_LEN bytes and not chars.
+          Hence, the overrun happens only if the name is in length > 32 and
+          uses multibyte (cyrillic, greek, etc.)
+        */
+        char n[NAME_LEN*2+2];
+
+        /* m_qname.str is not always \0 terminated */
+        memcpy(n, name->m_qname.str, name->m_qname.length);
+        n[name->m_qname.length]= '\0';
+        my_error(ER_SP_PROC_TABLE_CORRUPT, MYF(0), n, ret);
+        ret= 1;
+      }
+      break;
+    }
+  }
+  else
+  {
+    if (thd->m_object_observer && type != TYPE_ENUM_TRIGGER)
+    {
+      sp->make_object_version(& sp_version);
+
+      if (thd->m_object_observer->notify(thd, sp_version))
+        DBUG_RETURN(2);
+    }
+
+    ret= 0;
+    *sphp= sp;
+  }
+
+  DBUG_RETURN(ret);
+}
+
+
+/**
+  Refresh all the routines used by a statement in the session cache.
+  Note that routines that can be found are reloaded,
+  while routines that can not be found are ignored.
+  @param [in] thd Current thread
+  @param [in] lex Statement semantic tree
+  @return An error status
+    @retval 0 Success
+*/
+int
+sp_refresh_routines(THD *thd, LEX *lex)
+{
+  Sroutine_hash_entry *start;
+  int ret= 0;
+
+  DBUG_ENTER("sp_recache_routines");
+  start= (Sroutine_hash_entry *)lex->sroutines_list.first;
+
+  for (Sroutine_hash_entry *rt= start; rt; rt= rt->next)
+  {
+    sp_name name(thd, rt->key.str, rt->key.length);
+    int type= rt->key.str[0];
+    sp_head *sp;
+
+    ret= sp_cache_load(thd, type, &name, &sp);
+
+    if (ret)
+      break;
+  }
+  DBUG_RETURN(ret);
+}
+
+
 /*
   Cache sub-set of routines used by statement, add tables used by these
   routines to statement table list. Do the same for all routines used
@@ -1836,56 +1997,11 @@ sp_cache_routines_and_add_tables_aux(THD
     int type= rt->key.str[0];
     sp_head *sp;
 
-    if (!(sp= sp_cache_lookup((type == TYPE_ENUM_FUNCTION ?
-                              &thd->sp_func_cache : &thd->sp_proc_cache),
-                              &name)))
-    {
-      switch ((ret= db_find_routine(thd, type, &name, &sp)))
-      {
-      case SP_OK:
-        {
-          if (type == TYPE_ENUM_FUNCTION)
-            sp_cache_insert(&thd->sp_func_cache, sp);
-          else
-            sp_cache_insert(&thd->sp_proc_cache, sp);
-        }
-        break;
-      case SP_KEY_NOT_FOUND:
-        ret= SP_OK;
-        break;
-      default:
-        /*
-          Any error when loading an existing routine is either some problem
-          with the mysql.proc table, or a parse error because the contents
-          has been tampered with (in which case we clear that error).
-        */
-        if (ret == SP_PARSE_ERROR)
-          thd->clear_error();
-        /*
-          If we cleared the parse error, or when db_find_routine() flagged
-          an error with it's return value without calling my_error(), we
-          set the generic "mysql.proc table corrupt" error here.
-         */
-        if (! thd->is_error())
-        {
-          /*
-            SP allows full NAME_LEN chars thus he have to allocate enough
-            size in bytes. Otherwise there is stack overrun could happen
-            if multibyte sequence is `name`. `db` is still safe because the
-            rest of the server checks agains NAME_LEN bytes and not chars.
-            Hence, the overrun happens only if the name is in length > 32 and
-            uses multibyte (cyrillic, greek, etc.)
-          */
-          char n[NAME_LEN*2+2];
-
-          /* m_qname.str is not always \0 terminated */
-          memcpy(n, name.m_qname.str, name.m_qname.length);
-          n[name.m_qname.length]= '\0';
-          my_error(ER_SP_PROC_TABLE_CORRUPT, MYF(0), n, ret);
-        }
-        break;
-      }
-    }
+    ret= sp_cache_load(thd, type, &name, &sp);
+
+    if (ret)
+      break;
+
     if (sp)
     {
       if (!(first && first_no_prelock))
diff -Nrup a/sql/sp.h b/sql/sp.h
--- a/sql/sp.h	2007-10-17 02:13:53 -06:00
+++ b/sql/sp.h	2007-11-26 09:14:50 -07:00
@@ -70,6 +70,7 @@ void sp_add_used_routine(LEX *lex, Query
                          sp_name *rt, char rt_type);
 void sp_remove_not_own_routines(LEX *lex);
 void sp_update_sp_used_routines(HASH *dst, HASH *src);
+int sp_refresh_routines(THD *thd, LEX *lex);
 int sp_cache_routines_and_add_tables(THD *thd, LEX *lex,
                                      bool first_no_prelock);
 int sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex,
diff -Nrup a/sql/sp_head.cc b/sql/sp_head.cc
--- a/sql/sp_head.cc	2007-11-01 14:52:51 -06:00
+++ b/sql/sp_head.cc	2007-11-26 09:14:51 -07:00
@@ -528,6 +528,66 @@ sp_head::sp_head()
   DBUG_VOID_RETURN;
 }
 
+int sp_head::make_object_version(Object_version * version)
+{
+  int rc;
+
+  switch(m_type)
+  {
+  case TYPE_ENUM_FUNCTION:
+    rc= version->m_oid.set_function(m_db, m_name);
+    break;
+  case TYPE_ENUM_PROCEDURE:
+    rc= version->m_oid.set_procedure(m_db, m_name);
+    break;
+  case TYPE_ENUM_TRIGGER:
+    /*
+      Triggers are part of the table definition, and are not tracked
+      as independent objects.
+      When this is changed, the trigger Object_id should include:
+      - the schema name
+      - the table name
+      - the trigger action (before / after)
+      - the trigger event type (insert / update / delete)
+    */
+  default:
+    DBUG_ASSERT(false);
+    rc= 1;
+  }
+
+  version->m_vid.set_long_1(m_modified);
+
+  return rc;
+}
+
+int sp_head::make_object_null_version(
+  Object_version * version,
+  int type,
+  const LEX_STRING& db,
+  const LEX_STRING& name)
+{
+  int rc;
+
+  switch(type)
+  {
+  case TYPE_ENUM_FUNCTION:
+    rc= version->m_oid.set_function(db, name);
+    break;
+  case TYPE_ENUM_PROCEDURE:
+    rc= version->m_oid.set_procedure(db, name);
+    break;
+  case TYPE_ENUM_TRIGGER:
+    /* See sp_head::make_object_version() */
+  default:
+    DBUG_ASSERT(false);
+    rc= 1;
+  }
+
+  version->m_vid.set_null();
+
+  return rc;
+}
+
 
 void
 sp_head::init(LEX *lex)
diff -Nrup a/sql/sp_head.h b/sql/sp_head.h
--- a/sql/sp_head.h	2007-10-16 15:41:27 -06:00
+++ b/sql/sp_head.h	2007-11-26 09:14:51 -07:00
@@ -195,6 +195,13 @@ public:
   LEX_STRING m_definer_user;
   LEX_STRING m_definer_host;
 
+  int make_object_version(Object_version * version);
+  static int make_object_null_version(
+    Object_version * version,
+    int type,
+    const LEX_STRING& db,
+    const LEX_STRING& name);
+
 private:
   Stored_program_creation_ctx *m_creation_ctx;
 
diff -Nrup a/sql/sql_base.cc b/sql/sql_base.cc
--- a/sql/sql_base.cc	2007-11-12 13:09:44 -07:00
+++ b/sql/sql_base.cc	2007-11-26 09:14:51 -07:00
@@ -2335,7 +2335,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
                       (ulong) table->query_id, (uint) thd->server_id,
                       (ulong) thd->variables.pseudo_thread_id));
 	  my_error(ER_CANT_REOPEN_TABLE, MYF(0), table->alias);
-	  DBUG_RETURN(0);
+          goto end_null;
 	}
 	table->query_id= thd->query_id;
 	thd->thread_specific_used= TRUE;
@@ -2348,7 +2348,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
   if (flags & MYSQL_OPEN_TEMPORARY_ONLY)
   {
     my_error(ER_NO_SUCH_TABLE, MYF(0), table_list->db, table_list->table_name);
-    DBUG_RETURN(0);
+    goto end_null;
   }
 
   /*
@@ -2381,7 +2381,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
           */
           my_error(ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG, MYF(0),
                    table->s->table_name.str);
-          DBUG_RETURN(0);
+          goto end_null;
         }
         if (!my_strcasecmp(system_charset_info, table->alias, alias) &&
             table->query_id != thd->query_id && /* skip tables already used */
@@ -2451,7 +2451,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
         {
           DBUG_ASSERT(table_list->view != 0);
           VOID(pthread_mutex_unlock(&LOCK_open));
-          DBUG_RETURN(0); // VIEW
+          goto end_null; // VIEW
         }
         VOID(pthread_mutex_unlock(&LOCK_open));
       }
@@ -2467,7 +2467,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
       my_error(ER_NO_SUCH_TABLE, MYF(0), table_list->db, table_list->alias);
     else
       my_error(ER_TABLE_NOT_LOCKED, MYF(0), alias);
-    DBUG_RETURN(0);
+    goto end_null;
   }
 
   /*
@@ -2505,7 +2505,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
     if (refresh)
       *refresh=1;
     VOID(pthread_mutex_unlock(&LOCK_open));
-    DBUG_RETURN(0);
+    goto end_null;
   }
 
   /*
@@ -2570,7 +2570,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
       {
 	VOID(pthread_mutex_unlock(&LOCK_open));
         my_error(ER_UPDATE_TABLE_USED, MYF(0), table->s->table_name.str);
-        DBUG_RETURN(0);
+        goto end_null;
       }
 
       /*
@@ -2617,7 +2617,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
       */
       if (refresh)
 	*refresh=1;
-      DBUG_RETURN(0);
+      goto end_null;
     }
   }
   if (table)
@@ -2648,7 +2648,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
       if (check_if_table_exists(thd, table_list, &exists))
       {
         VOID(pthread_mutex_unlock(&LOCK_open));
-        DBUG_RETURN(NULL);
+        goto end_null;
       }
 
       if (!exists)
@@ -2659,7 +2659,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
         if (!(table= table_cache_insert_placeholder(thd, key, key_length)))
         {
           VOID(pthread_mutex_unlock(&LOCK_open));
-          DBUG_RETURN(NULL);
+          goto end_null;
         }
         /*
           Link placeholder to the open tables list so it will be automatically
@@ -2670,7 +2670,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
         table->next= thd->open_tables;
         thd->open_tables= table;
         VOID(pthread_mutex_unlock(&LOCK_open));
-        DBUG_RETURN(table);
+        goto end_table;
       }
       /* Table exists. Let us try to open it. */
     }
@@ -2679,7 +2679,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
     if (!(table=(TABLE*) my_malloc(sizeof(*table),MYF(MY_WME))))
     {
       VOID(pthread_mutex_unlock(&LOCK_open));
-      DBUG_RETURN(NULL);
+      goto end_null;
     }
 
     error= open_unireg_entry(thd, table, table_list, alias, key, key_length,
@@ -2688,7 +2688,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
     {
       my_free((uchar*)table, MYF(0));
       VOID(pthread_mutex_unlock(&LOCK_open));
-      DBUG_RETURN(NULL);
+      goto end_null;
     }
     if (table_list->view || error < 0)
     {
@@ -2701,7 +2701,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
 
       my_free((uchar*)table, MYF(0));
       VOID(pthread_mutex_unlock(&LOCK_open));
-      DBUG_RETURN(0); // VIEW
+      goto end_null; // VIEW
     }
     DBUG_PRINT("info", ("inserting table 0x%lx into the cache", (long) table));
     VOID(my_hash_insert(&open_cache,(uchar*) table));
@@ -2710,6 +2710,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
   check_unused();				// Debugging call
 
   VOID(pthread_mutex_unlock(&LOCK_open));
+
   if (refresh)
   {
     table->next=thd->open_tables;		/* Link into simple list */
@@ -2749,7 +2750,31 @@ TABLE *open_table(THD *thd, TABLE_LIST *
   table_list->updatable= 1; // It is not derived table nor non-updatable VIEW
   table->clear_column_bitmaps();
   DBUG_ASSERT(table->key_read == 0);
+
+end_table:
+  if (thd->m_object_observer)
+  {
+    Object_version table_version;
+    table->s->make_object_version(& table_version);
+    if (thd->m_object_observer->notify(thd, table_version))
+    {
+      *refresh= FALSE;
+      DBUG_RETURN(NULL);
+    }
+  }
   DBUG_RETURN(table);
+
+end_null:
+  if (thd->m_object_observer)
+  {
+    Object_version table_version;
+
+    TABLE_SHARE::make_object_null_version(& table_version,
+                                          table_list->db,
+                                          table_list->table_name);
+    thd->m_object_observer->notify(thd, table_version);
+  }
+  DBUG_RETURN(NULL);
 }
 
 
diff -Nrup a/sql/sql_class.cc b/sql/sql_class.cc
--- a/sql/sql_class.cc	2007-11-15 05:35:32 -07:00
+++ b/sql/sql_class.cc	2007-11-26 09:14:51 -07:00
@@ -50,6 +50,311 @@ char empty_c_string[1]= {0};    /* used 
 
 const char * const THD::DEFAULT_WHERE= "field list";
 
+Object_id::Object_id()
+{
+  m_type= NO_OBJECT_TYPE;
+  m_key[0]= '\0';
+  m_key_length= 0;
+}
+
+Object_id::Object_id(const Object_id& oid)
+{
+  m_type= oid.m_type;
+  memcpy(m_key, oid.m_key, sizeof(m_key));
+  m_key_length= oid.m_key_length;
+}
+
+bool Object_id::is_equal(const Object_id& oid) const
+{
+  return ((m_type == oid.m_type) &&
+         (m_key_length == oid.m_key_length) &&
+         (memcmp(m_key, oid.m_key, m_key_length) == 0));
+}
+
+int
+Object_id::set(const Object_id& oid)
+{
+  m_type= oid.m_type;
+  m_key_length= oid.m_key_length;
+  memcpy(m_key, oid.m_key, m_key_length);
+  return 0;
+}
+
+void
+Object_id::reset_type(Object_type type)
+{
+  DBUG_ASSERT(m_type != NO_OBJECT_TYPE);
+  DBUG_ASSERT(type != NO_OBJECT_TYPE);
+
+  m_type= type;
+  switch(m_type)
+  {
+  case OBJECT_TYPE_TABLE:
+    m_key[0]= 'T';
+    break;
+  case OBJECT_TYPE_VIEW:
+    m_key[0]= 'V';
+    break;
+  default:
+    DBUG_ASSERT(FALSE);
+    break;
+  }
+}
+
+int
+Object_id::set_table(const char* db, const char* name)
+{
+  /* FIXME: Fix TABLE_SHARE::db, TABLE_SHARE::table_name */
+  LEX_STRING lex_db;
+  LEX_STRING lex_name;
+  lex_db.str= (char*) db;
+  lex_db.length= strlen(lex_db.str);
+  lex_name.str= (char*) name;
+  lex_name.length= strlen(lex_name.str);
+
+  return set_table(lex_db, lex_name);
+}
+
+int
+Object_id::set_view(const char* db, const char* name)
+{
+  /* FIXME: Fix TABLE_LIST::db, TABLE_LIST::table_name */
+  LEX_STRING lex_db;
+  LEX_STRING lex_name;
+  lex_db.str= (char*) db;
+  lex_db.length= strlen(lex_db.str);
+  lex_name.str= (char*) name;
+  lex_name.length= strlen(lex_name.str);
+
+  return set_view(lex_db, lex_name);
+}
+
+int
+Object_id::set_key(
+  char kind, const LEX_STRING& db, const LEX_STRING& name)
+{
+  DBUG_ASSERT(db.length <= NAME_LEN);
+  DBUG_ASSERT(name.length <= NAME_LEN);
+
+  /* format: <type> <db> . <name> \0 */
+  m_key_length= sprintf(m_key, "%c%.*s.%.*s",
+                        kind,
+                        (int) db.length, db.str,
+                        (int) name.length, name.str);
+
+  return 0;
+}
+
+const char*
+Object_id::get_printable_type() const
+{
+  switch (m_type)
+  {
+  case OBJECT_TYPE_TABLE:
+    return "TABLE";
+  case OBJECT_TYPE_VIEW:
+    return "VIEW";
+  case OBJECT_TYPE_PROCEDURE:
+    return "PROCEDURE";
+  case OBJECT_TYPE_FUNCTION:
+    return "FUNCTION";
+  default:
+    return "OBJECT";
+  }
+}
+
+const char*
+Object_id::get_printable_name() const
+{
+  return & m_key[1];
+}
+
+Version_id::Version_id()
+  : m_kind(NULL_VERSION),
+    m_long_value(0)
+{
+  m_md5_value[0]= 0;
+}
+
+Version_id::Version_id(const Version_id& vid)
+{
+  m_kind= vid.m_kind;
+  switch(m_kind)
+  {
+  case NULL_VERSION:
+    break;
+  case LONG_VERSION_1:
+  case LONG_VERSION_2:
+    m_long_value= vid.m_long_value;
+    break;
+  case MD5_VERSION:
+    memcpy(m_md5_value, vid.m_md5_value, sizeof(m_md5_value));
+    break;
+  }
+}
+
+Version_id::~Version_id()
+{}
+
+bool
+Version_id::is_equal(const Version_id& vid) const
+{
+  if (m_kind != vid.m_kind)
+    return false;
+
+  switch(m_kind)
+  {
+    case NULL_VERSION:
+      return true;
+    case LONG_VERSION_1:
+    case LONG_VERSION_2:
+      return (m_long_value == vid.m_long_value);
+    case MD5_VERSION:
+      return (memcmp(m_md5_value, vid.m_md5_value, sizeof(m_md5_value)) == 0);
+    default:
+      DBUG_ASSERT(false);
+  }
+}
+
+bool
+Version_id::is_null_or_equal(const Version_id& vid) const
+{
+  return is_null() || vid.is_null() || is_equal(vid);
+}
+
+bool
+Version_id::is_null() const
+{
+  return (m_kind == NULL_VERSION);
+}
+
+void
+Version_id::set_null()
+{
+  m_kind= NULL_VERSION;
+}
+
+void
+Version_id::set_long_1(longlong value)
+{
+  m_kind= LONG_VERSION_1;
+  m_long_value= value;
+}
+
+void
+Version_id::set_long_2(longlong value)
+{
+  m_kind= LONG_VERSION_2;
+  m_long_value= value;
+}
+
+void
+Version_id::set_md5(const LEX_STRING& md5)
+{
+  DBUG_ASSERT(md5.length == sizeof(m_md5_value));
+  m_kind= MD5_VERSION;
+  memcpy(m_md5_value, md5.str, sizeof(m_md5_value));
+}
+
+extern "C" uchar*
+get_obj_version_hash_key(const uchar *buff, size_t *length,
+                         my_bool /* unused */)
+{
+  Object_version *over= (Object_version*) buff;
+  return over->m_oid.get_hash_key(length);
+}
+
+void* Object_version::operator new(size_t size, MEM_ROOT* mem_root) throw()
+{
+  void *ptr;
+  DBUG_ENTER("Object_version::operator new");
+  ptr= alloc_root(mem_root, size);
+  DBUG_RETURN(ptr);
+}
+
+void
+Object_version::operator delete(void *ptr, MEM_ROOT* mem_root) throw()
+{
+}
+
+int
+Object_version_collection::init(MEM_ROOT *mem_root)
+{
+  DBUG_ENTER("Object_version_collection::init");
+  int rc;
+
+  DBUG_ASSERT(m_mem_root == NULL);
+  m_mem_root= mem_root;
+
+  rc= hash_init(& m_hash,
+                & my_charset_bin,
+                0,                             /* initial size */
+                0,
+                0,
+                (hash_get_key) get_obj_version_hash_key,
+                NULL,                          /* Nothing to free */
+                MYF(0));
+
+  DBUG_RETURN(rc);
+}
+
+Object_version_collection::~Object_version_collection()
+{
+  if (m_mem_root)
+  {
+    (void) hash_free(& m_hash);
+    m_mem_root= NULL;
+  }
+}
+
+int
+Object_version_collection::add(
+  const Object_id& oid,
+  const Version_id& vid)
+{
+  Object_version *over;
+  size_t hash_key_length;
+  uchar* hash_key;
+
+  DBUG_ENTER("Object_version_collection::add()");
+
+  hash_key= oid.get_hash_key(& hash_key_length);
+  over= (Object_version*) hash_search(& m_hash, hash_key, hash_key_length);
+
+  if (over)
+  {
+    if (! over->m_vid.is_equal(vid))
+      DBUG_RETURN(1);
+  }
+  else
+  {
+    over= new (m_mem_root) Object_version(oid, vid);
+    if (my_hash_insert(& m_hash, (uchar*) over))
+      DBUG_RETURN(2);
+  }
+
+  DBUG_PRINT("info",("key: %s  length: %d",
+                    hash_key, (int) hash_key_length));
+  DBUG_RETURN(0);
+}
+
+int
+Object_version_collection::find(
+  const Object_id& oid,
+  const Object_version ** over) const
+{
+  Object_version *v;
+  size_t hash_key_length;
+  uchar* hash_key;
+
+  hash_key= oid.get_hash_key(& hash_key_length);
+  v= (Object_version*) hash_search(& m_hash, hash_key, hash_key_length);
+
+  *over= v;
+  return 0;
+}
+
+
 
 /*****************************************************************************
 ** Instansiate templates
@@ -375,7 +680,8 @@ THD::THD()
    bootstrap(0),
    derived_tables_processing(FALSE),
    spcont(NULL),
-   m_lip(NULL)
+   m_lip(NULL),
+   m_object_observer(NULL)
 {
   ulong tmp;
 
diff -Nrup a/sql/sql_class.h b/sql/sql_class.h
--- a/sql/sql_class.h	2007-10-31 09:33:06 -06:00
+++ b/sql/sql_class.h	2007-11-26 09:14:51 -07:00
@@ -23,6 +23,227 @@
 #include "log.h"
 #include "rpl_tblmap.h"
 
+#define MD5_CHAR_LENGTH 32
+
+/**
+  The type of a MySQL object.
+*/
+enum Object_type
+{
+  NO_OBJECT_TYPE= 0,
+  OBJECT_TYPE_TABLE= 1,
+  OBJECT_TYPE_VIEW= 2,
+  OBJECT_TYPE_PROCEDURE= 3,
+  OBJECT_TYPE_FUNCTION= 4
+
+  /*
+    TODO: To be extended:
+    - OBJECT_TYPE_UDF,
+    - OBJECT_TYPE_TRIGGER,
+    - OBJECT_TYPE_EVENT,
+    - OBJECT_TYPE_USER,
+    - OBJECT_TYPE_TABLESPACE,
+    - OBJECT_TYPE_PARTITION,
+    etc, as needed.
+  */
+};
+
+#define MAX_OBJ_ID_KEY_LEN (2*NAME_LEN + 3)
+
+class Object_id
+{
+public:
+  Object_id();
+  Object_id(const Object_id& oid);
+  ~Object_id() {}
+
+  bool is_equal(const Object_id& oid) const;
+
+  Object_type get_type() const
+  { return m_type; }
+
+  void reset_type(Object_type type);
+
+  int set(const Object_id& oid);
+
+  int set_table(const char* db, const char* name);
+
+  int set_table(const LEX_STRING& db, const LEX_STRING& name)
+  {
+    m_type= OBJECT_TYPE_TABLE;
+    return set_key('T', db, name);
+  }
+
+  int set_view(const char* db, const char* name);
+
+  int set_view(const LEX_STRING& db, const LEX_STRING& name)
+  {
+    m_type= OBJECT_TYPE_VIEW;
+    return set_key('V', db, name);
+  }
+
+  int set_procedure(const LEX_STRING& db, const LEX_STRING& name)
+  {
+    m_type= OBJECT_TYPE_PROCEDURE;
+    return set_key('P', db, name);
+  }
+
+  int set_function(const LEX_STRING& db, const LEX_STRING& name)
+  {
+    m_type= OBJECT_TYPE_FUNCTION;
+    return set_key('F', db, name);
+  }
+
+  uchar* get_hash_key(size_t *length) const
+  {
+    *length= m_key_length;
+    return (uchar*) & m_key[0];
+  }
+
+  const char* get_printable_type() const;
+
+  const char* get_printable_name() const;
+
+private:
+  int set_key(char kind, const LEX_STRING& db, const LEX_STRING& name);
+
+private:
+  Object_type m_type;
+  char m_key[MAX_OBJ_ID_KEY_LEN];
+  int m_key_length;
+
+private: // Not implemented, use set()
+  Object_id& operator = (const Object_id&);
+};
+
+/**
+  A Version_id identifies a version of a given object.
+  Different objects (TABLE, VIEW, FUNCTION, PROCEDURE, ...)
+  can use version ids of different spaces (MD5, timestamp, counter, ...),
+  so that a Version_id is an heterogeneous type.
+  Versions can be compared for equality, but don't define any order.
+*/
+
+class Version_id
+{
+public:
+  Version_id();
+  Version_id(const Version_id& vid);
+  ~Version_id();
+
+  bool is_null() const;
+  /**
+    Test for equality.
+    Equality is defined by:
+    - having the same namespace
+    - having the same value within that namespace
+  */
+  bool is_equal(const Version_id& v) const;
+  bool is_null_or_equal(const Version_id& v) const;
+
+  void set_null();
+
+  /** Set a long version number, in namespace 1. */
+  void set_long_1(longlong value);
+
+  /** Set a long version number, in namespace 2. */
+  void set_long_2(longlong value);
+
+  void set_md5(const LEX_STRING& md5);
+
+private:
+  enum kind {
+    NULL_VERSION,
+    LONG_VERSION_1,
+    LONG_VERSION_2,
+    MD5_VERSION
+  };
+
+  kind m_kind;
+  longlong m_long_value;
+  char m_md5_value[MD5_CHAR_LENGTH];
+
+private: // Non implemented
+  Version_id& operator = (const Version_id&);
+};
+
+
+class Object_version
+{
+public:
+  static void* operator new(size_t size, MEM_ROOT* mem_root) throw();
+  static void operator delete(void *ptr, MEM_ROOT* mem_root) throw();
+
+  Object_version()
+  {}
+
+  Object_version(const Object_id& oid, const Version_id& vid)
+   : m_oid(oid), m_vid(vid)
+  {}
+
+  ~Object_version() {}
+
+  Object_id m_oid;
+  Version_id m_vid;
+
+private: // Non implemented
+  Object_version(const Object_version&);
+  Object_version& operator = (const Object_version&);
+};
+
+
+class Object_version_collection
+{
+public:
+  Object_version_collection()
+    : m_mem_root(NULL)
+  {}
+
+  ~Object_version_collection();
+
+  int init(MEM_ROOT *mem_root);
+  int add(const Object_id& oid, const Version_id & vid);
+  int find(const Object_id& oid, const Object_version ** over) const;
+
+private:
+  HASH m_hash;
+  MEM_ROOT *m_mem_root;
+
+private: // Non implemented
+  Object_version_collection(const Object_version_collection&);
+  Object_version_collection& operator=(const Object_version_collection&);
+};
+
+/**
+  Interface class for observers, used to inspect objects referenced
+  by a statement.
+  An Object_observer can inspect existing objects,
+  as well as get notified that some objects used in a statement are missing.
+  Observers can cause the current operation to fail, by returning an error
+  from the notification methods.
+*/
+
+class Object_observer
+{
+protected:
+  Object_observer() {}
+  virtual ~Object_observer() {}
+
+public:
+  /**
+    Notify that an object is used in a statement.
+    @param [in] thd Current thread
+    @param [in] over object version
+    @return An error status
+      @retval 0 Success
+  */
+  virtual int notify(THD *thd, const Object_version& over) = 0;
+
+private: // Non implemented
+  Object_observer(const Object_observer&);
+  Object_observer& operator = (const Object_observer&);
+};
+
 class Relay_log_info;
 
 class Query_log_event;
@@ -1542,6 +1763,8 @@ public:
     and may point to invalid memory after that.
   */
   Lex_input_stream *m_lip;
+
+  Object_observer *m_object_observer;
 
 #ifdef WITH_PARTITION_STORAGE_ENGINE
   partition_info *work_part_info;
diff -Nrup a/sql/sql_prepare.cc b/sql/sql_prepare.cc
--- a/sql/sql_prepare.cc	2007-10-30 11:08:12 -06:00
+++ b/sql/sql_prepare.cc	2007-11-26 09:14:51 -07:00
@@ -109,6 +109,300 @@ public:
 #endif
 };
 
+class Prepare_observer : public Object_observer
+{
+public:
+  Prepare_observer(Object_version_collection* coll)
+    : m_coll(coll)
+  {}
+
+  virtual ~Prepare_observer() {}
+
+  virtual int notify(THD *thd, const Object_version& over);
+
+private:
+  Object_version_collection *m_coll;
+};
+
+
+class Execute_observer : public Object_observer
+{
+public:
+  Execute_observer(Object_version_collection* coll,
+                   const Object_id& expected_table_change)
+    : m_coll(coll),
+    m_expected_table_change(expected_table_change)
+  {}
+
+  virtual ~Execute_observer() {}
+
+  virtual int notify(THD *thd, const Object_version& over);
+
+private:
+  bool is_safe_table_operation(LEX *lex) const;
+  bool is_safe_sp_operation(LEX *lex) const;
+
+private:
+  Object_version_collection *m_coll;
+  const Object_id& m_expected_table_change;
+};
+
+int visit_all_views(THD *thd, TABLE_LIST *start)
+{
+  Object_observer *observer;
+  TABLE_LIST *tables;
+  Object_version view_version;
+
+  DBUG_ASSERT(thd->m_object_observer);
+
+  observer= thd->m_object_observer;
+  for (tables= start; tables; tables= tables->next_global)
+  {
+    if (tables->view)
+    {
+      if (tables->make_object_version(& view_version))
+        return 1;
+
+      if (observer->notify(thd, view_version))
+        return 1;
+    }
+  }
+
+  return 0;
+}
+
+int recheck_all_views(THD *thd, TABLE_LIST *start)
+{
+  Object_observer *observer;
+  TABLE_LIST *tables;
+  TABLE_LIST dummy;
+  int rc;
+  Object_version view_version;
+
+  DBUG_ASSERT(thd->m_object_observer);
+
+  observer= thd->m_object_observer;
+
+  /*
+    FIXME: There is no equivalent of sroutines_list for views,
+    so we have to iterate in TABLE_LIST.
+    This is poor, since the same view might be expanded
+    in multiple places, and will cause multiple parsing
+  */
+  for (tables= start; tables; tables= tables->next_global)
+  {
+    if (tables->view)
+    {
+      /*
+        WARNING: [1] and [2] are due to the implementation of views,
+        where the view logical name is found:
+        - sometime in (db, table_name) if the view has not be processed,
+        - sometime in (view_db, view_name) if the view has been processed.
+        See the comments in mysql_make_view()
+      */
+
+      /* [1] mysql_load_view_md5 uses (db, table_name) */
+      dummy.db= tables->view_db.str;
+      dummy.table_name= tables->view_name.str;
+      rc= mysql_load_view_md5(thd, &dummy);
+
+      if (rc == 0)
+      {
+        /* [2] TABLE_LIST::make_object_version uses (view_db, view_name) */
+        LEX fake_lex;
+        dummy.view_db= tables->view_db;
+        dummy.view_name= tables->view_name;
+        dummy.view= & fake_lex;
+        rc= dummy.make_object_version(& view_version);
+      }
+      else
+        rc= TABLE_LIST::make_object_null_version(& view_version,
+                                                 tables->db,
+                                                 tables->table_name);
+
+      if (rc != 0)
+        return 1;
+
+      if (observer->notify(thd, view_version))
+        return 1;
+    }
+  }
+
+  return 0;
+}
+
+int
+Prepare_observer::notify(THD *thd, const Object_version& over)
+{
+  switch(over.m_oid.get_type())
+  {
+  case OBJECT_TYPE_TABLE:
+  case OBJECT_TYPE_VIEW:
+  case OBJECT_TYPE_PROCEDURE:
+  case OBJECT_TYPE_FUNCTION:
+    return m_coll->add(over.m_oid, over.m_vid);
+  default:
+    return 0;
+  }
+}
+
+bool
+Execute_observer::is_safe_table_operation(LEX *lex) const
+{
+  switch(lex->sql_command)
+  {
+  case SQLCOM_ALTER_TABLE:
+  case SQLCOM_REPAIR:
+  case SQLCOM_ANALYZE:
+  case SQLCOM_OPTIMIZE:
+    return TRUE;
+
+  case SQLCOM_SHOW_CREATE: // safe for tables, not views
+  default:
+    return FALSE;
+  }
+}
+
+bool
+Execute_observer::is_safe_sp_operation(LEX *lex) const
+{
+  switch(lex->sql_command)
+  {
+  case SQLCOM_SHOW_CREATE_PROC:
+  case SQLCOM_SHOW_CREATE_FUNC:
+    return TRUE;
+
+  default:
+    return FALSE;
+  }
+}
+
+int
+Execute_observer::notify(THD *thd, const Object_version& over)
+{
+  const Object_version *collected;
+  int rc;
+
+  DBUG_ENTER("Execute_observer::notify()");
+
+  switch(over.m_oid.get_type())
+  {
+  case OBJECT_TYPE_TABLE:
+  case OBJECT_TYPE_VIEW:
+    rc= m_coll->find(over.m_oid, & collected);
+    if (rc != 0)
+      goto err;
+
+    if (collected)
+    {
+      if (! collected->m_vid.is_equal(over.m_vid))
+      {
+        bool allow_change= FALSE;
+        if ((over.m_oid.get_type() == OBJECT_TYPE_TABLE) &&
+            m_expected_table_change.is_equal(over.m_oid))
+        {
+          allow_change= TRUE;
+        }
+
+        if (! allow_change &&
+            ! is_safe_table_operation(thd->lex))
+        {
+          my_error(ER_PS_INVALIDATED, MYF(0),
+                   over.m_oid.get_printable_type(),
+                   over.m_oid.get_printable_name());
+          rc= 1;
+        }
+      }
+    }
+    else
+    {
+      /*
+        See if:
+        - a TABLE was replaced by a VIEW,
+        - a VIEW was replaced by a TABLE
+      */
+      Object_id shadowing_oid(over.m_oid);
+      if (over.m_oid.get_type() == OBJECT_TYPE_TABLE)
+        shadowing_oid.reset_type(OBJECT_TYPE_VIEW);
+      else
+        shadowing_oid.reset_type(OBJECT_TYPE_TABLE);
+
+      rc= m_coll->find(shadowing_oid, & collected);
+      if (rc != 0)
+        goto err;
+
+      if (collected)
+      {
+        my_error(ER_PS_INVALIDATED, MYF(0),
+                 shadowing_oid.get_printable_type(),
+                 shadowing_oid.get_printable_name());
+        rc= 1;
+      }
+      else
+      {
+        /*
+          Here, the Prepared statement execute code is discovering
+          an object that was never found during the prepare phase.
+          For some statements, the prepare phase does not open and lock
+          tables (which is ok, performances), so that this is a normal case.
+          Example of such statements are:
+          - SHOW COLUMNS FROM <table>
+            --> open the table <table>
+          - INSTALL PLUGIN ... SONAME ...
+            --> open the table mysql.plugin
+          - SHOW CREATE TRIGGER t1_bi
+            --> open the table t1 (that the trigger relates to)
+          - SHOW COLUMNS FROM <view>
+            --> open the view <view>
+          Once a real object (version not null) is found by the first
+          execute(), it's added to the prepared statement internal state.
+          This is to properly detect further DDL changes for these late found
+          dependencies.
+          This is critical, since until Bug#27011 is fixed, the internal
+          state of the statement can contain a copy of view DDL,
+          which needs to be checked for changes later.
+        */
+        if (! over.m_vid.is_null())
+          rc= m_coll->add(over.m_oid, over.m_vid);
+      }
+    }
+    break;
+  case OBJECT_TYPE_PROCEDURE:
+  case OBJECT_TYPE_FUNCTION:
+    rc= m_coll->find(over.m_oid, & collected);
+    if (rc != 0)
+      goto err;
+
+    if (! collected || ! collected->m_vid.is_equal(over.m_vid))
+    {
+      if (! is_safe_sp_operation(thd->lex))
+      {
+        /*
+          For these objects, we do require an exact match:
+          - collected != NULL means the object name must have been seen
+          during prepare, and not be discovered during execute.
+          - Version_id::is_equal() means that:
+            - either the object did not exist, and still does not exist
+            - or the object existed and is unchanged.
+        */
+        my_error(ER_PS_INVALIDATED, MYF(0),
+                 over.m_oid.get_printable_type(),
+                 over.m_oid.get_printable_name());
+        rc= 1;
+      }
+    }
+    break;
+  case NO_OBJECT_TYPE:
+  default:
+    rc= 0;
+    break;
+  }
+
+err:
+  DBUG_RETURN(rc);
+}
+
+
 /****************************************************************************/
 
 /**
@@ -153,6 +447,9 @@ public:
   bool execute(String *expanded_query, bool open_cursor);
   /* Destroy this statement */
   bool deallocate();
+
+  void expect_table_change(const Object_id& oid);
+
 private:
   /**
     Store the parsed tree of a prepared statement here.
@@ -163,6 +460,26 @@ private:
     SELECT_LEX and other classes).
   */
   MEM_ROOT main_mem_root;
+
+  /**
+    Collection of all objects versions this prepared statement depends on.
+    The prepared statement internal state contains optimizations based on
+    the objects seen during prepare. Should any of the objects listed change
+    between prepare() and execute(), the prepared statement will be declared
+    invalidated (with some exceptions, see Execute_observer).
+  */
+  Object_version_collection m_versions;
+
+  /**
+    Allow DDL for a table to occur between prepare and execute.
+    This is a special case for the CREATE TABLE ... SELECT statement,
+    which is hybrid (DDL + DML).
+    Optimizations performed for the DML part (that can contain sub selects)
+    are protected by m_versions.
+    Allowable exceptions for the DDL part (the table to create) are
+    recorded in this attribute.
+  */
+  Object_id m_expected_table_change;
 };
 
 
@@ -1495,6 +1812,16 @@ static bool mysql_test_create_table(Prep
       create_table->create= TRUE;
     }
 
+    /*
+      FIXME: instead, open_normal_and_derived_tables
+      should not open the table to create
+    */
+    Object_version table_version;
+    TABLE_SHARE::make_object_null_version(& table_version,
+                                          create_table->db,
+                                          create_table->table_name);
+    stmt->expect_table_change(table_version.m_oid);
+
     if (open_normal_and_derived_tables(stmt->thd, lex->query_tables, 0))
       DBUG_RETURN(TRUE);
 
@@ -1506,7 +1833,7 @@ static bool mysql_test_create_table(Prep
     res= select_like_stmt_test(stmt, 0, 0);
   }
 
-  /* put tables back for PS rexecuting */
+  /* put tables back for PS re-executing */
   lex->link_first_table_back(create_table, link_to_local);
   DBUG_RETURN(res);
 }
@@ -2698,11 +3025,20 @@ 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_versions(),
+  m_expected_table_change()
 {
   init_alloc_root(&main_mem_root, thd_arg->variables.query_alloc_block_size,
                   thd_arg->variables.query_prealloc_size);
   *last_error= '\0';
+
+  if (m_versions.init(& main_mem_root))
+  {
+    state= Query_arena::ERROR;
+    last_errno= ER_OUTOFMEMORY;
+    sprintf(last_error, ER(ER_OUTOFMEMORY), 0);
+  }
 }
 
 
@@ -2898,6 +3234,10 @@ bool Prepared_statement::prepare(const c
   */
   DBUG_ASSERT(thd->change_list.is_empty());
 
+  Prepare_observer observer(& m_versions);
+  Object_observer *save_observer= thd->m_object_observer;
+  thd->m_object_observer= & observer;
+
   /* 
    The only case where we should have items in the thd->free_list is
    after stmt->set_params_from_vars(), which may in some cases create
@@ -2907,6 +3247,14 @@ bool Prepared_statement::prepare(const c
   if (error == 0)
     error= check_prepared_statement(this, name.str != 0);
 
+  if (error == 0)
+    error= sp_refresh_routines(thd, thd->lex);
+
+  if (error == 0)
+    error= visit_all_views(thd, thd->lex->query_tables);
+
+  thd->m_object_observer= save_observer;
+
   /*
     Currently CREATE PROCEDURE/TRIGGER/EVENT are prohibited in prepared
     statements: ensure we have no memory leak here if by someone tries
@@ -2952,6 +3300,12 @@ bool Prepared_statement::prepare(const c
   DBUG_RETURN(error);
 }
 
+void Prepared_statement::expect_table_change(const Object_id& oid)
+{
+  DBUG_ASSERT(m_expected_table_change.get_type() == NO_OBJECT_TYPE);
+  m_expected_table_change.set(oid);
+}
+
 /*
   Execute a prepared statement.
 
@@ -3083,22 +3437,53 @@ bool Prepared_statement::execute(String 
 
   /* Go! */
 
-  if (open_cursor)
-    error= mysql_open_cursor(thd, (uint) ALWAYS_MATERIALIZED_CURSOR,
-                             &result, &cursor);
-  else
-  {
+  /*
+    Try to find it in the query cache, if not, execute it.
+    Note that multi-statements cannot exist here (they are not supported in
+    prepared statements).
+  */
+  if (query_cache_send_result_to_client(thd, thd->query,
+                                        thd->query_length) <= 0)
+  {
+    int rc;
+    Execute_observer observer(& m_versions,
+                              m_expected_table_change);
+    Object_observer *save_observer= thd->m_object_observer;
+    thd->m_object_observer= & observer;
+
     /*
-      Try to find it in the query cache, if not, execute it.
-      Note that multi-statements cannot exist here (they are not supported in
-      prepared statements).
+      For tables, the TABLE_SHARE cache automatically detects outdated
+      or missing tables, and transparently reload them.
+      For Stored Procedures and Stored Functions, sp_cache does not
+      provide this functionality, so that the list of routines used
+      by this prepared statement has to be refreshed in the sp_cache
+      explicitly.
     */
-    if (query_cache_send_result_to_client(thd, thd->query,
-                                          thd->query_length) <= 0)
+    rc= sp_refresh_routines(thd, thd->lex);
+
+    /*
+      For views, there is no caching at all,
+      and the content of thd->lex->query_tables could be obsolete by now.
+      Making sure that the views in-lined in TABLE_LIST are still valid.
+    */
+    if (rc == 0)
+      rc= recheck_all_views(thd, thd->lex->query_tables);
+
+    if (rc == 0)
     {
-      error= mysql_execute_command(thd);
-      query_cache_end_of_result(thd);
+      if (open_cursor)
+        error= mysql_open_cursor(thd, (uint) ALWAYS_MATERIALIZED_CURSOR,
+                                 &result, &cursor);
+      else
+      {
+        error= mysql_execute_command(thd);
+        query_cache_end_of_result(thd);
+      }
     }
+    else
+      error= 1;
+
+    thd->m_object_observer= save_observer;
   }
 
   /*
diff -Nrup a/sql/sql_view.cc b/sql/sql_view.cc
--- a/sql/sql_view.cc	2007-10-30 11:08:13 -06:00
+++ b/sql/sql_view.cc	2007-11-26 09:14:51 -07:00
@@ -674,6 +674,19 @@ static File_option view_parameters[]=
   FILE_OPTIONS_STRING}
 };
 
+/* Short parsing, only to read MD5 */
+static File_option view_md5_parameters[]=
+{{{ C_STRING_WITH_LEN("query")},
+  my_offsetof(TABLE_LIST, select_stmt),
+  FILE_OPTIONS_ESTRING},
+ {{ C_STRING_WITH_LEN("md5")},
+  my_offsetof(TABLE_LIST, md5),
+  FILE_OPTIONS_STRING},
+ {{NullS, 0},			0,
+  FILE_OPTIONS_STRING}
+};
+
+
 static LEX_STRING view_file_type[]= {{(char*) STRING_WITH_LEN("VIEW") }};
 
 
@@ -1440,6 +1453,44 @@ err:
   table->view= 0;	// now it is not VIEW placeholder
   result= 1;
   goto end;
+}
+
+int mysql_load_view_md5(THD *thd, TABLE_LIST *view)
+{
+  int rc;
+  LEX_STRING pathstr;
+  File_parser *parser;
+  char path_buff[FN_REFLEN];
+  TABLE_LIST dummy;
+
+  DBUG_ENTER("mysql_load_view_md5");
+  DBUG_PRINT("info", ("view: (%s.%s)", view->db, view->table_name));
+
+  bzero(& dummy, sizeof(dummy));
+
+  pathstr.str= (char *) path_buff;
+  pathstr.length= build_table_filename(path_buff, sizeof(path_buff) - 1,
+                                       view->db, view->table_name,
+                                       reg_ext, 0);
+
+  parser= sql_parse_prepare(&pathstr, thd->mem_root, 0);
+  if (parser &&
+      parser->ok() &&
+      is_equal(&view_type, parser->type()) &&
+      parser->parse((uchar*) &dummy,
+                    thd->mem_root,
+                    view_md5_parameters,
+                    array_elements(view_md5_parameters)-1,
+                    &file_parser_dummy_hook) == 0)
+  {
+    view->md5.str= dummy.md5.str;
+    view->md5.length= dummy.md5.length;
+    rc= 0;
+  }
+  else
+    rc= 1;
+
+  DBUG_RETURN(rc);
 }
 
 
diff -Nrup a/sql/sql_view.h b/sql/sql_view.h
--- a/sql/sql_view.h	2006-12-23 12:19:56 -07:00
+++ b/sql/sql_view.h	2007-11-26 09:14:52 -07:00
@@ -31,6 +31,8 @@ frm_type_enum mysql_frm_type(THD *thd, c
 
 int view_checksum(THD *thd, TABLE_LIST *view);
 
+int mysql_load_view_md5(THD *thd, TABLE_LIST *view);
+
 extern TYPELIB updatable_views_with_limit_typelib;
 
 bool check_duplicate_names(List<Item>& item_list, bool gen_unique_view_names);
diff -Nrup a/sql/table.cc b/sql/table.cc
--- a/sql/table.cc	2007-11-10 03:58:37 -07:00
+++ b/sql/table.cc	2007-11-26 09:14:52 -07:00
@@ -250,6 +250,54 @@ TABLE_CATEGORY get_table_category(const 
   return TABLE_CATEGORY_USER;
 }
 
+int TABLE_SHARE::make_object_version(Object_version * version)
+{
+  int rc;
+
+  rc= version->m_oid.set_table(db, table_name);
+
+  if (tmp_table == INTERNAL_TMP_TABLE)
+  {
+    /*
+      table_map_id is THD::query_id.
+      The version number is not persistent,
+      but the table is not persistent either,
+      so using table_map_id is ok here.
+    */
+    version->m_vid.set_long_1(table_map_id);
+  }
+  else
+  {
+    /*
+      TABLE_SHARE::table_map_id is a union of two things:
+      - for real tables, it's a counter, protected by LOCK_open,
+      and assigned in assign_new_table_id().
+      - for temporary tables, it's the THD::query_id that created the table.
+      As there is no guarantee that counters from these disjointed spaces
+      won't collide, we must use different namespaces in Version_id,
+      to disambiguate TABLE_SHARE::table_map_id.
+
+      FIXME: Using table_map_id as a version number is poor, since
+      the version number is not persistent. In particular, it does not
+      survive a FLUSH TABLES: we should use something else here, like an
+      md5 checksum based on the table definition.
+    */
+    version->m_vid.set_long_2(table_map_id);
+  }
+
+  return rc;
+}
+
+int TABLE_SHARE::make_object_null_version(
+  Object_version * version, const char* db, const char* name)
+{
+  int rc;
+
+  rc= version->m_oid.set_table(db, name);
+  version->m_vid.set_null();
+
+  return rc;
+}
 
 /*
   Allocate a setup TABLE_SHARE structure
@@ -376,6 +424,10 @@ void init_tmp_table_share(TABLE_SHARE *s
   share->table_map_id= ~0UL;
   share->cached_row_logging_check= -1;
 
+#warning "Change this code after Bug#26379 is merged"
+  static int waiting_for_51_engines_to_merge= 0;
+  share->table_map_id= waiting_for_51_engines_to_merge++;
+
   DBUG_VOID_RETURN;
 }
 
@@ -2922,6 +2974,28 @@ void st_table::reset_item_list(List<Item
     DBUG_ASSERT(item_field != 0);
     item_field->reset_field(*ptr);
   }
+}
+
+int TABLE_LIST::make_object_version(Object_version * version)
+{
+  int rc;
+
+  DBUG_ASSERT(view);
+  rc= version->m_oid.set_view(view_db, view_name);
+  version->m_vid.set_md5(md5);
+
+  return rc;
+}
+
+int TABLE_LIST::make_object_null_version(
+  Object_version * version, const char* db, const char* name)
+{
+  int rc;
+
+  rc= version->m_oid.set_view(db, name);
+  version->m_vid.set_null();
+
+  return rc;
 }
 
 /*
diff -Nrup a/sql/table.h b/sql/table.h
--- a/sql/table.h	2007-11-01 14:52:52 -06:00
+++ b/sql/table.h	2007-11-26 09:14:52 -07:00
@@ -24,6 +24,7 @@ class st_select_lex;
 class partition_info;
 class COND_EQUAL;
 class Security_context;
+class Object_version;
 
 /*************************************************************************/
 
@@ -350,6 +351,7 @@ typedef struct st_table_share
   ulong table_map_id;                   /* for row-based replication */
   ulonglong table_map_version;
 
+  char m_md5_value[32];
   /*
     Cache for row-based replication table share checks that does not
     need to be repeated. Possible values are: -1 when cache value is
@@ -431,6 +433,10 @@ typedef struct st_table_share
   {
     return (table_category == TABLE_CATEGORY_PERFORMANCE);
   }
+
+  int make_object_version(Object_version * version);
+  static int make_object_null_version(
+    Object_version * version, const char* db, const char* name);
 } TABLE_SHARE;
 
 
@@ -878,6 +884,10 @@ class Index_hint;
 struct TABLE_LIST
 {
   TABLE_LIST() {}                          /* Remove gcc warning */
+
+  int make_object_version(Object_version * version);
+  static int make_object_null_version(
+    Object_version * version, const char* db, const char* name);
 
   /**
     Prepare TABLE_LIST that consists of one table instance to use in
diff -Nrup a/tests/mysql_client_test.c b/tests/mysql_client_test.c
--- a/tests/mysql_client_test.c	2007-10-31 09:54:51 -06:00
+++ b/tests/mysql_client_test.c	2007-11-26 09:14:52 -07:00
@@ -5771,10 +5771,10 @@ static void test_prepare_alter()
 
   is_null= 1;
   rc= mysql_stmt_execute(stmt);
-  check_execute(stmt, rc);
+  check_execute_r(stmt, rc); // ER_PS_INVALIDATED
 
   rc= my_stmt_result("SELECT * FROM test_prep_alter");
-  DIE_UNLESS(rc == 4);
+  DIE_UNLESS(rc == 3);
 
   mysql_stmt_close(stmt);
 }
@@ -9803,6 +9803,7 @@ static void test_selecttmp()
 static void test_create_drop()
 {
   MYSQL_STMT *stmt_create, *stmt_drop, *stmt_select, *stmt_create_select;
+  MYSQL_STMT *stmt_select_inner;
   char *query;
   int rc, i;
   myheader("test_table_manipulation");
@@ -9846,10 +9847,19 @@ static void test_create_drop()
       fprintf(stdout, "created %i\n", i);
 
     rc= mysql_stmt_execute(stmt_select);
-    check_execute(stmt_select, rc);
-    rc= my_process_stmt_result(stmt_select);
+    check_execute_r(stmt_select, rc); // ER_PS_INVALIDATED
+
+    query= (char*)"select a in (select a from t2) from t1";
+    stmt_select_inner= mysql_simple_prepare(mysql, query);
+    check_stmt(stmt_select_inner);
+
+    rc= mysql_stmt_execute(stmt_select_inner);
+    check_execute(stmt_select_inner, rc);
+    rc= my_process_stmt_result(stmt_select_inner);
     DIE_UNLESS(rc == 0);
 
+    mysql_stmt_close(stmt_select_inner);
+
     rc= mysql_stmt_execute(stmt_drop);
     check_execute(stmt_drop, rc);
     if (!opt_silent)
@@ -9861,10 +9871,19 @@ static void test_create_drop()
       fprintf(stdout, "created select %i\n", i);
 
     rc= mysql_stmt_execute(stmt_select);
-    check_execute(stmt_select, rc);
-    rc= my_process_stmt_result(stmt_select);
+    check_execute_r(stmt_select, rc); // ER_PS_INVALIDATED
+
+    query= (char*)"select a in (select a from t2) from t1";
+    stmt_select_inner= mysql_simple_prepare(mysql, query);
+    check_stmt(stmt_select_inner);
+
+    rc= mysql_stmt_execute(stmt_select_inner);
+    check_execute(stmt_select_inner, rc);
+    rc= my_process_stmt_result(stmt_select_inner);
     DIE_UNLESS(rc == 3);
 
+    mysql_stmt_close(stmt_select_inner);
+
     rc= mysql_stmt_execute(stmt_drop);
     check_execute(stmt_drop, rc);
     if (!opt_silent)
@@ -14354,34 +14373,88 @@ static void test_bug10760()
   }
   else
   {
+    bzero(my_bind, sizeof(my_bind));
+    my_bind[0].buffer_type= MYSQL_TYPE_STRING;
+    my_bind[0].buffer= (void*) id_buf;
+    my_bind[0].buffer_length= sizeof(id_buf);
+    my_bind[0].length= &id_len;
+
     stmt_text= "select id from t1 order by 1";
     rc= mysql_stmt_prepare(stmt, stmt_text, strlen(stmt_text));
     check_execute(stmt, rc);
 
+    mysql_stmt_bind_result(stmt, my_bind);
+    check_execute(stmt, rc);
+
+    rc= mysql_stmt_execute(stmt);
+    check_execute(stmt, rc);
+
+    rc= mysql_stmt_fetch(stmt);
+    check_execute(stmt, rc);
+    if (!opt_silent)
+      printf("Fetched (before alter) row %s\n", id_buf);
+
+    rc= mysql_stmt_fetch(stmt);
+    check_execute(stmt, rc);
+    if (!opt_silent)
+      printf("Fetched (before alter) row %s\n", id_buf);
+
     rc= mysql_query(mysql, "alter table t1 engine=InnoDB");
     myquery(rc);
 
-    bzero(my_bind, sizeof(my_bind));
-    my_bind[0].buffer_type= MYSQL_TYPE_STRING;
-    my_bind[0].buffer= (void*) id_buf;
-    my_bind[0].buffer_length= sizeof(id_buf);
-    my_bind[0].length= &id_len;
+    rc= mysql_stmt_fetch(stmt);
     check_execute(stmt, rc);
-    mysql_stmt_bind_result(stmt, my_bind);
+    if (!opt_silent)
+      printf("Fetched (after alter) row %s\n", id_buf);
+
+    rc= mysql_stmt_fetch(stmt);
+    check_execute(stmt, rc);
+    if (!opt_silent)
+      printf("Fetched (after alter) row %s\n", id_buf);
 
     rc= mysql_stmt_execute(stmt);
+    check_execute_r(stmt, rc); // ER_PS_INVALIDATED
+
     rc= mysql_stmt_fetch(stmt);
+    check_execute_r(stmt, rc); // CR_NO_RESULT_SET
+
+    stmt_text= "select id from t1 order by 1";
+    rc= mysql_stmt_prepare(stmt, stmt_text, strlen(stmt_text));
+    check_execute(stmt, rc);
+
+    mysql_stmt_bind_result(stmt, my_bind);
+    check_execute(stmt, rc);
+
+    rc= mysql_query(mysql, "update t1 set id=id+100");
     DIE_UNLESS(rc == 0);
+
+    rc= mysql_stmt_execute(stmt);
+    check_execute(stmt, rc);
+
+    rc= mysql_stmt_fetch(stmt);
+    check_execute(stmt, rc);
+    if (!opt_silent)
+      printf("Fetched (before rollback) row %s\n", id_buf);
+
+    rc= mysql_stmt_fetch(stmt);
+    check_execute(stmt, rc);
     if (!opt_silent)
-      printf("Fetched row %s\n", id_buf);
-    rc= mysql_rollback(mysql);                  /* should close the cursor */
+      printf("Fetched (before rollback) row %s\n", id_buf);
+
+    /* should close the cursor ... */
+    rc= mysql_rollback(mysql);
     myquery(rc);
-#if 0
+
+    /* ... but did not. */
     rc= mysql_stmt_fetch(stmt);
-    DIE_UNLESS(rc);
+    check_execute(stmt, rc);
     if (!opt_silent)
-      printf("Got error (as expected): %s\n", mysql_error(mysql));
-#endif
+      printf("Fetched (after rollback) row %s\n", id_buf);
+
+    rc= mysql_stmt_fetch(stmt);
+    check_execute(stmt, rc);
+    if (!opt_silent)
+      printf("Fetched (after rollback) row %s\n", id_buf);
   }
 
   mysql_stmt_close(stmt);
Thread
bk commit into 5.1 tree (malff:1.2616) BUG#12093marc.alff26 Nov