mariadb/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;

enable_prepare_warnings;
DELIMITER /;
CREATE FUNCTION comment RETURN INT COMMENT 'test' AS
BEGIN
  RETURN 1;
END;
/
DELIMITER ;/
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 #


--echo #
--echo # MDEV-21998: Server crashes in st_select_lex::add_table_to_list
--echo # upon mix of KILL and sequences
--echo #

--error ER_SUBQUERIES_NOT_SUPPORTED
KILL ( SELECT 1 ) + LASTVAL(s);
--error ER_SUBQUERIES_NOT_SUPPORTED
KILL LASTVAL(s);

--echo #
--echo # MDEV-23094: Multiple calls to a Stored Procedure from another
--echo # Stored Procedure crashes server
--echo #

create table t1 (id1 int primary key, data1 int);
create table t2 (id2 int primary key, data2 int);

delimiter //;
create procedure p1(id int,dt int) as
begin
  if (exists(select * from t1 where id1 = id and data1 = dt) or
      not exists (select * from t2 where id2 = id and data2 = dt))
  then
      select 1;
  end if;
end //
delimiter ;//

call p1(1,2);
call p1(1,2);

drop procedure p1;

delimiter //;
create procedure p1(id int, dt int) as
begin
case (exists(select * from t1 where id1 = id and data1 = dt) or
      not exists (select * from t2 where id2 = id and data2 = dt))
when 1 then
  select 1;
else
  select 0;
end case;
end //
delimiter ;//

call p1(1,2);
call p1(1,2);

drop procedure p1;

delimiter //;
create procedure p1(id int, dt int) as
begin
declare wcont int default 1;
begin
  while (exists(select * from t1 where id1 = id and data1 = dt) or
        not exists (select * from t2 where id2 = id and data2 = dt)) and wcont
  loop
    select 1;
    set wcont=0;
  end loop;
end;
end //
delimiter ;//

call p1(1,2);
call p1(1,2);

drop procedure p1;

delimiter //;
create procedure p1(id int, dt int) as
begin
declare count int default 1;
begin
  repeat
    select 1;
    set count=count+1;
  until (exists(select * from t1 where id1 = id and data1 = dt) or
        not exists (select * from t2 where id2 = id and data2 = dt)) and
        count < 3
  end repeat;
end;
end //
delimiter ;//

call p1(1,2);
call p1(1,2);

drop procedure p1;

delimiter //;
create procedure p1(id int, dt int) as
begin
for i in 1..(exists(select * from t1 where id1 = id and data1 = dt) or
             not exists (select * from t2 where id2 = id and data2 = dt))
loop
  select 1;
end loop;
end //
delimiter ;//

call p1(1,2);
call p1(1,2);

drop procedure p1;

set sql_mode=ORACLE;
delimiter //;
create or replace procedure p1(id int, dt int) as
begin
  while (1)
  loop
    exit when (exists(select * from t1 where id1 = id and data1 = dt) or
               not exists (select * from t2 where id2 = id and data2 = dt));
  end loop;
end;
//
delimiter ;//

call p1(1,2);
call p1(1,2);

drop procedure p1;

drop table t1,t2;

--echo # End of 10.4 tests

--echo #
--echo # Start of 10.5 tests
--echo #

--echo #
--echo # MDEV-20734 Allow reserved keywords as user defined type names
--echo #

--error ER_UNKNOWN_DATA_TYPE
CREATE TABLE t1 (a DUAL);
--error ER_UNKNOWN_DATA_TYPE
SELECT CAST(1 AS DUAL);


--echo #
--echo # MDEV-20735 Allow non-reserved keywords as user defined type names
--echo #

--error ER_UNKNOWN_DATA_TYPE
CREATE TABLE t1 (a ASCII);
--error ER_UNKNOWN_DATA_TYPE
SELECT CAST(1 AS ASCII);

--error ER_UNKNOWN_DATA_TYPE
CREATE TABLE t1 (a LANGUAGE);
--error ER_UNKNOWN_DATA_TYPE
SELECT CAST(1 AS LANGUAGE);

--error ER_UNKNOWN_DATA_TYPE
CREATE TABLE t1 (a CLOSE);
--error ER_UNKNOWN_DATA_TYPE
SELECT CAST(1 AS CLOSE);

--error ER_UNKNOWN_DATA_TYPE
CREATE TABLE t1 (a NAMES);
--error ER_UNKNOWN_DATA_TYPE
SELECT CAST(1 AS NAMES);

--error ER_UNKNOWN_DATA_TYPE
CREATE TABLE t1 (a END);
--error ER_UNKNOWN_DATA_TYPE
SELECT CAST(1 AS END);

--error ER_UNKNOWN_DATA_TYPE
CREATE TABLE t1 (a GLOBAL);
--error ER_UNKNOWN_DATA_TYPE
SELECT CAST(1 AS GLOBAL);

--error ER_UNKNOWN_DATA_TYPE
CREATE TABLE t1 (a ACTION);
--error ER_UNKNOWN_DATA_TYPE
SELECT CAST(1 AS ACTION);

--error ER_UNKNOWN_DATA_TYPE
CREATE TABLE t1 (a BEGIN);
--error ER_UNKNOWN_DATA_TYPE
SELECT CAST(1 AS BEGIN);

--echo #
--echo # End of 10.5 tests
--echo #


--echo #
--echo # Start of 10.6 tests
--echo #

--echo #
--echo # MDEV-19682 sql_mode="oracle" does not support sysdate
--echo #

# SYSDATE is not deterministic. Let's use LIKE and equality.
# The main point here is only to check that SYSDATE
# gets parsed without parentheses. The actial value is not important.
SELECT sysdate LIKE '____-__-__ __:__:__';
SELECT sysdate = sysdate();
SELECT sysdate = sysdate(0);

--error ER_PARSE_ERROR
CREATE DATABASE sysdate;

--error ER_PARSE_ERROR
CREATE TABLE sysdate (a INT);

--error ER_PARSE_ERROR
CREATE TABLE t1 (sysdate INT);

--error ER_PARSE_ERROR
CREATE TABLE t1 (a sysdate);

DELIMITER $$;
--error ER_PARSE_ERROR
CREATE FUNCTION sysdate RETURN INT AS
BEGIN
  RETURN 1;
END;
$$
DELIMITER ;$$

DELIMITER $$;
--error ER_PARSE_ERROR
CREATE FUNCTION sysdate() RETURN INT AS
BEGIN
  RETURN 1;
END;
$$
DELIMITER ;$$


DELIMITER $$;
--error ER_PARSE_ERROR
DECLARE
  sysdate INT := 10;
BEGIN
  NULL;
END;
$$
DELIMITER ;$$


DELIMITER $$;
--error ER_PARSE_ERROR
BEGIN
<<sysdate>>
  NULL;
END;
$$
DELIMITER ;$$


--echo #
--echo # End of 10.6 tests
--echo #