Document mariadb_schema data type qualifier

When running with sql_mode=ORACLE, MariaDB server translates the data type "DATE" to "DATETIME", for better Oracle compatibility:

SET SQL_mode=ORACLE;

CREATE OR REPLACE TABLE t1 (

  d DATE

);

SHOW CREATE TABLE t1;

+-------+---------------------------------------------------+

| Table | Create Table                                      |

+-------+---------------------------------------------------+

| t1    | CREATE TABLE "t1" (

  "d" datetime DEFAULT NULL

) |

+-------+---------------------------------------------------+

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:

SET sql_mode=DEFAULT;

CREATE OR REPLACE TABLE t1 (

  d DATE

);

SET SQL_mode=ORACLE;

SHOW CREATE TABLE t1;

Before the versions 10.3.24, 10.4.14, 10.5.5, the above script displayed:

CREATE TABLE "t1" (

  "d" date DEFAULT NULL

);

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 MDEV-19632).

To address this problem, starting from the mentioned versions, MariaDB uses the idea of qualified data types:

SET sql_mode=DEFAULT;

CREATE OR REPLACE TABLE t1 (

  d DATE

);

SET SQL_mode=ORACLE;

SHOW CREATE TABLE t1;

+-------+--------------------------------------------------------------+

| Table | Create Table                                                 |

+-------+--------------------------------------------------------------+

| t1    | CREATE TABLE "t1" (

  "d" mariadb_schema.date DEFAULT NULL

) |

+-------+--------------------------------------------------------------+

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:

CREATE OR REPLACE TABLE t1 (a mariadb_schema.INT);

SHOW CREATE TABLE t1;

+-------+--------------------------------------------------+

| Table | Create Table                                     |

+-------+--------------------------------------------------+

| t1    | CREATE TABLE "t1" (

  "a" int(11) DEFAULT NULL

) |

+-------+--------------------------------------------------+