summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/rpl_insert_id.result58
-rw-r--r--mysql-test/r/sp-goto.result205
-rw-r--r--mysql-test/r/sp.result56
-rw-r--r--mysql-test/r/timezone_grant.result17
-rw-r--r--mysql-test/r/trigger.result35
-rw-r--r--mysql-test/r/view.result26
6 files changed, 191 insertions, 206 deletions
diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result
index 4504ad8e5a7..622b1489f91 100644
--- a/mysql-test/r/rpl_insert_id.result
+++ b/mysql-test/r/rpl_insert_id.result
@@ -73,3 +73,61 @@ CREATE TABLE t1 ( a INT UNIQUE );
SET FOREIGN_KEY_CHECKS=0;
INSERT INTO t1 VALUES (1),(1);
Got one of the listed errors
+drop function if exists bug15728;
+drop function if exists bug15728_insert;
+drop table if exists t1, t2;
+create table t1 (
+id int not null auto_increment,
+last_id int,
+primary key (id)
+);
+create function bug15728() returns int(11)
+return last_insert_id();
+insert into t1 (last_id) values (0);
+insert into t1 (last_id) values (last_insert_id());
+insert into t1 (last_id) values (bug15728());
+create table t2 (
+id int not null auto_increment,
+last_id int,
+primary key (id)
+);
+create function bug15728_insert() returns int(11) modifies sql data
+begin
+insert into t2 (last_id) values (bug15728());
+return bug15728();
+end|
+create trigger t1_bi before insert on t1 for each row
+begin
+declare res int;
+select bug15728_insert() into res;
+set NEW.last_id = res;
+end|
+insert into t1 (last_id) values (0);
+drop trigger t1_bi;
+select last_insert_id();
+last_insert_id()
+4
+select bug15728_insert();
+bug15728_insert()
+2
+select last_insert_id();
+last_insert_id()
+4
+insert into t1 (last_id) values (bug15728());
+select last_insert_id();
+last_insert_id()
+5
+select * from t1;
+id last_id
+1 0
+2 1
+3 2
+4 1
+5 4
+select * from t2;
+id last_id
+1 3
+2 4
+drop function bug15728;
+drop function bug15728_insert;
+drop table t1, t2;
diff --git a/mysql-test/r/sp-goto.result b/mysql-test/r/sp-goto.result
deleted file mode 100644
index ca560f62318..00000000000
--- a/mysql-test/r/sp-goto.result
+++ /dev/null
@@ -1,205 +0,0 @@
-drop table if exists t1;
-create table t1 (
-id char(16) not null default '',
-data int not null
-);
-drop procedure if exists goto1//
-create procedure goto1()
-begin
-declare y int;
-label a;
-select * from t1;
-select count(*) into y from t1;
-if y > 2 then
-goto b;
-end if;
-insert into t1 values ("j", y);
-goto a;
-label b;
-end//
-call goto1()//
-id data
-id data
-j 0
-id data
-j 0
-j 1
-id data
-j 0
-j 1
-j 2
-drop procedure goto1//
-drop procedure if exists goto2//
-create procedure goto2(a int)
-begin
-declare x int default 0;
-declare continue handler for sqlstate '42S98' set x = 1;
-label a;
-select * from t1;
-b:
-while x < 2 do
-begin
-declare continue handler for sqlstate '42S99' set x = 2;
-if a = 0 then
-set x = x + 1;
-iterate b;
-elseif a = 1 then
-leave b;
-elseif a = 2 then
-set a = 1;
-goto a;
-end if;
-end;
-end while b;
-select * from t1;
-end//
-call goto2(0)//
-id data
-j 0
-j 1
-j 2
-id data
-j 0
-j 1
-j 2
-call goto2(1)//
-id data
-j 0
-j 1
-j 2
-id data
-j 0
-j 1
-j 2
-call goto2(2)//
-id data
-j 0
-j 1
-j 2
-id data
-j 0
-j 1
-j 2
-id data
-j 0
-j 1
-j 2
-drop procedure goto2//
-delete from t1//
-drop procedure if exists goto3//
-create procedure goto3()
-begin
-label L1;
-begin
-end;
-goto L1;
-end//
-drop procedure goto3//
-drop procedure if exists goto4//
-create procedure goto4()
-begin
-begin
-label lab1;
-begin
-goto lab1;
-end;
-end;
-end//
-drop procedure goto4//
-drop procedure if exists goto5//
-create procedure goto5()
-begin
-begin
-begin
-goto lab1;
-end;
-label lab1;
-end;
-end//
-drop procedure goto5//
-drop procedure if exists goto6//
-create procedure goto6()
-begin
-label L1;
-goto L5;
-begin
-label L2;
-goto L1;
-goto L5;
-begin
-label L3;
-goto L1;
-goto L2;
-goto L3;
-goto L4;
-goto L5;
-end;
-goto L2;
-goto L4;
-label L4;
-end;
-label L5;
-goto L1;
-end//
-drop procedure goto6//
-create procedure foo()
-begin
-goto foo;
-end//
-ERROR 42000: GOTO with no matching label: foo
-create procedure foo()
-begin
-begin
-label foo;
-end;
-goto foo;
-end//
-ERROR 42000: GOTO with no matching label: foo
-create procedure foo()
-begin
-goto foo;
-begin
-label foo;
-end;
-end//
-ERROR 42000: GOTO with no matching label: foo
-create procedure foo()
-begin
-begin
-goto foo;
-end;
-begin
-label foo;
-end;
-end//
-ERROR 42000: GOTO with no matching label: foo
-create procedure foo()
-begin
-begin
-label foo;
-end;
-begin
-goto foo;
-end;
-end//
-ERROR 42000: GOTO with no matching label: foo
-create procedure p()
-begin
-declare continue handler for sqlexception
-begin
-goto L1;
-end;
-select field from t1;
-label L1;
-end//
-ERROR HY000: GOTO is not allowed in a stored procedure handler
-drop procedure if exists bug6898//
-create procedure bug6898()
-begin
-goto label1;
-label label1;
-begin end;
-goto label1;
-end//
-drop procedure bug6898//
-drop table t1;
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 4468991c0ef..5aa984bfa4e 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -4858,4 +4858,60 @@ c 2
b 3
a 1
delete from t1|
+drop function if exists bug15728|
+drop table if exists t3|
+create table t3 (
+id int not null auto_increment,
+primary key (id)
+)|
+create function bug15728() returns int(11)
+return last_insert_id()|
+insert into t3 values (0)|
+select last_insert_id()|
+last_insert_id()
+1
+select bug15728()|
+bug15728()
+1
+drop function bug15728|
+drop table t3|
+drop procedure if exists bug18787|
+create procedure bug18787()
+begin
+declare continue handler for sqlexception begin end;
+select no_such_function();
+end|
+call bug18787()|
+no_such_function()
+NULL
+drop procedure bug18787|
+create database bug18344_012345678901|
+use bug18344_012345678901|
+create procedure bug18344() begin end|
+create procedure bug18344_2() begin end|
+create database bug18344_0123456789012|
+use bug18344_0123456789012|
+create procedure bug18344() begin end|
+create procedure bug18344_2() begin end|
+use test|
+select schema_name from information_schema.schemata where
+schema_name like 'bug18344%'|
+schema_name
+bug18344_012345678901
+bug18344_0123456789012
+select routine_name,routine_schema from information_schema.routines where
+routine_schema like 'bug18344%'|
+routine_name routine_schema
+bug18344 bug18344_012345678901
+bug18344_2 bug18344_012345678901
+bug18344 bug18344_0123456789012
+bug18344_2 bug18344_0123456789012
+drop database bug18344_012345678901|
+drop database bug18344_0123456789012|
+select schema_name from information_schema.schemata where
+schema_name like 'bug18344%'|
+schema_name
+select routine_name,routine_schema from information_schema.routines where
+routine_schema like 'bug18344%'|
+routine_name routine_schema
drop table t1,t2;
diff --git a/mysql-test/r/timezone_grant.result b/mysql-test/r/timezone_grant.result
index 77cfe1c151e..49918038da5 100644
--- a/mysql-test/r/timezone_grant.result
+++ b/mysql-test/r/timezone_grant.result
@@ -1,3 +1,5 @@
+drop tables if exists t1, t2;
+drop view if exists v1;
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
delete from mysql.tables_priv where user like 'mysqltest\_%';
@@ -59,3 +61,18 @@ delete from mysql.db where user like 'mysqltest\_%';
delete from mysql.tables_priv where user like 'mysqltest\_%';
flush privileges;
drop table t1, t2;
+create table t1 (a int, b datetime);
+insert into t1 values (1, 20010101000000), (2, 20020101000000);
+grant all privileges on test.* to mysqltest_1@localhost;
+create view v1 as select a, convert_tz(b, 'UTC', 'Europe/Moscow') as lb from t1;
+select * from v1;
+a lb
+1 2001-01-01 03:00:00
+2 2002-01-01 03:00:00
+select * from v1, mysql.time_zone;
+ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'time_zone'
+drop view v1;
+create view v1 as select a, convert_tz(b, 'UTC', 'Europe/Moscow') as lb from t1, mysql.time_zone;
+ERROR 42000: ANY command denied to user 'mysqltest_1'@'localhost' for table 'time_zone'
+drop table t1;
+drop user mysqltest_1@localhost;
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index 7cc3a954cd6..a0c4e212d58 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -949,9 +949,42 @@ insert into t1 values
create function f2() returns int return (select max(b) from t2);
insert into t2 select a, f2() from t1;
load data infile '../std_data_ln/words.dat' into table t1 (a) set b:= f1();
-drop table t1;
+drop tables t1, t2;
drop function f1;
drop function f2;
+create table t1(i int not null, j int not null, n numeric(15,2), primary key(i,j));
+create table t2(i int not null, n numeric(15,2), primary key(i));
+create trigger t1_ai after insert on t1 for each row
+begin
+declare sn numeric(15,2);
+select sum(n) into sn from t1 where i=new.i;
+replace into t2 values(new.i, sn);
+end|
+insert into t1 values
+(1,1,10.00),(1,2,10.00),(1,3,10.00),(1,4,10.00),(1,5,10.00),
+(1,6,10.00),(1,7,10.00),(1,8,10.00),(1,9,10.00),(1,10,10.00),
+(1,11,10.00),(1,12,10.00),(1,13,10.00),(1,14,10.00),(1,15,10.00);
+select * from t1;
+i j n
+1 1 10.00
+1 2 10.00
+1 3 10.00
+1 4 10.00
+1 5 10.00
+1 6 10.00
+1 7 10.00
+1 8 10.00
+1 9 10.00
+1 10 10.00
+1 11 10.00
+1 12 10.00
+1 13 10.00
+1 14 10.00
+1 15 10.00
+select * from t2;
+i n
+1 150.00
+drop tables t1, t2;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
conn_id INT,
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index fd6a6dbd3d3..91ee6716723 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2623,3 +2623,29 @@ select * from v1;
ERROR HY000: Recursive stored functions and triggers are not allowed.
drop function f1;
drop view t1, v1;
+create table t1 (dt datetime);
+insert into t1 values (20040101000000), (20050101000000), (20060101000000);
+create view v1 as select convert_tz(dt, 'UTC', 'Europe/Moscow') as ldt from t1;
+select * from v1;
+ldt
+2004-01-01 03:00:00
+2005-01-01 03:00:00
+2006-01-01 03:00:00
+drop view v1;
+create view v1 as select * from t1 where convert_tz(dt, 'UTC', 'Europe/Moscow') >= 20050101000000;
+select * from v1;
+dt
+2005-01-01 00:00:00
+2006-01-01 00:00:00
+create view v2 as select * from v1 where dt < 20060101000000;
+select * from v2;
+dt
+2005-01-01 00:00:00
+drop view v2;
+create view v2 as select convert_tz(dt, 'UTC', 'Europe/Moscow') as ldt from v1;
+select * from v2;
+ldt
+2005-01-01 03:00:00
+2006-01-01 03:00:00
+drop view v1, v2;
+drop table t1;
View Lorry Controller Status