Details
-
Technical task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
-
None
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.
Attachments
Issue Links
- is part of
-
MDEV-11070 Providing compatibility to other databases - Phase 2
-
- Open
-
- relates to
-
MDEV-12518 Unify sql_yacc.yy and sql_yacc_ora.yy
-
- Closed
-
-
MDEV-16244 sql_mode=ORACLE: Some keywords do not work in variable declarations
-
- Closed
-
-
-
- Open
-
-
-
- Open
-
Activity
Seems at least REF_SYSTEM_ID is missing from https://mariadb.com/kb/en/mariadb/reserved-words/ (and the rest of the GIS documentation)
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 | |
|
+-------------------------------+-------------------+
|
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 | |
|
+-------------------------------+-------------------+
|
This is scripts to test all keyword used as
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