Debian Codegraph @

mysql-test/suite/compat/oracle/r/parser.result

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