summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/derived.result2
-rw-r--r--mysql-test/r/sp.result15
-rw-r--r--mysql-test/r/sp.result.orig4853
-rw-r--r--mysql-test/t/derived.test2
-rw-r--r--mysql-test/t/sp.test25
-rw-r--r--sql/sp.cc1
-rw-r--r--sql/sql_class.cc1
-rw-r--r--sql/sql_class.h13
-rw-r--r--sql/sql_parse.cc8
9 files changed, 61 insertions, 4859 deletions
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/sp.result b/mysql-test/r/sp.result
index 96bf2f01f86..229dab72422 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -5057,4 +5057,19 @@ 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 table t1,t2;
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/sp.test b/mysql-test/t/sp.test
index 25c96042e6f..9a0003bab9c 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -5963,6 +5963,31 @@ 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#NNNN: New bug synopsis
#
#--disable_warnings
diff --git a/sql/sp.cc b/sql/sp.cc
index 553465ebff8..a7078da2f50 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -1846,7 +1846,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 06082a57964..0bface8509a 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -43,6 +43,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 eb075dd54bb..62cd73c38ff 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -41,6 +41,7 @@ enum enum_check_fields { CHECK_FIELD_IGNORE, CHECK_FIELD_WARN,
CHECK_FIELD_ERROR_FOR_NULL };
extern char internal_table_name[2];
+extern char empty_c_string[1];
extern const char **errmesg;
#define TC_LOG_PAGE_SIZE 8192
@@ -1977,11 +1978,21 @@ public:
{
db.str=0;
}
+ /*
+ 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 cd57c280950..4c542234d4f 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -6088,8 +6088,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) ||
- table->db.str && check_db_name(table->db.str))
+ if (check_table_name(table->table.str, table->table.length))
{
my_error(ER_WRONG_TABLE_NAME, MYF(0), table->table.str);
DBUG_RETURN(0);
@@ -6110,6 +6109,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;
}
View Lorry Controller Status