diff options
| -rw-r--r-- | mysql-test/r/sp.result | 54 | ||||
| -rw-r--r-- | mysql-test/t/sp.test | 45 |
2 files changed, 99 insertions, 0 deletions
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 9ea43e5aeb3..6f0a8623a4c 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -4099,4 +4099,58 @@ call bug14376(4711)| x 4711 drop procedure bug14376| +drop procedure if exists p1| +Warnings: +Note 1305 PROCEDURE p1 does not exist +drop table if exists t1| +create table t1 (a varchar(255))| +insert into t1 (a) values ("a - table column")| +create procedure p1(a varchar(255)) +begin +declare i varchar(255); +declare c cursor for select a from t1; +select a; +select a from t1 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 t1; +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 t1; +select a as 'A local variable in a nested compound statement takes precedence over a local variable in the outer statement'; +select a from t1 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 p1("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 table t1,t2; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 0563835de09..760110b0a64 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -4898,7 +4898,52 @@ call bug14376(4711)| drop procedure bug14376| +# +# Bug#5967 "Stored procedure declared variable used instead of column" +# The bug should be fixed later. +# Test precedence of names of parameters, variable declarations, +# variable declarations in nested compound statements, table columns, +# table columns in cursor declarations. +# According to the standard, table columns take precedence over +# variable declarations. In MySQL 5.0 it's vice versa. +# +drop procedure if exists p1| +drop table if exists t1| +create table t1 (a varchar(255))| +insert into t1 (a) values ("a - table column")| +create procedure p1(a varchar(255)) +begin + declare i varchar(255); + declare c cursor for select a from t1; + select a; + select a from t1 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 t1; + 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 t1; + select a as 'A local variable in a nested compound statement takes precedence over a local variable in the outer statement'; + select a from t1 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 p1("a - stored procedure parameter")| # # BUG#NNNN: New bug synopsis |