sql_mode with MariaDB Xpand

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 and STRICT_ALL_TABLES are equivalent

  • STRICT_TRANS_TABLES and STRICT_ALL_TABLES are not as strict as MariaDB and do not enforce types or validate when values are out of range

  • When 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