Merge bk-internal.mysql.com:/home/bk/mysql-5.0

into  mysql.com:/home/dlenev/src/mysql-5.0-bg12198-2


mysql-test/r/sp.result:
  Auto merged
mysql-test/t/sp.test:
  Auto merged
sql/sp_head.cc:
  Auto merged

2 files changed, 145 insertions, 2 deletions

diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index ded9754f172..d56cf086cdf 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -918,6 +918,11 @@ drop function if exists f5|
 drop function if exists f6|
 drop function if exists f7|
 drop function if exists f8|
+drop function if exists f9|
+drop function if exists f10|
+drop function if exists f11|
+drop function if exists f12_1|
+drop function if exists f12_2|
 drop view if exists v0|
 drop view if exists v1|
 drop view if exists v2|
@@ -1097,6 +1102,62 @@ ERROR HY000: Table 't1' was not locked with LOCK TABLES
 select f4()|
 ERROR HY000: Table 't2' was not locked with LOCK TABLES
 unlock tables|
+create function f9() returns int
+begin
+declare a, b int;
+drop temporary table if exists t3;
+create temporary table t3 (id int);
+insert into t3 values (1), (2), (3);
+set a:= (select count(*) from t3);
+set b:= (select count(*) from t3 t3_alias);
+return a + b;
+end|
+select f9()|
+f9()
+6
+Warnings:
+Note	1051	Unknown table 't3'
+select f9() from t1 limit 1|
+f9()
+6
+create function f10() returns int
+begin
+drop temporary table if exists t3;
+create temporary table t3 (id int);
+insert into t3 select id from t4;
+return (select count(*) from t3);
+end|
+select f10()|
+ERROR 42S02: Table 'test.t4' doesn't exist
+create table t4 as select 1 as id|
+select f10()|
+f10()
+1
+create function f11() returns int
+begin
+drop temporary table if exists t3;
+create temporary table t3 (id int);
+insert into t3 values (1), (2), (3);
+return (select count(*) from t3 as a, t3 as b);
+end|
+select f11()|
+ERROR HY000: Can't reopen table: 'a'
+select f11() from t1|
+ERROR HY000: Can't reopen table: 'a'
+create function f12_1() returns int
+begin
+drop temporary table if exists t3;
+create temporary table t3 (id int);
+insert into t3 values (1), (2), (3);
+return f12_2();
+end|
+create function f12_2() returns int
+return (select count(*) from t3)|
+drop temporary table t3|
+select f12_1()|
+ERROR 42S02: Table 'test.t3' doesn't exist
+select f12_1() from t1 limit 1|
+ERROR 42S02: Table 'test.t3' doesn't exist
 drop function f0|
 drop function f1|
 drop function f2|
@@ -1106,11 +1167,17 @@ drop function f5|
 drop function f6|
 drop function f7|
 drop function f8|
+drop function f9|
+drop function f10|
+drop function f11|
+drop function f12_1|
+drop function f12_2|
 drop view v0|
 drop view v1|
 drop view v2|
 delete from t1 |
 delete from t2 |
+drop table t4|
 drop table if exists fac|
 create table fac (n int unsigned not null primary key, f bigint unsigned)|
 drop procedure if exists ifac|
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index f73288f04ba..a1eba73635e 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -1157,6 +1157,11 @@ drop function if exists f5|
 drop function if exists f6|
 drop function if exists f7|
 drop function if exists f8|
+drop function if exists f9|
+drop function if exists f10|
+drop function if exists f11|
+drop function if exists f12_1|
+drop function if exists f12_2|
 drop view if exists v0|
 drop view if exists v1|
 drop view if exists v2|
@@ -1234,8 +1239,6 @@ create function f7() returns int
 select f6()|
 select id, f6() from t1|
 
-# TODO Test temporary table handling
-
 #
 # Let us test how new locking work with views
 #
@@ -1316,6 +1319,73 @@ select * from v1, t1|
 select f4()|
 unlock tables|
 
+# Tests for handling of temporary tables in functions.
+#
+# Unlike for permanent tables we should be able to create, use
+# and drop such tables in functions.
+# 
+# Simplest function using temporary table. It is also test case for bug 
+# #12198 "Temporary table aliasing does not work inside stored functions"
+create function f9() returns int
+begin
+  declare a, b int;
+  drop temporary table if exists t3;
+  create temporary table t3 (id int);
+  insert into t3 values (1), (2), (3);
+  set a:= (select count(*) from t3);
+  set b:= (select count(*) from t3 t3_alias);
+  return a + b;
+end|
+# This will emit warning as t3 was not existing before.
+select f9()|
+select f9() from t1 limit 1|
+
+# Function which uses both temporary and permanent tables.
+create function f10() returns int
+begin
+  drop temporary table if exists t3;
+  create temporary table t3 (id int);
+  insert into t3 select id from t4;
+  return (select count(*) from t3);
+end|
+# Check that we don't ignore completely tables used in function
+--error ER_NO_SUCH_TABLE
+select f10()|
+create table t4 as select 1 as id|
+select f10()|
+
+# Practical cases which we don't handle well (yet)
+#
+# Function which does not work because of well-known and documented
+# limitation of MySQL. We can't use the several instances of the
+# same temporary table in statement.
+create function f11() returns int
+begin
+  drop temporary table if exists t3;
+  create temporary table t3 (id int);
+  insert into t3 values (1), (2), (3);
+  return (select count(*) from t3 as a, t3 as b);
+end|
+--error ER_CANT_REOPEN_TABLE
+select f11()|
+--error ER_CANT_REOPEN_TABLE
+select f11() from t1|
+# We don't handle temporary tables used by nested functions well
+create function f12_1() returns int
+begin
+  drop temporary table if exists t3;
+  create temporary table t3 (id int);
+  insert into t3 values (1), (2), (3);
+  return f12_2();
+end|
+create function f12_2() returns int
+  return (select count(*) from t3)|
+# We need clean start to get error
+drop temporary table t3|
+--error ER_NO_SUCH_TABLE
+select f12_1()|
+--error ER_NO_SUCH_TABLE
+select f12_1() from t1 limit 1|
 
 # Cleanup
 drop function f0|
@@ -1327,11 +1397,17 @@ drop function f5|
 drop function f6|
 drop function f7|
 drop function f8|
+drop function f9|
+drop function f10|
+drop function f11|
+drop function f12_1|
+drop function f12_2|
 drop view v0|
 drop view v1|
 drop view v2|
 delete from t1 |
 delete from t2 |
+drop table t4|
 
 # End of non-bug tests