diff options
Diffstat (limited to 'mysql-test/t/sp.test')
| -rw-r--r-- | mysql-test/t/sp.test | 586 |
1 files changed, 485 insertions, 101 deletions
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 21ca2528e4f..c1e8d4b6f6c 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -11,6 +11,7 @@ # Tests that uses 'goto' to into sp-goto.test (currently disabled) # Tests that destroys system tables (e.g. mysql.proc) for error testing # go to sp-destruct. +# Tests that require --with-geometry go into sp_gis.test # Tests that require multibyte character sets, which are not always available, # go into separate files (e.g. sp-ucs2.test) @@ -146,7 +147,7 @@ begin end| call setcontext()| -select * from t1| +select * from t1 order by data| delete from t1| drop procedure setcontext| @@ -290,7 +291,7 @@ create procedure inc(inout io int) set io = io + 1| call iotest("io1", "io2", 1)| -select * from t1| +select * from t1 order by data desc| delete from t1| drop procedure iotest| drop procedure inc2| @@ -336,7 +337,7 @@ begin end| call cbv1()| -select * from t1| +select * from t1 order by data| delete from t1| drop procedure cbv1| drop procedure cbv2| @@ -365,7 +366,7 @@ end| --disable_warnings drop procedure if exists sub3| --enable_warnings -create function sub3(i int) returns int +create function sub3(i int) returns int deterministic return i+1| call sub1("sub1a", (select 7))| @@ -374,7 +375,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| +select * from t1 order by id| select sub3((select max(i) from t2))| drop procedure sub1| drop procedure sub2| @@ -395,7 +396,7 @@ while x do end while| call a0(3)| -select * from t1| +select * from t1 order by data desc| delete from t1| drop procedure a0| @@ -411,7 +412,7 @@ while x > 0 do end while| call a(3)| -select * from t1| +select * from t1 order by data desc| delete from t1| drop procedure a| @@ -427,7 +428,7 @@ repeat until x = 0 end repeat| call b(3)| -select * from t1| +select * from t1 order by data desc| delete from t1| drop procedure b| @@ -459,7 +460,7 @@ hmm: while x > 0 do end while hmm| call c(3)| -select * from t1| +select * from t1 order by data desc| delete from t1| drop procedure c| @@ -496,7 +497,7 @@ foo: loop end loop foo| call e(3)| -select * from t1| +select * from t1 order by data desc| delete from t1| drop procedure e| @@ -517,7 +518,7 @@ end if| call f(-2)| call f(0)| call f(4)| -select * from t1| +select * from t1 order by data| delete from t1| drop procedure f| @@ -539,7 +540,7 @@ end case| call g(-42)| call g(0)| call g(1)| -select * from t1| +select * from t1 order by data| delete from t1| drop procedure g| @@ -561,7 +562,7 @@ end case| call h(0)| call h(1)| call h(17)| -select * from t1| +select * from t1 order by data| delete from t1| drop procedure h| @@ -595,7 +596,7 @@ drop procedure if exists sel1| --enable_warnings create procedure sel1() begin - select * from t1; + select * from t1 order by data; end| call sel1()| @@ -606,8 +607,8 @@ drop procedure if exists sel2| --enable_warnings create procedure sel2() begin - select * from t1; - select * from t2; + select * from t1 order by data; + select * from t2 order by s; end| call sel2()| @@ -627,7 +628,7 @@ begin end| call into_test("into", 100)| -select * from t1| +select * from t1 order by data| delete from t1| drop procedure into_test| @@ -644,7 +645,7 @@ begin end| call into_test2("into", 100)| -select id,data,@z from t1| +select id,data,@z from t1 order by data| delete from t1| drop procedure into_test2| @@ -826,9 +827,9 @@ insert into t2 values (append("a", "b"), mul(2,mul(3,4)), fun(1.7, 4, 6))| # Disable PS because double's give a bit different values --disable_ps_protocol select * from t2 where s = append("a", "b")| -select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2)| +select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2) order by i| select * from t2 where d = e()| -select * from t2| +select * from t2 order by i| --enable_ps_protocol delete from t2| @@ -983,8 +984,8 @@ drop procedure if exists cur2| create procedure cur2() begin declare done int default 0; - declare c1 cursor for select id,data from test.t1; - declare c2 cursor for select i from test.t2; + declare c1 cursor for select id,data from test.t1 order by id,data; + declare c2 cursor for select i from test.t2 order by i; declare continue handler for sqlstate '02000' set done = 1; open c1; @@ -1010,7 +1011,7 @@ begin end| call cur2()| -select * from t3| +select * from t3 order by i,s| delete from t1| delete from t2| drop table t3| @@ -1185,13 +1186,13 @@ create function f1() returns int return (select sum(data) from t1)| select f1()| # This should work too (and give 2 rows as result) -select id, f1() from t1| +select id, f1() from t1 order by id| # Function which uses two instances of table simultaneously create function f2() returns int - return (select data from t1 where data <= (select sum(data) from t1) limit 1)| + return (select data from t1 where data <= (select sum(data) from t1) order by data limit 1)| select f2()| -select id, f2() from t1| +select id, f2() from t1 order by id| # Function which uses the same table twice in different queries create function f3() returns int @@ -1203,17 +1204,17 @@ begin return n < m; end| select f3()| -select id, f3() from t1| +select id, f3() from t1 order by id| # Calling two functions using same table select f1(), f3()| -select id, f1(), f3() from t1| +select id, f1(), f3() from t1 order by id| # Function which uses two different tables create function f4() returns double return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")| select f4()| -select s, f4() from t2| +select s, f4() from t2 order by s| # Recursive functions which due to this recursion require simultaneous # access to several instance of the same table won't work @@ -1246,7 +1247,7 @@ end| create function f7() returns int return (select sum(data) from t1 where data <= f1())| select f6()| -select id, f6() from t1| +select id, f6() from t1 order by id| # # Let us test how new locking work with views @@ -1254,12 +1255,12 @@ select id, f6() from t1| # The most trivial view create view v1 (a) as select f1()| select * from v1| -select id, a from t1, v1| +select id, a from t1, v1 order by id| select * from v1, v1 as v| # A bit more complex construction create view v2 (a) as select a*10 from v1| select * from v2| -select id, a from t1, v2| +select id, a from t1, v2 order by id| select * from v1, v2| # Nice example where the same view is used on @@ -1304,7 +1305,7 @@ select *, f0() from v0| lock tables t1 read, t1 as t11 read| # These should work well select f3()| -select id, f3() from t1 as t11| +select id, f3() from t1 as t11 order by id| # Degenerate cases work too :) select f0()| select * from v0| @@ -1412,8 +1413,8 @@ drop function f12_2| drop view v0| drop view v1| drop view v2| -delete from t1 | -delete from t2 | +truncate table t1 | +truncate table t2 | drop table t4| # End of non-bug tests @@ -1451,11 +1452,11 @@ end| call ifac(20)| select * from t3| drop table t3| ---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +--replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' show function status like '%f%'| drop procedure ifac| drop function fac| ---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +--replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' show function status like '%f%'| @@ -1601,7 +1602,7 @@ call fib(3)| select * from t3 order by f asc| -delete from t3| +truncate table t3| # The original test, 20 levels, ran into memory limits on some machines # and builds. Try 10 instead... @@ -1698,7 +1699,6 @@ begin end if; return x; end| - select * from t1 where data = getcount("bar")| select * from t3| select getcount("zip")| @@ -2125,7 +2125,7 @@ delete from t1| call bug822('foo', 42)| call bug822('foo', 42)| call bug822('bar', 666)| -select * from t1| +select * from t1 order by data| delete from t1| drop procedure bug822| @@ -2153,7 +2153,7 @@ delete from t1 where id='foo'| insert into t1 values ('bar', 7)| call bug1495()| delete from t1 where id='bar'| -select * from t1| +select * from t1 order by data| delete from t1| drop procedure bug1495| @@ -2178,7 +2178,7 @@ end| insert into t1 values ("foo", 12), ("bar", 7)| call bug1547("foo")| call bug1547("bar")| -select * from t1| +select * from t1 order by id| delete from t1| drop procedure bug1547| @@ -2245,7 +2245,7 @@ end| insert into t1 (data) values (3), (1), (5), (9), (4)| call bug1874()| -select * from t2| +select * from t2 order by i| delete from t1| delete from t2| drop procedure bug1874| @@ -2594,6 +2594,9 @@ begin end if; end| +# so that from_unixtime() has a deterministic result +set time_zone='+03:00'; + call bug3426(1000, @i)| select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| call bug3426(NULL, @i)| @@ -2942,13 +2945,15 @@ begin show columns from t1; show keys from t1; show open tables like 'foo'; - show privileges; + # Removed because result will differ in embedded mode. + #show privileges; show status like 'foo'; show tables like 'foo'; show variables like 'foo'; show warnings; end| --disable_parsing +--replace_regex /table_id: [0-9]+/table_id: #/ show binlog events| show storage engines| show master status| @@ -4386,12 +4391,23 @@ drop table t3| --disable_warnings drop function if exists bug9048| --enable_warnings -create function bug9048(f1 char binary) returns char binary +create function bug9048(f1 char binary) returns char begin set f1= concat( 'hello', f1 ); return f1; end| drop function bug9048| +# +# This was disabled in 5.1.12. See bug #20701 +# When collation support in SP is implemented, then this test should +# be removed. +# +--error ER_NOT_SUPPORTED_YET +create function bug9048(f1 char binary) returns char binary +begin + set f1= concat( 'hello', f1 ); + return f1; +end| # Bug #12849 Stored Procedure: Crash on procedure call with CHAR type # 'INOUT' parameter @@ -4501,14 +4517,18 @@ begin select bug12379(); end| ---error 1062 +--error ER_DUP_ENTRY select bug12379()| select 1| +# statement-based binlogging will show warning which row-based won't; +# so we hide it (this warning is already tested in rpl_stm_sp.test) +--disable_warnings call bug12379_1()| select 2| call bug12379_2()| +--enable_warnings select 3| ---error 1062 +--error ER_DUP_ENTRY call bug12379_3()| select 4| @@ -4941,7 +4961,7 @@ end| # a procedure which use tables and recursion create table t3 (a int)| insert into t3 values (0)| -create view v1 as select a from t3; +create view v1 as select a from t3| create procedure bug10100pt(level int, lim int) begin if level < lim then @@ -4964,7 +4984,7 @@ begin end if; end| # dynamic sql & recursion -prepare stmt2 from "select * from t3;"; +prepare stmt2 from "select * from t3;"| create procedure bug10100pd(level int, lim int) begin if level < lim then @@ -5237,7 +5257,7 @@ drop procedure bug5967| # --disable_warnings drop procedure if exists bug13012| ---enable_warnings +# Disable warnings also for BACKUP/RESTORE: they are deprecated. create procedure bug13012() BEGIN REPAIR TABLE t1; @@ -5246,6 +5266,7 @@ BEGIN RESTORE TABLE t1 FROM '../tmp'; END| call bug13012()| +--enable_warnings drop procedure bug13012| create view v1 as select * from t1| create procedure bug13012() @@ -5258,8 +5279,8 @@ call bug13012()| call bug13012()| call bug13012()| drop procedure bug13012| -drop view v1; -select * from t1| +drop view v1| +select * from t1 order by data| # # A test case for Bug#15392 "Server crashes during prepared statement @@ -5613,26 +5634,6 @@ drop table t3| drop procedure bug16887| # -# Bug#13575 SP funcs in select with distinct/group and order by can -# produce bad data -# -# Disable warnings to allow test to run also without InnoDB ---disable_warnings -create table t3 (f1 int, f2 varchar(3), primary key(f1)) engine=innodb| ---enable_warnings -insert into t3 values (1,'aaa'),(2,'bbb'),(3,'ccc')| -CREATE FUNCTION bug13575 ( p1 integer ) -returns varchar(3) -BEGIN -DECLARE v1 VARCHAR(10) DEFAULT null; -SELECT f2 INTO v1 FROM t3 WHERE f1 = p1; -RETURN v1; -END| -select distinct f1, bug13575(f1) from t3 order by f1| -drop function bug13575; -drop table t3| - -# # BUG#16474: SP crashed MySQL # (when using "order by localvar", where 'localvar' is just that. # @@ -5648,7 +5649,7 @@ create procedure bug16474_1() begin declare x int; - select id from t1 order by x; + select id from t1 order by x, id; end| # @@ -5668,7 +5669,7 @@ drop procedure bug14945| # This does NOT order by column index; variable is an expression. create procedure bug16474_2(x int) - select id from t1 order by x| + select id from t1 order by x, id| call bug16474_1()| call bug16474_2(1)| @@ -5678,7 +5679,7 @@ drop procedure bug16474_2| # For reference: user variables are expressions too and do not affect ordering. set @x = 2| -select * from t1 order by @x| +select * from t1 order by @x, data| delete from t1| @@ -6247,6 +6248,8 @@ SELECT bug19862(a) FROM t12 ORDER BY 1| SELECT * FROM t11| DROP TABLE t11, t12| DROP FUNCTION bug19862| + + # Bug#21002 "Derived table not selecting from a "real" table fails in JOINs" # # A regression caused by the fix for Bug#18444: for derived tables we should @@ -6272,10 +6275,8 @@ use test| drop table t3| -# # Test for BUG#16899: Possible buffer overflow in handling of DEFINER-clause. # - # Prepare. --disable_warnings @@ -6336,7 +6337,7 @@ set names utf8| drop database if exists това_е_дълго_име_за_база_данни_нали| --enable_warnings create database това_е_дълго_име_за_база_данни_нали| -INSERT INTO mysql.proc VALUES ('това_е_дълго_име_за_база_данни_нали','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','PROCEDURE','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','SQL','CONTAINS_SQL','NO','DEFINER','','','bad_body','root@localhost',now(), now(),'','')| +INSERT INTO mysql.proc VALUES ('това_е_дълго_име_за_база_данни_нали','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','PROCEDURE','това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго','SQL','CONTAINS_SQL','NO','DEFINER','','','bad_body','root@localhost',now(), now(),'','', 'utf8', 'utf8_general_ci', 'utf8_general_ci', 'n/a')| --error ER_SP_PROC_TABLE_CORRUPT call това_е_дълго_име_за_база_данни_нали.това_е_процедура_с_доста_дълго_име_нали_и_още_по_дълго()| drop database това_е_дълго_име_за_база_данни_нали| @@ -6641,6 +6642,124 @@ DROP PROCEDURE p1| DROP VIEW v1, v2| DROP TABLE t3, t4| +--echo End of 5.0 tests + +--echo Begin of 5.1 tests + +# +# BUG#18239: Possible to overload internal functions with stored functions +# + +delimiter ;| + +--disable_warnings +drop function if exists pi; +--enable_warnings + +create function pi() returns varchar(50) +return "pie, my favorite desert."; + +SET @save_sql_mode=@@sql_mode; + +SET SQL_MODE='IGNORE_SPACE'; + +select pi(), pi (); + +# Non deterministic warnings from db_load_routine +--disable_warnings +select test.pi(), test.pi (); +--enable_warnings + +SET SQL_MODE=''; + +select pi(), pi (); + +# Non deterministic warnings from db_load_routine +--disable_warnings +select test.pi(), test.pi (); +--enable_warnings + +SET @@sql_mode=@save_sql_mode; + +drop function pi; +# End of BUG#18239 + +# +# BUG#22619: Spaces considered harmful +# + +--disable_warnings +drop function if exists test.database; +drop function if exists test.current_user; +drop function if exists test.md5; +--enable_warnings + +create database nowhere; +use nowhere; +drop database nowhere; + +SET @save_sql_mode=@@sql_mode; + +SET SQL_MODE='IGNORE_SPACE'; + +select database(), database (); +select current_user(), current_user (); +select md5("aaa"), md5 ("aaa"); + +SET SQL_MODE=''; + +select database(), database (); +select current_user(), current_user (); +select md5("aaa"), md5 ("aaa"); + +use test; + +create function `database`() returns varchar(50) +return "Stored function database"; + +create function `current_user`() returns varchar(50) +return "Stored function current_user"; + +create function md5(x varchar(50)) returns varchar(50) +return "Stored function md5"; + +SET SQL_MODE='IGNORE_SPACE'; + +select database(), database (); +select current_user(), current_user (); +select md5("aaa"), md5 ("aaa"); + +# Non deterministic warnings from db_load_routine +--disable_warnings +select test.database(), test.database (); +select test.current_user(), test.current_user (); +select test.md5("aaa"), test.md5 ("aaa"); +--enable_warnings + +SET SQL_MODE=''; + +select database(), database (); +select current_user(), current_user (); +select md5("aaa"), md5 ("aaa"); + +# Non deterministic warnings from db_load_routine +--disable_warnings +select test.database(), test.database (); +select test.current_user(), test.current_user (); +select test.md5("aaa"), test.md5 ("aaa"); +--enable_warnings + +SET @@sql_mode=@save_sql_mode; + +drop function test.database; +drop function test.current_user; +drop function md5; + +use test; +delimiter |; +# End of BUG#22619 + +--echo End of 5.1 tests # # BUG#23760: ROW_COUNT() and store procedure not owrking together @@ -7073,6 +7192,9 @@ show create procedure proc_21513| drop procedure proc_21513| +### +--echo End of 5.0 tests. + # # BUG#NNNN: New bug synopsis # @@ -7244,6 +7366,106 @@ drop procedure sp_bug29050; drop table t1; # +# Bug #30120 SP with local variables with non-ASCII names crashes server. +# + +SET NAMES latin1; + +DELIMITER |; + +CREATE PROCEDURE p1() +BEGIN + DECLARE INT; + SELECT ; +END| + +DELIMITER ;| + +CALL p1(); + +SET NAMES default; +DROP PROCEDURE p1; + +# +# Bug#25411 (trigger code truncated) +# + +--disable_warnings +drop procedure if exists proc_25411_a; +drop procedure if exists proc_25411_b; +drop procedure if exists proc_25411_c; +--enable_warnings + +delimiter $$; + +create procedure proc_25411_a() +begin + /* real comment */ + select 1; + /*! select 2; */ + select 3; + /*!00000 select 4; */ + /*!99999 select 5; */ +end +$$ + +create procedure proc_25411_b( +/* real comment */ +/*! p1 int, */ +/*!00000 p2 int */ +/*!99999 ,p3 int */ +) +begin + select p1, p2; +end +$$ + +create procedure proc_25411_c() +begin + select 1/*!,2*//*!00000,3*//*!99999,4*/; + select 1/*! ,2*//*!00000 ,3*//*!99999 ,4*/; + select 1/*!,2 *//*!00000,3 *//*!99999,4 */; + select 1/*! ,2 *//*!00000 ,3 *//*!99999 ,4 */; + select 1 /*!,2*/ /*!00000,3*/ /*!99999,4*/ ; +end +$$ + +delimiter ;$$ + +show create procedure proc_25411_a; +call proc_25411_a(); + +show create procedure proc_25411_b; +select name, param_list, body from mysql.proc where name like "%25411%"; +call proc_25411_b(10, 20); + +show create procedure proc_25411_c; +call proc_25411_c(); + +drop procedure proc_25411_a; +drop procedure proc_25411_b; +drop procedure proc_25411_c; + + +# +# Bug#26302 (MySQL server cuts off trailing "*/" from comments in SP/func) +# + +--disable_warnings +drop procedure if exists proc_26302; +--enable_warnings + +create procedure proc_26302() +select 1 /* testing */; + +show create procedure proc_26302; + +select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES +where ROUTINE_NAME = "proc_26302"; + +drop procedure proc_26302; + + # Bug #29338: no optimization for stored functions with a trivial body # always returning constant. # @@ -7274,27 +7496,6 @@ DROP FUNCTION f2; DROP TABLE t1; # -# Bug #30120 SP with local variables with non-ASCII names crashes server. -# - -SET NAMES latin1; - -DELIMITER |; - -CREATE PROCEDURE p1() -BEGIN - DECLARE INT; - SELECT ; -END| - -DELIMITER ;| - -CALL p1(); - -SET NAMES default; -DROP PROCEDURE p1; - -# # Bug#29408 Cannot find view in columns table if the selection contains a function # delimiter |; @@ -7698,7 +7899,9 @@ DROP PROCEDURE db28318_a.t1; DROP PROCEDURE db28318_b.t2; DROP DATABASE db28318_a; DROP DATABASE db28318_b; -USE test; +use test; + +########################################################################### # # Bug#29770 Two handlers are allowed to catch an error in an stored procedure. @@ -7744,7 +7947,7 @@ begin declare count1 int default '0'; declare vb varchar(30); declare last_row int; - + while(num>=1) do set num=num-1; begin @@ -7774,6 +7977,7 @@ call proc_33618(20); drop table t_33618; drop procedure proc_33618; + --echo # --echo # Bug#30787: Stored function ignores user defined alias. --echo # @@ -7793,6 +7997,7 @@ select (select func30787(f1)) as ttt from t1; drop function func30787; drop table t1; +########################################################################### # # Bug#38291 memory corruption and server crash with view/sp/function @@ -7839,3 +8044,182 @@ drop procedure `p2`; --echo # ------------------------------------------------------------------ --echo # -- End of 5.0 tests --echo # ------------------------------------------------------------------ + +########################################################################### + +# +# Bug#20550: Stored function: wrong RETURN type metadata when used in a VIEW. +# + +########################################################################### + +--echo + +--echo # +--echo # Bug#20550. +--echo # + +--echo + +--echo # +--echo # - Prepare. +--echo # + +--echo + +--disable_warnings +DROP VIEW IF EXISTS v1; +DROP VIEW IF EXISTS v2; +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +--enable_warnings + +--echo + +--echo # +--echo # - Create required objects. +--echo # + +--echo + +CREATE FUNCTION f1() RETURNS VARCHAR(65525) RETURN 'Hello'; + +--echo + +CREATE FUNCTION f2() RETURNS TINYINT RETURN 1; + +--echo + +CREATE VIEW v1 AS SELECT f1(); + +--echo + +CREATE VIEW v2 AS SELECT f2(); + +--echo + +--echo # +--echo # - Check. +--echo # + +--echo + +SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'v1'; + +--echo + +SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'v2'; + +--echo + +--echo # +--echo # - Cleanup. +--echo # + +--echo + +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP VIEW v1; +DROP VIEW v2; + +--echo + +########################################################################### + +# +# Bug#24923: Functions with ENUM issues. +# + +########################################################################### + +--echo # +--echo # - Bug#24923: prepare. +--echo # + +--echo + +--disable_warnings +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +--echo + +--echo # +--echo # - Bug#24923: create required objects. +--echo # + +--echo + +delimiter |; + +CREATE FUNCTION f1(p INT) + RETURNS ENUM ('Very_long_enum_element_identifier', + 'Another_very_long_enum_element_identifier') + BEGIN + CASE p + WHEN 1 THEN + RETURN 'Very_long_enum_element_identifier'; + ELSE + RETURN 'Another_very_long_enum_element_identifier'; + END CASE; + END| + +delimiter ;| + +--echo + +--echo # +--echo # - Bug#24923: check. +--echo # + +--echo + +SELECT f1(1); + +--echo + +SELECT f1(2); + +--echo + +SHOW CREATE FUNCTION f1; + +--echo # +--echo # - Bug#24923: cleanup. +--echo # + +--echo + +DROP FUNCTION f1; + +--echo + +########################################################################### + +# +# Bug#32633 Can not create any routine if SQL_MODE=no_engine_substitution +# +# Ensure that when new SQL modes are introduced, they are also added to +# the mysql.proc table. +# + +--disable_warnings +drop procedure if exists p; +--enable_warnings +set @old_mode= @@sql_mode; +set @@sql_mode= pow(2,32)-1; +select @@sql_mode into @full_mode; +create procedure p() begin end; +call p(); +select @@sql_mode; +set @@sql_mode= @old_mode; +# Rename SQL modes that differ in name between the server and the table definition. +select replace(@full_mode, '?', 'NOT_USED') into @full_mode; +select replace(@full_mode, 'ALLOW_INVALID_DATES', 'INVALID_DATES') into @full_mode; +select name from mysql.proc where name = 'p' and sql_mode = @full_mode; +drop procedure p; + +--echo # ------------------------------------------------------------------ +--echo # -- End of 5.1 tests +--echo # ------------------------------------------------------------------ |