STRICT_TRANS_TABLES

Data Type Exceptions

In MariaDB Xpand, strict mode does not enable validation and range checks for all data types:

• Before Xpand 5.3.19, data types are not validated, and range checks are not performed.

• Starting with Xpand 5.3.19, data types are validated, and range checks are performed for all types, except:

  • BIT

  • DATE

  • DATETIME

  • DOUBLE

  • ENUM

  • FLOAT

  • REAL UNSIGNED

  • SET

  • TIME

  • TIMESTAMP

  • UNSIGNED INT

  • YEAR

• Starting with Xpand 6.1, data types are validated, and range checks are performed for all types, except:

  • BIT

  • DATE

  • DATETIME

  • DOUBLE

  • FLOAT

  • REAL UNSIGNED

  • TIME

  • TIMESTAMP

  • YEAR

To upgrade to a newer version of Xpand, see "Upgrades for MariaDB Xpand".

To request help with the upgrade, please contact MariaDB Support.

Setting the SQL_MODE

Note that @@sql_mode is the same as @@session.sql_mode:

SELECT @@sql_mode, @@session.sql_mode;

+---------------------+---------------------+
| @@sql_mode          | @@session.sql_mode  |
+---------------------+---------------------+
| STRICT_TRANS_TABLES | STRICT_TRANS_TABLES |
+---------------------+---------------------+

The STRICT_TRANS_TABLES flag is on by default, as shown above. In the following example, a request to add a flag that is already set shows how multiple identical flag values are reduced to a single SQL_MODE flag:

SET sql_mode = CONCAT(@@sql_mode, ',STRICT_TRANS_TABLES');

SELECT @@sql_mode;

+---------------------+
| @@sql_mode          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+

Rejecting Invalid Values

This table will let us test validating some integer values:

CREATE TABLE strict_test (
  j TINYINT,
  descr varchar(128)
);

These inserted values are rejected when STRICT_TRANS_TABLES is set due to one row having an integer overflow:

INSERT INTO strict_test VALUES
   (0, 'inserted 0'),
   (1, 'inserted 1'),
   (2000, 'inserted 2000');

ERROR 1264 (HY000): [63494] Value out of range: "2000 overflowed TINYINT"

Inserting Invalid Values

To allow invalid values to be inserted as truncated values instead of being rejected, the SQL_MODE flags must not contain a flag that enables strict tables. One way to do that is to assign an empty string to the SQL_MODE flags:

SET sql_mode = '';

INSERT INTO strict_test VALUES
   (0, 'inserted 0'),
   (1, 'inserted 1'),
   (2000, 'inserted 2000');

SELECT * FROM strict_test;

+------+---------------+
| j    | descr         |
+------+---------------+
|    0 | inserted 0    |
|    1 | inserted 1    |
|  127 | inserted 2000 |
+------+---------------+