Debian Codegraph @

mysql-test/suite/compat/oracle/t/sp-param.test

raw
1 --source include/test_db_charset_latin1.inc
2
3 SET sql_mode=ORACLE;
4
5 --echo #
6 --echo # MDEV-10596 Allow VARCHAR and VARCHAR2 without length as a data type of routine parameters and in RETURN clause
7 --echo #
8
9 --let type = CHAR
10 --let length = 2000
11 --source sp-param.inc
12
13 --let type = NCHAR
14 --let length = 2000
15 --source sp-param.inc
16
17 --let type = BINARY
18 --let length = 2000
19 --source sp-param.inc
20
21 --let type = VARCHAR
22 --let length = 4000
23 --source sp-param.inc
24
25 --let type = VARCHAR2
26 --let length = 4000
27 --source sp-param.inc
28
29 --let type = NVARCHAR
30 --let length = 4000
31 --source sp-param.inc
32
33 --let type = VARBINARY
34 --let length = 4000
35 --source sp-param.inc
36
37 --let type = RAW
38 --let length = 4000
39 --source sp-param.inc
40
41 --echo
42 --echo MDEV-13919 sql_mode=ORACLE: Derive length of VARCHAR SP parameters with no length from actual parameters
43 --echo
44 set sql_mode= 'oracle,strict_trans_tables';
45 delimiter /;
46 CREATE OR REPLACE PROCEDURE p1(pinout INOUT varchar, pin IN varchar)
47 AS
48 BEGIN
49 pinout:=pin;
50 END;
51 /
52 call p1(@w,'0123456789')
53 /
54 declare w varchar(10);
55 begin
56 call p1(w,'0123456789');
57 end;
58 /
59 --error ER_DATA_TOO_LONG
60 declare w varchar(5);
61 begin
62 call p1(w,'0123456789');
63 end;
64 /
65 declare w varchar(20);
66 begin
67 w:='aaa';
68 call p1(w,'0123456789');
69 end;
70 /
71 --error ER_DATA_TOO_LONG
72 declare w varchar(8);
73 begin
74 w:='aaa';
75 call p1(w,'0123456789');
76 end;
77 /
78 declare str varchar(6000);
79 pout varchar(6000);
80 begin
81 str:=lpad('x',6000,'y');
82 call p1(pout,str);
83 select length(pout);
84 end;
85 /
86 --error ER_DATA_TOO_LONG
87 declare str varchar(6000);
88 pout varchar(4000);
89 begin
90 str:=lpad('x',6000,'y');
91 call p1(pout,str);
92 select length(pout);
93 end;
94 /
95 declare str varchar(40000);
96 pout varchar(60000);
97 begin
98 str:=lpad('x',40000,'y');
99 call p1(pout,str);
100 select length(pout);
101 end;
102 /
103 --error ER_DATA_TOO_LONG
104 declare str text(80000);
105 pout text(80000);
106 begin
107 str:=lpad('x',80000,'y');
108 call p1(pout,str);
109 select length(pout);
110 end;
111 /
112 declare str text(80000);
113 pout text(80000);
114 begin
115 str:=lpad('x',60000,'y');
116 call p1(pout,str);
117 select length(pout);
118 end;
119 /
120 drop procedure p1
121 /
122 DELIMITER ;/
123
124
125 #
126 # Procedure, non-strict mode
127 #
128
129 SET sql_mode=ORACLE;
130 DELIMITER /;
131 CREATE PROCEDURE p1(pinout INOUT varchar, pin IN varchar)
132 AS
133 BEGIN
134 pinout:=pin;
135 END;
136 /
137 CREATE PROCEDURE p2(len INT)
138 AS
139 pinout VARCHAR(10);
140 pin VARCHAR(30);
141 BEGIN
142 pin:= REPEAT('x', len);
143 p1(pinout, pin);
144 SELECT LENGTH(pinout);
145 END;
146 /
147 DELIMITER ;/
148 CALL p2(10);
149 CALL p2(11);
150 DROP PROCEDURE p1;
151 DROP PROCEDURE p2;
152
153
154 #
155 # Function, not-strict mode
156 #
157
158 SET sql_mode=ORACLE;
159 DELIMITER /;
160 CREATE FUNCTION f1(pin VARCHAR, padlen INT) RETURN TEXT
161 AS
162 BEGIN
163 pin:=LPAD(pin, padlen);
164 RETURN pin;
165 END;
166 /
167 CREATE PROCEDURE p2(padlen INT) AS
168 str TEXT :='x';
169 BEGIN
170 SELECT LENGTH(f1(str,padlen));
171 END;
172 /
173 DELIMITER ;/
174 CALL p2(65535);
175 CALL p2(65536);
176 DROP PROCEDURE p2;
177 DROP FUNCTION f1;
178
179
180 #
181 # Procedure, utf8 formal parameter, latin actual parameter
182 #
183
184 SET sql_mode='ORACLE,STRICT_TRANS_TABLES';
185 DELIMITER /;
186 CREATE PROCEDURE p1(pinout INOUT VARCHAR CHARACTER SET utf8,
187 pin IN VARCHAR CHARACTER SET utf8)
188 AS
189 BEGIN
190 pinout:=pin;
191 END;
192 /
193 CREATE PROCEDURE p2(padlen INT) AS
194 str VARCHAR(40000) CHARACTER SET latin1;
195 pout VARCHAR(60000) CHARACTER SET latin1;
196 BEGIN
197 str:=lpad('x',padlen,'y');
198 p1(pout,str);
199 SELECT length(pout);
200 END;
201 /
202 DELIMITER ;/
203 CALL p2(21844);
204 --error ER_DATA_TOO_LONG
205 CALL p2(21845);
206 --error ER_DATA_TOO_LONG
207 CALL p2(21846);
208 DROP PROCEDURE p2;
209 DROP PROCEDURE p1;
210
211
212 #
213 # Procedure, utf8 formal parameter, utf8 actual parameter
214 #
215
216 SET sql_mode='ORACLE,STRICT_TRANS_TABLES';
217 DELIMITER /;
218 CREATE PROCEDURE p1(pinout INOUT VARCHAR CHARACTER SET utf8,
219 pin IN VARCHAR CHARACTER SET utf8)
220 AS
221 BEGIN
222 pinout:=pin;
223 END;
224 /
225 CREATE PROCEDURE p2(padlen INT) AS
226 str TEXT CHARACTER SET utf8;
227 pout TEXT CHARACTER SET utf8;
228 BEGIN
229 str:=lpad('x',padlen,'y');
230 p1(pout,str);
231 SELECT length(pout);
232 END;
233 /
234 DELIMITER ;/
235 CALL p2(21844);
236 --error ER_DATA_TOO_LONG
237 CALL p2(21845);
238 --error ER_DATA_TOO_LONG
239 CALL p2(21846);
240 DROP PROCEDURE p2;
241 DROP PROCEDURE p1;
242
243
244 #
245 # Function, latin1 formal parameter, latin1 actual parameter
246 #
247
248 SET sql_mode='ORACLE,STRICT_TRANS_TABLES';
249 DELIMITER /;
250 CREATE FUNCTION f1(pin VARCHAR CHARACTER SET latin1, padlen INT) RETURN TEXT
251 AS
252 BEGIN
253 pin:=LPAD(pin, padlen);
254 RETURN pin;
255 END;
256 /
257 CREATE PROCEDURE p2(padlen INT) AS
258 str TEXT CHARACTER SET latin1 :='x';
259 BEGIN
260 SELECT LENGTH(f1(str,padlen));
261 END;
262 /
263 DELIMITER ;/
264 CALL p2(65532);
265 --error ER_DATA_TOO_LONG
266 CALL p2(65533);
267 --error ER_DATA_TOO_LONG
268 CALL p2(65534);
269 --error ER_DATA_TOO_LONG
270 CALL p2(65535);
271 --error ER_DATA_TOO_LONG
272 CALL p2(65536);
273 DROP PROCEDURE p2;
274 DROP FUNCTION f1;
275
276
277 #
278 # Function, utf8 formal parameter, utf8 actual parameter
279 #
280
281 SET sql_mode='ORACLE,STRICT_TRANS_TABLES';
282 DELIMITER /;
283 CREATE FUNCTION f1(pin VARCHAR CHARACTER SET utf8, padlen INT) RETURN TEXT
284 AS
285 BEGIN
286 pin:=LPAD(pin, padlen);
287 RETURN pin;
288 END;
289 /
290 CREATE PROCEDURE p2(padlen INT) AS
291 str TEXT CHARACTER SET utf8 := 'x';
292 BEGIN
293 SELECT LENGTH(f1(str,padlen));
294 END;
295 /
296 DELIMITER ;/
297 CALL p2(21844);
298 --error ER_DATA_TOO_LONG
299 CALL p2(21845);
300 --error ER_DATA_TOO_LONG
301 CALL p2(21846);
302 DROP PROCEDURE p2;
303 DROP FUNCTION f1;
304
305
306 #
307 # Function, utf8 formal parameter, latin1 actual parameter
308 #
309
310 SET sql_mode='ORACLE,STRICT_TRANS_TABLES';
311 DELIMITER /;
312 CREATE FUNCTION f1(pin VARCHAR CHARACTER SET utf8, padlen INT) RETURN TEXT
313 AS
314 BEGIN
315 pin:=LPAD(pin, padlen);
316 RETURN pin;
317 END;
318 /
319 CREATE PROCEDURE p2(padlen INT) AS
320 str TEXT CHARACTER SET latin1 := 'x';
321 BEGIN
322 SELECT LENGTH(f1(str,padlen));
323 END;
324 /
325 DELIMITER ;/
326 CALL p2(21844);
327 --error ER_DATA_TOO_LONG
328 CALL p2(21845);
329 --error ER_DATA_TOO_LONG
330 CALL p2(21846);
331 DROP PROCEDURE p2;
332 DROP FUNCTION f1;
333
334
335 #
336 # Function, latin1 formal parameter, utf8 actual parameter
337 #
338
339 SET sql_mode='ORACLE,STRICT_TRANS_TABLES';
340 DELIMITER /;
341 CREATE FUNCTION f1(pin VARCHAR CHARACTER SET latin1, padlen INT) RETURN TEXT
342 AS
343 BEGIN
344 pin:=LPAD(pin, padlen);
345 RETURN pin;
346 END;
347 /
348 CREATE PROCEDURE p2(padlen INT) AS
349 str TEXT CHARACTER SET utf8 := 'x';
350 BEGIN
351 SELECT LENGTH(f1(str,padlen));
352 END;
353 /
354 DELIMITER ;/
355 CALL p2(65532);
356 --error ER_DATA_TOO_LONG
357 CALL p2(65533);
358 --error ER_DATA_TOO_LONG
359 CALL p2(65534);
360 --error ER_DATA_TOO_LONG
361 CALL p2(65535);
362 --error ER_DATA_TOO_LONG
363 CALL p2(65536);
364 DROP PROCEDURE p2;
365 DROP FUNCTION f1;
366
367 --source include/test_db_charset_restore.inc
368
369 --echo #
370 --echo # MDEV-34316 sql_mode=ORACLE: Ignore the NOCOPY keyword in stored routine parameters
371 --echo #
372
373 SET sql_mode=ORACLE;
374
375 --echo #
376 --echo # sql_mode=ORACLE. Test with function
377 --echo #
378
379 DELIMITER /;
380
381 CREATE OR REPLACE FUNCTION example_func(
382 p_in1 IN VARCHAR(255),
383 p_in2 IN NOCOPY VARCHAR(255),
384 p_out1 OUT INT,
385 p_out2 OUT NOCOPY INT,
386 p_in_out1 IN OUT VARCHAR(255),
387 p_in_out2 IN OUT NOCOPY VARCHAR(255),
388 p_in_out3 INOUT NUMBER,
389 p_in_out4 INOUT NOCOPY NUMBER) RETURN NUMBER AS
390 BEGIN
391 RETURN 0;
392 END;
393 /
394
395 DELIMITER ;/
396
397 DROP FUNCTION example_func;
398
399 --echo #
400 --echo # sql_mode=ORACLE. Test with procedure
401 --echo #
402
403 DELIMITER /;
404
405 CREATE OR REPLACE PROCEDURE example_proc(
406 p_in1 IN VARCHAR(255),
407 p_in2 IN NOCOPY VARCHAR(255),
408 p_out1 OUT INT,
409 p_out2 OUT NOCOPY INT,
410 p_in_out1 IN OUT VARCHAR(255),
411 p_in_out2 IN OUT NOCOPY VARCHAR(255),
412 p_in_out3 INOUT NUMBER,
413 p_in_out4 INOUT NOCOPY NUMBER) AS
414 BEGIN
415 END;
416 /
417
418 DELIMITER ;/
419
420 DROP PROCEDURE example_proc;
421
422 SET sql_mode=DEFAULT;
423 DELIMITER $$;
424
425 --echo #
426 --echo # sql_mode=DEFAULT to perform the negative test case. Test with function, IN NOCOPY
427 --echo #
428
429 --error ER_UNKNOWN_DATA_TYPE
430 CREATE OR REPLACE FUNCTION example_func(IN NOCOPY p_in INT) RETURNS INT
431 BEGIN
432 RETURN 0;
433 END;
434 $$
435
436 --echo #
437 --echo # sql_mode=DEFAULT to perform the negative test case. Test with function, OUT NOCOPY
438 --echo #
439
440 --error ER_UNKNOWN_DATA_TYPE
441 CREATE OR REPLACE FUNCTION example_func(OUT NOCOPY p_out INT) RETURNS INT
442 BEGIN
443 RETURN 0;
444 END;
445 $$
446
447 --echo #
448 --echo # sql_mode=DEFAULT to perform the negative test case. Test with function, INOUT NOCOPY
449 --echo #
450
451 --error ER_UNKNOWN_DATA_TYPE
452 CREATE OR REPLACE FUNCTION example_func(INOUT NOCOPY p_inout INT) RETURNS INT
453 BEGIN
454 RETURN 0;
455 END;
456 $$
457
458 --echo #
459 --echo # sql_mode=DEFAULT to perform the negative test case. Test with procedure, IN NOCOPY
460 --echo #
461
462 --error ER_UNKNOWN_DATA_TYPE
463 CREATE OR REPLACE PROCEDURE example_proc(IN NOCOPY p_in INT)
464 BEGIN
465 END;
466 $$
467
468 --echo #
469 --echo # sql_mode=DEFAULT to perform the negative test case. Test with procedure, OUT NOCOPY
470 --echo #
471
472 --error ER_UNKNOWN_DATA_TYPE
473 CREATE OR REPLACE PROCEDURE example_proc(OUT NOCOPY p_out INT)
474 BEGIN
475 END;
476 $$
477
478 --echo #
479 --echo # sql_mode=DEFAULT to perform the negative test case. Test with procedure, INOUT NOCOPY
480 --echo #
481
482 --error ER_UNKNOWN_DATA_TYPE
483 CREATE OR REPLACE PROCEDURE example_proc(INOUT NOCOPY p_inout INT)
484 BEGIN
485 END;
486 $$
487
488 DELIMITER ;$$
489
490
491 --echo #
492 --echo # MDEV-35229 NOCOPY has become reserved word bringing wide incompatibility
493 --echo #
494
495 SET sql_mode=ORACLE;
496 --let keyword=nocopy
497 --source include/keyword_non_reserved.inc
498
499 # Statements like "CREATE FUNCTION nocopy" and "SELECT nocopy()" below
500 # erroneously print a warning:
501 # This function 'nocopy' has the same name as a native function
502 # Though it's not really a function, it's just a keyword.
503 # The warning is not printed with ps protocol. Let's disable ps protocol.
504
505 --disable_ps_protocol
506
507 # nocopy is a parameter name
508 DELIMITER $$;
509 CREATE FUNCTION nocopy (nocopy INT) RETURN INT AS
510 BEGIN
511 RETURN nocopy;
512 END;
513 $$
514 DELIMITER ;$$
515 SELECT nocopy(1);
516 DROP FUNCTION nocopy;
517
518 # The first nocopy is a parameter name, the second nocopy - is the mode
519 DELIMITER $$;
520 CREATE FUNCTION nocopy (nocopy nocopy INT) RETURN INT AS
521 BEGIN
522 RETURN nocopy;
523 END;
524 $$
525 DELIMITER ;$$
526 SELECT nocopy(1);
527 DROP FUNCTION nocopy;
528
529 --enable_ps_protocol
530
531
532 # nocopy is a parameter name
533 DELIMITER $$;
534 CREATE PROCEDURE nocopy (nocopy INT) AS
535 BEGIN
536 SELECT nocopy;
537 END;
538 $$
539 DELIMITER ;$$
540 CALL nocopy(1);
541 DROP PROCEDURE nocopy;
542
543 # The first nocopy is a parameter name, the second nocopy - is the mode
544 DELIMITER $$;
545 CREATE PROCEDURE nocopy (nocopy nocopy INT) AS
546 BEGIN
547 SELECT nocopy;
548 END;
549 $$
550 DELIMITER ;$$
551 CALL nocopy(1);
552 DROP PROCEDURE nocopy;
553
554 DELIMITER $$;
555 DECLARE
556 nocopy INT := 1;
557 BEGIN
558 <<nocopy>>
559 WHILE 1
560 LOOP
561 SELECT nocopy;
562 LEAVE nocopy;
563 END LOOP;
564 END;
565 $$
566 DELIMITER ;$$
567
568
569 --echo #
570 --echo # End of 11.7 tests
571 --echo #