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

Document OTHERS as reserved word in sql_mode=ORACLE in MariaDB 10.3+

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: N/A
    • Component/s: Documentation
    • Labels:
      None

      Description

      OTHERS can not be used as unquoted identifier in MariaDB 10.3+ when sql_mode is set to ORACLE:

      openxs@fc29 maria10.3]$ bin/mysql -uroot --socket=/tmp/mariadb.sock test
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Welcome to the MariaDB monitor. Commands end with ; or \g.
      Your MariaDB connection id is 8
      Server version: 10.3.17-MariaDB Source distribution
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [test]> select @@sql_mode;
      +-------------------------------------------------------------------------------------------+
      | @@sql_mode |
      +-------------------------------------------------------------------------------------------+
      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
      +-------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> create table tot(id int, others int);
      Query OK, 0 rows affected (0.184 sec)
       
      MariaDB [test]> select id, others from tot;
      Empty set (0.001 sec)
       
      MariaDB [test]> set session sql_mode=oracle;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [test]> select id, others from tot;
      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 'others from tot' at line 1
       
      MariaDB [test]> select @@sql_mode;
      +----------------------------------------------------------------------------------------------------------------------------------------------+
      | @@sql_mode |
      +----------------------------------------------------------------------------------------------------------------------------------------------+
      | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT |
      +----------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.009 sec)
       
      MariaDB [test]> insert into tot values(1,1);
      Query OK, 1 row affected (0.026 sec)
       
      MariaDB [test]> select id, "others" from tot;
      +------+--------+
      | id | others |
      +------+--------+
      | 1 | 1 |
      +------+--------+
      1 row in set (0.000 sec)
      

      This is NOT documented in the KB (see https://mariadb.com/kb/en/library/reserved-words/#oracle-mode).

      So, intil OTHERS is removed from the list of reserved words (see MDEV-10485) we should document it as being reserved.

        Attachments

          Activity

            People

            Assignee:
            greenman Ian Gilfillan
            Reporter:
            valerii Valerii Kravchuk
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration