summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/compat/oracle/r/sp.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/compat/oracle/r/sp.result')
-rw-r--r--mysql-test/suite/compat/oracle/r/sp.result2577
1 files changed, 2577 insertions, 0 deletions
diff --git a/mysql-test/suite/compat/oracle/r/sp.result b/mysql-test/suite/compat/oracle/r/sp.result
new file mode 100644
index 00000000..409ea3b8
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/sp.result
@@ -0,0 +1,2577 @@
+SET sql_mode=ORACLE;
+# Testing routines with no parameters
+CREATE FUNCTION f1 RETURN INT
+AS
+BEGIN
+RETURN 10;
+END;
+/
+SHOW CREATE FUNCTION f1;
+Function f1
+sql_mode PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT
+Create Function CREATE DEFINER="root"@"localhost" FUNCTION "f1"() RETURN int(11)
+AS
+BEGIN
+RETURN 10;
+END
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SELECT f1();
+f1()
+10
+DROP FUNCTION f1;
+CREATE PROCEDURE p1
+AS
+BEGIN
+SET @a=10;
+END;
+/
+SHOW CREATE PROCEDURE p1;
+Procedure p1
+sql_mode PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT
+Create Procedure CREATE DEFINER="root"@"localhost" PROCEDURE "p1"()
+AS
+BEGIN
+SET @a=10;
+END
+character_set_client latin1
+collation_connection latin1_swedish_ci
+Database Collation latin1_swedish_ci
+SET @a=0;
+CALL p1();
+SELECT @a;
+@a
+10
+DROP PROCEDURE p1;
+# Testing ":=" to set the default value of a variable
+CREATE FUNCTION f1 () RETURN NUMBER(10) AS
+a NUMBER(10) := 10;
+BEGIN
+DECLARE
+b NUMBER(10) DEFAULT 3;
+BEGIN
+RETURN a+b;
+END;
+END;
+/
+SELECT f1();
+f1()
+13
+DROP FUNCTION f1;
+# Testing labels
+CREATE FUNCTION f1 (a INT) RETURN CLOB AS
+BEGIN
+<<label1>>
+BEGIN
+IF a = 1 THEN
+LEAVE label1;
+END IF;
+RETURN 'IS NOT 1';
+END label1;
+RETURN 'IS 1';
+END;
+/
+SELECT f1(1);
+f1(1)
+IS 1
+SELECT f1(2);
+f1(2)
+IS NOT 1
+DROP FUNCTION f1;
+CREATE FUNCTION f1 (a INT) RETURN INT IS
+BEGIN
+<<label1>>
+LOOP
+IF a = 2 THEN
+LEAVE label1;
+END IF;
+SET a= a-1;
+END LOOP;
+RETURN a;
+END;
+/
+SELECT f1(4);
+f1(4)
+2
+DROP FUNCTION f1;
+CREATE FUNCTION f1 (a INT) RETURN INT AS
+BEGIN
+<<label1>>
+WHILE a>0 LOOP
+IF a = 2 THEN
+LEAVE label1;
+END IF;
+SET a= a-1;
+END LOOP label1;
+RETURN a;
+END;
+/
+SELECT f1(4);
+f1(4)
+2
+DROP FUNCTION f1;
+CREATE FUNCTION f1 (a INT) RETURN INT AS
+BEGIN
+<<label1>>
+REPEAT
+IF a = 2 THEN
+LEAVE label1;
+END IF;
+SET a= a-1;
+UNTIL a=0 END REPEAT;
+RETURN a;
+END;
+/
+SELECT f1(4);
+f1(4)
+2
+DROP FUNCTION f1;
+# Testing IN/OUT/INOUT
+CREATE PROCEDURE p1 (p1 IN VARCHAR2(10), p2 OUT VARCHAR2(10)) AS
+BEGIN
+SET p1='p1new';
+SET p2='p2new';
+END;
+/
+SET @p1='p1', @p2='p2';
+CALL p1(@p1, @p2);
+SELECT @p1, @p2;
+@p1 @p2
+p1 p2new
+DROP PROCEDURE p1;
+# Testing Oracle-style assigment
+CREATE PROCEDURE p1 (p1 OUT VARCHAR2(10)) AS
+BEGIN
+p1:= 'p1new';
+END;
+/
+SET @p1='p1';
+CALL p1(@p1);
+SELECT @p1;
+@p1
+p1new
+DROP PROCEDURE p1;
+# Testing that NULL is a valid statement
+CREATE PROCEDURE p1(a INT) AS
+BEGIN
+NULL;
+END;
+/
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1(a INT) AS
+a INT:=10;
+BEGIN
+IF a=10 THEN NULL; ELSE NULL; END IF;
+END;
+/
+DROP PROCEDURE p1;
+# Keywords that are OK for table names, but not for SP variables
+CREATE TABLE function (function int);
+INSERT INTO function SET function=10;
+SELECT function.function FROM function;
+function
+10
+DROP TABLE function;
+# Testing that (some) keyword_sp are allowed in Oracle-style assignments
+CREATE PROCEDURE p1 (action OUT INT) AS BEGIN action:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (clob OUT INT) AS BEGIN clob:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (enum OUT INT) AS BEGIN enum:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (via OUT INT) AS BEGIN via:=10; END;/
+DROP PROCEDURE p1/
+# Testing keyword_directly_assignable
+CREATE PROCEDURE p1 (ascii OUT INT) AS BEGIN ascii:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (backup OUT INT) AS BEGIN backup:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (binlog OUT INT) AS BEGIN binlog:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (byte OUT INT) AS BEGIN byte:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (cache OUT INT) AS BEGIN cache:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (checksum OUT INT) AS BEGIN checksum:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (checkpoint OUT INT) AS BEGIN checkpoint:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (column_add OUT INT) AS BEGIN column_add:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (column_check OUT INT) AS BEGIN column_check:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (column_create OUT INT) AS BEGIN column_create:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (column_delete OUT INT) AS BEGIN column_delete:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (column_get OUT INT) AS BEGIN column_get:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (deallocate OUT INT) AS BEGIN deallocate:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (examined OUT INT) AS BEGIN examined:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (execute OUT INT) AS BEGIN execute:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (flush OUT INT) AS BEGIN flush:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (format OUT INT) AS BEGIN format:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (get OUT INT) AS BEGIN get:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (help OUT INT) AS BEGIN help:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (host OUT INT) AS BEGIN host:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (install OUT INT) AS BEGIN install:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (option OUT INT) AS BEGIN option:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (options OUT INT) AS BEGIN options:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (owner OUT INT) AS BEGIN owner:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (parser OUT INT) AS BEGIN parser:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (port OUT INT) AS BEGIN port:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (prepare OUT INT) AS BEGIN prepare:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (remove OUT INT) AS BEGIN remove:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (reset OUT INT) AS BEGIN reset:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (restore OUT INT) AS BEGIN restore:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (security OUT INT) AS BEGIN security:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (server OUT INT) AS BEGIN server:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (signed OUT INT) AS BEGIN signed:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (socket OUT INT) AS BEGIN socket:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (slave OUT INT) AS BEGIN slave:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (slaves OUT INT) AS BEGIN slaves:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (soname OUT INT) AS BEGIN soname:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (start OUT INT) AS BEGIN start:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (stop OUT INT) AS BEGIN stop:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (stored OUT INT) AS BEGIN stored:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (unicode OUT INT) AS BEGIN unicode:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (uninstall OUT INT) AS BEGIN uninstall:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (upgrade OUT INT) AS BEGIN upgrade:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (wrapper OUT INT) AS BEGIN wrapper:=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (xa OUT INT) AS BEGIN xa:=10; END;/
+DROP PROCEDURE p1/
+# Testing that keyword_directly_not_assignable does not work in :=
+CREATE PROCEDURE p1 (commit OUT INT) AS BEGIN commit:=10; END;/
+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 ':=10; END' at line 1
+CREATE PROCEDURE p1 (rollback OUT INT) AS BEGIN rollback:=10; END;/
+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 ':=10; END' at line 1
+CREATE PROCEDURE p1 (shutdown OUT INT) AS BEGIN shutdown:=10; END;/
+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 ':=10; END' at line 1
+CREATE PROCEDURE p1 (exception OUT INT) AS BEGIN exception:=10; END;/
+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 ':=10; END' at line 1
+# Testing that keyword_directly_not_assignable works in SET statements.
+CREATE PROCEDURE p1 (contains OUT INT) AS BEGIN SET contains=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (language OUT INT) AS BEGIN SET language=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (no OUT INT) AS BEGIN SET no=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (charset OUT INT) AS BEGIN SET charset=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (do OUT INT) AS BEGIN SET do=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (repair OUT INT) AS BEGIN SET repair=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (handler OUT INT) AS BEGIN SET handler=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (open OUT INT) AS BEGIN SET open=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (close OUT INT) AS BEGIN SET close=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (savepoint OUT INT) AS BEGIN SET savepoint=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (truncate OUT INT) AS BEGIN SET truncate=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (begin OUT INT) AS BEGIN SET begin=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (end OUT INT) AS BEGIN SET end=10; END;/
+DROP PROCEDURE p1/
+CREATE PROCEDURE p1 (exception OUT INT) AS BEGIN SET exception=10; END;/
+DROP PROCEDURE p1/
+# Testing that keyword_directly_not_assignable works in table/column names
+CREATE TABLE contains (contains INT);
+DROP TABLE contains;
+CREATE TABLE language (language INT);
+DROP TABLE language;
+CREATE TABLE no (no INT);
+DROP TABLE no;
+CREATE TABLE charset (charset INT);
+DROP TABLE charset;
+CREATE TABLE do (do INT);
+DROP TABLE do;
+CREATE TABLE repair (repair INT);
+DROP TABLE repair;
+CREATE TABLE handler (handler INT);
+DROP TABLE handler;
+CREATE TABLE open (open INT);
+DROP TABLE open;
+CREATE TABLE close (close INT);
+DROP TABLE close;
+CREATE TABLE savepoint (savepoint INT);
+DROP TABLE savepoint;
+CREATE TABLE truncate (truncate INT);
+DROP TABLE truncate;
+CREATE TABLE begin (begin INT);
+DROP TABLE begin;
+CREATE TABLE end (end INT);
+DROP TABLE end;
+CREATE TABLE exception (exception INT);
+DROP TABLE exception;
+# Testing ELSIF
+CREATE FUNCTION f1(a INT) RETURN CLOB
+AS
+BEGIN
+IF a=1 THEN RETURN 'a is 1';
+ELSIF a=2 THEN RETURN 'a is 2';
+ELSE RETURN 'a is unknown';
+END IF;
+END;
+/
+SELECT f1(2) FROM DUAL;
+f1(2)
+a is 2
+DROP FUNCTION f1;
+# Testing top-level declarations
+CREATE PROCEDURE p1 (p1 OUT VARCHAR2(10))
+AS
+p2 VARCHAR(10);
+BEGIN
+p2:='p1new';
+p1:=p2;
+END;
+/
+SET @p1='p1';
+CALL p1(@p1);
+SELECT @p1;
+@p1
+p1new
+DROP PROCEDURE p1;
+CREATE FUNCTION f1 (p1 VARCHAR2(10)) RETURN VARCHAR(20)
+AS
+p2 VARCHAR(10);
+BEGIN
+p2:='new';
+RETURN CONCAT(p1, p2);
+END;
+/
+SET @p1='p1';
+SELECT f1(@p1);
+f1(@p1)
+p1new
+DROP FUNCTION f1;
+# Testing non-top declarations
+CREATE PROCEDURE p1 (p1 OUT VARCHAR2(10))
+AS
+BEGIN
+DECLARE
+p2 VARCHAR(10);
+BEGIN
+p2:='p1new';
+p1:=p2;
+END;
+DECLARE
+t1 VARCHAR(10);
+t2 VARCHAR(10);
+BEGIN
+END;
+END;
+/
+SET @p1='p1';
+CALL p1(@p1);
+SELECT @p1;
+@p1
+p1new
+DROP PROCEDURE p1;
+CREATE FUNCTION f1 (p1 VARCHAR2(10)) RETURN VARCHAR(20)
+AS
+BEGIN
+DECLARE
+p2 VARCHAR(10);
+BEGIN
+p2:='new';
+RETURN CONCAT(p1, p2);
+END;
+DECLARE
+t1 VARCHAR(10);
+t2 VARCHAR(10);
+BEGIN
+END;
+END;
+/
+SET @p1='p1';
+SELECT f1(@p1);
+f1(@p1)
+p1new
+DROP FUNCTION f1;
+# Testing exceptions
+CREATE TABLE t1 (c1 INT);
+CREATE PROCEDURE sp1 (p1 IN VARCHAR2(20), p2 OUT VARCHAR2(30))
+IS
+v1 INT;
+BEGIN
+SELECT c1 INTO v1 FROM t1;
+p2 := p1;
+EXCEPTION
+WHEN NOT FOUND THEN
+BEGIN
+p2 := 'def';
+END;
+END;
+/
+CALL sp1('abc', @a);
+SELECT @a;
+@a
+def
+DROP PROCEDURE sp1;
+DROP TABLE t1;
+CREATE PROCEDURE sp1 (v IN OUT INT, error IN INT)
+IS
+BEGIN
+SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=error, MESSAGE_TEXT='User defined error!';
+v:= 223;
+EXCEPTION
+WHEN 30001 THEN
+BEGIN
+v:= 113;
+END;
+END;
+/
+SET @v=10;
+CALL sp1(@v, 30001);
+CALL sp1(@v, 30002);
+ERROR 45000: User defined error!
+SELECT @v;
+@v
+113
+DROP PROCEDURE sp1;
+CREATE PROCEDURE sp1 (v IN OUT INT, error IN INT)
+IS
+BEGIN
+BEGIN
+BEGIN
+SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO=error, MESSAGE_TEXT='User defined error!';
+v:= 223;
+EXCEPTION
+WHEN 30001 THEN
+BEGIN
+v:= 113;
+END;
+END;
+END;
+END;
+/
+SET @v=10;
+CALL sp1(@v, 30001);
+SELECT @v;
+@v
+113
+SET @v=10;
+CALL sp1(@v, 30002);
+ERROR 45000: User defined error!
+SELECT @v;
+@v
+10
+DROP PROCEDURE sp1;
+#
+# Testing EXIT statement
+#
+CREATE FUNCTION f1 RETURN INT
+IS
+i INT := 0;
+BEGIN
+EXIT;
+END;
+/
+ERROR 42000: EXIT with no matching label:
+CREATE FUNCTION f1 RETURN INT
+IS
+i INT := 0;
+BEGIN
+<<lable1>>
+BEGIN
+<<label2>>
+LOOP
+EXIT label1;
+END LOOP;
+END;
+END;
+/
+ERROR 42000: EXIT with no matching label: label1
+CREATE FUNCTION f1 RETURN INT
+IS
+i INT := 0;
+BEGIN
+LOOP
+LOOP
+i:= i + 1;
+IF i >= 5 THEN
+EXIT;
+END IF;
+END LOOP;
+i:= i + 100;
+EXIT;
+END LOOP;
+RETURN i;
+END;
+/
+SELECT f1() FROM DUAL;
+f1()
+105
+DROP FUNCTION f1;
+CREATE FUNCTION f1 RETURN INT
+IS
+i INT := 0;
+BEGIN
+<<label1>>
+LOOP
+<<label2>>
+LOOP
+i:= i + 1;
+IF i >= 5 THEN
+EXIT label2;
+END IF;
+END LOOP;
+i:= i + 100;
+EXIT;
+END LOOP;
+RETURN i;
+END;
+/
+SELECT f1() FROM DUAL;
+f1()
+105
+DROP FUNCTION f1;
+CREATE FUNCTION f1 RETURN INT
+IS
+i INT := 0;
+BEGIN
+<<label1>>
+LOOP
+<<label2>>
+LOOP
+i:= i + 1;
+IF i >= 5 THEN
+EXIT label1;
+END IF;
+END LOOP;
+i:= i + 100;
+EXIT;
+END LOOP;
+RETURN i;
+END;
+/
+SELECT f1() FROM DUAL;
+f1()
+5
+DROP FUNCTION f1;
+CREATE FUNCTION f1 RETURN INT
+IS
+i INT := 0;
+BEGIN
+LOOP
+i:= i + 1;
+EXIT WHEN i >=5;
+END LOOP;
+RETURN i;
+END;
+/
+SELECT f1() FROM DUAL;
+f1()
+5
+DROP FUNCTION f1;
+CREATE FUNCTION f1 RETURN INT
+IS
+i INT := 0;
+BEGIN
+<<label1>>
+LOOP
+<<label2>>
+LOOP
+i:= i + 1;
+EXIT label2 WHEN i >= 5;
+END LOOP;
+i:= i + 100;
+EXIT;
+END LOOP;
+RETURN i;
+END;
+/
+SELECT f1() FROM DUAL;
+f1()
+105
+DROP FUNCTION f1;
+CREATE FUNCTION f1 RETURN INT
+IS
+i INT := 0;
+BEGIN
+<<label1>>
+LOOP
+<<label2>>
+LOOP
+i:= i + 1;
+EXIT label1 WHEN i >= 5;
+END LOOP;
+i:= i + 100;
+EXIT;
+END LOOP;
+RETURN i;
+END;
+/
+SELECT f1() FROM DUAL;
+f1()
+5
+DROP FUNCTION f1;
+# Testing CURSOR declaration
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+CREATE FUNCTION f1 RETURN INT
+AS
+v_a INT:=10;
+CURSOR c IS SELECT a FROM t1;
+BEGIN
+OPEN c;
+FETCH c INTO v_a;
+CLOSE c;
+RETURN v_a;
+EXCEPTION
+WHEN OTHERS THEN RETURN -1;
+END;
+/
+SELECT f1() FROM DUAL;
+f1()
+1
+DROP FUNCTION f1;
+DROP TABLE t1;
+# Testing RETURN in procedures
+CREATE PROCEDURE p1 (a IN OUT INT)
+AS
+BEGIN
+RETURN 10;
+END;
+/
+ERROR 42000: RETURN is only allowed in a FUNCTION
+CREATE FUNCTION f1 (a INT) RETURN INT
+AS
+BEGIN
+RETURN;
+END;
+/
+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 ';
+END' at line 4
+CREATE PROCEDURE p1 (a IN OUT INT)
+AS
+BEGIN
+IF a < 10 THEN
+BEGIN
+a:= a - 1;
+RETURN;
+END;
+END IF;
+a:= a + 1;
+EXCEPTION
+WHEN OTHERS THEN RETURN;
+END;
+/
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+@v
+11
+SET @v=9;
+CALL p1(@v);
+SELECT @v;
+@v
+8
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1 (a IN OUT INT)
+AS
+BEGIN
+DROP TABLE t1_non_existent;
+EXCEPTION
+WHEN OTHERS THEN
+BEGIN
+a:= 100;
+RETURN;
+END;
+END;
+/
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+@v
+100
+DROP PROCEDURE p1;
+# Testing WHILE loop
+CREATE PROCEDURE p1 (a IN OUT INT)
+AS
+i INT:= 1;
+j INT:= 3;
+BEGIN
+WHILE i<=j
+LOOP
+a:= a + i;
+i:= i + 1;
+END LOOP;
+END;
+/
+SET @v=0;
+CALL p1(@v);
+SELECT @v;
+@v
+6
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1 (a IN OUT INT)
+AS
+i INT:= 1;
+j INT:= 3;
+BEGIN
+<<label>>
+WHILE i<=j
+LOOP
+a:= a + i;
+i:= i + 1;
+END LOOP label;
+END;
+/
+SET @v=0;
+CALL p1(@v);
+SELECT @v;
+@v
+6
+DROP PROCEDURE p1;
+# Testing the FOR loop statement
+CREATE TABLE t1 (a INT);
+FOR i IN 1..3
+LOOP
+INSERT INTO t1 VALUES (i);
+END LOOP;
+/
+SELECT * FROM t1;
+a
+1
+2
+3
+DROP TABLE t1;
+CREATE FUNCTION f1 (lower_bound INT, upper_bound INT, lim INT) RETURN INT
+AS
+total INT := 0;
+BEGIN
+FOR i IN lower_bound . . upper_bound
+LOOP
+NULL
+END LOOP;
+RETURN total;
+END;
+/
+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 '. upper_bound
+LOOP
+NULL
+END LOOP;
+RETURN total;
+END' at line 5
+CREATE FUNCTION f1 (lower_bound INT, upper_bound INT, lim INT) RETURN INT
+AS
+total INT := 0;
+BEGIN
+FOR i IN lower_bound .. upper_bound
+LOOP
+total:= total + i;
+IF i = lim THEN
+EXIT;
+END IF;
+-- Bounds are calculated only once.
+-- The below assignments have no effect on the loop condition
+lower_bound:= 900;
+upper_bound:= 1000;
+END LOOP;
+RETURN total;
+END;
+/
+SELECT f1(1, 3, 100) FROM DUAL;
+f1(1, 3, 100)
+6
+SELECT f1(1, 3, 2) FROM DUAL;
+f1(1, 3, 2)
+3
+DROP FUNCTION f1;
+CREATE FUNCTION f1 RETURN INT
+AS
+total INT := 0;
+BEGIN
+FOR i IN 1 .. 5
+LOOP
+total:= total + 1000;
+FOR j IN 1 .. 5
+LOOP
+total:= total + 1;
+IF j = 3 THEN
+EXIT; -- End the internal loop
+END IF;
+END LOOP;
+END LOOP;
+RETURN total;
+END;
+/
+SELECT f1() FROM DUAL;
+f1()
+5015
+DROP FUNCTION f1;
+CREATE FUNCTION f1 (a INT, b INT) RETURN INT
+AS
+total INT := 0;
+BEGIN
+FOR i IN REVERSE 1..a
+LOOP
+total:= total + i;
+IF i = b THEN
+EXIT;
+END IF;
+END LOOP;
+RETURN total;
+END
+/
+SELECT f1(3, 100) FROM DUAL;
+f1(3, 100)
+6
+SELECT f1(3, 2) FROM DUAL;
+f1(3, 2)
+5
+DROP FUNCTION f1;
+# Testing labeled FOR LOOP statement
+CREATE FUNCTION f1 (a INT, limita INT, b INT, limitb INT) RETURN INT
+AS
+total INT := 0;
+BEGIN
+<<la>>
+FOR ia IN 1 .. a
+LOOP
+total:= total + 1000;
+<<lb>>
+FOR ib IN 1 .. b
+LOOP
+total:= total + 1;
+EXIT lb WHEN ib = limitb;
+EXIT la WHEN ia = limita;
+END LOOP lb;
+END LOOP la;
+RETURN total;
+END;
+/
+SELECT f1(1, 1, 1, 1) FROM DUAL;
+f1(1, 1, 1, 1)
+1001
+SELECT f1(1, 2, 1, 2) FROM DUAL;
+f1(1, 2, 1, 2)
+1001
+SELECT f1(2, 1, 2, 1) FROM DUAL;
+f1(2, 1, 2, 1)
+2002
+SELECT f1(2, 1, 2, 2) FROM DUAL;
+f1(2, 1, 2, 2)
+1001
+SELECT f1(2, 2, 2, 2) FROM DUAL;
+f1(2, 2, 2, 2)
+2003
+SELECT f1(2, 3, 2, 3) FROM DUAL;
+f1(2, 3, 2, 3)
+2004
+DROP FUNCTION f1;
+# Testing labeled ITERATE in a labeled FOR LOOP statement
+CREATE FUNCTION f1 (a INT, b INT, blim INT) RETURN INT
+AS
+total INT := 0;
+BEGIN
+<<la>>
+FOR ia IN 1 .. a
+LOOP
+total:= total + 1000;
+DECLARE
+ib INT:= 1;
+BEGIN
+WHILE ib <= b
+LOOP
+IF ib > blim THEN
+ITERATE la;
+END IF;
+ib:= ib + 1;
+total:= total + 1;
+END LOOP;
+END;
+END LOOP la;
+RETURN total;
+END;
+/
+SELECT f1(3,3,0), f1(3,3,1), f1(3,3,2), f1(3,3,3), f1(3,3,4) FROM DUAL;
+f1(3,3,0) f1(3,3,1) f1(3,3,2) f1(3,3,3) f1(3,3,4)
+3000 3003 3006 3009 3009
+DROP FUNCTION f1;
+# Testing CONTINUE statement
+CREATE FUNCTION f1(a INT) RETURN INT
+AS
+total INT:= 0;
+BEGIN
+FOR i IN 1 .. a
+LOOP
+IF i=5 THEN
+CONTINUE;
+END IF;
+total:= total + 1;
+END LOOP;
+RETURN total;
+END;
+/
+SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
+f1(3) f1(4) f1(5) f1(6)
+3 4 4 5
+DROP FUNCTION f1;
+CREATE FUNCTION f1(a INT) RETURN INT
+AS
+total INT:= 0;
+BEGIN
+<<lj>>
+FOR j IN 1 .. 2
+LOOP
+FOR i IN 1 .. a
+LOOP
+IF i=5 THEN
+CONTINUE lj;
+END IF;
+total:= total + 1;
+END LOOP;
+END LOOP;
+RETURN total;
+END;
+/
+SELECT f1(3), f1(4), f1(5) FROM DUAL;
+f1(3) f1(4) f1(5)
+6 8 8
+DROP FUNCTION f1;
+CREATE FUNCTION f1(a INT) RETURN INT
+AS
+total INT:= 0;
+BEGIN
+<<lj>>
+FOR j IN 1 .. 2
+LOOP
+FOR i IN 1 .. a
+LOOP
+CONTINUE lj WHEN i=5;
+total:= total + 1;
+END LOOP;
+END LOOP;
+RETURN total;
+END;
+/
+SELECT f1(3), f1(4), f1(5) FROM DUAL;
+f1(3) f1(4) f1(5)
+6 8 8
+DROP FUNCTION f1;
+CREATE FUNCTION f1(a INT) RETURN INT
+AS
+total INT:= 0;
+i INT:= 1;
+BEGIN
+WHILE i <= a
+LOOP
+i:= i + 1;
+IF i=6 THEN
+CONTINUE;
+END IF;
+total:= total + 1;
+END LOOP;
+RETURN total;
+END;
+/
+SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
+f1(3) f1(4) f1(5) f1(6)
+3 4 4 5
+DROP FUNCTION f1;
+#
+# Testing behaviour of unknown identifiers in EXIT and CONTINUE statements
+#
+CREATE PROCEDURE p1
+AS
+BEGIN
+LOOP
+EXIT WHEN unknown_ident IS NULL;
+END LOOP;
+END$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1
+AS
+BEGIN
+<<label>>
+LOOP
+EXIT label WHEN unknown_ident IS NULL;
+END LOOP;
+END$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1
+AS
+BEGIN
+LOOP
+CONTINUE WHEN unknown_ident IS NULL;
+END LOOP;
+END$$
+ERROR 42000: Undeclared variable: unknown_ident
+CREATE PROCEDURE p1
+AS
+BEGIN
+<<label>>
+LOOP
+CONTINUE label WHEN unknown_ident IS NULL;
+END LOOP;
+END$$
+ERROR 42000: Undeclared variable: unknown_ident
+#
+# MDEV-10583 sql_mode=ORACLE: SQL%ROWCOUNT
+#
+EXPLAIN EXTENDED SELECT sql%rowcount;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select SQL%ROWCOUNT AS "sql%rowcount"
+CREATE TABLE t1 AS SELECT SQL%ROWCOUNT;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE "t1" (
+ "SQL%ROWCOUNT" bigint(21) NOT NULL
+)
+DROP TABLE t1;
+#
+# UPDATE
+#
+CREATE TABLE t1 (a INT);
+CREATE PROCEDURE p1
+AS
+BEGIN
+UPDATE t1 SET a=30;
+SELECT SQL%ROWCOUNT;
+END;
+$$
+CALL p1();
+SQL%ROWCOUNT
+0
+DROP PROCEDURE p1;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (10);
+INSERT INTO t1 VALUES (20);
+CREATE PROCEDURE p1
+AS
+BEGIN
+UPDATE t1 SET a=30;
+SELECT SQL%ROWCOUNT;
+END;
+$$
+CALL p1();
+SQL%ROWCOUNT
+2
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# DELETE
+#
+CREATE TABLE t1 (a INT);
+CREATE PROCEDURE p1
+AS
+BEGIN
+DELETE FROM t1;
+SELECT SQL%ROWCOUNT;
+END;
+$$
+CALL p1();
+SQL%ROWCOUNT
+0
+DROP PROCEDURE p1;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (10);
+INSERT INTO t1 VALUES (20);
+CREATE PROCEDURE p1
+AS
+BEGIN
+DELETE FROM t1;
+SELECT SQL%ROWCOUNT;
+END;
+$$
+CALL p1();
+SQL%ROWCOUNT
+2
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# SELECT ... INTO var FROM ... - one row found
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (10);
+INSERT INTO t1 VALUES (20);
+CREATE PROCEDURE p1
+AS
+va INT;
+BEGIN
+SELECT a INTO va FROM t1 LIMIT 1;
+SELECT SQL%ROWCOUNT;
+END;
+$$
+CALL p1();
+SQL%ROWCOUNT
+1
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# SELECT ... INTO var FROM ... - no rows found
+#
+CREATE TABLE t1 (a INT);
+CREATE PROCEDURE p1
+AS
+va INT;
+BEGIN
+SELECT a INTO va FROM t1;
+SELECT SQL%ROWCOUNT;
+END;
+$$
+CALL p1();
+SQL%ROWCOUNT
+0
+Warnings:
+Warning 1329 No data - zero rows fetched, selected, or processed
+DROP PROCEDURE p1;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+CREATE PROCEDURE p1
+AS
+va INT;
+BEGIN
+SELECT a INTO va FROM t1;
+SELECT SQL%ROWCOUNT;
+EXCEPTION
+WHEN NO_DATA_FOUND THEN SELECT SQL%ROWCOUNT||' (EXCEPTION)';
+END;
+$$
+CALL p1();
+SQL%ROWCOUNT||' (EXCEPTION)'
+0 (EXCEPTION)
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# SELECT ... INTO var FROM ... - multiple rows found
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (10);
+INSERT INTO t1 VALUES (20);
+CREATE PROCEDURE p1
+AS
+va INT:=1;
+BEGIN
+SELECT a INTO va FROM t1;
+SELECT SQL%ROWCOUNT;
+EXCEPTION
+WHEN TOO_MANY_ROWS THEN SELECT SQL%ROWCOUNT||' (EXCEPTION) va='||va;
+END;
+$$
+CALL p1();
+SQL%ROWCOUNT||' (EXCEPTION) va='||va
+1 (EXCEPTION) va=10
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# INSERT INTO t2 SELECT ...
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (10);
+INSERT INTO t1 VALUES (20);
+CREATE PROCEDURE p1
+AS
+BEGIN
+INSERT INTO t2 SELECT * FROM t1;
+SELECT SQL%ROWCOUNT;
+END;
+$$
+CALL p1();
+SQL%ROWCOUNT
+2
+DROP PROCEDURE p1;
+DROP TABLE t1, t2;
+#
+# End of MDEV-10583 sql_mode=ORACLE: SQL%ROWCOUNT
+#
+#
+# MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations
+#
+#
+# Missing table
+#
+CREATE PROCEDURE p1
+AS
+a t1.a%TYPE;
+BEGIN
+NULL;
+END;
+$$
+CALL p1();
+ERROR 42S02: Table 'test.t1' doesn't exist
+DROP PROCEDURE p1;
+#
+# Missing column
+#
+CREATE TABLE t1 (b INT);
+CREATE PROCEDURE p1
+AS
+a t1.a%TYPE;
+BEGIN
+NULL;
+END;
+$$
+CALL p1();
+ERROR 42S22: Unknown column 'a' in 't1'
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# One %TYPE variable
+#
+CREATE TABLE t1 (a INT);
+CREATE PROCEDURE p1
+AS
+a t1.a%TYPE;
+BEGIN
+a:= 123;
+SELECT a;
+END;
+$$
+CALL p1();
+a
+123
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# Two %TYPE variables, with a truncation warning on assignment
+#
+CREATE TABLE t1 (a TINYINT, b INT);
+CREATE PROCEDURE p1
+AS
+a t1.a%TYPE;
+b t1.b%TYPE;
+BEGIN
+a:= 200;
+b:= 200;
+SELECT a, b;
+END;
+$$
+CALL p1();
+a b
+127 200
+Warnings:
+Warning 1264 Out of range value for column 'a' at row 0
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# %TYPE variables for fields with various attributes
+#
+CREATE TABLE t1 (
+id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
+a TINYINT NOT NULL,
+b INT NOT NULL,
+ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+UNIQUE(a)
+);
+CREATE PROCEDURE p1
+AS
+id t1.id%TYPE;
+a t1.a%TYPE;
+b t1.b%TYPE;
+ts t1.ts%TYPE;
+BEGIN
+SELECT id, a, b, ts;
+CREATE TABLE t2 AS SELECT id, a, b, ts;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+END;
+$$
+CALL p1();
+id a b ts
+NULL NULL NULL NULL
+Table Create Table
+t2 CREATE TABLE "t2" (
+ "id" int(11) DEFAULT NULL,
+ "a" tinyint(4) DEFAULT NULL,
+ "b" int(11) DEFAULT NULL,
+ "ts" timestamp NULL DEFAULT NULL
+)
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# %TYPE + virtual columns
+#
+CREATE TABLE t1 (
+a INT NOT NULL,
+b VARCHAR(32),
+c INT AS (a + 10) VIRTUAL,
+d VARCHAR(5) AS (left(b,5)) PERSISTENT
+);
+CREATE PROCEDURE p1
+AS
+c t1.c%TYPE;
+d t1.d%TYPE;
+BEGIN
+SELECT c, d;
+CREATE TABLE t2 AS SELECT c, d;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+END;
+$$
+CALL p1();
+c d
+NULL NULL
+Table Create Table
+t2 CREATE TABLE "t2" (
+ "c" int(11) DEFAULT NULL,
+ "d" varchar(5) DEFAULT NULL
+)
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# %TYPE + the ZEROFILL attribute
+#
+CREATE TABLE t1 (
+dz DECIMAL(10,3) ZEROFILL
+);
+CREATE PROCEDURE p1
+AS
+dzr t1.dz%TYPE := 10;
+dzt DECIMAL(10,3) ZEROFILL := 10;
+BEGIN
+SELECT dzr, dzt;
+CREATE TABLE t2 AS SELECT dzr,dzt;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+END;
+$$
+CALL p1();
+dzr dzt
+0000010.000 0000010.000
+Table Create Table
+t2 CREATE TABLE "t2" (
+ "dzr" decimal(10,3) unsigned DEFAULT NULL,
+ "dzt" decimal(10,3) unsigned DEFAULT NULL
+)
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# Temporary tables shadow real tables for %TYPE purposes
+#
+CREATE TABLE t1 (a VARCHAR(10));
+INSERT INTO t1 VALUES ('t1');
+CREATE TEMPORARY TABLE t1 (a INT);
+INSERT INTO t1 VALUES (10);
+SELECT * FROM t1;
+a
+10
+CREATE PROCEDURE p1
+AS
+a t1.a%TYPE:=11;
+BEGIN
+CREATE TABLE t2 AS SELECT a;
+END;
+$$
+#
+# Should use INT(11) as %TYPE, as in the temporary table
+#
+CALL p1();
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE "t2" (
+ "a" int(11) DEFAULT NULL
+)
+SELECT * FROM t2;
+a
+11
+DROP TABLE t2;
+SELECT * FROM t1;
+a
+10
+DROP TEMPORARY TABLE t1;
+SELECT * FROM t1;
+a
+t1
+#
+# Should use VARCHAR(10) as %TYPE, as in the real table
+#
+CALL p1();
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE "t2" (
+ "a" varchar(10) DEFAULT NULL
+)
+SELECT * FROM t2;
+a
+11
+DROP TABLE t2;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# t1.a%TYPE searches for "t1" in the current database
+#
+CREATE TABLE t1 (a VARCHAR(10));
+CREATE DATABASE test1;
+CREATE TABLE test1.t1 (a INT);
+CREATE PROCEDURE p1
+AS
+a t1.a%TYPE:=11;
+BEGIN
+CREATE TABLE test.t2 AS SELECT a;
+END;
+$$
+#
+# This interprets t1.a%TYPE as VARCHAR(10), as in test.t1.a
+#
+USE test;
+CALL test.p1();
+SHOW CREATE TABLE test.t2;
+Table Create Table
+t2 CREATE TABLE "t2" (
+ "a" varchar(10) DEFAULT NULL
+)
+DROP TABLE test.t2;
+#
+# This interprets t1.a%TYPE as INT, as in test1.t1.a
+#
+USE test1;
+CALL test.p1();
+SHOW CREATE TABLE test.t2;
+Table Create Table
+t2 CREATE TABLE "t2" (
+ "a" int(11) DEFAULT NULL
+)
+DROP TABLE test.t2;
+#
+# Error if there is no an active database
+#
+DROP DATABASE test1;
+CALL test.p1();
+ERROR 3D000: No database selected
+USE test;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+#
+# A reference to a table in a non-existing database
+#
+CREATE PROCEDURE p1
+AS
+a test1.t1.a%TYPE;
+BEGIN
+CREATE TABLE t1 AS SELECT a;
+END;
+$$
+CALL p1;
+ERROR 42S02: Table 'test1.t1' doesn't exist
+DROP PROCEDURE p1;
+#
+# A reference to a table in a different database
+#
+CREATE TABLE t1(a INT);
+CREATE DATABASE test1;
+CREATE TABLE test1.t1 (a VARCHAR(10));
+CREATE PROCEDURE p1
+AS
+a t1.a%TYPE;
+b test1.t1.a%TYPE;
+BEGIN
+CREATE TABLE t2 AS SELECT a,b;
+END;
+$$
+CALL p1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE "t2" (
+ "a" int(11) DEFAULT NULL,
+ "b" varchar(10) DEFAULT NULL
+)
+DROP PROCEDURE p1;
+DROP TABLE t2;
+DROP DATABASE test1;
+DROP TABLE t1;
+#
+# Using a table before it appears in a %TYPE declaration + multiple %TYPE declarations
+#
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 (a,b) VALUES (10,'b10');
+CREATE PROCEDURE p1
+AS
+BEGIN
+INSERT INTO t1 (a,b) VALUES (11, 'b11');
+SELECT * FROM t1;
+DECLARE
+va t1.a%TYPE:= 30;
+vb t1.b%TYPE:= 'b30';
+BEGIN
+INSERT INTO t1 (a,b) VALUES (12,'b12');
+SELECT * FROM t1;
+INSERT INTO t1 (a,b) VALUES (va, vb);
+SELECT * FROM t1;
+END;
+DECLARE
+va t1.a%TYPE:= 40;
+vb t1.b%TYPE:= 'b40';
+BEGIN
+INSERT INTO t1 (a,b) VALUES (va,vb);
+SELECT * FROM t1;
+END;
+END;
+$$
+CALL p1;
+a b
+10 b10
+11 b11
+a b
+10 b10
+11 b11
+12 b12
+a b
+10 b10
+11 b11
+12 b12
+30 b30
+a b
+10 b10
+11 b11
+12 b12
+30 b30
+40 b40
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# %TYPE variables + TABLE vs VIEW
+#
+CREATE TABLE t1 (
+bit6 BIT(6),
+bit7 BIT(7),
+bit8 BIT(8),
+i1 TINYINT,
+i2 SMALLINT,
+i3 MEDIUMINT,
+i4 INT,
+i8 BIGINT,
+ff FLOAT,
+fd DOUBLE,
+cc CHAR(10),
+cv VARCHAR(10),
+cvu VARCHAR(10) CHARACTER SET utf8,
+t1 TINYTEXT,
+t2 TEXT,
+t3 MEDIUMTEXT,
+t4 LONGTEXT,
+enum1 ENUM('a','b','c'),
+set1 SET('a','b','c'),
+blob1 TINYBLOB,
+blob2 BLOB,
+blob3 MEDIUMBLOB,
+blob4 LONGBLOB,
+yy YEAR,
+dd DATE,
+tm0 TIME,
+tm3 TIME(3),
+tm6 TIME(6),
+dt0 DATETIME,
+dt3 DATETIME(3),
+dt6 DATETIME(6),
+ts0 TIMESTAMP,
+ts3 TIMESTAMP(3),
+ts6 TIMESTAMP(6),
+dc100 DECIMAL(10,0),
+dc103 DECIMAL(10,3),
+dc209 DECIMAL(20,9)
+);
+CREATE PROCEDURE p1(command enum('create','select'))
+AS
+bit6 t1.bit6%TYPE := 0x30;
+bit7 t1.bit7%TYPE := 0x41;
+bit8 t1.bit8%TYPE := 0x7E;
+i1 t1.i1%TYPE := 11;
+i2 t1.i2%TYPE := 12;
+i3 t1.i3%TYPE := 13;
+i4 t1.i4%TYPE := 14;
+i8 t1.i8%TYPE := 18;
+ff t1.ff%TYPE := 21;
+fd t1.fd%TYPE := 22;
+cc t1.cc%TYPE := 'char';
+cv t1.cv%TYPE := 'varchar';
+cvu t1.cvu%TYPE := 'varcharu8';
+t1 t1.t1%TYPE := 'text1';
+t2 t1.t2%TYPE := 'text2';
+t3 t1.t3%TYPE := 'text3';
+t4 t1.t4%TYPE := 'text4';
+enum1 t1.enum1%TYPE := 'b';
+set1 t1.set1%TYPE := 'a,c';
+blob1 t1.blob1%TYPE := 'blob1';
+blob2 t1.blob2%TYPE := 'blob2';
+blob3 t1.blob3%TYPE := 'blob3';
+blob4 t1.blob4%TYPE := 'blob4';
+yy t1.yy%TYPE := 2001;
+dd t1.dd%TYPE := '2001-01-01';
+tm0 t1.tm0%TYPE := '00:00:01';
+tm3 t1.tm3%TYPE := '00:00:03.333';
+tm6 t1.tm6%TYPE := '00:00:06.666666';
+dt0 t1.dt0%TYPE := '2001-01-01 00:00:01';
+dt3 t1.dt3%TYPE := '2001-01-03 00:00:01.333';
+dt6 t1.dt6%TYPE := '2001-01-06 00:00:01.666666';
+ts0 t1.ts0%TYPE := '2002-01-01 00:00:01';
+ts3 t1.ts3%TYPE := '2002-01-03 00:00:01.333';
+ts6 t1.ts6%TYPE := '2002-01-06 00:00:01.666666';
+dc100 t1.dc100%TYPE := 10;
+dc103 t1.dc103%TYPE := 10.123;
+dc209 t1.dc209%TYPE := 10.123456789;
+BEGIN
+CASE
+WHEN command='create' THEN
+CREATE TABLE t2 AS SELECT
+bit6, bit7, bit8,
+i1,i2,i3,i4,i8,
+ff,fd, dc100, dc103, dc209,
+cc,cv,cvu,
+t1,t2,t3,t4,
+enum1, set1,
+blob1, blob2, blob3, blob4,
+dd, yy,
+tm0, tm3, tm6,
+dt0, dt3, dt6,
+ts0, ts3, ts6;
+WHEN command='select' THEN
+SELECT
+bit6, bit7, bit8,
+i1,i2,i3,i4,i8,
+ff,fd, dc100, dc103, dc209,
+cc,cv,cvu,
+t1,t2,t3,t4,
+enum1, set1,
+blob1, blob2, blob3, blob4,
+dd, yy,
+tm0, tm3, tm6,
+dt0, dt3, dt6,
+ts0, ts3, ts6;
+END CASE;
+END;
+$$
+#
+# TABLE
+#
+CALL p1('create');
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE "t2" (
+ "bit6" bit(6) DEFAULT NULL,
+ "bit7" bit(7) DEFAULT NULL,
+ "bit8" bit(8) DEFAULT NULL,
+ "i1" tinyint(4) DEFAULT NULL,
+ "i2" smallint(6) DEFAULT NULL,
+ "i3" mediumint(9) DEFAULT NULL,
+ "i4" int(11) DEFAULT NULL,
+ "i8" bigint(20) DEFAULT NULL,
+ "ff" float DEFAULT NULL,
+ "fd" double DEFAULT NULL,
+ "dc100" decimal(10,0) DEFAULT NULL,
+ "dc103" decimal(10,3) DEFAULT NULL,
+ "dc209" decimal(20,9) DEFAULT NULL,
+ "cc" char(10) DEFAULT NULL,
+ "cv" varchar(10) DEFAULT NULL,
+ "cvu" varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+ "t1" tinytext DEFAULT NULL,
+ "t2" text DEFAULT NULL,
+ "t3" mediumtext DEFAULT NULL,
+ "t4" longtext DEFAULT NULL,
+ "enum1" varchar(1) DEFAULT NULL,
+ "set1" varchar(5) DEFAULT NULL,
+ "blob1" tinyblob DEFAULT NULL,
+ "blob2" longblob DEFAULT NULL,
+ "blob3" mediumblob DEFAULT NULL,
+ "blob4" longblob DEFAULT NULL,
+ "dd" datetime DEFAULT NULL,
+ "yy" year(4) DEFAULT NULL,
+ "tm0" time DEFAULT NULL,
+ "tm3" time(3) DEFAULT NULL,
+ "tm6" time(6) DEFAULT NULL,
+ "dt0" datetime DEFAULT NULL,
+ "dt3" datetime(3) DEFAULT NULL,
+ "dt6" datetime(6) DEFAULT NULL,
+ "ts0" timestamp NULL DEFAULT NULL,
+ "ts3" timestamp(3) NULL DEFAULT NULL,
+ "ts6" timestamp(6) NULL DEFAULT NULL
+)
+SELECT * FROM t2;
+bit6 0
+bit7 A
+bit8 ~
+i1 11
+i2 12
+i3 13
+i4 14
+i8 18
+ff 21
+fd 22
+dc100 10
+dc103 10.123
+dc209 10.123456789
+cc char
+cv varchar
+cvu varcharu8
+t1 text1
+t2 text2
+t3 text3
+t4 text4
+enum1 b
+set1 a,c
+blob1 blob1
+blob2 blob2
+blob3 blob3
+blob4 blob4
+dd 2001-01-01 00:00:00
+yy 2001
+tm0 00:00:01
+tm3 00:00:03.333
+tm6 00:00:06.666666
+dt0 2001-01-01 00:00:01
+dt3 2001-01-03 00:00:01.333
+dt6 2001-01-06 00:00:01.666666
+ts0 2002-01-01 00:00:01
+ts3 2002-01-03 00:00:01.333
+ts6 2002-01-06 00:00:01.666666
+DROP TABLE t2;
+CALL p1('select');
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def bit6 bit6 16 6 1 Y 32 0 63
+def bit7 bit7 16 7 1 Y 32 0 63
+def bit8 bit8 16 8 1 Y 32 0 63
+def i1 i1 1 4 2 Y 32768 0 63
+def i2 i2 2 6 2 Y 32768 0 63
+def i3 i3 9 9 2 Y 32768 0 63
+def i4 i4 3 11 2 Y 32768 0 63
+def i8 i8 8 20 2 Y 32768 0 63
+def ff ff 4 12 2 Y 32768 31 63
+def fd fd 5 22 2 Y 32768 31 63
+def dc100 dc100 246 11 2 Y 32768 0 63
+def dc103 dc103 246 12 6 Y 32768 3 63
+def dc209 dc209 246 22 12 Y 32768 9 63
+def cc cc 254 10 4 Y 0 0 8
+def cv cv 253 10 7 Y 0 0 8
+def cvu cvu 253 10 9 Y 0 0 8
+def t1 t1 252 255 5 Y 16 0 8
+def t2 t2 252 65535 5 Y 16 0 8
+def t3 t3 252 16777215 5 Y 16 0 8
+def t4 t4 252 4294967295 5 Y 16 0 8
+def enum1 enum1 254 1 1 Y 256 0 8
+def set1 set1 254 5 3 Y 2048 0 8
+def blob1 blob1 252 255 5 Y 144 0 63
+def blob2 blob2 252 4294967295 5 Y 144 0 63
+def blob3 blob3 252 16777215 5 Y 144 0 63
+def blob4 blob4 252 4294967295 5 Y 144 0 63
+def dd dd 12 19 19 Y 128 0 63
+def yy yy 13 4 4 Y 32864 0 63
+def tm0 tm0 11 10 8 Y 128 0 63
+def tm3 tm3 11 14 12 Y 128 3 63
+def tm6 tm6 11 17 15 Y 128 6 63
+def dt0 dt0 12 19 19 Y 128 0 63
+def dt3 dt3 12 23 23 Y 128 3 63
+def dt6 dt6 12 26 26 Y 128 6 63
+def ts0 ts0 7 19 19 Y 160 0 63
+def ts3 ts3 7 23 23 Y 160 3 63
+def ts6 ts6 7 26 26 Y 160 6 63
+bit6 0
+bit7 A
+bit8 ~
+i1 11
+i2 12
+i3 13
+i4 14
+i8 18
+ff 21
+fd 22
+dc100 10
+dc103 10.123
+dc209 10.123456789
+cc char
+cv varchar
+cvu varcharu8
+t1 text1
+t2 text2
+t3 text3
+t4 text4
+enum1 b
+set1 a,c
+blob1 blob1
+blob2 blob2
+blob3 blob3
+blob4 blob4
+dd 2001-01-01 00:00:00
+yy 2001
+tm0 00:00:01
+tm3 00:00:03.333
+tm6 00:00:06.666666
+dt0 2001-01-01 00:00:01
+dt3 2001-01-03 00:00:01.333
+dt6 2001-01-06 00:00:01.666666
+ts0 2002-01-01 00:00:01
+ts3 2002-01-03 00:00:01.333
+ts6 2002-01-06 00:00:01.666666
+#
+# VIEW
+#
+ALTER TABLE t1 RENAME t0;
+CREATE VIEW t1 AS SELECT * FROM t0;
+CALL p1('create');
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE "t2" (
+ "bit6" bit(6) DEFAULT NULL,
+ "bit7" bit(7) DEFAULT NULL,
+ "bit8" bit(8) DEFAULT NULL,
+ "i1" tinyint(4) DEFAULT NULL,
+ "i2" smallint(6) DEFAULT NULL,
+ "i3" mediumint(9) DEFAULT NULL,
+ "i4" int(11) DEFAULT NULL,
+ "i8" bigint(20) DEFAULT NULL,
+ "ff" float DEFAULT NULL,
+ "fd" double DEFAULT NULL,
+ "dc100" decimal(10,0) DEFAULT NULL,
+ "dc103" decimal(10,3) DEFAULT NULL,
+ "dc209" decimal(20,9) DEFAULT NULL,
+ "cc" char(10) DEFAULT NULL,
+ "cv" varchar(10) DEFAULT NULL,
+ "cvu" varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+ "t1" tinytext DEFAULT NULL,
+ "t2" text DEFAULT NULL,
+ "t3" mediumtext DEFAULT NULL,
+ "t4" longtext DEFAULT NULL,
+ "enum1" varchar(1) DEFAULT NULL,
+ "set1" varchar(5) DEFAULT NULL,
+ "blob1" tinyblob DEFAULT NULL,
+ "blob2" longblob DEFAULT NULL,
+ "blob3" mediumblob DEFAULT NULL,
+ "blob4" longblob DEFAULT NULL,
+ "dd" datetime DEFAULT NULL,
+ "yy" year(4) DEFAULT NULL,
+ "tm0" time DEFAULT NULL,
+ "tm3" time(3) DEFAULT NULL,
+ "tm6" time(6) DEFAULT NULL,
+ "dt0" datetime DEFAULT NULL,
+ "dt3" datetime(3) DEFAULT NULL,
+ "dt6" datetime(6) DEFAULT NULL,
+ "ts0" timestamp NULL DEFAULT NULL,
+ "ts3" timestamp(3) NULL DEFAULT NULL,
+ "ts6" timestamp(6) NULL DEFAULT NULL
+)
+SELECT * FROM t2;
+bit6 0
+bit7 A
+bit8 ~
+i1 11
+i2 12
+i3 13
+i4 14
+i8 18
+ff 21
+fd 22
+dc100 10
+dc103 10.123
+dc209 10.123456789
+cc char
+cv varchar
+cvu varcharu8
+t1 text1
+t2 text2
+t3 text3
+t4 text4
+enum1 b
+set1 a,c
+blob1 blob1
+blob2 blob2
+blob3 blob3
+blob4 blob4
+dd 2001-01-01 00:00:00
+yy 2001
+tm0 00:00:01
+tm3 00:00:03.333
+tm6 00:00:06.666666
+dt0 2001-01-01 00:00:01
+dt3 2001-01-03 00:00:01.333
+dt6 2001-01-06 00:00:01.666666
+ts0 2002-01-01 00:00:01
+ts3 2002-01-03 00:00:01.333
+ts6 2002-01-06 00:00:01.666666
+DROP TABLE t2;
+CALL p1('select');
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def bit6 bit6 16 6 1 Y 32 0 63
+def bit7 bit7 16 7 1 Y 32 0 63
+def bit8 bit8 16 8 1 Y 32 0 63
+def i1 i1 1 4 2 Y 32768 0 63
+def i2 i2 2 6 2 Y 32768 0 63
+def i3 i3 9 9 2 Y 32768 0 63
+def i4 i4 3 11 2 Y 32768 0 63
+def i8 i8 8 20 2 Y 32768 0 63
+def ff ff 4 12 2 Y 32768 31 63
+def fd fd 5 22 2 Y 32768 31 63
+def dc100 dc100 246 11 2 Y 32768 0 63
+def dc103 dc103 246 12 6 Y 32768 3 63
+def dc209 dc209 246 22 12 Y 32768 9 63
+def cc cc 254 10 4 Y 0 0 8
+def cv cv 253 10 7 Y 0 0 8
+def cvu cvu 253 10 9 Y 0 0 8
+def t1 t1 252 255 5 Y 16 0 8
+def t2 t2 252 65535 5 Y 16 0 8
+def t3 t3 252 16777215 5 Y 16 0 8
+def t4 t4 252 4294967295 5 Y 16 0 8
+def enum1 enum1 254 1 1 Y 256 0 8
+def set1 set1 254 5 3 Y 2048 0 8
+def blob1 blob1 252 255 5 Y 144 0 63
+def blob2 blob2 252 4294967295 5 Y 144 0 63
+def blob3 blob3 252 16777215 5 Y 144 0 63
+def blob4 blob4 252 4294967295 5 Y 144 0 63
+def dd dd 12 19 19 Y 128 0 63
+def yy yy 13 4 4 Y 32864 0 63
+def tm0 tm0 11 10 8 Y 128 0 63
+def tm3 tm3 11 14 12 Y 128 3 63
+def tm6 tm6 11 17 15 Y 128 6 63
+def dt0 dt0 12 19 19 Y 128 0 63
+def dt3 dt3 12 23 23 Y 128 3 63
+def dt6 dt6 12 26 26 Y 128 6 63
+def ts0 ts0 7 19 19 Y 160 0 63
+def ts3 ts3 7 23 23 Y 160 3 63
+def ts6 ts6 7 26 26 Y 160 6 63
+bit6 0
+bit7 A
+bit8 ~
+i1 11
+i2 12
+i3 13
+i4 14
+i8 18
+ff 21
+fd 22
+dc100 10
+dc103 10.123
+dc209 10.123456789
+cc char
+cv varchar
+cvu varcharu8
+t1 text1
+t2 text2
+t3 text3
+t4 text4
+enum1 b
+set1 a,c
+blob1 blob1
+blob2 blob2
+blob3 blob3
+blob4 blob4
+dd 2001-01-01 00:00:00
+yy 2001
+tm0 00:00:01
+tm3 00:00:03.333
+tm6 00:00:06.666666
+dt0 2001-01-01 00:00:01
+dt3 2001-01-03 00:00:01.333
+dt6 2001-01-06 00:00:01.666666
+ts0 2002-01-01 00:00:01
+ts3 2002-01-03 00:00:01.333
+ts6 2002-01-06 00:00:01.666666
+DROP VIEW t1;
+DROP TABLE t0;
+DROP PROCEDURE p1;
+#
+# VIEW with subqueries
+#
+CREATE TABLE t1 (a INT,b INT);
+INSERT INTO t1 VALUES (10,1),(20,2),(30,3),(40,4);
+SELECT AVG(a) FROM t1;
+AVG(a)
+25.0000
+CREATE VIEW v1 AS SELECT a,1 as b FROM t1 WHERE a>(SELECT AVG(a) FROM t1) AND b>(SELECT 1);
+SELECT * FROM v1;
+a b
+30 1
+40 1
+CREATE PROCEDURE p1
+AS
+a v1.a%TYPE := 10;
+b v1.b%TYPE := 1;
+BEGIN
+SELECT a,b;
+END;
+$$
+CALL p1;
+a b
+10 1
+DROP PROCEDURE p1;
+CREATE FUNCTION f1 RETURN INT
+AS
+a v1.a%TYPE := 10;
+b v1.b%TYPE := 1;
+BEGIN
+RETURN a+b;
+END;
+$$
+SELECT f1();
+f1()
+11
+DROP FUNCTION f1;
+DROP VIEW v1;
+DROP TABLE t1;
+#
+# %TYPE variables + INFORMATION_SCHEMA
+#
+CREATE PROCEDURE p1
+AS
+tables_table_name INFORMATION_SCHEMA.TABLES.TABLE_NAME%TYPE;
+tables_table_rows INFORMATION_SCHEMA.TABLES.TABLE_ROWS%TYPE;
+processlist_info INFORMATION_SCHEMA.PROCESSLIST.INFO%TYPE;
+processlist_info_binary INFORMATION_SCHEMA.PROCESSLIST.INFO_BINARY%TYPE;
+BEGIN
+CREATE TABLE t1 AS SELECT
+tables_table_name,
+tables_table_rows,
+processlist_info,
+processlist_info_binary;
+END;
+$$
+CALL p1();
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE "t1" (
+ "tables_table_name" varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+ "tables_table_rows" bigint(21) unsigned DEFAULT NULL,
+ "processlist_info" longtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+ "processlist_info_binary" blob(65535) DEFAULT NULL
+)
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# %TYPE + Table structure change
+# Data type for both a0 and a1 is chosen in the very beginning
+#
+CREATE PROCEDURE p1
+AS
+a0 t1.a%TYPE;
+BEGIN
+ALTER TABLE t1 MODIFY a VARCHAR(10); -- This does not affect a1
+DECLARE
+a1 t1.a%TYPE;
+BEGIN
+CREATE TABLE t2 AS SELECT a0, a1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+END;
+END
+$$
+CREATE TABLE t1 (a INT);
+CALL p1;
+Table Create Table
+t2 CREATE TABLE "t2" (
+ "a0" int(11) DEFAULT NULL,
+ "a1" int(11) DEFAULT NULL
+)
+DROP TABLE t1;
+DROP PROCEDURE p1;
+#
+# %TYPE in parameters
+#
+CREATE TABLE t1 (a VARCHAR(10));
+CREATE DATABASE test1;
+CREATE TABLE test1.t1 (b SMALLINT);
+CREATE PROCEDURE p1(a t1.a%TYPE, b test1.t1.b%TYPE)
+AS
+BEGIN
+CREATE TABLE t2 AS SELECT a, b;
+END;
+$$
+CALL p1('test', 123);
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE "t2" (
+ "a" varchar(10) DEFAULT NULL,
+ "b" smallint(6) DEFAULT NULL
+)
+SELECT * FROM t2;
+a b
+test 123
+DROP TABLE t2;
+DROP PROCEDURE p1;
+DROP TABLE test1.t1;
+DROP DATABASE test1;
+DROP TABLE t1;
+#
+# %TYPE in a stored function variables and arguments
+#
+CREATE TABLE t1 (a INT);
+SET sql_mode=ORACLE;
+CREATE FUNCTION f1 (prm t1.a%TYPE) RETURN INT
+AS
+a t1.a%TYPE:= prm;
+BEGIN
+RETURN a;
+END;
+$$
+SELECT f1(20);
+f1(20)
+20
+DROP FUNCTION f1;
+DROP TABLE t1;
+#
+# %TYPE in function RETURN clause is not supported yet
+#
+CREATE FUNCTION f1 RETURN t1.a%TYPE
+AS
+BEGIN
+RETURN 0;
+END;
+$$
+ERROR HY000: Unknown data type: 't1'
+#
+# End of MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations
+#
+#
+# MDEV-12089 sql_mode=ORACLE: Understand optional routine name after the END keyword
+#
+CREATE FUNCTION f1 RETURN INT AS
+BEGIN
+RETURN 10;
+END f1;
+$$
+DROP FUNCTION f1;
+CREATE FUNCTION test.f1 RETURN INT AS
+BEGIN
+RETURN 10;
+END test.f1;
+$$
+DROP FUNCTION f1;
+CREATE FUNCTION test.f1 RETURN INT AS
+BEGIN
+RETURN 10;
+END test2.f1;
+$$
+ERROR HY000: END identifier 'test2.f1' does not match 'test.f1'
+CREATE FUNCTION test.f1 RETURN INT AS
+BEGIN
+RETURN 10;
+END test.f2;
+$$
+ERROR HY000: END identifier 'test.f2' does not match 'test.f1'
+CREATE FUNCTION f1 RETURN INT AS
+BEGIN
+RETURN 10;
+END test.f2;
+$$
+ERROR HY000: END identifier 'test.f2' does not match 'test.f1'
+CREATE FUNCTION f1 RETURN INT AS
+BEGIN
+RETURN 10;
+END test2.f1;
+$$
+ERROR HY000: END identifier 'test2.f1' does not match 'test.f1'
+CREATE PROCEDURE p1 AS
+BEGIN
+NULL;
+END p1;
+$$
+DROP PROCEDURE p1;
+CREATE PROCEDURE test.p1 AS
+BEGIN
+NULL;
+END test.p1;
+$$
+DROP PROCEDURE p1;
+CREATE PROCEDURE test.p1 AS
+BEGIN
+NULL;
+END test2.p1;
+$$
+ERROR HY000: END identifier 'test2.p1' does not match 'test.p1'
+CREATE PROCEDURE test.p1 AS
+BEGIN
+NULL;
+END test.p2;
+$$
+ERROR HY000: END identifier 'test.p2' does not match 'test.p1'
+CREATE PROCEDURE p1 AS
+BEGIN
+NULL;
+END test.p2;
+$$
+ERROR HY000: END identifier 'test.p2' does not match 'test.p1'
+CREATE PROCEDURE p1 AS
+BEGIN
+NULL;
+END test2.p1;
+$$
+ERROR HY000: END identifier 'test2.p1' does not match 'test.p1'
+#
+# MDEV-12107 sql_mode=ORACLE: Inside routines the CALL keywoard is optional
+#
+CREATE OR REPLACE PROCEDURE p1(a INT) AS
+BEGIN
+SELECT 'This is p1' AS "comment";
+END;
+/
+CREATE OR REPLACE PROCEDURE p2 AS
+BEGIN
+SELECT 'This is p2' AS "comment";
+END;
+/
+BEGIN
+p1(10);
+p2;
+test.p1(10);
+test.p2;
+END;
+/
+comment
+This is p1
+comment
+This is p2
+comment
+This is p1
+comment
+This is p2
+CREATE PROCEDURE p3 AS
+BEGIN
+p1(10);
+p2;
+test.p1(10);
+test.p2;
+END
+/
+CALL p3;
+comment
+This is p1
+comment
+This is p2
+comment
+This is p1
+comment
+This is p2
+DROP PROCEDURE p3;
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+#
+# MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
+#
+SELECT SQL%ROWCOUNT;
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def SQL%ROWCOUNT 8 21 1 N 32897 0 63
+SQL%ROWCOUNT
+0
+#
+# MDEV-13686 EXCEPTION reserved keyword in SQL_MODE=oracle but not in Oracle itself
+#
+CREATE TABLE t1 (c1 int);
+CREATE VIEW v1 AS SELECT c1 exception FROM t1;
+SELECT exception FROM v1;
+exception
+DROP VIEW v1;
+DROP TABLE t1;
+#
+# MDEV-14139 Anchored data types for variables
+#
+BEGIN NOT ATOMIC
+DECLARE a a%TYPE;
+END;
+$$
+ERROR 42000: Undeclared variable: a
+DECLARE
+int11 INT;
+dec103 DECIMAL(10,3);
+flt0 FLOAT;
+dbl0 DOUBLE;
+enum0 ENUM('a','b');
+bit3 BIT(3);
+varchar10 VARCHAR(10);
+text1 TEXT;
+tinytext1 TINYTEXT;
+mediumtext1 MEDIUMTEXT;
+longtext1 LONGTEXT;
+time3 TIME(3);
+datetime4 DATETIME(4);
+timestamp5 TIMESTAMP(5);
+date0 DATE;
+a_int11 int11%TYPE;
+a_dec103 dec103%TYPE;
+a_flt0 flt0%TYPE;
+a_dbl0 dbl0%TYPE;
+a_bit3 bit3%TYPE;
+a_enum0 enum0%TYPE;
+a_varchar10 varchar10%TYPE;
+a_text1 text1%TYPE;
+a_tinytext1 tinytext1%TYPE;
+a_mediumtext1 mediumtext1%TYPE;
+a_longtext1 longtext1%TYPE;
+a_time3 time3%TYPE;
+a_datetime4 datetime4%TYPE;
+a_timestamp5 timestamp5%TYPE;
+a_date0 date0%TYPE;
+aa_int11 a_int11%TYPE;
+aa_dec103 a_dec103%TYPE;
+aa_flt0 a_flt0%TYPE;
+aa_dbl0 a_dbl0%TYPE;
+aa_bit3 a_bit3%TYPE;
+aa_enum0 a_enum0%TYPE;
+aa_varchar10 a_varchar10%TYPE;
+aa_text1 a_text1%TYPE;
+aa_tinytext1 a_tinytext1%TYPE;
+aa_mediumtext1 a_mediumtext1%TYPE;
+aa_longtext1 a_longtext1%TYPE;
+aa_time3 a_time3%TYPE;
+aa_datetime4 a_datetime4%TYPE;
+aa_timestamp5 a_timestamp5%TYPE;
+aa_date0 a_date0%TYPE;
+BEGIN
+CREATE TABLE t1 AS
+SELECT a_int11,a_dec103,a_flt0,a_dbl0,a_bit3,
+a_enum0,a_varchar10,
+a_text1,a_tinytext1,a_mediumtext1,a_longtext1,
+a_time3,a_datetime4,a_timestamp5,a_date0;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+CREATE TABLE t1 AS
+SELECT aa_int11,aa_dec103,aa_flt0,aa_dbl0,aa_bit3,
+aa_enum0,aa_varchar10,
+aa_text1,aa_tinytext1,aa_mediumtext1,aa_longtext1,
+aa_time3,aa_datetime4,aa_timestamp5,aa_date0;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+END;
+$$
+Table Create Table
+t1 CREATE TABLE "t1" (
+ "a_int11" int(11) DEFAULT NULL,
+ "a_dec103" decimal(10,3) DEFAULT NULL,
+ "a_flt0" float DEFAULT NULL,
+ "a_dbl0" double DEFAULT NULL,
+ "a_bit3" bit(3) DEFAULT NULL,
+ "a_enum0" varchar(1) DEFAULT NULL,
+ "a_varchar10" varchar(10) DEFAULT NULL,
+ "a_text1" text DEFAULT NULL,
+ "a_tinytext1" tinytext DEFAULT NULL,
+ "a_mediumtext1" mediumtext DEFAULT NULL,
+ "a_longtext1" longtext DEFAULT NULL,
+ "a_time3" time(3) DEFAULT NULL,
+ "a_datetime4" datetime(4) DEFAULT NULL,
+ "a_timestamp5" timestamp(5) NULL DEFAULT NULL,
+ "a_date0" datetime DEFAULT NULL
+)
+Table Create Table
+t1 CREATE TABLE "t1" (
+ "aa_int11" int(11) DEFAULT NULL,
+ "aa_dec103" decimal(10,3) DEFAULT NULL,
+ "aa_flt0" float DEFAULT NULL,
+ "aa_dbl0" double DEFAULT NULL,
+ "aa_bit3" bit(3) DEFAULT NULL,
+ "aa_enum0" varchar(1) DEFAULT NULL,
+ "aa_varchar10" varchar(10) DEFAULT NULL,
+ "aa_text1" text DEFAULT NULL,
+ "aa_tinytext1" tinytext DEFAULT NULL,
+ "aa_mediumtext1" mediumtext DEFAULT NULL,
+ "aa_longtext1" longtext DEFAULT NULL,
+ "aa_time3" time(3) DEFAULT NULL,
+ "aa_datetime4" datetime(4) DEFAULT NULL,
+ "aa_timestamp5" timestamp(5) NULL DEFAULT NULL,
+ "aa_date0" datetime DEFAULT NULL
+)
+#
+# MDEV-11160 "Incorrect column name" when "CREATE TABLE t1 AS SELECT spvar"
+#
+CREATE TABLE t1 (x INT);
+INSERT INTO t1 VALUES (10);
+CREATE VIEW v1 AS SELECT x+1 AS a,x+1 AS b FROM t1;
+CREATE PROCEDURE p1
+AS
+a INT := 1;
+b INT := 2;
+BEGIN
+CREATE TABLE t2 AS SELECT a,b FROM v1;
+SHOW CREATE TABLE t2;
+SELECT * FROM t2;
+DROP TABLE t2;
+END;
+$$
+CALL p1();
+Table Create Table
+t2 CREATE TABLE "t2" (
+ "a" int(11) DEFAULT NULL,
+ "b" int(11) DEFAULT NULL
+)
+a b
+1 2
+DROP PROCEDURE p1;
+DROP VIEW v1;
+DROP TABLE t1;
+#
+# MDEV-14228 MariaDB crashes with function
+#
+CREATE TABLE t1 (c VARCHAR(16), KEY(c));
+INSERT INTO t1 VALUES ('foo');
+CREATE FUNCTION f1() RETURN VARCHAR(16)
+IS
+v VARCHAR2(16);
+BEGIN
+FOR v IN (SELECT DISTINCT c FROM t1)
+LOOP
+IF (v = 'bar') THEN
+SELECT 1 INTO @a;
+END IF;
+END LOOP;
+RETURN 'qux';
+END $$
+SELECT f1();
+ERROR HY000: Illegal parameter data types row and varchar for operation '='
+DROP FUNCTION f1;
+CREATE FUNCTION f1() RETURN VARCHAR(16)
+IS
+v t1%ROWTYPE;
+BEGIN
+IF v = 'bar' THEN
+NULL;
+END IF;
+RETURN 'qux';
+END $$
+SELECT f1();
+ERROR HY000: Illegal parameter data types row and varchar for operation '='
+DROP FUNCTION f1;
+CREATE FUNCTION f1() RETURN VARCHAR(16)
+IS
+v ROW(a INT);
+BEGIN
+IF v = 'bar' THEN
+NULL;
+END IF;
+RETURN 'qux';
+END $$
+SELECT f1();
+ERROR HY000: Illegal parameter data types row and varchar for operation '='
+DROP FUNCTION f1;
+DROP TABLE t1;
+DECLARE
+v ROW(a INT);
+BEGIN
+SELECT v IN ('a','b');
+END $$
+ERROR HY000: Illegal parameter data types row and varchar for operation 'in'
+DECLARE
+v ROW(a INT);
+BEGIN
+SELECT 'a' IN (v,'b');
+END $$
+ERROR HY000: Illegal parameter data types varchar and row for operation 'in'
+DECLARE
+v ROW(a INT);
+BEGIN
+SELECT 'a' IN ('b',v);
+END $$
+ERROR HY000: Illegal parameter data types varchar and row for operation 'in'
+#
+# MDEV-17253 Oracle compatibility: The REVERSE key word for FOR loop behaves incorrectly
+#
+DECLARE
+totalprice DECIMAL(12,2):=NULL;
+loop_start INTEGER := 1;
+BEGIN
+FOR idx IN REVERSE loop_start..10 LOOP
+SELECT idx;
+END LOOP;
+END;
+$$
+idx
+10
+idx
+9
+idx
+8
+idx
+7
+idx
+6
+idx
+5
+idx
+4
+idx
+3
+idx
+2
+idx
+1
+CREATE PROCEDURE p1 AS
+loop_start INTEGER := 1;
+BEGIN
+FOR idx IN REVERSE 3..loop_start LOOP
+SELECT idx;
+END LOOP;
+END;
+$$
+CALL p1();
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1 AS
+loop_start INTEGER := 1;
+BEGIN
+FOR idx IN REVERSE loop_start..3 LOOP
+SELECT idx;
+END LOOP;
+END;
+$$
+CALL p1();
+idx
+3
+idx
+2
+idx
+1
+DROP PROCEDURE p1;
+#
+# MDEV-28588 SIGSEGV in __memmove_avx_unaligned_erms, strmake_root
+#
+SET sql_mode=ORACLE;
+BEGIN END;
+CREATE TABLE t1 (a INT);
+CREATE TRIGGER tr AFTER INSERT ON t1 FOR EACH ROW BEGIN END;
+SELECT ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA='test' AND TRIGGER_NAME='tr';
+ACTION_STATEMENT BEGIN END
+DROP TRIGGER tr;
+DROP TABLE t1;
+#
+# End of 10.3 tests
+#
+#
+# MDEV-19637 Crash on an SP variable assignment to a wrong subselect
+#
+DECLARE
+a INT;
+BEGIN
+SET a=(SELECT 1 FROM DUAL UNION SELECT HIGH_PRIORITY 2 FROM DUAL);
+END;
+$$
+ERROR 42000: Incorrect usage/placement of 'HIGH_PRIORITY'
+#
+# End of 10.4 tests
+#
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-25 00:25:57 +0000