When running with sql_mode=ORACLE, MariaDB server translates the data type "DATE" to "DATETIME", for better Oracle compatibility:
Notice, DATE was translated to DATETIME.
This translation may cause some ambiguity. Suppose a user creates a table with a column of the traditional MariaDB DATE data type using the default sql_mode, but then switches to sql_mode=ORACLE and runs a SHOW CREATE TABLE statement:
Before the versions 10.3.24, 10.4.14, 10.5.5, the above script displayed:
which had two problems:
- It was confusing for the reader: its not clear if it is the traditional MariaDB DATE, or is it Oracle-alike date (which is actually DATETIME)?
- It broke replication and caused data type mismatch on the master and on the slave (see
To address this problem, starting from the mentioned versions, MariaDB uses the idea of qualified data types:
When the server sees the "mariadb_schema" qualifier, it disables sql_mode specific data type translation and interprets the data type literally, so for example mariadb_schema.DATE is interpreted as the traditional MariaDB DATE data type, no matter what the current sql_mode is.
Note, the "mariadb_schema" prefix is displayed only when the data type name would be ambiguous otherwise. As of version 10.5.9, the prefix is displayed together with MariaDB DATE when SHOW CREATE TABLE is executed in sql_mode=ORACLE. The prefix is not displayed when SHOW CREATE TABLE is executed in sql_mode=DEFAULT, or when a non-ambiguous data type is displayed.
Note, the "mariadb_schema" prefix can be used with any data type, including non-ambiguous ones: