| 1 |
SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION'; |
| 2 |
|
| 3 |
--source suite/funcs_1/storedproc/load_sp_tb.inc |
| 4 |
-------------------------------------------------------------------------------- |
| 5 |
SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; |
| 6 |
|
| 7 |
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc |
| 8 |
-------------------------------------------------------------------------------- |
| 9 |
DROP DATABASE IF EXISTS db_storedproc; |
| 10 |
DROP DATABASE IF EXISTS db_storedproc_1; |
| 11 |
CREATE DATABASE db_storedproc charset utf8mb4; |
| 12 |
CREATE DATABASE db_storedproc_1 charset utf8mb4; |
| 13 |
USE db_storedproc; |
| 14 |
create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) |
| 15 |
engine = <engine_to_be_tested>; |
| 16 |
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1; |
| 17 |
create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) |
| 18 |
engine = <engine_to_be_tested>; |
| 19 |
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2; |
| 20 |
create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>; |
| 21 |
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3; |
| 22 |
create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) |
| 23 |
engine = <engine_to_be_tested>; |
| 24 |
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t4; |
| 25 |
USE db_storedproc_1; |
| 26 |
create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) |
| 27 |
engine = <engine_to_be_tested>; |
| 28 |
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6; |
| 29 |
USE db_storedproc; |
| 30 |
create table t7 (f1 char(20), f2 char(25), f3 date, f4 int) |
| 31 |
engine = <engine_to_be_tested>; |
| 32 |
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7; |
| 33 |
Warnings: |
| 34 |
Warning 1265 Data truncated for column 'f3' at row 1 |
| 35 |
Warning 1265 Data truncated for column 'f3' at row 2 |
| 36 |
Warning 1265 Data truncated for column 'f3' at row 3 |
| 37 |
Warning 1265 Data truncated for column 'f3' at row 4 |
| 38 |
Warning 1265 Data truncated for column 'f3' at row 5 |
| 39 |
Warning 1265 Data truncated for column 'f3' at row 6 |
| 40 |
Warning 1265 Data truncated for column 'f3' at row 7 |
| 41 |
Warning 1265 Data truncated for column 'f3' at row 8 |
| 42 |
Warning 1265 Data truncated for column 'f3' at row 9 |
| 43 |
Warning 1265 Data truncated for column 'f3' at row 10 |
| 44 |
create table t8 (f1 char(20), f2 char(25), f3 date, f4 int) |
| 45 |
engine = <engine_to_be_tested>; |
| 46 |
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8; |
| 47 |
Warnings: |
| 48 |
Warning 1265 Data truncated for column 'f3' at row 1 |
| 49 |
Warning 1265 Data truncated for column 'f3' at row 2 |
| 50 |
Warning 1265 Data truncated for column 'f3' at row 3 |
| 51 |
Warning 1265 Data truncated for column 'f3' at row 4 |
| 52 |
Warning 1265 Data truncated for column 'f3' at row 5 |
| 53 |
Warning 1265 Data truncated for column 'f3' at row 6 |
| 54 |
Warning 1265 Data truncated for column 'f3' at row 7 |
| 55 |
Warning 1265 Data truncated for column 'f3' at row 8 |
| 56 |
Warning 1265 Data truncated for column 'f3' at row 9 |
| 57 |
Warning 1265 Data truncated for column 'f3' at row 10 |
| 58 |
create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>; |
| 59 |
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9; |
| 60 |
create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) |
| 61 |
engine = <engine_to_be_tested>; |
| 62 |
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10; |
| 63 |
create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int) |
| 64 |
engine = <engine_to_be_tested>; |
| 65 |
load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11; |
| 66 |
SET sql_mode = default; |
| 67 |
Warnings: |
| 68 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 69 |
|
| 70 |
Section 3.1.2 - Syntax checks for the stored procedure-specific |
| 71 |
programming statements BEGIN/END, DECLARE, SET, SELECT/INTO, OPEN, FETCH, CLOSE: |
| 72 |
-------------------------------------------------------------------------------- |
| 73 |
Warnings: |
| 74 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 75 |
SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; |
| 76 |
Warnings: |
| 77 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 78 |
|
| 79 |
Testcase 3.1.2.8: |
| 80 |
----------------- |
| 81 |
Warnings: |
| 82 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 83 |
Warnings: |
| 84 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 85 |
|
| 86 |
Ensure that the scope of each BEGIN/END compound statement within a stored |
| 87 |
procedure definition is properly applied |
| 88 |
-------------------------------------------------------------------------------- |
| 89 |
Warnings: |
| 90 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 91 |
DROP PROCEDURE IF EXISTS sp1; |
| 92 |
CREATE PROCEDURE sp1( ) |
| 93 |
begin_label: BEGIN |
| 94 |
declare x char DEFAULT 'x'; |
| 95 |
declare y char DEFAULT 'y'; |
| 96 |
set x = '1'; |
| 97 |
set y = '2'; |
| 98 |
label1: BEGIN |
| 99 |
declare x char DEFAULT 'X'; |
| 100 |
declare y char DEFAULT 'Y'; |
| 101 |
SELECT f1, f2 into x, y from t2 limit 1; |
| 102 |
SELECT '1.1', x, y; |
| 103 |
label2: BEGIN |
| 104 |
declare x char default 'a'; |
| 105 |
declare y char default 'b'; |
| 106 |
label3: BEGIN |
| 107 |
declare x char default 'c'; |
| 108 |
declare y char default 'd'; |
| 109 |
label4: BEGIN |
| 110 |
declare x char default 'e'; |
| 111 |
declare y char default 'f'; |
| 112 |
label5: BEGIN |
| 113 |
declare x char default 'g'; |
| 114 |
declare y char default 'h'; |
| 115 |
SELECT 5, x, y; |
| 116 |
END label5; |
| 117 |
SELECT 4, x, y; |
| 118 |
END label4; |
| 119 |
SELECT 3, x, y; |
| 120 |
END label3; |
| 121 |
SELECT 2, x, y; |
| 122 |
END label2; |
| 123 |
END label1; |
| 124 |
set @v1 = x; |
| 125 |
set @v2 = y; |
| 126 |
SELECT '1.2', @v1, @v2; |
| 127 |
END begin_label// |
| 128 |
CALL sp1(); |
| 129 |
1.1 x y |
| 130 |
1.1 a a |
| 131 |
5 x y |
| 132 |
5 g h |
| 133 |
4 x y |
| 134 |
4 e f |
| 135 |
3 x y |
| 136 |
3 c d |
| 137 |
2 x y |
| 138 |
2 a b |
| 139 |
1.2 @v1 @v2 |
| 140 |
1.2 1 2 |
| 141 |
DROP PROCEDURE IF EXISTS sp1; |
| 142 |
Warnings: |
| 143 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 144 |
|
| 145 |
Testcase 3.1.2.26: |
| 146 |
------------------ |
| 147 |
Warnings: |
| 148 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 149 |
Warnings: |
| 150 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 151 |
|
| 152 |
Ensure that the initial value of every variable declared for a stored procedure |
| 153 |
is either NULL or its DEFAULT value, as appropriate. |
| 154 |
-------------------------------------------------------------------------------- |
| 155 |
Warnings: |
| 156 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 157 |
DROP PROCEDURE IF EXISTS sp1; |
| 158 |
set @v1=0; |
| 159 |
set @v2=0; |
| 160 |
CREATE PROCEDURE sp1( ) |
| 161 |
BEGIN |
| 162 |
declare x1 char default 'x'; |
| 163 |
declare y1 char; |
| 164 |
declare x2 tinytext default 'tinytext'; |
| 165 |
declare y2 tinytext; |
| 166 |
declare x3 datetime default '2005-10-03 12:13:14'; |
| 167 |
declare y3 datetime; |
| 168 |
declare x4 float default 1.2; |
| 169 |
declare y4 float; |
| 170 |
declare x5 blob default 'b'; |
| 171 |
declare y5 blob; |
| 172 |
declare x6 smallint default 127; |
| 173 |
declare y6 smallint; |
| 174 |
SELECT x1, x2, x3, x4, x5, x6, y1, y2, y3, y4, y5, y6; |
| 175 |
END// |
| 176 |
CALL sp1(); |
| 177 |
x1 x2 x3 x4 x5 x6 y1 y2 y3 y4 y5 y6 |
| 178 |
x tinytext 2005-10-03 12:13:14 1.2 b 127 NULL NULL NULL NULL NULL NULL |
| 179 |
DROP PROCEDURE sp1; |
| 180 |
Warnings: |
| 181 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 182 |
|
| 183 |
Testcase 3.1.2.30: |
| 184 |
------------------ |
| 185 |
Warnings: |
| 186 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 187 |
Warnings: |
| 188 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 189 |
|
| 190 |
Ensure that, when a stored procedure is called/executed, every variable always |
| 191 |
uses the correct value: either the value with which it is initialized or the |
| 192 |
value to which it is subsequently SET or otherwise assigned, as appropriate. |
| 193 |
-------------------------------------------------------------------------------- |
| 194 |
Warnings: |
| 195 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 196 |
DROP PROCEDURE IF EXISTS sp1; |
| 197 |
CREATE PROCEDURE sp1( IN invar INT, OUT outvar INT ) |
| 198 |
BEGIN |
| 199 |
declare x integer; |
| 200 |
declare y integer default 1; |
| 201 |
set @x = x; |
| 202 |
set @y = y; |
| 203 |
set @z = 234; |
| 204 |
SELECT f1, f2 into @x, @y from t2 where f1='a`' and f2='a`' limit 1; |
| 205 |
SELECT @x, @y, @z, invar; |
| 206 |
BEGIN |
| 207 |
set @x = 2; |
| 208 |
SELECT @x, @y, @z; |
| 209 |
SET outvar = @x * invar + @z * @f; |
| 210 |
SET invar = outvar; |
| 211 |
BEGIN |
| 212 |
set @y = null, @z = 'abcd'; |
| 213 |
SELECT @x, @y, @z; |
| 214 |
END; |
| 215 |
END; |
| 216 |
END// |
| 217 |
SET @invar = 100; |
| 218 |
SET @outvar = @invar; |
| 219 |
SET @f = 10; |
| 220 |
SELECT @x, @y, @z, @invar, @outvar; |
| 221 |
@x @y @z @invar @outvar |
| 222 |
NULL NULL NULL 100 100 |
| 223 |
CALL sp1( @invar, @outvar ); |
| 224 |
@x @y @z invar |
| 225 |
a` a` 234 100 |
| 226 |
@x @y @z |
| 227 |
2 a` 234 |
| 228 |
@x @y @z |
| 229 |
2 NULL abcd |
| 230 |
SELECT @x, @y, @z, @invar, @outvar; |
| 231 |
@x @y @z @invar @outvar |
| 232 |
2 NULL abcd 100 2540 |
| 233 |
DROP PROCEDURE sp1; |
| 234 |
Warnings: |
| 235 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 236 |
|
| 237 |
Testcase 3.1.2.31: |
| 238 |
------------------ |
| 239 |
Warnings: |
| 240 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 241 |
Warnings: |
| 242 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 243 |
|
| 244 |
Ensure that the SELECT ... INTO statement properly assigns values to the |
| 245 |
variables in its variable list. |
| 246 |
-------------------------------------------------------------------------------- |
| 247 |
Warnings: |
| 248 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 249 |
DROP PROCEDURE IF EXISTS sp1; |
| 250 |
CREATE PROCEDURE sp1( ) |
| 251 |
BEGIN |
| 252 |
declare x integer; declare y integer; |
| 253 |
set @x=x; |
| 254 |
set @y=y; |
| 255 |
SELECT f4, f3 into @x, @y from t2 where f4=-5000 and f3='1000-01-01' limit 1; |
| 256 |
SELECT @x, @y; |
| 257 |
END// |
| 258 |
CALL sp1(); |
| 259 |
@x @y |
| 260 |
-5000 1000-01-01 |
| 261 |
DROP PROCEDURE sp1; |
| 262 |
Warnings: |
| 263 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 264 |
|
| 265 |
Testcase 3.1.2.32: |
| 266 |
------------------ |
| 267 |
Warnings: |
| 268 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 269 |
Warnings: |
| 270 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 271 |
|
| 272 |
Ensure that a SELECT ... INTO statement that retrieves multiple rows is |
| 273 |
rejected, with an appropriate error message. |
| 274 |
-------------------------------------------------------------------------------- |
| 275 |
Warnings: |
| 276 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 277 |
DROP PROCEDURE IF EXISTS sp1; |
| 278 |
CREATE PROCEDURE sp1( ) |
| 279 |
BEGIN |
| 280 |
declare x integer; declare y integer; |
| 281 |
set @x=x; |
| 282 |
set @y=y; |
| 283 |
SELECT f4, f3 into @x, @y from t2; |
| 284 |
END// |
| 285 |
CALL sp1(); |
| 286 |
ERROR 42000: Result consisted of more than one row |
| 287 |
DROP PROCEDURE sp1; |
| 288 |
Warnings: |
| 289 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 290 |
|
| 291 |
Testcase 3.1.2.33: |
| 292 |
------------------ |
| 293 |
Warnings: |
| 294 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 295 |
Warnings: |
| 296 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 297 |
|
| 298 |
Ensure that a SELECT ... INTO statement that retrieves too many columns for the |
| 299 |
number of variables in its variable list is rejected, with an appropriate error |
| 300 |
message. |
| 301 |
-------------------------------------------------------------------------------- |
| 302 |
Warnings: |
| 303 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 304 |
DROP PROCEDURE IF EXISTS sp1; |
| 305 |
CREATE PROCEDURE sp1( ) |
| 306 |
BEGIN |
| 307 |
declare x integer; declare y integer; |
| 308 |
set @x=x; |
| 309 |
set @y=y; |
| 310 |
SELECT f4, f3, f2, f1 into @x, @y from t2; |
| 311 |
END// |
| 312 |
CALL sp1(); |
| 313 |
ERROR 21000: The used SELECT statements have a different number of columns |
| 314 |
DROP PROCEDURE sp1; |
| 315 |
Warnings: |
| 316 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 317 |
|
| 318 |
Testcase 3.1.2.34: |
| 319 |
------------------ |
| 320 |
Warnings: |
| 321 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 322 |
Warnings: |
| 323 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 324 |
|
| 325 |
Ensure that a SELECT ... INTO statement that retrieves too few columns for the |
| 326 |
number of variables in its variable list is rejected, with an appropriate error |
| 327 |
message. |
| 328 |
-------------------------------------------------------------------------------- |
| 329 |
Warnings: |
| 330 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 331 |
DROP PROCEDURE IF EXISTS sp1; |
| 332 |
CREATE PROCEDURE sp1( ) |
| 333 |
BEGIN |
| 334 |
declare x integer; declare y integer; declare z integer; |
| 335 |
set @x=x; |
| 336 |
set @y=y; |
| 337 |
set @z=z; |
| 338 |
SELECT f4 into @x, @y, @z from t2; |
| 339 |
END// |
| 340 |
CALL sp1(); |
| 341 |
ERROR 21000: The used SELECT statements have a different number of columns |
| 342 |
DROP PROCEDURE sp1; |
| 343 |
Warnings: |
| 344 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 345 |
|
| 346 |
Testcase 3.1.2.38: |
| 347 |
------------------ |
| 348 |
Warnings: |
| 349 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 350 |
Warnings: |
| 351 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 352 |
|
| 353 |
Ensure that the scope of every condition declared is properly applied. |
| 354 |
-------------------------------------------------------------------------------- |
| 355 |
Warnings: |
| 356 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 357 |
DROP PROCEDURE IF EXISTS h1; |
| 358 |
DROP TABLE IF EXISTS res_t1; |
| 359 |
create table res_t1(w char unique, x char); |
| 360 |
insert into res_t1 values('a', 'b'); |
| 361 |
CREATE PROCEDURE h1 () |
| 362 |
BEGIN |
| 363 |
declare x1, x2, x3, x4, x5, x6 int default 0; |
| 364 |
SELECT '-1-', x1, x2, x3, x4, x5, x6; |
| 365 |
BEGIN |
| 366 |
declare condname condition for sqlstate '23000'; |
| 367 |
declare continue handler for condname set x5 = 1; |
| 368 |
set x6 = 0; |
| 369 |
insert into res_t1 values ('a', 'b'); |
| 370 |
set x6 = 1; |
| 371 |
SELECT '-2-', x1, x2, x3, x4, x5, x6; |
| 372 |
END; |
| 373 |
begin1_label: BEGIN |
| 374 |
BEGIN |
| 375 |
declare condname condition for sqlstate '20000'; |
| 376 |
declare continue handler for condname set x1 = 1; |
| 377 |
set x2 = 0; |
| 378 |
case x2 |
| 379 |
when 1 then set x2=10; |
| 380 |
when 2 then set x2=11; |
| 381 |
END case; |
| 382 |
set x2 = 1; |
| 383 |
SELECT '-3-', x1, x2, x3, x4, x5, x6; |
| 384 |
begin2_label: BEGIN |
| 385 |
BEGIN |
| 386 |
declare condname condition for sqlstate '23000'; |
| 387 |
declare exit handler for condname set x3 = 1; |
| 388 |
set x4= 1; |
| 389 |
SELECT '-4a', x1, x2, x3, x4, x5, x6; |
| 390 |
insert into res_t1 values ('a', 'b'); |
| 391 |
set x4= 2; |
| 392 |
SELECT '-4b', x1, x2, x3, x4, x5, x6; |
| 393 |
END; |
| 394 |
SELECT '-5-', x1, x2, x3, x4, x5, x6; |
| 395 |
END begin2_label; |
| 396 |
SELECT '-6-', x1, x2, x3, x4, x5, x6; |
| 397 |
END; |
| 398 |
SELECT '-7-', x1, x2, x3, x4, x5, x6; |
| 399 |
END begin1_label; |
| 400 |
SELECT 'END', x1, x2, x3, x4, x5, x6; |
| 401 |
END// |
| 402 |
CALL h1(); |
| 403 |
-1- x1 x2 x3 x4 x5 x6 |
| 404 |
-1- 0 0 0 0 0 0 |
| 405 |
-2- x1 x2 x3 x4 x5 x6 |
| 406 |
-2- 0 0 0 0 1 1 |
| 407 |
-3- x1 x2 x3 x4 x5 x6 |
| 408 |
-3- 1 1 0 0 1 1 |
| 409 |
-4a x1 x2 x3 x4 x5 x6 |
| 410 |
-4a 1 1 0 1 1 1 |
| 411 |
-5- x1 x2 x3 x4 x5 x6 |
| 412 |
-5- 1 1 1 1 1 1 |
| 413 |
-6- x1 x2 x3 x4 x5 x6 |
| 414 |
-6- 1 1 1 1 1 1 |
| 415 |
-7- x1 x2 x3 x4 x5 x6 |
| 416 |
-7- 1 1 1 1 1 1 |
| 417 |
END x1 x2 x3 x4 x5 x6 |
| 418 |
END 1 1 1 1 1 1 |
| 419 |
DROP TABLE IF EXISTS tnull; |
| 420 |
DROP PROCEDURE IF EXISTS sp1; |
| 421 |
CREATE TABLE tnull(f1 int); |
| 422 |
CREATE PROCEDURE sp1() |
| 423 |
BEGIN |
| 424 |
declare cond1 condition for sqlstate '42S02'; |
| 425 |
declare continue handler for cond1 set @var2 = 1; |
| 426 |
BEGIN |
| 427 |
declare cond1 condition for sqlstate '23000'; |
| 428 |
declare continue handler for cond1 set @var2 = 1; |
| 429 |
END; |
| 430 |
insert into tnull values(1); |
| 431 |
END// |
| 432 |
CALL sp1(); |
| 433 |
DROP PROCEDURE h1; |
| 434 |
drop table res_t1; |
| 435 |
DROP PROCEDURE sp1; |
| 436 |
DROP TABLE tnull; |
| 437 |
Warnings: |
| 438 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 439 |
|
| 440 |
Testcase 3.1.2.43: |
| 441 |
------------------ |
| 442 |
Warnings: |
| 443 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 444 |
Warnings: |
| 445 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 446 |
|
| 447 |
Ensure that the DECLARE ... HANDLER FOR statement can not declare any handler |
| 448 |
for a condition declared outside of the scope of the handler. |
| 449 |
-------------------------------------------------------------------------------- |
| 450 |
Warnings: |
| 451 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 452 |
DROP PROCEDURE IF EXISTS h1; |
| 453 |
DROP PROCEDURE IF EXISTS h2; |
| 454 |
drop table IF EXISTS res_t1; |
| 455 |
create table res_t1(w char unique, x char); |
| 456 |
insert into res_t1 values ('a', 'b'); |
| 457 |
CREATE PROCEDURE h1 () |
| 458 |
BEGIN |
| 459 |
declare x1, x2, x3, x4, x5, x6 int default 0; |
| 460 |
BEGIN |
| 461 |
declare cond_1 condition for sqlstate '23000'; |
| 462 |
declare continue handler for cond_1 set x5 = 1; |
| 463 |
BEGIN |
| 464 |
declare cond_2 condition for sqlstate '20000'; |
| 465 |
declare continue handler for cond_1 set x1 = 1; |
| 466 |
BEGIN |
| 467 |
declare continue handler for cond_2 set x3 = 1; |
| 468 |
set x2 = 1; |
| 469 |
END; |
| 470 |
set x6 = 0; |
| 471 |
END; |
| 472 |
BEGIN |
| 473 |
declare continue handler for cond_1 set x1 = 1; |
| 474 |
BEGIN |
| 475 |
declare continue handler for cond_2 set x3 = 1; |
| 476 |
set x2 = 1; |
| 477 |
END; |
| 478 |
set x6 = 0; |
| 479 |
END; |
| 480 |
END; |
| 481 |
SELECT x1, x2, x3, x4, x5, x6; |
| 482 |
END// |
| 483 |
ERROR 42000: Undefined CONDITION: cond_2 |
| 484 |
CREATE PROCEDURE h2 () |
| 485 |
BEGIN |
| 486 |
declare x1, x2, x3, x4, x5, x6 int default 0; |
| 487 |
BEGIN |
| 488 |
declare condname condition for sqlstate '23000'; |
| 489 |
declare continue handler for condname set x5 = 1; |
| 490 |
BEGIN |
| 491 |
declare condname condition for sqlstate '20000'; |
| 492 |
declare continue handler for condname set x1 = 1; |
| 493 |
BEGIN |
| 494 |
declare condname condition for sqlstate '42000'; |
| 495 |
declare continue handler for condname set x3 = 1; |
| 496 |
set x6 = 0; |
| 497 |
insert into res_t1 values ('a', 'b'); |
| 498 |
set x6 = 1; |
| 499 |
set x4= 0; |
| 500 |
CALL sp1(); |
| 501 |
set x4= 1; |
| 502 |
set x2 = 0; |
| 503 |
case x2 |
| 504 |
when 1 then set x2=10; |
| 505 |
when 2 then set x2=11; |
| 506 |
END case; |
| 507 |
set x2 = 1; |
| 508 |
END; |
| 509 |
set x2 = 0; |
| 510 |
case x2 |
| 511 |
when 1 then set x2=10; |
| 512 |
when 2 then set x2=11; |
| 513 |
END case; |
| 514 |
set x2 = 1; |
| 515 |
set x6 = 0; |
| 516 |
insert into res_t1 values ('a', 'b'); |
| 517 |
set x6 = 1; |
| 518 |
END; |
| 519 |
END; |
| 520 |
SELECT x1, x2, x3, x4, x5, x6; |
| 521 |
END// |
| 522 |
CALL h2(); |
| 523 |
x1 x2 x3 x4 x5 x6 |
| 524 |
1 1 1 1 1 1 |
| 525 |
SELECT * FROM res_t1; |
| 526 |
w x |
| 527 |
a b |
| 528 |
DROP PROCEDURE h2; |
| 529 |
drop table res_t1; |
| 530 |
Warnings: |
| 531 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 532 |
|
| 533 |
Testcase 3.1.2.44: |
| 534 |
------------------ |
| 535 |
Warnings: |
| 536 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 537 |
Warnings: |
| 538 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 539 |
|
| 540 |
Ensure that the DECLARE ... HANDLER FOR statement cannot declare a handler for |
| 541 |
any invalid, or undeclared, condition. |
| 542 |
-------------------------------------------------------------------------------- |
| 543 |
Warnings: |
| 544 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 545 |
DROP PROCEDURE IF EXISTS h1; |
| 546 |
CREATE PROCEDURE h1 () |
| 547 |
BEGIN |
| 548 |
declare x1, x2, x3, x4, x5, x6 int default 0; |
| 549 |
BEGIN |
| 550 |
declare condname1 condition for sqlstate '23000'; |
| 551 |
BEGIN |
| 552 |
declare condname2 condition for sqlstate '20000'; |
| 553 |
declare continue handler for condname1 set x3 = 1; |
| 554 |
declare continue handler for condname2 set x1 = 1; |
| 555 |
END; |
| 556 |
END; |
| 557 |
BEGIN |
| 558 |
declare condname3 condition for sqlstate '42000'; |
| 559 |
declare continue handler for condname1 set x3 = 1; |
| 560 |
declare continue handler for condname2 set x5 = 1; |
| 561 |
declare continue handler for condname3 set x1 = 1; |
| 562 |
END; |
| 563 |
END// |
| 564 |
ERROR 42000: Undefined CONDITION: condname1 |
| 565 |
CREATE PROCEDURE h1 () |
| 566 |
BEGIN |
| 567 |
DECLARE x1 INT DEFAULT 0; |
| 568 |
BEGIN |
| 569 |
DECLARE condname1 CONDITION CHECK SQLSTATE '23000'; |
| 570 |
END; |
| 571 |
DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1; |
| 572 |
END// |
| 573 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHECK SQLSTATE '23000'; |
| 574 |
END; |
| 575 |
DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1; |
| 576 |
' at line 5 |
| 577 |
CREATE PROCEDURE h1 () |
| 578 |
BEGIN |
| 579 |
DECLARE x1 INT DEFAULT 0; |
| 580 |
BEGIN |
| 581 |
DECLARE condname1 CONDITION FOR SQLSTATE 'qwert'; |
| 582 |
END; |
| 583 |
DECLARE CONTINUE HANDLER FOR condname1 SET x1 = 1; |
| 584 |
END// |
| 585 |
ERROR 42000: Bad SQLSTATE: 'qwert' |
| 586 |
Warnings: |
| 587 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 588 |
|
| 589 |
Testcase 3.1.2.45 + 3.1.2.50: |
| 590 |
----------------------------- |
| 591 |
Warnings: |
| 592 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 593 |
Warnings: |
| 594 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 595 |
|
| 596 |
45. Ensure that the scope of every handler declared is properly applied. |
| 597 |
50. Ensure that a CONTINUE handler allows the execution of the stored procedure |
| 598 |
. to continue once the handler statement has completed its own execution (that |
| 599 |
. is, once the handler action statement has been executed). |
| 600 |
-------------------------------------------------------------------------------- |
| 601 |
Warnings: |
| 602 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 603 |
DROP PROCEDURE IF EXISTS p1; |
| 604 |
DROP PROCEDURE IF EXISTS p1undo; |
| 605 |
DROP PROCEDURE IF EXISTS h1; |
| 606 |
DROP PROCEDURE IF EXISTS sp1; |
| 607 |
drop table IF EXISTS res_t1; |
| 608 |
==> 'UNDO' is still not supported. |
| 609 |
create procedure p1undo () |
| 610 |
begin |
| 611 |
declare undo handler for sqlexception select '1'; |
| 612 |
select * from tqq; |
| 613 |
SELECT 'end of 1'; |
| 614 |
end;// |
| 615 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'undo handler for sqlexception select '1'; |
| 616 |
select * from tqq; |
| 617 |
SELECT 'end of 1'; |
| 618 |
' at line 3 |
| 619 |
create procedure p1 () |
| 620 |
begin |
| 621 |
declare exit handler for sqlexception select 'exit handler 1'; |
| 622 |
begin |
| 623 |
declare exit handler for sqlexception select 'exit handler 2'; |
| 624 |
begin |
| 625 |
declare continue handler for sqlexception select 'continue handler 3'; |
| 626 |
drop table if exists tqq; |
| 627 |
select * from tqq; |
| 628 |
SELECT 'end of BEGIN/END 3'; |
| 629 |
end; |
| 630 |
drop table if exists tqq; |
| 631 |
select * from tqq; |
| 632 |
SELECT 'end of BEGIN/END 2'; |
| 633 |
end; |
| 634 |
select * from tqq; |
| 635 |
SELECT 'end of BEGIN/END 1'; |
| 636 |
end;// |
| 637 |
call p1()// |
| 638 |
continue handler 3 |
| 639 |
continue handler 3 |
| 640 |
end of BEGIN/END 3 |
| 641 |
end of BEGIN/END 3 |
| 642 |
exit handler 2 |
| 643 |
exit handler 2 |
| 644 |
exit handler 1 |
| 645 |
exit handler 1 |
| 646 |
create table res_t1(w char unique, x char); |
| 647 |
insert into res_t1 values ('a', 'b'); |
| 648 |
CREATE PROCEDURE h1 () |
| 649 |
BEGIN |
| 650 |
declare x1, x2, x3, x4, x5, x6 int default 0; |
| 651 |
BEGIN |
| 652 |
declare continue handler for sqlstate '23000' set x5 = 1; |
| 653 |
insert into res_t1 values ('a', 'b'); |
| 654 |
set x6 = 1; |
| 655 |
END; |
| 656 |
begin1_label: BEGIN |
| 657 |
BEGIN |
| 658 |
declare continue handler for sqlstate '23000' set x1 = 1; |
| 659 |
insert into res_t1 values ('a', 'b'); |
| 660 |
set x2 = 1; |
| 661 |
begin2_label: BEGIN |
| 662 |
BEGIN |
| 663 |
declare exit handler for sqlstate '23000' set x3 = 1; |
| 664 |
set x4= 1; |
| 665 |
insert into res_t1 values ('a', 'b'); |
| 666 |
set x4= 0; |
| 667 |
END; |
| 668 |
END begin2_label; |
| 669 |
END; |
| 670 |
END begin1_label; |
| 671 |
SELECT x1, x2, x3, x4, x5, x6; |
| 672 |
END// |
| 673 |
CALL h1(); |
| 674 |
x1 x2 x3 x4 x5 x6 |
| 675 |
1 1 1 1 1 1 |
| 676 |
This will fail, SQLSTATE 00000 is not allowed |
| 677 |
CREATE PROCEDURE sp1() |
| 678 |
begin1_label:BEGIN |
| 679 |
declare exit handler for sqlstate '00000' set @var1 = 5; |
| 680 |
set @var2 = 6; |
| 681 |
begin2_label:BEGIN |
| 682 |
declare continue handler for sqlstate '00000' set @var3 = 7; |
| 683 |
set @var4 = 8; |
| 684 |
SELECT @var3, @var4; |
| 685 |
END begin2_label; |
| 686 |
SELECT @var1, @var2; |
| 687 |
END begin1_label// |
| 688 |
ERROR 42000: Bad SQLSTATE: '00000' |
| 689 |
Verify SP wasn't created |
| 690 |
CALL sp1(); |
| 691 |
ERROR 42000: PROCEDURE db_storedproc.sp1 does not exist |
| 692 |
DROP PROCEDURE p1; |
| 693 |
DROP PROCEDURE h1; |
| 694 |
DROP PROCEDURE IF EXISTS sp1; |
| 695 |
DROP TABLE res_t1; |
| 696 |
Warnings: |
| 697 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 698 |
|
| 699 |
Testcase 3.1.2.50: |
| 700 |
------------------ |
| 701 |
Warnings: |
| 702 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 703 |
DROP PROCEDURE IF EXISTS sp1; |
| 704 |
DROP PROCEDURE IF EXISTS sp2; |
| 705 |
CREATE PROCEDURE sp1 (x int, y int) |
| 706 |
BEGIN |
| 707 |
set @y=0; |
| 708 |
END// |
| 709 |
CREATE PROCEDURE sp2 () |
| 710 |
BEGIN |
| 711 |
declare continue handler for sqlstate '42000' set @x2 = 1; |
| 712 |
set @x=1; |
| 713 |
SELECT @x2; |
| 714 |
CALL sp1(1); |
| 715 |
set @x=2; |
| 716 |
SELECT @x2, @x; |
| 717 |
END// |
| 718 |
CALL sp2(); |
| 719 |
@x2 |
| 720 |
NULL |
| 721 |
@x2 @x |
| 722 |
1 2 |
| 723 |
DROP PROCEDURE sp1; |
| 724 |
DROP PROCEDURE sp2; |
| 725 |
Warnings: |
| 726 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 727 |
|
| 728 |
Testcase 3.2.2.51: |
| 729 |
------------------ |
| 730 |
Warnings: |
| 731 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 732 |
Warnings: |
| 733 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 734 |
|
| 735 |
Ensure that an EXIT handler causes the execution of the stored procedure to |
| 736 |
terminate, within its scope, once the handler action statement has been |
| 737 |
executed. |
| 738 |
-------------------------------------------------------------------------------- |
| 739 |
Warnings: |
| 740 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 741 |
DROP PROCEDURE IF EXISTS sp1; |
| 742 |
DROP PROCEDURE IF EXISTS sp2; |
| 743 |
CREATE PROCEDURE sp1 (x int, y int) |
| 744 |
BEGIN |
| 745 |
set @x=0; |
| 746 |
END// |
| 747 |
CREATE PROCEDURE sp2 () |
| 748 |
BEGIN |
| 749 |
declare exit handler for sqlstate '42000' set @x2 = 1; |
| 750 |
set @x2=0; |
| 751 |
set @x=1; |
| 752 |
SELECT '-1-', @x2, @x; |
| 753 |
CALL sp1(1); |
| 754 |
SELECT '-2-', @x2, @x; |
| 755 |
set @x=2; |
| 756 |
END// |
| 757 |
CALL sp1(1); |
| 758 |
ERROR 42000: Incorrect number of arguments for PROCEDURE db_storedproc.sp1; expected 2, got 1 |
| 759 |
CALL sp2(); |
| 760 |
-1- @x2 @x |
| 761 |
-1- 0 1 |
| 762 |
SELECT '-3-', @x2, @x; |
| 763 |
-3- @x2 @x |
| 764 |
-3- 1 1 |
| 765 |
DROP PROCEDURE sp1; |
| 766 |
DROP PROCEDURE sp2; |
| 767 |
Warnings: |
| 768 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 769 |
|
| 770 |
Testcase 3.1.2.52: |
| 771 |
------------------ |
| 772 |
Warnings: |
| 773 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 774 |
Warnings: |
| 775 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 776 |
|
| 777 |
Ensure that an EXIT handler does not cause the execution of the stored procedure |
| 778 |
to terminate outside of its scope. |
| 779 |
-------------------------------------------------------------------------------- |
| 780 |
Warnings: |
| 781 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 782 |
DROP PROCEDURE IF EXISTS sp1; |
| 783 |
DROP PROCEDURE IF EXISTS sp2; |
| 784 |
CREATE PROCEDURE sp1 (x int, y int) |
| 785 |
BEGIN |
| 786 |
set @x=0; |
| 787 |
END// |
| 788 |
CREATE PROCEDURE sp2() |
| 789 |
BEGIN |
| 790 |
declare continue handler for sqlstate '42000' set @x2 = 2; |
| 791 |
set @x2 = 1; |
| 792 |
set @x =20; |
| 793 |
SELECT '-1-', @x2, @x; |
| 794 |
BEGIN |
| 795 |
declare exit handler for sqlstate '42000' set @x2 = 11; |
| 796 |
SELECT '-2-', @x2, @x; |
| 797 |
CALL sp1(1); |
| 798 |
SELECT '-3a', @x2, @x; |
| 799 |
set @x=21; |
| 800 |
SELECT '-3b', @x2, @x; |
| 801 |
END; |
| 802 |
set @x=22; |
| 803 |
SELECT '-4-', @x2, @x; |
| 804 |
END// |
| 805 |
CALL sp2(); |
| 806 |
-1- @x2 @x |
| 807 |
-1- 1 20 |
| 808 |
-2- @x2 @x |
| 809 |
-2- 1 20 |
| 810 |
-4- @x2 @x |
| 811 |
-4- 11 22 |
| 812 |
DROP PROCEDURE sp1; |
| 813 |
DROP PROCEDURE sp2; |
| 814 |
Warnings: |
| 815 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 816 |
|
| 817 |
Testcase 3.1.2.54: |
| 818 |
------------------ |
| 819 |
Warnings: |
| 820 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 821 |
Warnings: |
| 822 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 823 |
|
| 824 |
Ensure that a handler with a condition defined with an SQLSTATE that begins with |
| 825 |
“01“ is always exactly equivalent in action to a handler with an SQLWARNING |
| 826 |
condition. |
| 827 |
-------------------------------------------------------------------------------- |
| 828 |
Warnings: |
| 829 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 830 |
DROP PROCEDURE IF EXISTS sp0; |
| 831 |
DROP PROCEDURE IF EXISTS sp1; |
| 832 |
DROP PROCEDURE IF EXISTS sp2; |
| 833 |
DROP PROCEDURE IF EXISTS sp3; |
| 834 |
DROP PROCEDURE IF EXISTS sp4; |
| 835 |
DROP TABLE IF EXISTS temp; |
| 836 |
CREATE TABLE temp( f1 CHAR, f2 CHAR); |
| 837 |
CREATE PROCEDURE sp0() |
| 838 |
BEGIN |
| 839 |
set @done=0; |
| 840 |
set @x=0; |
| 841 |
insert into temp values('xxx', 'yy'); |
| 842 |
set @x=1; |
| 843 |
END// |
| 844 |
CREATE PROCEDURE sp1() |
| 845 |
BEGIN |
| 846 |
declare continue handler for sqlstate '01000' set @done = 1; |
| 847 |
set @done=0; |
| 848 |
set @x=0; |
| 849 |
insert into temp values('xxx', 'yy'); |
| 850 |
set @x=1; |
| 851 |
END// |
| 852 |
CREATE PROCEDURE sp2() |
| 853 |
BEGIN |
| 854 |
declare continue handler for sqlwarning set @done = 1; |
| 855 |
set @done=0; |
| 856 |
set @x=0; |
| 857 |
insert into temp values('xxx', 'yy'); |
| 858 |
set @x=1; |
| 859 |
END// |
| 860 |
CREATE PROCEDURE sp3() |
| 861 |
BEGIN |
| 862 |
declare exit handler for sqlstate '01000' set @done = 1; |
| 863 |
set @done=0; |
| 864 |
set @x=0; |
| 865 |
insert into temp values('xxx', 'yy'); |
| 866 |
set @x=1; |
| 867 |
END// |
| 868 |
CREATE PROCEDURE sp4() |
| 869 |
BEGIN |
| 870 |
declare exit handler for sqlwarning set @done = 1; |
| 871 |
set @done=0; |
| 872 |
set @x=0; |
| 873 |
insert into temp values('xxx', 'yy'); |
| 874 |
set @x=1; |
| 875 |
END// |
| 876 |
INSERT INTO temp VALUES('0', NULL); |
| 877 |
CALL sp0(); |
| 878 |
SELECT @done, @x; |
| 879 |
@done @x |
| 880 |
0 1 |
| 881 |
INSERT INTO temp VALUES('1', NULL); |
| 882 |
CALL sp1(); |
| 883 |
SELECT @done, @x; |
| 884 |
@done @x |
| 885 |
1 1 |
| 886 |
INSERT INTO temp VALUES('2', NULL); |
| 887 |
CALL sp2(); |
| 888 |
SELECT @done, @x; |
| 889 |
@done @x |
| 890 |
1 1 |
| 891 |
INSERT INTO temp VALUES('3', NULL); |
| 892 |
CALL sp3(); |
| 893 |
SELECT @done, @x; |
| 894 |
@done @x |
| 895 |
1 0 |
| 896 |
INSERT INTO temp VALUES('4', NULL); |
| 897 |
CALL sp4(); |
| 898 |
SELECT @done, @x; |
| 899 |
@done @x |
| 900 |
1 0 |
| 901 |
SELECT * FROM temp; |
| 902 |
f1 f2 |
| 903 |
0 NULL |
| 904 |
x y |
| 905 |
1 NULL |
| 906 |
x y |
| 907 |
2 NULL |
| 908 |
x y |
| 909 |
3 NULL |
| 910 |
x y |
| 911 |
4 NULL |
| 912 |
x y |
| 913 |
DROP PROCEDURE sp1; |
| 914 |
DROP PROCEDURE sp2; |
| 915 |
DROP PROCEDURE sp3; |
| 916 |
DROP PROCEDURE sp4; |
| 917 |
DROP TABLE temp; |
| 918 |
Warnings: |
| 919 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 920 |
|
| 921 |
Testcase 3.1.2.56: |
| 922 |
------------------ |
| 923 |
Warnings: |
| 924 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 925 |
Warnings: |
| 926 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 927 |
|
| 928 |
Ensure that a handler with a condition defined with an SQLSTATE that begins with |
| 929 |
“02“ is always exactly equivalent in action to a handler with a NOT FOUND |
| 930 |
condition. |
| 931 |
-------------------------------------------------------------------------------- |
| 932 |
Warnings: |
| 933 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 934 |
DROP PROCEDURE IF EXISTS sp0; |
| 935 |
DROP PROCEDURE IF EXISTS sp1; |
| 936 |
DROP PROCEDURE IF EXISTS sp2; |
| 937 |
DROP PROCEDURE IF EXISTS sp3; |
| 938 |
DROP PROCEDURE IF EXISTS sp4; |
| 939 |
CREATE PROCEDURE sp0() |
| 940 |
BEGIN |
| 941 |
DECLARE f1_value CHAR(20); |
| 942 |
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; |
| 943 |
SET @done = 0; |
| 944 |
SET @x = 0; |
| 945 |
OPEN cur1; |
| 946 |
FETCH cur1 INTO f1_value; |
| 947 |
SET @x = 1; |
| 948 |
FETCH cur1 INTO f1_value; |
| 949 |
SET @x = 2; |
| 950 |
CLOSE cur1; |
| 951 |
END// |
| 952 |
CREATE PROCEDURE sp1() |
| 953 |
BEGIN |
| 954 |
DECLARE f1_value CHAR(20); |
| 955 |
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; |
| 956 |
declare continue handler for sqlstate '02000' set @done = 1; |
| 957 |
SET @done = 0; |
| 958 |
SET @x = 0; |
| 959 |
OPEN cur1; |
| 960 |
FETCH cur1 INTO f1_value; |
| 961 |
SET @x = 1; |
| 962 |
FETCH cur1 INTO f1_value; |
| 963 |
SET @x = 2; |
| 964 |
CLOSE cur1; |
| 965 |
END// |
| 966 |
CREATE PROCEDURE sp2() |
| 967 |
BEGIN |
| 968 |
DECLARE f1_value CHAR(20); |
| 969 |
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; |
| 970 |
declare continue handler for not found set @done = 1; |
| 971 |
SET @done = 0; |
| 972 |
SET @x = 0; |
| 973 |
OPEN cur1; |
| 974 |
FETCH cur1 INTO f1_value; |
| 975 |
SET @x = 1; |
| 976 |
FETCH cur1 INTO f1_value; |
| 977 |
SET @x = 2; |
| 978 |
CLOSE cur1; |
| 979 |
END// |
| 980 |
CREATE PROCEDURE sp3() |
| 981 |
BEGIN |
| 982 |
DECLARE f1_value CHAR(20); |
| 983 |
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; |
| 984 |
declare exit handler for sqlstate '02000' set @done = 1; |
| 985 |
SET @done = 0; |
| 986 |
SET @x = 0; |
| 987 |
OPEN cur1; |
| 988 |
FETCH cur1 INTO f1_value; |
| 989 |
SET @x = 1; |
| 990 |
FETCH cur1 INTO f1_value; |
| 991 |
SET @x = 2; |
| 992 |
CLOSE cur1; |
| 993 |
END// |
| 994 |
CREATE PROCEDURE sp4() |
| 995 |
BEGIN |
| 996 |
DECLARE f1_value CHAR(20); |
| 997 |
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; |
| 998 |
declare exit handler for not found set @done = 1; |
| 999 |
SET @done = 0; |
| 1000 |
SET @x = 0; |
| 1001 |
OPEN cur1; |
| 1002 |
FETCH cur1 INTO f1_value; |
| 1003 |
SET @x = 1; |
| 1004 |
FETCH cur1 INTO f1_value; |
| 1005 |
SET @x = 2; |
| 1006 |
CLOSE cur1; |
| 1007 |
END// |
| 1008 |
CALL sp0(); |
| 1009 |
ERROR 02000: No data - zero rows fetched, selected, or processed |
| 1010 |
SELECT @done, @x; |
| 1011 |
@done @x |
| 1012 |
0 1 |
| 1013 |
CALL sp1(); |
| 1014 |
SELECT @done, @x; |
| 1015 |
@done @x |
| 1016 |
1 2 |
| 1017 |
CALL sp2(); |
| 1018 |
SELECT @done, @x; |
| 1019 |
@done @x |
| 1020 |
1 2 |
| 1021 |
CALL sp3(); |
| 1022 |
SELECT @done, @x; |
| 1023 |
@done @x |
| 1024 |
1 1 |
| 1025 |
CALL sp4(); |
| 1026 |
SELECT @done, @x; |
| 1027 |
@done @x |
| 1028 |
1 1 |
| 1029 |
DROP PROCEDURE sp0; |
| 1030 |
DROP PROCEDURE sp1; |
| 1031 |
DROP PROCEDURE sp2; |
| 1032 |
DROP PROCEDURE sp3; |
| 1033 |
DROP PROCEDURE sp4; |
| 1034 |
Warnings: |
| 1035 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1036 |
|
| 1037 |
Testcase 3.1.2.58: |
| 1038 |
------------------ |
| 1039 |
Warnings: |
| 1040 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1041 |
Warnings: |
| 1042 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1043 |
|
| 1044 |
Ensure that a handler with a condition defined with an SQLSTATE that begins with |
| 1045 |
anything other that “01“ or “02“ is always exactly equivalent in action to a |
| 1046 |
handler with an SQLEXCEPTION condition. |
| 1047 |
-------------------------------------------------------------------------------- |
| 1048 |
Warnings: |
| 1049 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1050 |
DROP PROCEDURE IF EXISTS sp0; |
| 1051 |
DROP PROCEDURE IF EXISTS sp1; |
| 1052 |
DROP PROCEDURE IF EXISTS sp2; |
| 1053 |
DROP PROCEDURE IF EXISTS sp3; |
| 1054 |
DROP PROCEDURE IF EXISTS sp4; |
| 1055 |
CREATE PROCEDURE sp0() |
| 1056 |
BEGIN |
| 1057 |
DECLARE f1_value CHAR(20); |
| 1058 |
DECLARE cv INT DEFAULT 0; |
| 1059 |
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; |
| 1060 |
SET @x = 1; |
| 1061 |
CASE cv |
| 1062 |
WHEN 2 THEN SET @x = 2; |
| 1063 |
WHEN 3 THEN SET @x = 3; |
| 1064 |
END case; |
| 1065 |
SET @x = 4; |
| 1066 |
SELECT f1, f2 FROM t2 |
| 1067 |
UNION |
| 1068 |
SELECT f1, f2,3 FROM t2; |
| 1069 |
SET @x = 5; |
| 1070 |
FETCH cur1 INTO f1_value; |
| 1071 |
SET @x = 6; |
| 1072 |
END// |
| 1073 |
CREATE PROCEDURE sp1() |
| 1074 |
BEGIN |
| 1075 |
DECLARE f1_value CHAR(20); |
| 1076 |
DECLARE cv INT DEFAULT 0; |
| 1077 |
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; |
| 1078 |
DECLARE continue HANDLER FOR SQLSTATE '20000' SELECT '20000' AS 'SQLSTATE'; |
| 1079 |
DECLARE continue HANDLER FOR SQLSTATE '21000' SELECT '21000' AS 'SQLSTATE'; |
| 1080 |
DECLARE continue HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE'; |
| 1081 |
SET @x = 1; |
| 1082 |
CASE cv |
| 1083 |
WHEN 2 THEN SET @x = 2; |
| 1084 |
WHEN 3 THEN SET @x = 3; |
| 1085 |
END case; |
| 1086 |
SET @x = 4; |
| 1087 |
SELECT f1, f2 FROM t2 |
| 1088 |
UNION |
| 1089 |
SELECT f1, f2,3 FROM t2; |
| 1090 |
SET @x = 5; |
| 1091 |
FETCH cur1 INTO f1_value; |
| 1092 |
SET @x = 6; |
| 1093 |
END// |
| 1094 |
CREATE PROCEDURE sp2() |
| 1095 |
BEGIN |
| 1096 |
DECLARE f1_value CHAR(20); |
| 1097 |
DECLARE cv INT DEFAULT 0; |
| 1098 |
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; |
| 1099 |
DECLARE continue HANDLER FOR SQLEXCEPTION SELECT 'SQLEXCEPTION' AS 'SQLSTATE'; |
| 1100 |
DECLARE continue HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE'; |
| 1101 |
SET @x = 1; |
| 1102 |
CASE cv |
| 1103 |
WHEN 2 THEN SET @x = 2; |
| 1104 |
WHEN 3 THEN SET @x = 3; |
| 1105 |
END case; |
| 1106 |
SET @x = 4; |
| 1107 |
SELECT f1, f2 FROM t2 |
| 1108 |
UNION |
| 1109 |
SELECT f1, f2,3 FROM t2; |
| 1110 |
SET @x = 5; |
| 1111 |
FETCH cur1 INTO f1_value; |
| 1112 |
SET @x = 6; |
| 1113 |
END// |
| 1114 |
CREATE PROCEDURE sp3() |
| 1115 |
BEGIN |
| 1116 |
DECLARE f1_value CHAR(20); |
| 1117 |
DECLARE cv INT DEFAULT 0; |
| 1118 |
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; |
| 1119 |
DECLARE EXIT HANDLER FOR SQLSTATE '20000' SELECT '20000' AS 'SQLSTATE'; |
| 1120 |
DECLARE EXIT HANDLER FOR SQLSTATE '21000' SELECT '21000' AS 'SQLSTATE'; |
| 1121 |
DECLARE EXIT HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE'; |
| 1122 |
SET @x = 1; |
| 1123 |
CASE cv |
| 1124 |
WHEN 2 THEN SET @x = 2; |
| 1125 |
WHEN 3 THEN SET @x = 3; |
| 1126 |
END case; |
| 1127 |
SET @x = 4; |
| 1128 |
SELECT f1, f2 FROM t2 |
| 1129 |
UNION |
| 1130 |
SELECT f1, f2,3 FROM t2; |
| 1131 |
SET @x = 5; |
| 1132 |
FETCH cur1 INTO f1_value; |
| 1133 |
SET @x = 6; |
| 1134 |
END// |
| 1135 |
CREATE PROCEDURE sp4() |
| 1136 |
BEGIN |
| 1137 |
DECLARE f1_value CHAR(20); |
| 1138 |
DECLARE cv INT DEFAULT 0; |
| 1139 |
DECLARE cur1 CURSOR FOR SELECT f1 FROM t2 LIMIT 1; |
| 1140 |
DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLEXCEPTION' AS 'SQLSTATE'; |
| 1141 |
DECLARE EXIT HANDLER FOR SQLSTATE '24000' SELECT '24000' AS 'SQLSTATE'; |
| 1142 |
SET @x = 1; |
| 1143 |
CASE cv |
| 1144 |
WHEN 2 THEN SET @x = 2; |
| 1145 |
WHEN 3 THEN SET @x = 3; |
| 1146 |
END case; |
| 1147 |
SET @x = 4; |
| 1148 |
SELECT f1, f2 FROM t2 |
| 1149 |
UNION |
| 1150 |
SELECT f1, f2,3 FROM t2; |
| 1151 |
SET @x = 5; |
| 1152 |
FETCH cur1 INTO f1_value; |
| 1153 |
SET @x = 6; |
| 1154 |
CLOSE cur1; |
| 1155 |
END// |
| 1156 |
CALL sp0(); |
| 1157 |
ERROR 20000: Case not found for CASE statement |
| 1158 |
SELECT '-0-', @x; |
| 1159 |
-0- @x |
| 1160 |
-0- 1 |
| 1161 |
CALL sp1(); |
| 1162 |
SQLSTATE |
| 1163 |
20000 |
| 1164 |
SQLSTATE |
| 1165 |
21000 |
| 1166 |
SQLSTATE |
| 1167 |
24000 |
| 1168 |
SELECT '-1-', @x; |
| 1169 |
-1- @x |
| 1170 |
-1- 6 |
| 1171 |
CALL sp2(); |
| 1172 |
SQLSTATE |
| 1173 |
SQLEXCEPTION |
| 1174 |
SQLSTATE |
| 1175 |
SQLEXCEPTION |
| 1176 |
SQLSTATE |
| 1177 |
24000 |
| 1178 |
SELECT '-2-', @x; |
| 1179 |
-2- @x |
| 1180 |
-2- 6 |
| 1181 |
CALL sp3(); |
| 1182 |
SQLSTATE |
| 1183 |
20000 |
| 1184 |
SELECT '-3-', @x; |
| 1185 |
-3- @x |
| 1186 |
-3- 1 |
| 1187 |
CALL sp4(); |
| 1188 |
SQLSTATE |
| 1189 |
SQLEXCEPTION |
| 1190 |
SELECT '-4-', @x; |
| 1191 |
-4- @x |
| 1192 |
-4- 1 |
| 1193 |
DROP PROCEDURE sp0; |
| 1194 |
DROP PROCEDURE sp1; |
| 1195 |
DROP PROCEDURE sp2; |
| 1196 |
DROP PROCEDURE sp3; |
| 1197 |
DROP PROCEDURE sp4; |
| 1198 |
Warnings: |
| 1199 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1200 |
|
| 1201 |
Testcase 3.1.2.65: |
| 1202 |
------------------ |
| 1203 |
Warnings: |
| 1204 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1205 |
Warnings: |
| 1206 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1207 |
|
| 1208 |
Ensure that FETCH <cursor name> returns the first row of the cursor_s result set |
| 1209 |
the first time FETCH is executed, that it returns each subsequent row of the |
| 1210 |
cursor_s result set each of the subsequent times FETCH is executed, and that it |
| 1211 |
returns a NOT FOUND warning if it is executed after the last row of the cursor_s |
| 1212 |
result set has already been fetched. |
| 1213 |
-------------------------------------------------------------------------------- |
| 1214 |
Warnings: |
| 1215 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1216 |
DROP PROCEDURE IF EXISTS sp1; |
| 1217 |
DROP TABLE IF EXISTS temp; |
| 1218 |
CREATE TABLE temp( |
| 1219 |
cnt INT, |
| 1220 |
f1 CHAR(20), |
| 1221 |
f2 CHAR(20), |
| 1222 |
f3 INT, |
| 1223 |
f4 CHAR(20), |
| 1224 |
f5 INT); |
| 1225 |
INSERT INTO temp VALUES(0, 'onip', 'abc', 8760, 'xyz', 10); |
| 1226 |
CREATE PROCEDURE sp1( ) |
| 1227 |
BEGIN |
| 1228 |
declare proceed int default 1; |
| 1229 |
declare count integer default 1; |
| 1230 |
declare f1_value char(20); |
| 1231 |
declare f2_value char(20); |
| 1232 |
declare f5_value char(20); |
| 1233 |
declare f4_value integer; |
| 1234 |
declare f6_value integer; |
| 1235 |
declare cur1 cursor for SELECT f1, f2, f4, f5, f6 from t2 |
| 1236 |
where f4 >=-5000 order by f4 limit 3; |
| 1237 |
open cur1; |
| 1238 |
while proceed do |
| 1239 |
SELECT count AS 'loop'; |
| 1240 |
fetch cur1 into f1_value, f2_value, f4_value, f5_value, f6_value; |
| 1241 |
insert into temp values (count, f1_value, f2_value, f4_value, f5_value, f6_value); |
| 1242 |
set count = count + 1; |
| 1243 |
END while; |
| 1244 |
END// |
| 1245 |
CALL sp1(); |
| 1246 |
loop |
| 1247 |
1 |
| 1248 |
loop |
| 1249 |
2 |
| 1250 |
loop |
| 1251 |
3 |
| 1252 |
loop |
| 1253 |
4 |
| 1254 |
ERROR 02000: No data - zero rows fetched, selected, or processed |
| 1255 |
SELECT * FROM temp; |
| 1256 |
cnt f1 f2 f3 f4 f5 |
| 1257 |
0 onip abc 8760 xyz 10 |
| 1258 |
1 a` a` -5000 a` -5000 |
| 1259 |
2 aaa aaa -4999 aaa -4999 |
| 1260 |
3 abaa abaa -4998 abaa -4998 |
| 1261 |
DROP TABLE temp; |
| 1262 |
DROP PROCEDURE sp1; |
| 1263 |
Warnings: |
| 1264 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1265 |
|
| 1266 |
Testcase 3.1.2.68: |
| 1267 |
------------------ |
| 1268 |
Warnings: |
| 1269 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1270 |
Warnings: |
| 1271 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1272 |
|
| 1273 |
Ensure that FETCH <cursor name> fails with an appropriate error message if the |
| 1274 |
number of columns to be fetched does not match the number of variables specified |
| 1275 |
by the FETCH statement. |
| 1276 |
-------------------------------------------------------------------------------- |
| 1277 |
Warnings: |
| 1278 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1279 |
DROP PROCEDURE IF EXISTS sp1; |
| 1280 |
DROP PROCEDURE IF EXISTS sp2; |
| 1281 |
--> not enough columns in FETCH statement |
| 1282 |
CREATE PROCEDURE sp1( ) |
| 1283 |
BEGIN |
| 1284 |
declare newf1 char(20); |
| 1285 |
declare cur1 cursor for SELECT f1, f2 from t2 limit 10; |
| 1286 |
declare continue handler for sqlstate '02000' SELECT 'HANDLER executed.' AS ''; |
| 1287 |
BEGIN |
| 1288 |
open cur1; |
| 1289 |
fetch cur1 into newf1; |
| 1290 |
SELECT newf1; |
| 1291 |
close cur1; |
| 1292 |
END; |
| 1293 |
END// |
| 1294 |
--> too many columns in FETCH statement |
| 1295 |
CREATE PROCEDURE sp2( ) |
| 1296 |
BEGIN |
| 1297 |
declare newf1 char(20); |
| 1298 |
declare newf2 char(20); |
| 1299 |
declare cur1 cursor for SELECT f1 from t2 limit 10; |
| 1300 |
declare continue handler for sqlstate '02000' SELECT 'HANDLER executed.' AS ''; |
| 1301 |
BEGIN |
| 1302 |
open cur1; |
| 1303 |
fetch cur1 into newf1, newf2; |
| 1304 |
SELECT newf1, newf2; |
| 1305 |
close cur1; |
| 1306 |
END; |
| 1307 |
END// |
| 1308 |
--> not enough columns in FETCH statement |
| 1309 |
CALL sp1(); |
| 1310 |
ERROR HY000: Incorrect number of FETCH variables |
| 1311 |
--> too many columns in FETCH statement |
| 1312 |
CALL sp2(); |
| 1313 |
ERROR HY000: Incorrect number of FETCH variables |
| 1314 |
DROP PROCEDURE sp1; |
| 1315 |
DROP PROCEDURE sp2; |
| 1316 |
Warnings: |
| 1317 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1318 |
|
| 1319 |
Testcase 3.1.2.75: |
| 1320 |
------------------ |
| 1321 |
Warnings: |
| 1322 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1323 |
Warnings: |
| 1324 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1325 |
|
| 1326 |
Ensure that, for nested compound statements, a cursor that was declared and |
| 1327 |
opened during an outer level of the statement is not closed when an inner level |
| 1328 |
of a compound statement ends. |
| 1329 |
-------------------------------------------------------------------------------- |
| 1330 |
Warnings: |
| 1331 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1332 |
DROP TABLE IF EXISTS temp1; |
| 1333 |
DROP PROCEDURE IF EXISTS sp1; |
| 1334 |
create table temp1( f0 char(20), f1 char(20), f2 char(20), f3 int, f4 char(20) ); |
| 1335 |
SELECT f1, f2, f4, f5 from t2 order by f4; |
| 1336 |
f1 f2 f4 f5 |
| 1337 |
a` a` -5000 a` |
| 1338 |
aaa aaa -4999 aaa |
| 1339 |
abaa abaa -4998 abaa |
| 1340 |
acaaa acaaa -4997 acaaa |
| 1341 |
adaaaa adaaaa -4996 adaaaa |
| 1342 |
aeaaaaa aeaaaaa -4995 aeaaaaa |
| 1343 |
afaaaaaa afaaaaaa -4994 afaaaaaa |
| 1344 |
agaaaaaaa agaaaaaaa -4993 agaaaaaaa |
| 1345 |
a^aaaaaaaa a^aaaaaaaa -4992 a^aaaaaaaa |
| 1346 |
a_aaaaaaaaa a_aaaaaaaaa -4991 a_aaaaaaaaa |
| 1347 |
CREATE PROCEDURE sp1( ) |
| 1348 |
BEGIN |
| 1349 |
declare count integer; |
| 1350 |
declare from0 char(20); |
| 1351 |
declare newf1 char(20); |
| 1352 |
declare newf2 char(20); |
| 1353 |
declare newf5 char(20); |
| 1354 |
declare newf4 integer; |
| 1355 |
declare cur1 cursor for SELECT f1, f2, f4, f5 from t2 where f4 >= -5000 order by f4 limit 5; |
| 1356 |
declare cur2 cursor for SELECT f1, f2, f4, f5 from t2 where f4 >= -5000 order by f4 limit 5; |
| 1357 |
open cur1; |
| 1358 |
open cur2; |
| 1359 |
BEGIN |
| 1360 |
declare continue handler for sqlstate '02000' set count = 1; |
| 1361 |
fetch cur1 into newf1, newf2, newf4, newf5; |
| 1362 |
SELECT '-1-', count, newf1, newf2, newf4, newf5; |
| 1363 |
insert into temp1 values ('cur1_out', newf1, newf2, newf4, newf5); |
| 1364 |
set count = 4; |
| 1365 |
BEGIN |
| 1366 |
while count > 0 do |
| 1367 |
fetch cur1 into newf1, newf2, newf4, newf5; |
| 1368 |
SELECT '-2-', count, newf1, newf2, newf4, newf5; |
| 1369 |
set count = count - 1; |
| 1370 |
END while; |
| 1371 |
SELECT '-3-', count, newf1, newf2, newf4, newf4; |
| 1372 |
END; |
| 1373 |
BEGIN |
| 1374 |
fetch cur1 into newf1, newf2, newf4, newf5; |
| 1375 |
SELECT '-4-', newf1, newf2, newf4, newf5; |
| 1376 |
insert into temp1 values ('cur1_in', newf1, newf2, newf4, newf5); |
| 1377 |
END; |
| 1378 |
fetch cur2 into newf1, newf2, newf4, newf5; |
| 1379 |
SELECT '-5-', newf1, newf2, newf4, newf5; |
| 1380 |
insert into temp1 values ('cur2', newf1, newf2, newf4, newf5); |
| 1381 |
close cur1; |
| 1382 |
END; |
| 1383 |
fetch cur2 into newf1, newf2, newf4, newf5; |
| 1384 |
SELECT '-6-', newf1, newf2, newf4, newf5; |
| 1385 |
close cur2; |
| 1386 |
END// |
| 1387 |
CALL sp1(); |
| 1388 |
-1- count newf1 newf2 newf4 newf5 |
| 1389 |
-1- NULL a` a` -5000 a` |
| 1390 |
-2- count newf1 newf2 newf4 newf5 |
| 1391 |
-2- 4 aaa aaa -4999 aaa |
| 1392 |
-2- count newf1 newf2 newf4 newf5 |
| 1393 |
-2- 3 abaa abaa -4998 abaa |
| 1394 |
-2- count newf1 newf2 newf4 newf5 |
| 1395 |
-2- 2 acaaa acaaa -4997 acaaa |
| 1396 |
-2- count newf1 newf2 newf4 newf5 |
| 1397 |
-2- 1 adaaaa adaaaa -4996 adaaaa |
| 1398 |
-3- count newf1 newf2 newf4 newf4 |
| 1399 |
-3- 0 adaaaa adaaaa -4996 -4996 |
| 1400 |
-4- newf1 newf2 newf4 newf5 |
| 1401 |
-4- adaaaa adaaaa -4996 adaaaa |
| 1402 |
-5- newf1 newf2 newf4 newf5 |
| 1403 |
-5- a` a` -5000 a` |
| 1404 |
-6- newf1 newf2 newf4 newf5 |
| 1405 |
-6- aaa aaa -4999 aaa |
| 1406 |
SELECT * from temp1; |
| 1407 |
f0 f1 f2 f3 f4 |
| 1408 |
cur1_out a` a` -5000 a` |
| 1409 |
cur1_in adaaaa adaaaa -4996 adaaaa |
| 1410 |
cur2 a` a` -5000 a` |
| 1411 |
DROP PROCEDURE sp1; |
| 1412 |
drop table temp1; |
| 1413 |
Warnings: |
| 1414 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1415 |
|
| 1416 |
Testcase 3.1.2.76: |
| 1417 |
------------------ |
| 1418 |
Warnings: |
| 1419 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1420 |
Warnings: |
| 1421 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1422 |
|
| 1423 |
Ensure that all cursors operate asensitively, so that there is no concurrency |
| 1424 |
conflict between cursors operating on the same, or similar, sets of results |
| 1425 |
during execution of one or more stored procedures. |
| 1426 |
-------------------------------------------------------------------------------- |
| 1427 |
Warnings: |
| 1428 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 1429 |
DROP PROCEDURE IF EXISTS sp1; |
| 1430 |
drop table IF EXISTS temp1; |
| 1431 |
drop table IF EXISTS temp2; |
| 1432 |
create table temp1( f0 char(10), cnt int, f1 char(20), f2 char(20), f3 date, f4 integer ); |
| 1433 |
create table temp2( f0 char(10), cnt int, f1 char(20), f2 char(20), f3 date, f4 integer ); |
| 1434 |
CREATE PROCEDURE sp_inner( ) |
| 1435 |
BEGIN |
| 1436 |
declare proceed int default 1; |
| 1437 |
declare i_count integer default 20; |
| 1438 |
declare i_newf1 char(20); |
| 1439 |
declare i_newf2 char(20); |
| 1440 |
declare i_newf3 date; |
| 1441 |
declare i_newf4 integer; |
| 1442 |
declare i_newf11 char(20); |
| 1443 |
declare i_newf12 char(20); |
| 1444 |
declare i_newf13 date; |
| 1445 |
declare i_newf14 integer; |
| 1446 |
declare cur1 cursor for SELECT f1, f2, f3, f4 from t2 |
| 1447 |
where f4>=-5000 order by f4 limit 4; |
| 1448 |
declare cur2 cursor for SELECT f1, f2, f3, f4 from t2 |
| 1449 |
where f4>=-5000 order by f4 limit 3; |
| 1450 |
declare continue handler for sqlstate '02000' set proceed=0; |
| 1451 |
open cur1; |
| 1452 |
open cur2; |
| 1453 |
set i_count = 10; |
| 1454 |
while proceed do |
| 1455 |
fetch cur1 into i_newf1, i_newf2, i_newf3, i_newf4; |
| 1456 |
IF proceed THEN |
| 1457 |
insert into temp1 values ('sp_inner', i_count, i_newf1, i_newf2, i_newf3, i_newf4); |
| 1458 |
fetch cur2 into i_newf11, i_newf12, i_newf13, i_newf14; |
| 1459 |
IF proceed THEN |
| 1460 |
insert into temp2 values ('sp_inner', i_count, i_newf11, i_newf12, i_newf13, i_newf14); |
| 1461 |
END IF; |
| 1462 |
END IF; |
| 1463 |
set i_count = i_count - 1; |
| 1464 |
END while; |
| 1465 |
close cur1; |
| 1466 |
close cur2; |
| 1467 |
END// |
| 1468 |
CREATE PROCEDURE sp_outer( ) |
| 1469 |
BEGIN |
| 1470 |
DECLARE proceed INT DEFAULT 1; |
| 1471 |
DECLARE o_count INTEGER DEFAULT 20; |
| 1472 |
DECLARE o_newf1 CHAR(20); |
| 1473 |
DECLARE o_newf2 CHAR(20); |
| 1474 |
DECLARE o_newf3 DATE; |
| 1475 |
DECLARE o_newf4 INTEGER; |
| 1476 |
DECLARE o_newf11 CHAR(20); |
| 1477 |
DECLARE o_newf12 CHAR(20); |
| 1478 |
DECLARE o_newf13 DATE; |
| 1479 |
DECLARE o_newf14 INTEGER; |
| 1480 |
DECLARE cur1 CURSOR FOR SELECT f1, f2, f3, f4 FROM t2 |
| 1481 |
WHERE f4>=-5000 ORDER BY f4 LIMIT 5; |
| 1482 |
DECLARE cur2 CURSOR FOR SELECT f1, f2, f3, f4 FROM t2 |
| 1483 |
WHERE f4>=-5000 ORDER BY f4 LIMIT 5; |
| 1484 |
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET proceed=0; |
| 1485 |
OPEN cur1; |
| 1486 |
OPEN cur2; |
| 1487 |
SET o_count = 1; |
| 1488 |
WHILE proceed DO |
| 1489 |
FETCH cur1 INTO o_newf1, o_newf2, o_newf3, o_newf4; |
| 1490 |
IF proceed THEN |
| 1491 |
INSERT INTO temp1 VALUES ('_sp_out_', o_count, o_newf1, o_newf2, o_newf3, o_newf4); |
| 1492 |
CALL sp_inner(); |
| 1493 |
FETCH cur2 INTO o_newf11, o_newf12, o_newf13, o_newf14; |
| 1494 |
IF proceed THEN |
| 1495 |
INSERT INTO temp2 VALUES ('_sp_out_', o_count, o_newf11, o_newf12, o_newf13, o_newf14); |
| 1496 |
END IF; |
| 1497 |
END IF; |
| 1498 |
SET o_count = o_count + 1; |
| 1499 |
END WHILE; |
| 1500 |
CLOSE cur1; |
| 1501 |
CLOSE cur2; |
| 1502 |
END// |
| 1503 |
CALL sp_outer(); |
| 1504 |
SELECT * FROM temp1; |
| 1505 |
f0 cnt f1 f2 f3 f4 |
| 1506 |
_sp_out_ 1 a` a` 1000-01-01 -5000 |
| 1507 |
sp_inner 10 a` a` 1000-01-01 -5000 |
| 1508 |
sp_inner 9 aaa aaa 1000-01-02 -4999 |
| 1509 |
sp_inner 8 abaa abaa 1000-01-03 -4998 |
| 1510 |
sp_inner 7 acaaa acaaa 1000-01-04 -4997 |
| 1511 |
_sp_out_ 2 aaa aaa 1000-01-02 -4999 |
| 1512 |
sp_inner 10 a` a` 1000-01-01 -5000 |
| 1513 |
sp_inner 9 aaa aaa 1000-01-02 -4999 |
| 1514 |
sp_inner 8 abaa abaa 1000-01-03 -4998 |
| 1515 |
sp_inner 7 acaaa acaaa 1000-01-04 -4997 |
| 1516 |
_sp_out_ 3 abaa abaa 1000-01-03 -4998 |
| 1517 |
sp_inner 10 a` a` 1000-01-01 -5000 |
| 1518 |
sp_inner 9 aaa aaa 1000-01-02 -4999 |
| 1519 |
sp_inner 8 abaa abaa 1000-01-03 -4998 |
| 1520 |
sp_inner 7 acaaa acaaa 1000-01-04 -4997 |
| 1521 |
_sp_out_ 4 acaaa acaaa 1000-01-04 -4997 |
| 1522 |
sp_inner 10 a` a` 1000-01-01 -5000 |
| 1523 |
sp_inner 9 aaa aaa 1000-01-02 -4999 |
| 1524 |
sp_inner 8 abaa abaa 1000-01-03 -4998 |
| 1525 |
sp_inner 7 acaaa acaaa 1000-01-04 -4997 |
| 1526 |
_sp_out_ 5 adaaaa adaaaa 1000-01-05 -4996 |
| 1527 |
sp_inner 10 a` a` 1000-01-01 -5000 |
| 1528 |
sp_inner 9 aaa aaa 1000-01-02 -4999 |
| 1529 |
sp_inner 8 abaa abaa 1000-01-03 -4998 |
| 1530 |
sp_inner 7 acaaa acaaa 1000-01-04 -4997 |
| 1531 |
SELECT * FROM temp2; |
| 1532 |
f0 cnt f1 f2 f3 f4 |
| 1533 |
sp_inner 10 a` a` 1000-01-01 -5000 |
| 1534 |
sp_inner 9 aaa aaa 1000-01-02 -4999 |
| 1535 |
sp_inner 8 abaa abaa 1000-01-03 -4998 |
| 1536 |
_sp_out_ 1 a` a` 1000-01-01 -5000 |
| 1537 |
sp_inner 10 a` a` 1000-01-01 -5000 |
| 1538 |
sp_inner 9 aaa aaa 1000-01-02 -4999 |
| 1539 |
sp_inner 8 abaa abaa 1000-01-03 -4998 |
| 1540 |
_sp_out_ 2 aaa aaa 1000-01-02 -4999 |
| 1541 |
sp_inner 10 a` a` 1000-01-01 -5000 |
| 1542 |
sp_inner 9 aaa aaa 1000-01-02 -4999 |
| 1543 |
sp_inner 8 abaa abaa 1000-01-03 -4998 |
| 1544 |
_sp_out_ 3 abaa abaa 1000-01-03 -4998 |
| 1545 |
sp_inner 10 a` a` 1000-01-01 -5000 |
| 1546 |
sp_inner 9 aaa aaa 1000-01-02 -4999 |
| 1547 |
sp_inner 8 abaa abaa 1000-01-03 -4998 |
| 1548 |
_sp_out_ 4 acaaa acaaa 1000-01-04 -4997 |
| 1549 |
sp_inner 10 a` a` 1000-01-01 -5000 |
| 1550 |
sp_inner 9 aaa aaa 1000-01-02 -4999 |
| 1551 |
sp_inner 8 abaa abaa 1000-01-03 -4998 |
| 1552 |
_sp_out_ 5 adaaaa adaaaa 1000-01-05 -4996 |
| 1553 |
DROP PROCEDURE sp_outer; |
| 1554 |
DROP PROCEDURE sp_inner; |
| 1555 |
DROP TABLE temp1; |
| 1556 |
DROP TABLE temp2; |
| 1557 |
SET sql_mode = default; |
| 1558 |
|
| 1559 |
--source suite/funcs_1/storedproc/cleanup_sp_tb.inc |
| 1560 |
-------------------------------------------------------------------------------- |
| 1561 |
DROP DATABASE IF EXISTS db_storedproc; |
| 1562 |
DROP DATABASE IF EXISTS db_storedproc_1; |
| 1563 |
|
| 1564 |
. +++ END OF SCRIPT +++ |
| 1565 |
-------------------------------------------------------------------------------- |