[MDEV-13686] EXCEPTION reserved keyword in SQL_MODE=oracle but not in Oracle itself Created: 2017-08-31  Updated: 2017-09-14  Resolved: 2017-09-14

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.3.1
Fix Version/s: 10.3.2

Type: Bug Priority: Minor
Reporter: Anders Karlsson Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None


 Description   

"Exception" is not a reserved word in MariaDB usually, but it is when SQL_MODE is set to "Oracle". This is odd, as "exception" is not a SQL keyword in Oracle itself (tested with Oracle 11.2). When sql_mode is not set to Oracle, this is not the case.

$ mysql -u root test
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 15
 
Server version: 10.3.1-MariaDB MariaDB Server
 
 
 
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [test]> create table tt1(c1 int);
 
Query OK, 0 rows affected (0.021 sec)
 
 
 
MariaDB [test]> create view v1 as select c1 exception from tt1;
 
Query OK, 0 rows affected (0.003 sec)
 
 
 
MariaDB [test]> drop view v1;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> set sql_mode=oracle;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> create view v1 as select c1 exception from tt1;
 
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 'exception from tt1' at line 1

In SQLPlus:

$ sqlplus anders/XXXXXXXX
 
 
 
SQL*Plus: Release 11.2.0.2.0 Production on Thu Aug 31 10:28:27 2017
 
 
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
 
 
 
 
Connected to:
 
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
 
 
 
SQL> create table tt1(c1 int);
 
 
 
Table created.
 
 
 
SQL> create view v1 as select c1 exception from tt1;
 
 
 
View created.



 Comments   
Comment by Elena Stepanova [ 2017-08-31 ]

I couldn't find it in the standard (draft 2011) either.

It was apparently added in the scope of MDEV-10411, somewhere here:

commit 8fdc1f0147cd77d3191139247a4c54d1f1cbe189
 
Author: Alexander Barkov <bar@mariadb.org>
 
Date:   Thu Aug 11 14:12:14 2016 +0400
 
 
 
    MDEV-10411 Providing compatibility for basic PL/SQL constructs
 
    
    Part 9: EXCEPTION handlers
 
    
    - Adding exception handler syntax:
 
      WHEN exception_name THEN statement
 
    - Adding EXCEPTION section intoi the top BEGIN..END SP block.
 
      Note, currently EXCEPTION goes in the beginning of the top BEGIN..END
 
      SP block.
 
    
    TODO:
 
    - add EXCEPTION section into inner blocks
 
    - move EXCEPTION to the end of the block

Comment by Alexander Barkov [ 2017-09-14 ]

Pushed into bb-10.2-ext and 10.3.

Generated at Thu Feb 08 08:07:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.