Debian Codegraph @

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

raw
1 SET sql_mode=ORACLE;
2
3 --echo #
4 --echo # MDEV-15620 Crash when using "SET @@NEW.a=expr" inside a trigger
5 --echo #
6
7 CREATE TABLE t1 (a INT);
8 --error ER_UNKNOWN_STRUCTURED_VARIABLE
9 CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @@NEW.a=0;
10 DROP TABLE t1;
11
12 --echo #
13 --echo # MDEV-15615 Unexpected syntax error instead of "Unknown system variable" inside an SP
14 --echo #
15
16 DELIMITER $$;
17 --error ER_UNKNOWN_SYSTEM_VARIABLE
18 DECLARE
19 a INT;
20 BEGIN
21 SET GLOBAL a=10;
22 END;
23 $$
24 DELIMITER ;$$
25
26
27 --echo #
28 --echo # MDEV-16202 Latest changes made erroneously some keywords reserved in sql_mode=ORACLE
29 --echo #
30
31
32 DELIMITER $$;
33 CREATE PROCEDURE p1(name VARCHAR(64), pattern TEXT) AS
34 query TEXT DEFAULT REPLACE(pattern, 'name', name);
35 BEGIN
36 SELECT query AS '';
37 EXECUTE IMMEDIATE query;
38 EXCEPTION
39 WHEN OTHERS THEN
40 BEGIN
41 SHOW ERRORS;
42 END;
43 END;
44 $$
45
46 CREATE PROCEDURE p2(name VARCHAR(64)) AS
47 BEGIN
48 CALL p1(name, 'DECLARE name INT; BEGIN name:=10; SELECT name; END');
49 EXECUTE IMMEDIATE REPLACE('CREATE TABLE t1 (name INT)', 'name', name);
50 CALL p1(name, 'SELECT name FROM t1');
51 CALL p1(name, 'SELECT name ''alias'' FROM t1');
52 CALL p1(name, 'SELECT name()');
53 CALL p1(name, 'SELECT name.name()');
54 CALL p1(name, 'SELECT name DATE FROM t1');
55 CALL p1(name, 'SELECT name HISTORY FROM t1');
56 CALL p1(name, 'SELECT name NEXT FROM t1');
57 CALL p1(name, 'SELECT name PERIOD FROM t1');
58 CALL p1(name, 'SELECT name PREVIOUS FROM t1');
59 CALL p1(name, 'SELECT name SYSTEM FROM t1');
60 CALL p1(name, 'SELECT name SYSTEM_TIME FROM t1');
61 CALL p1(name, 'SELECT name TIME FROM t1');
62 CALL p1(name, 'SELECT name TIMESTAMP FROM t1');
63 CALL p1(name, 'SELECT name TRANSACTION FROM t1');
64 CALL p1(name, 'SELECT name VALUE FROM t1');
65 CALL p1(name, 'SELECT name VERSIONING FROM t1');
66 CALL p1(name, 'SELECT name WITHOUT FROM t1');
67 DROP TABLE t1;
68 END;
69 $$
70 DELIMITER ;$$
71
72 --disable_column_names
73 CALL p2('date');
74 CALL p2('history');
75 CALL p2('next');
76 CALL p2('period');
77 CALL p2('previous');
78 CALL p2('system');
79 CALL p2('system_time');
80 CALL p2('time');
81 CALL p2('timestamp');
82 CALL p2('transaction');
83 CALL p2('value');
84 CALL p2('versioning');
85 CALL p2('without');
86 --enable_column_names
87
88 DROP PROCEDURE p2;
89 DROP PROCEDURE p1;
90
91
92 --echo #
93 --echo # MDEV-16244 sql_mode=ORACLE: Some keywords do not work in variable declarations
94 --echo #
95
96 SET sql_mode=ORACLE;
97 DELIMITER /;
98
99 DECLARE
100 do INT;
101 BEGIN
102 SELECT do INTO do FROM DUAL;
103 END;
104 /
105
106 DECLARE
107 handler INT;
108 BEGIN
109 SELECT handler INTO handler FROM DUAL;
110 END;
111 /
112
113 DECLARE
114 repair INT;
115 BEGIN
116 SELECT repair INTO repair FROM DUAL;
117 END;
118 /
119
120 DECLARE
121 shutdown INT;
122 BEGIN
123 SELECT shutdown INTO shutdown FROM DUAL;
124 END;
125 /
126
127 DECLARE
128 truncate INT;
129 BEGIN
130 SELECT truncate INTO truncate FROM DUAL;
131 END;
132 /
133
134 DECLARE
135 close INT;
136 BEGIN
137 SELECT close INTO close FROM DUAL;
138 END;
139 /
140
141 DECLARE
142 commit INT;
143 BEGIN
144 SELECT commit INTO commit FROM DUAL;
145 END;
146 /
147
148 DECLARE
149 open INT;
150 BEGIN
151 SELECT open INTO open FROM DUAL;
152 END;
153 /
154
155 DECLARE
156 rollback INT;
157 BEGIN
158 SELECT rollback INTO rollback FROM DUAL;
159 END;
160 /
161
162 DECLARE
163 savepoint INT;
164 BEGIN
165 SELECT savepoint INTO savepoint FROM DUAL;
166 END;
167 /
168
169 DECLARE
170 contains INT;
171 BEGIN
172 SELECT contains INTO contains FROM DUAL;
173 END;
174 /
175
176 DECLARE
177 language INT;
178 BEGIN
179 SELECT language INTO language FROM DUAL;
180 END;
181 /
182
183 DECLARE
184 no INT;
185 BEGIN
186 SELECT no INTO no FROM DUAL;
187 END;
188 /
189
190 DECLARE
191 charset INT;
192 BEGIN
193 SELECT charset INTO charset FROM DUAL;
194 END;
195 /
196 DECLARE
197 follows INT;
198 BEGIN
199 SELECT follows INTO follows FROM DUAL;
200 END;
201 /
202
203 DECLARE
204 precedes INT;
205 BEGIN
206 SELECT precedes INTO precedes FROM DUAL;
207 END;
208 /
209
210 DELIMITER ;/
211
212
213 --echo #
214 --echo # MDEV-16464 Oracle Comp.: Sql-Error on "SELECT name, comment FROM mysql.proc"
215 --echo #
216
217 SET sql_mode=ORACLE;
218 SELECT name, comment FROM mysql.proc WHERE db='test';
219
220 CREATE TABLE comment (comment INT);
221 SELECT comment FROM comment;
222 SELECT comment comment FROM comment comment;
223 SELECT comment AS comment FROM comment AS comment;
224 DROP TABLE comment;
225
226 DELIMITER /;
227 DECLARE
228 comment INT;
229 BEGIN
230 SELECT comment INTO comment FROM DUAL;
231 END;
232 /
233 DELIMITER ;/
234
235 DELIMITER /;
236 CREATE PROCEDURE comment COMMENT 'test' AS
237 BEGIN
238 SELECT 1;
239 END;
240 /
241 BEGIN
242 comment;
243 END;
244 /
245 DELIMITER ;/
246 CALL comment();
247 CALL comment;
248 DROP PROCEDURE comment;
249
250 enable_prepare_warnings;
251 DELIMITER /;
252 CREATE FUNCTION comment RETURN INT COMMENT 'test' AS
253 BEGIN
254 RETURN 1;
255 END;
256 /
257 DELIMITER ;/
258 SELECT test.comment() FROM DUAL;
259 disable_prepare_warnings;
260 DROP FUNCTION comment;
261
262
263 --echo #
264 --echo # MDEV-17660 sql_mode=ORACLE: Some keywords do not work as label names: history, system, versioning, without
265 --echo #
266
267 DELIMITER /;
268 BEGIN
269 <<date_format>>
270 NULL;
271 END;
272 /
273 DELIMITER ;/
274
275
276 DELIMITER /;
277 BEGIN
278 <<decode>>
279 NULL;
280 END;
281 /
282 DELIMITER ;/
283
284
285 DELIMITER /;
286 BEGIN
287 <<history>>
288 NULL;
289 END;
290 /
291 DELIMITER ;/
292
293
294 DELIMITER /;
295 BEGIN
296 <<system>>
297 NULL;
298 END;
299 /
300 DELIMITER ;/
301
302
303 DELIMITER /;
304 BEGIN
305 <<versioning>>
306 NULL;
307 END;
308 /
309 DELIMITER ;/
310
311
312 DELIMITER /;
313 BEGIN
314 <<without>>
315 NULL;
316 END;
317 /
318 DELIMITER ;/
319
320
321 --echo #
322 --echo # MDEV-17666 sql_mode=ORACLE: Keyword ELSEIF should not be reserved
323 --echo #
324
325 DELIMITER /;
326 DECLARE
327 ELSEIF INT;
328 BEGIN
329 ELSEIF:=1;
330 END;
331 /
332 DELIMITER ;/
333
334 DELIMITER /;
335 BEGIN
336 <<ELSEIF>>
337 NULL;
338 END;
339 /
340 DELIMITER ;/
341
342
343 --echo #
344 --echo # MDEV-17693 Shift/reduce conflicts for NAMES,ROLE,PASSWORD in the option_value_no_option_type grammar
345 --echo #
346
347 CREATE TABLE names (names INT);
348 SELECT names FROM names AS names;
349 DROP TABLE names;
350
351 CREATE TABLE password (password INT);
352 SELECT password FROM password AS password;
353 DROP TABLE password;
354
355 CREATE TABLE role (role INT);
356 SELECT role FROM role AS role;
357 DROP TABLE role;
358
359 DELIMITER $$;
360 DECLARE
361 names VARCHAR(32) DEFAULT '[names]';
362 password VARCHAR(32) DEFAULT '[password]';
363 role VARCHAR(32) DEFAULT '[role]';
364 BEGIN
365 <<names>>
366 SELECT names;
367 <<password>>
368 SELECT password;
369 <<role>>
370 SELECT role;
371 END;
372 $$
373 DELIMITER ;$$
374
375 DELIMITER $$;
376 --error ER_SP_BAD_VAR_SHADOW
377 DECLARE
378 names VARCHAR(32);
379 BEGIN
380 SET names='[names]';
381 END;
382 $$
383 DELIMITER ;$$
384
385 DELIMITER $$;
386 --error ER_SP_BAD_VAR_SHADOW
387 DECLARE
388 password VARCHAR(32);
389 BEGIN
390 SET password='[password]';
391 END;
392 $$
393 DELIMITER ;$$
394
395 DELIMITER $$;
396 DECLARE
397 role VARCHAR(32);
398 BEGIN
399 SET role='[role]';
400 END;
401 $$
402 DELIMITER ;$$
403
404 --error ER_UNKNOWN_SYSTEM_VARIABLE
405 SELECT @@GLOBAL.names;
406 --error ER_UNKNOWN_SYSTEM_VARIABLE
407 SELECT @@GLOBAL.password;
408 --error ER_UNKNOWN_SYSTEM_VARIABLE
409 SELECT @@GLOBAL.role;
410
411
412 --echo #
413 --echo # MDEV-22822 sql_mode="oracle" cannot declare without variable errors
414 --echo #
415 --echo # It's OK to have no declarations between DECLARE and BEGIN.
416 --echo #
417
418 DELIMITER //;
419 BEGIN
420 DECLARE
421 BEGIN
422 NULL;
423 END;
424 EXCEPTION
425 WHEN OTHERS THEN
426 NULL;
427 END;
428 //
429 DELIMITER ;//
430
431
432 DELIMITER //;
433 DECLARE
434 BEGIN
435 NULL;
436 EXCEPTION
437 WHEN OTHERS THEN
438 NULL;
439 END;
440 //
441 DELIMITER ;//
442
443
444 DELIMITER //;
445 BEGIN
446 <<lab>>
447 DECLARE
448 BEGIN
449 NULL;
450 END;
451 EXCEPTION
452 WHEN OTHERS THEN
453 NULL;
454 END;
455 //
456 DELIMITER ;//
457
458
459 --echo #
460 --echo # End of 10.3 tests
461 --echo #
462
463
464 --echo #
465 --echo # MDEV-21998: Server crashes in st_select_lex::add_table_to_list
466 --echo # upon mix of KILL and sequences
467 --echo #
468
469 --error ER_SUBQUERIES_NOT_SUPPORTED
470 KILL ( SELECT 1 ) + LASTVAL(s);
471 --error ER_SUBQUERIES_NOT_SUPPORTED
472 KILL LASTVAL(s);
473
474 --echo #
475 --echo # MDEV-23094: Multiple calls to a Stored Procedure from another
476 --echo # Stored Procedure crashes server
477 --echo #
478
479 create table t1 (id1 int primary key, data1 int);
480 create table t2 (id2 int primary key, data2 int);
481
482 delimiter //;
483 create procedure p1(id int,dt int) as
484 begin
485 if (exists(select * from t1 where id1 = id and data1 = dt) or
486 not exists (select * from t2 where id2 = id and data2 = dt))
487 then
488 select 1;
489 end if;
490 end //
491 delimiter ;//
492
493 call p1(1,2);
494 call p1(1,2);
495
496 drop procedure p1;
497
498 delimiter //;
499 create procedure p1(id int, dt int) as
500 begin
501 case (exists(select * from t1 where id1 = id and data1 = dt) or
502 not exists (select * from t2 where id2 = id and data2 = dt))
503 when 1 then
504 select 1;
505 else
506 select 0;
507 end case;
508 end //
509 delimiter ;//
510
511 call p1(1,2);
512 call p1(1,2);
513
514 drop procedure p1;
515
516 delimiter //;
517 create procedure p1(id int, dt int) as
518 begin
519 declare wcont int default 1;
520 begin
521 while (exists(select * from t1 where id1 = id and data1 = dt) or
522 not exists (select * from t2 where id2 = id and data2 = dt)) and wcont
523 loop
524 select 1;
525 set wcont=0;
526 end loop;
527 end;
528 end //
529 delimiter ;//
530
531 call p1(1,2);
532 call p1(1,2);
533
534 drop procedure p1;
535
536 delimiter //;
537 create procedure p1(id int, dt int) as
538 begin
539 declare count int default 1;
540 begin
541 repeat
542 select 1;
543 set count=count+1;
544 until (exists(select * from t1 where id1 = id and data1 = dt) or
545 not exists (select * from t2 where id2 = id and data2 = dt)) and
546 count < 3
547 end repeat;
548 end;
549 end //
550 delimiter ;//
551
552 call p1(1,2);
553 call p1(1,2);
554
555 drop procedure p1;
556
557 delimiter //;
558 create procedure p1(id int, dt int) as
559 begin
560 for i in 1..(exists(select * from t1 where id1 = id and data1 = dt) or
561 not exists (select * from t2 where id2 = id and data2 = dt))
562 loop
563 select 1;
564 end loop;
565 end //
566 delimiter ;//
567
568 call p1(1,2);
569 call p1(1,2);
570
571 drop procedure p1;
572
573 set sql_mode=ORACLE;
574 delimiter //;
575 create or replace procedure p1(id int, dt int) as
576 begin
577 while (1)
578 loop
579 exit when (exists(select * from t1 where id1 = id and data1 = dt) or
580 not exists (select * from t2 where id2 = id and data2 = dt));
581 end loop;
582 end;
583 //
584 delimiter ;//
585
586 call p1(1,2);
587 call p1(1,2);
588
589 drop procedure p1;
590
591 drop table t1,t2;
592
593 --echo # End of 10.4 tests
594
595 --echo #
596 --echo # Start of 10.5 tests
597 --echo #
598
599 --echo #
600 --echo # MDEV-20734 Allow reserved keywords as user defined type names
601 --echo #
602
603 --error ER_UNKNOWN_DATA_TYPE
604 CREATE TABLE t1 (a DUAL);
605 --error ER_UNKNOWN_DATA_TYPE
606 SELECT CAST(1 AS DUAL);
607
608
609 --echo #
610 --echo # MDEV-20735 Allow non-reserved keywords as user defined type names
611 --echo #
612
613 --error ER_UNKNOWN_DATA_TYPE
614 CREATE TABLE t1 (a ASCII);
615 --error ER_UNKNOWN_DATA_TYPE
616 SELECT CAST(1 AS ASCII);
617
618 --error ER_UNKNOWN_DATA_TYPE
619 CREATE TABLE t1 (a LANGUAGE);
620 --error ER_UNKNOWN_DATA_TYPE
621 SELECT CAST(1 AS LANGUAGE);
622
623 --error ER_UNKNOWN_DATA_TYPE
624 CREATE TABLE t1 (a CLOSE);
625 --error ER_UNKNOWN_DATA_TYPE
626 SELECT CAST(1 AS CLOSE);
627
628 --error ER_UNKNOWN_DATA_TYPE
629 CREATE TABLE t1 (a NAMES);
630 --error ER_UNKNOWN_DATA_TYPE
631 SELECT CAST(1 AS NAMES);
632
633 --error ER_UNKNOWN_DATA_TYPE
634 CREATE TABLE t1 (a END);
635 --error ER_UNKNOWN_DATA_TYPE
636 SELECT CAST(1 AS END);
637
638 --error ER_UNKNOWN_DATA_TYPE
639 CREATE TABLE t1 (a GLOBAL);
640 --error ER_UNKNOWN_DATA_TYPE
641 SELECT CAST(1 AS GLOBAL);
642
643 --error ER_UNKNOWN_DATA_TYPE
644 CREATE TABLE t1 (a ACTION);
645 --error ER_UNKNOWN_DATA_TYPE
646 SELECT CAST(1 AS ACTION);
647
648 --error ER_UNKNOWN_DATA_TYPE
649 CREATE TABLE t1 (a BEGIN);
650 --error ER_UNKNOWN_DATA_TYPE
651 SELECT CAST(1 AS BEGIN);
652
653 --echo #
654 --echo # End of 10.5 tests
655 --echo #
656
657
658 --echo #
659 --echo # Start of 10.6 tests
660 --echo #
661
662 --echo #
663 --echo # MDEV-19682 sql_mode="oracle" does not support sysdate
664 --echo #
665
666 # SYSDATE is not deterministic. Let's use LIKE and equality.
667 # The main point here is only to check that SYSDATE
668 # gets parsed without parentheses. The actial value is not important.
669 SELECT sysdate LIKE '____-__-__ __:__:__';
670 SELECT sysdate = sysdate();
671 SELECT sysdate = sysdate(0);
672
673 --error ER_PARSE_ERROR
674 CREATE DATABASE sysdate;
675
676 --error ER_PARSE_ERROR
677 CREATE TABLE sysdate (a INT);
678
679 --error ER_PARSE_ERROR
680 CREATE TABLE t1 (sysdate INT);
681
682 --error ER_PARSE_ERROR
683 CREATE TABLE t1 (a sysdate);
684
685 DELIMITER $$;
686 --error ER_PARSE_ERROR
687 CREATE FUNCTION sysdate RETURN INT AS
688 BEGIN
689 RETURN 1;
690 END;
691 $$
692 DELIMITER ;$$
693
694 DELIMITER $$;
695 --error ER_PARSE_ERROR
696 CREATE FUNCTION sysdate() RETURN INT AS
697 BEGIN
698 RETURN 1;
699 END;
700 $$
701 DELIMITER ;$$
702
703
704 DELIMITER $$;
705 --error ER_PARSE_ERROR
706 DECLARE
707 sysdate INT := 10;
708 BEGIN
709 NULL;
710 END;
711 $$
712 DELIMITER ;$$
713
714
715 DELIMITER $$;
716 --error ER_PARSE_ERROR
717 BEGIN
718 <<sysdate>>
719 NULL;
720 END;
721 $$
722 DELIMITER ;$$
723
724
725 --echo #
726 --echo # End of 10.6 tests
727 --echo #