Below is the list of changes that have just been committed into a local
5.0 repository of pem. When pem does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet
1.2102 06/03/03 19:18:17 pem@stripped +3 -0
Merge mysql.com:/extern/mysql/5.0/bug16887/mysql-5.0-runtime
into mysql.com:/extern/mysql/5.0/bug16887/mysql-5.0
mysql-test/t/sp.test
1.176 06/03/03 19:18:14 pem@stripped +1 -0
Manual merge.
mysql-test/r/sp.result
1.187 06/03/03 19:18:14 pem@stripped +0 -0
Manual merge.
sql/sql_yacc.yy
1.455 06/03/03 19:09:52 pem@stripped +0 -0
Auto merged
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: pem
# Host: pem.mysql.com
# Root: /extern/mysql/5.0/bug16887/mysql-5.0/RESYNC
--- 1.454/sql/sql_yacc.yy 2006-02-15 12:11:25 +01:00
+++ 1.455/sql/sql_yacc.yy 2006-03-03 19:09:52 +01:00
@@ -628,6 +628,7 @@
%token UNTIL_SYM
%token UPDATE_SYM
%token UPDATE_SYM
+%token UPGRADE_SYM
%token USAGE
%token USER
%token USE_FRM
@@ -684,7 +685,7 @@
LEX_HOSTNAME ULONGLONG_NUM field_ident select_alias ident ident_or_text
UNDERSCORE_CHARSET IDENT_sys TEXT_STRING_sys TEXT_STRING_literal
NCHAR_STRING opt_component key_cache_name
- sp_opt_label BIN_NUM label_ident
+ sp_opt_label BIN_NUM label_ident TEXT_STRING_filesystem
%type <lex_str_ptr>
opt_table_alias
@@ -3846,7 +3847,8 @@
| FAST_SYM { Lex->check_opt.flags|= T_FAST; }
| MEDIUM_SYM { Lex->check_opt.flags|= T_MEDIUM; }
| EXTENDED_SYM { Lex->check_opt.flags|= T_EXTEND; }
- | CHANGED { Lex->check_opt.flags|= T_CHECK_ONLY_CHANGED; };
+ | CHANGED { Lex->check_opt.flags|= T_CHECK_ONLY_CHANGED; }
+ | FOR_SYM UPGRADE_SYM { Lex->check_opt.sql_flags|= TT_FOR_UPGRADE; };
optimize:
OPTIMIZE opt_no_write_to_binlog table_or_tables
@@ -5947,7 +5949,7 @@
;
into:
- INTO OUTFILE TEXT_STRING_sys
+ INTO OUTFILE TEXT_STRING_filesystem
{
LEX *lex= Lex;
lex->uncacheable(UNCACHEABLE_SIDEEFFECT);
@@ -5956,7 +5958,7 @@
YYABORT;
}
opt_field_term opt_line_term
- | INTO DUMPFILE TEXT_STRING_sys
+ | INTO DUMPFILE TEXT_STRING_filesystem
{
LEX *lex=Lex;
if (!lex->describe)
@@ -6940,7 +6942,7 @@
};
load_data:
- load_data_lock opt_local INFILE TEXT_STRING_sys
+ load_data_lock opt_local INFILE TEXT_STRING_filesystem
{
LEX *lex=Lex;
lex->sql_command= SQLCOM_LOAD;
@@ -7469,6 +7471,18 @@
;
+TEXT_STRING_filesystem:
+ TEXT_STRING
+ {
+ THD *thd= YYTHD;
+ if (thd->charset_is_character_set_filesystem)
+ $$= $1;
+ else
+ thd->convert_string(&$$, thd->variables.character_set_filesystem,
+ $1.str, $1.length, thd->charset());
+ }
+ ;
+
ident:
IDENT_sys { $$=$1; }
| keyword
@@ -9112,8 +9126,8 @@
**************************************************************************/
trigger_tail:
- TRIGGER_SYM remember_name sp_name trg_action_time trg_event
- ON table_ident FOR_SYM EACH_SYM ROW_SYM
+ TRIGGER_SYM remember_name sp_name trg_action_time trg_event
+ ON remember_name table_ident remember_end FOR_SYM EACH_SYM ROW_SYM
{
LEX *lex= Lex;
sp_head *sp;
@@ -9130,7 +9144,9 @@
sp->init(lex);
lex->trigger_definition_begin= $2;
-
+ lex->ident.str= $7;
+ lex->ident.length= $9 - $7;
+
sp->m_type= TYPE_ENUM_TRIGGER;
lex->sphead= sp;
lex->spname= $3;
@@ -9167,15 +9183,11 @@
We have to do it after parsing trigger body, because some of
sp_proc_stmt alternatives are not saving/restoring LEX, so
lex->query_tables can be wiped out.
-
- QQ: What are other consequences of this?
-
- QQ: Could we loosen lock type in certain cases ?
*/
- if (!lex->select_lex.add_table_to_list(YYTHD, $7,
+ if (!lex->select_lex.add_table_to_list(YYTHD, $8,
(LEX_STRING*) 0,
TL_OPTION_UPDATING,
- TL_WRITE))
+ TL_IGNORE))
YYABORT;
}
;
--- 1.186/mysql-test/r/sp.result 2006-02-15 12:11:25 +01:00
+++ 1.187/mysql-test/r/sp.result 2006-03-03 19:18:14 +01:00
@@ -539,7 +539,7 @@
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;
+select * into outfile "../tmp/spout" from test.t1;
insert into test.t1 values (concat(x, "2"), y+2);
end|
call into_outfile("ofile", 1)|
@@ -549,7 +549,7 @@
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;
+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)|
@@ -1413,8 +1413,6 @@
5
drop function `foo`|
drop function if exists t1max|
-Warnings:
-Note 1305 FUNCTION t1max does not exist
create function t1max() returns int
begin
declare x int;
@@ -1470,6 +1468,339 @@
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
@@ -2132,7 +2463,6 @@
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;
@@ -2159,8 +2489,6 @@
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
@@ -2213,8 +2541,6 @@
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
@@ -2250,18 +2576,6 @@
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
@@ -2404,52 +2718,6 @@
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|
@@ -2665,17 +2933,6 @@
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)|
@@ -4021,14 +4278,6 @@
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|
@@ -4519,6 +4768,39 @@
Inner
drop procedure bug15011|
drop table t3|
+drop function if exists bug17615|
+create table t3 (a varchar(256) unicode)|
+create function bug17615() returns varchar(256) unicode
+begin
+declare tmp_res varchar(256) unicode;
+set tmp_res= 'foo string';
+return tmp_res;
+end|
+insert into t3 values(bug17615())|
+select * from t3|
+a
+foo string
+drop function bug17615|
+drop table t3|
+drop procedure if exists bug17476|
+create table t3 ( d date )|
+insert into t3 values
+( '2005-01-01' ), ( '2005-01-02' ), ( '2005-01-03' ),
+( '2005-01-04' ), ( '2005-02-01' ), ( '2005-02-02' )|
+create procedure bug17476(pDateFormat varchar(10))
+select date_format(t3.d, pDateFormat), count(*)
+from t3
+group by date_format(t3.d, pDateFormat)|
+call bug17476('%Y-%m')|
+date_format(t3.d, pDateFormat) count(*)
+2005-01 4
+2005-02 2
+call bug17476('%Y-%m')|
+date_format(t3.d, pDateFormat) count(*)
+2005-01 4
+2005-02 2
+drop table t3|
+drop procedure bug17476|
drop table if exists t3|
drop procedure if exists bug16887|
create table t3 ( c varchar(1) )|
--- 1.175/mysql-test/t/sp.test 2006-02-15 12:11:25 +01:00
+++ 1.176/mysql-test/t/sp.test 2006-03-03 19:18:14 +01:00
@@ -699,32 +699,34 @@
--disable_warnings
drop procedure if exists into_outfile|
--enable_warnings
-create procedure into_outfile(x char(16), y int)
+--replace_result $MYSQLTEST_VARDIR ..
+eval 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;
+ select * into outfile "$MYSQLTEST_VARDIR/tmp/spout" from test.t1;
insert into test.t1 values (concat(x, "2"), y+2);
end|
-system rm -f /tmp/spout|
+--system rm -f $MYSQLTEST_VARDIR/tmp/spout
call into_outfile("ofile", 1)|
-system rm -f /tmp/spout|
+--system rm -f $MYSQLTEST_VARDIR/tmp/spout
delete from t1|
drop procedure into_outfile|
--disable_warnings
drop procedure if exists into_dumpfile|
--enable_warnings
-create procedure into_dumpfile(x char(16), y int)
+--replace_result $MYSQLTEST_VARDIR ..
+eval 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;
+ select * into dumpfile "$MYSQLTEST_VARDIR/tmp/spdump" from test.t1 limit 1;
insert into test.t1 values (concat(x, "2"), y+2);
end|
-system rm -f /tmp/spdump|
+--system rm -f $MYSQLTEST_VARDIR/tmp/spdump
call into_dumpfile("dfile", 1)|
-system rm -f /tmp/spdump|
+--system rm -f $MYSQLTEST_VARDIR/tmp/spdump
delete from t1|
drop procedure into_dumpfile|
@@ -1531,7 +1533,7 @@
end while;
end|
show create procedure opp|
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+--replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
show procedure status like '%p%'|
# This isn't the fastest way in the world to compute prime numbers, so
@@ -1549,7 +1551,7 @@
drop table t3|
drop procedure opp|
drop procedure ip|
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+--replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
show procedure status like '%p%'|
@@ -1618,13 +1620,13 @@
create procedure bar(x char(16), y int)
comment "111111111111" sql security invoker
insert into test.t1 values (x, y)|
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+--replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
show procedure status like 'bar'|
alter procedure bar comment "2222222222" sql security definer|
alter procedure bar comment "3333333333"|
alter procedure bar|
show create procedure bar|
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+--replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
show procedure status like 'bar'|
drop procedure bar|
@@ -1660,7 +1662,7 @@
# Implicit LOCK/UNLOCK TABLES for table access in functions
#
---disable_warning
+--disable_warnings
drop function if exists t1max|
--enable_warnings
create function t1max() returns int
@@ -1704,6 +1706,397 @@
drop function getcount|
+# Test cases for different combinations of condition handlers in nested
+# begin-end blocks in stored procedures.
+#
+# Note that the standard specifies that the most specific handler should
+# be triggered even if it's an outer handler masked by a less specific
+# handler in an inner block.
+# Note also that '02000' is more specific than NOT FOUND; there might be
+# other '02xxx' states, even if we currently do not issue them in any
+# situation (e.g. '02001').
+#
+# The combinations we test are these:
+#
+# Inner
+# errcode sqlstate not found sqlwarning sqlexception
+# Outer +------------+------------+------------+------------+------------+
+#errcode | h_ee (i) | h_es (o) | h_en (o) | h_ew (o) | h_ex (o) |
+#sqlstate | h_se (i) | h_ss (i) | h_sn (o) | h_sw (o) | h_sx (o) |
+#not found | h_ne (i) | h_ns (i) | h_nn (i) | | |
+#sqlwarning | h_we (i) | h_ws (i) | | h_ww (i) | |
+#sqlexception | h_xe (i) | h_xs (i) | | | h_xx (i) |
+# +------------+---------------------------------------------------+
+#
+# (i) means that the inner handler is the one that should be invoked,
+# (o) means that the outer handler should be invoked.
+#
+# ('not found', 'sqlwarning' and 'sqlexception' are mutually exclusive, hence
+# no tests for those combinations.)
+#
+
+--disable_warnings
+drop table if exists t3|
+drop procedure if exists h_ee|
+drop procedure if exists h_es|
+drop procedure if exists h_en|
+drop procedure if exists h_ew|
+drop procedure if exists h_ex|
+drop procedure if exists h_se|
+drop procedure if exists h_ss|
+drop procedure if exists h_sn|
+drop procedure if exists h_sw|
+drop procedure if exists h_sx|
+drop procedure if exists h_ne|
+drop procedure if exists h_ns|
+drop procedure if exists h_nn|
+drop procedure if exists h_we|
+drop procedure if exists h_ws|
+drop procedure if exists h_ww|
+drop procedure if exists h_xe|
+drop procedure if exists h_xs|
+drop procedure if exists h_xx|
+--enable_warnings
+
+# smallint - to get out of range warnings
+# primary key - to get constraint errors
+create table t3 (a smallint primary key)|
+
+insert into t3 (a) values (1)|
+
+create procedure h_ee()
+ deterministic
+begin
+ declare continue handler for 1062 -- ER_DUP_ENTRY
+ select 'Outer (bad)' as 'h_ee';
+
+ begin
+ declare continue handler for 1062 -- ER_DUP_ENTRY
+ select 'Inner (good)' as 'h_ee';
+
+ insert into t3 values (1);
+ end;
+end|
+
+create procedure h_es()
+ deterministic
+begin
+ declare continue handler for 1062 -- ER_DUP_ENTRY
+ select 'Outer (good)' as 'h_es';
+
+ begin
+ -- integrity constraint violation
+ declare continue handler for sqlstate '23000'
+ select 'Inner (bad)' as 'h_es';
+
+ insert into t3 values (1);
+ end;
+end|
+
+create procedure h_en()
+ deterministic
+begin
+ declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA
+ select 'Outer (good)' as 'h_en';
+
+ begin
+ declare x int;
+ declare continue handler for sqlstate '02000' -- no data
+ select 'Inner (bad)' as 'h_en';
+
+ select a into x from t3 where a = 42;
+ end;
+end|
+
+create procedure h_ew()
+ deterministic
+begin
+ declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE
+ select 'Outer (good)' as 'h_ew';
+
+ begin
+ declare continue handler for sqlwarning
+ select 'Inner (bad)' as 'h_ew';
+
+ insert into t3 values (123456789012);
+ end;
+ delete from t3;
+ insert into t3 values (1);
+end|
+
+create procedure h_ex()
+ deterministic
+begin
+ declare continue handler for 1062 -- ER_DUP_ENTRY
+ select 'Outer (good)' as 'h_ex';
+
+ begin
+ declare continue handler for sqlexception
+ select 'Inner (bad)' as 'h_ex';
+
+ insert into t3 values (1);
+ end;
+end|
+
+create procedure h_se()
+ deterministic
+begin
+ -- integrity constraint violation
+ declare continue handler for sqlstate '23000'
+ select 'Outer (bad)' as 'h_se';
+
+ begin
+ declare continue handler for 1062 -- ER_DUP_ENTRY
+ select 'Inner (good)' as 'h_se';
+
+ insert into t3 values (1);
+ end;
+end|
+
+create procedure h_ss()
+ deterministic
+begin
+ -- integrity constraint violation
+ declare continue handler for sqlstate '23000'
+ select 'Outer (bad)' as 'h_ss';
+
+ begin
+ -- integrity constraint violation
+ declare continue handler for sqlstate '23000'
+ select 'Inner (good)' as 'h_ss';
+
+ insert into t3 values (1);
+ end;
+end|
+
+create procedure h_sn()
+ deterministic
+begin
+ -- Note: '02000' is more specific than NOT FOUND ;
+ -- there might be other not found states
+ declare continue handler for sqlstate '02000' -- no data
+ select 'Outer (good)' as 'h_sn';
+
+ begin
+ declare x int;
+ declare continue handler for not found
+ select 'Inner (bad)' as 'h_sn';
+
+ select a into x from t3 where a = 42;
+ end;
+end|
+
+create procedure h_sw()
+ deterministic
+begin
+ -- data exception - numeric value out of range
+ declare continue handler for sqlstate '22003'
+ select 'Outer (good)' as 'h_sw';
+
+ begin
+ declare continue handler for sqlwarning
+ select 'Inner (bad)' as 'h_sw';
+
+ insert into t3 values (123456789012);
+ end;
+ delete from t3;
+ insert into t3 values (1);
+end|
+
+create procedure h_sx()
+ deterministic
+begin
+ -- integrity constraint violation
+ declare continue handler for sqlstate '23000'
+ select 'Outer (good)' as 'h_sx';
+
+ begin
+ declare continue handler for sqlexception
+ select 'Inner (bad)' as 'h_sx';
+
+ insert into t3 values (1);
+ end;
+end|
+
+create procedure h_ne()
+ deterministic
+begin
+ declare continue handler for not found
+ select 'Outer (bad)' as 'h_ne';
+
+ begin
+ declare x int;
+ declare continue handler for 1329 -- ER_SP_FETCH_NO_DATA
+ select 'Inner (good)' as 'h_ne';
+
+ select a into x from t3 where a = 42;
+ end;
+end|
+
+create procedure h_ns()
+ deterministic
+begin
+ declare continue handler for not found
+ select 'Outer (bad)' as 'h_ns';
+
+ begin
+ declare x int;
+ declare continue handler for sqlstate '02000' -- no data
+ select 'Inner (good)' as 'h_ns';
+
+ select a into x from t3 where a = 42;
+ end;
+end|
+
+create procedure h_nn()
+ deterministic
+begin
+ declare continue handler for not found
+ select 'Outer (bad)' as 'h_nn';
+
+ begin
+ declare x int;
+ declare continue handler for not found
+ select 'Inner (good)' as 'h_nn';
+
+ select a into x from t3 where a = 42;
+ end;
+end|
+
+create procedure h_we()
+ deterministic
+begin
+ declare continue handler for sqlwarning
+ select 'Outer (bad)' as 'h_we';
+
+ begin
+ declare continue handler for 1264 -- ER_WARN_DATA_OUT_OF_RANGE
+ select 'Inner (good)' as 'h_we';
+
+ insert into t3 values (123456789012);
+ end;
+ delete from t3;
+ insert into t3 values (1);
+end|
+
+create procedure h_ws()
+ deterministic
+begin
+ declare continue handler for sqlwarning
+ select 'Outer (bad)' as 'h_ws';
+
+ begin
+ -- data exception - numeric value out of range
+ declare continue handler for sqlstate '22003'
+ select 'Inner (good)' as 'h_ws';
+
+ insert into t3 values (123456789012);
+ end;
+ delete from t3;
+ insert into t3 values (1);
+end|
+
+create procedure h_ww()
+ deterministic
+begin
+ declare continue handler for sqlwarning
+ select 'Outer (bad)' as 'h_ww';
+
+ begin
+ declare continue handler for sqlwarning
+ select 'Inner (good)' as 'h_ww';
+
+ insert into t3 values (123456789012);
+ end;
+ delete from t3;
+ insert into t3 values (1);
+end|
+
+create procedure h_xe()
+ deterministic
+begin
+ declare continue handler for sqlexception
+ select 'Outer (bad)' as 'h_xe';
+
+ begin
+ declare continue handler for 1062 -- ER_DUP_ENTRY
+ select 'Inner (good)' as 'h_xe';
+
+ insert into t3 values (1);
+ end;
+end|
+
+create procedure h_xs()
+ deterministic
+begin
+ declare continue handler for sqlexception
+ select 'Outer (bad)' as 'h_xs';
+
+ begin
+ -- integrity constraint violation
+ declare continue handler for sqlstate '23000'
+ select 'Inner (good)' as 'h_xs';
+
+ insert into t3 values (1);
+ end;
+end|
+
+create procedure h_xx()
+ deterministic
+begin
+ declare continue handler for sqlexception
+ select 'Outer (bad)' as 'h_xx';
+
+ begin
+ declare continue handler for sqlexception
+ select 'Inner (good)' as 'h_xx';
+
+ insert into t3 values (1);
+ end;
+end|
+
+call h_ee()|
+call h_es()|
+call h_en()|
+call h_ew()|
+call h_ex()|
+call h_se()|
+call h_ss()|
+call h_sn()|
+call h_sw()|
+call h_sx()|
+call h_ne()|
+call h_ns()|
+call h_nn()|
+call h_we()|
+call h_ws()|
+call h_ww()|
+call h_xe()|
+call h_xs()|
+call h_xx()|
+
+drop table t3|
+drop procedure h_ee|
+drop procedure h_es|
+drop procedure h_en|
+drop procedure h_ew|
+drop procedure h_ex|
+drop procedure h_se|
+drop procedure h_ss|
+drop procedure h_sn|
+drop procedure h_sw|
+drop procedure h_sx|
+drop procedure h_ne|
+drop procedure h_ns|
+drop procedure h_nn|
+drop procedure h_we|
+drop procedure h_ws|
+drop procedure h_ww|
+drop procedure h_xe|
+drop procedure h_xs|
+drop procedure h_xx|
+
+
#
# Test cases for old bugs
#
@@ -2574,7 +2967,6 @@
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;
@@ -2596,20 +2988,6 @@
drop procedure bug4902|
-# We need separate SP for SHOW PROCESSLIST since we want use replace_column
---disable_warnings
-drop procedure if exists bug4902_2|
---enable_warnings
-create procedure bug4902_2()
-begin
- show processlist;
-end|
---replace_column 1 # 6 # 3 localhost
-call bug4902_2()|
---replace_column 1 # 6 # 3 localhost
-call bug4902_2()|
-drop procedure bug4902_2|
-
#
# BUG#4904
#
@@ -2824,44 +3202,6 @@
delete from t1|
drop procedure bug4941|
-
-#
-# BUG#3583: query cache doesn't work for stored procedures
-#
---disable_warnings
-drop procedure if exists bug3583|
---enable_warnings
---disable_warnings
-drop procedure if exists bug3583|
---enable_warnings
-create procedure bug3583()
-begin
- declare c int;
-
- select * from t1;
- select count(*) into c from t1;
- select c;
-end|
-
-insert into t1 values ("x", 3), ("y", 5)|
-set @x = @@query_cache_size|
-set global query_cache_size = 10*1024*1024|
-
-flush status|
-flush query cache|
-show status like 'Qcache_hits'|
-call bug3583()|
-show status like 'Qcache_hits'|
-call bug3583()|
-call bug3583()|
-show status like 'Qcache_hits'|
-
-set global query_cache_size = @x|
-flush status|
-flush query cache|
-delete from t1|
-drop procedure bug3583|
-
#
# BUG#4905: Stored procedure doesn't clear for "Rows affected"
#
@@ -3171,24 +3511,6 @@
drop function bug5240|
#
-# BUG#5278: Stored procedure packets out of order if SET PASSWORD.
-#
---disable_warnings
-drop function if exists bug5278|
---enable_warnings
-create function bug5278 () returns char
-begin
- SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');
- return 'okay';
-end|
-
---error 1133
-select bug5278()|
---error 1133
-select bug5278()|
-drop function bug5278|
-
-#
# BUG#7992: rolling back temporary Item tree changes in SP
#
--disable_warnings
@@ -4785,24 +5107,6 @@
call bug10100t(5)|
#end of the stack checking
-set @@max_sp_recursion_depth=255|
-set @var=1|
-#disable log because error about stack overrun contains numbers which
-#depend on a system
--- disable_result_log
--- error ER_STACK_OVERRUN_NEED_MORE
-call bug10100p(255, @var)|
--- error ER_STACK_OVERRUN_NEED_MORE
-call bug10100pt(1,255)|
--- error ER_STACK_OVERRUN_NEED_MORE
-call bug10100pv(1,255)|
--- error ER_STACK_OVERRUN_NEED_MORE
-call bug10100pd(1,255)|
--- error ER_STACK_OVERRUN_NEED_MORE
-call bug10100pc(1,255)|
--- enable_result_log
-set @@max_sp_recursion_depth=0|
-
deallocate prepare stmt2|
drop function bug10100f|
@@ -5309,6 +5613,54 @@
drop procedure bug15011|
drop table t3|
+
+
+#
+# BUG#17615: problem with character set
+#
+--disable_warnings
+drop function if exists bug17615|
+--enable_warnings
+
+create table t3 (a varchar(256) unicode)|
+
+create function bug17615() returns varchar(256) unicode
+begin
+ declare tmp_res varchar(256) unicode;
+ set tmp_res= 'foo string';
+ return tmp_res;
+end|
+
+insert into t3 values(bug17615())|
+select * from t3|
+
+drop function bug17615|
+drop table t3|
+
+
+#
+# BUG#17476: Stored procedure not returning data when it is called first
+# time per connection
+#
+--disable_warnings
+drop procedure if exists bug17476|
+--enable_warnings
+
+create table t3 ( d date )|
+insert into t3 values
+ ( '2005-01-01' ), ( '2005-01-02' ), ( '2005-01-03' ),
+ ( '2005-01-04' ), ( '2005-02-01' ), ( '2005-02-02' )|
+
+create procedure bug17476(pDateFormat varchar(10))
+ select date_format(t3.d, pDateFormat), count(*)
+ from t3
+ group by date_format(t3.d, pDateFormat)|
+
+call bug17476('%Y-%m')|
+call bug17476('%Y-%m')|
+
+drop table t3|
+drop procedure bug17476|
#
| Thread |
|---|
| • bk commit into 5.0 tree (pem:1.2102) | pem | 3 Mar |
