Debian Codegraph @

mysql-test/suite/compat/oracle/t/exception.test

raw
1 SET sql_mode=ORACLE;
2
3 --echo #
4 --echo # sql_mode=ORACLE: Predefined exceptions: TOO_MANY_ROWS, NO_DATA_FOUND, DUP_VAL_ON_INDEX
5 --echo #
6
7 --echo #
8 --echo # Testing NO_DATA_FOUND and TOO_MANY_ROWS
9 --echo #
10
11 CREATE TABLE t1 (a INT);
12 INSERT INTO t1 VALUES (10),(20);
13 DELIMITER $$;
14 CREATE PROCEDURE p1(lim INT, res OUT VARCHAR)
15 AS
16 a INT;
17 BEGIN
18 SELECT a INTO a FROM t1 LIMIT lim;
19 EXCEPTION
20 WHEN TOO_MANY_ROWS THEN res:='--- too_many_rows cought ---';
21 WHEN NO_DATA_FOUND THEN res:='--- no_data_found cought ---';
22 END;
23 $$
24 DELIMITER ;$$
25 SET @res='';
26 CALL p1(0, @res);
27 SELECT @res;
28 CALL p1(2, @res);
29 SELECT @res;
30 DROP PROCEDURE p1;
31 DROP TABLE t1;
32
33 --echo #
34 --echo # Testing DUP_VAL_ON_INDEX
35 --echo #
36
37 CREATE TABLE t1 (a INT PRIMARY KEY);
38 DELIMITER $$;
39 CREATE PROCEDURE p1(res OUT VARCHAR)
40 AS
41 BEGIN
42 INSERT INTO t1 VALUES (10);
43 INSERT INTO t1 VALUES (10);
44 EXCEPTION
45 WHEN DUP_VAL_ON_INDEX THEN res:='--- dup_val_on_index cought ---';
46 END;
47 $$
48 DELIMITER ;$$
49 SET @res='';
50 CALL p1(@res);
51 SELECT @res;
52 SELECT * FROM t1;
53 DROP PROCEDURE p1;
54 DROP TABLE t1;
55
56
57 --echo #
58 --echo # MDEV-10840 sql_mode=ORACLE: RAISE statement for predefined exceptions
59 --echo #
60
61 --echo #
62 --echo # RAISE outside of an SP context
63 --echo #
64
65 --error ER_SP_COND_MISMATCH
66 RAISE NO_DATA_FOUND;
67 --error ER_SP_COND_MISMATCH
68 RAISE INVALID_CURSOR;
69 --error ER_SP_COND_MISMATCH
70 RAISE DUP_VAL_ON_INDEX;
71 --error ER_SP_COND_MISMATCH
72 RAISE TOO_MANY_ROWS;
73
74 --error ER_RESIGNAL_WITHOUT_ACTIVE_HANDLER
75 RAISE;
76
77
78 --echo #
79 --echo # RAISE for an undefinite exception
80 --echo #
81
82 DELIMITER $$;
83 --error ER_SP_COND_MISMATCH
84 CREATE PROCEDURE p1
85 AS
86 BEGIN
87 RAISE xxx;
88 END;
89 $$
90 DELIMITER ;$$
91
92
93 --echo #
94 --echo # RAISE for predefined exceptions
95 --echo #
96
97 DELIMITER $$;
98 CREATE PROCEDURE p1
99 AS
100 BEGIN
101 RAISE no_data_found;
102 END;
103 $$
104 DELIMITER ;$$
105 CALL p1();
106 DROP PROCEDURE p1;
107
108 DELIMITER $$;
109 CREATE PROCEDURE p1
110 AS
111 BEGIN
112 RAISE invalid_cursor;
113 END;
114 $$
115 DELIMITER ;$$
116 --error ER_SP_CURSOR_NOT_OPEN
117 CALL p1();
118 DROP PROCEDURE p1;
119
120 DELIMITER $$;
121 CREATE PROCEDURE p1
122 AS
123 BEGIN
124 RAISE dup_val_on_index;
125 END;
126 $$
127 DELIMITER ;$$
128 --error ER_DUP_ENTRY
129 CALL p1();
130 DROP PROCEDURE p1;
131
132 DELIMITER $$;
133 CREATE PROCEDURE p1
134 AS
135 BEGIN
136 raise too_many_rows;
137 END;
138 $$
139 DELIMITER ;$$
140 --error ER_TOO_MANY_ROWS
141 CALL p1();
142 DROP PROCEDURE p1;
143
144
145 --echo #
146 --echo # RAISE with no exception name (resignal)
147 --echo #
148
149 DELIMITER $$;
150 CREATE PROCEDURE p1()
151 AS
152 BEGIN
153 RAISE;
154 END;
155 $$
156 DELIMITER ;$$
157 --error ER_RESIGNAL_WITHOUT_ACTIVE_HANDLER
158 CALL p1();
159 DROP PROCEDURE p1;
160
161
162 CREATE TABLE t1 (a INT);
163 INSERT INTO t1 VALUES (10),(20);
164 DELIMITER $$;
165 CREATE PROCEDURE p1(lim INT)
166 AS
167 a INT;
168 BEGIN
169 SELECT a INTO a FROM t1 LIMIT lim;
170 EXCEPTION
171 WHEN TOO_MANY_ROWS THEN RAISE;
172 WHEN NO_DATA_FOUND THEN RAISE;
173 END;
174 $$
175 DELIMITER ;$$
176 CALL p1(0);
177 --error ER_TOO_MANY_ROWS
178 CALL p1(2);
179 DROP PROCEDURE p1;
180 DROP TABLE t1;
181
182
183 CREATE TABLE t1 (a INT);
184 INSERT INTO t1 VALUES (10),(20);
185 DELIMITER $$;
186 CREATE PROCEDURE p1(lim INT)
187 AS
188 a INT;
189 BEGIN
190 SELECT a INTO a FROM t1 LIMIT lim;
191 EXCEPTION
192 WHEN OTHERS THEN RAISE;
193 END;
194 $$
195 DELIMITER ;$$
196 CALL p1(0);
197 --error ER_TOO_MANY_ROWS
198 CALL p1(2);
199 DROP PROCEDURE p1;
200 DROP TABLE t1;
201
202
203 CREATE TABLE t1 (a INT);
204 INSERT INTO t1 VALUES (10),(20);
205 DELIMITER $$;
206 CREATE PROCEDURE p1()
207 AS
208 a INT;
209 CURSOR c IS SELECT a FROM t1;
210 BEGIN
211 FETCH c INTO a;
212 EXCEPTION
213 WHEN INVALID_CURSOR THEN RAISE;
214 END;
215 $$
216 DELIMITER ;$$
217 --error ER_SP_CURSOR_NOT_OPEN
218 CALL p1();
219 DROP PROCEDURE p1;
220 DROP TABLE t1;
221
222
223 CREATE TABLE t1 (a INT);
224 INSERT INTO t1 VALUES (10),(20);
225 DELIMITER $$;
226 CREATE PROCEDURE p1()
227 AS
228 a INT;
229 CURSOR c IS SELECT a FROM t1;
230 BEGIN
231 FETCH c INTO a;
232 EXCEPTION
233 WHEN OTHERS THEN RAISE;
234 END;
235 $$
236 DELIMITER ;$$
237 --error ER_SP_CURSOR_NOT_OPEN
238 CALL p1();
239 DROP PROCEDURE p1;
240 DROP TABLE t1;
241
242 --echo #
243 --echo # Testing that warning-alike errors are caught by OTHERS
244 --echo #
245
246 CREATE TABLE t1 (a INT);
247 DELIMITER $$;
248 CREATE FUNCTION f1 RETURN VARCHAR
249 AS
250 a INT:=10;
251 BEGIN
252 SELECT a INTO a FROM t1;
253 RETURN 'OK';
254 EXCEPTION
255 WHEN OTHERS THEN RETURN 'Exception';
256 END;
257 $$
258 DELIMITER ;$$
259 SELECT f1() FROM DUAL;
260 DROP FUNCTION f1;
261 DROP TABLE t1;
262
263
264 --echo #
265 --echo # End of MDEV-10840 sql_mode=ORACLE: RAISE statement for predefined exceptions
266 --echo #
267
268
269 --echo #
270 --echo # MDEV-10587 sql_mode=ORACLE: User defined exceptions
271 --echo #
272
273 --echo #
274 --echo # Checking that duplicate WHEN clause is not allowed
275 --echo #
276
277 DELIMITER $$;
278 --error ER_SP_DUP_HANDLER
279 CREATE FUNCTION f1() RETURN VARCHAR
280 AS
281 e EXCEPTION;
282 BEGIN
283 RETURN 'Got no exceptions';
284 EXCEPTION
285 WHEN e THEN RETURN 'Got exception e';
286 WHEN e THEN RETURN 'Got exception e';
287 END;
288 $$
289 DELIMITER ;$$
290
291
292 --echo #
293 --echo # Checking that raised user exceptions are further caught by name
294 --echo #
295
296 DELIMITER $$;
297 CREATE FUNCTION f1(c VARCHAR) RETURN VARCHAR
298 AS
299 e EXCEPTION;
300 f EXCEPTION;
301 BEGIN
302 IF c = 'e' THEN RAISE e; END IF;
303 IF c = 'f' THEN RAISE f; END IF;
304 RETURN 'Got no exceptions';
305 EXCEPTION
306 WHEN e THEN RETURN 'Got exception e';
307 END;
308 $$
309 DELIMITER ;$$
310 SELECT f1('');
311 SELECT f1('e');
312 --error ER_SIGNAL_EXCEPTION
313 SELECT f1('f');
314 DROP FUNCTION f1;
315
316
317 --echo #
318 --echo # Checking that raised user exceptions are further caught by OTHERS
319 --echo #
320
321 DELIMITER $$;
322 CREATE FUNCTION f1(c VARCHAR) RETURN VARCHAR
323 AS
324 e EXCEPTION;
325 f EXCEPTION;
326 BEGIN
327 IF c = 'e' THEN RAISE e; END IF;
328 IF c = 'f' THEN RAISE f; END IF;
329 RETURN 'Got no exceptions';
330 EXCEPTION
331 WHEN OTHERS THEN RETURN 'Got some exception';
332 END;
333 $$
334 DELIMITER ;$$
335 SELECT f1('');
336 SELECT f1('e');
337 SELECT f1('f');
338 DROP FUNCTION f1;
339
340
341 --echo #
342 --echo # Checking that 'WHEN e .. WHEN f' does not produce ER_SP_DUP_HANDLER
343 --echo #
344
345 DELIMITER $$;
346 CREATE FUNCTION f1(c VARCHAR) RETURN VARCHAR
347 AS
348 e EXCEPTION;
349 f EXCEPTION;
350 a VARCHAR(64):='';
351 BEGIN
352 BEGIN
353 IF c = 'e' THEN RAISE e; END IF;
354 IF c = 'f' THEN RAISE f; END IF;
355 EXCEPTION
356 WHEN e THEN BEGIN a:='Got EXCEPTION1/e; '; RAISE e; END;
357 WHEN f THEN BEGIN a:='Got EXCEPTION1/f; '; RAISE f; END;
358 END;
359 RETURN 'Got no exceptions';
360 EXCEPTION
361 WHEN OTHERS THEN RETURN a || 'Got EXCEPTION2/OTHERS;';
362 END;
363 $$
364 DELIMITER ;$$
365 SELECT f1('');
366 SELECT f1('e');
367 SELECT f1('f');
368 DROP FUNCTION f1;
369
370
371 --echo #
372 --echo # Checking that resignaled user exceptions are further caught by name
373 --echo #
374 DELIMITER $$;
375 CREATE FUNCTION f1(c VARCHAR) RETURN VARCHAR
376 AS
377 e EXCEPTION;
378 f EXCEPTION;
379 a VARCHAR(64):='';
380 BEGIN
381 BEGIN
382 IF c = 'e' THEN RAISE e; END IF;
383 IF c = 'f' THEN RAISE f; END IF;
384 EXCEPTION
385 WHEN e THEN BEGIN a:='Got EXCEPTION1/e; '; RAISE; END;
386 WHEN f THEN BEGIN a:='Got EXCEPTION1/f; '; RAISE; END;
387 END;
388 RETURN 'Got no exceptions';
389 EXCEPTION
390 WHEN e THEN RETURN a || 'Got EXCEPTION2/e;';
391 END;
392 $$
393 DELIMITER ;$$
394 SELECT f1('');
395 SELECT f1('e');
396 --error ER_SIGNAL_EXCEPTION
397 SELECT f1('f');
398 DROP FUNCTION f1;
399
400
401 --echo #
402 --echo # Checking that resignaled user exceptions are further caught by OTHERS
403 --echo #
404
405 DELIMITER $$;
406 CREATE FUNCTION f1(c VARCHAR) RETURN VARCHAR
407 AS
408 e EXCEPTION;
409 f EXCEPTION;
410 a VARCHAR(64):='';
411 BEGIN
412 BEGIN
413 IF c = 'e' THEN RAISE e; END IF;
414 IF c = 'f' THEN RAISE f; END IF;
415 EXCEPTION
416 WHEN e THEN BEGIN a:='Got EXCEPTION1/e; '; RAISE; END;
417 WHEN f THEN BEGIN a:='Got EXCEPTION1/f; '; RAISE; END;
418 END;
419 RETURN 'Got no exceptions';
420 EXCEPTION
421 WHEN OTHERS THEN RETURN a || 'Got EXCEPTION2/OTHERS;';
422 END;
423 $$
424 DELIMITER ;$$
425 SELECT f1('');
426 SELECT f1('e');
427 SELECT f1('f');
428 DROP FUNCTION f1;
429
430
431 --echo #
432 --echo # End of MDEV-10587 sql_mode=ORACLE: User defined exceptions
433 --echo #
434
435 --echo #
436 --echo # MDEV-12088 sql_mode=ORACLE: Do not require BEGIN..END in multi-statement exception handlers in THEN clause
437 --echo #
438 CREATE TABLE t1 (a INT PRIMARY KEY);
439 INSERT INTO t1 VALUES (10),(20),(30);
440 DELIMITER $$;
441 CREATE PROCEDURE p1(a INT) AS
442 BEGIN
443 INSERT INTO t1 (a) VALUES (a);
444 EXCEPTION
445 WHEN DUP_VAL_ON_INDEX THEN
446 a:= a+1;
447 INSERT INTO t1 VALUES (a);
448 WHEN OTHERS THEN
449 NULL;
450 NULL;
451 END;
452 $$
453 DELIMITER ;$$
454 CALL p1(30);
455 SELECT * FROM t1;
456 DROP PROCEDURE p1;
457 DROP TABLE t1;