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
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 | |
|
+-------------------------------+-------------------+
|
Seems at least REF_SYSTEM_ID is missing from https://mariadb.com/kb/en/mariadb/reserved-words/ (and the rest of the GIS documentation)
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
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 | |+-------------------------------+-------------------+