sql_mode for MariaDB Xpand

When using the Xpand Storage Engine topology, the details described here only apply when you connect to the Xpand nodes.

Supported SQL Modes

Xpand service provides support for the following SQL Modes:

  • NO_AUTO_VALUE_ON_ZERO

  • STRICT_TRANS_TABLES

  • STRICT_ALL_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 in MariaDB Server and do not enforce types or validate when values are out of range

See also warning below about unsupported values.

Unsupported SQL Modes

Attempting to set sql_mode to an unsupported value will result in a NULL value being set. The Xpand service does not raise warnings or errors when you set an unsupported SQL Mode.

The Xpand service does not support the following SQL Modes:

  • 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