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.
Unsupported SQL_MODES:
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