sql_mode
with MariaDB Xpand
This page is part of MariaDB's Enterprise Documentation.
The parent of this page is: sql_
Topics on this page:
Overview
MariaDB Xpand supports the following values for the sql_mode
system variable:
NO_AUTO_VALUE_ON_ZERO
STRICT_TRANS_TABLES
(default)STRICT_ALL_TABLES
Setting SQL_MODE
SQL_MODE
can be set via a variable for a session (affects only the current client), or globally (which affects new sessions). For more information, see "System Variables for MariaDB Xpand". The global variable for SQL_MODE
can capture multiple modes:
sql> set sql_mode = "STRICT_ALL_TABLES,STRICT_TRANS_TABLES";
Caveats for SQL_MODE:
Since all tables are transactional with Xpand,
STRICT_TRANS_TABLES
andSTRICT_ALL_TABLES
are equivalentSTRICT_TRANS_TABLES
andSTRICT_ALL_TABLES
are not as strict as MariaDB and do not enforce types or validate when values are out of rangeWhen the
STRICT_TRANS_TABLES
mode is used, newer version of Xpand (5.3.19+) will perform type and data length checks for all types except:SET
,UNSIGNED INT
,BIT
,ENUM
,FLOAT
,DOUBLE
,REAL UNSIGNED
,DATE
,TIME
,DATETIME
,TIMESTAMP
,YEAR
Please contact support before upgrading from an older version of Xpand to 5.3.19+
Unsupported SQL_MODES:
Note
Attempting to set SQL_MODE
to an unsupported value will result in a NULL value being set. Xpand does not warn on unsupported values for SQL_MODE
Xpand does not support the following values for SQL_MODE
:
ALLOW_INVALID_DATES
ANSI
ANSI_QUOTES
DB2
EMPTY_STRING_IS_NULL
ERROR_FOR_DIVISION_BY_ZERO
HIGH_NOT_PRECEDENCE
IGNORE_BAD_TABLE_OPTIONS
IGNORE_SPACE
MAXDB
MSSQL
MYSQL323
MYSQL40
NO_AUTO_CREATE_USER
NO_AUTO_VALUE_ON_ZERO
NO_BACKSLASH_ESCAPES
NO_DIR_IN_CREATE
NO_ENGINE_SUBSTITUTION
NO_FIELD_OPTIONS
NO_KEY_OPTIONS
NO_TABLE_OPTIONS
NO_UNSIGNED_SUBTRACTION
NO_ZERO_DATE
NO_ZERO_IN_DATE
ONLY_FULL_GROUP_BY
ORACLE
PAD_CHAR_TO_FULL_LENGTH
PIPES_AS_CONCAT
POSTGRESQL
REAL_AS_FLOAT
SIMULTANEOUS_ASSIGNMENT
SIMULTANEOUS_ASSIGNMENT
TIME_ROUND_FRACTIONAL
TRADITIONAL