Enable support of access to tables from triggers. Thus fix bug #8406 "Triggers

crash if referencing a table" and several other related bugs.
Fix for bug #11834 "Re-execution of prepared statement with dropped function
crashes server." which was spotted during work on previous bugs.

Also couple of nice cleanups:
- Replaced two separate hashes for stored routines used by statement with one.
- Now instead of doing one pass through all routines used in statement for
  caching them and then doing another pass for adding their tables to table
  list, we do only one pass during which do both things.

6 files changed, 216 insertions, 8 deletions

diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
index b6ba737a8ba..1af88049adb 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -686,3 +686,19 @@ ERROR 0A000: EXECUTE is not allowed in stored procedures
 create function f() returns int begin execute stmt;
 ERROR 0A000: EXECUTE is not allowed in stored procedures
 deallocate prepare stmt;
+drop function if exists bug11834_1;
+drop function if exists bug11834_2;
+create function bug11834_1() returns int return 10;
+create function bug11834_2() returns int return bug11834_1();
+prepare stmt from "select bug11834_2()";
+execute stmt;
+bug11834_2()
+10
+execute stmt;
+bug11834_2()
+10
+drop function bug11834_1;
+execute stmt;
+ERROR 42000: FUNCTION test.bug11834_1 does not exist
+deallocate prepare stmt;
+drop function bug11834_2;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index bf6a4dbf68c..7f6d3be8956 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -1229,7 +1229,7 @@ a
 select * from v1|
 a
 3
-select * from v1, v2|
+select * from v1, t1|
 ERROR HY000: Table 't1' was not locked with LOCK TABLES
 select f4()|
 ERROR HY000: Table 't2' was not locked with LOCK TABLES
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index 996a692d531..746c900d743 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -1,6 +1,7 @@
-drop table if exists t1, t2;
+drop table if exists t1, t2, t3;
 drop view if exists v1;
 drop database if exists mysqltest;
+drop function if exists f1;
 create table t1 (i int);
 create trigger trg before insert on t1 for each row set @a:=1;
 set @a:=0;
@@ -182,6 +183,96 @@ select @log;
 @log
 (BEFORE_INSERT: new=(id=1, data=5))(BEFORE_UPDATE: old=(id=1, data=4) new=(id=1, data=6))(AFTER_UPDATE: old=(id=1, data=4) new=(id=1, data=6))(BEFORE_INSERT: new=(id=3, data=3))(AFTER_INSERT: new=(id=3, data=3))
 drop table t1;
+create table t1 (id int primary key, data varchar(10), fk int);
+create table t2 (event varchar(100));
+create table t3 (id int primary key);
+create trigger t1_ai after insert on t1 for each row 
+insert into t2 values (concat("INSERT INTO t1 id=", new.id, " data='", new.data, "'"));
+insert into t1 (id, data) values (1, "one"), (2, "two");
+select * from t1;
+id	data	fk
+1	one	NULL
+2	two	NULL
+select * from t2;
+event
+INSERT INTO t1 id=1 data='one'
+INSERT INTO t1 id=2 data='two'
+drop trigger t1.t1_ai;
+create trigger t1_bi before insert on t1 for each row
+begin
+if exists (select id from t3 where id=new.fk) then
+insert into t2 values (concat("INSERT INTO t1 id=", new.id, " data='", new.data, "' fk=", new.fk));
+else
+insert into t2 values (concat("INSERT INTO t1 FAILED id=", new.id, " data='", new.data, "' fk=", new.fk));
+set new.id= NULL;
+end if;
+end|
+insert into t3 values (1);
+insert into t1 values (4, "four", 1), (5, "five", 2);
+ERROR 23000: Column 'id' cannot be null
+select * from t1;
+id	data	fk
+1	one	NULL
+2	two	NULL
+4	four	1
+select * from t2;
+event
+INSERT INTO t1 id=1 data='one'
+INSERT INTO t1 id=2 data='two'
+INSERT INTO t1 id=4 data='four' fk=1
+INSERT INTO t1 FAILED id=5 data='five' fk=2
+drop table t1, t2, t3;
+create table t1 (id int primary key, data varchar(10));
+create table t2 (seq int);
+insert into t2 values (10);
+create function f1 () returns int return (select max(seq) from t2);
+create trigger t1_bi before insert on t1 for each row
+begin
+if new.id > f1() then
+set new.id:= f1();
+end if;
+end|
+select f1();
+f1()
+10
+insert into t1 values (1, "first");
+insert into t1 values (f1(), "max");
+select * from t1;
+id	data
+1	first
+10	max
+drop table t1, t2;
+drop function f1;
+create table t1 (id int primary key, fk_t2 int);
+create table t2 (id int primary key, fk_t3 int);
+create table t3 (id int primary key);
+insert into t1 values (1,1), (2,1), (3,2);
+insert into t2 values (1,1), (2,2);
+insert into t3 values (1), (2);
+create trigger t3_ad after delete on t3 for each row
+delete from t2 where fk_t3=old.id;
+create trigger t2_ad after delete on t2 for each row
+delete from t1 where fk_t2=old.id;
+delete from t3 where id = 1;
+select * from t1 left join (t2 left join t3 on t2.fk_t3 = t3.id) on t1.fk_t2 = t2.id;
+id	fk_t2	id	fk_t3	id
+3	2	2	2	2
+drop table t1, t2, t3;
+create table t1 (id int primary key, copy int);
+create table t2 (id int primary key, data int);
+insert into t2 values (1,1), (2,2);
+create trigger t1_bi before insert on t1 for each row
+set new.copy= (select data from t2 where id = new.id);
+create trigger t1_bu before update on t1 for each row
+set new.copy= (select data from t2 where id = new.id);
+insert into t1 values (1,3), (2,4), (3,3);
+update t1 set copy= 1 where id = 2;
+select * from t1;
+id	copy
+1	1
+2	2
+3	NULL
+drop table t1, t2;
 create table t1 (i int);
 create trigger trg before insert on t1 for each row set @a:= old.i;
 ERROR HY000: There is no OLD row in on INSERT trigger
diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test
index faf6d8b4de3..e4cb352e544 100644
--- a/mysql-test/t/sp-error.test
+++ b/mysql-test/t/sp-error.test
@@ -986,3 +986,25 @@ create procedure p() execute stmt;
 create function f() returns int begin execute stmt;
 deallocate prepare stmt;
 
+#
+# Bug#11834 "Re-execution of prepared statement with dropped function
+# crashes server". Also tests handling of prepared stmts which use
+# stored functions but does not require prelocking.
+#
+--disable_warnings
+drop function if exists bug11834_1;
+drop function if exists bug11834_2;
+--enable_warnings
+create function bug11834_1() returns int return 10;
+create function bug11834_2() returns int return bug11834_1();
+prepare stmt from "select bug11834_2()";
+execute stmt;
+# Re-execution of statement should not crash server.
+execute stmt;
+drop function bug11834_1;
+# Attempt to execute statement should return proper error and 
+# should not crash server.
+--error ER_SP_DOES_NOT_EXIST
+execute stmt;
+deallocate prepare stmt;
+drop function bug11834_2;
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 20b1a98702c..cbd77ee0221 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -1414,7 +1414,8 @@ select * from v1|
 # views and functions ?
 create function f1() returns int
   return (select sum(data) from t1) + (select sum(data) from v1)|
-# FIXME All these just exceed file limit for me :)
+# This queries will crash server because we can't use LEX in
+# reenterable fashion yet. Patch disabling recursion will heal this.
 #select f1()|
 #select * from v1|
 #select * from v2|
@@ -1459,15 +1460,12 @@ select * from v2|
 select * from v1|
 # These should not work as we have too little instances of tables locked
 --error 1100
-select * from v1, v2|
+select * from v1, t1|
 --error 1100
 select f4()|
 unlock tables|
 
 
-# TODO We also should test integration with triggers
-
-
 # Cleanup
 drop function f0|
 drop function f1|
diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index 0c5ef077159..75b2f6de57a 100644
--- a/mysql-test/t/trigger.test
+++ b/mysql-test/t/trigger.test
@@ -3,9 +3,10 @@
 #
 
 --disable_warnings
-drop table if exists t1, t2;
+drop table if exists t1, t2, t3;
 drop view if exists v1;
 drop database if exists mysqltest;
+drop function if exists f1;
 --enable_warnings
 
 create table t1 (i int);
@@ -200,6 +201,86 @@ drop table t1;
 
 
 #
+# Let us test triggers which access other tables.
+#
+# Trivial trigger which inserts data into another table
+create table t1 (id int primary key, data varchar(10), fk int);
+create table t2 (event varchar(100));
+create table t3 (id int primary key);
+create trigger t1_ai after insert on t1 for each row 
+  insert into t2 values (concat("INSERT INTO t1 id=", new.id, " data='", new.data, "'"));
+insert into t1 (id, data) values (1, "one"), (2, "two");
+select * from t1;
+select * from t2;
+drop trigger t1.t1_ai;
+# Trigger which uses couple of tables (and partially emulates FK constraint)
+delimiter |;
+create trigger t1_bi before insert on t1 for each row
+begin
+  if exists (select id from t3 where id=new.fk) then
+    insert into t2 values (concat("INSERT INTO t1 id=", new.id, " data='", new.data, "' fk=", new.fk));
+  else
+    insert into t2 values (concat("INSERT INTO t1 FAILED id=", new.id, " data='", new.data, "' fk=", new.fk));
+    set new.id= NULL;
+  end if;
+end|
+delimiter ;|
+insert into t3 values (1);
+--error 1048
+insert into t1 values (4, "four", 1), (5, "five", 2);
+select * from t1;
+select * from t2;
+drop table t1, t2, t3;
+# Trigger which invokes function
+create table t1 (id int primary key, data varchar(10));
+create table t2 (seq int);
+insert into t2 values (10);
+create function f1 () returns int return (select max(seq) from t2);
+delimiter |;
+create trigger t1_bi before insert on t1 for each row
+begin
+  if new.id > f1() then
+    set new.id:= f1();
+  end if;
+end|
+delimiter ;|
+# Remove this once bug #11554 will be fixed.
+select f1();
+insert into t1 values (1, "first");
+insert into t1 values (f1(), "max");
+select * from t1;
+drop table t1, t2;
+drop function f1;
+# Trigger which forces invocation of another trigger
+# (emulation of FK on delete cascade policy)
+create table t1 (id int primary key, fk_t2 int);
+create table t2 (id int primary key, fk_t3 int);
+create table t3 (id int primary key);
+insert into t1 values (1,1), (2,1), (3,2);
+insert into t2 values (1,1), (2,2);
+insert into t3 values (1), (2);
+create trigger t3_ad after delete on t3 for each row
+  delete from t2 where fk_t3=old.id;
+create trigger t2_ad after delete on t2 for each row
+  delete from t1 where fk_t2=old.id;
+delete from t3 where id = 1;
+select * from t1 left join (t2 left join t3 on t2.fk_t3 = t3.id) on t1.fk_t2 = t2.id;
+drop table t1, t2, t3;
+# Trigger which assigns value selected from table to field of row
+# being inserted/updated.
+create table t1 (id int primary key, copy int);
+create table t2 (id int primary key, data int);
+insert into t2 values (1,1), (2,2);
+create trigger t1_bi before insert on t1 for each row
+  set new.copy= (select data from t2 where id = new.id);
+create trigger t1_bu before update on t1 for each row
+  set new.copy= (select data from t2 where id = new.id);
+insert into t1 values (1,3), (2,4), (3,3);
+update t1 set copy= 1 where id = 2;
+select * from t1;
+drop table t1, t2;
+
+#
 # Test of wrong column specifiers in triggers
 #
 create table t1 (i int);