diff options
Diffstat (limited to 'mysql-test/r/sp.result')
| -rw-r--r-- | mysql-test/r/sp.result | 260 |
1 files changed, 208 insertions, 52 deletions
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 0af6b821ce0..af58b551a17 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -237,6 +237,13 @@ insert into t2 values ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3)| drop procedure if exists sub1| create procedure sub1(id char(16), x int) insert into test.t1 values (id, x)| +drop procedure if exists sub2| +create procedure sub2(id char(16)) +begin +declare x int; +set x = (select sum(t.i) from test.t2 t); +insert into test.t1 values (id, x); +end| drop procedure if exists sub3| create function sub3(i int) returns int return i+1| @@ -244,16 +251,19 @@ call sub1("sub1a", (select 7))| call sub1("sub1b", (select max(i) from t2))| call sub1("sub1c", (select i,d from t2 limit 1))| call sub1("sub1d", (select 1 from (select 1) a))| +call sub2("sub2"); select * from t1| id data sub1a 7 sub1b 3 sub1c 1 sub1d 1 +sub2 6 select sub3((select max(i) from t2))| sub3((select max(i) from t2)) 4 drop procedure sub1| +drop procedure sub2| drop function sub3| delete from t2| drop procedure if exists a0| @@ -269,6 +279,7 @@ sub1a 7 sub1b 3 sub1c 1 sub1d 1 +sub2 6 a0 2 a0 1 a0 0 @@ -1046,6 +1057,200 @@ select row_count()| row_count() -1 drop procedure rc| +drop function if exists f0| +drop function if exists f1| +drop function if exists f2| +drop function if exists f3| +drop function if exists f4| +drop function if exists f5| +drop function if exists f6| +drop function if exists f7| +drop function if exists f8| +drop view if exists v0| +drop view if exists v1| +drop view if exists v2| +delete from t1| +delete from t2| +insert into t1 values ("a", 1), ("b", 2) | +insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) | +create function f1() returns int +return (select sum(data) from t1)| +select f1()| +f1() +3 +select id, f1() from t1| +id f1() +a 3 +b 3 +create function f2() returns int +return (select data from t1 where data <= (select sum(data) from t1) limit 1)| +select f2()| +f2() +1 +select id, f2() from t1| +id f2() +a 1 +b 1 +create function f3() returns int +begin +declare n int; +declare m int; +set n:= (select min(data) from t1); +set m:= (select max(data) from t1); +return n < m; +end| +select f3()| +f3() +1 +select id, f3() from t1| +id f3() +a 1 +b 1 +select f1(), f3()| +f1() f3() +3 1 +select id, f1(), f3() from t1| +id f1() f3() +a 3 1 +b 3 1 +create function f4() returns double +return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")| +select f4()| +f4() +2 +select s, f4() from t2| +s f4() +a 2 +b 2 +c 2 +create function f5(i int) returns int +begin +if i <= 0 then +return 0; +elseif i = 1 then +return (select count(*) from t1 where data = i); +else +return (select count(*) + f5( i - 1) from t1 where data = i); +end if; +end| +select f5(1)| +f5(1) +1 +select f5(2)| +ERROR HY000: Table 't1' was not locked with LOCK TABLES +create function f6() returns int +begin +declare n int; +set n:= f1(); +return (select count(*) from t1 where data <= f7() and data <= n); +end| +create function f7() returns int +return (select sum(data) from t1 where data <= f1())| +select f6()| +f6() +2 +select id, f6() from t1| +id f6() +a 2 +b 2 +create view v1 (a) as select f1()| +select * from v1| +a +3 +select id, a from t1, v1| +id a +a 3 +b 3 +select * from v1, v1 as v| +a a +3 3 +create view v2 (a) as select a*10 from v1| +select * from v2| +a +30 +select id, a from t1, v2| +id a +a 30 +b 30 +select * from v1, v2| +a a +3 30 +create function f8 () returns int +return (select count(*) from v2)| +select *, f8() from v1| +a f8() +3 1 +drop function f1| +select * from v1| +ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) +create function f1() returns int +return (select sum(data) from t1) + (select sum(data) from v1)| +drop function f1| +create function f1() returns int +return (select sum(data) from t1)| +create function f0() returns int +return (select * from (select 100) as r)| +select f0()| +f0() +100 +select *, f0() from (select 1) as t| +1 f0() +1 100 +create view v0 as select f0()| +select * from v0| +f0() +100 +select *, f0() from v0| +f0() f0() +100 100 +lock tables t1 read, t1 as t11 read, mysql.proc read| +select f3()| +f3() +1 +select id, f3() from t1 as t11| +id f3() +a 1 +b 1 +select f0()| +f0() +100 +select * from v0| +f0() +100 +select *, f0() from v0, (select 123) as d1| +f0() 123 f0() +100 123 100 +select id, f3() from t1| +ERROR HY000: Table 't1' was not locked with LOCK TABLES +select f4()| +ERROR HY000: Table 't2' was not locked with LOCK TABLES +unlock tables| +lock tables v2 read, mysql.proc read| +select * from v2| +a +30 +select * from v1| +a +3 +select * from v1, v2| +ERROR HY000: Table 't1' was not locked with LOCK TABLES +select f4()| +ERROR HY000: Table 't2' was not locked with LOCK TABLES +unlock tables| +drop function f0| +drop function f1| +drop function f2| +drop function f3| +drop function f4| +drop function f5| +drop function f6| +drop function f7| +drop function f8| +drop view v0| +drop view v1| +drop view v2| +delete from t1 | +delete from t2 | drop procedure if exists bug822| create procedure bug822(a_id char(16), a_data int) begin @@ -1178,56 +1383,6 @@ select @x2| @x2 2 drop procedure bug2260| -drop procedure if exists bug2267_1| -create procedure bug2267_1() -begin -show procedure status; -end| -drop procedure if exists bug2267_2| -create procedure bug2267_2() -begin -show function status; -end| -drop procedure if exists bug2267_3| -create procedure bug2267_3() -begin -show create procedure bug2267_1; -end| -drop procedure if exists bug2267_4| -create procedure bug2267_4() -begin -show create function fac; -end| -call bug2267_1()| -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()| -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 sql_mode Create Procedure -bug2267_1 CREATE PROCEDURE `test`.`bug2267_1`() -begin -show procedure status; -end -call bug2267_4()| -Function sql_mode Create Function -fac CREATE FUNCTION `test`.`fac`(n int unsigned) RETURNS bigint unsigned -begin -declare f bigint unsigned default 1; -while n > 1 do -set f = f * n; -set n = n - 1; -end while; -return f; -end -drop procedure bug2267_1| -drop procedure bug2267_2| -drop procedure bug2267_3| -drop procedure bug2267_4| drop procedure if exists bug2227| create procedure bug2227(x int) begin @@ -1332,7 +1487,7 @@ declare t2 int; declare t3 int; declare rc int default 0; declare continue handler for 1065 set rc = 1; -drop table if exists temp_t1; +drop temporary table if exists temp_t1; create temporary table temp_t1 ( f1 int auto_increment, f2 varchar(20), primary key (f1) ); @@ -1352,6 +1507,7 @@ f1 rc t3 2 0 NULL 2 0 NULL drop procedure bug1863| +drop temporary table temp_t1; drop table t3, t4| drop table if exists t3, t4| create table t3 ( @@ -2366,7 +2522,7 @@ delete from t1| insert into t1 values ("answer", 42)| select id, bug5240() from t1| id bug5240() -42 42 +answer 42 drop function bug5240| drop function if exists bug5278| create function bug5278 () returns char |