diff options
| -rw-r--r-- | configure.in | 4 | ||||
| -rw-r--r-- | extra/yassl/taocrypt/include/asn.hpp | 8 | ||||
| -rw-r--r-- | extra/yassl/taocrypt/src/asn.cpp | 2 | ||||
| -rw-r--r-- | man/Makefile.am | 3 | ||||
| -rw-r--r-- | mysql-test/r/derived.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/func_time.result | 7 | ||||
| -rw-r--r-- | mysql-test/r/sp.result | 15 | ||||
| -rw-r--r-- | mysql-test/r/sp.result.orig | 4853 | ||||
| -rw-r--r-- | mysql-test/t/derived.test | 2 | ||||
| -rw-r--r-- | mysql-test/t/func_time.test | 10 | ||||
| -rw-r--r-- | mysql-test/t/mysqlbinlog.test | 10 | ||||
| -rw-r--r-- | mysql-test/t/sp.test | 29 | ||||
| -rw-r--r-- | mysql-test/t/udf.test | 2 | ||||
| -rw-r--r-- | scripts/make_binary_distribution.sh | 8 | ||||
| -rw-r--r-- | server-tools/instance-manager/mysqlmanager.vcproj | 2 | ||||
| -rw-r--r-- | sql/Makefile.am | 4 | ||||
| -rw-r--r-- | sql/field.cc | 2 | ||||
| -rw-r--r-- | sql/sp.cc | 1 | ||||
| -rw-r--r-- | sql/sql_class.cc | 1 | ||||
| -rw-r--r-- | sql/sql_class.h | 15 | ||||
| -rw-r--r-- | sql/sql_parse.cc | 7 | ||||
| -rw-r--r-- | sql/sql_select.cc | 20 | ||||
| -rw-r--r-- | sql/udf_example.c (renamed from sql/udf_example.cc) | 157 | ||||
| -rw-r--r-- | support-files/mysql.spec.sh | 2 | ||||
| -rw-r--r-- | tests/mysql_client_test.c | 46 |
25 files changed, 260 insertions, 4952 deletions
diff --git a/configure.in b/configure.in index 14a20322e69..26ac723c0bc 100644 --- a/configure.in +++ b/configure.in @@ -2316,12 +2316,16 @@ then man_dirs="man" man1_files=`ls -1 $srcdir/man/*.1 | sed -e 's;^.*man/;;'` man1_files=`echo $man1_files` + man8_files=`ls -1 $srcdir/man/*.8 | sed -e 's;^.*man/;;'` + man8_files=`echo $man8_files` else man_dirs="" man1_files="" + man8_files="" fi AC_SUBST(man_dirs) AC_SUBST(man1_files) +AC_SUBST(man8_files) # Don't build readline, i have it already AC_ARG_WITH(readline, diff --git a/extra/yassl/taocrypt/include/asn.hpp b/extra/yassl/taocrypt/include/asn.hpp index 90bc46a59fd..8bea2ae780b 100644 --- a/extra/yassl/taocrypt/include/asn.hpp +++ b/extra/yassl/taocrypt/include/asn.hpp @@ -103,7 +103,7 @@ enum Constants MAX_ALGO_SIZE = 9, MAX_DIGEST_SZ = 25, // SHA + enum(Bit or Octet) + length(4) DSA_SIG_SZ = 40, - NAME_MAX = 512 // max total of all included names + ASN_NAME_MAX = 512 // max total of all included names }; @@ -216,7 +216,7 @@ enum { SHA_SIZE = 20 }; // A Signing Authority class Signer { PublicKey key_; - char name_[NAME_MAX]; + char name_[ASN_NAME_MAX]; byte hash_[SHA_SIZE]; public: Signer(const byte* k, word32 kSz, const char* n, const byte* h); @@ -270,8 +270,8 @@ private: byte subjectHash_[SHA_SIZE]; // hash of all Names byte issuerHash_[SHA_SIZE]; // hash of all Names byte* signature_; - char issuer_[NAME_MAX]; // Names - char subject_[NAME_MAX]; // Names + char issuer_[ASN_NAME_MAX]; // Names + char subject_[ASN_NAME_MAX]; // Names char beforeDate_[MAX_DATE_SZ]; // valid before date char afterDate_[MAX_DATE_SZ]; // valid after date bool verify_; // Default to yes, but could be off diff --git a/extra/yassl/taocrypt/src/asn.cpp b/extra/yassl/taocrypt/src/asn.cpp index beb5490bb66..45fb1e60a0c 100644 --- a/extra/yassl/taocrypt/src/asn.cpp +++ b/extra/yassl/taocrypt/src/asn.cpp @@ -665,7 +665,7 @@ void CertDecoder::GetName(NameType nt) SHA sha; word32 length = GetSequence(); // length of all distinguished names - assert (length < NAME_MAX); + assert (length < ASN_NAME_MAX); length += source_.get_index(); char* ptr = (nt == ISSUER) ? issuer_ : subject_; diff --git a/man/Makefile.am b/man/Makefile.am index 9702c4b2ace..5753259fd3d 100644 --- a/man/Makefile.am +++ b/man/Makefile.am @@ -18,7 +18,8 @@ ## Process this file with automake to create Makefile.in man1_MANS = @man1_files@ -EXTRA_DIST = $(man1_MANS) +man8_MANS = @man8_files@ +EXTRA_DIST = $(man1_MANS) $(man8_MANS) # Don't update the files from bitkeeper %::SCCS/s.% diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 19325731d35..8c7e39e0e90 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -276,7 +276,7 @@ select * from t1; N M 3 0 delete P1.*,p2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS p2 ON P1.N = p2.N; -ERROR HY000: The target table p2 of the DELETE is not updatable +ERROR 42S02: Unknown table 'p2' in MULTI DELETE delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; ERROR 42S22: Unknown column 'aaa' in 'field list' drop table t1; diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index b6a3a6462ab..487d40f8a67 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -758,6 +758,13 @@ select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')), monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m')); monthname(str_to_date(null, '%m')) monthname(str_to_date(null, '%m')) monthname(str_to_date(1, '%m')) monthname(str_to_date(0, '%m')) NULL NULL January NULL +set time_zone='-6:00'; +create table t1(a timestamp); +insert into t1 values (19691231190001); +select * from t1; +a +1969-12-31 19:00:01 +drop table t1; create table t1(f1 date, f2 time, f3 datetime); insert into t1 values ("2006-01-01", "12:01:01", "2006-01-01 12:01:01"); insert into t1 values ("2006-01-02", "12:01:02", "2006-01-02 12:01:02"); diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index d9c4577e2b2..7b31744cba9 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -5051,6 +5051,21 @@ concat('data was: /', var1, '/') data was: /1/ drop table t3| drop procedure bug15217| +drop table if exists t3| +drop database if exists mysqltest1| +create table t3 (a int)| +insert into t3 (a) values (1), (2)| +create database mysqltest1| +use mysqltest1| +drop database mysqltest1| +select database()| +database() +NULL +select * from (select 1 as a) as t1 natural join (select * from test.t3) as t2| +a +1 +use test| +drop table t3| drop procedure if exists bug19862| CREATE TABLE t11 (a INT)| CREATE TABLE t12 (a INT)| diff --git a/mysql-test/r/sp.result.orig b/mysql-test/r/sp.result.orig deleted file mode 100644 index 663204681f2..00000000000 --- a/mysql-test/r/sp.result.orig +++ /dev/null @@ -1,4853 +0,0 @@ -use test; -drop table if exists t1,t2,t3,t4; -create table t1 ( -id char(16) not null default '', -data int not null -); -create table t2 ( -s char(16), -i int, -d double -); -drop procedure if exists foo42; -create procedure foo42() -insert into test.t1 values ("foo", 42); -call foo42(); -select * from t1; -id data -foo 42 -delete from t1; -drop procedure foo42; -drop procedure if exists bar; -create procedure bar(x char(16), y int) -insert into test.t1 values (x, y); -call bar("bar", 666); -select * from t1; -id data -bar 666 -delete from t1; -drop procedure if exists empty| -create procedure empty() -begin -end| -call empty()| -drop procedure empty| -drop procedure if exists scope| -create procedure scope(a int, b float) -begin -declare b int; -declare c float; -begin -declare c int; -end; -end| -drop procedure scope| -drop procedure if exists two| -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| -id data -one 3 -two 3 -delete from t1| -drop procedure two| -drop procedure if exists locset| -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| -id data -locset 21 -delete from t1| -drop procedure locset| -drop procedure if exists setcontext| -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| -id data -foo 1 -kaka 3 -delete from t1| -drop procedure setcontext| -create table t3 ( d date, i int, f double, s varchar(32) )| -drop procedure if exists nullset| -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| -d i f s -NULL NULL NULL NULL -NULL 1 1 ld is null -NULL 1 1 li is null -NULL NULL NULL li = 0 -NULL 1 1 lf is null -NULL NULL NULL lf = 0 -NULL 1 1 ls is null -drop table t3| -drop procedure nullset| -drop procedure if exists mixset| -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'| -Variable_name Value -max_join_size 100 -select id,data,@z from t1| -id data @z -mixset 666 19 -delete from t1| -drop procedure mixset| -drop procedure if exists zip| -create procedure zip(x char(16), y int) -begin -declare z int; -call zap(y, z); -call bar(x, z); -end| -drop procedure if exists zap| -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| -id data -zip 100 -delete from t1| -drop procedure zip| -drop procedure bar| -call zap(7, @zap)| -select @zap| -@zap -8 -drop procedure zap| -drop procedure if exists c1| -create procedure c1(x int) -call c2("c", x)| -drop procedure if exists c2| -create procedure c2(s char(16), x int) -call c3(x, s)| -drop procedure if exists c3| -create procedure c3(x int, s char(16)) -call c4("level", x, s)| -drop procedure if exists c4| -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| -id data -levelc 42 -delete from t1| -drop procedure c1| -drop procedure c2| -drop procedure c3| -drop procedure c4| -drop procedure if exists iotest| -create procedure iotest(x1 char(16), x2 char(16), y int) -begin -call inc2(x2, y); -insert into test.t1 values (x1, y); -end| -drop procedure if exists inc2| -create procedure inc2(x char(16), y int) -begin -call inc(y); -insert into test.t1 values (x, y); -end| -drop procedure if exists inc| -create procedure inc(inout io int) -set io = io + 1| -call iotest("io1", "io2", 1)| -select * from t1| -id data -io2 2 -io1 1 -delete from t1| -drop procedure iotest| -drop procedure inc2| -drop procedure if exists incr| -create procedure incr(inout x int) -call inc(x)| -select @zap| -@zap -8 -call incr(@zap)| -select @zap| -@zap -9 -drop procedure inc| -drop procedure incr| -drop procedure if exists cbv1| -create procedure cbv1() -begin -declare y int default 3; -call cbv2(y+1, y); -insert into test.t1 values ("cbv1", y); -end| -drop procedure if exists cbv2| -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| -id data -cbv2 4 -cbv1 4711 -delete from t1| -drop procedure cbv1| -drop procedure cbv2| -insert into t2 values ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3)| -drop procedure if exists sub1| -create procedure sub1(id char(16), x int) -insert into test.t1 values (id, x)| -drop procedure if exists sub2| -create procedure sub2(id char(16)) -begin -declare x int; -set x = (select sum(t.i) from test.t2 t); -insert into test.t1 values (id, x); -end| -drop procedure if exists sub3| -create function sub3(i int) returns int -return i+1| -call sub1("sub1a", (select 7))| -call sub1("sub1b", (select max(i) from t2))| -call sub1("sub1c", (select i,d from t2 limit 1))| -ERROR 21000: Operand should contain 1 column(s) -call sub1("sub1d", (select 1 from (select 1) a))| -call sub2("sub2")| -select * from t1| -id data -sub1a 7 -sub1b 3 -sub1d 1 -sub2 6 -select sub3((select max(i) from t2))| -sub3((select max(i) from t2)) -4 -drop procedure sub1| -drop procedure sub2| -drop function sub3| -delete from t1| -delete from t2| -drop procedure if exists a0| -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| -id data -a0 2 -a0 1 -a0 0 -delete from t1| -drop procedure a0| -drop procedure if exists a| -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| -id data -a 2 -a 1 -a 0 -delete from t1| -drop procedure a| -drop procedure if exists b| -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| -id data -bbb 3 -bbb 2 -bbb 1 -delete from t1| -drop procedure b| -drop procedure if exists b2| -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| -drop procedure b2| -drop procedure if exists c| -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| -id data -c 3 -c 2 -c 1 -delete from t1| -drop procedure c| -drop procedure if exists d| -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| -id data -d 3 -delete from t1| -drop procedure d| -drop procedure if exists e| -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| -id data -e 3 -e 2 -e 1 -delete from t1| -drop procedure e| -drop procedure if exists f| -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| -id data -f 0 -f 1 -f 2 -delete from t1| -drop procedure f| -drop procedure if exists g| -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| -id data -g 0 -g 1 -g 2 -delete from t1| -drop procedure g| -drop procedure if exists h| -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| -id data -h0 0 -h1 1 -h? 17 -delete from t1| -drop procedure h| -drop procedure if exists i| -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| -id data -i 3 -delete from t1| -drop procedure i| -insert into t1 values ("foo", 3), ("bar", 19)| -insert into t2 values ("x", 9, 4.1), ("y", -1, 19.2), ("z", 3, 2.2)| -drop procedure if exists sel1| -create procedure sel1() -begin -select * from t1; -end| -call sel1()| -id data -foo 3 -bar 19 -drop procedure sel1| -drop procedure if exists sel2| -create procedure sel2() -begin -select * from t1; -select * from t2; -end| -call sel2()| -id data -foo 3 -bar 19 -s i d -x 9 4.1 -y -1 19.2 -z 3 2.2 -drop procedure sel2| -delete from t1| -delete from t2| -drop procedure if exists into_test| -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| -id data -into 100 -into2 102 -delete from t1| -drop procedure into_test| -drop procedure if exists into_tes2| -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| -id data @z -into 100 100 -into2 102 100 -delete from t1| -drop procedure into_test2| -drop procedure if exists into_test3| -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)| -call into_test3()| -call into_test3()| -select * from t2| -s i d -into3 19 0 -into3 19 0 -delete from t1| -delete from t2| -drop procedure into_test3| -drop procedure if exists into_test4| -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()| -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed -select * from t3| -s d -into4 NULL -insert into t1 values ("i4", 77)| -call into_test4()| -select * from t3| -s d -into4 NULL -into4 77 -delete from t1| -drop table t3| -drop procedure into_test4| -drop procedure if exists into_outfile| -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| -call into_outfile("ofile", 1)| -delete from t1| -drop procedure into_outfile| -drop procedure if exists into_dumpfile| -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| -call into_dumpfile("dfile", 1)| -delete from t1| -drop procedure into_dumpfile| -drop procedure if exists create_select| -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| -id data id data -cs 90 cs 90 -cs 90 cs2 92 -drop table t3| -delete from t1| -drop procedure create_select| -drop function if exists e| -create function e() returns double -return 2.7182818284590452354| -set @e = e()| -select e(), @e| -e() @e -2.718281828459 2.718281828459 -drop function if exists inc| -create function inc(i int) returns int -return i+1| -select inc(1), inc(99), inc(-71)| -inc(1) inc(99) inc(-71) -2 100 -70 -drop function if exists mul| -create function mul(x int, y int) returns int -return x*y| -select mul(1,1), mul(3,5), mul(4711, 666)| -mul(1,1) mul(3,5) mul(4711, 666) -1 15 3137526 -drop function if exists append| -create function append(s1 char(8), s2 char(8)) returns char(16) -return concat(s1, s2)| -select append("foo", "bar")| -append("foo", "bar") -foobar -drop function if exists fac| -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)| -fac(1) fac(2) fac(5) fac(10) -1 2 120 3628800 -drop function if exists fun| -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)| -fun(2.3, 3, 5) -176.58213176229 -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))| -select * from t2 where s = append("a", "b")| -s i d -ab 24 1324.36598821719 -select * from t2 where i = mul(4,3) or i = mul(mul(3,4),2)| -s i d -xxxyyy 12 2.71828182845905 -ab 24 1324.36598821719 -select * from t2 where d = e()| -s i d -xxxyyy 12 2.71828182845905 -select * from t2| -s i d -xxxyyy 12 2.71828182845905 -ab 24 1324.36598821719 -delete from t2| -drop function e| -drop function inc| -drop function mul| -drop function append| -drop function fun| -drop procedure if exists hndlr1| -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| -id data -hndlr1 42 -delete from t1| -drop procedure hndlr1| -drop procedure if exists hndlr2| -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| -id data -hndlr2 1 -delete from t1| -drop procedure hndlr2| -drop procedure if exists hndlr3| -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| -id data -hndlr3 13 -delete from t1| -drop procedure hndlr3| -create table t3 ( id char(16), data int )| -drop procedure if exists hndlr4| -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| -id data -z NULL -drop table t3| -drop procedure hndlr4| -drop procedure if exists cur1| -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| -id data -foo 40 -bar 15 -zap 663 -drop procedure cur1| -create table t3 ( s char(16), i int )| -drop procedure if exists cur2| -create procedure cur2() -begin -declare done int default 0; -declare c1 cursor for select id,data from test.t1; -declare c2 cursor for select i from test.t2; -declare 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| -s i -foo 40 -bar 3 -zap 663 -delete from t1| -delete from t2| -drop table t3| -drop procedure cur2| -drop procedure if exists chistics| -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| -Procedure sql_mode Create Procedure -chistics CREATE PROCEDURE `chistics`() - MODIFIES SQL DATA - COMMENT 'Characteristics procedure test' -insert into t1 values ("chistics", 1) -call chistics()| -select * from t1| -id data -chistics 1 -delete from t1| -alter procedure chistics sql security invoker| -show create procedure chistics| -Procedure sql_mode Create Procedure -chistics CREATE PROCEDURE `chistics`() - MODIFIES SQL DATA - SQL SECURITY INVOKER - COMMENT 'Characteristics procedure test' -insert into t1 values ("chistics", 1) -drop procedure chistics| -drop function if exists chistics| -create function chistics() returns int -language sql -deterministic -sql security invoker -comment 'Characteristics procedure test' - return 42| -show create function chistics| -Function sql_mode Create Function -chistics CREATE FUNCTION `chistics`() RETURNS int(11) - DETERMINISTIC - SQL SECURITY INVOKER - COMMENT 'Characteristics procedure test' -return 42 -select chistics()| -chistics() -42 -alter function chistics -no sql -comment 'Characteristics function test'| -show create function chistics| -Function sql_mode Create Function -chistics CREATE FUNCTION `chistics`() RETURNS int(11) - NO SQL - DETERMINISTIC - SQL SECURITY INVOKER - COMMENT 'Characteristics function test' -return 42 -drop function chistics| -insert into t1 values ("foo", 1), ("bar", 2), ("zip", 3)| -set @@sql_mode = 'ANSI'| -drop procedure if exists modes$ -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$ -set @@sql_mode = ''| -set sql_select_limit = 1| -call modes(@c1, @c2)| -set sql_select_limit = default| -select @c1, @c2| -@c1 @c2 -12 3 -delete from t1| -drop procedure modes| -create database sp_db1| -drop database sp_db1| -create database sp_db2| -use sp_db2| -create table t3 ( s char(4), t int )| -insert into t3 values ("abcd", 42), ("dcba", 666)| -use test| -drop database sp_db2| -create database sp_db3| -use sp_db3| -drop procedure if exists dummy| -create procedure dummy(out x int) -set x = 42| -use test| -drop database sp_db3| -select type,db,name from mysql.proc where db = 'sp_db3'| -type db name -drop procedure if exists rc| -create procedure rc() -begin -delete from t1; -insert into t1 values ("a", 1), ("b", 2), ("c", 3); -end| -call rc()| -select row_count()| -row_count() -3 -update t1 set data=42 where id = "b"; -select row_count()| -row_count() -1 -delete from t1| -select row_count()| -row_count() -3 -delete from t1| -select row_count()| -row_count() -0 -select * from t1| -id data -select row_count()| -row_count() --1 -drop procedure rc| -drop function if exists f0| -drop function if exists f1| -drop function if exists f2| -drop function if exists f3| -drop function if exists f4| -drop function if exists f5| -drop function if exists f6| -drop function if exists f7| -drop function if exists f8| -drop 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| -delete from t1| -delete from t2| -insert into t1 values ("a", 1), ("b", 2) | -insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) | -create function f1() returns int -return (select sum(data) from t1)| -select f1()| -f1() -3 -select id, f1() from t1| -id f1() -a 3 -b 3 -create function f2() returns int -return (select data from t1 where data <= (select sum(data) from t1) limit 1)| -select f2()| -f2() -1 -select id, f2() from t1| -id f2() -a 1 -b 1 -create function f3() returns int -begin -declare n int; -declare m int; -set n:= (select min(data) from t1); -set m:= (select max(data) from t1); -return n < m; -end| -select f3()| -f3() -1 -select id, f3() from t1| -id f3() -a 1 -b 1 -select f1(), f3()| -f1() f3() -3 1 -select id, f1(), f3() from t1| -id f1() f3() -a 3 1 -b 3 1 -create function f4() returns double -return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")| -select f4()| -f4() -2 -select s, f4() from t2| -s f4() -a 2 -b 2 -c 2 -create function f5(i int) returns int -begin -if i <= 0 then -return 0; -elseif i = 1 then -return (select count(*) from t1 where data = i); -else -return (select count(*) + f5( i - 1) from t1 where data = i); -end if; -end| -select f5(1)| -f5(1) -1 -select f5(2)| -ERROR HY000: Recursive stored functions and triggers are not allowed. -select f5(3)| -ERROR HY000: Recursive stored functions and triggers are not allowed. -create function f6() returns int -begin -declare n int; -set n:= f1(); -return (select count(*) from t1 where data <= f7() and data <= n); -end| -create function f7() returns int -return (select sum(data) from t1 where data <= f1())| -select f6()| -f6() -2 -select id, f6() from t1| -id f6() -a 2 -b 2 -create view v1 (a) as select f1()| -select * from v1| -a -3 -select id, a from t1, v1| -id a -a 3 -b 3 -select * from v1, v1 as v| -a a -3 3 -create view v2 (a) as select a*10 from v1| -select * from v2| -a -30 -select id, a from t1, v2| -id a -a 30 -b 30 -select * from v1, v2| -a a -3 30 -create function f8 () returns int -return (select count(*) from v2)| -select *, f8() from v1| -a f8() -3 1 -drop function f1| -select * from v1| -ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them -create function f1() returns int -return (select sum(data) from t1) + (select sum(data) from v1)| -select f1()| -ERROR HY000: Recursive stored functions and triggers are not allowed. -select * from v1| -ERROR HY000: Recursive stored functions and triggers are not allowed. -select * from v2| -ERROR HY000: Recursive stored functions and triggers are not allowed. -drop function f1| -create function f1() returns int -return (select sum(data) from t1)| -create function f0() returns int -return (select * from (select 100) as r)| -select f0()| -f0() -100 -select *, f0() from (select 1) as t| -1 f0() -1 100 -create view v0 as select f0()| -select * from v0| -f0() -100 -select *, f0() from v0| -f0() f0() -100 100 -lock tables t1 read, t1 as t11 read| -select f3()| -f3() -1 -select id, f3() from t1 as t11| -id f3() -a 1 -b 1 -select f0()| -f0() -100 -select * from v0| -f0() -100 -select *, f0() from v0, (select 123) as d1| -f0() 123 f0() -100 123 100 -select id, f3() from t1| -ERROR HY000: Table 't1' was not locked with LOCK TABLES -select f4()| -ERROR HY000: Table 't2' was not locked with LOCK TABLES -unlock tables| -lock tables v2 read, mysql.proc read| -select * from v2| -a -30 -select * from v1| -a -3 -select * from v1, t1| -ERROR HY000: Table 't1' was not locked with LOCK TABLES -select f4()| -ERROR HY000: Table 't2' was not locked with LOCK TABLES -unlock tables| -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| -select f9()| -f9() -6 -Warnings: -Note 1051 Unknown table 't3' -select f9() from t1 limit 1| -f9() -6 -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| -select f10()| -ERROR 42S02: Table 'test.t4' doesn't exist -create table t4 as select 1 as id| -select f10()| -f10() -1 -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| -select f11()| -ERROR HY000: Can't reopen table: 'a' -select f11() from t1| -ERROR HY000: Can't reopen table: 'a' -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)| -drop temporary table t3| -select f12_1()| -ERROR 42S02: Table 'test.t3' doesn't exist -select f12_1() from t1 limit 1| -ERROR 42S02: Table 'test.t3' doesn't exist -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| -drop table if exists t3| -create table t3 (n int unsigned not null primary key, f bigint unsigned)| -drop procedure if exists ifac| -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| -n f -1 1 -2 2 -3 6 -4 24 -5 120 -6 720 -7 5040 -8 40320 -9 362880 -10 3628800 -11 39916800 -12 479001600 -13 6227020800 -14 87178291200 -15 1307674368000 -16 20922789888000 -17 355687428096000 -18 6402373705728000 -19 121645100408832000 -20 2432902008176640000 -drop table t3| -show function status like '%f%'| -Db Name Type Definer Modified Created Security_type Comment -test fac FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -drop procedure ifac| -drop function fac| -show function status like '%f%'| -Db Name Type Definer Modified Created Security_type Comment -drop table if exists t3| -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)| -drop procedure if exists opp| -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| -drop procedure if exists ip| -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| -Procedure sql_mode Create Procedure -opp 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 -show procedure status like '%p%'| -Db Name Type Definer Modified Created Security_type Comment -test ip PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -test opp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -call ip(200)| -select * from t3 where i=45 or i=100 or i=199| -i p -45 211 -100 557 -199 1229 -drop table t3| -drop procedure opp| -drop procedure ip| -show procedure status like '%p%'| -Db Name Type Definer Modified Created Security_type Comment -drop table if exists t3| -create table t3 ( f bigint unsigned not null )| -drop procedure if exists fib| -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| -set @@max_sp_recursion_depth= 20| -insert into t3 values (0), (1)| -call fib(3)| -select * from t3 order by f asc| -f -0 -1 -1 -2 -delete from t3| -insert into t3 values (0), (1)| -call fib(10)| -select * from t3 order by f asc| -f -0 -1 -1 -2 -3 -5 -8 -13 -21 -34 -55 -drop table t3| -drop procedure fib| -set @@max_sp_recursion_depth= 0| -drop procedure if exists bar| -create procedure bar(x char(16), y int) -comment "111111111111" sql security invoker -insert into test.t1 values (x, y)| -show procedure status like 'bar'| -Db Name Type Definer Modified Created Security_type Comment -test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER 111111111111 -alter procedure bar comment "2222222222" sql security definer| -alter procedure bar comment "3333333333"| -alter procedure bar| -show create procedure bar| -Procedure sql_mode Create Procedure -bar CREATE PROCEDURE `bar`(x char(16), y int) - COMMENT '3333333333' -insert into test.t1 values (x, y) -show procedure status like 'bar'| -Db Name Type Definer Modified Created Security_type Comment -test bar PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER 3333333333 -drop procedure bar| -drop procedure if exists p1| -create procedure p1 () -select (select s1 from t3) from t3| -create table t3 (s1 int)| -call p1()| -(select s1 from t3) -insert into t3 values (1)| -call p1()| -(select s1 from t3) -1 -drop procedure p1| -drop table t3| -drop function if exists foo| -create function `foo` () returns int -return 5| -select `foo` ()| -`foo` () -5 -drop function `foo`| -drop function if exists t1max| -create function t1max() returns int -begin -declare x int; -select max(data) into x from t1; -return x; -end| -insert into t1 values ("foo", 3), ("bar", 2), ("zip", 5), ("zap", 1)| -select t1max()| -t1max() -5 -drop function t1max| -create table t3 ( -v char(16) not null primary key, -c int unsigned not null -)| -create function getcount(s char(16)) returns int -begin -declare x int; -select count(*) into x from t3 where v = s; -if x = 0 then -insert into t3 values (s, 1); -else -update t3 set c = c+1 where v = s; -end if; -return x; -end| -select * from t1 where data = getcount("bar")| -id data -zap 1 -select * from t3| -v c -bar 4 -select getcount("zip")| -getcount("zip") -0 -select getcount("zip")| -getcount("zip") -1 -select * from t3| -v c -bar 4 -zip 2 -select getcount(id) from t1 where data = 3| -getcount(id) -0 -select getcount(id) from t1 where data = 5| -getcount(id) -1 -select * from t3| -v c -bar 4 -zip 3 -foo 1 -drop table t3| -drop function getcount| -drop 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| -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()| -h_ee -Inner (good) -call h_es()| -h_es -Outer (good) -call h_en()| -h_en -Outer (good) -call h_ew()| -h_ew -Outer (good) -call h_ex()| -h_ex -Outer (good) -call h_se()| -h_se -Inner (good) -call h_ss()| -h_ss -Inner (good) -call h_sn()| -h_sn -Outer (good) -call h_sw()| -h_sw -Outer (good) -call h_sx()| -h_sx -Outer (good) -call h_ne()| -h_ne -Inner (good) -call h_ns()| -h_ns -Inner (good) -call h_nn()| -h_nn -Inner (good) -call h_we()| -h_we -Inner (good) -call h_ws()| -h_ws -Inner (good) -call h_ww()| -h_ww -Inner (good) -call h_xe()| -h_xe -Inner (good) -call h_xs()| -h_xs -Inner (good) -call h_xx()| -h_xx -Inner (good) -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| -drop procedure if exists bug822| -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| -id data -foo 42 -bar 666 -delete from t1| -drop procedure bug822| -drop procedure if exists bug1495| -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| -id data -less 2 -more 17 -delete from t1| -drop procedure bug1495| -drop procedure if exists bug1547| -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| -id data -foo 12 -bar 7 -less 2 -more 17 -delete from t1| -drop procedure bug1547| -drop table if exists t70| -create table t70 (s1 int,s2 int)| -insert into t70 values (1,2)| -drop procedure if exists bug1656| -create procedure bug1656(out p1 int, out p2 int) -select * into p1, p1 from t70| -call bug1656(@1, @2)| -select @1, @2| -@1 @2 -2 NULL -drop table t70| -drop procedure bug1656| -create table t3(a int)| -drop procedure if exists bug1862| -create procedure bug1862() -begin -insert into t3 values(2); -flush tables; -end| -call bug1862()| -call bug1862()| -select * from t3| -a -2 -2 -drop table t3| -drop procedure bug1862| -drop procedure if exists bug1874| -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| -s i d -max 9 0 -min 1 0 -sum 22 0 -avg 0 4.4 -delete from t1| -delete from t2| -drop procedure bug1874| -drop procedure if exists bug2260| -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| -@x2 -2 -drop procedure bug2260| -drop procedure if exists bug2267_1| -create procedure bug2267_1() -begin -show procedure status; -end| -drop procedure if exists bug2267_2| -create procedure bug2267_2() -begin -show function status; -end| -drop procedure if exists bug2267_3| -create procedure bug2267_3() -begin -show create procedure bug2267_1; -end| -drop procedure if exists bug2267_4| -drop function if exists bug2267_4| -create procedure bug2267_4() -begin -show create function bug2267_4; -end| -create function bug2267_4() returns int return 100| -call bug2267_1()| -Db Name Type Definer Modified Created Security_type Comment -test bug2267_1 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -test bug2267_2 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -test bug2267_3 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -test bug2267_4 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -call bug2267_2()| -Db Name Type Definer Modified Created Security_type Comment -test bug2267_4 FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -call bug2267_3()| -Procedure sql_mode Create Procedure -bug2267_1 CREATE PROCEDURE `bug2267_1`() -begin -show procedure status; -end -call bug2267_4()| -Function sql_mode Create Function -bug2267_4 CREATE FUNCTION `bug2267_4`() RETURNS int(11) -return 100 -drop procedure bug2267_1| -drop procedure bug2267_2| -drop procedure bug2267_3| -drop procedure bug2267_4| -drop function bug2267_4| -drop procedure if exists bug2227| -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)| -1.3 x y 42 z -1.3 9 2.6 42 zzz -drop procedure bug2227| -drop procedure if exists bug2614| -create procedure bug2614() -begin -drop table if exists t3; -create table t3 (id int default '0' not null); -insert into t3 select 12; -insert into t3 select * from t3; -end| -call bug2614()| -call bug2614()| -drop table t3| -drop procedure bug2614| -drop function if exists bug2674| -create function bug2674() returns int -return @@sort_buffer_size| -set @osbs = @@sort_buffer_size| -set @@sort_buffer_size = 262000| -select bug2674()| -bug2674() -262000 -drop function bug2674| -set @@sort_buffer_size = @osbs| -drop procedure if exists bug3259_1 | -create procedure bug3259_1 () begin end| -drop procedure if exists BUG3259_2 | -create procedure BUG3259_2 () begin end| -drop procedure if exists Bug3259_3 | -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| -drop function if exists bug2772| -create function bug2772() returns char(10) character set latin2 -return 'a'| -select bug2772()| -bug2772() -a -drop function bug2772| -drop procedure if exists bug2776_1| -create procedure bug2776_1(out x int) -begin -declare v int; -set v = default; -set x = v; -end| -drop procedure if exists bug2776_2| -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| -@x -NULL -call bug2776_2(@x)| -select @x| -@x -42 -drop procedure bug2776_1| -drop procedure bug2776_2| -create table t3 (s1 smallint)| -insert into t3 values (123456789012)| -Warnings: -Warning 1264 Out of range value adjusted for column 's1' at row 1 -drop procedure if exists bug2780| -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| -@x -1 -select * from t3| -s1 -32767 -32767 -drop procedure bug2780| -drop table t3| -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)| -drop procedure if exists bug1863| -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)| -Warnings: -Note 1051 Unknown table 'temp_t1' -Warning 1329 No data - zero rows fetched, selected, or processed -call bug1863(10)| -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed -select * from t4| -f1 rc t3 -2 0 NULL -2 0 NULL -drop procedure bug1863| -drop temporary table temp_t1; -drop table t3, t4| -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")| -drop procedure if exists bug2656_1| -create procedure bug2656_1() -begin -select -m.Market -from t4 m JOIN t3 o -ON o.MarketID != 1 and o.MarketID = m.MarketID; -end | -drop procedure if exists bug2656_2| -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()| -Market -MarketID Two -call bug2656_1()| -Market -MarketID Two -call bug2656_2()| -Market -MarketID Two -call bug2656_2()| -Market -MarketID Two -drop procedure bug2656_1| -drop procedure bug2656_2| -drop table t3, t4| -drop procedure if exists bug3426| -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| -@i time -2 01-01-1970 03:16:40 -call bug3426(NULL, @i)| -select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| -@i time -1 01-01-1970 03:00:10 -alter procedure bug3426 sql security invoker| -call bug3426(NULL, @i)| -select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| -@i time -1 01-01-1970 03:00:10 -call bug3426(1000, @i)| -select @i, from_unixtime(@stamped_time, '%d-%m-%Y %h:%i:%s') as time| -@i time -2 01-01-1970 03:16:40 -drop procedure bug3426| -create table t3 ( -a int primary key, -ach char(1) -) engine = innodb| -create table t4 ( -b int primary key , -bch char(1) -) engine = innodb| -insert into t3 values (1 , 'aCh1' ) , ('2' , 'aCh2')| -Warnings: -Warning 1265 Data truncated for column 'ach' at row 1 -Warning 1265 Data truncated for column 'ach' at row 2 -insert into t4 values (1 , 'bCh1' )| -Warnings: -Warning 1265 Data truncated for column 'bch' at row 1 -drop procedure if exists bug3448| -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| -a ach b bch -1 a 1 b -call bug3448()| -a ach b bch -1 a 1 b -call bug3448()| -a ach b bch -1 a 1 b -drop procedure bug3448| -drop table t3, t4| -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 ...')| -drop procedure if exists bug3734 | -create procedure bug3734 (param1 varchar(100)) -select * from t3 where match (title,body) against (param1)| -call bug3734('database')| -id title body -5 MySQL vs. YourSQL In the following database comparison ... -1 MySQL Tutorial DBMS stands for DataBase ... -call bug3734('Security')| -id title body -6 MySQL Security When configured properly, MySQL ... -drop procedure bug3734| -drop table t3| -drop procedure if exists bug3863| -create procedure bug3863() -begin -set @a = 0; -while @a < 5 do -set @a = @a + 1; -end while; -end| -call bug3863()| -select @a| -@a -5 -call bug3863()| -select @a| -@a -5 -drop procedure bug3863| -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) -)| -drop procedure if exists bug2460_1| -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)| -id -call bug2460_1(2)| -id -insert into t3 values (1, 1, 'foo'), (2, 1, 'bar'), (3, 1, 'zip zap')| -call bug2460_1(2)| -id -2 -1 -call bug2460_1(2)| -id -2 -1 -drop procedure if exists bug2460_2| -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| -s1 -1 -drop procedure bug2460_1| -drop procedure bug2460_2| -drop table t3| -set @@sql_mode = ''| -drop procedure if exists bug2564_1| -create procedure bug2564_1() -comment 'Joe''s procedure' - insert into `t1` values ("foo", 1)| -set @@sql_mode = 'ANSI_QUOTES'| -drop procedure if exists bug2564_2| -create procedure bug2564_2() -insert into "t1" values ('foo', 1)| -set @@sql_mode = ''$ -drop function if exists bug2564_3$ -create function bug2564_3(x int, y int) returns int -return x || y$ -set @@sql_mode = 'ANSI'$ -drop function if exists bug2564_4$ -create function bug2564_4(x int, y int) returns int -return x || y$ -set @@sql_mode = ''| -show create procedure bug2564_1| -Procedure sql_mode Create Procedure -bug2564_1 CREATE PROCEDURE `bug2564_1`() - COMMENT 'Joe''s procedure' -insert into `t1` values ("foo", 1) -show create procedure bug2564_2| -Procedure sql_mode Create Procedure -bug2564_2 ANSI_QUOTES CREATE PROCEDURE "bug2564_2"() -insert into "t1" values ('foo', 1) -show create function bug2564_3| -Function sql_mode Create Function -bug2564_3 CREATE FUNCTION `bug2564_3`(x int, y int) RETURNS int(11) -return x || y -show create function bug2564_4| -Function sql_mode Create Function -bug2564_4 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI CREATE FUNCTION "bug2564_4"(x int, y int) RETURNS int(11) -return x || y -drop procedure bug2564_1| -drop procedure bug2564_2| -drop function bug2564_3| -drop function bug2564_4| -drop function if exists bug3132| -create function bug3132(s char(20)) returns char(50) -return concat('Hello, ', s, '!')| -select bug3132('Bob') union all select bug3132('Judy')| -bug3132('Bob') -Hello, Bob! -Hello, Judy! -drop function bug3132| -drop procedure if exists bug3843| -create procedure bug3843() -analyze table t1| -call bug3843()| -Table Op Msg_type Msg_text -test.t1 analyze status OK -call bug3843()| -Table Op Msg_type Msg_text -test.t1 analyze status Table is already up to date -select 1+2| -1+2 -3 -drop procedure bug3843| -create table t3 ( s1 char(10) )| -insert into t3 values ('a'), ('b')| -drop procedure if exists bug3368| -create procedure bug3368(v char(10)) -begin -select group_concat(v) from t3; -end| -call bug3368('x')| -group_concat(v) -x,x -call bug3368('yz')| -group_concat(v) -yz,yz -drop procedure bug3368| -drop table t3| -create table t3 (f1 int, f2 int)| -insert into t3 values (1,1)| -drop procedure if exists bug4579_1| -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| -drop procedure if exists bug4579_2| -create procedure bug4579_2 () -begin -end| -call bug4579_1()| -call bug4579_1()| -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed -call bug4579_1()| -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed -drop procedure bug4579_1| -drop procedure bug4579_2| -drop table t3| -drop procedure if exists bug2773| -create function bug2773() returns int return null| -create table t3 as select bug2773()| -show create table t3| -Table Create Table -t3 CREATE TABLE `t3` ( - `bug2773()` int(11) default NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -drop table t3| -drop function bug2773| -drop procedure if exists bug3788| -create function bug3788() returns date return cast("2005-03-04" as date)| -select bug3788()| -bug3788() -2005-03-04 -drop function bug3788| -create function bug3788() returns binary(1) return 5| -select bug3788()| -bug3788() -5 -drop function bug3788| -create table t3 (f1 int, f2 int, f3 int)| -insert into t3 values (1,1,1)| -drop procedure if exists bug4726| -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| -drop procedure if exists bug4902| -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| -call bug4902()| -Charset Description Default collation Maxlen -Collation Charset Id Default Compiled Sortlen -Type Size Min_Value Max_Value Prec Scale Nullable Auto_Increment Unsigned Zerofill Searchable Case_Sensitive Default Comment -tinyint 1 -128 127 0 0 YES YES NO YES YES NO NULL,0 A very small integer -tinyint unsigned 1 0 255 0 0 YES YES YES YES YES NO NULL,0 A very small integer -Table Create Table -t1 CREATE TABLE `t1` ( - `id` char(16) NOT NULL default '', - `data` int(11) NOT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -Database Create Database -test CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ -Database (foo) -Level Code Message -Field Type Null Key Default Extra -id char(16) NO -data int(11) NO -Grants for root@localhost -GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -Database Table In_use Name_locked -Privilege Context Comment -Alter Tables To alter the table -Alter routine Functions,Procedures To alter or drop stored functions/procedures -Create Databases,Tables,Indexes To create new databases and tables -Create routine Functions,Procedures To use CREATE FUNCTION/PROCEDURE -Create temporary tables Databases To use CREATE TEMPORARY TABLE -Create view Tables To create new views -Create user Server Admin To create new users -Delete Tables To delete existing rows -Drop Databases,Tables To drop databases, tables, and views -Execute Functions,Procedures To execute stored routines -File File access on server To read and write files on the server -Grant option Databases,Tables,Functions,Procedures To give to other users those privileges you possess -Index Tables To create or drop indexes -Insert Tables To insert data into tables -Lock tables Databases To use LOCK TABLES (together with SELECT privilege) -Process Server Admin To view the plain text of currently executing queries -References Databases,Tables To have references on tables -Reload Server Admin To reload or refresh tables, logs and privileges -Replication client Server Admin To ask where the slave or master servers are -Replication slave Server Admin To read binary log events from the master -Select Tables To retrieve rows from table -Show databases Server Admin To see all databases with SHOW DATABASES -Show view Tables To see views with SHOW CREATE VIEW -Shutdown Server Admin To shut down the server -Super Server Admin To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. -Update Tables To update existing rows -Usage Server Admin No privileges - allow connect only -Variable_name Value -Tables_in_test (foo) -Variable_name Value -Level Code Message -call bug4902()| -Charset Description Default collation Maxlen -Collation Charset Id Default Compiled Sortlen -Type Size Min_Value Max_Value Prec Scale Nullable Auto_Increment Unsigned Zerofill Searchable Case_Sensitive Default Comment -tinyint 1 -128 127 0 0 YES YES NO YES YES NO NULL,0 A very small integer -tinyint unsigned 1 0 255 0 0 YES YES YES YES YES NO NULL,0 A very small integer -Table Create Table -t1 CREATE TABLE `t1` ( - `id` char(16) NOT NULL default '', - `data` int(11) NOT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -Database Create Database -test CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ -Database (foo) -Level Code Message -Field Type Null Key Default Extra -id char(16) NO -data int(11) NO -Grants for root@localhost -GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -Database Table In_use Name_locked -Privilege Context Comment -Alter Tables To alter the table -Alter routine Functions,Procedures To alter or drop stored functions/procedures -Create Databases,Tables,Indexes To create new databases and tables -Create routine Functions,Procedures To use CREATE FUNCTION/PROCEDURE -Create temporary tables Databases To use CREATE TEMPORARY TABLE -Create view Tables To create new views -Create user Server Admin To create new users -Delete Tables To delete existing rows -Drop Databases,Tables To drop databases, tables, and views -Execute Functions,Procedures To execute stored routines -File File access on server To read and write files on the server -Grant option Databases,Tables,Functions,Procedures To give to other users those privileges you possess -Index Tables To create or drop indexes -Insert Tables To insert data into tables -Lock tables Databases To use LOCK TABLES (together with SELECT privilege) -Process Server Admin To view the plain text of currently executing queries -References Databases,Tables To have references on tables -Reload Server Admin To reload or refresh tables, logs and privileges -Replication client Server Admin To ask where the slave or master servers are -Replication slave Server Admin To read binary log events from the master -Select Tables To retrieve rows from table -Show databases Server Admin To see all databases with SHOW DATABASES -Show view Tables To see views with SHOW CREATE VIEW -Shutdown Server Admin To shut down the server -Super Server Admin To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. -Update Tables To update existing rows -Usage Server Admin No privileges - allow connect only -Variable_name Value -Tables_in_test (foo) -Variable_name Value -Level Code Message -drop procedure bug4902| -drop procedure if exists bug4902_2| -create procedure bug4902_2() -begin -show processlist; -end| -call bug4902_2()| -Id User Host db Command Time State Info -# root localhost test Query # NULL show processlist -call bug4902_2()| -Id User Host db Command Time State Info -# root localhost test Query # NULL show processlist -drop procedure bug4902_2| -drop procedure if exists bug4904| -create procedure bug4904() -begin -declare continue handler for sqlstate 'HY000' begin end; -create table t2 as select * from t3; -end| -call bug4904()| -ERROR 42S02: Table 'test.t3' doesn't exist -drop procedure bug4904| -create table t3 (s1 char character set latin1, s2 char character set latin2)| -drop procedure if exists bug4904| -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| -drop procedure if exists bug336| -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| -@y -5 -delete from t1| -drop procedure bug336| -drop procedure if exists bug3157| -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| -@n -2 -delete from t1| -drop procedure bug3157| -drop procedure if exists bug5251| -create procedure bug5251() -begin -end| -select created into @c1 from mysql.proc -where db='test' and name='bug5251'| -alter procedure bug5251 comment 'foobar'| -select count(*) from mysql.proc -where db='test' and name='bug5251' and created = @c1| -count(*) -1 -drop procedure bug5251| -drop procedure if exists bug5251| -create procedure bug5251() -checksum table t1| -call bug5251()| -Table Checksum -test.t1 0 -call bug5251()| -Table Checksum -test.t1 0 -drop procedure bug5251| -drop procedure if exists bug5287| -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| -drop procedure if exists bug5307| -create procedure bug5307() -begin -end; set @x = 3| -call bug5307()| -select @x| -@x -3 -drop procedure bug5307| -drop procedure if exists bug5258| -create procedure bug5258() -begin -end| -drop procedure if exists bug5258_aux| -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()| -Ok -Ok -drop procedure bug5258| -drop procedure bug5258_aux| -drop function if exists bug4487| -create function bug4487() returns char -begin -declare v char; -return v; -end| -select bug4487()| -bug4487() -NULL -drop function bug4487| -drop procedure if exists bug4941| -drop procedure if exists bug4941| -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| -@x -NULL -delete from t1| -drop procedure bug4941| -drop procedure if exists bug3583| -drop procedure if exists bug3583| -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'| -Variable_name Value -Qcache_hits 0 -call bug3583()| -id data -x 3 -y 5 -c -2 -show status like 'Qcache_hits'| -Variable_name Value -Qcache_hits 0 -call bug3583()| -id data -x 3 -y 5 -c -2 -call bug3583()| -id data -x 3 -y 5 -c -2 -show status like 'Qcache_hits'| -Variable_name Value -Qcache_hits 2 -set global query_cache_size = @x| -flush status| -flush query cache| -delete from t1| -drop procedure bug3583| -drop procedure if exists bug4905| -create table t3 (s1 int,primary key (s1))| -drop procedure if exists bug4905| -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()| -row_count() -1 -call bug4905()| -select row_count()| -row_count() -0 -call bug4905()| -select row_count()| -row_count() -0 -select * from t3| -s1 -1 -drop procedure bug4905| -drop table t3| -drop procedure if exists bug6029| -drop procedure if exists bug6029| -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()| -sqlstate 23000 -sqlstate 23000 -delete from t3| -call bug6029()| -1136 -1136 -drop procedure bug6029| -drop table t3| -drop procedure if exists bug8540| -create procedure bug8540() -begin -declare x int default 1; -select x as y, x+0 as z; -end| -call bug8540()| -y z -1 1 -drop procedure bug8540| -create table t3 (s1 int)| -drop procedure if exists bug6642| -create procedure bug6642() -select abs(count(s1)) from t3| -call bug6642()| -abs(count(s1)) -0 -call bug6642()| -abs(count(s1)) -0 -drop procedure bug6642| -insert into t3 values (0),(1)| -drop procedure if exists bug7013| -create procedure bug7013() -select s1,count(s1) from t3 group by s1 with rollup| -call bug7013()| -s1 count(s1) -0 1 -1 1 -NULL 2 -call bug7013()| -s1 count(s1) -0 1 -1 1 -NULL 2 -drop procedure bug7013| -drop table if exists t4| -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')| -drop procedure if exists bug7743| -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")| -var -1 -call bug7743("OneWord")| -var -NULL -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed -call bug7743("anotherword")| -var -2 -call bug7743("AnotherWord")| -var -NULL -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed -drop procedure bug7743| -drop table t4| -delete from t3| -insert into t3 values(1)| -drop procedure if exists bug7992_1| -Warnings: -Note 1305 PROCEDURE bug7992_1 does not exist -drop procedure if exists bug7992_2| -Warnings: -Note 1305 PROCEDURE bug7992_2 does not exist -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| -create table t3 ( userid bigint(20) not null default 0 )| -drop procedure if exists bug8116| -create procedure bug8116(in _userid int) -select * from t3 where userid = _userid| -call bug8116(42)| -userid -call bug8116(42)| -userid -drop procedure bug8116| -drop table t3| -drop procedure if exists bug6857| -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| -drop procedure bug6857| -drop procedure if exists bug8757| -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()| -2 y -2 2 -1 x -1 1 -delete from t1| -delete from t2| -drop procedure bug8757| -drop procedure if exists bug8762| -drop procedure if exists bug8762; create procedure bug8762() begin end| -drop procedure if exists bug8762; create procedure bug8762() begin end| -drop procedure bug8762| -drop function if exists bug5240| -create function bug5240 () returns int -begin -declare x int; -declare c cursor for select data from t1 limit 1; -open c; -fetch c into x; -close c; -return x; -end| -delete from t1| -insert into t1 values ("answer", 42)| -select id, bug5240() from t1| -id bug5240() -answer 42 -drop function bug5240| -drop function if exists bug5278| -create function bug5278 () returns char -begin -SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass'); -return 'okay'; -end| -select bug5278()| -ERROR 42000: Can't find any matching row in the user table -select bug5278()| -ERROR 42000: Can't find any matching row in the user table -drop function bug5278| -drop procedure if exists p1| -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| -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) -)| -drop procedure if exists bug8849| -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| -drop procedure if exists bug8937| -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()| -s x y z -16 3 1 6 -a -3.2 -drop procedure bug8937| -delete from t1| -drop procedure if exists bug6900| -drop procedure if exists bug9074| -drop procedure if exists bug6900_9074| -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()| -2 -2 -call bug9074()| -x1 x2 x3 x4 x5 x6 -1 1 1 1 1 1 -call bug6900_9074(0)| -sqlexception -sqlexception -call bug6900_9074(1)| -23000 -23000 -drop procedure bug6900| -drop procedure bug9074| -drop procedure bug6900_9074| -drop table t3| -drop procedure if exists avg| -create procedure avg () -begin -end| -call avg ()| -drop procedure avg| -drop procedure if exists bug6129| -set @old_mode= @@sql_mode; -set @@sql_mode= "ERROR_FOR_DIVISION_BY_ZERO"; -create procedure bug6129() -select @@sql_mode| -call bug6129()| -@@sql_mode -ERROR_FOR_DIVISION_BY_ZERO -set @@sql_mode= "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO"| -call bug6129()| -@@sql_mode -ERROR_FOR_DIVISION_BY_ZERO -set @@sql_mode= "NO_ZERO_IN_DATE"| -call bug6129()| -@@sql_mode -ERROR_FOR_DIVISION_BY_ZERO -set @@sql_mode=@old_mode; -drop procedure bug6129| -drop procedure if exists bug9856| -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()| -16 -16 -call bug9856()| -16 -16 -drop procedure bug9856| -drop procedure if exists bug9674_1| -drop procedure if exists bug9674_2| -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| -@sptmp -10 -call bug9674_2()| -v/10 -10.0000 -call bug9674_2()| -v/10 -10.0000 -drop procedure bug9674_1| -drop procedure bug9674_2| -drop procedure if exists bug9598_1| -drop procedure if exists bug9598_2| -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)| -v1 v2 v3 v4 v5 -Test 2 3 4 5 -v1 v2 v3 v4 v5 -Test 2 3 4 5 -select @tmp1, @tmp2| -@tmp1 @tmp2 -50 60 -drop procedure bug9598_1| -drop procedure bug9598_2| -drop procedure if exists bug9902| -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| -bug9902() -2 -select bug9902() from t1| -bug9902() -3 -select @x| -@x -3 -set global query_cache_size = @qcs1| -delete from t1| -drop function bug9902| -drop function if exists bug9102| -create function bug9102() returns blob return 'a'| -select bug9102()| -bug9102() -a -drop function bug9102| -drop function if exists bug7648| -create function bug7648() returns bit(8) return 'a'| -select bug7648()| -bug7648() -a -drop function bug7648| -drop function if exists bug9775| -create function bug9775(v1 char(1)) returns enum('a','b') return v1| -select bug9775('a'),bug9775('b'),bug9775('c')| -bug9775('a') bug9775('b') bug9775('c') -a b -Warnings: -Warning 1265 Data truncated for column 'bug9775('c')' at row 1 -drop function bug9775| -create function bug9775(v1 int) returns enum('a','b') return v1| -select bug9775(1),bug9775(2),bug9775(3)| -bug9775(1) bug9775(2) bug9775(3) -a b -Warnings: -Warning 1265 Data truncated for column 'bug9775(3)' at row 1 -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')| -bug9775('a') bug9775('b') bug9775('a,b') bug9775('c') -a b a -Warnings: -Warning 1265 Data truncated for column 'v1' at row 1 -Warning 1265 Data truncated for column 'bug9775('c')' at row 1 -drop function bug9775| -create function bug9775(v1 int) returns set('a','b') return v1| -select bug9775(1),bug9775(2),bug9775(3),bug9775(4)| -bug9775(1) bug9775(2) bug9775(3) bug9775(4) -a b a,b -Warnings: -Warning 1265 Data truncated for column 'bug9775(4)' at row 1 -drop function bug9775| -drop function if exists bug8861| -create function bug8861(v1 int) returns year return v1| -select bug8861(05)| -bug8861(05) -2005 -set @x = bug8861(05)| -select @x| -@x -2005 -drop function bug8861| -drop procedure if exists bug9004_1| -drop procedure if exists bug9004_2| -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)| -call bug9004_1('12345678901234567')| -Warnings: -Warning 1265 Data truncated for column 'x' at row 1 -call bug9004_2('12345678901234567890')| -Warnings: -Warning 1265 Data truncated for column 'x' at row 1 -delete from t1| -drop procedure bug9004_1| -drop procedure bug9004_2| -drop procedure if exists bug7293| -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')| -yes -yes -call bug7293 ('secrete')| -yes -yes -drop procedure bug7293| -delete from t1| -drop procedure if exists bug9841| -drop view if exists v1| -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| -drop procedure if exists bug5963| -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()| -v -5 -call bug5963_1()| -v -5 -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| -drop function if exists bug9559| -create function bug9559() -returns int -begin -set @y = -6/2; -return @y; -end| -select bug9559()| -bug9559() --3 -drop function bug9559| -drop procedure if exists bug10961| -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()| -x -1 -x -2 -x -3 -call bug10961()| -x -1 -x -2 -x -3 -drop procedure bug10961| -DROP PROCEDURE IF EXISTS bug6866| -DROP VIEW IF EXISTS tv| -Warnings: -Note 1051 Unknown table 'test.tv' -DROP TABLE IF EXISTS tt1,tt2,tt3| -Warnings: -Note 1051 Unknown table 'tt1' -Note 1051 Unknown table 'tt2' -Note 1051 Unknown table '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)| -a1 a2 a3 data data2 data3 -1 1 4 xx a d -CALL bug6866(1)| -a1 a2 a3 data data2 data3 -1 1 4 xx a d -CALL bug6866(1)| -a1 a2 a3 data data2 data3 -1 1 4 xx a d -DROP PROCEDURE bug6866; -DROP VIEW tv| -DROP TABLE tt1, tt2, tt3| -DROP PROCEDURE IF EXISTS bug10136| -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()| -name val -aaaaa 1 -bbbbb 2 -ccccc 3 -name val -aaaaa 1 -bbbbb 2 -ccccc 3 -name val -aaaaa 1 -bbbbb 2 -ccccc 3 -call bug10136()| -name val -aaaaa 1 -bbbbb 2 -ccccc 3 -name val -aaaaa 1 -bbbbb 2 -ccccc 3 -name val -aaaaa 1 -bbbbb 2 -ccccc 3 -call bug10136()| -name val -aaaaa 1 -bbbbb 2 -ccccc 3 -name val -aaaaa 1 -bbbbb 2 -ccccc 3 -name val -aaaaa 1 -bbbbb 2 -ccccc 3 -drop procedure bug10136| -drop table t3| -drop procedure if exists bug11529| -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| -drop procedure if exists bug6063| -drop procedure if exists bug7088_1| -drop procedure if exists bug7088_2| -drop procedure if exists bug9565_sub| -drop procedure if exists bug9565| -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()| -id data -one 1 -delete from t1| -drop procedure bug9565_sub| -drop procedure bug9565| -drop procedure if exists bug9538| -create procedure bug9538() -set @@sort_buffer_size = 1000000| -set @x = @@sort_buffer_size| -set @@sort_buffer_size = 2000000| -select @@sort_buffer_size| -@@sort_buffer_size -2000000 -call bug9538()| -select @@sort_buffer_size| -@@sort_buffer_size -1000000 -set @@sort_buffer_size = @x| -drop procedure bug9538| -drop procedure if exists bug8692| -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)| -Warnings: -Warning 1265 Data truncated for column 'c3' at row 1 -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()| -v1 v2 v3 v4 v5 v6 v7 - NULL -drop procedure bug8692| -drop table t3| -drop function if exists bug10055| -create function bug10055(v char(255)) returns char(255) return lower(v)| -select t.column_name, bug10055(t.column_name) -from information_schema.columns as t -where t.table_schema = 'test' and t.table_name = 't1'| -column_name bug10055(t.column_name) -id id -data data -drop function bug10055| -drop procedure if exists bug12297| -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| -drop function if exists f_bug11247| -drop procedure if exists p_bug11247| -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| -drop procedure if exists bug12168| -drop table if exists t3, t4| -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| -a -1 -3 -truncate t4| -call bug12168('b')| -select * from t4| -a -2 -4 -truncate t4| -call bug12168('a')| -select * from t4| -a -1 -3 -truncate t4| -call bug12168('b')| -select * from t4| -a -2 -4 -truncate t4| -drop table t3, t4| -drop procedure if exists bug12168| -drop table if exists t3| -drop procedure if exists bug11333| -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| -drop function if exists bug9048| -create function bug9048(f1 char binary) returns char binary -begin -set f1= concat( 'hello', f1 ); -return f1; -end| -drop function bug9048| -drop procedure if exists bug12849_1| -create procedure bug12849_1(inout x char) select x into x| -set @var='a'| -call bug12849_1(@var)| -select @var| -@var -a -drop procedure bug12849_1| -drop procedure if exists bug12849_2| -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| -@var -abcdabcd -drop procedure bug12849_2| -drop procedure if exists bug131333| -drop function if exists bug131333| -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()| -a -NULL -a -1 -b -NULL -select bug131333()| -bug131333() -NULL -drop procedure bug131333| -drop function bug131333| -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| -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| -select bug12379()| -ERROR 23000: Duplicate entry 'X' for key 1 -select 1| -1 -1 -call bug12379_1()| -bug12379() -42 -42 -select 2| -2 -2 -call bug12379_2()| -bug12379() -select 3| -3 -3 -call bug12379_3()| -ERROR 23000: Duplicate entry 'X' for key 1 -select 4| -4 -4 -drop function bug12379| -drop procedure bug12379_1| -drop procedure bug12379_2| -drop procedure bug12379_3| -drop table t3| -drop procedure if exists bug13124| -create procedure bug13124() -begin -declare y integer; -set @x=y; -end| -call bug13124()| -drop procedure bug13124| -drop procedure if exists bug12979_1| -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| -drop procedure if exists bug12979_2| -create procedure bug12979_2() -begin -declare internal_var decimal(5); -set internal_var= internal_var / 2; -select internal_var; -end| -call bug12979_2()| -internal_var -NULL -drop procedure bug12979_2| -drop table if exists t3| -drop procedure if exists bug6127| -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| -s1 -0 -1 -call bug6127()| -ERROR 23000: Duplicate entry '0' for key 1 -select * from t3| -s1 -0 -1 -set sql_mode=@sm| -drop table t3| -drop procedure bug6127| -drop procedure if exists bug12589_1| -drop procedure if exists bug12589_2| -drop procedure if exists bug12589_3| -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| -call bug12589_1()| -Table Create Table -tm1 CREATE TEMPORARY TABLE `tm1` ( - `spv1` decimal(3,3) default NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -Warnings: -Warning 1264 Out of range value adjusted for column 'spv1' at row 1 -Warning 1366 Incorrect decimal value: 'test' for column 'spv1' at row 1 -call bug12589_2()| -Table Create Table -tm1 CREATE TEMPORARY TABLE `tm1` ( - `spv1` decimal(6,3) default NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -call bug12589_3()| -Table Create Table -tm1 CREATE TEMPORARY TABLE `tm1` ( - `spv1` decimal(6,3) default NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -drop procedure bug12589_1| -drop procedure bug12589_2| -drop procedure bug12589_3| -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| -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()| -Result -Caught it -select * from t3| -x -42 -delete from t3| -call bug7049_4()| -Result -Caught it -select * from t3| -x -42 -select bug7049_2()| -bug7049_2() -1 -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| -drop function if exists bug13941| -drop procedure if exists bug13941| -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| -select bug13941('this is a test')| -bug13941('this is a test') -questo una prova -call bug13941(@a)| -select @a| -@a -Local -drop function bug13941| -drop procedure bug13941| -DROP PROCEDURE IF EXISTS bug13095; -DROP TABLE IF EXISTS bug13095_t1; -DROP VIEW IF EXISTS bug13095_v1; -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| -CALL bug13095('bug13095_t1'); -@str -CREATE TABLE bug13095_t1(stuff char(15)) -@str -INSERT INTO bug13095_t1 VALUES('row1'),('row2'),('row3') -@str -CREATE VIEW bug13095_v1(c1) AS SELECT stuff FROM bug13095_t1 -c1 -row1 -row2 -row3 -@str -DROP VIEW bug13095_v1 -DROP PROCEDURE IF EXISTS bug13095; -DROP VIEW IF EXISTS bug13095_v1; -DROP TABLE IF EXISTS bug13095_t1; -drop procedure if exists bug14210| -set @@session.max_heap_table_size=16384| -select @@session.max_heap_table_size| -@@session.max_heap_table_size -16384 -create table t3 (a char(255)) engine=InnoDB| -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| -count(*) -256 -drop table t3, t4| -drop procedure bug14210| -set @@session.max_heap_table_size=default| -drop function if exists bug14723| -drop procedure if exists bug14723| -/*!50003 create function bug14723() -returns bigint(20) -main_loop: begin -return 42; -end */;; -show create function bug14723;; -Function sql_mode Create Function -bug14723 CREATE FUNCTION `bug14723`() RETURNS bigint(20) -main_loop: begin -return 42; -end -select bug14723();; -bug14723() -42 -/*!50003 create procedure bug14723() -main_loop: begin -select 42; -end */;; -show create procedure bug14723;; -Procedure sql_mode Create Procedure -bug14723 CREATE PROCEDURE `bug14723`() -main_loop: begin -select 42; -end -call bug14723();; -42 -42 -drop function bug14723| -drop procedure bug14723| -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()| -a -0 -drop procedure bug14845| -drop procedure if exists bug13549_1| -drop procedure if exists bug13549_2| -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| -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| -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| -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| -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| -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| -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| -@@max_sp_recursion_depth -4 -select bug10100f(3)| -ERROR HY000: Recursive stored functions and triggers are not allowed. -select bug10100f(6)| -ERROR HY000: Recursive stored functions and triggers are not allowed. -call bug10100t(5)| -res -120 -call bug10100pt(1,5)| -a -4 -call bug10100pv(1,5)| -a -4 -update t3 set a=1| -call bug10100pd(1,5)| -level -1 -a -7 -a -7 -level -2 -a -13 -a -13 -level -3 -a -19 -a -19 -level -4 -a -25 -a -25 -a -25 -select * from t3| -a -25 -update t3 set a=1| -call bug10100pc(1,5)| -level -1 -lv -1 -level -2 -lv -2 -level -3 -lv -4 -level -4 -lv -7 -a -11 -select * from t3| -a -11 -set @@max_sp_recursion_depth=0| -select @@max_sp_recursion_depth| -@@max_sp_recursion_depth -0 -select bug10100f(5)| -ERROR HY000: Recursive stored functions and triggers are not allowed. -call bug10100t(5)| -ERROR HY000: Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine bug10100p -set @@max_sp_recursion_depth=255| -set @var=1| -call bug10100p(255, @var)| -call bug10100pt(1,255)| -call bug10100pv(1,255)| -call bug10100pd(1,255)| -call bug10100pc(1,255)| -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| -drop procedure if exists bug13729| -drop table if exists t3| -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()| -55 -55 -select * from t3| -s1 -1 -2 -drop procedure bug13729| -drop table t3| -drop procedure if exists bug14643_1| -drop procedure if exists bug14643_2| -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()| -Handler -boo -v isnull(v) -NULL 1 -call bug14643_2()| -Handler -boo -Handler -boo -drop procedure bug14643_1| -drop procedure bug14643_2| -drop procedure if exists bug14304| -drop table if exists t3, t4| -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()| -a -1 -2 -3 -drop procedure bug14304| -drop table t3, t4| -drop procedure if exists bug14376| -create procedure bug14376() -begin -declare x int default x; -end| -call bug14376()| -ERROR 42S22: Unknown column 'x' in 'field list' -drop procedure bug14376| -create procedure bug14376() -begin -declare x int default 42; -begin -declare x int default x; -select x; -end; -end| -call bug14376()| -x -42 -drop procedure bug14376| -create procedure bug14376(x int) -begin -declare x int default x; -select x; -end| -call bug14376(4711)| -x -4711 -drop procedure bug14376| -drop procedure if exists bug5967| -drop table if exists t3| -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")| -a -a - stored procedure parameter -Parameter takes precedence over table column -a - stored procedure parameter -Parameter takes precedence over table column in cursors -a - stored procedure parameter -A local variable takes precedence over parameter -a - local variable -A local variable takes precedence over parameter in cursors -a - local variable -A local variable in a nested compound statement takes precedence over a local variable in the outer statement -a - local variable in a nested compound statement -A local variable in a nested compound statement takes precedence over table column -a - local variable in a nested compound statement -A local variable in a nested compound statement takes precedence over table column in cursors -a - local variable in a nested compound statement -drop procedure bug5967| -drop procedure if exists bug13012| -create procedure bug13012() -BEGIN -REPAIR TABLE t1; -BACKUP TABLE t1 to '../tmp'; -DROP TABLE t1; -RESTORE TABLE t1 FROM '../tmp'; -END| -call bug13012()| -Table Op Msg_type Msg_text -test.t1 repair status OK -Table Op Msg_type Msg_text -test.t1 backup status OK -Table Op Msg_type Msg_text -test.t1 restore status OK -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()| -Table Op Msg_type Msg_text -test.t1 repair status OK -test.t2 repair status OK -test.t3 repair status OK -test.v1 repair error 'test.v1' is not BASE TABLE -Table Op Msg_type Msg_text -test.t1 optimize status OK -test.t2 optimize status OK -test.t3 optimize status OK -test.v1 optimize error 'test.v1' is not BASE TABLE -Table Op Msg_type Msg_text -test.t1 analyze status Table is already up to date -test.t2 analyze status Table is already up to date -test.t3 analyze status Table is already up to date -test.v1 analyze error 'test.v1' is not BASE TABLE -Warnings: -Error 1347 'test.v1' is not BASE TABLE -call bug13012()| -Table Op Msg_type Msg_text -test.t1 repair status OK -test.t2 repair status OK -test.t3 repair status OK -test.v1 repair error 'test.v1' is not BASE TABLE -Table Op Msg_type Msg_text -test.t1 optimize status OK -test.t2 optimize status OK -test.t3 optimize status OK -test.v1 optimize error 'test.v1' is not BASE TABLE -Table Op Msg_type Msg_text -test.t1 analyze status Table is already up to date -test.t2 analyze status Table is already up to date -test.t3 analyze status Table is already up to date -test.v1 analyze error 'test.v1' is not BASE TABLE -Warnings: -Error 1347 'test.v1' is not BASE TABLE -call bug13012()| -Table Op Msg_type Msg_text -test.t1 repair status OK -test.t2 repair status OK -test.t3 repair status OK -test.v1 repair error 'test.v1' is not BASE TABLE -Table Op Msg_type Msg_text -test.t1 optimize status OK -test.t2 optimize status OK -test.t3 optimize status OK -test.v1 optimize error 'test.v1' is not BASE TABLE -Table Op Msg_type Msg_text -test.t1 analyze status Table is already up to date -test.t2 analyze status Table is already up to date -test.t3 analyze status Table is already up to date -test.v1 analyze error 'test.v1' is not BASE TABLE -Warnings: -Error 1347 'test.v1' is not BASE TABLE -drop procedure bug13012| -drop view v1; -select * from t1| -id data -aa 0 -aa 1 -aa 2 -aa 3 -aa 4 -aa 5 -aa 6 -aa 7 -aa 8 -aa 9 -drop schema if exists mysqltest1| -Warnings: -Note 1008 Can't drop database 'mysqltest1'; database doesn't exist -drop schema if exists mysqltest2| -Warnings: -Note 1008 Can't drop database 'mysqltest2'; database doesn't exist -drop schema if exists mysqltest3| -Warnings: -Note 1008 Can't drop database 'mysqltest3'; database doesn't exist -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| -call mysqltest1.p1(@rs)| -ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2 -call mysqltest1.p1(@rs)| -ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2 -call mysqltest1.p1(@rs)| -ERROR 42000: Incorrect number of arguments for PROCEDURE mysqltest3.p3; expected 1, got 2 -drop schema if exists mysqltest1| -drop schema if exists mysqltest2| -drop schema if exists mysqltest3| -use test| -drop table if exists t3| -drop procedure if exists bug15441| -create table t3 (id int not null primary key, county varchar(25))| -insert into t3 (id, county) values (1, 'York')| -create procedure bug15441(c varchar(25)) -begin -update t3 set id=2, county=values(c); -end| -call bug15441('county')| -ERROR 42S22: Unknown column 'c' in 'field list' -drop procedure bug15441| -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')| -id county -1 Yale -id county -2 NULL -drop table t3| -drop procedure bug15441| -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| -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()| -Handler -error -End -done -call bug14498_2()| -Handler -error -End -done -call bug14498_3()| -v -maybe -Handler -error -End -done -call bug14498_4()| -Handler -error -End -done -call bug14498_5()| -Handler -error -End -done -drop procedure bug14498_1| -drop procedure bug14498_2| -drop procedure bug14498_3| -drop procedure bug14498_4| -drop procedure bug14498_5| -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| -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()| -1 -Before NOT FOUND condition is triggered -2 -After NOT FOUND condtition is triggered -xid xdone -1 0 -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed -call bug15231_3()| -Result -Missed it (correct) -Warnings: -Warning 1366 Incorrect decimal value: 'zap' for column 'x' at row 1 -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| -drop procedure if exists bug15011| -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()| -Handler -Inner -drop procedure bug15011| -drop table t3| -drop table t1,t2; diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 33b06e9bc11..3ad33dddcbe 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -157,7 +157,7 @@ UPDATE `t1` AS P1 INNER JOIN (SELECT aaaa FROM `t1` GROUP BY N HAVING Count(M) > delete P1.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; select * from t1; --replace_result P2 p2 ---error 1288 +--error ER_UNKNOWN_TABLE delete P1.*,P2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; -- error 1054 delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N; diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 042ad178fc8..7987b3f563d 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -376,6 +376,16 @@ select monthname(str_to_date(null, '%m')), monthname(str_to_date(null, '%m')), monthname(str_to_date(1, '%m')), monthname(str_to_date(0, '%m')); # +# Bug #16327: problem with timestamp < 1970 +# + +set time_zone='-6:00'; +create table t1(a timestamp); +insert into t1 values (19691231190001); +select * from t1; +drop table t1; + +# # Bug#16377 result of DATE/TIME functions were compared as strings which # can lead to a wrong result. # diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test index ceba78bf762..178816cbf43 100644 --- a/mysql-test/t/mysqlbinlog.test +++ b/mysql-test/t/mysqlbinlog.test @@ -44,21 +44,21 @@ select "--- Local --" as ""; # --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ $MYSQLTEST_VARDIR/log/master-bin.000001 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ $MYSQLTEST_VARDIR/log/master-bin.000001 # this should not fail but shouldn't produce any working statements --disable_query_log select "--- Broken LOAD DATA --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ $MYSQLTEST_VARDIR/log/master-bin.000002 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ $MYSQLTEST_VARDIR/log/master-bin.000002 2> /dev/null # this should show almost nothing --disable_query_log select "--- --database --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --database=nottest $MYSQLTEST_VARDIR/log/master-bin.000001 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --database=nottest $MYSQLTEST_VARDIR/log/master-bin.000001 2> /dev/null # this test for position option --disable_query_log @@ -83,14 +83,14 @@ select "--- Remote --" as ""; select "--- Broken LOAD DATA --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 2> /dev/null # And this too ! (altough it is documented) --disable_query_log select "--- --database --" as ""; --enable_query_log --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT --database=nottest master-bin.000001 +--exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT --database=nottest master-bin.000001 2> /dev/null # Strangely but this works --disable_query_log diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 3052a8c9161..322e21d989b 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -5950,6 +5950,33 @@ call bug15217()| 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 +# set an empty string as the current database. They do not belong to any +# database and must be usable even if there is no database +# selected. +--disable_warnings +drop table if exists t3| +drop database if exists mysqltest1| +--enable_warnings +create table t3 (a int)| +insert into t3 (a) values (1), (2)| + +create database mysqltest1| +use mysqltest1| +drop database mysqltest1| + +# No current database +select database()| + +select * from (select 1 as a) as t1 natural join (select * from test.t3) as t2| +use test| +drop table t3| + + # # BUG#19862: Sort with filesort by function evaluates function twice # @@ -5968,6 +5995,8 @@ SELECT bug19862(a) FROM t12 ORDER BY 1| SELECT * FROM t11| DROP TABLE t11, t12| DROP FUNCTION bug19862| + + # # BUG#NNNN: New bug synopsis # diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test index 560ec88eb10..c8843e42f87 100644 --- a/mysql-test/t/udf.test +++ b/mysql-test/t/udf.test @@ -1,6 +1,6 @@ --source include/have_udf.inc # -# To run this tests the "sql/udf_example.cc" need to be compiled into +# To run this tests the "sql/udf_example.c" need to be compiled into # udf_example.so and LD_LIBRARY_PATH should be setup to point out where # the library are. # diff --git a/scripts/make_binary_distribution.sh b/scripts/make_binary_distribution.sh index 58799880769..df07dd8ef9b 100644 --- a/scripts/make_binary_distribution.sh +++ b/scripts/make_binary_distribution.sh @@ -98,7 +98,7 @@ mkdir $BASE $BASE/bin $BASE/docs \ if [ $BASE_SYSTEM != "netware" ] ; then mkdir $BASE/share/mysql $BASE/tests $BASE/man \ - $BASE/man/man1 $BASE/data $BASE/data/mysql $BASE/data/test + $BASE/man/man1 $BASE/man/man8 $BASE/data $BASE/data/mysql $BASE/data/test chmod o-rwx $BASE/data $BASE/data/* fi @@ -222,6 +222,7 @@ if [ $BASE_SYSTEM != "netware" ] ; then fi if [ -d man ] ; then $CP man/*.1 $BASE/man/man1 + $CP man/*.8 $BASE/man/man8 fi fi @@ -313,6 +314,11 @@ else rm -f $BASE/README.NW fi +# Make safe_mysqld a symlink to mysqld_safe for backwards portability +if [ $BASE_SYSTEM != "netware" ] ; then + (cd $BASE/bin ; ln -s mysqld_safe safe_mysqld ) +fi + # Clean up if we did this from a bk tree if [ -d $BASE/share/SCCS ] ; then find $BASE/share -name SCCS -print | xargs rm -rf diff --git a/server-tools/instance-manager/mysqlmanager.vcproj b/server-tools/instance-manager/mysqlmanager.vcproj index bbcb94fa221..d835e242eb1 100644 --- a/server-tools/instance-manager/mysqlmanager.vcproj +++ b/server-tools/instance-manager/mysqlmanager.vcproj @@ -37,7 +37,7 @@ OutputFile="../../client_debug/mysqlmanager.exe" LinkIncremental="2" GenerateDebugInformation="TRUE" - ProgramDatabaseFile="$(OutDir)/mysqlmanager.pdb" + ProgramDatabaseFile="../../client_debug/mysqlmanager.pdb" SubSystem="1" TargetMachine="1"/> <Tool diff --git a/sql/Makefile.am b/sql/Makefile.am index e453bd6010f..31d6a327c06 100644 --- a/sql/Makefile.am +++ b/sql/Makefile.am @@ -122,7 +122,7 @@ DEFS = -DMYSQL_SERVER \ @DEFS@ BUILT_SOURCES = sql_yacc.cc sql_yacc.h lex_hash.h -EXTRA_DIST = udf_example.cc $(BUILT_SOURCES) \ +EXTRA_DIST = udf_example.c $(BUILT_SOURCES) \ nt_servc.cc nt_servc.h message.mc CMakeLists.txt CLEANFILES = lex_hash.h sql_yacc.cc sql_yacc.h AM_YFLAGS = -d @@ -176,7 +176,7 @@ handler.o: handler.cc ha_ndbcluster.h # For testing of udf_example.so noinst_LTLIBRARIES= udf_example.la -udf_example_la_SOURCES= udf_example.cc +udf_example_la_SOURCES= udf_example.c udf_example_la_LDFLAGS= -module -rpath $(pkglibdir) diff --git a/sql/field.cc b/sql/field.cc index 53948f571c5..4e3c54fbd63 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -4722,7 +4722,7 @@ String *Field_timestamp::val_str(String *val_buffer, String *val_ptr) thd->time_zone_used= 1; temp= time_tmp.year % 100; - if (temp < YY_PART_YEAR) + if (temp < YY_PART_YEAR - 1) { *to++= '2'; *to++= '0'; diff --git a/sql/sp.cc b/sql/sp.cc index e794a461402..c1bfc4f26cd 100644 --- a/sql/sp.cc +++ b/sql/sp.cc @@ -1841,7 +1841,6 @@ sp_use_new_db(THD *thd, LEX_STRING new_db, LEX_STRING *old_db, bool no_access_check, bool *dbchangedp) { int ret; - static char empty_c_string[1]= {0}; /* used for not defined db */ DBUG_ENTER("sp_use_new_db"); DBUG_PRINT("enter", ("newdb: %s", new_db.str)); diff --git a/sql/sql_class.cc b/sql/sql_class.cc index be8cf76b573..827a12e4ebd 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -45,6 +45,7 @@ table name */ char internal_table_name[2]= "*"; +char empty_c_string[1]= {0}; /* used for not defined db */ const char * const THD::DEFAULT_WHERE= "field list"; diff --git a/sql/sql_class.h b/sql/sql_class.h index 01b28eaee96..31b97cf5982 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -43,6 +43,7 @@ enum enum_mark_columns { MARK_COLUMNS_NONE, MARK_COLUMNS_READ, MARK_COLUMNS_WRITE}; extern char internal_table_name[2]; +extern char empty_c_string[1]; extern const char **errmesg; #define TC_LOG_PAGE_SIZE 8192 @@ -1986,11 +1987,21 @@ public: { db.str=0; } - inline Table_ident(SELECT_LEX_UNIT *s) : sel(s) + /* + This constructor is used only for the case when we create a derived + table. A derived table has no name and doesn't belong to any database. + Later, if there was an alias specified for the table, it will be set + by add_table_to_list. + */ + inline Table_ident(SELECT_LEX_UNIT *s) : sel(s) { /* We must have a table name here as this is used with add_table_to_list */ - db.str=0; table.str= internal_table_name; table.length=1; + db.str= empty_c_string; /* a subject to casedn_str */ + db.length= 0; + table.str= internal_table_name; + table.length=1; } + bool is_derived_table() const { return test(sel); } inline void change_db(char *db_name) { db.str= db_name; db.length= (uint) strlen(db_name); diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 30dea60f7a2..555cac03c64 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -6380,7 +6380,7 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, if (!table) DBUG_RETURN(0); // End of memory alias_str= alias ? alias->str : table->table.str; - if (check_table_name(table->table.str,table->table.length)) + if (check_table_name(table->table.str, table->table.length)) { my_error(ER_WRONG_TABLE_NAME, MYF(0), table->table.str); DBUG_RETURN(0); @@ -6406,6 +6406,11 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, DBUG_RETURN(0); /* purecov: inspected */ if (table->db.str) { + if (table->is_derived_table() == FALSE && check_db_name(table->db.str)) + { + my_error(ER_WRONG_DB_NAME, MYF(0), table->db.str); + DBUG_RETURN(0); + } ptr->db= table->db.str; ptr->db_length= table->db.length; } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 36f87a7fe6d..7eda55c6a3f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4214,14 +4214,14 @@ greedy_search(JOIN *join, double read_time= 0.0; uint idx= join->const_tables; // index into 'join->best_ref' uint best_idx; - uint rem_size; // cardinality of remaining_tables + uint size_remain; // cardinality of remaining_tables POSITION best_pos; JOIN_TAB *best_table; // the next plan node to be added to the curr QEP DBUG_ENTER("greedy_search"); /* number of tables that remain to be optimized */ - rem_size= my_count_bits(remaining_tables); + size_remain= my_count_bits(remaining_tables); do { /* Find the extension of the current QEP with the lowest cost */ @@ -4229,7 +4229,7 @@ greedy_search(JOIN *join, best_extension_by_limited_search(join, remaining_tables, idx, record_count, read_time, search_depth, prune_level); - if (rem_size <= search_depth) + if (size_remain <= search_depth) { /* 'join->best_positions' contains a complete optimal extension of the @@ -4265,7 +4265,7 @@ greedy_search(JOIN *join, read_time+= join->positions[idx].read_time; remaining_tables&= ~(best_table->table->map); - --rem_size; + --size_remain; ++idx; DBUG_EXECUTE("opt", print_plan(join, join->tables, @@ -8599,13 +8599,15 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, &bitmaps, bitmap_buffer_size(field_count)*2, NullS)) { - bitmap_lock_clear_bit(&temp_pool, temp_pool_slot); + if (temp_pool_slot != MY_BIT_NONE) + bitmap_lock_clear_bit(&temp_pool, temp_pool_slot); DBUG_RETURN(NULL); /* purecov: inspected */ } /* Copy_field belongs to TMP_TABLE_PARAM, allocate it in THD mem_root */ if (!(param->copy_field= copy= new (thd->mem_root) Copy_field[field_count])) { - bitmap_lock_clear_bit(&temp_pool, temp_pool_slot); + if (temp_pool_slot != MY_BIT_NONE) + bitmap_lock_clear_bit(&temp_pool, temp_pool_slot); free_root(&own_root, MYF(0)); /* purecov: inspected */ DBUG_RETURN(NULL); /* purecov: inspected */ } @@ -9132,7 +9134,8 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, err: thd->mem_root= mem_root_save; free_tmp_table(thd,table); /* purecov: inspected */ - bitmap_lock_clear_bit(&temp_pool, temp_pool_slot); + if (temp_pool_slot != MY_BIT_NONE) + bitmap_lock_clear_bit(&temp_pool, temp_pool_slot); DBUG_RETURN(NULL); /* purecov: inspected */ } @@ -9420,7 +9423,8 @@ free_tmp_table(THD *thd, TABLE *entry) (*ptr)->free(); free_io_cache(entry); - bitmap_lock_clear_bit(&temp_pool, entry->temp_pool_slot); + if (entry->temp_pool_slot != MY_BIT_NONE) + bitmap_lock_clear_bit(&temp_pool, entry->temp_pool_slot); free_root(&own_root, MYF(0)); /* the table is allocated in its own root */ thd->proc_info=save_proc_info; diff --git a/sql/udf_example.cc b/sql/udf_example.c index 6ad066eacc2..62995085599 100644 --- a/sql/udf_example.cc +++ b/sql/udf_example.c @@ -127,7 +127,7 @@ typedef long long longlong; #else #include <my_global.h> #include <my_sys.h> -#include <m_string.h> // To get strmov() +#include <m_string.h> /* To get strmov() */ #endif #include <mysql.h> #include <ctype.h> @@ -138,7 +138,6 @@ static pthread_mutex_t LOCK_hostname; /* These must be right or mysqld will not find the symbol! */ -extern "C" { my_bool metaphon_init(UDF_INIT *initid, UDF_ARGS *args, char *message); void metaphon_deinit(UDF_INIT *initid); char *metaphon(UDF_INIT *initid, UDF_ARGS *args, char *result, @@ -159,7 +158,6 @@ void avgcost_reset( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error void avgcost_clear( UDF_INIT* initid, char* is_null, char *error ); void avgcost_add( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error ); double avgcost( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error ); -} /************************************************************************* @@ -221,7 +219,7 @@ my_bool metaphon_init(UDF_INIT *initid, UDF_ARGS *args, char *message) ****************************************************************************/ -void metaphon_deinit(UDF_INIT *initid) +void metaphon_deinit(UDF_INIT *initid __attribute__((unused))) { } @@ -267,23 +265,25 @@ static char codes[26] = { #define NOGHTOF(x) (codes[(x) - 'A'] & 16) /* BDH */ -char *metaphon(UDF_INIT *initid, UDF_ARGS *args, char *result, - unsigned long *length, char *is_null, char *error) +char *metaphon(UDF_INIT *initid __attribute__((unused)), + UDF_ARGS *args, char *result, unsigned long *length, + char *is_null, char *error __attribute__((unused))) { const char *word=args->args[0]; - if (!word) // Null argument + const char *w_end; + char *org_result; + char *n, *n_start, *n_end; /* pointers to string */ + char *metaph_end; /* pointers to end of metaph */ + char ntrans[32]; /* word with uppercase letters */ + int KSflag; /* state flag for X to KS */ + + if (!word) /* Null argument */ { *is_null=1; return 0; } - const char *w_end=word+args->lengths[0]; - char *org_result=result; - - char *n, *n_start, *n_end; /* pointers to string */ - char *metaph, *metaph_end; /* pointers to metaph */ - char ntrans[32]; /* word with uppercase letters */ - char newm[8]; /* new metaph for comparison */ - int KSflag; /* state flag for X to KS */ + w_end=word+args->lengths[0]; + org_result=result; /*-------------------------------------------------------- * Copy word to internal buffer, dropping non-alphabetic @@ -519,6 +519,8 @@ char *metaphon(UDF_INIT *initid, UDF_ARGS *args, char *result, my_bool myfunc_double_init(UDF_INIT *initid, UDF_ARGS *args, char *message) { + uint i; + if (!args->arg_count) { strcpy(message,"myfunc_double must have at least one argument"); @@ -528,27 +530,28 @@ my_bool myfunc_double_init(UDF_INIT *initid, UDF_ARGS *args, char *message) ** As this function wants to have everything as strings, force all arguments ** to strings. */ - for (uint i=0 ; i < args->arg_count; i++) + for (i=0 ; i < args->arg_count; i++) args->arg_type[i]=STRING_RESULT; - initid->maybe_null=1; // The result may be null - initid->decimals=2; // We want 2 decimals in the result - initid->max_length=6; // 3 digits + . + 2 decimals + initid->maybe_null=1; /* The result may be null */ + initid->decimals=2; /* We want 2 decimals in the result */ + initid->max_length=6; /* 3 digits + . + 2 decimals */ return 0; } -double myfunc_double(UDF_INIT *initid, UDF_ARGS *args, char *is_null, - char *error) +double myfunc_double(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args, + char *is_null, char *error __attribute__((unused))) { unsigned long val = 0; unsigned long v = 0; + uint i, j; - for (uint i = 0; i < args->arg_count; i++) + for (i = 0; i < args->arg_count; i++) { if (args->args[i] == NULL) continue; val += args->lengths[i]; - for (uint j=args->lengths[i] ; j-- > 0 ;) + for (j=args->lengths[i] ; j-- > 0 ;) v += args->args[i][j]; } if (val) @@ -575,22 +578,25 @@ double myfunc_double(UDF_INIT *initid, UDF_ARGS *args, char *is_null, /* This function returns the sum of all arguments */ -longlong myfunc_int(UDF_INIT *initid, UDF_ARGS *args, char *is_null, - char *error) +longlong myfunc_int(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args, + char *is_null __attribute__((unused)), + char *error __attribute__((unused))) { longlong val = 0; - for (uint i = 0; i < args->arg_count; i++) + uint i; + + for (i = 0; i < args->arg_count; i++) { if (args->args[i] == NULL) continue; switch (args->arg_type[i]) { - case STRING_RESULT: // Add string lengths + case STRING_RESULT: /* Add string lengths */ val += args->lengths[i]; break; - case INT_RESULT: // Add numbers + case INT_RESULT: /* Add numbers */ val += *((longlong*) args->args[i]); break; - case REAL_RESULT: // Add numers as longlong + case REAL_RESULT: /* Add numers as longlong */ val += (longlong) *((double*) args->args[i]); break; default: @@ -604,7 +610,9 @@ longlong myfunc_int(UDF_INIT *initid, UDF_ARGS *args, char *is_null, At least one of _init/_deinit is needed unless the server is started with --allow_suspicious_udfs. */ -my_bool myfunc_int_init(UDF_INIT *initid, UDF_ARGS *args, char *message) +my_bool myfunc_int_init(UDF_INIT *initid __attribute__((unused)), + UDF_ARGS *args __attribute__((unused)), + char *message __attribute__((unused))) { return 0; } @@ -622,7 +630,7 @@ my_bool sequence_init(UDF_INIT *initid, UDF_ARGS *args, char *message) return 1; } if (args->arg_count) - args->arg_type[0]= INT_RESULT; // Force argument to int + args->arg_type[0]= INT_RESULT; /* Force argument to int */ if (!(initid->ptr=(char*) malloc(sizeof(longlong)))) { @@ -646,8 +654,9 @@ void sequence_deinit(UDF_INIT *initid) free(initid->ptr); } -longlong sequence(UDF_INIT *initid, UDF_ARGS *args, char *is_null, - char *error) +longlong sequence(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args, + char *is_null __attribute__((unused)), + char *error __attribute__((unused))) { ulonglong val=0; if (args->arg_count) @@ -670,7 +679,6 @@ longlong sequence(UDF_INIT *initid, UDF_ARGS *args, char *is_null, #include <arpa/inet.h> #include <netdb.h> -extern "C" { my_bool lookup_init(UDF_INIT *initid, UDF_ARGS *args, char *message); void lookup_deinit(UDF_INIT *initid); char *lookup(UDF_INIT *initid, UDF_ARGS *args, char *result, @@ -679,7 +687,6 @@ my_bool reverse_lookup_init(UDF_INIT *initid, UDF_ARGS *args, char *message); void reverse_lookup_deinit(UDF_INIT *initid); char *reverse_lookup(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *null_value, char *error); -} /**************************************************************************** @@ -705,20 +712,26 @@ my_bool lookup_init(UDF_INIT *initid, UDF_ARGS *args, char *message) return 0; } -void lookup_deinit(UDF_INIT *initid) +void lookup_deinit(UDF_INIT *initid __attribute__((unused))) { #if !defined(HAVE_GETHOSTBYADDR_R) || !defined(HAVE_SOLARIS_STYLE_GETHOST) (void) pthread_mutex_destroy(&LOCK_hostname); #endif } -char *lookup(UDF_INIT *initid, UDF_ARGS *args, char *result, - unsigned long *res_length, char *null_value, char *error) +char *lookup(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args, + char *result, unsigned long *res_length, char *null_value, + char *error __attribute__((unused))) { uint length; + char name_buff[256]; + struct hostent *hostent; +#if defined(HAVE_GETHOSTBYADDR_R) && defined(HAVE_SOLARIS_STYLE_GETHOST) int tmp_errno; - char name_buff[256],hostname_buff[2048]; - struct hostent tmp_hostent,*hostent; + char hostname_buff[2048]; + struct hostent tmp_hostent; +#endif + struct in_addr in; if (!args->args[0] || !(length=args->lengths[0])) { @@ -746,7 +759,6 @@ char *lookup(UDF_INIT *initid, UDF_ARGS *args, char *result, } VOID(pthread_mutex_unlock(&LOCK_hostname)); #endif - struct in_addr in; memcpy_fixed((char*) &in,(char*) *hostent->h_addr_list, sizeof(in.s_addr)); *res_length= (ulong) (strmov(result, inet_ntoa(in)) - result); return result; @@ -780,18 +792,23 @@ my_bool reverse_lookup_init(UDF_INIT *initid, UDF_ARGS *args, char *message) return 0; } -void reverse_lookup_deinit(UDF_INIT *initid) +void reverse_lookup_deinit(UDF_INIT *initid __attribute__((unused))) { #if !defined(HAVE_GETHOSTBYADDR_R) || !defined(HAVE_SOLARIS_STYLE_GETHOST) (void) pthread_mutex_destroy(&LOCK_hostname); #endif } -char *reverse_lookup(UDF_INIT *initid, UDF_ARGS *args, char *result, - unsigned long *res_length, char *null_value, char *error) +char *reverse_lookup(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args, + char *result, unsigned long *res_length, + char *null_value, char *error __attribute__((unused))) { +#if defined(HAVE_GETHOSTBYADDR_R) && defined(HAVE_SOLARIS_STYLE_GETHOST) char name_buff[256]; struct hostent tmp_hostent; +#endif + struct hostent *hp; + unsigned long taddr; uint length; if (args->arg_count == 4) @@ -808,8 +825,8 @@ char *reverse_lookup(UDF_INIT *initid, UDF_ARGS *args, char *result, (int) *((longlong*) args->args[3])); } else - { // string argument - if (!args->args[0]) // Return NULL for NULL values + { /* string argument */ + if (!args->args[0]) /* Return NULL for NULL values */ { *null_value=1; return 0; @@ -821,13 +838,12 @@ char *reverse_lookup(UDF_INIT *initid, UDF_ARGS *args, char *result, result[length]=0; } - unsigned long taddr = inet_addr(result); + taddr = inet_addr(result); if (taddr == (unsigned long) -1L) { *null_value=1; return 0; } - struct hostent *hp; #if defined(HAVE_GETHOSTBYADDR_R) && defined(HAVE_SOLARIS_STYLE_GETHOST) int tmp_errno; if (!(hp=gethostbyaddr_r((char*) &taddr,sizeof(taddr), AF_INET, @@ -902,11 +918,15 @@ avgcost_init( UDF_INIT* initid, UDF_ARGS* args, char* message ) /*args->arg_type[0] = REAL_RESULT; args->arg_type[1] = REAL_RESULT;*/ - initid->maybe_null = 0; // The result may be null - initid->decimals = 4; // We want 4 decimals in the result - initid->max_length = 20; // 6 digits + . + 10 decimals + initid->maybe_null = 0; /* The result may be null */ + initid->decimals = 4; /* We want 4 decimals in the result */ + initid->max_length = 20; /* 6 digits + . + 10 decimals */ - data = new struct avgcost_data; + if (!(data = (struct avgcost_data*) malloc(sizeof(struct avgcost_data)))) + { + strmov(message,"Couldn't allocate memory"); + return 1; + } data->totalquantity = 0; data->totalprice = 0.0; @@ -918,7 +938,7 @@ avgcost_init( UDF_INIT* initid, UDF_ARGS* args, char* message ) void avgcost_deinit( UDF_INIT* initid ) { - delete initid->ptr; + free(initid->ptr); } @@ -933,7 +953,8 @@ avgcost_reset(UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message) /* This is needed to get things to work in MySQL 4.1.1 and above */ void -avgcost_clear(UDF_INIT* initid, char* is_null, char* message) +avgcost_clear(UDF_INIT* initid, char* is_null __attribute__((unused)), + char* message __attribute__((unused))) { struct avgcost_data* data = (struct avgcost_data*)initid->ptr; data->totalprice= 0.0; @@ -943,7 +964,9 @@ avgcost_clear(UDF_INIT* initid, char* is_null, char* message) void -avgcost_add(UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message) +avgcost_add(UDF_INIT* initid, UDF_ARGS* args, + char* is_null __attribute__((unused)), + char* message __attribute__((unused))) { if (args->args[0] && args->args[1]) { @@ -963,7 +986,7 @@ avgcost_add(UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message) if ( ((quantity < 0) && (newquantity < 0)) || ((quantity > 0) && (newquantity > 0)) ) { - data->totalprice = price * double(newquantity); + data->totalprice = price * (double)newquantity; } /* ** sub q if totalq > 0 @@ -971,15 +994,15 @@ avgcost_add(UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message) */ else { - price = data->totalprice / double(data->totalquantity); - data->totalprice = price * double(newquantity); + price = data->totalprice / (double)data->totalquantity; + data->totalprice = price * (double)newquantity; } data->totalquantity = newquantity; } else { data->totalquantity += quantity; - data->totalprice += price * double(quantity); + data->totalprice += price * (double)quantity; } if (data->totalquantity == 0) @@ -989,7 +1012,8 @@ avgcost_add(UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message) double -avgcost( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* error ) +avgcost( UDF_INIT* initid, UDF_ARGS* args __attribute__((unused)), + char* is_null, char* error __attribute__((unused))) { struct avgcost_data* data = (struct avgcost_data*)initid->ptr; if (!data->count || !data->totalquantity) @@ -999,16 +1023,14 @@ avgcost( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* error ) } *is_null = 0; - return data->totalprice/double(data->totalquantity); + return data->totalprice/(double)data->totalquantity; } -extern "C" { my_bool myfunc_argument_name_init(UDF_INIT *initid, UDF_ARGS *args, char *message); char *myfunc_argument_name(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *null_value, char *error); -} my_bool myfunc_argument_name_init(UDF_INIT *initid, UDF_ARGS *args, char *message) @@ -1024,16 +1046,17 @@ my_bool myfunc_argument_name_init(UDF_INIT *initid, UDF_ARGS *args, return 0; } -char *myfunc_argument_name(UDF_INIT *initid, UDF_ARGS *args, char *result, - unsigned long *length, char *null_value, - char *error) +char *myfunc_argument_name(UDF_INIT *initid __attribute__((unused)), + UDF_ARGS *args, char *result, + unsigned long *length, char *null_value, + char *error __attribute__((unused))) { if (!args->attributes[0]) { null_value= 0; return 0; } - (*length)--; // space for ending \0 (for debugging purposes) + (*length)--; /* space for ending \0 (for debugging purposes) */ if (*length > args->attribute_lengths[0]) *length= args->attribute_lengths[0]; memcpy(result, args->attributes[0], *length); diff --git a/support-files/mysql.spec.sh b/support-files/mysql.spec.sh index 8dbf1bef7fe..d3a57aaa65c 100644 --- a/support-files/mysql.spec.sh +++ b/support-files/mysql.spec.sh @@ -557,7 +557,7 @@ fi %doc %attr(644, root, man) %{_mandir}/man1/myisamlog.1* %doc %attr(644, root, man) %{_mandir}/man1/myisampack.1* %doc %attr(644, root, man) %{_mandir}/man1/mysql_explain_log.1* -%doc %attr(644, root, man) %{_mandir}/man1/mysqld.1* +%doc %attr(644, root, man) %{_mandir}/man8/mysqld.8* %doc %attr(644, root, man) %{_mandir}/man1/mysqld_multi.1* %doc %attr(644, root, man) %{_mandir}/man1/mysqld_safe.1* %doc %attr(644, root, man) %{_mandir}/man1/mysql_fix_privilege_tables.1* diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index eb5465d4d35..924f9169f5a 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -15227,6 +15227,51 @@ static void test_bug20152() DIE_UNLESS(0==1); } } + + +/* + Bug#21206: memory corruption when too many cursors are opened at once + + Memory corruption happens when more than 1024 cursors are open + simultaneously. +*/ +static void test_bug21206() +{ + const size_t cursor_count= 1025; + + const char *create_table[]= + { + "DROP TABLE IF EXISTS t1", + "CREATE TABLE t1 (i INT)", + "INSERT INTO t1 VALUES (1), (2), (3)" + }; + const char *query= "SELECT * FROM t1"; + + Stmt_fetch *fetch_array= + (Stmt_fetch*) calloc(cursor_count, sizeof(Stmt_fetch)); + + Stmt_fetch *fetch; + + DBUG_ENTER("test_bug21206"); + myheader("test_bug21206"); + + fill_tables(create_table, sizeof(create_table) / sizeof(*create_table)); + + for (fetch= fetch_array; fetch < fetch_array + cursor_count; ++fetch) + { + /* Init will exit(1) in case of error */ + stmt_fetch_init(fetch, fetch - fetch_array, query); + } + + for (fetch= fetch_array; fetch < fetch_array + cursor_count; ++fetch) + stmt_fetch_close(fetch); + + free(fetch_array); + + DBUG_VOID_RETURN; +} + + /* Read and parse arguments and MySQL options from my.cnf */ @@ -15501,6 +15546,7 @@ static struct my_tests_st my_tests[]= { { "test_bug17667", test_bug17667 }, { "test_mysql_insert_id", test_mysql_insert_id }, { "test_bug19671", test_bug19671}, + { "test_bug21206", test_bug21206}, { 0, 0 } }; |