summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-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
-rw-r--r--mysql-test/t/disabled.def1
-rw-r--r--mysql-test/t/rpl_insert_id.test70
-rw-r--r--mysql-test/t/sp-goto.test238
-rw-r--r--mysql-test/t/sp.test75
-rw-r--r--mysql-test/t/timezone_grant.test31
-rw-r--r--mysql-test/t/trigger.test26
-rw-r--r--mysql-test/t/view.test27
-rw-r--r--sql/item_func.cc22
-rw-r--r--sql/lex.h7
-rw-r--r--sql/sp.cc20
-rw-r--r--sql/sp_head.cc37
-rw-r--r--sql/sp_head.h7
-rw-r--r--sql/sp_pcontext.cc2
-rw-r--r--sql/sp_pcontext.h7
-rw-r--r--sql/sql_class.cc5
-rw-r--r--sql/sql_view.cc16
-rw-r--r--sql/sql_yacc.yy90
23 files changed, 462 insertions, 616 deletions
diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result
index d7b6946f7e5..b11f1b92020 100644
--- a/mysql-test/r/rpl_insert_id.result
+++ b/mysql-test/r/rpl_insert_id.result
@@ -74,3 +74,61 @@ SET FOREIGN_KEY_CHECKS=0;
INSERT INTO t1 VALUES (1),(1);
ERROR 23000: Duplicate entry '1' for key 1
drop table t1;
+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 dadcab76947..59ce1d13d2b 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -4848,4 +4848,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 3758f3c2645..2f4d46dfdc0 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 681b805f547..a5cec65b454 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 b52e6b58c0e..890e5be66c6 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;
diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def
index a836b1a2897..37ba97851aa 100644
--- a/mysql-test/t/disabled.def
+++ b/mysql-test/t/disabled.def
@@ -10,6 +10,5 @@
#
##############################################################################
-sp-goto : GOTO is currently is disabled - will be fixed in the future
ndb_load : Bug#17233
udf : Bug#18564 (Permission by Brian)
diff --git a/mysql-test/t/rpl_insert_id.test b/mysql-test/t/rpl_insert_id.test
index f025ae9e587..ccd80dce388 100644
--- a/mysql-test/t/rpl_insert_id.test
+++ b/mysql-test/t/rpl_insert_id.test
@@ -78,3 +78,73 @@ connection master;
drop table t1;
sync_slave_with_master;
# End of 4.1 tests
+
+
+#
+# BUG#15728: LAST_INSERT_ID function inside a stored function returns 0
+#
+# The solution is not to reset last_insert_id on enter to sub-statement.
+#
+connection master;
+--disable_warnings
+drop function if exists bug15728;
+drop function if exists bug15728_insert;
+drop table if exists t1, t2;
+--enable_warnings
+
+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());
+
+# Check that nested call replicates too.
+create table t2 (
+ id int not null auto_increment,
+ last_id int,
+ primary key (id)
+);
+delimiter |;
+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|
+delimiter ;|
+
+insert into t1 (last_id) values (0);
+
+drop trigger t1_bi;
+
+# Check that nested call doesn't affect outer context.
+select last_insert_id();
+select bug15728_insert();
+select last_insert_id();
+insert into t1 (last_id) values (bug15728());
+# This should be exactly one greater than in the previous call.
+select last_insert_id();
+
+save_master_pos;
+connection slave;
+sync_with_master;
+select * from t1;
+select * from t2;
+connection master;
+
+drop function bug15728;
+drop function bug15728_insert;
+drop table t1, t2;
+
+# End of 5.0 tests
diff --git a/mysql-test/t/sp-goto.test b/mysql-test/t/sp-goto.test
deleted file mode 100644
index e770dd285ff..00000000000
--- a/mysql-test/t/sp-goto.test
+++ /dev/null
@@ -1,238 +0,0 @@
-#
-# The non-standard GOTO, for compatibility
-#
-# QQQ The "label" syntax is temporary, it will (hopefully)
-# change to the more common "L:" syntax soon.
-# For the time being, this feature is disabled, until
-# the syntax (and some other known bugs) can be fixed.
-#
-# Test cases for bugs are added at the end. See template there.
-#
-
---disable_warnings
-drop table if exists t1;
---enable_warnings
-create table t1 (
- id char(16) not null default '',
- data int not null
-);
-
-delimiter //;
-
---disable_warnings
-drop procedure if exists goto1//
---enable_warnings
-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()//
-drop procedure goto1//
-
-# With dummy handlers, just to test restore of contexts with jumps
---disable_warnings
-drop procedure if exists goto2//
---enable_warnings
-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)//
-call goto2(1)//
-call goto2(2)//
-
-drop procedure goto2//
-delete from t1//
-
-# Check label visibility for some more cases. We don't call these.
---disable_warnings
-drop procedure if exists goto3//
---enable_warnings
-create procedure goto3()
-begin
- label L1;
- begin
- end;
- goto L1;
-end//
-drop procedure goto3//
-
---disable_warnings
-drop procedure if exists goto4//
---enable_warnings
-create procedure goto4()
-begin
- begin
- label lab1;
- begin
- goto lab1;
- end;
- end;
-end//
-drop procedure goto4//
-
---disable_warnings
-drop procedure if exists goto5//
---enable_warnings
-create procedure goto5()
-begin
- begin
- begin
- goto lab1;
- end;
- label lab1;
- end;
-end//
-drop procedure goto5//
-
---disable_warnings
-drop procedure if exists goto6//
---enable_warnings
-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//
-
-# Mismatching labels
---error 1308
-create procedure foo()
-begin
- goto foo;
-end//
---error 1308
-create procedure foo()
-begin
- begin
- label foo;
- end;
- goto foo;
-end//
---error 1308
-create procedure foo()
-begin
- goto foo;
- begin
- label foo;
- end;
-end//
---error 1308
-create procedure foo()
-begin
- begin
- goto foo;
- end;
- begin
- label foo;
- end;
-end//
---error 1308
-create procedure foo()
-begin
- begin
- label foo;
- end;
- begin
- goto foo;
- end;
-end//
-
-# No goto in a handler
---error 1358
-create procedure p()
-begin
- declare continue handler for sqlexception
- begin
- goto L1;
- end;
-
- select field from t1;
- label L1;
-end//
-
-
-#
-# Test cases for old bugs
-#
-
-#
-# BUG#6898: Stored procedure crash if GOTO statements exist
-#
---disable_warnings
-drop procedure if exists bug6898//
---enable_warnings
-create procedure bug6898()
-begin
- goto label1;
- label label1;
- begin end;
- goto label1;
-end//
-drop procedure bug6898//
-
-#
-# BUG#NNNN: New bug synopsis
-#
-#--disable_warnings
-#drop procedure if exists bugNNNN//
-#--enable_warnings
-#create procedure bugNNNN...
-
-
-# Add bugs above this line. Use existing tables t1 and t2 when
-# practical, or create table t3, t4 etc temporarily (and drop them).
-delimiter ;//
-drop table t1;
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 1658e06d518..22500bbd280 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -5699,6 +5699,81 @@ delete from t1|
#
+# BUG#15728: LAST_INSERT_ID function inside a stored function returns 0
+#
+# The solution is not to reset last_insert_id on enter to sub-statement.
+#
+--disable_warnings
+drop function if exists bug15728|
+drop table if exists t3|
+--enable_warnings
+
+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()|
+select bug15728()|
+
+drop function bug15728|
+drop table t3|
+
+
+#
+# BUG#18787: Server crashed when calling a stored procedure containing
+# a misnamed function
+#
+--disable_warnings
+drop procedure if exists bug18787|
+--enable_warnings
+create procedure bug18787()
+begin
+ declare continue handler for sqlexception begin end;
+
+ select no_such_function();
+end|
+
+call bug18787()|
+drop procedure bug18787|
+
+
+#
+# BUG#18344: DROP DATABASE does not drop associated routines
+# (... if the database name is longer than 21 characters)
+#
+# 1234567890123456789012
+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%'|
+select routine_name,routine_schema from information_schema.routines where
+ routine_schema like 'bug18344%'|
+
+drop database bug18344_012345678901|
+drop database bug18344_0123456789012|
+
+# Should be nothing left.
+select schema_name from information_schema.schemata where
+ schema_name like 'bug18344%'|
+select routine_name,routine_schema from information_schema.routines where
+ routine_schema like 'bug18344%'|
+
+
+#
# BUG#NNNN: New bug synopsis
#
#--disable_warnings
diff --git a/mysql-test/t/timezone_grant.test b/mysql-test/t/timezone_grant.test
index f94d86eb266..450c1edc47e 100644
--- a/mysql-test/t/timezone_grant.test
+++ b/mysql-test/t/timezone_grant.test
@@ -1,6 +1,11 @@
# Embedded server testing does not support grants
-- source include/not_embedded.inc
+--disable_warnings
+drop tables if exists t1, t2;
+drop view if exists v1;
+--enable_warnings
+
#
# Test for bug #6116 "SET time_zone := ... requires access to mysql.time_zone
# tables". We should allow implicit access to time zone description tables
@@ -82,3 +87,29 @@ flush privileges;
drop table t1, t2;
# End of 4.1 tests
+
+#
+# Additional test for bug #15153: CONVERT_TZ() is not allowed in all
+# places in views.
+#
+# Let us check that usage of CONVERT_TZ() function in view does not
+# require additional privileges.
+
+# Let us rely on that previous tests done proper cleanups
+create table t1 (a int, b datetime);
+insert into t1 values (1, 20010101000000), (2, 20020101000000);
+grant all privileges on test.* to mysqltest_1@localhost;
+connect (tzuser3, localhost, mysqltest_1,,);
+create view v1 as select a, convert_tz(b, 'UTC', 'Europe/Moscow') as lb from t1;
+select * from v1;
+# Of course we should not be able select from mysql.time_zone tables
+--error ER_TABLEACCESS_DENIED_ERROR
+select * from v1, mysql.time_zone;
+drop view v1;
+--error ER_TABLEACCESS_DENIED_ERROR
+create view v1 as select a, convert_tz(b, 'UTC', 'Europe/Moscow') as lb from t1, mysql.time_zone;
+connection default;
+drop table t1;
+drop user mysqltest_1@localhost;
+
+# End of 5.0 tests
diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index a0b67b2204d..ae05d70bf67 100644
--- a/mysql-test/t/trigger.test
+++ b/mysql-test/t/trigger.test
@@ -1111,11 +1111,35 @@ 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;
#
+# Test for bug #16021 "Wrong index given to function in trigger" which
+# was caused by the same bulk insert optimization as bug #17764 but had
+# slightly different symptoms (instead of reporting table as crashed
+# storage engine reported error number 124)
+#
+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));
+delimiter |;
+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|
+delimiter ;|
+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;
+select * from t2;
+drop tables t1, t2;
+
+#
# Test for Bug #16461 connection_id() does not work properly inside trigger
#
--disable_warnings
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 81fb161b69a..8f759c2d43e 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -2485,3 +2485,30 @@ rename table v2 to t1;
select * from v1;
drop function f1;
drop view t1, v1;
+
+#
+# Bug #15153: CONVERT_TZ() is not allowed in all places in VIEWs
+#
+# Error was reported when one tried to use CONVERT_TZ() function
+# select list of view which was processed using MERGE algorithm.
+# (Also see additional test in timezone_grant.test)
+create table t1 (dt datetime);
+insert into t1 values (20040101000000), (20050101000000), (20060101000000);
+# Let us test that convert_tz() can be used in view's select list
+create view v1 as select convert_tz(dt, 'UTC', 'Europe/Moscow') as ldt from t1;
+select * from v1;
+drop view v1;
+# And in its where part
+create view v1 as select * from t1 where convert_tz(dt, 'UTC', 'Europe/Moscow') >= 20050101000000;
+select * from v1;
+# Other interesting case - a view which uses convert_tz() function
+# through other view.
+create view v2 as select * from v1 where dt < 20060101000000;
+select * from v2;
+drop view v2;
+# And even more interesting case when view uses convert_tz() both
+# directly and indirectly
+create view v2 as select convert_tz(dt, 'UTC', 'Europe/Moscow') as ldt from v1;
+select * from v2;
+drop view v1, v2;
+drop table t1;
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 4bdb62c6e7a..bd59a750bfc 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -4744,7 +4744,9 @@ Item_func_sp::sp_result_field(void) const
share->table_cache_key = empty_name;
share->table_name = empty_name;
}
- field= m_sp->create_result_field(max_length, name, dummy_table);
+ if (!(field= m_sp->create_result_field(max_length, name, dummy_table)))
+ my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
+
DBUG_RETURN(field);
}
@@ -4772,8 +4774,9 @@ Item_func_sp::execute(Field **flp)
{
if (!(*flp= f= sp_result_field()))
{
- my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
- return 0;
+ /* Error set by sp_result_field() */
+ null_value= 1;
+ return TRUE;
}
f->move_field((f->pack_length() > sizeof(result_buf)) ?
@@ -4928,16 +4931,19 @@ longlong Item_func_found_rows::val_int()
Field *
Item_func_sp::tmp_table_field(TABLE *t_arg)
{
- Field *res= 0;
+ Field *field= 0;
DBUG_ENTER("Item_func_sp::tmp_table_field");
if (m_sp)
- res= m_sp->create_result_field(max_length, (const char*) name, t_arg);
+ field= m_sp->create_result_field(max_length, (const char*) name, t_arg);
- if (!res)
- res= Item_func::tmp_table_field(t_arg);
+ if (!field)
+ field= Item_func::tmp_table_field(t_arg);
- DBUG_RETURN(res);
+ if (!field)
+ my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
+
+ DBUG_RETURN(field);
}
diff --git a/sql/lex.h b/sql/lex.h
index 1acfbaac211..68f34d8de93 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -215,9 +215,6 @@ static SYMBOL symbols[] = {
{ "GEOMETRYCOLLECTION",SYM(GEOMETRYCOLLECTION)},
{ "GET_FORMAT", SYM(GET_FORMAT)},
{ "GLOBAL", SYM(GLOBAL_SYM)},
-#ifdef SP_GOTO
- { "GOTO", SYM(GOTO_SYM)},
-#endif
{ "GRANT", SYM(GRANT)},
{ "GRANTS", SYM(GRANTS)},
{ "GROUP", SYM(GROUP)},
@@ -265,10 +262,6 @@ static SYMBOL symbols[] = {
{ "KEY", SYM(KEY_SYM)},
{ "KEYS", SYM(KEYS)},
{ "KILL", SYM(KILL_SYM)},
-#ifdef SP_GOTO
- /* QQ This will go away when the GOTO label syntax is fixed */
- { "LABEL", SYM(LABEL_SYM)},
-#endif
{ "LANGUAGE", SYM(LANGUAGE_SYM)},
{ "LAST", SYM(LAST_SYM)},
{ "LEADING", SYM(LEADING)},
diff --git a/sql/sp.cc b/sql/sp.cc
index cfcf011032d..7df22c92cb8 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -886,28 +886,23 @@ int
sp_drop_db_routines(THD *thd, char *db)
{
TABLE *table;
- byte key[64]; // db
- uint keylen;
int ret;
+ uint key_len;
DBUG_ENTER("sp_drop_db_routines");
DBUG_PRINT("enter", ("db: %s", db));
- // Put the key used to read the row together
- keylen= strlen(db);
- if (keylen > 64)
- keylen= 64;
- memcpy(key, db, keylen);
- memset(key+keylen, (int)' ', 64-keylen); // Pad with space
- keylen= sizeof(key);
-
ret= SP_OPEN_TABLE_FAILED;
if (!(table= open_proc_table_for_update(thd)))
goto err;
+ table->field[MYSQL_PROC_FIELD_DB]->store(db, strlen(db), system_charset_info);
+ key_len= table->key_info->key_part[0].store_length;
+
ret= SP_OK;
table->file->ha_index_init(0);
if (! table->file->index_read(table->record[0],
- key, keylen, HA_READ_KEY_EXACT))
+ (byte *)table->field[MYSQL_PROC_FIELD_DB]->ptr,
+ key_len, HA_READ_KEY_EXACT))
{
int nxtres;
bool deleted= FALSE;
@@ -923,7 +918,8 @@ sp_drop_db_routines(THD *thd, char *db)
break;
}
} while (! (nxtres= table->file->index_next_same(table->record[0],
- key, keylen)));
+ (byte *)table->field[MYSQL_PROC_FIELD_DB]->ptr,
+ key_len)));
if (nxtres != HA_ERR_END_OF_FILE)
ret= SP_KEY_NOT_FOUND;
if (deleted)
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 15d621b1d6d..6b7cdb1ea98 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -1670,44 +1670,11 @@ sp_head::backpatch(sp_label_t *lab)
while ((bp= li++))
{
- if (bp->lab == lab ||
- (bp->lab->type == SP_LAB_REF &&
- my_strcasecmp(system_charset_info, bp->lab->name, lab->name) == 0))
- {
- if (bp->lab->type != SP_LAB_REF)
- bp->instr->backpatch(dest, lab->ctx);
- else
- {
- sp_label_t *dstlab= bp->lab->ctx->find_label(lab->name);
-
- if (dstlab)
- {
- bp->lab= lab;
- bp->instr->backpatch(dest, dstlab->ctx);
- }
- }
- }
- }
-}
-
-int
-sp_head::check_backpatch(THD *thd)
-{
- bp_t *bp;
- List_iterator_fast<bp_t> li(m_backpatch);
-
- while ((bp= li++))
- {
- if (bp->lab->type == SP_LAB_REF)
- {
- my_error(ER_SP_LILABEL_MISMATCH, MYF(0), "GOTO", bp->lab->name);
- return -1;
- }
+ if (bp->lab == lab)
+ bp->instr->backpatch(dest, lab->ctx);
}
- return 0;
}
-
/*
Prepare an instance of create_field for field creation (fill all necessary
attributes).
diff --git a/sql/sp_head.h b/sql/sp_head.h
index 17a5d1ae528..3ad81542ce7 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -263,13 +263,6 @@ public:
void
backpatch(struct sp_label *);
- // Check that no unresolved references exist.
- // If none found, 0 is returned, otherwise errors have been issued
- // and -1 is returned.
- // This is called by the parser at the end of a create procedure/function.
- int
- check_backpatch(THD *thd);
-
// Start a new cont. backpatch level. If 'i' is NULL, the level is just incr.
void
new_cont_backpatch(sp_instr_opt_meta *i);
diff --git a/sql/sp_pcontext.cc b/sql/sp_pcontext.cc
index 448df908a32..b0b65d5313b 100644
--- a/sql/sp_pcontext.cc
+++ b/sql/sp_pcontext.cc
@@ -241,7 +241,7 @@ sp_pcontext::push_label(char *name, uint ip)
{
lab->name= name;
lab->ip= ip;
- lab->type= SP_LAB_GOTO;
+ lab->type= SP_LAB_IMPL;
lab->ctx= this;
m_label.push_front(lab);
}
diff --git a/sql/sp_pcontext.h b/sql/sp_pcontext.h
index e61057537da..2ee77696efb 100644
--- a/sql/sp_pcontext.h
+++ b/sql/sp_pcontext.h
@@ -48,10 +48,9 @@ typedef struct sp_variable
} sp_variable_t;
-#define SP_LAB_REF 0 // Unresolved reference (for goto)
-#define SP_LAB_GOTO 1 // Free goto label
-#define SP_LAB_BEGIN 2 // Label at BEGIN
-#define SP_LAB_ITER 3 // Label at iteration control
+#define SP_LAB_IMPL 0 // Implicit label generated by parser
+#define SP_LAB_BEGIN 1 // Label at BEGIN
+#define SP_LAB_ITER 2 // Label at iteration control
/*
An SQL/PSM label. Can refer to the identifier used with the
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index d56f10a7a30..026c3e0d515 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -2025,7 +2025,7 @@ void THD::restore_backup_open_tables_state(Open_tables_state *backup)
The following things is done
- Disable binary logging for the duration of the statement
- Disable multi-result-sets for the duration of the statement
- - Value of last_insert_id() is reset and restored
+ - Value of last_insert_id() is saved and restored
- Value set by 'SET INSERT_ID=#' is reset and restored
- Value for found_rows() is reset and restored
- examined_row_count is added to the total
@@ -2037,6 +2037,8 @@ void THD::restore_backup_open_tables_state(Open_tables_state *backup)
We reset examined_row_count and cuted_fields and add these to the
result to ensure that if we have a bug that would reset these within
a function, we are not loosing any rows from the main statement.
+
+ We do not reset value of last_insert_id().
****************************************************************************/
void THD::reset_sub_statement_state(Sub_statement_state *backup,
@@ -2062,7 +2064,6 @@ void THD::reset_sub_statement_state(Sub_statement_state *backup,
/* Disable result sets */
client_capabilities &= ~CLIENT_MULTI_RESULTS;
in_sub_stmt|= new_state;
- last_insert_id= 0;
next_insert_id= 0;
insert_id_used= 0;
examined_row_count= 0;
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index cdb6c581565..0fb043430a4 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -1057,15 +1057,23 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table)
!old_lex->can_not_use_merged())
{
List_iterator_fast<TABLE_LIST> ti(view_select->top_join_list);
+ /*
+ Currently 'view_main_select_tables' differs from 'view_tables'
+ only then view has CONVERT_TZ() function in its select list.
+ This may change in future, for example if we enable merging
+ of views with subqueries in select list.
+ */
+ TABLE_LIST *view_main_select_tables=
+ (TABLE_LIST*)lex->select_lex.table_list.first;
/* lex should contain at least one table */
- DBUG_ASSERT(view_tables != 0);
+ DBUG_ASSERT(view_main_select_tables != 0);
table->effective_algorithm= VIEW_ALGORITHM_MERGE;
DBUG_PRINT("info", ("algorithm: MERGE"));
table->updatable= (table->updatable_view != 0);
table->effective_with_check=
old_lex->get_effective_with_check(table);
- table->merge_underlying_list= view_tables;
+ table->merge_underlying_list= view_main_select_tables;
/*
Let us set proper lock type for tables of the view's main select
since we may want to perform update or insert on view. This won't
@@ -1081,7 +1089,7 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table)
}
/* prepare view context */
- lex->select_lex.context.resolve_in_table_list_only(view_tables);
+ lex->select_lex.context.resolve_in_table_list_only(view_main_select_tables);
lex->select_lex.context.outer_context= 0;
lex->select_lex.context.select_lex= table->select_lex;
lex->select_lex.select_n_having_items+=
@@ -1097,7 +1105,7 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table)
tbl->select_lex= table->select_lex;
{
- if (view_tables->next_local)
+ if (view_main_select_tables->next_local)
{
table->multitable_view= TRUE;
if (table->belong_to_view)
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index f3f0990c917..6473163a6ec 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -301,7 +301,6 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
%token GEOMFROMWKB
%token GET_FORMAT
%token GLOBAL_SYM
-%token GOTO_SYM
%token GRANT
%token GRANTS
%token GREATEST_SYM
@@ -1332,8 +1331,6 @@ create_function_tail:
if (sp->is_not_allowed_in_function("function"))
YYABORT;
- if (sp->check_backpatch(YYTHD))
- YYABORT;
lex->sql_command= SQLCOM_CREATE_SPFUNCTION;
sp->init_strings(YYTHD, lex, lex->spname);
if (!(sp->m_flags & sp_head::HAS_RETURN))
@@ -2054,91 +2051,6 @@ sp_proc_stmt:
sp->add_instr(i);
}
}
- | LABEL_SYM IDENT
- {
-#ifdef SP_GOTO
- LEX *lex= Lex;
- sp_head *sp= lex->sphead;
- sp_pcontext *ctx= lex->spcont;
- sp_label_t *lab= ctx->find_label($2.str);
-
- if (lab)
- {
- my_error(ER_SP_LABEL_REDEFINE, MYF(0), $2.str);
- YYABORT;
- }
- else
- {
- lab= ctx->push_label($2.str, sp->instructions());
- lab->type= SP_LAB_GOTO;
- lab->ctx= ctx;
- sp->backpatch(lab);
- }
-#else
- yyerror(ER(ER_SYNTAX_ERROR));
- YYABORT;
-#endif
- }
- | GOTO_SYM IDENT
- {
-#ifdef SP_GOTO
- LEX *lex= Lex;
- sp_head *sp= lex->sphead;
- sp_pcontext *ctx= lex->spcont;
- uint ip= lex->sphead->instructions();
- sp_label_t *lab;
- sp_instr_jump *i;
- sp_instr_hpop *ih;
- sp_instr_cpop *ic;
-
- if (sp->m_in_handler)
- {
- my_message(ER_SP_GOTO_IN_HNDLR, ER(ER_SP_GOTO_IN_HNDLR), MYF(0));
- YYABORT;
- }
- lab= ctx->find_label($2.str);
- if (! lab)
- {
- lab= (sp_label_t *)YYTHD->alloc(sizeof(sp_label_t));
- lab->name= $2.str;
- lab->ip= 0;
- lab->type= SP_LAB_REF;
- lab->ctx= ctx;
-
- ih= new sp_instr_hpop(ip++, ctx, 0);
- sp->push_backpatch(ih, lab);
- sp->add_instr(ih);
- ic= new sp_instr_cpop(ip++, ctx, 0);
- sp->add_instr(ic);
- sp->push_backpatch(ic, lab);
- i= new sp_instr_jump(ip, ctx);
- sp->push_backpatch(i, lab); /* Jumping forward */
- sp->add_instr(i);
- }
- else
- {
- uint n;
-
- n= ctx->diff_handlers(lab->ctx);
- if (n)
- {
- ih= new sp_instr_hpop(ip++, ctx, n);
- sp->add_instr(ih);
- }
- n= ctx->diff_cursors(lab->ctx);
- if (n)
- {
- ic= new sp_instr_cpop(ip++, ctx, n);
- sp->add_instr(ic);
- }
- i= new sp_instr_jump(ip, ctx, lab->ip); /* Jump back */
- sp->add_instr(i);
- }
-#else
- yyerror(ER(ER_SYNTAX_ERROR));
- YYABORT;
-#endif
- }
| OPEN_SYM ident
{
LEX *lex= Lex;
@@ -9246,8 +9158,6 @@ sp_tail:
LEX *lex= Lex;
sp_head *sp= lex->sphead;
- if (sp->check_backpatch(YYTHD))
- YYABORT;
sp->init_strings(YYTHD, lex, $3);
lex->sql_command= SQLCOM_CREATE_PROCEDURE;
/* Restore flag if it was cleared above */
View Lorry Controller Status