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
| Field | Original Value | New Value |
|---|---|---|
| Epic Link | MDEV-10137 [ 56868 ] |
| 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: {code:sql} CREATE TABLE localtimestamp (a INT); {code} works without problems in Oracle, but returns an error in MariaDB: {noformat} ERROR 1064 (42000): You have an error in your SQL syntax...near 'localtimestamp ... {noformat} Under terms of this tasks, we'll find all keywords that are reserved in MariaDB but not reserved in the other databases. |
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: {code:sql} CREATE TABLE localtimestamp (a INT); {code} works without problems in Oracle, but returns an error in MariaDB: {noformat} ERROR 1064 (42000): You have an error in your SQL syntax...near 'localtimestamp ... {noformat} 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. |
| Affects Version/s | 10.2 [ 14601 ] | |
| Issue Type | Bug [ 1 ] | Task [ 3 ] |
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 | |
|
+-------------------------------+-------------------+
|
| Labels | Compatibility |
Seems at least REF_SYSTEM_ID is missing from https://mariadb.com/kb/en/mariadb/reserved-words/ (and the rest of the GIS documentation)
| Link | This issue is part of MDEV-10137 [ MDEV-10137 ] |
| Labels | Compatibility |
| Fix Version/s | 10.2 [ 14601 ] |
| Link | This issue is part of MDEV-11070 [ MDEV-11070 ] |
| Link | This issue is part of MDEV-10137 [ MDEV-10137 ] |
| Epic Link | MDEV-10137 [ 56868 ] |
| Parent | MDEV-10764 [ 57940 ] | |
| Issue Type | Task [ 3 ] | Technical task [ 7 ] |
| Attachment | keywords.xlsx [ 43422 ] | |
| Attachment | keywords_alias_mdb.sql [ 43423 ] | |
| Attachment | keywords_alias_ora.sql [ 43424 ] | |
| Attachment | keywords_label_mdb.sql [ 43425 ] | |
| Attachment | keywords_label_ora.sql [ 43426 ] | |
| Attachment | keywords_var_mdb.sql [ 43427 ] | |
| Attachment | keywords_var_ora.sql [ 43428 ] |
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
| Link |
This issue relates to |
| Fix Version/s | 10.3 [ 22126 ] |
| Fix Version/s | 10.3 [ 22126 ] |
| Link |
This issue relates to |
| Link | This issue relates to MDEV-16258 [ MDEV-16258 ] |
| Link | This issue relates to MDEV-16259 [ MDEV-16259 ] |
| Assignee | Alexander Barkov [ bar ] |
| Workflow | MariaDB v3 [ 76560 ] | MariaDB v4 [ 140028 ] |
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 | |+-------------------------------+-------------------+