diff options
Diffstat (limited to 'mysql-test/suite/compat/oracle/t/sp.test')
-rw-r--r-- | mysql-test/suite/compat/oracle/t/sp.test | 2399 |
1 files changed, 2399 insertions, 0 deletions
diff --git a/mysql-test/suite/compat/oracle/t/sp.test b/mysql-test/suite/compat/oracle/t/sp.test new file mode 100644 index 00000000000..31657aa4aef --- /dev/null +++ b/mysql-test/suite/compat/oracle/t/sp.test @@ -0,0 +1,2399 @@ +--source include/default_charset.inc + +SET sql_mode=ORACLE; + +--echo # Testing routines with no parameters +DELIMITER /; +CREATE FUNCTION f1 RETURN INT +AS +BEGIN + RETURN 10; +END; +/ +DELIMITER ;/ +--vertical_results +SHOW CREATE FUNCTION f1; +--horizontal_results +SELECT f1(); +DROP FUNCTION f1; + + +DELIMITER /; +CREATE PROCEDURE p1 +AS +BEGIN + SET @a=10; +END; +/ +DELIMITER ;/ +--vertical_results +SHOW CREATE PROCEDURE p1; +--horizontal_results +SET @a=0; +CALL p1(); +SELECT @a; +DROP PROCEDURE p1; + +--echo # Testing ":=" to set the default value of a variable +DELIMITER /; +CREATE FUNCTION f1 () RETURN NUMBER(10) AS + a NUMBER(10) := 10; +BEGIN + DECLARE + b NUMBER(10) DEFAULT 3; + BEGIN + RETURN a+b; + END; +END; +/ +DELIMITER ;/ +SELECT f1(); +DROP FUNCTION f1; + +--echo # Testing labels + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1(1); +SELECT f1(2); +DROP FUNCTION f1; + + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1(4); +DROP FUNCTION f1; + + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1(4); +DROP FUNCTION f1; + + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1(4); +DROP FUNCTION f1; + +--echo # Testing IN/OUT/INOUT + +DELIMITER /; +CREATE PROCEDURE p1 (p1 IN VARCHAR2(10), p2 OUT VARCHAR2(10)) AS +BEGIN + SET p1='p1new'; + SET p2='p2new'; +END; +/ +DELIMITER ;/ +SET @p1='p1', @p2='p2'; +CALL p1(@p1, @p2); +SELECT @p1, @p2; +DROP PROCEDURE p1; + +--echo # Testing Oracle-style assigment +DELIMITER /; +CREATE PROCEDURE p1 (p1 OUT VARCHAR2(10)) AS +BEGIN + p1:= 'p1new'; +END; +/ +DELIMITER ;/ +SET @p1='p1'; +CALL p1(@p1); +SELECT @p1; +DROP PROCEDURE p1; + +--echo # Testing that NULL is a valid statement +DELIMITER /; +CREATE PROCEDURE p1(a INT) AS +BEGIN + NULL; +END; +/ +DELIMITER ;/ +DROP PROCEDURE p1; + +DELIMITER /; +CREATE PROCEDURE p1(a INT) AS + a INT:=10; +BEGIN + IF a=10 THEN NULL; ELSE NULL; END IF; +END; +/ +DELIMITER ;/ +DROP PROCEDURE p1; + +--echo # 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; +DROP TABLE function; + +--echo # Testing that (some) keyword_sp are allowed in Oracle-style assignments +DELIMITER /; +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/ +DELIMITER ;/ + +--echo # Testing keyword_directly_assignable +DELIMITER /; +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/ +DELIMITER ;/ + + +--echo # Testing that keyword_directly_not_assignable does not work in := +DELIMITER /; +--error ER_PARSE_ERROR +CREATE PROCEDURE p1 (commit OUT INT) AS BEGIN commit:=10; END;/ +--error ER_PARSE_ERROR +CREATE PROCEDURE p1 (rollback OUT INT) AS BEGIN rollback:=10; END;/ +--error ER_PARSE_ERROR +CREATE PROCEDURE p1 (shutdown OUT INT) AS BEGIN shutdown:=10; END;/ +--error ER_PARSE_ERROR +CREATE PROCEDURE p1 (exception OUT INT) AS BEGIN exception:=10; END;/ +DELIMITER ;/ + + +--echo # Testing that keyword_directly_not_assignable works in SET statements. +DELIMITER /; +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/ +DELIMITER ;/ + +--echo # 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; + +--echo # Testing ELSIF +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1(2) FROM DUAL; +DROP FUNCTION f1; + + + +--echo # Testing top-level declarations +DELIMITER /; +CREATE PROCEDURE p1 (p1 OUT VARCHAR2(10)) +AS + p2 VARCHAR(10); +BEGIN + p2:='p1new'; + p1:=p2; +END; +/ +DELIMITER ;/ +SET @p1='p1'; +CALL p1(@p1); +SELECT @p1; +DROP PROCEDURE p1; + +DELIMITER /; +CREATE FUNCTION f1 (p1 VARCHAR2(10)) RETURN VARCHAR(20) +AS + p2 VARCHAR(10); +BEGIN + p2:='new'; + RETURN CONCAT(p1, p2); +END; +/ +DELIMITER ;/ +SET @p1='p1'; +SELECT f1(@p1); +DROP FUNCTION f1; + +--echo # Testing non-top declarations + +DELIMITER /; +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; +/ +DELIMITER ;/ +SET @p1='p1'; +CALL p1(@p1); +SELECT @p1; +DROP PROCEDURE p1; + +DELIMITER /; +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; +/ +DELIMITER ;/ +SET @p1='p1'; +SELECT f1(@p1); +DROP FUNCTION f1; + + +--echo # Testing exceptions + +CREATE TABLE t1 (c1 INT); + +DELIMITER /; + +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; +/ + +DELIMITER ;/ + +CALL sp1('abc', @a); +SELECT @a; + +DROP PROCEDURE sp1; +DROP TABLE t1; + + +DELIMITER /; +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; +/ +DELIMITER ;/ +SET @v=10; +CALL sp1(@v, 30001); +--error 30002 +CALL sp1(@v, 30002); +SELECT @v; +DROP PROCEDURE sp1; + + +DELIMITER /; +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; +/ +DELIMITER ;/ +SET @v=10; +CALL sp1(@v, 30001); +SELECT @v; +SET @v=10; +--error 30002 +CALL sp1(@v, 30002); +SELECT @v; +DROP PROCEDURE sp1; + + +--echo # +--echo # Testing EXIT statement +--echo # + +DELIMITER /; +--error ER_SP_LILABEL_MISMATCH +CREATE FUNCTION f1 RETURN INT +IS + i INT := 0; +BEGIN + EXIT; +END; +/ +DELIMITER ;/ + + +DELIMITER /; +--error ER_SP_LILABEL_MISMATCH +CREATE FUNCTION f1 RETURN INT +IS + i INT := 0; +BEGIN + <<lable1>> + BEGIN + <<label2>> + LOOP + EXIT label1; + END LOOP; + END; +END; +/ +DELIMITER ;/ + + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1() FROM DUAL; +DROP FUNCTION f1; + + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1() FROM DUAL; +DROP FUNCTION f1; + + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1() FROM DUAL; +DROP FUNCTION f1; + + +DELIMITER /; +CREATE FUNCTION f1 RETURN INT +IS + i INT := 0; +BEGIN + LOOP + i:= i + 1; + EXIT WHEN i >=5; + END LOOP; + RETURN i; +END; +/ +DELIMITER ;/ +SELECT f1() FROM DUAL; +DROP FUNCTION f1; + + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1() FROM DUAL; +DROP FUNCTION f1; + + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1() FROM DUAL; +DROP FUNCTION f1; + + +--echo # Testing CURSOR declaration + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1); +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1() FROM DUAL; +DROP FUNCTION f1; +DROP TABLE t1; + + +--echo # Testing RETURN in procedures + +DELIMITER /; +--error ER_SP_BADRETURN +CREATE PROCEDURE p1 (a IN OUT INT) +AS +BEGIN + RETURN 10; +END; +/ +DELIMITER ;/ + +DELIMITER /; +--error ER_PARSE_ERROR +CREATE FUNCTION f1 (a INT) RETURN INT +AS +BEGIN + RETURN; +END; +/ +DELIMITER ;/ + +DELIMITER /; +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; +/ +DELIMITER ;/ +SET @v=10; +CALL p1(@v); +SELECT @v; +SET @v=9; +CALL p1(@v); +SELECT @v; +DROP PROCEDURE p1; + +DELIMITER /; +CREATE PROCEDURE p1 (a IN OUT INT) +AS +BEGIN + DROP TABLE t1_non_existent; +EXCEPTION + WHEN OTHERS THEN + BEGIN + a:= 100; + RETURN; + END; +END; +/ +DELIMITER ;/ +SET @v=10; +CALL p1(@v); +SELECT @v; +DROP PROCEDURE p1; + + +--echo # Testing WHILE loop + +DELIMITER /; +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; +/ +DELIMITER ;/ +SET @v=0; +CALL p1(@v); +SELECT @v; +DROP PROCEDURE p1; + +DELIMITER /; +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; +/ +DELIMITER ;/ +SET @v=0; +CALL p1(@v); +SELECT @v; +DROP PROCEDURE p1; + + +--echo # Testing the FOR loop statement + +CREATE TABLE t1 (a INT); +DELIMITER /; +FOR i IN 1..3 +LOOP + INSERT INTO t1 VALUES (i); +END LOOP; +/ +DELIMITER ;/ +SELECT * FROM t1; +DROP TABLE t1; + + +DELIMITER /; +--error ER_PARSE_ERROR +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; +/ +DELIMITER ;/ + + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1(1, 3, 100) FROM DUAL; +SELECT f1(1, 3, 2) FROM DUAL; +DROP FUNCTION f1; + + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1() FROM DUAL; +DROP FUNCTION f1; + + +DELIMITER /; +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 +/ +DELIMITER ;/ +SELECT f1(3, 100) FROM DUAL; +SELECT f1(3, 2) FROM DUAL; +DROP FUNCTION f1; + + +--echo # Testing labeled FOR LOOP statement + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1(1, 1, 1, 1) FROM DUAL; +SELECT f1(1, 2, 1, 2) FROM DUAL; +SELECT f1(2, 1, 2, 1) FROM DUAL; +SELECT f1(2, 1, 2, 2) FROM DUAL; +SELECT f1(2, 2, 2, 2) FROM DUAL; +SELECT f1(2, 3, 2, 3) FROM DUAL; +DROP FUNCTION f1; + + +--echo # Testing labeled ITERATE in a labeled FOR LOOP statement + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1(3,3,0), f1(3,3,1), f1(3,3,2), f1(3,3,3), f1(3,3,4) FROM DUAL; +DROP FUNCTION f1; + + +--echo # Testing CONTINUE statement + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL; +DROP FUNCTION f1; + + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1(3), f1(4), f1(5) FROM DUAL; +DROP FUNCTION f1; + + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1(3), f1(4), f1(5) FROM DUAL; +DROP FUNCTION f1; + + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL; +DROP FUNCTION f1; + +--echo # +--echo # Testing behaviour of unknown identifiers in EXIT and CONTINUE statements +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1 +AS +BEGIN + LOOP + EXIT WHEN unknown_ident IS NULL; + END LOOP; +END$$ +DELIMITER ;$$ +--error ER_BAD_FIELD_ERROR +CALL p1; +DROP PROCEDURE p1; + + +DELIMITER $$; +CREATE PROCEDURE p1 +AS +BEGIN + <<label>> + LOOP + EXIT label WHEN unknown_ident IS NULL; + END LOOP; +END$$ +DELIMITER ;$$ +--error ER_BAD_FIELD_ERROR +CALL p1; +DROP PROCEDURE p1; + + +DELIMITER $$; +CREATE PROCEDURE p1 +AS +BEGIN + LOOP + CONTINUE WHEN unknown_ident IS NULL; + END LOOP; +END$$ +DELIMITER ;$$ +--error ER_BAD_FIELD_ERROR +CALL p1; +DROP PROCEDURE p1; + + +DELIMITER $$; +CREATE PROCEDURE p1 +AS +BEGIN + <<label>> + LOOP + CONTINUE label WHEN unknown_ident IS NULL; + END LOOP; +END$$ +DELIMITER ;$$ +--error ER_BAD_FIELD_ERROR +CALL p1; +DROP PROCEDURE p1; + +--echo # +--echo # MDEV-10583 sql_mode=ORACLE: SQL%ROWCOUNT +--echo # + +EXPLAIN EXTENDED SELECT sql%rowcount; +CREATE TABLE t1 AS SELECT SQL%ROWCOUNT; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # UPDATE +--echo # + +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE PROCEDURE p1 +AS +BEGIN + UPDATE t1 SET a=30; + SELECT SQL%ROWCOUNT; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10); +INSERT INTO t1 VALUES (20); +DELIMITER $$; +CREATE PROCEDURE p1 +AS +BEGIN + UPDATE t1 SET a=30; + SELECT SQL%ROWCOUNT; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # DELETE +--echo # + +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE PROCEDURE p1 +AS +BEGIN + DELETE FROM t1; + SELECT SQL%ROWCOUNT; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10); +INSERT INTO t1 VALUES (20); +DELIMITER $$; +CREATE PROCEDURE p1 +AS +BEGIN + DELETE FROM t1; + SELECT SQL%ROWCOUNT; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # SELECT ... INTO var FROM ... - one row found +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10); +INSERT INTO t1 VALUES (20); +DELIMITER $$; +CREATE PROCEDURE p1 +AS + va INT; +BEGIN + SELECT a INTO va FROM t1 LIMIT 1; + SELECT SQL%ROWCOUNT; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # SELECT ... INTO var FROM ... - no rows found +--echo # +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE PROCEDURE p1 +AS + va INT; +BEGIN + SELECT a INTO va FROM t1; + SELECT SQL%ROWCOUNT; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +CREATE TABLE t1 (a INT); +DELIMITER $$; +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; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # SELECT ... INTO var FROM ... - multiple rows found +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (10); +INSERT INTO t1 VALUES (20); +DELIMITER $$; +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; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + + +--echo # +--echo # INSERT INTO t2 SELECT ... +--echo # + +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a INT); +INSERT INTO t1 VALUES (10); +INSERT INTO t1 VALUES (20); +DELIMITER $$; +CREATE PROCEDURE p1 +AS +BEGIN + INSERT INTO t2 SELECT * FROM t1; + SELECT SQL%ROWCOUNT; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1, t2; + +--echo # +--echo # End of MDEV-10583 sql_mode=ORACLE: SQL%ROWCOUNT +--echo # + +--echo # +--echo # MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations +--echo # + +--echo # +--echo # Missing table +--echo # + +DELIMITER $$; +CREATE PROCEDURE p1 +AS + a t1.a%TYPE; +BEGIN + NULL; +END; +$$ +DELIMITER ;$$ +--error ER_NO_SUCH_TABLE +CALL p1(); +DROP PROCEDURE p1; + + +--echo # +--echo # Missing column +--echo # + +CREATE TABLE t1 (b INT); +DELIMITER $$; +CREATE PROCEDURE p1 +AS + a t1.a%TYPE; +BEGIN + NULL; +END; +$$ +DELIMITER ;$$ +--error ER_BAD_FIELD_ERROR +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # One %TYPE variable +--echo # + +CREATE TABLE t1 (a INT); +DELIMITER $$; +CREATE PROCEDURE p1 +AS + a t1.a%TYPE; +BEGIN + a:= 123; + SELECT a; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + +--echo # +--echo # Two %TYPE variables, with a truncation warning on assignment +--echo # + +CREATE TABLE t1 (a TINYINT, b INT); +DELIMITER $$; +CREATE PROCEDURE p1 +AS + a t1.a%TYPE; + b t1.b%TYPE; +BEGIN + a:= 200; + b:= 200; + SELECT a, b; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # %TYPE variables for fields with various attributes +--echo # + +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) +); +DELIMITER $$; +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; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # %TYPE + virtual columns +--echo # + +# +# TODO: Changing 'a + 10' to 'a mod 10' make it fail, because +# it's Item::print() returns 'a % 10' which makes grammar conflict +# with cursor attributes + +CREATE TABLE t1 ( + a INT NOT NULL, + b VARCHAR(32), + c INT AS (a + 10) VIRTUAL, + d VARCHAR(5) AS (left(b,5)) PERSISTENT +); +DELIMITER $$; +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; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # %TYPE + the ZEROFILL attribute +--echo # + +CREATE TABLE t1 ( + dz DECIMAL(10,3) ZEROFILL +); +DELIMITER $$; +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; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # Temporary tables shadow real tables for %TYPE purposes +--echo # +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; + +DELIMITER $$; +CREATE PROCEDURE p1 +AS + a t1.a%TYPE:=11; +BEGIN + CREATE TABLE t2 AS SELECT a; +END; +$$ +DELIMITER ;$$ +--echo # +--echo # Should use INT(11) as %TYPE, as in the temporary table +--echo # +CALL p1(); +SHOW CREATE TABLE t2; +SELECT * FROM t2; +DROP TABLE t2; +SELECT * FROM t1; +DROP TEMPORARY TABLE t1; +SELECT * FROM t1; +--echo # +--echo # Should use VARCHAR(10) as %TYPE, as in the real table +--echo # +CALL p1(); +SHOW CREATE TABLE t2; +SELECT * FROM t2; +DROP TABLE t2; +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # t1.a%TYPE searches for "t1" in the current database +--echo # + +CREATE TABLE t1 (a VARCHAR(10)); +CREATE DATABASE test1; +CREATE TABLE test1.t1 (a INT); +DELIMITER $$; +CREATE PROCEDURE p1 +AS + a t1.a%TYPE:=11; +BEGIN + CREATE TABLE test.t2 AS SELECT a; +END; +$$ +DELIMITER ;$$ + +--echo # +--echo # This interprets t1.a%TYPE as VARCHAR(10), as in test.t1.a +--echo # + +USE test; +CALL test.p1(); +SHOW CREATE TABLE test.t2; +DROP TABLE test.t2; + +--echo # +--echo # This interprets t1.a%TYPE as INT, as in test1.t1.a +--echo # + +USE test1; +CALL test.p1(); +SHOW CREATE TABLE test.t2; +DROP TABLE test.t2; + +--echo # +--echo # Error if there is no an active database +--echo # + +DROP DATABASE test1; +--error ER_NO_DB_ERROR +CALL test.p1(); + +USE test; +DROP PROCEDURE p1; +DROP TABLE t1; + + +--echo # +--echo # A reference to a table in a non-existing database +--echo # +DELIMITER $$; +CREATE PROCEDURE p1 +AS + a test1.t1.a%TYPE; +BEGIN + CREATE TABLE t1 AS SELECT a; +END; +$$ +DELIMITER ;$$ +--error ER_NO_SUCH_TABLE +CALL p1; +DROP PROCEDURE p1; + + +--echo # +--echo # A reference to a table in a different database +--echo # +CREATE TABLE t1(a INT); +CREATE DATABASE test1; +CREATE TABLE test1.t1 (a VARCHAR(10)); +DELIMITER $$; +CREATE PROCEDURE p1 +AS + a t1.a%TYPE; + b test1.t1.a%TYPE; +BEGIN + CREATE TABLE t2 AS SELECT a,b; +END; +$$ +DELIMITER ;$$ +CALL p1; +SHOW CREATE TABLE t2; +DROP PROCEDURE p1; +DROP TABLE t2; +DROP DATABASE test1; +DROP TABLE t1; + + +--echo # +--echo # Using a table before it appears in a %TYPE declaration + multiple %TYPE declarations +--echo # +CREATE TABLE t1 (a INT, b VARCHAR(10)); +INSERT INTO t1 (a,b) VALUES (10,'b10'); +DELIMITER $$; +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; +$$ +DELIMITER ;$$ +CALL p1; +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # %TYPE variables + TABLE vs VIEW +--echo # + +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) +); + + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + +--echo # +--echo # TABLE +--echo # +CALL p1('create'); +SHOW CREATE TABLE t2; +--vertical_results +SELECT * FROM t2; +--horizontal_results +DROP TABLE t2; + +--disable_ps_protocol +--enable_metadata +--vertical_results +CALL p1('select'); +--horizontal_results +--disable_metadata +--enable_ps_protocol + +--echo # +--echo # VIEW +--echo # +ALTER TABLE t1 RENAME t0; +CREATE VIEW t1 AS SELECT * FROM t0; + +CALL p1('create'); +SHOW CREATE TABLE t2; +--vertical_results +SELECT * FROM t2; +--horizontal_results +DROP TABLE t2; + +--disable_ps_protocol +--enable_metadata +--vertical_results +CALL p1('select'); +--horizontal_results +--disable_metadata +--enable_ps_protocol + +DROP VIEW t1; +DROP TABLE t0; + +DROP PROCEDURE p1; + +--echo # +--echo # VIEW with subqueries +--echo # +CREATE TABLE t1 (a INT,b INT); +INSERT INTO t1 VALUES (10,1),(20,2),(30,3),(40,4); +SELECT AVG(a) FROM t1; +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; +DELIMITER $$; +CREATE PROCEDURE p1 +AS + a v1.a%TYPE := 10; + b v1.b%TYPE := 1; +BEGIN + SELECT a,b; +END; +$$ +DELIMITER ;$$ +CALL p1; +DROP PROCEDURE p1; +DELIMITER $$; +CREATE FUNCTION f1 RETURN INT +AS + a v1.a%TYPE := 10; + b v1.b%TYPE := 1; +BEGIN + RETURN a+b; +END; +$$ +DELIMITER ;$$ +SELECT f1(); +DROP FUNCTION f1; +DROP VIEW v1; +DROP TABLE t1; + + +--echo # +--echo # %TYPE variables + INFORMATION_SCHEMA +--echo # +DELIMITER $$; +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; +$$ +DELIMITER ;$$ +CALL p1(); +SHOW CREATE TABLE t1; +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # %TYPE + Table structure change +--echo # Data type for both a0 and a1 is chosen in the very beginning +--echo # +DELIMITER $$; +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 +$$ +DELIMITER ;$$ +CREATE TABLE t1 (a INT); +CALL p1; +DROP TABLE t1; +DROP PROCEDURE p1; + + +--echo # +--echo # %TYPE in parameters +--echo # +CREATE TABLE t1 (a VARCHAR(10)); +CREATE DATABASE test1; +CREATE TABLE test1.t1 (b SMALLINT); +DELIMITER $$; +CREATE PROCEDURE p1(a t1.a%TYPE, b test1.t1.b%TYPE) +AS +BEGIN + CREATE TABLE t2 AS SELECT a, b; +END; +$$ +DELIMITER ;$$ +CALL p1('test', 123); +SHOW CREATE TABLE t2; +SELECT * FROM t2; +DROP TABLE t2; +DROP PROCEDURE p1; +DROP TABLE test1.t1; +DROP DATABASE test1; +DROP TABLE t1; + +--echo # +--echo # %TYPE in a stored function variables and arguments +--echo # + +CREATE TABLE t1 (a INT); +SET sql_mode=ORACLE; +DELIMITER $$; +CREATE FUNCTION f1 (prm t1.a%TYPE) RETURN INT +AS + a t1.a%TYPE:= prm; +BEGIN + RETURN a; +END; +$$ +DELIMITER ;$$ +SELECT f1(20); +DROP FUNCTION f1; +DROP TABLE t1; + + +--echo # +--echo # %TYPE in function RETURN clause is not supported yet +--echo # +DELIMITER $$; +--error ER_PARSE_ERROR +CREATE FUNCTION f1 RETURN t1.a%TYPE +AS +BEGIN + RETURN 0; +END; +$$ +DELIMITER ;$$ + + +--echo # +--echo # End of MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations +--echo # + + +--echo # +--echo # MDEV-12089 sql_mode=ORACLE: Understand optional routine name after the END keyword +--echo # + +DELIMITER $$; +CREATE FUNCTION f1 RETURN INT AS +BEGIN + RETURN 10; +END f1; +$$ +DELIMITER ;$$ +DROP FUNCTION f1; + +DELIMITER $$; +CREATE FUNCTION test.f1 RETURN INT AS +BEGIN + RETURN 10; +END test.f1; +$$ +DELIMITER ;$$ +DROP FUNCTION f1; + +DELIMITER $$; +--error ER_END_IDENTIFIER_DOES_NOT_MATCH +CREATE FUNCTION test.f1 RETURN INT AS +BEGIN + RETURN 10; +END test2.f1; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_END_IDENTIFIER_DOES_NOT_MATCH +CREATE FUNCTION test.f1 RETURN INT AS +BEGIN + RETURN 10; +END test.f2; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_END_IDENTIFIER_DOES_NOT_MATCH +CREATE FUNCTION f1 RETURN INT AS +BEGIN + RETURN 10; +END test.f2; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_END_IDENTIFIER_DOES_NOT_MATCH +CREATE FUNCTION f1 RETURN INT AS +BEGIN + RETURN 10; +END test2.f1; +$$ +DELIMITER ;$$ + + +DELIMITER $$; +CREATE PROCEDURE p1 AS +BEGIN + NULL; +END p1; +$$ +DELIMITER ;$$ +DROP PROCEDURE p1; + +DELIMITER $$; +CREATE PROCEDURE test.p1 AS +BEGIN + NULL; +END test.p1; +$$ +DELIMITER ;$$ +DROP PROCEDURE p1; + +DELIMITER $$; +--error ER_END_IDENTIFIER_DOES_NOT_MATCH +CREATE PROCEDURE test.p1 AS +BEGIN + NULL; +END test2.p1; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_END_IDENTIFIER_DOES_NOT_MATCH +CREATE PROCEDURE test.p1 AS +BEGIN + NULL; +END test.p2; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_END_IDENTIFIER_DOES_NOT_MATCH +CREATE PROCEDURE p1 AS +BEGIN + NULL; +END test.p2; +$$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_END_IDENTIFIER_DOES_NOT_MATCH +CREATE PROCEDURE p1 AS +BEGIN + NULL; +END test2.p1; +$$ +DELIMITER ;$$ + +--echo # +--echo # MDEV-12107 sql_mode=ORACLE: Inside routines the CALL keywoard is optional +--echo # +DELIMITER /; +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; +/ +CREATE PROCEDURE p3 AS +BEGIN + p1(10); + p2; + test.p1(10); + test.p2; +END +/ +DELIMITER ;/ +CALL p3; +DROP PROCEDURE p3; +DROP PROCEDURE p2; +DROP PROCEDURE p1; + + +--echo # +--echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +--echo # + +--enable_metadata +--disable_ps_protocol +SELECT SQL%ROWCOUNT; +--enable_ps_protocol +--disable_metadata + +--echo # +--echo # MDEV-13686 EXCEPTION reserved keyword in SQL_MODE=oracle but not in Oracle itself +--echo # +CREATE TABLE t1 (c1 int); +CREATE VIEW v1 AS SELECT c1 exception FROM t1; +SELECT exception FROM v1; +DROP VIEW v1; +DROP TABLE t1; + + + +--echo # +--echo # MDEV-14139 Anchored data types for variables +--echo # + +DELIMITER $$; +--error ER_SP_UNDECLARED_VAR +BEGIN NOT ATOMIC + DECLARE a a%TYPE; +END; +$$ +DELIMITER ;$$ + + +DELIMITER $$; +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; +$$ +DELIMITER ;$$ + + +--echo # +--echo # MDEV-11160 "Incorrect column name" when "CREATE TABLE t1 AS SELECT spvar" +--echo # + + +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; +DELIMITER $$; +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; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; +DROP VIEW v1; +DROP TABLE t1; + + +--echo # +--echo # MDEV-14228 MariaDB crashes with function +--echo # + +CREATE TABLE t1 (c VARCHAR(16), KEY(c)); +INSERT INTO t1 VALUES ('foo'); + +DELIMITER $$; +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 $$ +DELIMITER ;$$ +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT f1(); +DROP FUNCTION f1; + +DELIMITER $$; +CREATE FUNCTION f1() RETURN VARCHAR(16) +IS + v t1%ROWTYPE; +BEGIN + IF v = 'bar' THEN + NULL; + END IF; + RETURN 'qux'; +END $$ +DELIMITER ;$$ +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT f1(); +DROP FUNCTION f1; + +DELIMITER $$; +CREATE FUNCTION f1() RETURN VARCHAR(16) +IS + v ROW(a INT); +BEGIN + IF v = 'bar' THEN + NULL; + END IF; + RETURN 'qux'; +END $$ +DELIMITER ;$$ +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +SELECT f1(); +DROP FUNCTION f1; + +DROP TABLE t1; + + +DELIMITER $$; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +DECLARE + v ROW(a INT); +BEGIN + SELECT v IN ('a','b'); +END $$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +DECLARE + v ROW(a INT); +BEGIN + SELECT 'a' IN (v,'b'); +END $$ +DELIMITER ;$$ + +DELIMITER $$; +--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION +DECLARE + v ROW(a INT); +BEGIN + SELECT 'a' IN ('b',v); +END $$ +DELIMITER ;$$ + +--echo # +--echo # MDEV-17253 Oracle compatibility: The REVERSE key word for FOR loop behaves incorrectly +--echo # + +DELIMITER $$; +DECLARE + totalprice DECIMAL(12,2):=NULL; + loop_start INTEGER := 1; +BEGIN + FOR idx IN REVERSE loop_start..10 LOOP + SELECT idx; + END LOOP; +END; +$$ +DELIMITER ;$$ + + +DELIMITER $$; +CREATE PROCEDURE p1 AS + loop_start INTEGER := 1; +BEGIN + FOR idx IN REVERSE 3..loop_start LOOP + SELECT idx; + END LOOP; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; + + +DELIMITER $$; +CREATE PROCEDURE p1 AS + loop_start INTEGER := 1; +BEGIN + FOR idx IN REVERSE loop_start..3 LOOP + SELECT idx; + END LOOP; +END; +$$ +DELIMITER ;$$ +CALL p1(); +DROP PROCEDURE p1; |