path: root/mysql-test/suite/compat/oracle/t/parser.test

SET sql_mode=ORACLE;

--echo #
--echo # MDEV-15620 Crash when using "SET @@NEW.a=expr" inside a trigger
--echo #

CREATE TABLE t1 (a INT);
--error ER_UNKNOWN_STRUCTURED_VARIABLE
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @@NEW.a=0;
DROP TABLE t1;

--echo #
--echo # MDEV-15615 Unexpected syntax error instead of "Unknown system variable" inside an SP
--echo #

DELIMITER $$;
--error ER_UNKNOWN_SYSTEM_VARIABLE
DECLARE
  a INT;
BEGIN
  SET GLOBAL a=10;
END;
$$
DELIMITER ;$$


--echo #
--echo # MDEV-16202 Latest changes made erroneously some keywords reserved in sql_mode=ORACLE
--echo #


DELIMITER $$;
CREATE PROCEDURE p1(name VARCHAR(64), pattern TEXT) AS
  query TEXT DEFAULT REPLACE(pattern, 'name', name);
BEGIN
  SELECT query AS '';
  EXECUTE IMMEDIATE query;
EXCEPTION
  WHEN OTHERS THEN
  BEGIN
    SHOW ERRORS;
  END;
END;
$$

CREATE PROCEDURE p2(name VARCHAR(64)) AS
BEGIN
  CALL p1(name, 'DECLARE name INT; BEGIN name:=10; SELECT name; END');
  EXECUTE IMMEDIATE REPLACE('CREATE TABLE t1 (name INT)', 'name', name);
  CALL p1(name, 'SELECT name FROM t1');
  CALL p1(name, 'SELECT name ''alias'' FROM t1');
  CALL p1(name, 'SELECT name()');
  CALL p1(name, 'SELECT name.name()');
  CALL p1(name, 'SELECT name DATE FROM t1');
  CALL p1(name, 'SELECT name HISTORY FROM t1');
  CALL p1(name, 'SELECT name NEXT FROM t1');
  CALL p1(name, 'SELECT name PERIOD FROM t1');
  CALL p1(name, 'SELECT name PREVIOUS FROM t1');
  CALL p1(name, 'SELECT name SYSTEM FROM t1');
  CALL p1(name, 'SELECT name SYSTEM_TIME FROM t1');
  CALL p1(name, 'SELECT name TIME FROM t1');
  CALL p1(name, 'SELECT name TIMESTAMP FROM t1');
  CALL p1(name, 'SELECT name TRANSACTION FROM t1');
  CALL p1(name, 'SELECT name VALUE FROM t1');
  CALL p1(name, 'SELECT name VERSIONING FROM t1');
  CALL p1(name, 'SELECT name WITHOUT FROM t1');
  DROP TABLE t1;
END;
$$
DELIMITER ;$$

--disable_column_names
CALL p2('date');
CALL p2('history');
CALL p2('next');
CALL p2('period');
CALL p2('previous');
CALL p2('system');
CALL p2('system_time');
CALL p2('time');
CALL p2('timestamp');
CALL p2('transaction');
CALL p2('value');
CALL p2('versioning');
CALL p2('without');
--enable_column_names

DROP PROCEDURE p2;
DROP PROCEDURE p1;


--echo #
--echo # MDEV-16244 sql_mode=ORACLE: Some keywords do not work in variable declarations
--echo #

SET sql_mode=ORACLE;
DELIMITER /;

DECLARE
  do INT;
BEGIN
  SELECT do INTO do FROM DUAL;
END;
/

DECLARE
  handler INT;
BEGIN
  SELECT handler INTO handler FROM DUAL;
END;
/

DECLARE
  repair INT;
BEGIN
  SELECT repair INTO repair FROM DUAL;
END;
/

DECLARE
  shutdown INT;
BEGIN
  SELECT shutdown INTO shutdown FROM DUAL;
END;
/

DECLARE
  truncate INT;
BEGIN
  SELECT truncate INTO truncate FROM DUAL;
END;
/

DECLARE
  close INT;
BEGIN
  SELECT close INTO close FROM DUAL;
END;
/

DECLARE
  commit INT;
BEGIN
  SELECT commit INTO commit FROM DUAL;
END;
/

DECLARE
  open INT;
BEGIN
  SELECT open INTO open FROM DUAL;
END;
/

DECLARE
  rollback INT;
BEGIN
  SELECT rollback INTO rollback FROM DUAL;
END;
/

DECLARE
  savepoint INT;
BEGIN
  SELECT savepoint INTO savepoint FROM DUAL;
END;
/

DECLARE
  contains INT;
BEGIN
  SELECT contains INTO contains FROM DUAL;
END;
/

DECLARE
  language INT;
BEGIN
  SELECT language INTO language FROM DUAL;
END;
/

DECLARE
  no INT;
BEGIN
  SELECT no INTO no FROM DUAL;
END;
/

DECLARE
  charset INT;
BEGIN
  SELECT charset INTO charset FROM DUAL;
END;
/
DECLARE
  follows INT;
BEGIN
  SELECT follows INTO follows FROM DUAL;
END;
/

DECLARE
  precedes INT;
BEGIN
  SELECT precedes INTO precedes FROM DUAL;
END;
/

DELIMITER ;/


--echo #
--echo # MDEV-16464 Oracle Comp.: Sql-Error on "SELECT name, comment FROM mysql.proc"
--echo #

SET sql_mode=ORACLE;
SELECT name, comment FROM mysql.proc WHERE db='test';

CREATE TABLE comment (comment INT);
SELECT comment FROM comment;
SELECT comment comment FROM comment comment;
SELECT comment AS comment FROM comment AS comment;
DROP TABLE comment;

DELIMITER /;
DECLARE
  comment INT;
BEGIN
  SELECT comment INTO comment FROM DUAL;
END;
/
DELIMITER ;/

DELIMITER /;
CREATE PROCEDURE comment COMMENT 'test' AS
BEGIN
  SELECT 1;
END;
/
BEGIN
  comment;
END;
/
DELIMITER ;/
CALL comment();
CALL comment;
DROP PROCEDURE comment;

DELIMITER /;
CREATE FUNCTION comment RETURN INT COMMENT 'test' AS
BEGIN
  RETURN 1;
END;
/
DELIMITER ;/
enable_prepare_warnings;
SELECT test.comment() FROM DUAL;
disable_prepare_warnings;
DROP FUNCTION comment;


--echo #
--echo # MDEV-17660 sql_mode=ORACLE: Some keywords do not work as label names: history, system, versioning, without
--echo #

DELIMITER /;
BEGIN
<<date_format>>
  NULL;
END;
/
DELIMITER ;/


DELIMITER /;
BEGIN
<<decode>>
  NULL;
END;
/
DELIMITER ;/


DELIMITER /;
BEGIN
<<history>>
  NULL;
END;
/
DELIMITER ;/


DELIMITER /;
BEGIN
<<system>>
  NULL;
END;
/
DELIMITER ;/


DELIMITER /;
BEGIN
<<versioning>>
  NULL;
END;
/
DELIMITER ;/


DELIMITER /;
BEGIN
<<without>>
  NULL;
END;
/
DELIMITER ;/


--echo #
--echo # MDEV-17666 sql_mode=ORACLE: Keyword ELSEIF should not be reserved
--echo #

DELIMITER /;
DECLARE
  ELSEIF INT;
BEGIN
  ELSEIF:=1;
END;
/
DELIMITER ;/

DELIMITER /;
BEGIN
<<ELSEIF>>
  NULL;
END;
/
DELIMITER ;/


--echo #
--echo # MDEV-17693 Shift/reduce conflicts for NAMES,ROLE,PASSWORD in the option_value_no_option_type grammar
--echo #

CREATE TABLE names (names INT);
SELECT names FROM names AS names;
DROP TABLE names;

CREATE TABLE password (password INT);
SELECT password FROM password AS password;
DROP TABLE password;

CREATE TABLE role (role INT);
SELECT role FROM role AS role;
DROP TABLE role;

DELIMITER $$;
DECLARE
  names    VARCHAR(32) DEFAULT '[names]';
  password VARCHAR(32) DEFAULT '[password]';
  role     VARCHAR(32) DEFAULT '[role]';
BEGIN
<<names>>
   SELECT names;
<<password>>
   SELECT password;
<<role>>
  SELECT role;
END;
$$
DELIMITER ;$$

DELIMITER $$;
--error ER_SP_BAD_VAR_SHADOW
DECLARE
  names VARCHAR(32);
BEGIN
  SET names='[names]';
END;
$$
DELIMITER ;$$

DELIMITER $$;
--error ER_SP_BAD_VAR_SHADOW
DECLARE
  password VARCHAR(32);
BEGIN
  SET password='[password]';
END;
$$
DELIMITER ;$$

DELIMITER $$;
DECLARE
  role VARCHAR(32);
BEGIN
  SET role='[role]';
END;
$$
DELIMITER ;$$

--error ER_UNKNOWN_SYSTEM_VARIABLE
SELECT @@GLOBAL.names;
--error ER_UNKNOWN_SYSTEM_VARIABLE
SELECT @@GLOBAL.password;
--error ER_UNKNOWN_SYSTEM_VARIABLE
SELECT @@GLOBAL.role;


--echo #
--echo # MDEV-22822 sql_mode="oracle" cannot declare without variable errors
--echo #
--echo # It's OK to have no declarations between DECLARE and BEGIN.
--echo #

DELIMITER //;
BEGIN
  DECLARE
  BEGIN
    NULL;
  END;
EXCEPTION
WHEN OTHERS THEN
    NULL;
END;
//
DELIMITER ;//


DELIMITER //;
DECLARE
BEGIN
  NULL;
EXCEPTION
WHEN OTHERS THEN
  NULL;
END;
//
DELIMITER ;//


DELIMITER //;
BEGIN
<<lab>>
  DECLARE
  BEGIN
    NULL;
  END;
EXCEPTION
WHEN OTHERS THEN
    NULL;
END;
//
DELIMITER ;//


--echo #
--echo # End of 10.3 tests
--echo #