Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13686

EXCEPTION reserved keyword in SQL_MODE=oracle but not in Oracle itself

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 10.3.1
    • Fix Version/s: 10.3.2
    • Component/s: Parser
    • 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.
      

        Attachments

          Activity

            People

            • Assignee:
              bar Alexander Barkov
              Reporter:
              karlsson Anders Karlsson
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: