WL#1366: Use the schema (db) associated with an SP.

Phase 2: Make SPs belong to a DB, and use qualified names.
  As a side effect, using USE in an SP is no longer allowed.
  (It just doesn't work otherwise.)


include/mysqld_error.h:
  New error code (USE is no longer allowed in a stored procedure).
include/sql_state.h:
  New error state (USE is no longer allowed in a stored procedure).
mysql-test/r/sp-error.result:
  Updated result for test of USE in SP (not allowed now).
mysql-test/r/sp-security.result:
  Updated test results for new db column and qualified procedured names.
mysql-test/r/sp.result:
  Updated results for USE in SP (as it's no longer allowed), and
  for new db column in status result.
mysql-test/t/sp-error.test:
  Moved test of USE in SP from sp.test (as it's no longer allowed).
mysql-test/t/sp-security.test:
  Ajusted tests for new db column and qualified procedured names.
mysql-test/t/sp.test:
  Moved test of USE in SP to sp-error.test (as it's no longer allowed).
  Adjusted tests for new db column in status result.
sql/mysql_priv.h:
  mysql_change_db() now has optional arguments for use by SP with qualified names.
sql/share/czech/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/danish/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/dutch/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/english/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/estonian/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/french/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/german/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/greek/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/hungarian/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/italian/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/japanese/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/korean/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/norwegian-ny/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/norwegian/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/polish/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/portuguese/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/romanian/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/russian/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/serbian/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/slovak/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/spanish/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/swedish/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/share/ukrainian/errmsg.txt:
  New error message: USE is not allowed in a stored procedure.
sql/sp.cc:
  SPs are now "belong" to a DB and may have qualified names.
  New functions for changing DB ("use") when parsing and invoking SPs.
sql/sp.h:
  New functions for changing DB ("use") when parsing and invoking SPs.
sql/sp_cache.cc:
  Use the qualified name in the SP cache.
sql/sp_head.cc:
  New function for allocating a qualified SP name (used in sql_yacc.yy).
  Change DB when executing an SP (if needed).
  Moved thd_mem_root swap functions from sp_head.h.
sql/sp_head.h:
  New function for allocating a qualified SP name (used in sql_yacc.yy).
  Moved thd_mem_root swap functions to sp_head.cc.
sql/sql_db.cc:
  mysql_change_db() now has optional arguments for use by SP with qualified names
  (for use when reading an SP from database and executing it); also allow "unusing"
  a database, i.e. setting thd->thd to "".
sql/sql_yacc.yy:
  Initialize qualfied SP names correctly.
  USE is no longer allowed in an SP.

3 files changed, 39 insertions, 46 deletions

diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
index f965cf90eb4..1877789e2b0 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -251,6 +251,10 @@ declare c cursor for select * from t1;
 declare c cursor for select field from t1;
 end|
 ERROR 42000: Duplicate cursor: c
+create procedure u()
+use sptmp;
+#|
+ERROR 42000: USE is not allowed in a stored procedure
 create procedure bug1965()
 begin
 declare c cursor for select val from t1 order by valname;
diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result
index c4fbece9d72..51439e08782 100644
--- a/mysql-test/r/sp-security.result
+++ b/mysql-test/r/sp-security.result
@@ -8,16 +8,16 @@ create table t1 ( u varchar(64), i int );
 create procedure stamp(i int)
 insert into db1_secret.t1 values (user(), i);
 show procedure status like 'stamp';
-Name	Type	Definer	Modified	Created	Security_type	Comment
-stamp	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+Db	Name	Type	Definer	Modified	Created	Security_type	Comment
+db1_secret	stamp	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
 call stamp(1);
 select * from t1;
 u	i
 root@localhost	1
-call stamp(2);
+call db1_secret.stamp(2);
 select * from db1_secret.t1;
 ERROR 42000: Access denied for user: 'user1'@'localhost' to database 'db1_secret'
-call stamp(3);
+call db1_secret.stamp(3);
 select * from db1_secret.t1;
 ERROR 42000: Access denied for user: ''@'localhost' to database 'db1_secret'
 select * from t1;
@@ -27,8 +27,8 @@ user1@localhost	2
 anon@localhost	3
 alter procedure stamp sql security invoker;
 show procedure status like 'stamp';
-Name	Type	Definer	Modified	Created	Security_type	Comment
-stamp	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	INVOKER	
+Db	Name	Type	Definer	Modified	Created	Security_type	Comment
+db1_secret	stamp	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	INVOKER	
 call stamp(4);
 select * from t1;
 u	i
@@ -36,9 +36,9 @@ root@localhost	1
 user1@localhost	2
 anon@localhost	3
 root@localhost	4
-call stamp(5);
+call db1_secret.stamp(5);
 ERROR 42000: Access denied for user: 'user1'@'localhost' to database 'db1_secret'
-call stamp(6);
+call db1_secret.stamp(6);
 ERROR 42000: Access denied for user: ''@'localhost' to database 'db1_secret'
 drop database if exists db2;
 create database db2;
@@ -73,9 +73,9 @@ s1
 0
 2
 2
-drop procedure stamp;
-drop procedure p;
-drop procedure q;
+drop procedure db1_secret.stamp;
+drop procedure db2.p;
+drop procedure db2.q;
 use test;
 drop database db1_secret;
 drop database db2;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index bf515e4b4cd..0a934817d85 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -18,17 +18,6 @@ id	data
 foo	42
 delete from t1;
 drop procedure foo42;
-create procedure u()
-use sptmp;
-drop database if exists sptmp;
-create database sptmp;
-use test;
-call u();
-select database();
-database()
-test
-drop database sptmp;
-drop procedure u;
 create procedure bar(x char(16), y int)
 insert into test.t1 values (x, y);
 call bar("bar", 666);
@@ -746,7 +735,7 @@ delete from t1|
 alter procedure chistics sql security invoker name chistics2|
 show create procedure chistics2|
 Procedure	Create Procedure
-chistics2	CREATE PROCEDURE `chistics2`()
+chistics2	CREATE PROCEDURE `test`.`chistics2`()
     SQL SECURITY INVOKER
     COMMENT 'Characteristics procedure test'
 insert into t1 values ("chistics", 1)
@@ -763,7 +752,7 @@ chistics()
 alter function chistics name chistics2 comment 'Characteristics function test'|
 show create function chistics2|
 Function	Create Function
-chistics2	CREATE FUNCTION `chistics2`() RETURNS int
+chistics2	CREATE FUNCTION `test`.`chistics2`() RETURNS int
     DETERMINISTIC
     SQL SECURITY INVOKER
     COMMENT 'Characteristics function test'
@@ -939,23 +928,23 @@ begin
 show create function fac;
 end|
 call bug2267_1()|
-Name	Type	Definer	Modified	Created	Security_type	Comment
-bug2267_1	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
-bug2267_2	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
-bug2267_3	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
-bug2267_4	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+Db	Name	Type	Definer	Modified	Created	Security_type	Comment
+test	bug2267_1	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+test	bug2267_2	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+test	bug2267_3	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+test	bug2267_4	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
 call bug2267_2()|
-Name	Type	Definer	Modified	Created	Security_type	Comment
-fac	FUNCTION	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+Db	Name	Type	Definer	Modified	Created	Security_type	Comment
+test	fac	FUNCTION	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
 call bug2267_3()|
 Procedure	Create Procedure
-bug2267_1	CREATE PROCEDURE `bug2267_1`()
+bug2267_1	CREATE PROCEDURE `test`.`bug2267_1`()
 begin
 show procedure status;
 end
 call bug2267_4()|
 Function	Create Function
-fac	CREATE FUNCTION `fac`(n int unsigned) RETURNS bigint unsigned
+fac	CREATE FUNCTION `test`.`fac`(n int unsigned) RETURNS bigint unsigned
 begin
 declare f bigint unsigned default 1;
 while n > 1 do
@@ -1029,12 +1018,12 @@ n	f
 20	2432902008176640000
 drop table fac|
 show function status like '%f%'|
-Name	Type	Definer	Modified	Created	Security_type	Comment
-fac	FUNCTION	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+Db	Name	Type	Definer	Modified	Created	Security_type	Comment
+test	fac	FUNCTION	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
 drop procedure ifac|
 drop function fac|
 show function status like '%f%'|
-Name	Type	Definer	Modified	Created	Security_type	Comment
+Db	Name	Type	Definer	Modified	Created	Security_type	Comment
 drop table if exists primes|
 create table primes (
 i int unsigned not null primary key,
@@ -1095,7 +1084,7 @@ end while;
 end|
 show create procedure opp|
 Procedure	Create Procedure
-opp	CREATE PROCEDURE `opp`(n bigint unsigned, out pp bool)
+opp	CREATE PROCEDURE `test`.`opp`(n bigint unsigned, out pp bool)
 begin
 declare r double;
 declare b, s bigint unsigned default 0;
@@ -1122,9 +1111,9 @@ end if;
 end loop;
 end
 show procedure status like '%p%'|
-Name	Type	Definer	Modified	Created	Security_type	Comment
-ip	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
-opp	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+Db	Name	Type	Definer	Modified	Created	Security_type	Comment
+test	ip	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
+test	opp	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
 call ip(200)|
 select * from primes where i=45 or i=100 or i=199|
 i	p
@@ -1135,7 +1124,7 @@ drop table primes|
 drop procedure opp|
 drop procedure ip|
 show procedure status like '%p%'|
-Name	Type	Definer	Modified	Created	Security_type	Comment
+Db	Name	Type	Definer	Modified	Created	Security_type	Comment
 drop table if exists fib|
 create table fib ( f bigint unsigned not null )|
 insert into fib values (1), (1)|
@@ -1185,19 +1174,19 @@ create procedure bar(x char(16), y int)
 comment "111111111111" sql security invoker
 insert into test.t1 values (x, y)|
 show procedure status like 'bar'|
-Name	Type	Definer	Modified	Created	Security_type	Comment
-bar	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	INVOKER	111111111111
+Db	Name	Type	Definer	Modified	Created	Security_type	Comment
+test	bar	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	INVOKER	111111111111
 alter procedure bar name bar2 comment "2222222222" sql security definer|
 alter procedure bar2 name bar comment "3333333333"|
 alter procedure bar|
 show create procedure bar|
 Procedure	Create Procedure
-bar	CREATE PROCEDURE `bar`(x char(16), y int)
+bar	CREATE PROCEDURE `test`.`bar`(x char(16), y int)
     COMMENT '3333333333'
 insert into test.t1 values (x, y)
 show procedure status like 'bar'|
-Name	Type	Definer	Modified	Created	Security_type	Comment
-bar	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	3333333333
+Db	Name	Type	Definer	Modified	Created	Security_type	Comment
+test	bar	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	3333333333
 drop procedure bar|
 drop table t1;
 drop table t2;