PL/SQL parser - Phase 2 (MDEV-10764)

[MDEV-10485] "Unreserve" MariaDB reserved keywords that are not reserved in the other databases Created: 2016-08-03  Updated: 2023-10-20

Status: Open
Project: MariaDB Server
Component/s: Parser
Affects Version/s: None
Fix Version/s: None

Type: Technical task Priority: Major
Reporter: Alexander Barkov Assignee: Unassigned
Resolution: Unresolved Votes: 3
Labels: None

Attachments: Microsoft Word keywords.xlsx     File keywords_alias_mdb.sql     File keywords_alias_ora.sql     File keywords_label_mdb.sql     File keywords_label_ora.sql     File keywords_var_mdb.sql     File keywords_var_ora.sql    
Issue Links:
PartOf
is part of MDEV-11070 Providing compatibility to other data... Open
Relates
relates to MDEV-12518 Unify sql_yacc.yy and sql_yacc_ora.yy Closed
relates to MDEV-16244 sql_mode=ORACLE: Some keywords do not... Closed
relates to MDEV-16258 sql_mode=ORACLE: Keywords from keywor... Open
relates to MDEV-16259 sql_mode=ORACLE: Keywords from keywor... Open

 Description   

MariaDB has a number of unnecessarily reserved keywords that are not reserved neither in the SQL standard nor in the other databases. This makes migration to MariaDB harder, as the DBA has to rename tables, columns and other objects.

LOCALTIMESTAMP is an example.

This query:

CREATE TABLE localtimestamp (a INT);

works without problems in Oracle, but returns an error in MariaDB:

ERROR 1064 (42000): You have an error in your SQL syntax...near 'localtimestamp ...

Under terms of this tasks, we'll find all keywords that are reserved in MariaDB but not reserved in the other databases and try to "unreserve" those that do not really have to be reserved.

 Comments   
Comment by Alexander Barkov [ 2016-08-03 ]

The list of MariaDB reserved keywords that are not reserved in SQL-2011:

+-------------------------------+-------------------+
 
| keyword                       | type              |
 
+-------------------------------+-------------------+
 
| ACCESSIBLE                    |                   |
 
| ADD                           |                   |
 
| ANALYZE                       | VerbClause        |
 
| ASC                           |                   |
 
| BEFORE                        |                   |
 
| CASCADE                       |                   |
 
| CHANGE                        |                   |
 
| CONTINUE                      |                   |
 
| DATABASE                      |                   |
 
| DATABASES                     |                   |
 
| DAY_HOUR                      | TermporalInterval |
 
| DAY_MICROSECOND               | TermporalInterval |
 
| DAY_MINUTE                    | TermporalInterval |
 
| DAY_SECOND                    | TermporalInterval |
 
| DELAYED                       |                   |
 
| DESC                          |                   |
 
| DISTINCTROW                   |                   |
 
| DIV                           | Operator          |
 
| DO_DOMAIN_IDS                 |                   |
 
| DUAL                          |                   |
 
| ELSEIF                        |                   |
 
| ENCLOSED                      |                   |
 
| ESCAPED                       |                   |
 
| EXCLUDE                       |                   |
 
| EXIT                          |                   |
 
| EXPLAIN                       | VerbClause        |
 
| FLOAT4                        | DataType          |
 
| FLOAT8                        | DataType          |
 
| FOLLOWING                     |                   |
 
| FORCE                         |                   |
 
| FULLTEXT                      |                   |
 
| HIGH_PRIORITY                 | QueryOption       |
 
| HOUR_MICROSECOND              | TermporalInterval |
 
| HOUR_MINUTE                   | TermporalInterval |
 
| HOUR_SECOND                   | TermporalInterval |
 
| IGNORE                        |                   |
 
| IGNORE_DOMAIN_IDS             |                   |
 
| INDEX                         |                   |
 
| INFILE                        |                   |
 
| INT1                          | DataType          |
 
| INT2                          | DataType          |
 
| INT3                          | DataType          |
 
| INT4                          | DataType          |
 
| INT8                          | DataType          |
 
| ITERATE                       |                   |
 
| KEY                           |                   |
 
| KEYS                          |                   |
 
| KILL                          | VerbClause        |
 
| LEAVE                         |                   |
 
| LIMIT                         |                   |
 
| LINEAR                        |                   |
 
| LINES                         |                   |
 
| LOAD                          | VerbClause        |
 
| LOCK                          | VerbClause        |
 
| LONG                          |                   |
 
| LONGBLOB                      | DataType          |
 
| LONGTEXT                      | DataType          |
 
| LOOP                          |                   |
 
| LOW_PRIORITY                  |                   |
 
| MASTER_SSL_VERIFY_SERVER_CERT |                   |
 
| MAXVALUE                      |                   |
 
| MEDIUMBLOB                    | DataType          |
 
| MEDIUMINT                     | DataType          |
 
| MEDIUMTEXT                    | DataType          |
 
| MIDDLEINT                     | DataType          |
 
| MINUTE_MICROSECOND            | TermporalInterval |
 
| MINUTE_SECOND                 | TermporalInterval |
 
| NO_WRITE_TO_BINLOG            |                   |
 
| OPTIMIZE                      | VerbClause        |
 
| OPTIONALLY                    |                   |
 
| OTHERS                        |                   |
 
| OUTFILE                       |                   |
 
| PAGE_CHECKSUM                 |                   |
 
| PARSE_VCOL_EXPR               |                   |
 
| PRECEDING                     |                   |
 
| PURGE                         | VerbClause        |
 
| READ                          |                   |
 
| READ_WRITE                    |                   |
 
| REF_SYSTEM_ID                 |                   |
 
| REGEXP                        | Operator          |
 
| RENAME                        | VerbClause        |
 
| REPEAT                        |                   |
 
| REPLACE                       | VerbClause        |
 
| REQUIRE                       |                   |
 
| RESIGNAL                      | VerbClause        |
 
| RESTRICT                      |                   |
 
| RETURNING                     |                   |
 
| RLIKE                         | Operator          |
 
| SCHEMA                        |                   |
 
| SCHEMAS                       |                   |
 
| SECOND_MICROSECOND            | TermporalInterval |
 
| SEPARATOR                     |                   |
 
| SHOW                          | VerbClause        |
 
| SIGNAL                        | VerbClause        |
 
| SPATIAL                       |                   |
 
| SQL_BIG_RESULT                | QueryOption       |
 
| SQL_CALC_FOUND_ROWS           | QueryOption       |
 
| SQL_SMALL_RESULT              | QueryOption       |
 
| SSL                           |                   |
 
| STARTING                      |                   |
 
| STATS_AUTO_RECALC             |                   |
 
| STATS_PERSISTENT              |                   |
 
| STATS_SAMPLE_PAGES            |                   |
 
| STRAIGHT_JOIN                 | QueryOption       |
 
| TERMINATED                    |                   |
 
| TIES                          |                   |
 
| TINYBLOB                      | DataType          |
 
| TINYINT                       | DataType          |
 
| TINYTEXT                      | DataType          |
 
| UNBOUNDED                     |                   |
 
| UNDO                          |                   |
 
| UNLOCK                        | VerbClause        |
 
| UNSIGNED                      |                   |
 
| USAGE                         |                   |
 
| USE                           | VerbClause        |
 
| UTC_DATE                      | Function          |
 
| UTC_TIME                      | Function          |
 
| UTC_TIMESTAMP                 | Function          |
 
| VARCHARACTER                  | DataType          |
 
| WHILE                         |                   |
 
| WRITE                         |                   |
 
| XOR                           | Operator          |
 
| YEAR_MONTH                    | TermporalInterval |
 
| ZEROFILL                      |                   |
 
+-------------------------------+-------------------+

Comment by Alexander Barkov [ 2016-08-03 ]

MariaDB reserved keywords that are not reserved in Oracle:

+-------------------------------+-------------------+
 
| keyword                       | type              |
 
+-------------------------------+-------------------+
 
| ACCESSIBLE                    |                   |
 
| ASENSITIVE                    |                   |
 
| BIGINT                        | DataType          |
 
| BINARY                        | DataType          |
 
| BOTH                          |                   |
 
| CALL                          | VerbClause        |
 
| CASE                          | Operator          |
 
| COLLATE                       | Operator          |
 
| CONDITION                     |                   |
 
| CROSS                         |                   |
 
| CURRENT_DATE                  | STDSysVar         |
 
| CURRENT_ROLE                  | STDSysVar         |
 
| CURRENT_TIME                  | STDSysVar         |
 
| CURRENT_TIMESTAMP             | STDSysVar         |
 
| CURRENT_USER                  | STDSysVar         |
 
| DATABASES                     |                   |
 
| DAY_HOUR                      | TermporalInterval |
 
| DAY_MICROSECOND               | TermporalInterval |
 
| DAY_MINUTE                    | TermporalInterval |
 
| DAY_SECOND                    | TermporalInterval |
 
| DELAYED                       |                   |
 
| DESCRIBE                      | VerbClause        |
 
| DETERMINISTIC                 |                   |
 
| DISTINCTROW                   |                   |
 
| DIV                           | Operator          |
 
| DO_DOMAIN_IDS                 |                   |
 
| DUAL                          |                   |
 
| ELSEIF                        |                   |
 
| ENCLOSED                      |                   |
 
| ESCAPED                       |                   |
 
| EXCLUDE                       |                   |
 
| EXIT                          |                   |
 
| FETCH                         |                   |
 
| FLOAT4                        | DataType          |
 
| FLOAT8                        | DataType          |
 
| FOLLOWING                     |                   |
 
| FULLTEXT                      |                   |
 
| HIGH_PRIORITY                 | QueryOption       |
 
| HOUR_MICROSECOND              | TermporalInterval |
 
| HOUR_MINUTE                   | TermporalInterval |
 
| HOUR_SECOND                   | TermporalInterval |
 
| IGNORE                        |                   |
 
| IGNORE_DOMAIN_IDS             |                   |
 
| INFILE                        |                   |
 
| INNER                         |                   |
 
| INOUT                         |                   |
 
| INSENSITIVE                   |                   |
 
| INT1                          | DataType          |
 
| INT2                          | DataType          |
 
| INT3                          | DataType          |
 
| INT4                          | DataType          |
 
| INT8                          | DataType          |
 
| INTERVAL                      |                   |
 
| ITERATE                       |                   |
 
| JOIN                          |                   |
 
| KEYS                          |                   |
 
| LEADING                       |                   |
 
| LEAVE                         |                   |
 
| LEFT                          |                   |
 
| LINEAR                        |                   |
 
| LINES                         |                   |
 
| LOAD                          | VerbClause        |
 
| LOCALTIME                     | Function          |
 
| LOCALTIMESTAMP                | Function          |
 
| LONGBLOB                      | DataType          |
 
| LONGTEXT                      | DataType          |
 
| LOOP                          |                   |
 
| LOW_PRIORITY                  |                   |
 
| MASTER_SSL_VERIFY_SERVER_CERT |                   |
 
| MATCH                         | Operator          |
 
| MEDIUMBLOB                    | DataType          |
 
| MEDIUMINT                     | DataType          |
 
| MEDIUMTEXT                    | DataType          |
 
| MIDDLEINT                     | DataType          |
 
| MINUTE_MICROSECOND            | TermporalInterval |
 
| MINUTE_SECOND                 | TermporalInterval |
 
| MOD                           | Operator          |
 
| MODIFIES                      |                   |
 
| NATURAL                       |                   |
 
| NO_WRITE_TO_BINLOG            |                   |
 
| OPTIMIZE                      | VerbClause        |
 
| OPTIONALLY                    |                   |
 
| OTHERS                        |                   |
 
| OUT                           |                   |
 
| OUTER                         |                   |
 
| OUTFILE                       |                   |
 
| OVER                          |                   |
 
| PAGE_CHECKSUM                 |                   |
 
| PARSE_VCOL_EXPR               |                   |
 
| PRECEDING                     |                   |
 
| READS                         |                   |
 
| READ_WRITE                    |                   |
 
| RECURSIVE                     |                   |
 
| REF_SYSTEM_ID                 |                   |
 
| REGEXP                        | Operator          |
 
| RELEASE                       | VerbClause        |
 
| REPEAT                        |                   |
 
| REQUIRE                       |                   |
 
| RESIGNAL                      | VerbClause        |
 
| RESTRICT                      |                   |
 
| RIGHT                         |                   |
 
| RLIKE                         | Operator          |
 
| SCHEMAS                       |                   |
 
| SECOND_MICROSECOND            | TermporalInterval |
 
| SENSITIVE                     |                   |
 
| SEPARATOR                     |                   |
 
| SHOW                          | VerbClause        |
 
| SIGNAL                        | VerbClause        |
 
| SPATIAL                       |                   |
 
| SPECIFIC                      |                   |
 
| SQL                           |                   |
 
| SQL_BIG_RESULT                | QueryOption       |
 
| SQL_CALC_FOUND_ROWS           | QueryOption       |
 
| SQLEXCEPTION                  |                   |
 
| SQL_SMALL_RESULT              | QueryOption       |
 
| SQLSTATE                      |                   |
 
| SQLWARNING                    |                   |
 
| SSL                           |                   |
 
| STARTING                      |                   |
 
| STATS_AUTO_RECALC             |                   |
 
| STATS_PERSISTENT              |                   |
 
| STATS_SAMPLE_PAGES            |                   |
 
| STRAIGHT_JOIN                 | QueryOption       |
 
| TERMINATED                    |                   |
 
| TIES                          |                   |
 
| TINYBLOB                      | DataType          |
 
| TINYINT                       | DataType          |
 
| TINYTEXT                      | DataType          |
 
| TRAILING                      |                   |
 
| UNBOUNDED                     |                   |
 
| UNSIGNED                      |                   |
 
| UTC_DATE                      | Function          |
 
| UTC_TIME                      | Function          |
 
| UTC_TIMESTAMP                 | Function          |
 
| VARBINARY                     | DataType          |
 
| VARCHARACTER                  | DataType          |
 
| WHILE                         |                   |
 
| WINDOW                        |                   |
 
| XOR                           | Operator          |
 
| YEAR_MONTH                    | TermporalInterval |
 
| ZEROFILL                      |                   |
 
+-------------------------------+-------------------+

Comment by Daniel Black [ 2016-09-05 ]

Seems at least REF_SYSTEM_ID is missing from https://mariadb.com/kb/en/mariadb/reserved-words/ (and the rest of the GIS documentation)

Comment by Jérôme Brauge [ 2017-03-13 ]

This is scripts to test all keyword used as

  • variable name : keywords_var_mdb.sql and keywords_var_ora.sql
  • table alias name : keywords_alias_mdb.sql and keywords_alias_ora.sql
  • block/goto label : keywords_label_mdb.sql and keywords_label_ora.sql

Results are aggregate in an excel file: keywords.xlsx.

"TODO" in cells of this excel means : works fine in Oracle (as a variable, or a label, or a table alias), but does not work in sql_mode=ORACLE

keywords.xlsx keywords_alias_mdb.sql keywords_alias_ora.sql keywords_label_mdb.sql keywords_label_ora.sql keywords_var_mdb.sql keywords_var_ora.sql

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