WL#2130: Table locking for stored FUNCTIONs

Collect all tables and SPs refered by a statement, and open all tables
with an implicit LOCK TABLES. Do find things refered by triggers and views,
we open them first (and then repeat this until nothing new is found), before
doing the actual lock tables.


mysql-test/r/information_schema.result:
  Updated result for WL#2130.
mysql-test/r/lock.result:
  Updated result for WL#2130.
mysql-test/r/sp-error.result:
  Updated result for WL#2130.
mysql-test/r/sp.result:
  Updated result for WL#2130.
mysql-test/r/view.result:
  Updated result for WL#2130.
mysql-test/t/information_schema.test:
  Disabled one test case due to a bug involving LOCK TABLES,
  which shows up with WL#2130.
mysql-test/t/lock.test:
  New error message with WL#2130. This change is under debate and might change
  in the future, but will do for now.
mysql-test/t/sp-error.test:
  Updated for WL#2130. Some tests are voided when table access does work from
  functions.
mysql-test/t/sp.test:
  Updated for WL#2130.
mysql-test/t/view.test:
  Updated for WL#2130.
sql/item_func.cc:
  We now have to set net.no_send_ok for functions too, with WL#2130.
sql/share/errmsg.txt:
  Reused an error code since the old use was voided by WL#2130, but a new
  one was needed instead (similar, but more specific restriction).
sql/sp.cc:
  Fixed error handling and collection of used tables for WL#2130.
sql/sp.h:
  Fixed error handling and collection of used tables for WL#2130.
sql/sp_head.cc:
  Added support functions for collecting and merging hash tables and lists
  of used tables from SPs and substatements, for WL#2130.
sql/sp_head.h:
  Added support functions for collecting and merging hash tables and lists
  of used tables from SPs and substatements, for WL#2130.
sql/sql_base.cc:
  Changed the way table->query_id is tested and set during with locked tables
  in effect. This makes some SP test cases work with WL#2130, but has a side
  effect on some error cases with explicit LOCK TABLES. It's still debated if
  this is the correct way, so it might change.
sql/sql_class.h:
  Added flags for circumventing some interference between WL#2130 and mysql_make_view().
sql/sql_derived.cc:
  Added some missing initializations. (Potential bugs.)
sql/sql_lex.cc:
  Clear the new hash tables for WL#2130.
sql/sql_lex.h:
  Added hash tables for procedures and tables too (as for functions), for WL#2130.
sql/sql_parse.cc:
  WL#2130: Make table accesses from stored functions work by adding an implicit
  LOCK TABLES around (most) executed statements. To do this, we have to go through
  a loop where we collect all SPs and tables in mysql_execute_statement.
sql/sql_prepare.cc:
  Cache both functions and procedures for WL#2130.
sql/sql_show.cc:
  Added some missing initializations. (Potential bugs.)
sql/sql_view.cc:
  Shortcut mysql_make_view() if thd->shortcut_make_view is true during
  the pre-open phase for collecting tables in WL#2130. Otherwise, the
  similar mechanism here causes interference.
sql/sql_yacc.yy:
  For WL#2130, added caching of procedures and disallowed LOCK/UNLOCK TABLES in SPs.

5 files changed, 117 insertions, 90 deletions

diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index 2532eed8abc..9bf21e9f061 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -380,26 +380,6 @@ delete from mysql.db where user='joe';
 delete from mysql.tables_priv where user='joe';
 delete from mysql.columns_priv where user='joe';
 flush privileges;
-create procedure px5 ()
-begin
-declare v int;
-declare c cursor for select version from
-information_schema.tables where table_schema <> 'information_schema';
-open c;
-fetch c into v;
-select v;
-close c;
-end;//
-call px5()//
-v
-9
-call px5()//
-v
-9
-select sql_mode from information_schema.ROUTINES;
-sql_mode
-
-drop procedure px5;
 create table t1 (a int not null auto_increment,b int, primary key (a));
 insert into t1 values (1,1),(NULL,3),(NULL,4);
 select AUTO_INCREMENT from information_schema.tables where table_name = 't1';
diff --git a/mysql-test/r/lock.result b/mysql-test/r/lock.result
index 429bc5ed352..db2842061b4 100644
--- a/mysql-test/r/lock.result
+++ b/mysql-test/r/lock.result
@@ -41,8 +41,8 @@ lock tables t1 write;
 check table t2;
 Table	Op	Msg_type	Msg_text
 test.t2	check	error	Table 't2' was not locked with LOCK TABLES
-insert into t1 select nr from t1;
-ERROR HY000: Table 't1' was not locked with LOCK TABLES
+insert into t1 select index1,nr from t1;
+ERROR 42000: INSERT command denied to user 'root'@'localhost' for column 'index1' in table 't1'
 unlock tables;
 lock tables t1 write, t1 as t1_alias read;
 insert into t1 select index1,nr from t1 as t1_alias;
diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
index 57126162e3f..1c2f4662ef1 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -120,15 +120,8 @@ ERROR 42000: End-label bar without match
 create procedure foo()
 return 42|
 ERROR 42000: RETURN is only allowed in a FUNCTION
-create function foo() returns int
-begin
-declare x int;
-select max(c) into x from test.t;
-return x;
-end|
-ERROR 0A000: Statements like SELECT, INSERT, UPDATE (and others) are not allowed in a FUNCTION
 create procedure p(x int)
-insert into test.t1 values (x)|
+set @x = x|
 create function f(x int) returns int
 return x+42|
 call p()|
@@ -336,10 +329,6 @@ ERROR 42S22: Unknown column 'valname' in 'order clause'
 drop procedure bug1965|
 select 1 into a|
 ERROR 42000: Undeclared variable: a
-create function bug1654()
-returns int
-return (select sum(t.data) from test.t2 t)|
-ERROR 0A000: Statements like SELECT, INSERT, UPDATE (and others) are not allowed in a FUNCTION
 drop table if exists t3|
 create table t3 (column_1_0 int)|
 create procedure bug1653()
@@ -354,7 +343,7 @@ drop table t3|
 create procedure bug2259()
 begin
 declare v1 int;
-declare c1 cursor for select s1 from t10;
+declare c1 cursor for select s1 from t1;
 fetch c1 into v1;
 end|
 call bug2259()|
@@ -458,7 +447,9 @@ create procedure bug3294()
 begin
 declare continue handler for sqlexception drop table t5;
 drop table t5;
+drop table t5;
 end|
+create table t5 (x int)|
 call bug3294()|
 ERROR 42S02: Unknown table 't5'
 drop procedure bug3294|
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 1fd519bc729..0af6b821ce0 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -693,7 +693,7 @@ drop procedure if exists create_select|
 create procedure create_select(x char(16), y int)
 begin
 insert into test.t1 values (x, y);
-create table test.t3 select * from test.t1;
+create temporary table test.t3 select * from test.t1;
 insert into test.t3 values (concat(x, "2"), y+2);
 end|
 drop table if exists t3|
@@ -775,11 +775,11 @@ drop procedure if exists hndlr1|
 create procedure hndlr1(val int)
 begin
 declare x int default 0;
-declare foo condition for 1146;
+declare foo condition for 1136;
 declare bar condition for sqlstate '42S98';        # Just for testing syntax
 declare zip condition for sqlstate value '42S99';  # Just for testing syntax
 declare continue handler for foo set x = 1;
-insert into test.t666 values ("hndlr1", val);   # Non-existing table
+insert into test.t1 values ("hndlr1", val, 2);  # Too many values
 if (x) then
 insert into test.t1 values ("hndlr1", val);   # This instead then
 end if;
@@ -795,8 +795,8 @@ create procedure hndlr2(val int)
 begin
 declare x int default 0;
 begin
-declare exit handler for sqlstate '42S02' set x = 1;
-insert into test.t666 values ("hndlr2", val); # Non-existing table
+declare exit handler for sqlstate '21S01' set x = 1;
+insert into test.t1 values ("hndlr2", val, 2); # Too many values
 end;
 insert into test.t1 values ("hndlr2", x);
 end|
@@ -820,7 +820,7 @@ if val < 10 then
 begin
 declare y int;
 set y = val + 10;
-insert into test.t666 values ("hndlr3", y);  # Non-existing table
+insert into test.t1 values ("hndlr3", y, 2);  # Too many values
 if x then
 insert into test.t1 values ("hndlr3", y);
 end if;
@@ -1239,18 +1239,6 @@ call bug2227(9)|
 1.3	x	y	42	z
 1.3	9	2.6	42	zzz
 drop procedure bug2227|
-drop procedure if exists bug2614|
-create procedure bug2614()
-begin
-drop table if exists t3;
-create table t3 (id int default '0' not null);
-insert into t3 select 12;
-insert into t3 select * from t3;
-end|
-call bug2614()|
-call bug2614()|
-drop table t3|
-drop procedure bug2614|
 drop function if exists bug2674|
 create function bug2674() returns int
 return @@sort_buffer_size|
@@ -1551,7 +1539,7 @@ drop procedure if exists bug2460_2|
 create procedure bug2460_2()
 begin
 drop table if exists t3;
-create table t3 (s1 int);
+create temporary table t3 (s1 int);
 insert into t3 select 1 union select 1;
 end|
 call bug2460_2()|
@@ -1681,22 +1669,6 @@ call bug4726()|
 call bug4726()|
 drop procedure bug4726|
 drop table t3|
-drop table if exists t3|
-create table t3 (s1 int)|
-insert into t3 values (3), (4)|
-drop procedure if exists bug4318|
-create procedure bug4318()
-handler t3 read next|
-handler t3 open|
-call bug4318()|
-s1
-3
-call bug4318()|
-s1
-4
-handler t3 close|
-drop procedure bug4318|
-drop table t3|
 drop procedure if exists bug4902|
 create procedure bug4902()
 begin
@@ -2302,22 +2274,110 @@ show procedure status like 'bar'|
 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;
-drop procedure if exists p1;
-create procedure p1 () select (select s1 from t1) from t1;
-create table t1 (s1 int);
-call p1();
-(select s1 from t1)
-insert into t1 values (1);
-call p1();
-(select s1 from t1)
+drop procedure if exists p1|
+create procedure p1 ()
+select (select s1 from t3) from t3|
+create table t3 (s1 int)|
+call p1()|
+(select s1 from t3)
+insert into t3 values (1)|
+call p1()|
+(select s1 from t3)
 1
-drop procedure p1;
-drop table t1;
-drop function if exists foo;
-create function `foo` () returns int return 5;
-select `foo` ();
+drop procedure p1|
+drop table t3|
+drop function if exists foo|
+create function `foo` () returns int
+return 5|
+select `foo` ()|
 `foo` ()
 5
-drop function `foo`;
+drop function `foo`|
+drop function if exists t1max|
+Warnings:
+Note	1305	FUNCTION t1max does not exist
+create function t1max() returns int
+begin
+declare x int;
+select max(data) into x from t1;
+return x;
+end|
+insert into t1 values ("foo", 3), ("bar", 2), ("zip", 5), ("zap", 1)|
+select t1max()|
+t1max()
+5
+drop function t1max|
+drop table if exists t3|
+create table t3 (
+v char(16) not null primary key,
+c int unsigned not null
+)|
+create function getcount(s char(16)) returns int
+begin
+declare x int;
+select count(*) into x from t3 where v = s;
+if x = 0 then
+insert into t3 values (s, 1);
+else
+update t3 set c = c+1 where v = s;
+end if;
+return x;
+end|
+select * from t1 where data = getcount("bar")|
+id	data
+zap	1
+select * from t3|
+v	c
+bar	4
+select getcount("zip")|
+getcount("zip")
+0
+select getcount("zip")|
+getcount("zip")
+1
+select * from t3|
+v	c
+bar	4
+zip	2
+select getcount(id) from t1 where data = 3|
+getcount(id)
+0
+select getcount(id) from t1 where data = 5|
+getcount(id)
+1
+select * from t3|
+v	c
+bar	4
+zip	3
+foo	1
+drop table t3|
+drop function getcount|
+drop function if exists bug5240|
+create function bug5240 () returns int
+begin
+declare x int;
+declare c cursor for select data from t1 limit 1;
+open c;
+fetch c into x;
+close c;
+return x;
+end|
+delete from t1|
+insert into t1 values ("answer", 42)|
+select id, bug5240() from t1|
+id	bug5240()
+42	42
+drop function bug5240|
+drop function if exists bug5278|
+create function bug5278 () returns char
+begin
+SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');
+return 'okay';
+end|
+select bug5278()|
+ERROR 42000: Can't find any matching row in the user table
+select bug5278()|
+ERROR 42000: Can't find any matching row in the user table
+drop function bug5278|
+drop table t1;
+drop table t2;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 239849ed8d1..64db0a2509f 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -889,10 +889,6 @@ ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function
 drop view v1;
 create view v1 (a,a) as select 'a','a';
 ERROR 42S21: Duplicate column name 'a'
-create procedure p1 () begin declare v int; create view v1 as select v; end;//
-call p1();
-ERROR HY000: View's SELECT contains a variable or parameter
-drop procedure p1;
 create table t1 (col1 int,col2 char(22));
 insert into t1 values(5,'Hello, world of views');
 create view v1 as select * from t1;