MariaDB Xpand Documentation

Space shortcuts

Page tree

SQL_MODE values:

Xpand provides support for the following values for SQL_MODE:

  • 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 Global Variables. 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:

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_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
  • No labels