Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space ML1 and version 9.2

SQL_MODE values:

ClustrixDB 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 ClustrixDB, STRICT_TRANS_TABLES and STRICT_ALL_TABLES are equivalent
  • STRICT_TRANS_TABLES and STRICT_ALL_TABLES are not as strict as MySQL and do not enforce types or validate when values are out of range

See also warning below about unsupported values. 

Anchor
unsupported
unsupported
Unsupported SQL_MODES:

Warning

Attempting to set SQL_MODE to an unsupported value will result in a NULL value being set. ClustrixDB does not warn on unsupported values for SQL_MODE

Clustrix 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