Data Type Conversion
The global sync variable data_conversion_compatibility_level
controls the way certain data conversions are performed. This variable can have the following possible values: '6.0', '6.5', '7.0','7.5', and '8.0'. Higher values introduce stricter checking of values and will error for conversions that worked at lower levels. For example, the '7.0'
level will fail the conversion of 'abc'
to an integer value, while the '6.0'
level will silently convert it to a NULL or 0.
The following table lists the data type conversion checks and behavior changes that are introduced with each data_conversion_compatibility_level
value. Note that the data type conversion checks introduced in a compatibility level are preserved in subsequent levels.
data_conversion_compatibility_level | Data Type Conversion Checks |
---|---|
|
|
|
|
|
|
|
|
|
|
It’s recommended to set the data_conversion_compatibility_level
variable to the highest available value for new application development. For existing applications, it’s also recommended to use the highest available level, but it’s recommended that you test your application before deploying this change. In particular, changing the value of data_conversion_compatibility_level
can change the behavior of expressions in computed columns.
If a computed column value changes due to a change in data_conversion_compatibility_level
, columnstore sorting, indexes, and sharding can become logically corrupted. SingleStore does not recompile an existing plan when data_conversion_compatibility_level
or sql_mode
changes.
Note
sql_mode
is persisted to the CREATE TABLE
statement. Therefore, the computed column of a table always uses the sql_mode
that the table is created with, which may be different from the current sql_mode
.
Best practices to avoid data corruption
Review the computed column expressions when changing
data_conversion_compatibility_level
.Change
data_conversion_compatibility_level
only as a part of an application upgrade process.Perform application upgrade tests.
For example, if a computed column contains corrupted data and you have to switch to a higher data_conversion_compatibility_level
, you may need to drop the computed column before changing the level. Once the level is increased, add the computed column to the table. Alternatively, if a persisted computed column is used in a columnstore key, you may have to create a new table and select data into this new table. After copying the data, drop the old table and rename the new table.
The following examples demonstrate how the behavior of expressions may change if data_conversion_compatibility_level
is changed.
Example 1
SET GLOBAL data_conversion_compatibility_level = '6.0';
SELECT DATE('2019-04-20') + 2; **** +------------------------+ | DATE('2019-04-20') + 2 | +------------------------+ | 20190422 | +------------------------+
Example 2
SET GLOBAL data_conversion_compatibility_level = '7.0';
SELECT DATE('2019-04-20') + 2; **** +------------------------+ | DATE('2019-04-20') + 2 | +------------------------+ | 2019-04-22 | +------------------------+
The following example shows the error message generated if a zero value is attempted to be inserted into a DATE
, DATETIME
, or DATETIME(6)
column if the data_conversion_compatibility_level
is set to 7.0
or higher.
SET GLOBAL data_conversion_compatibility_level = '7.0';
CREATE TABLE dates (dt DATETIME); INSERT INTO dates VALUES ('0000-00-00'); **** ERROR 2363 (HY000): Leaf Error: Invalid DATE/TIME in type conversion
See the table on the Time and Date page for examples of zero values for DATE
, DATETIME
, and DATETIME(6)
data types. This page also discusses how date (and time) values are implicitly converted from string literals (e.g., '2023-02-03') and how to use date and time functions to perform explicit conversions from string literals.