[MDEV-26916] Clarify / Update Reserved Word List Created: 2021-10-27  Updated: 2021-12-20

Status: Open
Project: MariaDB Server
Component/s: Documentation
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Kevin Kirsche Assignee: Ian Gilfillan
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Good morning,

Sorry for not creating a more substantial issue, but I hope this will be sufficient.

In relationship to the following issue, we were working on updating the list of reserved words but it seems as though the website documentation specifically regarding the oracle mode keywords is incorrect, incomplete, or at the very least confusing.

https://github.com/sqlalchemy/sqlalchemy/issues/7167#issuecomment-952266131

I have replicated the behavior zzzeek shows in his comment. Per https://mariadb.com/kb/en/reserved-words/ The word system is not a reserved word in ORACLE MODE as the documentation uses "<= 10.3.6" meaning only versions that are less than 10.3.6 have this word as reserved.

As a result, the following should not trigger an error, but does.

 
 
❯ mariadb
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 5
 
Server version: 10.6.4-MariaDB Homebrew
 
 
 
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 [(none)]> USE test;
 
Database changed
 
MariaDB [test]> CREATE TABLE foo (id integer);
 
Query OK, 0 rows affected (0.111 sec)
 
 
 
MariaDB [test]> ALTER TABLE foo ADD system VARCHAR(64);
 
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 'VARCHAR(64)' at line 1
 
MariaDB [test]> SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;
 
+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+
 
| @@SQL_MODE                                                                                | @@GLOBAL.SQL_MODE                                                                         |
 
+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+
 
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
 
+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]>

Details:

  • Operating System: macOS Monterey Version 12.0.1
  • Hardware: MacBook Pro (Retina, 13-inch, Early 2015)
  • MariaDB Version 10.6.4
  • @@log_error:

 
 
211027 09:06:55 mysqld_safe Starting mariadbd daemon with databases from /usr/local/var/mysql
 
2021-10-27  9:06:56 0 [Note] /usr/local/opt/mariadb/bin/mariadbd (server 10.6.4-MariaDB) starting as process 97622 ...
 
2021-10-27  9:06:56 0 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
 
2021-10-27  9:06:56 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
 
2021-10-27  9:06:56 0 [Note] InnoDB: Number of pools: 1
 
2021-10-27  9:06:56 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
 
2021-10-27  9:06:56 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
 
2021-10-27  9:06:56 0 [Note] InnoDB: Completed initialization of buffer pool
 
2021-10-27  9:06:56 0 [Note] InnoDB: 128 rollback segments are active.
 
2021-10-27  9:06:56 0 [Note] InnoDB: Creating shared tablespace for temporary tables
 
2021-10-27  9:06:56 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
 
2021-10-27  9:06:56 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
 
2021-10-27  9:06:56 0 [Note] InnoDB: 10.6.4 started; log sequence number 42161; transaction id 14
 
2021-10-27  9:06:56 0 [Note] Plugin 'FEEDBACK' is disabled.
 
2021-10-27  9:06:56 0 [Note] InnoDB: Loading buffer pool(s) from /usr/local/var/mysql/ib_buffer_pool
 
2021-10-27  9:06:56 0 [Note] InnoDB: Buffer pool(s) load completed at 211027  9:06:56
 
2021-10-27  9:06:56 0 [Note] Server socket created on IP: '::'.
 
2021-10-27  9:06:56 0 [Note] Server socket created on IP: '0.0.0.0'.
 
2021-10-27  9:06:56 0 [Note] /usr/local/opt/mariadb/bin/mariadbd: ready for connections.
 
Version: '10.6.4-MariaDB'  socket: '/tmp/mysql.sock'  port: 3306  Homebrew

  • Defaults:

❯ mysqld --print-defaults
 
mysqld would have been started with the following arguments:
 
 

As such, I believe that https://mariadb.com/kb/en/reserved-words/ is incorrect.

 Comments   
Comment by Ian Gilfillan [ 2021-11-15 ]

This is not actually a reserved word. The problem comes from the parser, in this context only, expecting the word "VERSIONING" to follow, as per https://mariadb.com/kb/en/system-versioned-tables. For clarity however, I'll add a note on the reserved words page.

Comment by Ian Gilfillan [ 2021-11-15 ]

serg should SYSTEM actually be added as a reserved word?

Comment by Sergei Golubchik [ 2021-11-15 ]

A keyword can be reserved or not reserved depending on the context. We have many different lists that apply in various contexts. Obvisouly, we cannot put all that in the manual. So the question is, what should the "reserved word list" in the manual contain. Every keyword that is reserved in at least one context? Keywords that are reserved in specific context, like in CREATE/ALTER? Something else?

Comment by Ian Gilfillan [ 2021-11-15 ]

Everything that a user may need to know, so everything that needs to be quoted in some context, is a good goal. You say we "obviously" can't put that all in the manual. Why not? How deep does the list of contexts go? And secondly, is the inconsistency in a case like SYSTEM desirable? If a user can create a table with a field named SYSTEM, but cannot ALTER a table to add this field, wouldn't it be better to simply mark it as a reserved word that needs quoting in all cases, to avoid confusion?

Comment by Sergei Golubchik [ 2021-11-18 ]

The logic was "only require a user to quote a keyword in the context where a parser cannot figure it out otherwise". And this rabbit hole goes pretty deep. Here're the list of rule names from the parser:

/* Keywords which we allow as table aliases. */
 
keyword_table_alias:
 
/* Keyword that we allow for identifiers (except SP labels) */
 
keyword_ident:
 
keyword_sysvar_name:
 
/* can be used in SET as a variable name */
 
keyword_set_usual_case:
 
/* can be used as a type name in CREATE TABLE and CAST */
 
non_reserved_keyword_udt:
 
/*
 
  Keywords that we allow in Oracle-style direct assignments:
 
    xxx := 10;
 
  but do not allow in labels in the default sql_mode:
 
    label:
 
      stmt1;
 
      stmt2;
 
*/
 
keyword_sp_var_not_label:
 
/*
 
  Keywords that can start optional clauses in SP or trigger declarations
 
  Allowed as identifiers (e.g. table, column names),
 
  but:
 
  - not allowed as SP label names
 
  - not allowed as variable names in Oracle-style assignments:
 
    xxx := 10;
 
 
 
  If we allowed these variables in assignments, there would be conflicts
 
  with SP characteristics, or verb clauses, or compound statements, e.g.:
 
    CREATE PROCEDURE p1 LANGUAGE ...
 
  would be either:
 
    CREATE PROCEDURE p1 LANGUAGE SQL BEGIN END;
 
  or
 
    CREATE PROCEDURE p1 LANGUAGE:=10;
 
 
 
  Note, these variables can still be assigned using quoted identifiers:
 
    `do`:= 10;
 
    "do":= 10; (when ANSI_QUOTES)
 
  or using a SET statement:
 
    SET do= 10;
 
 
 
  Note, some of these keywords are reserved keywords in Oracle.
 
  In case if heavy grammar conflicts are found in the future,
 
  we'll possibly need to make them reserved for sql_mode=ORACLE.
 
*/
 
keyword_sp_head:
 
/*
 
  Keywords that start a statement.
 
  Generally allowed as identifiers (e.g. table, column names)
 
  - not allowed as SP label names
 
  - not allowed as variable names in Oracle-style assignments:
 
    xxx:=10
 
*/
 
keyword_verb_clause:
 
keyword_set_special_case:
 
keyword_sysvar_type:
 
/*
 
  These keywords are generally allowed as identifiers,
 
  but not allowed as non-delimited SP variable names in sql_mode=ORACLE.
 
*/
 
keyword_data_type:
 
keyword_cast_type:
 
/*
 
  These keywords are fine for both SP variable names and SP labels.
 
*/
 
keyword_sp_var_and_label:
 
reserved_keyword_udt_not_param_type:
 
reserved_keyword_udt:
 
reserved_keyword_udt_param_type:
 
// Keywords that start an SP block section
 
keyword_sp_block_section:
 
// Keywords that we allow for labels in SPs.
 
// Should not include keywords that start a statement or SP characteristics.
 
keyword_label:
 
keyword_directly_assignable:
 
keyword_sp_decl:

Some of the rules a directly used in the grammar, others only help to define other rules. Any given keyword is likely to be present in many of these lists. A "completely reserved" keyword is not present in any of them, otherwise it's reserved in some contexts and not reserved in others.

Generated at Thu Feb 08 09:48:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.