diff options
| -rw-r--r-- | BitKeeper/etc/collapsed | 1 | ||||
| -rw-r--r-- | mysql-test/Makefile.am | 1 | ||||
| -rw-r--r-- | mysql-test/r/ps_1general.result | 2 | ||||
| -rw-r--r-- | mysql-test/t/ps_1general.test | 1 | ||||
| -rw-r--r-- | mysql-test/t/sp.test | 1 | ||||
| -rw-r--r-- | mysql-test/t/sp.test.orig | 5716 | ||||
| -rw-r--r-- | mysys/my_open.c | 4 | ||||
| -rw-r--r-- | sql/udf_example.c | 2 |
8 files changed, 7 insertions, 5721 deletions
diff --git a/BitKeeper/etc/collapsed b/BitKeeper/etc/collapsed new file mode 100644 index 00000000000..8b90deae622 --- /dev/null +++ b/BitKeeper/etc/collapsed @@ -0,0 +1 @@ +44d03f27qNdqJmARzBoP3Is_cN5e0w diff --git a/mysql-test/Makefile.am b/mysql-test/Makefile.am index 852ec463420..7fa6fa1e23e 100644 --- a/mysql-test/Makefile.am +++ b/mysql-test/Makefile.am @@ -99,6 +99,7 @@ install-data-local: $(INSTALL_DATA) $(srcdir)/include/*.inc $(DESTDIR)$(testdir)/include $(INSTALL_DATA) $(srcdir)/std_data/*.dat $(DESTDIR)$(testdir)/std_data $(INSTALL_DATA) $(srcdir)/std_data/*.*001 $(DESTDIR)$(testdir)/std_data + $(INSTALL_DATA) $(srcdir)/std_data/*.cnf $(DESTDIR)$(testdir)/std_data $(INSTALL_DATA) $(srcdir)/std_data/des_key_file $(DESTDIR)$(testdir)/std_data $(INSTALL_DATA) $(srcdir)/std_data/Moscow_leap $(DESTDIR)$(testdir)/std_data $(INSTALL_DATA) $(srcdir)/std_data/*.pem $(DESTDIR)$(testdir)/std_data diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index 1a1d6432411..c94df7f5c2a 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -299,7 +299,7 @@ t9 MyISAM 10 Dynamic 2 216 432 # 2048 0 NULL # # # latin1_swedish_ci NULL prepare stmt4 from ' show status like ''Threads_running'' '; execute stmt4; Variable_name Value -Threads_running 2 +Threads_running # prepare stmt4 from ' show variables like ''sql_mode'' '; execute stmt4; Variable_name Value diff --git a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test index e9b8a1c95b1..c15bc1633a6 100644 --- a/mysql-test/t/ps_1general.test +++ b/mysql-test/t/ps_1general.test @@ -316,6 +316,7 @@ prepare stmt4 from ' show table status from test like ''t9%'' '; --replace_column 8 # 12 # 13 # 14 # # Bug#4288 execute stmt4; +--replace_column 2 # prepare stmt4 from ' show status like ''Threads_running'' '; execute stmt4; prepare stmt4 from ' show variables like ''sql_mode'' '; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 322e21d989b..d96d2419650 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -5951,7 +5951,6 @@ drop table t3| drop procedure bug15217| -# # 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 diff --git a/mysql-test/t/sp.test.orig b/mysql-test/t/sp.test.orig deleted file mode 100644 index a4b99620344..00000000000 --- a/mysql-test/t/sp.test.orig +++ /dev/null @@ -1,5716 +0,0 @@ -# -# Basic stored PROCEDURE tests -# -# Please keep this file free of --error cases and other -# things that will not run in a single debugged mysqld -# process (e.g. master-slave things). -# -# Test cases for bugs are added at the end. See template there. -# -# Tests that require --error go into sp-error.test -# Tests that require inndb go into sp_trans.test -# Tests that check privilege and security issues go to sp-security.test. -# Tests that require multiple connections, except security/privilege tests, -# go to sp-thread. -# 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. - -use test; - -# Test tables -# -# t1 and t2 are reused throughout the file, and dropped at the end. -# t3 and up are created and dropped when needed. -# ---disable_warnings -drop table if exists t1,t2,t3,t4; ---enable_warnings -create table t1 ( - id char(16) not null default '', - data int not null -); -create table t2 ( - s char(16), - i int, - d double -); - - -# Single statement, no params. ---disable_warnings -drop procedure if exists foo42; ---enable_warnings -create procedure foo42() - insert into test.t1 values ("foo", 42); - -call foo42(); -select * from t1; -delete from t1; -drop procedure foo42; - - -# Single statement, two IN params. ---disable_warnings -drop procedure if exists bar; ---enable_warnings -create procedure bar(x char(16), y int) - insert into test.t1 values (x, y); - -call bar("bar", 666); -select * from t1; -delete from t1; -# Don't drop procedure yet... - - -# Now for multiple statements... -delimiter |; - -# Empty statement ---disable_warnings -drop procedure if exists empty| ---enable_warnings -create procedure empty() -begin -end| - -call empty()| -drop procedure empty| - -# Scope test. This is legal (warnings might be possible in the future, -# but for the time being, we just accept it). ---disable_warnings -drop procedure if exists scope| ---enable_warnings -create procedure scope(a int, b float) -begin - declare b int; - declare c float; - - begin - declare c int; - end; -end| - -drop procedure scope| - -# Two statements. ---disable_warnings -drop procedure if exists two| ---enable_warnings -create procedure two(x1 char(16), x2 char(16), y int) -begin - insert into test.t1 values (x1, y); - insert into test.t1 values (x2, y); -end| - -call two("one", "two", 3)| -select * from t1| -delete from t1| -drop procedure two| - - -# Simple test of local variables and SET. ---disable_warnings -drop procedure if exists locset| ---enable_warnings -create procedure locset(x char(16), y int) -begin - declare z1, z2 int; - set z1 = y; - set z2 = z1+2; - insert into test.t1 values (x, z2); -end| - -call locset("locset", 19)| -select * from t1| -delete from t1| -drop procedure locset| - - -# In some contexts local variables are not recognized -# (and in some, you have to qualify the identifier). ---disable_warnings -drop procedure if exists setcontext| ---enable_warnings -create procedure setcontext() -begin - declare data int default 2; - - insert into t1 (id, data) values ("foo", 1); - replace t1 set data = data, id = "bar"; - update t1 set id = "kaka", data = 3 where t1.data = data; -end| - -call setcontext()| -select * from t1| -delete from t1| -drop procedure setcontext| - - -# Set things to null -create table t3 ( d date, i int, f double, s varchar(32) )| - ---disable_warnings -drop procedure if exists nullset| ---enable_warnings -create procedure nullset() -begin - declare ld date; - declare li int; - declare lf double; - declare ls varchar(32); - - set ld = null, li = null, lf = null, ls = null; - insert into t3 values (ld, li, lf, ls); - - insert into t3 (i, f, s) values ((ld is null), 1, "ld is null"), - ((li is null), 1, "li is null"), - ((li = 0), null, "li = 0"), - ((lf is null), 1, "lf is null"), - ((lf = 0), null, "lf = 0"), - ((ls is null), 1, "ls is null"); -end| - -call nullset()| -select * from t3| -drop table t3| -drop procedure nullset| - - -# The peculiar (non-standard) mixture of variables types in SET. ---disable_warnings -drop procedure if exists mixset| ---enable_warnings -create procedure mixset(x char(16), y int) -begin - declare z int; - - set @z = y, z = 666, max_join_size = 100; - insert into test.t1 values (x, z); -end| - -call mixset("mixset", 19)| -show variables like 'max_join_size'| -select id,data,@z from t1| -delete from t1| -drop procedure mixset| - - -# Multiple CALL statements, one with OUT parameter. ---disable_warnings -drop procedure if exists zip| ---enable_warnings -create procedure zip(x char(16), y int) -begin - declare z int; - call zap(y, z); - call bar(x, z); -end| - -# SET local variables and OUT parameter. ---disable_warnings -drop procedure if exists zap| ---enable_warnings -create procedure zap(x int, out y int) -begin - declare z int; - set z = x+1, y = z; -end| - -call zip("zip", 99)| -select * from t1| -delete from t1| -drop procedure zip| -drop procedure bar| - -# Top-level OUT parameter -call zap(7, @zap)| -select @zap| - -drop procedure zap| - - -# "Deep" calls... ---disable_warnings -drop procedure if exists c1| ---enable_warnings -create procedure c1(x int) - call c2("c", x)| ---disable_warnings -drop procedure if exists c2| ---enable_warnings -create procedure c2(s char(16), x int) - call c3(x, s)| ---disable_warnings -drop procedure if exists c3| ---enable_warnings -create procedure c3(x int, s char(16)) - call c4("level", x, s)| ---disable_warnings -drop procedure if exists c4| ---enable_warnings -create procedure c4(l char(8), x int, s char(16)) - insert into t1 values (concat(l,s), x)| - -call c1(42)| -select * from t1| -delete from t1| -drop procedure c1| -drop procedure c2| -drop procedure c3| -drop procedure c4| - -# INOUT test ---disable_warnings -drop procedure if exists iotest| ---enable_warnings -create procedure iotest(x1 char(16), x2 char(16), y int) -begin - call inc2(x2, y); - insert into test.t1 values (x1, y); -end| - ---disable_warnings -drop procedure if exists inc2| ---enable_warnings -create procedure inc2(x char(16), y int) -begin - call inc(y); - insert into test.t1 values (x, y); -end| - ---disable_warnings -drop procedure if exists inc| ---enable_warnings -create procedure inc(inout io int) - set io = io + 1| - -call iotest("io1", "io2", 1)| -select * from t1| -delete from t1| -drop procedure iotest| -drop procedure inc2| - -# Propagating top-level @-vars ---disable_warnings -drop procedure if exists incr| ---enable_warnings -create procedure incr(inout x int) - call inc(x)| - -# Before -select @zap| -call incr(@zap)| -# After -select @zap| - -drop procedure inc| -drop procedure incr| - -# Call-by-value test -# The expected result is: -# ("cbv2", 4) -# ("cbv1", 4711) ---disable_warnings -drop procedure if exists cbv1| ---enable_warnings -create procedure cbv1() -begin - declare y int default 3; - - call cbv2(y+1, y); - insert into test.t1 values ("cbv1", y); -end| - ---disable_warnings -drop procedure if exists cbv2| ---enable_warnings -create procedure cbv2(y1 int, inout y2 int) -begin - set y2 = 4711; - insert into test.t1 values ("cbv2", y1); -end| - -call cbv1()| -select * from t1| -delete from t1| -drop procedure cbv1| -drop procedure cbv2| - - -# Subselect arguments - -insert into t2 values ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3)| - ---disable_warnings -drop procedure if exists sub1| ---enable_warnings -create procedure sub1(id char(16), x int) - insert into test.t1 values (id, x)| - ---disable_warnings -drop procedure if exists sub2| ---enable_warnings -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| - ---disable_warnings -drop procedure if exists sub3| ---enable_warnings -create function sub3(i int) returns int - return i+1| - -call sub1("sub1a", (select 7))| -call sub1("sub1b", (select max(i) from t2))| ---error ER_OPERAND_COLUMNS -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 sub3((select max(i) from t2))| -drop procedure sub1| -drop procedure sub2| -drop function sub3| -delete from t1| -delete from t2| - -# Basic tests of the flow control constructs - -# Just test on 'x'... ---disable_warnings -drop procedure if exists a0| ---enable_warnings -create procedure a0(x int) -while x do - set x = x-1; - insert into test.t1 values ("a0", x); -end while| - -call a0(3)| -select * from t1| -delete from t1| -drop procedure a0| - - -# The same, but with a more traditional test. ---disable_warnings -drop procedure if exists a| ---enable_warnings -create procedure a(x int) -while x > 0 do - set x = x-1; - insert into test.t1 values ("a", x); -end while| - -call a(3)| -select * from t1| -delete from t1| -drop procedure a| - - -# REPEAT ---disable_warnings -drop procedure if exists b| ---enable_warnings -create procedure b(x int) -repeat - insert into test.t1 values (repeat("b",3), x); - set x = x-1; -until x = 0 end repeat| - -call b(3)| -select * from t1| -delete from t1| -drop procedure b| - - -# Check that repeat isn't parsed the wrong way ---disable_warnings -drop procedure if exists b2| ---enable_warnings -create procedure b2(x int) -repeat(select 1 into outfile 'b2'); - insert into test.t1 values (repeat("b2",3), x); - set x = x-1; -until x = 0 end repeat| - -# We don't actually want to call it. -drop procedure b2| - - -# Labelled WHILE with ITERATE (pointless really) ---disable_warnings -drop procedure if exists c| ---enable_warnings -create procedure c(x int) -hmm: while x > 0 do - insert into test.t1 values ("c", x); - set x = x-1; - iterate hmm; - insert into test.t1 values ("x", x); -end while hmm| - -call c(3)| -select * from t1| -delete from t1| -drop procedure c| - - -# Labelled WHILE with LEAVE ---disable_warnings -drop procedure if exists d| ---enable_warnings -create procedure d(x int) -hmm: while x > 0 do - insert into test.t1 values ("d", x); - set x = x-1; - leave hmm; - insert into test.t1 values ("x", x); -end while| - -call d(3)| -select * from t1| -delete from t1| -drop procedure d| - - -# LOOP, with simple IF statement ---disable_warnings -drop procedure if exists e| ---enable_warnings -create procedure e(x int) -foo: loop - if x = 0 then - leave foo; - end if; - insert into test.t1 values ("e", x); - set x = x-1; -end loop foo| - -call e(3)| -select * from t1| -delete from t1| -drop procedure e| - - -# A full IF statement ---disable_warnings -drop procedure if exists f| ---enable_warnings -create procedure f(x int) -if x < 0 then - insert into test.t1 values ("f", 0); -elseif x = 0 then - insert into test.t1 values ("f", 1); -else - insert into test.t1 values ("f", 2); -end if| - -call f(-2)| -call f(0)| -call f(4)| -select * from t1| -delete from t1| -drop procedure f| - - -# This form of CASE is really just syntactic sugar for IF-ELSEIF-... ---disable_warnings -drop procedure if exists g| ---enable_warnings -create procedure g(x int) -case -when x < 0 then - insert into test.t1 values ("g", 0); -when x = 0 then - insert into test.t1 values ("g", 1); -else - insert into test.t1 values ("g", 2); -end case| - -call g(-42)| -call g(0)| -call g(1)| -select * from t1| -delete from t1| -drop procedure g| - - -# The "simple CASE" ---disable_warnings -drop procedure if exists h| ---enable_warnings -create procedure h(x int) -case x -when 0 then - insert into test.t1 values ("h0", x); -when 1 then - insert into test.t1 values ("h1", x); -else - insert into test.t1 values ("h?", x); -end case| - -call h(0)| -call h(1)| -call h(17)| -select * from t1| -delete from t1| -drop procedure h| - - -# It's actually possible to LEAVE a BEGIN-END block ---disable_warnings -drop procedure if exists i| ---enable_warnings -create procedure i(x int) -foo: -begin - if x = 0 then - leave foo; - end if; - insert into test.t1 values ("i", x); -end foo| - -call i(0)| -call i(3)| -select * from t1| -delete from t1| -drop procedure i| - - -# SELECT with one of more result set sent back to the clinet -insert into t1 values ("foo", 3), ("bar", 19)| -insert into t2 values ("x", 9, 4.1), ("y", -1, 19.2), ("z", 3, 2.2)| - ---disable_warnings -drop procedure if exists sel1| ---enable_warnings -create procedure sel1() -begin - select * from t1; -end| - -call sel1()| -drop procedure sel1| - ---disable_warnings -drop procedure if exists sel2| ---enable_warnings -create procedure sel2() -begin - select * from t1; - select * from t2; -end| - -call sel2()| -drop procedure sel2| -delete from t1| -delete from t2| - -# SELECT INTO local variables ---disable_warnings -drop procedure if exists into_test| ---enable_warnings -create procedure into_test(x char(16), y int) -begin - insert into test.t1 values (x, y); - select id,data into x,y from test.t1 limit 1; - insert into test.t1 values (concat(x, "2"), y+2); -end| - -call into_test("into", 100)| -select * from t1| -delete from t1| -drop procedure into_test| - - -# SELECT INTO with a mix of local and global variables ---disable_warnings -drop procedure if exists into_tes2| ---enable_warnings -create procedure into_test2(x char(16), y int) -begin - insert into test.t1 values (x, y); - select id,data into x,@z from test.t1 limit 1; - insert into test.t1 values (concat(x, "2"), y+2); -end| - -call into_test2("into", 100)| -select id,data,@z from t1| -delete from t1| -drop procedure into_test2| - - -# SELECT * INTO ... (bug test) ---disable_warnings -drop procedure if exists into_test3| ---enable_warnings -create procedure into_test3() -begin - declare x char(16); - declare y int; - - select * into x,y from test.t1 limit 1; - insert into test.t2 values (x, y, 0.0); -end| - -insert into t1 values ("into3", 19)| -# Two call needed for bug test -call into_test3()| -call into_test3()| -select * from t2| -delete from t1| -delete from t2| -drop procedure into_test3| - - -# SELECT INTO with no data is a warning ("no data", which we will -# not see normally). When not caught, execution proceeds. ---disable_warnings -drop procedure if exists into_test4| ---enable_warnings -create procedure into_test4() -begin - declare x int; - - select data into x from test.t1 limit 1; - insert into test.t3 values ("into4", x); -end| - -delete from t1| -create table t3 ( s char(16), d int)| -call into_test4()| -select * from t3| -insert into t1 values ("i4", 77)| -call into_test4()| -select * from t3| -delete from t1| -drop table t3| -drop procedure into_test4| - - -# These two (and the two procedures above) caused an assert() to fail in -# sql_base.cc:lock_tables() at some point. ---disable_warnings -drop procedure if exists into_outfile| ---enable_warnings -create procedure into_outfile(x char(16), y int) -begin - insert into test.t1 values (x, y); - select * into outfile "../tmp/spout" from test.t1; - insert into test.t1 values (concat(x, "2"), y+2); -end| - ---system rm -f $MYSQLTEST_VARDIR/tmp/spout -call into_outfile("ofile", 1)| ---system rm -f $MYSQLTEST_VARDIR/tmp/spout -delete from t1| -drop procedure into_outfile| - ---disable_warnings -drop procedure if exists into_dumpfile| ---enable_warnings -create procedure into_dumpfile(x char(16), y int) -begin - insert into test.t1 values (x, y); - select * into dumpfile "../tmp/spdump" from test.t1 limit 1; - insert into test.t1 values (concat(x, "2"), y+2); -end| - ---system rm -f $MYSQLTEST_VARDIR/tmp/spdump -call into_dumpfile("dfile", 1)| ---system rm -f $MYSQLTEST_VARDIR/tmp/spdump -delete from t1| -drop procedure into_dumpfile| - ---disable_warnings -drop procedure if exists create_select| ---enable_warnings -create procedure create_select(x char(16), y int) -begin - insert into test.t1 values (x, y); - create temporary table test.t3 select * from test.t1; - insert into test.t3 values (concat(x, "2"), y+2); -end| - -call create_select("cs", 90)| -select * from t1, t3| -drop table t3| -delete from t1| -drop procedure create_select| - - -# A minimal, constant FUNCTION. ---disable_warnings -drop function if exists e| ---enable_warnings -create function e() returns double - return 2.7182818284590452354| - -set @e = e()| -select e(), @e| - -# A minimal function with one argument ---disable_warnings -drop function if exists inc| ---enable_warnings -create function inc(i int) returns int - return i+1| - -select inc(1), inc(99), inc(-71)| - -# A minimal function with two arguments ---disable_warnings -drop function if exists mul| ---enable_warnings -create function mul(x int, y int) returns int - return x*y| - -select mul(1,1), mul(3,5), mul(4711, 666)| - -# A minimal string function ---disable_warnings -drop function if exists append| ---enable_warnings -create function append(s1 char(8), s2 char(8)) returns char(16) - return concat(s1, s2)| - -select append("foo", "bar")| - -# A function with flow control ---disable_warnings -drop function if exists fac| ---enable_warnings -create function 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| - -select fac(1), fac(2), fac(5), fac(10)| - -# Nested calls ---disable_warnings -drop function if exists fun| ---enable_warnings -create function fun(d double, i int, u int unsigned) returns double - return mul(inc(i), fac(u)) / e()| - -select fun(2.3, 3, 5)| - - -# Various function calls in differen statements - -insert into t2 values (append("xxx", "yyy"), mul(4,3), e())| -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 d = e()| -select * from t2| ---enable_ps_protocol -delete from t2| - -drop function e| -drop function inc| -drop function mul| -drop function append| -drop function fun| - - -# -# CONDITIONs and HANDLERs -# - ---disable_warnings -drop procedure if exists hndlr1| ---enable_warnings -create procedure hndlr1(val int) -begin - declare x int default 0; - 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.t1 values ("hndlr1", val, 2); # Too many values - if (x) then - insert into test.t1 values ("hndlr1", val); # This instead then - end if; -end| - -call hndlr1(42)| -select * from t1| -delete from t1| -drop procedure hndlr1| - ---disable_warnings -drop procedure if exists hndlr2| ---enable_warnings -create procedure hndlr2(val int) -begin - declare x int default 0; - - begin - 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| - -call hndlr2(42)| -select * from t1| -delete from t1| -drop procedure hndlr2| - - ---disable_warnings -drop procedure if exists hndlr3| ---enable_warnings -create procedure hndlr3(val int) -begin - declare x int default 0; - declare continue handler for sqlexception # Any error - begin - declare z int; - - set z = 2 * val; - set x = 1; - end; - - if val < 10 then - begin - declare y int; - - set y = val + 10; - insert into test.t1 values ("hndlr3", y, 2); # Too many values - if x then - insert into test.t1 values ("hndlr3", y); - end if; - end; - end if; -end| - -call hndlr3(3)| -select * from t1| -delete from t1| -drop procedure hndlr3| - - -# Variables might be uninitialized when using handlers -# (Otherwise the compiler can detect if a variable is not set, but -# not in this case.) -create table t3 ( id char(16), data int )| - ---disable_warnings -drop procedure if exists hndlr4| ---enable_warnings -create procedure hndlr4() -begin - declare x int default 0; - declare val int; # No default - declare continue handler for sqlstate '02000' set x=1; - - select data into val from test.t3 where id='z' limit 1; # No hits - - insert into test.t3 values ('z', val); -end| - -call hndlr4()| -select * from t3| -drop table t3| -drop procedure hndlr4| - - -# -# Cursors -# ---disable_warnings -drop procedure if exists cur1| ---enable_warnings -create procedure cur1() -begin - declare a char(16); - declare b int; - declare c double; - declare done int default 0; - declare c cursor for select * from test.t2; - declare continue handler for sqlstate '02000' set done = 1; - - open c; - repeat - fetch c into a, b, c; - if not done then - insert into test.t1 values (a, b+c); - end if; - until done end repeat; - close c; -end| - -insert into t2 values ("foo", 42, -1.9), ("bar", 3, 12.1), ("zap", 666, -3.14)| -call cur1()| -select * from t1| -drop procedure cur1| - -create table t3 ( s char(16), i int )| - ---disable_warnings -drop procedure if exists cur2| ---enable_warnings -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 continue handler for sqlstate '02000' set done = 1; - - open c1; - open c2; - repeat - begin - declare a char(16); - declare b,c int; - - fetch from c1 into a, b; - fetch next from c2 into c; - if not done then - if b < c then - insert into test.t3 values (a, b); - else - insert into test.t3 values (a, c); - end if; - end if; - end; - until done end repeat; - close c1; - close c2; -end| - -call cur2()| -select * from t3| -delete from t1| -delete from t2| -drop table t3| -drop procedure cur2| - - -# The few characteristics we parse ---disable_warnings -drop procedure if exists chistics| ---enable_warnings -create procedure chistics() - language sql - modifies sql data - not deterministic - sql security definer - comment 'Characteristics procedure test' - insert into t1 values ("chistics", 1)| - -show create procedure chistics| -# Call it, just to make sure. -call chistics()| -select * from t1| -delete from t1| -alter procedure chistics sql security invoker| -show create procedure chistics| -drop procedure chistics| - ---disable_warnings -drop function if exists chistics| ---enable_warnings -create function chistics() returns int - language sql - deterministic - sql security invoker - comment 'Characteristics procedure test' - return 42| - -show create function chistics| -# Call it, just to make sure. -select chistics()| -alter function chistics - no sql - comment 'Characteristics function test'| -show create function chistics| -drop function chistics| - - -# Check mode settings -insert into t1 values ("foo", 1), ("bar", 2), ("zip", 3)| - -set @@sql_mode = 'ANSI'| -delimiter $| ---disable_warnings -drop procedure if exists modes$ ---enable_warnings -create procedure modes(out c1 int, out c2 int) -begin - declare done int default 0; - declare x int; - declare c cursor for select data from t1; - declare continue handler for sqlstate '02000' set done = 1; - - select 1 || 2 into c1; - set c2 = 0; - open c; - repeat - fetch c into x; - if not done then - set c2 = c2 + 1; - end if; - until done end repeat; - close c; -end$ -delimiter |$ -set @@sql_mode = ''| - -set sql_select_limit = 1| -call modes(@c1, @c2)| -set sql_select_limit = default| - -select @c1, @c2| -delete from t1| -drop procedure modes| - - -# Check that dropping a database without routines works. -# (Dropping with routines is tested in sp-security.test) -# First an empty db. -create database sp_db1| -drop database sp_db1| - -# Again, with a table. -create database sp_db2| -use sp_db2| -# Just put something in here... -create table t3 ( s char(4), t int )| -insert into t3 values ("abcd", 42), ("dcba", 666)| -use test| -drop database sp_db2| - -# And yet again, with just a procedure. -create database sp_db3| -use sp_db3| ---disable_warnings -drop procedure if exists dummy| ---enable_warnings -create procedure dummy(out x int) - set x = 42| -use test| -drop database sp_db3| -# Check that it's gone -select type,db,name from mysql.proc where db = 'sp_db3'| - - -# ROW_COUNT() function after a CALL -# We test the other cases here too, although it's not strictly SP specific ---disable_warnings -drop procedure if exists rc| ---enable_warnings -create procedure rc() -begin - delete from t1; - insert into t1 values ("a", 1), ("b", 2), ("c", 3); -end| - -call rc()| -select row_count()| ---disable_ps_protocol -update t1 set data=42 where id = "b"; -select row_count()| ---enable_ps_protocol -delete from t1| -select row_count()| -delete from t1| -select row_count()| -select * from t1| -select row_count()| -drop procedure rc| - - -# -# Let us test how well new locking scheme works. -# - -# Let us prepare playground ---disable_warnings -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 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| ---enable_warnings -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) | - -# Test the simplest function using tables -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| - -# 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)| -select f2()| -select id, f2() from t1| - -# Function which uses the same table twice in different queries -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()| -select id, f3() from t1| - -# Calling two functions using same table -select f1(), f3()| -select id, f1(), f3() from t1| - -# 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| - -# Recursive functions which due to this recursion require simultaneous -# access to several instance of the same table won't work -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)| -# Since currently recursive functions are disallowed ER_SP_NO_RECURSION -# error will be returned, once we will allow them error about -# insufficient number of locked tables will be returned instead. ---error ER_SP_NO_RECURSION -select f5(2)| ---error ER_SP_NO_RECURSION -select f5(3)| - -# OTOH this should work -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()| -select id, f6() from t1| - -# -# Let us test how new locking work with views -# -# The most trivial view -create view v1 (a) as select f1()| -select * from v1| -select id, a from t1, v1| -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 * from v1, v2| - -# Nice example where the same view is used on -# on different expression levels -create function f8 () returns int - return (select count(*) from v2)| - -select *, f8() from v1| - -# Let us test what will happen if function is missing -drop function f1| ---error 1356 -select * from v1| - -# And what will happen if we have recursion which involves -# views and functions ? -create function f1() returns int - return (select sum(data) from t1) + (select sum(data) from v1)| ---error ER_SP_NO_RECURSION -select f1()| ---error ER_SP_NO_RECURSION -select * from v1| ---error ER_SP_NO_RECURSION -select * from v2| -# Back to the normal cases -drop function f1| -create function f1() returns int - return (select sum(data) from t1)| - -# Let us also test some weird cases where no real tables is used -create function f0() returns int - return (select * from (select 100) as r)| -select f0()| -select *, f0() from (select 1) as t| -create view v0 as select f0()| -select * from v0| -select *, f0() from v0| - -# -# Let us test how well prelocking works with explicit LOCK TABLES. -# -lock tables t1 read, t1 as t11 read| -# These should work well -select f3()| -select id, f3() from t1 as t11| -# Degenerate cases work too :) -select f0()| -select * from v0| -select *, f0() from v0, (select 123) as d1| -# But these should not ! ---error 1100 -select id, f3() from t1| ---error 1100 -select f4()| -unlock tables| - -# Let us test how LOCK TABLES which implicitly depends on functions -# works -lock tables v2 read, mysql.proc read| -select * from v2| -select * from v1| -# These should not work as we have too little instances of tables locked ---error 1100 -select * from v1, t1| ---error 1100 -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| -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 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 - - -# -# Some "real" examples -# - -# fac - ---disable_warnings -drop table if exists t3| ---enable_warnings -create table t3 (n int unsigned not null primary key, f bigint unsigned)| - ---disable_warnings -drop procedure if exists ifac| ---enable_warnings -create procedure ifac(n int unsigned) -begin - declare i int unsigned default 1; - - if n > 20 then - set n = 20; # bigint overflow otherwise - end if; - while i <= n do - begin - insert into test.t3 values (i, fac(i)); - set i = i + 1; - end; - end while; -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' -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' -show function status like '%f%'| - - -# primes - ---disable_warnings -drop table if exists t3| ---enable_warnings - -create table t3 ( - i int unsigned not null primary key, - p bigint unsigned not null -)| - -insert into t3 values - ( 0, 3), ( 1, 5), ( 2, 7), ( 3, 11), ( 4, 13), - ( 5, 17), ( 6, 19), ( 7, 23), ( 8, 29), ( 9, 31), - (10, 37), (11, 41), (12, 43), (13, 47), (14, 53), - (15, 59), (16, 61), (17, 67), (18, 71), (19, 73), - (20, 79), (21, 83), (22, 89), (23, 97), (24, 101), - (25, 103), (26, 107), (27, 109), (28, 113), (29, 127), - (30, 131), (31, 137), (32, 139), (33, 149), (34, 151), - (35, 157), (36, 163), (37, 167), (38, 173), (39, 179), - (40, 181), (41, 191), (42, 193), (43, 197), (44, 199)| - ---disable_warnings -drop procedure if exists opp| ---enable_warnings -create procedure opp(n bigint unsigned, out pp bool) -begin - declare r double; - declare b, s bigint unsigned default 0; - - set r = sqrt(n); - - again: - loop - if s = 45 then - set b = b+200, s = 0; - else - begin - declare p bigint unsigned; - - select t.p into p from test.t3 t where t.i = s; - if b+p > r then - set pp = 1; - leave again; - end if; - if mod(n, b+p) = 0 then - set pp = 0; - leave again; - end if; - set s = s+1; - end; - end if; - end loop; -end| - ---disable_warnings -drop procedure if exists ip| ---enable_warnings -create procedure ip(m int unsigned) -begin - declare p bigint unsigned; - declare i int unsigned; - - set i=45, p=201; - - while i < m do - begin - declare pp bool default 0; - - call opp(p, pp); - if pp then - insert into test.t3 values (i, p); - set i = i+1; - end if; - set p = p+2; - end; - end while; -end| -show create procedure opp| ---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' -show procedure status like '%p%'| - -# This isn't the fastest way in the world to compute prime numbers, so -# don't be too ambitious. ;-) -call ip(200)| -# We don't want to select the entire table here, just pick a few -# examples. -# The expected result is: -# i p -# --- ---- -# 45 211 -# 100 557 -# 199 1229 -select * from t3 where i=45 or i=100 or i=199| -drop table t3| -drop procedure opp| -drop procedure ip| ---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' -show procedure status like '%p%'| - - -# Fibonacci, for recursion test. (Yet Another Numerical series :) -# ---disable_warnings -drop table if exists t3| ---enable_warnings -create table t3 ( f bigint unsigned not null )| - -# We deliberately do it the awkward way, fetching the last two -# values from the table, in order to exercise various statements -# and table accesses at each turn. ---disable_warnings -drop procedure if exists fib| ---enable_warnings -create procedure fib(n int unsigned) -begin - if n > 1 then - begin - declare x, y bigint unsigned; - declare c cursor for select f from t3 order by f desc limit 2; - - open c; - fetch c into y; - fetch c into x; - close c; - insert into t3 values (x+y); - call fib(n-1); - end; - end if; -end| - -# Enable recursion -set @@max_sp_recursion_depth= 20| - -# Minimum test: recursion of 3 levels - -insert into t3 values (0), (1)| - -call fib(3)| - -select * from t3 order by f asc| - -delete from t3| - -# The original test, 20 levels, ran into memory limits on some machines -# and builds. Try 10 instead... - -insert into t3 values (0), (1)| - -call fib(10)| - -select * from t3 order by f asc| -drop table t3| -drop procedure fib| -set @@max_sp_recursion_depth= 0| - -# -# Comment & suid -# - ---disable_warnings -drop procedure if exists bar| ---enable_warnings -create procedure bar(x char(16), y int) - comment "111111111111" sql security invoker - insert into test.t1 values (x, y)| ---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' -show procedure status like 'bar'| -alter procedure bar comment "2222222222" sql security definer| -alter procedure bar comment "3333333333"| -alter procedure bar| -show create procedure bar| ---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' -show procedure status like 'bar'| -drop procedure bar| - -# -# rexecution -# ---disable_warnings -drop procedure if exists p1| ---enable_warnings -create procedure p1 () - select (select s1 from t3) from t3| - -create table t3 (s1 int)| - -call p1()| -insert into t3 values (1)| -call p1()| -drop procedure p1| -drop table t3| - -# -# backticks -# ---disable_warnings -drop function if exists foo| ---enable_warnings -create function `foo` () returns int - return 5| -select `foo` ()| -drop function `foo`| - -# -# Implicit LOCK/UNLOCK TABLES for table access in functions -# - ---disable_warnings -drop function if exists t1max| ---enable_warnings -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()| -drop function t1max| - -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")| -select * from t3| -select getcount("zip")| -select getcount("zip")| -select * from t3| -select getcount(id) from t1 where data = 3| -select getcount(id) from t1 where data = 5| -select * from t3| -drop table t3| -drop function getcount| - - -# Test cases for different combinations of condition handlers in nested -# begin-end blocks in stored procedures. -# -# Note that the standard specifies that the most specific handler should -# be triggered even if it's an outer handler masked by a less specific -# handler in an inner block. -# Note also that '02000' is more specific than NOT FOUND; there might be -# other '02xxx' states, even if we currently do not issue them in any -# situation (e.g. '02001'). -# -# The combinations we test are these: -# -# Inner -# errcode sqlstate not found sqlwarning sqlexception -# Outer +------------+------------+------------+------------+------------+ -#errcode | h_ee (i) | h_es (o) | h_en (o) | h_ew (o) | h_ex (o) | -#sqlstate | h_se (i) | h_ss (i) | h_sn (o) | h_sw (o) | h_sx (o) | -#not found | h_ne (i) | h_ns (i) | h_nn (i) | | | -#sqlwarning | h_we (i) | h_ws (i) | | h_ww (i) | | -#sqlexception | h_xe (i) | h_xs (i) | | | h_xx (i) | -# +------------+---------------------------------------------------+ -# -# (i) means that the inner handler is the one that should be invoked, -# (o) means that the outer handler should be invoked. -# -# ('not found', 'sqlwarning' and 'sqlexception' are mutually exclusive, hence -# no tests for those combinations.) -# - ---disable_warnings -drop table if exists t3| -drop procedure if exists h_ee| -drop procedure if exists h_es| -drop procedure if exists h_en| -drop procedure if exists h_ew| -drop procedure if exists h_ex| -drop procedure if exists h_se| -drop procedure if exists h_ss| -drop procedure if exists h_sn| -drop procedure if exists h_sw| -drop procedure if exists h_sx| -drop procedure if exists h_ne| -drop procedure if exists h_ns| -drop procedure if exists h_nn| -drop procedure if exists h_we| -drop procedure if exists h_ws| -drop procedure if exists h_ww| -drop procedure if exists h_xe| -drop procedure if exists h_xs| -drop procedure if exists h_xx| ---enable_warnings - -# smallint - to get out of range warnings -# primary key - to get constraint errors -create table t3 (a smallint primary key)| - -insert into t3 (a) values (1)| - -create procedure h_ee() - deterministic -begin - declare continue handler for 1062 -- ER_DUP_ENTRY - select 'Outer (bad)' as 'h_ee'; - - begin - declare continue handler for 1062 -- ER_DUP_ENTRY - select 'Inner (good)' as 'h_ee'; - - insert into t3 values (1); - end; -end| - -create procedure h_es() - deterministic -begin - declare continue handler for 1062 -- ER_DUP_ENTRY - select 'Outer (good)' as 'h_es'; - - begin - -- integrity constraint violation - declare continue handler for sqlstate '23000' - select 'Inner (bad)' as 'h_es'; - - insert into t3 values (1); - end; -end| - -create procedure h_en() - deterministic -begin - declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA - select 'Outer (good)' as 'h_en'; - - begin - declare x int; - declare continue handler for sqlstate '02000' -- no data - select 'Inner (bad)' as 'h_en'; - - select a into x from t3 where a = 42; - end; -end| - -create procedure h_ew() - deterministic -begin - declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE - select 'Outer (good)' as 'h_ew'; - - begin - declare continue handler for sqlwarning - select 'Inner (bad)' as 'h_ew'; - - insert into t3 values (123456789012); - end; - delete from t3; - insert into t3 values (1); -end| - -create procedure h_ex() - deterministic -begin - declare continue handler for 1062 -- ER_DUP_ENTRY - select 'Outer (good)' as 'h_ex'; - - begin - declare continue handler for sqlexception - select 'Inner (bad)' as 'h_ex'; - - insert into t3 values (1); - end; -end| - -create procedure h_se() - deterministic -begin - -- integrity constraint violation - declare continue handler for sqlstate '23000' - select 'Outer (bad)' as 'h_se'; - - begin - declare continue handler for 1062 -- ER_DUP_ENTRY - select 'Inner (good)' as 'h_se'; - - insert into t3 values (1); - end; -end| - -create procedure h_ss() - deterministic -begin - -- integrity constraint violation - declare continue handler for sqlstate '23000' - select 'Outer (bad)' as 'h_ss'; - - begin - -- integrity constraint violation - declare continue handler for sqlstate '23000' - select 'Inner (good)' as 'h_ss'; - - insert into t3 values (1); - end; -end| - -create procedure h_sn() - deterministic -begin - -- Note: '02000' is more specific than NOT FOUND ; - -- there might be other not found states - declare continue handler for sqlstate '02000' -- no data - select 'Outer (good)' as 'h_sn'; - - begin - declare x int; - declare continue handler for not found - select 'Inner (bad)' as 'h_sn'; - - select a into x from t3 where a = 42; - end; -end| - -create procedure h_sw() - deterministic -begin - -- data exception - numeric value out of range - declare continue handler for sqlstate '22003' - select 'Outer (good)' as 'h_sw'; - - begin - declare continue handler for sqlwarning - select 'Inner (bad)' as 'h_sw'; - - insert into t3 values (123456789012); - end; - delete from t3; - insert into t3 values (1); -end| - -create procedure h_sx() - deterministic -begin - -- integrity constraint violation - declare continue handler for sqlstate '23000' - select 'Outer (good)' as 'h_sx'; - - begin - declare continue handler for sqlexception - select 'Inner (bad)' as 'h_sx'; - - insert into t3 values (1); - end; -end| - -create procedure h_ne() - deterministic -begin - declare continue handler for not found - select 'Outer (bad)' as 'h_ne'; - - begin - declare x int; - declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA - select 'Inner (good)' as 'h_ne'; - - select a into x from t3 where a = 42; - end; -end| - -create procedure h_ns() - deterministic -begin - declare continue handler for not found - select 'Outer (bad)' as 'h_ns'; - - begin - declare x int; - declare continue handler for sqlstate '02000' -- no data - select 'Inner (good)' as 'h_ns'; - - select a into x from t3 where a = 42; - end; -end| - -create procedure h_nn() - deterministic -begin - declare continue handler for not found - select 'Outer (bad)' as 'h_nn'; - - begin - declare x int; - declare continue handler for not found - select 'Inner (good)' as 'h_nn'; - - select a into x from t3 where a = 42; - end; -end| - -create procedure h_we() - deterministic -begin - declare continue handler for sqlwarning - select 'Outer (bad)' as 'h_we'; - - begin - declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE - select 'Inner (good)' as 'h_we'; - - insert into t3 values (123456789012); - end; - delete from t3; - insert into t3 values (1); -end| - -create procedure h_ws() - deterministic -begin - declare continue handler for sqlwarning - select 'Outer (bad)' as 'h_ws'; - - begin - -- data exception - numeric value out of range - declare continue handler for sqlstate '22003' - select 'Inner (good)' as 'h_ws'; - - insert into t3 values (123456789012); - end; - delete from t3; - insert into t3 values (1); -end| - -create procedure h_ww() - deterministic -begin - declare continue handler for sqlwarning - select 'Outer (bad)' as 'h_ww'; - - begin - declare continue handler for sqlwarning - select 'Inner (good)' as 'h_ww'; - - insert into t3 values (123456789012); - end; - delete from t3; - insert into t3 values (1); -end| - -create procedure h_xe() - deterministic -begin - declare continue handler for sqlexception - select 'Outer (bad)' as 'h_xe'; - - begin - declare continue handler for 1062 -- ER_DUP_ENTRY - select 'Inner (good)' as 'h_xe'; - - insert into t3 values (1); - end; -end| - -create procedure h_xs() - deterministic -begin - declare continue handler for sqlexception - select 'Outer (bad)' as 'h_xs'; - - begin - -- integrity constraint violation - declare continue handler for sqlstate '23000' - select 'Inner (good)' as 'h_xs'; - - insert into t3 values (1); - end; -end| - -create procedure h_xx() - deterministic -begin - declare continue handler for sqlexception - select 'Outer (bad)' as 'h_xx'; - - begin - declare continue handler for sqlexception - select 'Inner (good)' as 'h_xx'; - - insert into t3 values (1); - end; -end| - -call h_ee()| -call h_es()| -call h_en()| -call h_ew()| -call h_ex()| -call h_se()| -call h_ss()| -call h_sn()| -call h_sw()| -call h_sx()| -call h_ne()| -call h_ns()| -call h_nn()| -call h_we()| -call h_ws()| -call h_ww()| -call h_xe()| -call h_xs()| -call h_xx()| - -drop table t3| -drop procedure h_ee| -drop procedure h_es| -drop procedure h_en| -drop procedure h_ew| -drop procedure h_ex| -drop procedure h_se| -drop procedure h_ss| -drop procedure h_sn| -drop procedure h_sw| -drop procedure h_sx| -drop procedure h_ne| -drop procedure h_ns| -drop procedure h_nn| -drop procedure h_we| -drop procedure h_ws| -drop procedure h_ww| -drop procedure h_xe| -drop procedure h_xs| -drop procedure h_xx| - - -# -# Test cases for old bugs -# - -# -# BUG#822 -# ---disable_warnings -drop procedure if exists bug822| ---enable_warnings -create procedure bug822(a_id char(16), a_data int) -begin - declare n int; - select count(*) into n from t1 where id = a_id and data = a_data; - if n = 0 then - insert into t1 (id, data) values (a_id, a_data); - end if; -end| - -delete from t1| -call bug822('foo', 42)| -call bug822('foo', 42)| -call bug822('bar', 666)| -select * from t1| -delete from t1| -drop procedure bug822| - -# -# BUG#1495 -# ---disable_warnings -drop procedure if exists bug1495| ---enable_warnings -create procedure bug1495() -begin - declare x int; - - select data into x from t1 order by id limit 1; - if x > 10 then - insert into t1 values ("less", x-10); - else - insert into t1 values ("more", x+10); - end if; -end| - -insert into t1 values ('foo', 12)| -call bug1495()| -delete from t1 where id='foo'| -insert into t1 values ('bar', 7)| -call bug1495()| -delete from t1 where id='bar'| -select * from t1| -delete from t1| -drop procedure bug1495| - -# -# BUG#1547 -# ---disable_warnings -drop procedure if exists bug1547| ---enable_warnings -create procedure bug1547(s char(16)) -begin - declare x int; - - select data into x from t1 where s = id limit 1; - if x > 10 then - insert into t1 values ("less", x-10); - else - insert into t1 values ("more", x+10); - end if; -end| - -insert into t1 values ("foo", 12), ("bar", 7)| -call bug1547("foo")| -call bug1547("bar")| -select * from t1| -delete from t1| -drop procedure bug1547| - -# -# BUG#1656 -# ---disable_warnings -drop table if exists t70| ---enable_warnings -create table t70 (s1 int,s2 int)| -insert into t70 values (1,2)| - ---disable_warnings -drop procedure if exists bug1656| ---enable_warnings -create procedure bug1656(out p1 int, out p2 int) - select * into p1, p1 from t70| - -call bug1656(@1, @2)| -select @1, @2| -drop table t70| -drop procedure bug1656| - -# -# BUG#1862 -# -create table t3(a int)| - ---disable_warnings -drop procedure if exists bug1862| ---enable_warnings -create procedure bug1862() -begin - insert into t3 values(2); - flush tables; -end| - -call bug1862()| -# the second call caused a segmentation -call bug1862()| -select * from t3| -drop table t3| -drop procedure bug1862| - -# -# BUG#1874 -# ---disable_warnings -drop procedure if exists bug1874| ---enable_warnings -create procedure bug1874() -begin - declare x int; - declare y double; - select max(data) into x from t1; - insert into t2 values ("max", x, 0); - select min(data) into x from t1; - insert into t2 values ("min", x, 0); - select sum(data) into x from t1; - insert into t2 values ("sum", x, 0); - select avg(data) into y from t1; - insert into t2 values ("avg", 0, y); -end| - -insert into t1 (data) values (3), (1), (5), (9), (4)| -call bug1874()| -select * from t2| -delete from t1| -delete from t2| -drop procedure bug1874| - -# -# BUG#2260 -# ---disable_warnings -drop procedure if exists bug2260| ---enable_warnings -create procedure bug2260() -begin - declare v1 int; - declare c1 cursor for select data from t1; - declare continue handler for not found set @x2 = 1; - - open c1; - fetch c1 into v1; - set @x2 = 2; - close c1; -end| - -call bug2260()| -select @x2| -drop procedure bug2260| - -# -# BUG#2267 "Lost connect if stored procedure has SHOW FUNCTION STATUS" -# ---disable_warnings -drop procedure if exists bug2267_1| ---enable_warnings -create procedure bug2267_1() -begin - show procedure status; -end| - ---disable_warnings -drop procedure if exists bug2267_2| ---enable_warnings -create procedure bug2267_2() -begin - show function status; -end| - ---disable_warnings -drop procedure if exists bug2267_3| ---enable_warnings -create procedure bug2267_3() -begin - show create procedure bug2267_1; -end| - ---disable_warnings -drop procedure if exists bug2267_4| -drop function if exists bug2267_4| ---enable_warnings -create procedure bug2267_4() -begin - show create function bug2267_4; -end| -create function bug2267_4() returns int return 100| - ---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' -call bug2267_1()| ---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' -call bug2267_2()| -call bug2267_3()| -call bug2267_4()| - -drop procedure bug2267_1| -drop procedure bug2267_2| -drop procedure bug2267_3| -drop procedure bug2267_4| -drop function bug2267_4| - -# -# BUG#2227 -# ---disable_warnings -drop procedure if exists bug2227| ---enable_warnings -create procedure bug2227(x int) -begin - declare y float default 2.6; - declare z char(16) default "zzz"; - - select 1.3, x, y, 42, z; -end| - -call bug2227(9)| -drop procedure bug2227| - -# -# BUG#2614 "Stored procedure with INSERT ... SELECT that does not -# contain any tables crashes server" -# ---disable_warnings -drop procedure if exists bug2614| ---enable_warnings -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| - ---disable_warnings -call bug2614()| ---enable_warnings -call bug2614()| -drop table t3| -drop procedure bug2614| - -# -# BUG#2674 -# ---disable_warnings -drop function if exists bug2674| ---enable_warnings -create function bug2674() returns int - return @@sort_buffer_size| - -set @osbs = @@sort_buffer_size| -set @@sort_buffer_size = 262000| -select bug2674()| -drop function bug2674| -set @@sort_buffer_size = @osbs| - -# -# BUG#3259 -# ---disable_warnings -drop procedure if exists bug3259_1 | ---enable_warnings -create procedure bug3259_1 () begin end| ---disable_warnings -drop procedure if exists BUG3259_2 | ---enable_warnings -create procedure BUG3259_2 () begin end| ---disable_warnings -drop procedure if exists Bug3259_3 | ---enable_warnings -create procedure Bug3259_3 () begin end| - -call BUG3259_1()| -call BUG3259_1()| -call bug3259_2()| -call Bug3259_2()| -call bug3259_3()| -call bUG3259_3()| - -drop procedure bUg3259_1| -drop procedure BuG3259_2| -drop procedure BUG3259_3| - -# -# BUG#2772 -# ---disable_warnings -drop function if exists bug2772| ---enable_warnings -create function bug2772() returns char(10) character set latin2 - return 'a'| - -select bug2772()| -drop function bug2772| - -# -# BUG#2776 -# ---disable_warnings -drop procedure if exists bug2776_1| ---enable_warnings -create procedure bug2776_1(out x int) -begin - declare v int; - - set v = default; - set x = v; -end| - ---disable_warnings -drop procedure if exists bug2776_2| ---enable_warnings -create procedure bug2776_2(out x int) -begin - declare v int default 42; - - set v = default; - set x = v; -end| - -set @x = 1| -call bug2776_1(@x)| -select @x| -call bug2776_2(@x)| -select @x| -drop procedure bug2776_1| -drop procedure bug2776_2| - -# -# BUG#2780 -# -create table t3 (s1 smallint)| - -insert into t3 values (123456789012)| - ---disable_warnings -drop procedure if exists bug2780| ---enable_warnings -create procedure bug2780() -begin - declare exit handler for sqlwarning set @x = 1; - - set @x = 0; - insert into t3 values (123456789012); - insert into t3 values (0); -end| - -call bug2780()| -select @x| -select * from t3| - -drop procedure bug2780| -drop table t3| - -# -# BUG#1863 -# -create table t3 (content varchar(10) )| -insert into t3 values ("test1")| -insert into t3 values ("test2")| -create table t4 (f1 int, rc int, t3 int)| - ---disable_warnings -drop procedure if exists bug1863| ---enable_warnings -create procedure bug1863(in1 int) -begin - - declare ind int default 0; - declare t1 int; - declare t2 int; - declare t3 int; - - declare rc int default 0; - declare continue handler for 1065 set rc = 1; - - drop temporary table if exists temp_t1; - create temporary table temp_t1 ( - f1 int auto_increment, f2 varchar(20), primary key (f1) - ); - - insert into temp_t1 (f2) select content from t3; - - select f2 into t3 from temp_t1 where f1 = 10; - - if (rc) then - insert into t4 values (1, rc, t3); - end if; - - insert into t4 values (2, rc, t3); - -end| - -call bug1863(10)| -call bug1863(10)| -select * from t4| - -drop procedure bug1863| -drop temporary table temp_t1; -drop table t3, t4| - -# -# BUG#2656 -# - -create table t3 ( - OrderID int not null, - MarketID int, - primary key (OrderID) -)| - -create table t4 ( - MarketID int not null, - Market varchar(60), - Status char(1), - primary key (MarketID) -)| - -insert t3 (OrderID,MarketID) values (1,1)| -insert t3 (OrderID,MarketID) values (2,2)| -insert t4 (MarketID,Market,Status) values (1,"MarketID One","A")| -insert t4 (MarketID,Market,Status) values (2,"MarketID Two","A")| - ---disable_warnings -drop procedure if exists bug2656_1| ---enable_warnings -create procedure bug2656_1() -begin - select - m.Market - from t4 m JOIN t3 o - ON o.MarketID != 1 and o.MarketID = m.MarketID; -end | - ---disable_warnings -drop procedure if exists bug2656_2| ---enable_warnings -create procedure bug2656_2() -begin - select - m.Market - from - t4 m, t3 o - where - m.MarketID != 1 and m.MarketID = o.MarketID; - -end | - -call bug2656_1()| -call bug2656_1()| -call bug2656_2()| -call bug2656_2()| -drop procedure bug2656_1| -drop procedure bug2656_2| -drop table t3, t4| - - -# -# BUG#3426 -# ---disable_warnings -drop procedure if exists bug3426| ---enable_warnings -create procedure bug3426(in_time int unsigned, out x int) -begin - if in_time is null then - set @stamped_time=10; - set x=1; - else - set @stamped_time=in_time; - set x=2; - end if; -end| - -call bug3426(1000, @i)| -select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| -call bug3426(NULL, @i)| -select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| -# Clear SP cache -alter procedure bug3426 sql security invoker| -call bug3426(NULL, @i)| -select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| -call bug3426(1000, @i)| -select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| - -drop procedure bug3426| - -# -# BUG#3448 -# ---disable_warnings -create table t3 ( - a int primary key, - ach char(1) -) engine = innodb| - -create table t4 ( - b int primary key , - bch char(1) -) engine = innodb| ---enable_warnings - -insert into t3 values (1 , 'aCh1' ) , ('2' , 'aCh2')| -insert into t4 values (1 , 'bCh1' )| - ---disable_warnings -drop procedure if exists bug3448| ---enable_warnings -create procedure bug3448() - select * from t3 inner join t4 on t3.a = t4.b| - -select * from t3 inner join t4 on t3.a = t4.b| -call bug3448()| -call bug3448()| - -drop procedure bug3448| -drop table t3, t4| - - -# -# BUG#3734 -# -create table t3 ( - id int unsigned auto_increment not null primary key, - title VARCHAR(200), - body text, - fulltext (title,body) -)| - -insert into t3 (title,body) values - ('MySQL Tutorial','DBMS stands for DataBase ...'), - ('How To Use MySQL Well','After you went through a ...'), - ('Optimizing MySQL','In this tutorial we will show ...'), - ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), - ('MySQL vs. YourSQL','In the following database comparison ...'), - ('MySQL Security','When configured properly, MySQL ...')| - ---disable_warnings -drop procedure if exists bug3734 | ---enable_warnings -create procedure bug3734 (param1 varchar(100)) - select * from t3 where match (title,body) against (param1)| - -call bug3734('database')| -call bug3734('Security')| - -drop procedure bug3734| -drop table t3| - -# -# BUG#3863 -# ---disable_warnings -drop procedure if exists bug3863| ---enable_warnings -create procedure bug3863() -begin - set @a = 0; - while @a < 5 do - set @a = @a + 1; - end while; -end| - -call bug3863()| -select @a| -call bug3863()| -select @a| - -drop procedure bug3863| - -# -# BUG#2460 -# - -create table t3 ( - id int(10) unsigned not null default 0, - rid int(10) unsigned not null default 0, - msg text not null, - primary key (id), - unique key rid (rid, id) -)| - ---disable_warnings -drop procedure if exists bug2460_1| ---enable_warnings -create procedure bug2460_1(in v int) -begin - ( select n0.id from t3 as n0 where n0.id = v ) - union - ( select n0.id from t3 as n0, t3 as n1 - where n0.id = n1.rid and n1.id = v ) - union - ( select n0.id from t3 as n0, t3 as n1, t3 as n2 - where n0.id = n1.rid and n1.id = n2.rid and n2.id = v ); -end| - -call bug2460_1(2)| -call bug2460_1(2)| -insert into t3 values (1, 1, 'foo'), (2, 1, 'bar'), (3, 1, 'zip zap')| -call bug2460_1(2)| -call bug2460_1(2)| - ---disable_warnings -drop procedure if exists bug2460_2| ---enable_warnings -create procedure bug2460_2() -begin - drop table if exists t3; - create temporary table t3 (s1 int); - insert into t3 select 1 union select 1; -end| - -call bug2460_2()| -call bug2460_2()| -select * from t3| - -drop procedure bug2460_1| -drop procedure bug2460_2| -drop table t3| - - -# -# BUG#2564 -# -set @@sql_mode = ''| ---disable_warnings -drop procedure if exists bug2564_1| ---enable_warnings -create procedure bug2564_1() - comment 'Joe''s procedure' - insert into `t1` values ("foo", 1)| - -set @@sql_mode = 'ANSI_QUOTES'| ---disable_warnings -drop procedure if exists bug2564_2| ---enable_warnings -create procedure bug2564_2() - insert into "t1" values ('foo', 1)| - -delimiter $| -set @@sql_mode = ''$ ---disable_warnings -drop function if exists bug2564_3$ ---enable_warnings -create function bug2564_3(x int, y int) returns int - return x || y$ - -set @@sql_mode = 'ANSI'$ ---disable_warnings -drop function if exists bug2564_4$ ---enable_warnings -create function bug2564_4(x int, y int) returns int - return x || y$ -delimiter |$ - -set @@sql_mode = ''| -show create procedure bug2564_1| -show create procedure bug2564_2| -show create function bug2564_3| -show create function bug2564_4| - -drop procedure bug2564_1| -drop procedure bug2564_2| -drop function bug2564_3| -drop function bug2564_4| - -# -# BUG#3132 -# ---disable_warnings -drop function if exists bug3132| ---enable_warnings -create function bug3132(s char(20)) returns char(50) - return concat('Hello, ', s, '!')| - -select bug3132('Bob') union all select bug3132('Judy')| -drop function bug3132| - -# -# BUG#3843 -# ---disable_warnings -drop procedure if exists bug3843| ---enable_warnings -create procedure bug3843() - analyze table t1| - -# Testing for packets out of order -call bug3843()| -call bug3843()| -select 1+2| - -drop procedure bug3843| - -# -# BUG#3368 -# -create table t3 ( s1 char(10) )| -insert into t3 values ('a'), ('b')| - ---disable_warnings -drop procedure if exists bug3368| ---enable_warnings -create procedure bug3368(v char(10)) -begin - select group_concat(v) from t3; -end| - -call bug3368('x')| -call bug3368('yz')| -drop procedure bug3368| -drop table t3| - -# -# BUG#4579 -# -create table t3 (f1 int, f2 int)| -insert into t3 values (1,1)| - ---disable_warnings -drop procedure if exists bug4579_1| ---enable_warnings -create procedure bug4579_1 () -begin - declare sf1 int; - - select f1 into sf1 from t3 where f1=1 and f2=1; - update t3 set f2 = f2 + 1 where f1=1 and f2=1; - call bug4579_2(); -end| - ---disable_warnings -drop procedure if exists bug4579_2| ---enable_warnings -create procedure bug4579_2 () -begin -end| - -call bug4579_1()| -call bug4579_1()| -call bug4579_1()| - -drop procedure bug4579_1| -drop procedure bug4579_2| -drop table t3| - -# -# BUG#2773: Function's data type ignored in stored procedures -# ---disable_warnings -drop procedure if exists bug2773| ---enable_warnings - -create function bug2773() returns int return null| -create table t3 as select bug2773()| -show create table t3| -drop table t3| -drop function bug2773| - -# -# BUG#3788: Stored procedure packet error -# ---disable_warnings -drop procedure if exists bug3788| ---enable_warnings - -create function bug3788() returns date return cast("2005-03-04" as date)| -select bug3788()| -drop function bug3788| - -create function bug3788() returns binary(1) return 5| -select bug3788()| -drop function bug3788| - - -# -# BUG#4726 -# -create table t3 (f1 int, f2 int, f3 int)| -insert into t3 values (1,1,1)| - ---disable_warnings -drop procedure if exists bug4726| ---enable_warnings -create procedure bug4726() -begin - declare tmp_o_id INT; - declare tmp_d_id INT default 1; - - while tmp_d_id <= 2 do - begin - select f1 into tmp_o_id from t3 where f2=1 and f3=1; - set tmp_d_id = tmp_d_id + 1; - end; - end while; -end| - -call bug4726()| -call bug4726()| -call bug4726()| - -drop procedure bug4726| -drop table t3| - -# -# BUG#4318 -# - ---disable_parsing # Don't know if HANDLER commands can work with SPs, or at all.. -create table t3 (s1 int)| -insert into t3 values (3), (4)| - ---disable_warnings -drop procedure if exists bug4318| ---enable_warnings -create procedure bug4318() - handler t3 read next| - -handler t3 open| -# Expect no results, as tables are closed, but there shouldn't be any errors -call bug4318()| -call bug4318()| -handler t3 close| - -drop procedure bug4318| -drop table t3| ---enable_parsing - -# -# BUG#4902: Stored procedure with SHOW WARNINGS leads to packet error -# -# Added tests for most other show commands we could find too. -# (Skipping those already tested, and the ones depending on optional handlers.) -# -# Note: This will return a large number of results of different formats, -# which makes it impossible to filter with --replace_column. -# It's possible that some of these are not deterministic across -# platforms. If so, just remove the offending command. -# ---disable_warnings -drop procedure if exists bug4902| ---enable_warnings -create procedure bug4902() -begin - show charset like 'foo'; - show collation like 'foo'; - show column types; - show create table t1; - show create database test; - show databases like 'foo'; - show errors; - show columns from t1; - show grants for 'root'@'localhost'; - show keys from t1; - show open tables like 'foo'; - show privileges; - show status like 'foo'; - show tables like 'foo'; - show variables like 'foo'; - show warnings; -end| ---disable_parsing -show binlog events; -show storage engines; -show master status; -show slave hosts; -show slave status; ---enable_parsing - -call bug4902()| -call bug4902()| - -drop procedure bug4902| - -# We need separate SP for SHOW PROCESSLIST since we want use replace_column ---disable_warnings -drop procedure if exists bug4902_2| ---enable_warnings -create procedure bug4902_2() -begin - show processlist; -end| ---replace_column 1 # 6 # 3 localhost -call bug4902_2()| ---replace_column 1 # 6 # 3 localhost -call bug4902_2()| -drop procedure bug4902_2| - -# -# BUG#4904 -# ---disable_warnings -drop procedure if exists bug4904| ---enable_warnings -create procedure bug4904() -begin - declare continue handler for sqlstate 'HY000' begin end; - - create table t2 as select * from t3; -end| - --- error 1146 -call bug4904()| - -drop procedure bug4904| - -create table t3 (s1 char character set latin1, s2 char character set latin2)| - ---disable_warnings -drop procedure if exists bug4904| ---enable_warnings -create procedure bug4904 () -begin - declare continue handler for sqlstate 'HY000' begin end; - - select s1 from t3 union select s2 from t3; -end| - -call bug4904()| - -drop procedure bug4904| -drop table t3| - -# -# BUG#336 -# ---disable_warnings -drop procedure if exists bug336| ---enable_warnings -create procedure bug336(out y int) -begin - declare x int; - set x = (select sum(t.data) from test.t1 t); - set y = x; -end| - -insert into t1 values ("a", 2), ("b", 3)| -call bug336(@y)| -select @y| -delete from t1| -drop procedure bug336| - -# -# BUG#3157 -# ---disable_warnings -drop procedure if exists bug3157| ---enable_warnings -create procedure bug3157() -begin - if exists(select * from t1) then - set @n= @n + 1; - end if; - if (select count(*) from t1) then - set @n= @n + 1; - end if; -end| - -set @n = 0| -insert into t1 values ("a", 1)| -call bug3157()| -select @n| -delete from t1| -drop procedure bug3157| - -# -# BUG#5251: mysql changes creation time of a procedure/function when altering -# ---disable_warnings -drop procedure if exists bug5251| ---enable_warnings -create procedure bug5251() -begin -end| - -select created into @c1 from mysql.proc - where db='test' and name='bug5251'| ---sleep 2 -alter procedure bug5251 comment 'foobar'| -select count(*) from mysql.proc - where db='test' and name='bug5251' and created = @c1| - -drop procedure bug5251| - -# -# BUG#5279: Stored procedure packets out of order if CHECKSUM TABLE -# ---disable_warnings -drop procedure if exists bug5251| ---enable_warnings -create procedure bug5251() - checksum table t1| - -call bug5251()| -call bug5251()| -drop procedure bug5251| - -# -# BUG#5287: Stored procedure crash if leave outside loop -# ---disable_warnings -drop procedure if exists bug5287| ---enable_warnings -create procedure bug5287(param1 int) -label1: - begin - declare c cursor for select 5; - - loop - if param1 >= 0 then - leave label1; - end if; - end loop; -end| -call bug5287(1)| -drop procedure bug5287| - - -# -# BUG#5307: Stored procedure allows statement after BEGIN ... END -# ---disable_warnings -drop procedure if exists bug5307| ---enable_warnings -create procedure bug5307() -begin -end; set @x = 3| - -call bug5307()| -select @x| -drop procedure bug5307| - -# -# BUG#5258: Stored procedure modified date is 0000-00-00 -# (This was a design flaw) ---disable_warnings -drop procedure if exists bug5258| ---enable_warnings -create procedure bug5258() -begin -end| - ---disable_warnings -drop procedure if exists bug5258_aux| ---enable_warnings -create procedure bug5258_aux() -begin - declare c, m char(19); - - select created,modified into c,m from mysql.proc where name = 'bug5258'; - if c = m then - select 'Ok'; - else - select c, m; - end if; -end| - -call bug5258_aux()| - -drop procedure bug5258| -drop procedure bug5258_aux| - -# -# BUG#4487: Stored procedure connection aborted if uninitialized char -# ---disable_warnings -drop function if exists bug4487| ---enable_warnings -create function bug4487() returns char -begin - declare v char; - return v; -end| - -select bug4487()| -drop function bug4487| - - -# -# BUG#4941: Stored procedure crash fetching null value into variable. -# ---disable_warnings -drop procedure if exists bug4941| ---enable_warnings ---disable_warnings -drop procedure if exists bug4941| ---enable_warnings -create procedure bug4941(out x int) -begin - declare c cursor for select i from t2 limit 1; - open c; - fetch c into x; - close c; -end| - -insert into t2 values (null, null, null)| -set @x = 42| -call bug4941(@x)| -select @x| -delete from t1| -drop procedure bug4941| - - -# -# BUG#3583: query cache doesn't work for stored procedures -# ---disable_warnings -drop procedure if exists bug3583| ---enable_warnings ---disable_warnings -drop procedure if exists bug3583| ---enable_warnings -create procedure bug3583() -begin - declare c int; - - select * from t1; - select count(*) into c from t1; - select c; -end| - -insert into t1 values ("x", 3), ("y", 5)| -set @x = @@query_cache_size| -set global query_cache_size = 10*1024*1024| - -flush status| -flush query cache| -show status like 'Qcache_hits'| -call bug3583()| -show status like 'Qcache_hits'| -call bug3583()| -call bug3583()| -show status like 'Qcache_hits'| - -set global query_cache_size = @x| -flush status| -flush query cache| -delete from t1| -drop procedure bug3583| - -# -# BUG#4905: Stored procedure doesn't clear for "Rows affected" -# ---disable_warnings -drop procedure if exists bug4905| ---enable_warnings - -create table t3 (s1 int,primary key (s1))| - ---disable_warnings -drop procedure if exists bug4905| ---enable_warnings -create procedure bug4905() -begin - declare v int; - declare continue handler for sqlstate '23000' set v = 5; - - insert into t3 values (1); -end| - -call bug4905()| -select row_count()| -call bug4905()| -select row_count()| -call bug4905()| -select row_count()| -select * from t3| - -drop procedure bug4905| -drop table t3| - -# -# BUG#6022: Stored procedure shutdown problem with self-calling function. -# - ---disable_parsing # until we implement support for recursive stored functions. ---disable_warnings -drop function if exists bug6022| ---enable_warnings - ---disable_warnings -drop function if exists bug6022| ---enable_warnings -create function bug6022(x int) returns int -begin - if x < 0 then - return 0; - else - return bug6022(x-1); - end if; -end| - -select bug6022(5)| -drop function bug6022| ---enable_parsing - -# -# BUG#6029: Stored procedure specific handlers should have priority -# ---disable_warnings -drop procedure if exists bug6029| ---enable_warnings - ---disable_warnings -drop procedure if exists bug6029| ---enable_warnings -create procedure bug6029() -begin - declare exit handler for 1136 select '1136'; - declare exit handler for sqlstate '23000' select 'sqlstate 23000'; - declare continue handler for sqlexception select 'sqlexception'; - - insert into t3 values (1); - insert into t3 values (1,2); -end| - -create table t3 (s1 int, primary key (s1))| -insert into t3 values (1)| -call bug6029()| -delete from t3| -call bug6029()| - -drop procedure bug6029| -drop table t3| - -# -# BUG#8540: Local variable overrides an alias -# ---disable_warnings -drop procedure if exists bug8540| ---enable_warnings - -create procedure bug8540() -begin - declare x int default 1; - select x as y, x+0 as z; -end| - -call bug8540()| -drop procedure bug8540| - -# -# BUG#6642: Stored procedure crash if expression with set function -# -create table t3 (s1 int)| - ---disable_warnings -drop procedure if exists bug6642| ---enable_warnings - -create procedure bug6642() - select abs(count(s1)) from t3| - -call bug6642()| -call bug6642()| -drop procedure bug6642| - -# -# BUG#7013: Stored procedure crash if group by ... with rollup -# -insert into t3 values (0),(1)| ---disable_warnings -drop procedure if exists bug7013| ---enable_warnings -create procedure bug7013() - select s1,count(s1) from t3 group by s1 with rollup| -call bug7013()| -call bug7013()| -drop procedure bug7013| - -# -# BUG#7743: 'Lost connection to MySQL server during query' on Stored Procedure -# ---disable_warnings -drop table if exists t4| ---enable_warnings -create table t4 ( - a mediumint(8) unsigned not null auto_increment, - b smallint(5) unsigned not null, - c char(32) not null, - primary key (a) -) engine=myisam default charset=latin1| -insert into t4 values (1, 2, 'oneword')| -insert into t4 values (2, 2, 'anotherword')| - ---disable_warnings -drop procedure if exists bug7743| ---enable_warnings -create procedure bug7743 ( searchstring char(28) ) -begin - declare var mediumint(8) unsigned; - select a into var from t4 where b = 2 and c = binary searchstring limit 1; - select var; -end| - -call bug7743("oneword")| -call bug7743("OneWord")| -call bug7743("anotherword")| -call bug7743("AnotherWord")| -drop procedure bug7743| -drop table t4| - -# -# BUG#7992: SELECT .. INTO variable .. within Stored Procedure crashes -# the server -# -delete from t3| -insert into t3 values(1)| -drop procedure if exists bug7992_1| -drop procedure if exists bug7992_2| -create procedure bug7992_1() -begin - declare i int; - select max(s1)+1 into i from t3; -end| -create procedure bug7992_2() - insert into t3 (s1) select max(t4.s1)+1 from t3 as t4| - -call bug7992_1()| -call bug7992_1()| -call bug7992_2()| -call bug7992_2()| - -drop procedure bug7992_1| -drop procedure bug7992_2| -drop table t3| - -# -# BUG#8116: calling simple stored procedure twice in a row results -# in server crash -# -create table t3 ( userid bigint(20) not null default 0 )| - ---disable_warnings -drop procedure if exists bug8116| ---enable_warnings -create procedure bug8116(in _userid int) - select * from t3 where userid = _userid| - -call bug8116(42)| -call bug8116(42)| -drop procedure bug8116| -drop table t3| - -# -# BUG#6857: current_time() in STORED PROCEDURES -# ---disable_warnings -drop procedure if exists bug6857| ---enable_warnings -create procedure bug6857(counter int) -begin - declare t0, t1 int; - declare plus bool default 0; - - set t0 = current_time(); - while counter > 0 do - set counter = counter - 1; - end while; - set t1 = current_time(); - if t1 > t0 then - set plus = 1; - end if; - select plus; -end| - -# QQ: This is currently disabled. Not only does it slow down a normal test -# run, it makes running with valgrind (or similar tools) extremely -# painful. -# Make sure this takes at least one second on all machines in all builds. -# 30000 makes it about 3 seconds on an old 1.1GHz linux. -#call bug6857(300000)| - -drop procedure bug6857| - -# -# BUG#8757: Stored Procedures: Scope of Begin and End Statements do not -# work properly. ---disable_warnings -drop procedure if exists bug8757| ---enable_warnings -create procedure bug8757() -begin - declare x int; - declare c1 cursor for select data from t1 limit 1; - - begin - declare y int; - declare c2 cursor for select i from t2 limit 1; - - open c2; - fetch c2 into y; - close c2; - select 2,y; - end; - open c1; - fetch c1 into x; - close c1; - select 1,x; -end| - -delete from t1| -delete from t2| -insert into t1 values ("x", 1)| -insert into t2 values ("y", 2, 0.0)| - -call bug8757()| - -delete from t1| -delete from t2| -drop procedure bug8757| - - -# -# BUG#8762: Stored Procedures: Inconsistent behavior -# of DROP PROCEDURE IF EXISTS statement. ---disable_warnings -drop procedure if exists bug8762| ---enable_warnings -# Doesn't exist -drop procedure if exists bug8762; create procedure bug8762() begin end| -# Does exist -drop procedure if exists bug8762; create procedure bug8762() begin end| -drop procedure bug8762| - - -# -# BUG#5240: Stored procedure crash if function has cursor declaration -# ---disable_warnings -drop function if exists bug5240| ---enable_warnings -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| -drop function bug5240| - -# -# BUG#5278: Stored procedure packets out of order if SET PASSWORD. -# ---disable_warnings -drop function if exists bug5278| ---enable_warnings -create function bug5278 () returns char -begin - SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass'); - return 'okay'; -end| - ---error 1133 -select bug5278()| ---error 1133 -select bug5278()| -drop function bug5278| - -# -# BUG#7992: rolling back temporary Item tree changes in SP -# ---disable_warnings -drop procedure if exists p1| ---enable_warnings -create table t3(id int)| -insert into t3 values(1)| -create procedure bug7992() -begin - declare i int; - select max(id)+1 into i from t3; -end| - -call bug7992()| -call bug7992()| -drop procedure bug7992| -drop table t3| -delimiter ;| - -# -# BUG#8849: problem with insert statement with table alias's -# -# Rolling back changes to AND/OR structure of ON and WHERE clauses in SP -# - -delimiter |; -create table t3 ( - lpitnumber int(11) default null, - lrecordtype int(11) default null -)| - -create table t4 ( - lbsiid int(11) not null default '0', - ltradingmodeid int(11) not null default '0', - ltradingareaid int(11) not null default '0', - csellingprice decimal(19,4) default null, - primary key (lbsiid,ltradingmodeid,ltradingareaid) -)| - -create table t5 ( - lbsiid int(11) not null default '0', - ltradingareaid int(11) not null default '0', - primary key (lbsiid,ltradingareaid) -)| - ---disable_warnings -drop procedure if exists bug8849| ---enable_warnings -create procedure bug8849() -begin - insert into t5 - ( - t5.lbsiid, - t5.ltradingareaid - ) - select distinct t3.lpitnumber, t4.ltradingareaid - from - t4 join t3 on - t3.lpitnumber = t4.lbsiid - and t3.lrecordtype = 1 - left join t4 as price01 on - price01.lbsiid = t4.lbsiid and - price01.ltradingmodeid = 1 and - t4.ltradingareaid = price01.ltradingareaid; -end| - -call bug8849()| -call bug8849()| -call bug8849()| -drop procedure bug8849| -drop tables t3,t4,t5| - -# -# BUG#8937: Stored Procedure: AVG() works as SUM() in SELECT ... INTO statement -# ---disable_warnings -drop procedure if exists bug8937| ---enable_warnings -create procedure bug8937() -begin - declare s,x,y,z int; - declare a float; - - select sum(data),avg(data),min(data),max(data) into s,x,y,z from t1; - select s,x,y,z; - select avg(data) into a from t1; - select a; -end| - -delete from t1| -insert into t1 (data) values (1), (2), (3), (4), (6)| -call bug8937()| - -drop procedure bug8937| -delete from t1| - - -# -# BUG#6900: Stored procedure inner handler ignored -# BUG#9074: STORED PROC: The scope of every handler declared is not -# properly applied -# ---disable_warnings -drop procedure if exists bug6900| -drop procedure if exists bug9074| -drop procedure if exists bug6900_9074| ---enable_warnings - -create table t3 (w char unique, x char)| -insert into t3 values ('a', 'b')| - -create procedure bug6900() -begin - declare exit handler for sqlexception select '1'; - - begin - declare exit handler for sqlexception select '2'; - - insert into t3 values ('x', 'y', 'z'); - end; -end| - -create procedure bug9074() -begin - declare x1, x2, x3, x4, x5, x6 int default 0; - - begin - declare continue handler for sqlstate '23000' set x5 = 1; - - insert into t3 values ('a', 'b'); - set x6 = 1; - end; - - begin1_label: - begin - declare continue handler for sqlstate '23000' set x1 = 1; - - insert into t3 values ('a', 'b'); - set x2 = 1; - - begin2_label: - begin - declare exit handler for sqlstate '23000' set x3 = 1; - - set x4= 1; - insert into t3 values ('a','b'); - set x4= 0; - end begin2_label; - end begin1_label; - - select x1, x2, x3, x4, x5, x6; -end| - -create procedure bug6900_9074(z int) -begin - declare exit handler for sqlstate '23000' select '23000'; - - begin - declare exit handler for sqlexception select 'sqlexception'; - - if z = 1 then - insert into t3 values ('a', 'b'); - else - insert into t3 values ('x', 'y', 'z'); - end if; - end; -end| - -call bug6900()| -call bug9074()| -call bug6900_9074(0)| -call bug6900_9074(1)| - -drop procedure bug6900| -drop procedure bug9074| -drop procedure bug6900_9074| -drop table t3| - - -# -# BUG#7185: Stored procedure crash if identifier is AVG -# ---disable_warnings -drop procedure if exists avg| ---enable_warnings -create procedure avg () -begin -end| - -call avg ()| -drop procedure avg| - - -# -# BUG#6129: Stored procedure won't display @@sql_mode value -# ---disable_warnings -drop procedure if exists bug6129| ---enable_warnings -set @old_mode= @@sql_mode; -set @@sql_mode= "ERROR_FOR_DIVISION_BY_ZERO"; -create procedure bug6129() - select @@sql_mode| -call bug6129()| -set @@sql_mode= "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO"| -call bug6129()| -set @@sql_mode= "NO_ZERO_IN_DATE"| -call bug6129()| -set @@sql_mode=@old_mode; - -drop procedure bug6129| - - -# -# BUG#9856: Stored procedures: crash if handler for sqlexception, not found -# ---disable_warnings -drop procedure if exists bug9856| ---enable_warnings -create procedure bug9856() -begin - declare v int; - declare c cursor for select data from t1; - declare exit handler for sqlexception, not found select '16'; - - open c; - fetch c into v; - select v; -end| - -delete from t1| -call bug9856()| -call bug9856()| -drop procedure bug9856| - - -# -# BUG##9674: Stored Procs: Using declared vars in algebric operation causes -# system crash. -# ---disable_warnings -drop procedure if exists bug9674_1| -drop procedure if exists bug9674_2| ---enable_warnings -create procedure bug9674_1(out arg int) -begin - declare temp_in1 int default 0; - declare temp_fl1 int default 0; - - set temp_in1 = 100; - set temp_fl1 = temp_in1/10; - set arg = temp_fl1; -end| - -create procedure bug9674_2() -begin - declare v int default 100; - - select v/10; -end| - -call bug9674_1(@sptmp)| -call bug9674_1(@sptmp)| -select @sptmp| -call bug9674_2()| -call bug9674_2()| -drop procedure bug9674_1| -drop procedure bug9674_2| - - -# -# BUG#9598: stored procedure call within stored procedure overwrites IN variable -# ---disable_warnings -drop procedure if exists bug9598_1| -drop procedure if exists bug9598_2| ---enable_warnings -create procedure bug9598_1(in var_1 char(16), - out var_2 integer, out var_3 integer) -begin - set var_2 = 50; - set var_3 = 60; -end| - -create procedure bug9598_2(in v1 char(16), - in v2 integer, - in v3 integer, - in v4 integer, - in v5 integer) -begin - select v1,v2,v3,v4,v5; - call bug9598_1(v1,@tmp1,@tmp2); - select v1,v2,v3,v4,v5; -end| - -call bug9598_2('Test',2,3,4,5)| -select @tmp1, @tmp2| - -drop procedure bug9598_1| -drop procedure bug9598_2| - - -# -# BUG#9902: Crash with simple stored function using user defined variables -# ---disable_warnings -drop procedure if exists bug9902| ---enable_warnings -create function bug9902() returns int(11) -begin - set @x = @x + 1; - return @x; -end| - -set @qcs1 = @@query_cache_size| -set global query_cache_size = 100000| -set @x = 1| -insert into t1 values ("qc", 42)| -select bug9902() from t1| -select bug9902() from t1| -select @x| - -set global query_cache_size = @qcs1| -delete from t1| -drop function bug9902| - - -# -# BUG#9102: Stored proccedures: function which returns blob causes crash -# ---disable_warnings -drop function if exists bug9102| ---enable_warnings -create function bug9102() returns blob return 'a'| -select bug9102()| -drop function bug9102| - - -# -# BUG#7648: Stored procedure crash when invoking a function that returns a bit -# ---disable_warnings -drop function if exists bug7648| ---enable_warnings -create function bug7648() returns bit(8) return 'a'| -select bug7648()| -drop function bug7648| - - -# -# BUG#9775: crash if create function that returns enum or set -# ---disable_warnings -drop function if exists bug9775| ---enable_warnings -create function bug9775(v1 char(1)) returns enum('a','b') return v1| -select bug9775('a'),bug9775('b'),bug9775('c')| -drop function bug9775| -create function bug9775(v1 int) returns enum('a','b') return v1| -select bug9775(1),bug9775(2),bug9775(3)| -drop function bug9775| - -create function bug9775(v1 char(1)) returns set('a','b') return v1| -select bug9775('a'),bug9775('b'),bug9775('a,b'),bug9775('c')| -drop function bug9775| -create function bug9775(v1 int) returns set('a','b') return v1| -select bug9775(1),bug9775(2),bug9775(3),bug9775(4)| -drop function bug9775| - - -# -# BUG#8861: If Return is a YEAR data type, value is not shown in year format -# ---disable_warnings -drop function if exists bug8861| ---enable_warnings -create function bug8861(v1 int) returns year return v1| -select bug8861(05)| -set @x = bug8861(05)| -select @x| -drop function bug8861| - - -# -# BUG#9004: Inconsistent behaviour of SP re. warnings -# ---disable_warnings -drop procedure if exists bug9004_1| -drop procedure if exists bug9004_2| ---enable_warnings -create procedure bug9004_1(x char(16)) -begin - insert into t1 values (x, 42); - insert into t1 values (x, 17); -end| -create procedure bug9004_2(x char(16)) - call bug9004_1(x)| - -# Truncation warnings expected... -call bug9004_1('12345678901234567')| -call bug9004_2('12345678901234567890')| - -delete from t1| -drop procedure bug9004_1| -drop procedure bug9004_2| - -# -# BUG#7293: Stored procedure crash with soundex -# ---disable_warnings -drop procedure if exists bug7293| ---enable_warnings -insert into t1 values ('secret', 0)| -create procedure bug7293(p1 varchar(100)) -begin - if exists (select id from t1 where soundex(p1)=soundex(id)) then - select 'yes'; - end if; -end;| -call bug7293('secret')| -call bug7293 ('secrete')| -drop procedure bug7293| -delete from t1| - - -# -# BUG#9841: Unexpected read lock when trying to update a view in a -# stored procedure -# ---disable_warnings -drop procedure if exists bug9841| -drop view if exists v1| ---enable_warnings - -create view v1 as select * from t1, t2 where id = s| -create procedure bug9841 () - update v1 set data = 10| -call bug9841()| - -drop view v1| -drop procedure bug9841| - - -# -# BUG#5963 subqueries in SET/IF -# ---disable_warnings -drop procedure if exists bug5963| ---enable_warnings - -create procedure bug5963_1 () begin declare v int; set v = (select s1 from t3); select v; end;| -create table t3 (s1 int)| -insert into t3 values (5)| -call bug5963_1()| -call bug5963_1()| -drop procedure bug5963_1| -drop table t3| - -create procedure bug5963_2 (cfk_value int) -begin - if cfk_value in (select cpk from t3) then - set @x = 5; - end if; - end; -| -create table t3 (cpk int)| -insert into t3 values (1)| -call bug5963_2(1)| -call bug5963_2(1)| -drop procedure bug5963_2| -drop table t3| - - -# -# BUG#9559: Functions: Numeric Operations using -ve value gives incorrect -# results. -# ---disable_warnings -drop function if exists bug9559| ---enable_warnings -create function bug9559() - returns int -begin - set @y = -6/2; - return @y; -end| - -select bug9559()| - -drop function bug9559| - - -# -# BUG#10961: Stored procedures: crash if select * from dual -# ---disable_warnings -drop procedure if exists bug10961| ---enable_warnings -# "select * from dual" results in an error, so the cursor will not open -create procedure bug10961() -begin - declare v char; - declare x int; - declare c cursor for select * from dual; - declare continue handler for sqlexception select x; - - set x = 1; - open c; - set x = 2; - fetch c into v; - set x = 3; - close c; -end| - -call bug10961()| -call bug10961()| - -drop procedure bug10961| - -# -# BUG #6866: Second call of a stored procedure using a view with on expressions -# - ---disable_warnings -DROP PROCEDURE IF EXISTS bug6866| ---enable_warnings - -DROP VIEW IF EXISTS tv| -DROP TABLE IF EXISTS tt1,tt2,tt3| - -CREATE TABLE tt1 (a1 int, a2 int, a3 int, data varchar(10))| -CREATE TABLE tt2 (a2 int, data2 varchar(10))| -CREATE TABLE tt3 (a3 int, data3 varchar(10))| - -INSERT INTO tt1 VALUES (1, 1, 4, 'xx')| - -INSERT INTO tt2 VALUES (1, 'a')| -INSERT INTO tt2 VALUES (2, 'b')| -INSERT INTO tt2 VALUES (3, 'c')| - -INSERT INTO tt3 VALUES (4, 'd')| -INSERT INTO tt3 VALUES (5, 'e')| -INSERT INTO tt3 VALUES (6, 'f')| - -CREATE VIEW tv AS -SELECT tt1.*, tt2.data2, tt3.data3 - FROM tt1 INNER JOIN tt2 ON tt1.a2 = tt2.a2 - LEFT JOIN tt3 ON tt1.a3 = tt3.a3 - ORDER BY tt1.a1, tt2.a2, tt3.a3| - -CREATE PROCEDURE bug6866 (_a1 int) -BEGIN -SELECT * FROM tv WHERE a1 = _a1; -END| - -CALL bug6866(1)| -CALL bug6866(1)| -CALL bug6866(1)| - -DROP PROCEDURE bug6866; - -DROP VIEW tv| -DROP TABLE tt1, tt2, tt3| - -# -# BUG#10136: items cleunup -# ---disable_warnings -DROP PROCEDURE IF EXISTS bug10136| ---enable_warnings -create table t3 ( name char(5) not null primary key, val float not null)| -insert into t3 values ('aaaaa', 1), ('bbbbb', 2), ('ccccc', 3)| -create procedure bug10136() -begin - declare done int default 3; - - repeat - select * from t3; - set done = done - 1; - until done <= 0 end repeat; - -end| -call bug10136()| -call bug10136()| -call bug10136()| -drop procedure bug10136| -drop table t3| - -# -# BUG#11529: crash server after use stored procedure -# ---disable_warnings -drop procedure if exists bug11529| ---enable_warnings -create procedure bug11529() -begin - declare c cursor for select id, data from t1 where data in (10,13); - - open c; - begin - declare vid char(16); - declare vdata int; - declare exit handler for not found begin end; - - while true do - fetch c into vid, vdata; - end while; - end; - close c; -end| - -insert into t1 values - ('Name1', 10), - ('Name2', 11), - ('Name3', 12), - ('Name4', 13), - ('Name5', 14)| - -call bug11529()| -call bug11529()| -delete from t1| -drop procedure bug11529| - - -# -# BUG#6063: Stored procedure labels are subject to restrictions (partial) -# BUG#7088: Stored procedures: labels won't work if character set is utf8 -# ---disable_warnings -drop procedure if exists bug6063| -drop procedure if exists bug7088_1| -drop procedure if exists bug7088_2| ---enable_warnings - ---disable_parsing # temporarily disabled until Bar fixes BUG#11986 -create procedure bug6063() - lâbel: begin end| -call bug6063()| -# QQ Known bug: this will not show the label correctly. -show create procedure bug6063| - -set character set utf8| -create procedure bug7088_1() - label1: begin end label1| -create procedure bug7088_2() - läbel1: begin end| -call bug7088_1()| -call bug7088_2()| -set character set default| -show create procedure bug7088_1| -show create procedure bug7088_2| - -drop procedure bug6063| -drop procedure bug7088_1| -drop procedure bug7088_2| ---enable_parsing - -# -# BUG#9565: "Wrong locking in stored procedure if a sub-sequent procedure -# is called". -# ---disable_warnings -drop procedure if exists bug9565_sub| -drop procedure if exists bug9565| ---enable_warnings -create procedure bug9565_sub() -begin - select * from t1; -end| -create procedure bug9565() -begin - insert into t1 values ("one", 1); - call bug9565_sub(); -end| -call bug9565()| -delete from t1| -drop procedure bug9565_sub| -drop procedure bug9565| - - -# -# BUG#9538: SProc: Creation fails if we try to SET system variable -# using @@var_name in proc -# ---disable_warnings -drop procedure if exists bug9538| ---enable_warnings -create procedure bug9538() - set @@sort_buffer_size = 1000000| - -set @x = @@sort_buffer_size| -set @@sort_buffer_size = 2000000| -select @@sort_buffer_size| -call bug9538()| -select @@sort_buffer_size| -set @@sort_buffer_size = @x| - -drop procedure bug9538| - - -# -# BUG#8692: Cursor fetch of empty string -# ---disable_warnings -drop procedure if exists bug8692| ---enable_warnings -create table t3 (c1 varchar(5), c2 char(5), c3 enum('one','two'), c4 text, c5 blob, c6 char(5), c7 varchar(5))| -insert into t3 values ('', '', '', '', '', '', NULL)| - -create procedure bug8692() -begin - declare v1 VARCHAR(10); - declare v2 VARCHAR(10); - declare v3 VARCHAR(10); - declare v4 VARCHAR(10); - declare v5 VARCHAR(10); - declare v6 VARCHAR(10); - declare v7 VARCHAR(10); - declare c8692 cursor for select c1,c2,c3,c4,c5,c6,c7 from t3; - open c8692; - fetch c8692 into v1,v2,v3,v4,v5,v6,v7; - select v1, v2, v3, v4, v5, v6, v7; -end| - -call bug8692()| -drop procedure bug8692| -drop table t3| - -# -# Bug#10055 "Using stored function with information_schema causes empty -# result set" -# ---disable_warnings -drop function if exists bug10055| ---enable_warnings -create function bug10055(v char(255)) returns char(255) return lower(v)| -# This select should not crash server and should return all fields in t1 -select t.column_name, bug10055(t.column_name) -from information_schema.columns as t -where t.table_schema = 'test' and t.table_name = 't1'| -drop function bug10055| - -# -# Bug #12297 "SP crashes the server if data inserted inside a lon loop" -# The test for memleak bug, so actually there is no way to test it -# from the suite. The test below could be used to check SP memory -# consumption by passing large input parameter. -# - ---disable_warnings -drop procedure if exists bug12297| ---enable_warnings - -create procedure bug12297(lim int) -begin - set @x = 0; - repeat - insert into t1(id,data) - values('aa', @x); - set @x = @x + 1; - until @x >= lim - end repeat; -end| - -call bug12297(10)| -drop procedure bug12297| - -# -# Bug #11247 "Stored procedures: Function calls in long loops leak memory" -# One more memleak bug test. One could use this test to check that the memory -# isn't leaking by increasing the input value for p_bug11247. -# - ---disable_warnings -drop function if exists f_bug11247| -drop procedure if exists p_bug11247| ---enable_warnings - -create function f_bug11247(param int) - returns int -return param + 1| - -create procedure p_bug11247(lim int) -begin - declare v int default 0; - - while v < lim do - set v= f_bug11247(v); - end while; -end| - -call p_bug11247(10)| -drop function f_bug11247| -drop procedure p_bug11247| -# -# BUG#12168: "'DECLARE CONTINUE HANDLER FOR NOT FOUND ...' in conditional -# handled incorrectly" -# ---disable_warnings -drop procedure if exists bug12168| -drop table if exists t3, t4| ---enable_warnings - -create table t3 (a int)| -insert into t3 values (1),(2),(3),(4)| - -create table t4 (a int)| - -create procedure bug12168(arg1 char(1)) -begin - declare b, c integer; - if arg1 = 'a' then - begin - declare c1 cursor for select a from t3 where a % 2; - declare continue handler for not found set b = 1; - set b = 0; - open c1; - c1_repeat: repeat - fetch c1 into c; - if (b = 1) then - leave c1_repeat; - end if; - - insert into t4 values (c); - until b = 1 - end repeat; - end; - end if; - if arg1 = 'b' then - begin - declare c2 cursor for select a from t3 where not a % 2; - declare continue handler for not found set b = 1; - set b = 0; - open c2; - c2_repeat: repeat - fetch c2 into c; - if (b = 1) then - leave c2_repeat; - end if; - - insert into t4 values (c); - until b = 1 - end repeat; - end; - end if; -end| - -call bug12168('a')| -select * from t4| -truncate t4| -call bug12168('b')| -select * from t4| -truncate t4| -call bug12168('a')| -select * from t4| -truncate t4| -call bug12168('b')| -select * from t4| -truncate t4| -drop table t3, t4| -drop procedure if exists bug12168| - -# -# Bug #11333 "Stored Procedure: Memory blow up on repeated SELECT ... INTO -# query" -# One more memleak bug. Use the test to check memory consumption. -# - ---disable_warnings -drop table if exists t3| -drop procedure if exists bug11333| ---enable_warnings - -create table t3 (c1 char(128))| - -insert into t3 values - ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')| - - -create procedure bug11333(i int) -begin - declare tmp varchar(128); - set @x = 0; - repeat - select c1 into tmp from t3 - where c1 = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'; - set @x = @x + 1; - until @x >= i - end repeat; -end| - -call bug11333(10)| - -drop procedure bug11333| -drop table t3| - -# -# BUG#9048: Creating a function with char binary IN parameter fails -# ---disable_warnings -drop function if exists bug9048| ---enable_warnings -create function bug9048(f1 char binary) returns char binary -begin - set f1= concat( 'hello', f1 ); - return f1; -end| -drop function bug9048| - -# Bug #12849 Stored Procedure: Crash on procedure call with CHAR type -# 'INOUT' parameter -# - ---disable_warnings -drop procedure if exists bug12849_1| ---enable_warnings -create procedure bug12849_1(inout x char) select x into x| -set @var='a'| -call bug12849_1(@var)| -select @var| -drop procedure bug12849_1| - ---disable_warnings -drop procedure if exists bug12849_2| ---enable_warnings -create procedure bug12849_2(inout foo varchar(15)) -begin -select concat(foo, foo) INTO foo; -end| -set @var='abcd'| -call bug12849_2(@var)| -select @var| -drop procedure bug12849_2| - -# -# BUG#13133: Local variables in stored procedures are not initialized correctly. -# ---disable_warnings -drop procedure if exists bug131333| -drop function if exists bug131333| ---enable_warnings -create procedure bug131333() -begin - begin - declare a int; - - select a; - set a = 1; - select a; - end; - begin - declare b int; - - select b; - end; -end| - -create function bug131333() - returns int -begin - begin - declare a int; - - set a = 1; - end; - begin - declare b int; - - return b; - end; -end| - -call bug131333()| -select bug131333()| - -drop procedure bug131333| -drop function bug131333| - -# -# BUG#12379: PROCEDURE with HANDLER calling FUNCTION with error get -# strange result -# ---disable_warnings -drop function if exists bug12379| -drop procedure if exists bug12379_1| -drop procedure if exists bug12379_2| -drop procedure if exists bug12379_3| -drop table if exists t3| ---enable_warnings - -create table t3 (c1 char(1) primary key not null)| - -create function bug12379() - returns integer -begin - insert into t3 values('X'); - insert into t3 values('X'); - return 0; -end| - -create procedure bug12379_1() -begin - declare exit handler for sqlexception select 42; - - select bug12379(); -END| -create procedure bug12379_2() -begin - declare exit handler for sqlexception begin end; - - select bug12379(); -end| -create procedure bug12379_3() -begin - select bug12379(); -end| - ---error 1062 -select bug12379()| -select 1| -call bug12379_1()| -select 2| -call bug12379_2()| -select 3| ---error 1062 -call bug12379_3()| -select 4| - -drop function bug12379| -drop procedure bug12379_1| -drop procedure bug12379_2| -drop procedure bug12379_3| -drop table t3| - -# -# Bug #13124 Stored Procedure using SELECT INTO crashes server -# - ---disable_warnings -drop procedure if exists bug13124| ---enable_warnings -create procedure bug13124() -begin - declare y integer; - set @x=y; -end| -call bug13124()| -drop procedure bug13124| - -# -# Bug #12979 Stored procedures: crash if inout decimal parameter -# - -# check NULL inout parameters processing - ---disable_warnings -drop procedure if exists bug12979_1| ---enable_warnings -create procedure bug12979_1(inout d decimal(5)) set d = d / 2| -set @bug12979_user_var = NULL| -call bug12979_1(@bug12979_user_var)| -drop procedure bug12979_1| - -# check NULL local variables processing - ---disable_warnings -drop procedure if exists bug12979_2| ---enable_warnings -create procedure bug12979_2() -begin -declare internal_var decimal(5); -set internal_var= internal_var / 2; -select internal_var; -end| -call bug12979_2()| -drop procedure bug12979_2| - - -# -# BUG#6127: Stored procedure handlers within handlers don't work -# ---disable_warnings -drop table if exists t3| -drop procedure if exists bug6127| ---enable_warnings -create table t3 (s1 int unique)| - -set @sm=@@sql_mode| -set sql_mode='traditional'| - -create procedure bug6127() -begin - declare continue handler for sqlstate '23000' - begin - declare continue handler for sqlstate '22003' - insert into t3 values (0); - - insert into t3 values (1000000000000000); - end; - - insert into t3 values (1); - insert into t3 values (1); -end| - -call bug6127()| -select * from t3| ---error ER_DUP_ENTRY -call bug6127()| -select * from t3| -set sql_mode=@sm| -drop table t3| -drop procedure bug6127| - - -# -# BUG#12589: Assert when creating temp. table from decimal stored procedure -# variable -# ---disable_warnings -drop procedure if exists bug12589_1| -drop procedure if exists bug12589_2| -drop procedure if exists bug12589_3| ---enable_warnings -create procedure bug12589_1() -begin - declare spv1 decimal(3,3); - set spv1= 123.456; - - set spv1 = 'test'; - create temporary table tm1 as select spv1; - show create table tm1; - drop temporary table tm1; -end| - -create procedure bug12589_2() -begin - declare spv1 decimal(6,3); - set spv1= 123.456; - - create temporary table tm1 as select spv1; - show create table tm1; - drop temporary table tm1; -end| - -create procedure bug12589_3() -begin - declare spv1 decimal(6,3); - set spv1= -123.456; - - create temporary table tm1 as select spv1; - show create table tm1; - drop temporary table tm1; -end| - -# Note: The type of the field will match the value, not the declared -# type of the variable. (This is a type checking issue which -# might be changed later.) - -# Warning expected from "set spv1 = 'test'", the value is set to decimal "0". -call bug12589_1()| -# No warnings here -call bug12589_2()| -call bug12589_3()| -drop procedure bug12589_1| -drop procedure bug12589_2| -drop procedure bug12589_3| - -# -# BUG#7049: Stored procedure CALL errors are ignored -# ---disable_warnings -drop table if exists t3| -drop procedure if exists bug7049_1| -drop procedure if exists bug7049_2| -drop procedure if exists bug7049_3| -drop procedure if exists bug7049_4| -drop function if exists bug7049_1| -drop function if exists bug7049_2| ---enable_warnings - -create table t3 ( x int unique )| - -create procedure bug7049_1() -begin - insert into t3 values (42); - insert into t3 values (42); -end| - -create procedure bug7049_2() -begin - declare exit handler for sqlexception - select 'Caught it' as 'Result'; - - call bug7049_1(); - select 'Missed it' as 'Result'; -end| - -create procedure bug7049_3() - call bug7049_1()| - -create procedure bug7049_4() -begin - declare exit handler for sqlexception - select 'Caught it' as 'Result'; - - call bug7049_3(); - select 'Missed it' as 'Result'; -end| - -create function bug7049_1() - returns int -begin - insert into t3 values (42); - insert into t3 values (42); - return 42; -end| - -create function bug7049_2() - returns int -begin - declare x int default 0; - declare continue handler for sqlexception - set x = 1; - - set x = bug7049_1(); - return x; -end| - -call bug7049_2()| -select * from t3| -delete from t3| -call bug7049_4()| -select * from t3| -select bug7049_2()| - -drop table t3| -drop procedure bug7049_1| -drop procedure bug7049_2| -drop procedure bug7049_3| -drop procedure bug7049_4| -drop function bug7049_1| -drop function bug7049_2| - - -# -# BUG#13941: replace() string fuction behaves badly inside stored procedure -# (BUG#13914: IFNULL is returning garbage in stored procedure) -# ---disable_warnings -drop function if exists bug13941| -drop procedure if exists bug13941| ---enable_warnings - -create function bug13941(p_input_str text) - returns text -begin - declare p_output_str text; - - set p_output_str = p_input_str; - - set p_output_str = replace(p_output_str, 'xyzzy', 'plugh'); - set p_output_str = replace(p_output_str, 'test', 'prova'); - set p_output_str = replace(p_output_str, 'this', 'questo'); - set p_output_str = replace(p_output_str, ' a ', 'una '); - set p_output_str = replace(p_output_str, 'is', ''); - - return p_output_str; -end| - -create procedure bug13941(out sout varchar(128)) -begin - set sout = 'Local'; - set sout = ifnull(sout, 'DEF'); -end| - -# Note: The bug showed different behaviour in different types of builds, -# giving garbage results in some, and seemingly working in others. -# Running with valgrind (or purify) is the safe way to check that it's -# really working correctly. -select bug13941('this is a test')| -call bug13941(@a)| -select @a| - -drop function bug13941| -drop procedure bug13941| - - -# -# BUG#13095: Cannot create VIEWs in prepared statements -# - -delimiter ;| - ---disable_warnings -DROP PROCEDURE IF EXISTS bug13095; -DROP TABLE IF EXISTS bug13095_t1; -DROP VIEW IF EXISTS bug13095_v1; ---enable_warnings - -delimiter |; - -CREATE PROCEDURE bug13095(tbl_name varchar(32)) -BEGIN - SET @str = - CONCAT("CREATE TABLE ", tbl_name, "(stuff char(15))"); - SELECT @str; - PREPARE stmt FROM @str; - EXECUTE stmt; - - SET @str = - CONCAT("INSERT INTO ", tbl_name, " VALUES('row1'),('row2'),('row3')" ); - SELECT @str; - PREPARE stmt FROM @str; - EXECUTE stmt; - - SET @str = - CONCAT("CREATE VIEW bug13095_v1(c1) AS SELECT stuff FROM ", tbl_name); - SELECT @str; - PREPARE stmt FROM @str; - EXECUTE stmt; - - SELECT * FROM bug13095_v1; - - SET @str = - "DROP VIEW bug13095_v1"; - SELECT @str; - PREPARE stmt FROM @str; - EXECUTE stmt; -END| - -delimiter ;| - -CALL bug13095('bug13095_t1'); - ---disable_warnings -DROP PROCEDURE IF EXISTS bug13095; -DROP VIEW IF EXISTS bug13095_v1; -DROP TABLE IF EXISTS bug13095_t1; ---enable_warnings - -delimiter |; - -# -# BUG#14210: "Simple query with > operator on large table gives server -# crash" -# Check that cursors work in case when HEAP tables are converted to -# MyISAM -# ---disable_warnings -drop procedure if exists bug14210| ---enable_warnings -set @@session.max_heap_table_size=16384| -select @@session.max_heap_table_size| -# To trigger the memory corruption the original table must be InnoDB. -# No harm if it's not, so don't warn if the suite is run with --skip-innodb ---disable_warnings -create table t3 (a char(255)) engine=InnoDB| ---enable_warnings -create procedure bug14210_fill_table() -begin - declare table_size, max_table_size int default 0; - select @@session.max_heap_table_size into max_table_size; - delete from t3; - insert into t3 (a) values (repeat('a', 255)); - repeat - insert into t3 select a from t3; - select count(*)*255 from t3 into table_size; - until table_size > max_table_size*2 end repeat; -end| -call bug14210_fill_table()| -drop procedure bug14210_fill_table| -create table t4 like t3| - -create procedure bug14210() -begin - declare a char(255); - declare done int default 0; - declare c cursor for select * from t3; - declare continue handler for sqlstate '02000' set done = 1; - open c; - repeat - fetch c into a; - if not done then - insert into t4 values (upper(a)); - end if; - until done end repeat; - close c; -end| -call bug14210()| -select count(*) from t4| - -drop table t3, t4| -drop procedure bug14210| -set @@session.max_heap_table_size=default| - - -# -# BUG#1473: Dumping of stored functions seems to cause corruption in -# the function body -# ---disable_warnings -drop function if exists bug14723| -drop procedure if exists bug14723| ---enable_warnings - -delimiter ;;| -/*!50003 create function bug14723() - returns bigint(20) -main_loop: begin - return 42; -end */;; -show create function bug14723;; -select bug14723();; - -/*!50003 create procedure bug14723() -main_loop: begin - select 42; -end */;; -show create procedure bug14723;; -call bug14723();; - -delimiter |;; - -drop function bug14723| -drop procedure bug14723| - -# -# Bug#14845 "mysql_stmt_fetch returns MYSQL_NO_DATA when COUNT(*) is 0" -# Check that when fetching from a cursor, COUNT(*) works properly. -# -create procedure bug14845() -begin - declare a char(255); - declare done int default 0; - declare c cursor for select count(*) from t1 where 1 = 0; - declare continue handler for sqlstate '02000' set done = 1; - open c; - repeat - fetch c into a; - if not done then - select a; - end if; - until done end repeat; - close c; -end| -call bug14845()| -drop procedure bug14845| - -# -# BUG#13549 "Server crash with nested stored procedures". -# Server should not crash when during execution of stored procedure -# we have to parse trigger/function definition and this new trigger/ -# function has more local variables declared than invoking stored -# procedure and last of these variables is used in argument of NOT -# operator. -# ---disable_warnings -drop procedure if exists bug13549_1| -drop procedure if exists bug13549_2| ---enable_warnings -CREATE PROCEDURE `bug13549_2`() -begin - call bug13549_1(); -end| -CREATE PROCEDURE `bug13549_1`() -begin - declare done int default 0; - set done= not done; -end| -CALL bug13549_2()| -drop procedure bug13549_2| -drop procedure bug13549_1| - -# -# BUG#10100: function (and stored procedure?) recursivity problem -# ---disable_warnings -drop function if exists bug10100f| -drop procedure if exists bug10100p| -drop procedure if exists bug10100t| -drop procedure if exists bug10100pt| -drop procedure if exists bug10100pv| -drop procedure if exists bug10100pd| -drop procedure if exists bug10100pc| ---enable_warnings -# routines with simple recursion -create function bug10100f(prm int) returns int -begin - if prm > 1 then - return prm * bug10100f(prm - 1); - end if; - return 1; -end| -create procedure bug10100p(prm int, inout res int) -begin - set res = res * prm; - if prm > 1 then - call bug10100p(prm - 1, res); - end if; -end| -create procedure bug10100t(prm int) -begin - declare res int; - set res = 1; - call bug10100p(prm, res); - select res; -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 procedure bug10100pt(level int, lim int) -begin - if level < lim then - update t3 set a=level; - FLUSH TABLES; - call bug10100pt(level+1, lim); - else - select * from t3; - end if; -end| -# view & recursion -create procedure bug10100pv(level int, lim int) -begin - if level < lim then - update v1 set a=level; - FLUSH TABLES; - call bug10100pv(level+1, lim); - else - select * from v1; - end if; -end| -# dynamic sql & recursion -prepare stmt2 from "select * from t3;"; -create procedure bug10100pd(level int, lim int) -begin - if level < lim then - select level; - prepare stmt1 from "update t3 set a=a+2"; - execute stmt1; - FLUSH TABLES; - execute stmt1; - FLUSH TABLES; - execute stmt1; - FLUSH TABLES; - deallocate prepare stmt1; - execute stmt2; - select * from t3; - call bug10100pd(level+1, lim); - else - execute stmt2; - end if; -end| -# cursor & recursion -create procedure bug10100pc(level int, lim int) -begin - declare lv int; - declare c cursor for select a from t3; - open c; - if level < lim then - select level; - fetch c into lv; - select lv; - update t3 set a=level+lv; - FLUSH TABLES; - call bug10100pc(level+1, lim); - else - select * from t3; - end if; - close c; -end| - -set @@max_sp_recursion_depth=4| -select @@max_sp_recursion_depth| --- error ER_SP_NO_RECURSION -select bug10100f(3)| --- error ER_SP_NO_RECURSION -select bug10100f(6)| -call bug10100t(5)| -call bug10100pt(1,5)| -call bug10100pv(1,5)| -update t3 set a=1| -call bug10100pd(1,5)| -select * from t3| -update t3 set a=1| -call bug10100pc(1,5)| -select * from t3| -set @@max_sp_recursion_depth=0| -select @@max_sp_recursion_depth| --- error ER_SP_NO_RECURSION -select bug10100f(5)| --- error ER_SP_RECURSION_LIMIT -call bug10100t(5)| - -#end of the stack checking -set @@max_sp_recursion_depth=255| -set @var=1| -#disable log because error about stack overrun contains numbers which -#depend on a system --- disable_result_log --- error ER_STACK_OVERRUN_NEED_MORE -call bug10100p(255, @var)| --- error ER_STACK_OVERRUN_NEED_MORE -call bug10100pt(1,255)| --- error ER_STACK_OVERRUN_NEED_MORE -call bug10100pv(1,255)| --- error ER_STACK_OVERRUN_NEED_MORE -call bug10100pd(1,255)| --- error ER_STACK_OVERRUN_NEED_MORE -call bug10100pc(1,255)| --- enable_result_log -set @@max_sp_recursion_depth=0| - -deallocate prepare stmt2| - -drop function bug10100f| -drop procedure bug10100p| -drop procedure bug10100t| -drop procedure bug10100pt| -drop procedure bug10100pv| -drop procedure bug10100pd| -drop procedure bug10100pc| -drop view v1| - -# -# BUG#13729: Stored procedures: packet error after exception handled -# ---disable_warnings -drop procedure if exists bug13729| -drop table if exists t3| ---enable_warnings - -create table t3 (s1 int, primary key (s1))| - -insert into t3 values (1),(2)| - -create procedure bug13729() -begin - declare continue handler for sqlexception select 55; - - update t3 set s1 = 1; -end| - -call bug13729()| -# Used to cause Packets out of order -select * from t3| - -drop procedure bug13729| -drop table t3| - -# -# BUG#14643: Stored Procedure: Continuing after failed var. initialization -# crashes server. -# ---disable_warnings -drop procedure if exists bug14643_1| -drop procedure if exists bug14643_2| ---enable_warnings - -create procedure bug14643_1() -begin - declare continue handler for sqlexception select 'boo' as 'Handler'; - - begin - declare v int default undefined_var; - - if v = 1 then - select 1; - else - select v, isnull(v); - end if; - end; -end| - -create procedure bug14643_2() -begin - declare continue handler for sqlexception select 'boo' as 'Handler'; - - case undefined_var - when 1 then - select 1; - else - select 2; - end case; - - select undefined_var; -end| - -call bug14643_1()| -call bug14643_2()| - -drop procedure bug14643_1| -drop procedure bug14643_2| - -# -# BUG#14304: auto_increment field incorrect set in SP -# ---disable_warnings -drop procedure if exists bug14304| -drop table if exists t3, t4| ---enable_warnings - -create table t3(a int primary key auto_increment)| -create table t4(a int primary key auto_increment)| - -create procedure bug14304() -begin - insert into t3 set a=null; - insert into t4 set a=null; - insert into t4 set a=null; - insert into t4 set a=null; - insert into t4 set a=null; - insert into t4 set a=null; - insert into t4 select null as a; - - insert into t3 set a=null; - insert into t3 set a=null; - - select * from t3; -end| - -call bug14304()| - -drop procedure bug14304| -drop table t3, t4| - -# -# BUG#14376: MySQL crash on scoped variable (re)initialization -# ---disable_warnings -drop procedure if exists bug14376| ---enable_warnings - -create procedure bug14376() -begin - declare x int default x; -end| - -# Not the error we want, but that's what we got for now... ---error ER_BAD_FIELD_ERROR -call bug14376()| -drop procedure bug14376| - -create procedure bug14376() -begin - declare x int default 42; - - begin - declare x int default x; - - select x; - end; -end| - -call bug14376()| - -drop procedure bug14376| - -create procedure bug14376(x int) -begin - declare x int default x; - - select x; -end| - -call bug14376(4711)| - -drop procedure bug14376| - -# -# Bug#5967 "Stored procedure declared variable used instead of column" -# The bug should be fixed later. -# Test precedence of names of parameters, variable declarations, -# variable declarations in nested compound statements, table columns, -# table columns in cursor declarations. -# According to the standard, table columns take precedence over -# variable declarations. In MySQL 5.0 it's vice versa. -# - ---disable_warnings -drop procedure if exists bug5967| -drop table if exists t3| ---enable_warnings -create table t3 (a varchar(255))| -insert into t3 (a) values ("a - table column")| -create procedure bug5967(a varchar(255)) -begin - declare i varchar(255); - declare c cursor for select a from t3; - select a; - select a from t3 into i; - select i as 'Parameter takes precedence over table column'; open c; - fetch c into i; - close c; - select i as 'Parameter takes precedence over table column in cursors'; - begin - declare a varchar(255) default 'a - local variable'; - declare c1 cursor for select a from t3; - select a as 'A local variable takes precedence over parameter'; - open c1; - fetch c1 into i; - close c1; - select i as 'A local variable takes precedence over parameter in cursors'; - begin - declare a varchar(255) default 'a - local variable in a nested compound statement'; - declare c2 cursor for select a from t3; - select a as 'A local variable in a nested compound statement takes precedence over a local variable in the outer statement'; - select a from t3 into i; - select i as 'A local variable in a nested compound statement takes precedence over table column'; - open c2; - fetch c2 into i; - close c2; - select i as 'A local variable in a nested compound statement takes precedence over table column in cursors'; - end; - end; -end| -call bug5967("a - stored procedure parameter")| -drop procedure bug5967| - -# -# Bug#13012 "SP: REPAIR/BACKUP/RESTORE TABLE crashes the server" -# ---disable_warnings -drop procedure if exists bug13012| ---enable_warnings -create procedure bug13012() -BEGIN - REPAIR TABLE t1; - BACKUP TABLE t1 to '../tmp'; - DROP TABLE t1; - RESTORE TABLE t1 FROM '../tmp'; -END| -call bug13012()| -drop procedure bug13012| -create view v1 as select * from t1| -create procedure bug13012() -BEGIN - REPAIR TABLE t1,t2,t3,v1; - OPTIMIZE TABLE t1,t2,t3,v1; - ANALYZE TABLE t1,t2,t3,v1; -END| -call bug13012()| -call bug13012()| -call bug13012()| -drop procedure bug13012| -drop view v1; -select * from t1| - -# -# A test case for Bug#15392 "Server crashes during prepared statement -# execute": make sure that stored procedure check for error conditions -# properly and do not continue execution if an error has been set. -# -# It's necessary to use several DBs because in the original code -# the successful return of mysql_change_db overrode the error from -# execution. -drop schema if exists mysqltest1| -drop schema if exists mysqltest2| -drop schema if exists mysqltest3| -create schema mysqltest1| -create schema mysqltest2| -create schema mysqltest3| -use mysqltest3| - -create procedure mysqltest1.p1 (out prequestid varchar(100)) -begin - call mysqltest2.p2('call mysqltest3.p3(1, 2)'); -end| - -create procedure mysqltest2.p2(in psql text) -begin - declare lsql text; - set @lsql= psql; - prepare lstatement from @lsql; - execute lstatement; - deallocate prepare lstatement; -end| - -create procedure mysqltest3.p3(in p1 int) -begin - select p1; -end| - ---error ER_SP_WRONG_NO_OF_ARGS -call mysqltest1.p1(@rs)| ---error ER_SP_WRONG_NO_OF_ARGS -call mysqltest1.p1(@rs)| ---error ER_SP_WRONG_NO_OF_ARGS -call mysqltest1.p1(@rs)| -drop schema if exists mysqltest1| -drop schema if exists mysqltest2| -drop schema if exists mysqltest3| -use test| - -# -# Bug#15441 "Running SP causes Server to Crash": check that an SP variable -# can not be used in VALUES() function. -# ---disable_warnings -drop table if exists t3| -drop procedure if exists bug15441| ---enable_warnings -create table t3 (id int not null primary key, county varchar(25))| -insert into t3 (id, county) values (1, 'York')| - -# First check that a stored procedure that refers to a parameter in VALUES() -# function won't parse. - -create procedure bug15441(c varchar(25)) -begin - update t3 set id=2, county=values(c); -end| ---error ER_BAD_FIELD_ERROR -call bug15441('county')| -drop procedure bug15441| - -# Now check the case when there is an ambiguity between column names -# and stored procedure parameters: the parser shall resolve the argument -# of VALUES() function to the column name. - -# It's hard to deduce what county refers to in every case (INSERT statement): -# 1st county refers to the column -# 2nd county refers to the procedure parameter -# 3d and 4th county refers to the column, again, but -# for 4th county it has the value of SP parameter - -# In UPDATE statement, just check that values() function returns NULL for -# non- INSERT...UPDATE statements, as stated in the manual. - -create procedure bug15441(county varchar(25)) -begin - declare c varchar(25) default "hello"; - - insert into t3 (id, county) values (1, county) - on duplicate key update county= values(county); - select * from t3; - - update t3 set id=2, county=values(id); - select * from t3; -end| -call bug15441('Yale')| -drop table t3| -drop procedure bug15441| - -# -# BUG#14498: Stored procedures: hang if undefined variable and exception -# ---disable_warnings -drop procedure if exists bug14498_1| -drop procedure if exists bug14498_2| -drop procedure if exists bug14498_3| -drop procedure if exists bug14498_4| -drop procedure if exists bug14498_5| ---enable_warnings - -create procedure bug14498_1() -begin - declare continue handler for sqlexception select 'error' as 'Handler'; - - if v then - select 'yes' as 'v'; - else - select 'no' as 'v'; - end if; - select 'done' as 'End'; -end| - -create procedure bug14498_2() -begin - declare continue handler for sqlexception select 'error' as 'Handler'; - - while v do - select 'yes' as 'v'; - end while; - select 'done' as 'End'; -end| - -create procedure bug14498_3() -begin - declare continue handler for sqlexception select 'error' as 'Handler'; - - repeat - select 'maybe' as 'v'; - until v end repeat; - select 'done' as 'End'; -end| - -create procedure bug14498_4() -begin - declare continue handler for sqlexception select 'error' as 'Handler'; - - case v - when 1 then - select '1' as 'v'; - when 2 then - select '2' as 'v'; - else - select '?' as 'v'; - end case; - select 'done' as 'End'; -end| - -create procedure bug14498_5() -begin - declare continue handler for sqlexception select 'error' as 'Handler'; - - case - when v = 1 then - select '1' as 'v'; - when v = 2 then - select '2' as 'v'; - else - select '?' as 'v'; - end case; - select 'done' as 'End'; -end| - -call bug14498_1()| -call bug14498_2()| -call bug14498_3()| -call bug14498_4()| -call bug14498_5()| - -drop procedure bug14498_1| -drop procedure bug14498_2| -drop procedure bug14498_3| -drop procedure bug14498_4| -drop procedure bug14498_5| - -# -# BUG#15231: Stored procedure bug with not found condition handler -# ---disable_warnings -drop table if exists t3| -drop procedure if exists bug15231_1| -drop procedure if exists bug15231_2| -drop procedure if exists bug15231_3| -drop procedure if exists bug15231_4| ---enable_warnings - -create table t3 (id int not null)| - -create procedure bug15231_1() -begin - declare xid integer; - declare xdone integer default 0; - declare continue handler for not found set xdone = 1; - - set xid=null; - call bug15231_2(xid); - select xid, xdone; -end| - -create procedure bug15231_2(inout ioid integer) -begin - select "Before NOT FOUND condition is triggered" as '1'; - select id into ioid from t3 where id=ioid; - select "After NOT FOUND condtition is triggered" as '2'; - - if ioid is null then - set ioid=1; - end if; -end| - -create procedure bug15231_3() -begin - declare exit handler for sqlwarning - select 'Caught it (wrong)' as 'Result'; - - call bug15231_4(); -end| - -create procedure bug15231_4() -begin - declare x decimal(2,1); - - set x = 'zap'; - select 'Missed it (correct)' as 'Result'; -end| - -call bug15231_1()| -call bug15231_3()| - -drop table if exists t3| -drop procedure if exists bug15231_1| -drop procedure if exists bug15231_2| -drop procedure if exists bug15231_3| -drop procedure if exists bug15231_4| - - -# -# BUG#15011: error handler in nested block not activated -# ---disable_warnings -drop procedure if exists bug15011| ---enable_warnings - -create table t3 (c1 int primary key)| - -insert into t3 values (1)| - -create procedure bug15011() - deterministic -begin - declare continue handler for 1062 - select 'Outer' as 'Handler'; - - begin - declare continue handler for 1062 - select 'Inner' as 'Handler'; - - insert into t3 values (1); - end; -end| - -call bug15011()| - -drop procedure bug15011| -drop table t3| - - -# -# BUG#NNNN: New bug synopsis -# -#--disable_warnings -#drop procedure if exists bugNNNN| -#--enable_warnings -#create procedure bugNNNN... - -# Add bugs above this line. Use existing tables t1 and t2 when -# practical, or create table t3, t4 etc temporarily (and drop them). -delimiter ;| -drop table t1,t2; diff --git a/mysys/my_open.c b/mysys/my_open.c index 6e57132ae23..ab2f7c9ff27 100644 --- a/mysys/my_open.c +++ b/mysys/my_open.c @@ -334,7 +334,7 @@ File my_sopen(const char *path, int oflag, int shflag, int pmode) * try to open/create the file */ if ((osfh= CreateFile(path, fileaccess, fileshare, &SecurityAttributes, - filecreate, fileattrib, NULL)) == (HANDLE)0xffffffff) + filecreate, fileattrib, NULL)) == INVALID_HANDLE_VALUE) { /* * OS call to open/create file failed! map the error, release @@ -345,7 +345,7 @@ File my_sopen(const char *path, int oflag, int shflag, int pmode) return -1; /* return error to caller */ } - fh= _open_osfhandle((long)osfh, oflag & (_O_APPEND | _O_RDONLY | _O_TEXT)); + fh= _open_osfhandle((intptr_t)osfh, oflag & (_O_APPEND | _O_RDONLY | _O_TEXT)); return fh; /* return handle */ } diff --git a/sql/udf_example.c b/sql/udf_example.c index 62995085599..a80fce81278 100644 --- a/sql/udf_example.c +++ b/sql/udf_example.c @@ -806,6 +806,7 @@ char *reverse_lookup(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args, #if defined(HAVE_GETHOSTBYADDR_R) && defined(HAVE_SOLARIS_STYLE_GETHOST) char name_buff[256]; struct hostent tmp_hostent; + int tmp_errno; #endif struct hostent *hp; unsigned long taddr; @@ -845,7 +846,6 @@ char *reverse_lookup(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args, return 0; } #if defined(HAVE_GETHOSTBYADDR_R) && defined(HAVE_SOLARIS_STYLE_GETHOST) - int tmp_errno; if (!(hp=gethostbyaddr_r((char*) &taddr,sizeof(taddr), AF_INET, &tmp_hostent, name_buff,sizeof(name_buff), &tmp_errno))) |