[MDEV-16244] sql_mode=ORACLE: Some keywords do not work in variable declarations - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3(EOL)
Fix Version/s: 10.3.8
Component/s: Parser
Labels:
- Compatibility

Description

In Oracle, all these keywords can be used for SP variables (in declarations and as INTO targets):

DO
HANDLER
REPAIR
SHUTDOWN
TRUNCATE
CLOSE
COMMIT
OPEN
ROLLBACK
SAVEPOINT
CONTAINS
LANGUAGE
NO
CHARSET
FOLLOWS
PRECEDES

In MariaDB this script:

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;

returns an error:

ERROR 1064 (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 'do INT;

BEGIN

  SELECT do INTO do FROM DUAL;

END' at line 2

In Oracle the same script (without SET sql_mode and DELIMITER commands) works without errors.

Attachments

Issue Links

causes

MDEV-20101 Assertion failure on select @@global.'m2'.replicate_ignore_table;

Closed

relates to

MDEV-10485 "Unreserve" MariaDB reserved keywords that are not reserved in the other databases

Open

MDEV-16258 sql_mode=ORACLE: Keywords from keyword_verb_clause do not work in assignments

Open

MDEV-16259 sql_mode=ORACLE: Keywords from keyword_sp_head do not work in assignments

Open

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2018-05-22 14:32

Alexander Barkov made changes - 2018-05-22 19:19

Field	Original Value	New Value
Description	In Oracle, all these keywords can be used for SP variables (both declarations and assignments): - DO - HANDLER - REPAIR - SHUTDOWN - TRUNCATE {code:sql} DECLARE DO INT; BEGIN DO :=10; END; / {code} {code:sql} DECLARE HANDLER INT; BEGIN HANDLER :=10; END; / {code} {code:sql} DECLARE REPAIR INT; BEGIN REPAIR :=10; END; / {code} {code:sql} DECLARE SHUTDOWN INT; BEGIN SHUTDOWN :=10; END; / {code} {code:sql} DECLARE TRUNCATE INT; BEGIN TRUNCATE :=10; END; / {code} Additionally, these keywords are allowed non-quoted in declarations, not allowed non-quoted in assignments, allowed quoted in assignments: - CLOSE - COMMIT - OPEN - ROLLBACK - SAVEPOINT {code:sql} DECLARE CLOSE INT; BEGIN "CLOSE":=10; END; / {code} {code:sql} DECLARE COMMIT INT; BEGIN "COMMIT":=10; END; / {code} {code:sql} DECLARE OPEN INT; BEGIN "OPEN":=10; END; / {code} {code:sql} DECLARE ROLLBACK INT; BEGIN "ROLLBACK":=10; END; / {code} {code:sql} DECLARE SAVEPOINT INT; BEGIN "SAVEPOINT":=10; END; / {code} MariaDB does not allow to use these keywords in declarations/assignments when running in sql_mode=ORACLE. It should be fixed to be compatible.	In Oracle, all these keywords can be used for SP variables (in declarations and as INTO targets): - DO - HANDLER - REPAIR - SHUTDOWN - TRUNCATE - CLOSE - COMMIT - OPEN - ROLLBACK - SAVEPOINT - CONTAINS - LANGUAGE - NO - CHARSET - FOLLOWS - PRECEDES In MariaDB this script: {code:sql} 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; / {code} returns an error: {noformat} ERROR 1064 (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 'do INT; BEGIN SELECT do INTO do FROM DUAL; END' at line 2 {noformat} In Oracle the same script (without SET sql_mode and DELIMITER commands) works without errors. Also, In Oracle, these keywords can be used in SP assignments: - DO - HANDLER - REPAIR - SHUTDOWN - TRUNCATE {code:sql} DECLARE DO INT; BEGIN DO :=10; END; / {code} {code:sql} DECLARE HANDLER INT; BEGIN HANDLER :=10; END; / {code} {code:sql} DECLARE REPAIR INT; BEGIN REPAIR :=10; END; / {code} {code:sql} DECLARE SHUTDOWN INT; BEGIN SHUTDOWN :=10; END; / {code} {code:sql} DECLARE TRUNCATE INT; BEGIN TRUNCATE :=10; END; / {code} In MariaDB the same script returns a syntax error. MariaDB should be fixed to be more compatible.

Alexander Barkov made changes - 2018-05-22 19:22

Description

In Oracle, all these keywords can be used for SP variables (in declarations and as INTO targets):
- DO
- HANDLER
- REPAIR
- SHUTDOWN
- TRUNCATE
- CLOSE
- COMMIT
- OPEN
- ROLLBACK
- SAVEPOINT
- CONTAINS
- LANGUAGE
- NO
- CHARSET
- FOLLOWS
- PRECEDES

In MariaDB this script:
{code:sql}
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;
/
{code}

returns an error:
{noformat}
ERROR 1064 (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 'do INT;
BEGIN
  SELECT do INTO do FROM DUAL;
END' at line 2
{noformat}
In Oracle the same script (without SET sql_mode and DELIMITER commands) works without errors.

Also, In Oracle, these keywords can be used in SP assignments:
- DO
- HANDLER
- REPAIR
- SHUTDOWN
- TRUNCATE

{code:sql}
DECLARE
  DO INT;
BEGIN
  DO :=10;
END;
/
{code}

{code:sql}
DECLARE
  HANDLER INT;
BEGIN
  HANDLER :=10;
END;
/
{code}

{code:sql}
DECLARE
  REPAIR INT;
BEGIN
  REPAIR :=10;
END;
/
{code}

{code:sql}
DECLARE
  SHUTDOWN INT;
BEGIN
  SHUTDOWN :=10;
END;
/
{code}

{code:sql}
DECLARE
  TRUNCATE INT;
BEGIN
  TRUNCATE :=10;
END;
/
{code}
In MariaDB the same script returns a syntax error.

MariaDB should be fixed to be more compatible.

Alexander Barkov made changes - 2018-05-22 19:23

Comment

[ Some more keywords that work in Oracle both in declarations and assignments:
{code:sql}
DECLARE
  CONTAINS INT;
BEGIN
  CONTAINS:=10;
END;
/
{code}

{code:sql}
DECLARE
  LANGUAGE INT;
BEGIN
  LANGUAGE:=10;
END;
/
{code}

{code:sql}
DECLARE
  NO INT;
BEGIN
  NO:=10;
END;
/
{code}

{code:sql}
DECLARE
  CHARSET INT;
BEGIN
  CHARSET:=10;
END;
/
{code}

{code:sql}
DECLARE
  FOLLOWS INT;
BEGIN
  FOLLOWS:=10;
END;
/
{code}

{code:sql}
DECLARE
  PRECEDES INT;
BEGIN
  PRECEDES:=10;
END;
/
{code}
]

Alexander Barkov made changes - 2018-05-23 08:19

issue.field.resolutiondate

2018-05-23 08:19:54.0

2018-05-23 08:19:54.514

Alexander Barkov made changes - 2018-05-23 08:19

Fix Version/s		10.3.7 [ 23005 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Alexander Barkov made changes - 2018-05-23 08:28

Link

This issue relates to MDEV-16258 [ MDEV-16258 ]

Alexander Barkov made changes - 2018-05-23 08:28

Link

This issue relates to MDEV-10485 [ MDEV-10485 ]

Alexander Barkov made changes - 2018-05-23 08:33

Link

This issue relates to MDEV-16259 [ MDEV-16259 ]

Sergei Golubchik made changes - 2018-05-24 17:48

Fix Version/s		10.3.8 [ 23113 ]
Fix Version/s	10.3.7 [ 23005 ]

Marko Mäkelä made changes - 2019-07-19 08:14

Link

This issue causes ~~MDEV-20101~~ [ ~~MDEV-20101~~ ]

Sergei Golubchik made changes - 2021-12-06 21:47

Workflow

MariaDB v3 [ 87385 ]

MariaDB v4 [ 154401 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2018-05-22 14:32

Updated:: 2019-07-19 08:14

Resolved:: 2018-05-23 08:19

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration