Description\n-----------\n\nMariaDB offers schema qualifiers that allow you to define how data types and functions are managed when SQL modes result in operational differences.\n\nThese qualifiers are used as prefixes and are part of the SQL language structure. These are not system databases or tables.&#x20;\n\nThe schema qualifiers listed below are supported:\n\n mariadb_schema\n oracle_schema\n\nBoth prefixes describe the same mechanism, however the behavior differs based on the desired compatibility.\n\nCertain SQL modes, such as SQL_MODE=ORACLE, affect how MariaDB understands data type and functions. MariaDB translates several data type names and function behaviors according to Oracle Database standards when SQL_MODE=ORACLE is active. This translation may lead to ambiguity: depending on the SQL mode in use, a data type or function name written without a qualifier could be interpreted differently.\n\nFor example, in Oracle mode, the data type DATE is interpreted as DATETIME. This behavior can be explicitly overridden at the statement level using schema qualifiers, without modifying the SQL mode session.\n\nVersion Support\n\n mariadb_schema for data type qualification is supported since:\n   MariaDB 10.3.24\n   MariaDB 10.4.14\n   MariaDB 10.5.5\n oracle_schema is supported as part of the same schema qualifier mechanism.\n Functional qualification using schema qualifiers is supported since:\n   MariaDB 10.6.17 (see MDEV-27744)\n\nWhy Schema Qualifiers are Needed\n\nSpecific SQL modes, for example SQL_MODE=ORACLE, determine how MariaDB handles data types and functions.\n\nThis may result in ambiguity when:\n\n Switching between SQL modes\n Evaluating table definitions using SHOW CREATE TABLE\n Syncing data across servers\n\nRegardless of the SQL mode that is currently in use, schema qualifiers provide explicit control over interpretation, ensuring reliable and consistent behavior.\n\nmariadb_schema\n\nThe mariadb_schema qualifier forces MariaDB-native interpretation of the data type or function, regardless of the active SQL mode.\n\nWhen the server sees the mariadb_schema qualifier, it disables sql_mode-specific data type translation and interprets the data type literally. For example, mariadb_schema.DATE is interpreted as the traditional MariaDB DATE data type, regardless of the current SQL mode.\n\nData Type Qualification\n\nIn Oracle mode, the DATE type is translated to DATETIME:\n\n``sql\nSET sql_mode=ORACLE;\n\nCREATE TABLE t1 (\nd DATE\n);\n`\n\nTo specifically use the MariaDB-native DATE type while in Oracle mode:\n\n`sql\nCREATE TABLE t1 (\n  d mariadb_schema.DATE\n);\n`\n\nExample\n\n`sql\nSET sql_mode=ORACLE;\n\nCREATE TABLE t1 (\n  d DATE,\n  d_m mariadb_schema.DATE\n);\n\nDESCRIBE t1;\n`\n\nOutput\n\n`sql\n+-------+----------+\n| Field | Type     | \n+-------+----------+\n| d     | datetime |\n| d_m   | date     |\n+-------+----------+\n`\n\noracle_schema\n\nThe oracle_schema qualifier enforces Oracle-compatible behavior of the data type or function, regardless of the active SQL mode. It supports Oracle semantics but acts similarly to mariadb_schema.\n\nFor example:\n\n In Oracle mode, the DATE data types behaves like DATETIME.\n Using oracle_schema.DATE ensures Oracle-compatible interpretation even when Oracle mode is not enabled.\n\nData Type Qualification\n\n`sql\nSET sql_mode=ORACLE;\n\nCREATE TABLE t1 (\n  d DATE,\n  d_m mariadb_schema.DATE,\n  d_o oracle_schema.DATE\n);\n\nDESCRIBE t1;\n`\n\nOutput\n\n`sql\n+-------+----------+\n| Field | Type     | \n+-------+----------+\n| d     | datetime |\n| d_m   | date     |\n| d_o   | datetime |\n+-------+----------+\n`\n\nUsing Both Qualifiers Together\n\nThe following example demonstrates how to use both qualifiers together in a single table definition to control data type behavior.&#x20;\n\nTo explicitly use Oracle-compatible behavior outside Oracle mode:\n\n`sql\nSET sql_mode=DEFAULT;\n\nCREATE TABLE t1 (\nd oracle_schema.DATE\n);\n`\n\nWhen running in Oracle mode, both qualifiers can be used to explicitly control behavior.\n\n`sql\nSET sql_mode=ORACLE;\n\nCREATE TABLE t1 (\nd DATE,                  # Oracle-compatible (stored as DATETIME)\nd_m mariadb_schema.DATE, # MariaDB-native DATE\nd_o oracle_schema.DATE   # Oracle-compatible DATE\n);\n\nDESCRIBE t1;\n`\n\nOutput\n\n`sql\n+-------+----------+------+-----+---------+-------+\n| Field | Type     | Null | Key | Default | Extra |\n+-------+----------+------+-----+---------+-------+\n| d     | datetime | YES  |     | NULL    |       |\n| d_m   | date     | YES  |     | NULL    |       |\n| d_o   | datetime | YES  |     | NULL    |       |\n+-------+----------+------+-----+---------+-------+  \n`\n\nFunction Qualification\n\nStarting with MariaDB 10.6.17, schema qualifiers can also be added to functions.&#x20;\n\nRegardless of the current SQL mode, this enables the explicit selection of MariaDB-native or Oracle-compatible behavior.\n\nExample syntax:\n\n`sql\nMariaDB-native function behavior in any SQL mode\nmariadb_schema.function_name(...) \n\nOracle-compatible function behavior in any SQL mode\noracle_schema.function_name(...)\n`\n\nBehavior\n\n mariadb_schema.function_name(...): MariaDB-native behavior\n oracle_schema.function_name(...): Oracle-compatible behavior\n\nNote: Function behavior varies depending on the SQL mode and function.\n\\\nDetailed function-level differences are not fully listed here and may differ by version.\n\nSHOW CREATE TABLE\n\nTo prevent ambiguity in data type interpretation, MariaDB may display schema qualifiers when SHOW CREATE TABLE is executed.\n\n`sql\nSET sql_mode=DEFAULT;\nCREATE OR REPLACE TABLE t1 (\n  d DATE\n);\nSET SQL_mode=ORACLE;\nSHOW CREATE TABLE t1;\n+-------+--------------------------------------------------------------+\n| Table | Create Table                                                 |\n+-------+--------------------------------------------------------------+\n| t1    | CREATE TABLE "t1" (                                          |\n|       |   "d" mariadb_schema.DATE DEFAULT NULL                       |\n|       |  )                                                           |\n+-------+--------------------------------------------------------------+\n`\n\nThe prefix is displayed to indicate that the column uses MariaDB's native DATE type rather than the Oracle-compatible translated version.\n\nThe mariadb_schema prefix is only displayed when the data type is ambiguous. When the type is non-ambiguous or the SQL mode is set to default, it is not displayed.\n\nThis makes it easier to determine whether a column uses MariaDB-native or Oracle-compatible semantics.\n\nHistory\n\nWhen running with SQL_MODE=ORACLE, MariaDB server translates the DATE data type to DATETIME, for Oracle compatibility.&#x20;\n\n`sql\nSET SQL_mode=ORACLE;\nCREATE OR REPLACE TABLE t1 (\n  d DATE\n);\nSHOW CREATE TABLE t1;\n+-------+--------------------------------------------------------------+\n| Table | Create Table                                                 |\n+-------+--------------------------------------------------------------+\n| t1    | CREATE TABLE "t1" (                                          |\n|       |   "d" datetime DEFAULT NULL                       |\n|       |  )                                                           |\n+-------+--------------------------------------------------------------+\n`\n\nNote: DATE was converted to DATETIME.\n\nThis translation can cause ambiguity. For example, if a table is created in default SQL mode and then evaluated in Oracle mode:\n\n`sql\nSET sql_mode=DEFAULT;\nCREATE OR REPLACE TABLE t1 (\n  d DATE\n);\nSET SQL_mode=ORACLE;\nSHOW CREATE TABLE t1;\n`\n\nBefore schema qualifiers were introduced, this would display:\n\n`sql\nCREATE TABLE "t1" (\n  "d" DATE DEFAULT NULL\n);\n`\n\nThis created two problems:\n\n It was not clear whether DATE referred to the MariaDB DATE type or the Oracle-compatible DATETIME.\n It broke replication, causing a data type mismatch between the master and the slave. (see MDEV-19632).\n\nTo address this problem, MariaDB included schema qualifiers to explicitly qualify data types:\n\n`sql\nSET sql_mode=DEFAULT;\nCREATE OR REPLACE TABLE t1 (\n  d DATE\n);\nSET SQL_mode=ORACLE;\nSHOW CREATE TABLE t1;\n+-------+--------------------------------------------------------------+\n| Table | Create Table                                                 |\n+-------+--------------------------------------------------------------+\n| t1    | CREATE TABLE "t1" (                                          |\n|       |   "d" mariadb_schema.DATE DEFAULT NULL                       |\n|       |  )                                                           |\n+-------+--------------------------------------------------------------+\n``\n\nThis enables consistent and unambiguous behavior across all SQL modes.\n\nURL: https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/schema-qualifiers