Data Type Conversion

The following table lists the supported values and the data type conversion checks and behavior changes that are introduced with each data_conversion_compatibility_level value. 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. 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
`6.0`	The lowest data type conversion compatibility level. Default value for SingleStore engine versions lower than 8.0.
`6.5`	Error on integer overflows and underflows in `INSERT` statements. This check applies to all `INTEGER` data types, including `BIGINT`, `INT`, `MEDIUMINT`, `SMALLINT`, `TINYINT`, `BIT`, and `BOOL`. Error on string inputs to `VARCHAR` columns that are too short to store the data. This check is run against `INSERT` statements. Error on string inputs to `LONGBLOB`, `MEDIUMBLOB`, `BLOB`, `TINYBLOB`, `LONGTEXT`, `MEDIUMTEXT`, `TEXT`, and `TINYTEXT` columns that are too short to store the data. This check is run against `INSERT` statements. Error on invalid string to int conversion in `INSERT` statements. Only strings with valid characters will be converted to integers.
`7.0`	Error on string inputs to `CHAR` columns that are too short to store the data. This check is run against `INSERT` statements. Error on inputs to `BINARY` columns that are too short to store the data. This check is run against `INSERT` statements. Error on decimal inputs that do not match the size specified in the `DECIMAL` column definition. This check is run against `INSERT` statements. Error on invalid or out-of-range date inputs to `DATE`, `DATETIME`, `DATETIME(6)`, and `YEAR` columns in `INSERT` statements. Errors on zero value date inputs into `DATE`, `DATETIME`, and `DATETIME(6)` columns in `INSERT` statements. Arithmetic operations involving `DATE` and `INT` values are performed by converting the `INT` data into intervals (days) instead of converting both data types to `DOUBLE`.
`7.5`	Error on invalid string to `DECIMAL` conversion in `INSERT` statements. Only strings with valid characters undergo numeric conversion.

SingleStore recommends using the highest available data_conversion_compatibility_level value for new application development. 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.

Handling invalid source database DATE and DATETIME values during Flow migrations

When using SingleStore Flow to migrate data from supported source databases into SingleStore, the source data may contain non-standard or invalid DATE or DATETIME values such as 0001-01-01 07:07:24 or 0000-12-01. If the data_conversion_compatibility_level is set to a stricter setting, loading this data can fail with an error such as:

SQL

Error(PGT54): Table core_data.`mage_order` stage 3 error java.sql.SQLException: (conn=142441) Leaf Error (node-83201ac7-c278-4902-8df7-ab7902e5adb3-leaf-ag2-0.svc-83201ac7-c278-4902-a1f2-600f50d9b580:3306): Invalid DATE/TIME in type conversion for column 'col_datetime'

To work around this issue without modifying the source data, temporarily set the compatibility to an earlier version such as 6.5:

SQL

SET GLOBAL data_conversion_compatibility_level = '6.5';

For example, the following executes successfully:

SQL

DROP TABLE IF EXISTS date_tbl;

CREATE TABLE date_tbl (
  id INT PRIMARY KEY NOT NULL,
  d  DATETIME
);

INSERT date_tbl VALUES (1, '0001-01-01 07:07:24.000000');

SELECT * FROM date_tbl;

Important

By default, SingleStore recommends using the highest available data_conversion_compatibility_level to enforce strict validation. Lowering it can allow non‑standard or invalid data to be inserted across the entire cluster. Use this workaround only in exceptional cases (for example, short-term Flow migrations that must preserve these values), and restore the highest supported compatibility level as soon as possible.

Best Practices to Avoid Data Corruption

Review the computed column expressions before changing data_conversion_compatibility_level.
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. After increasing the level, 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

SQL

SET GLOBAL data_conversion_compatibility_level = '6.0';

SQL

SELECT DATE('2019-04-20') + 2;

+------------------------+
| DATE('2019-04-20') + 2 |
+------------------------+
|               20190422 |
+------------------------+

Example 2

SQL

SET GLOBAL data_conversion_compatibility_level = '7.0';

SQL

SELECT DATE('2019-04-20') + 2;

+------------------------+
| DATE('2019-04-20') + 2 |
+------------------------+
| 2019-04-22             |
+------------------------+

Example 3

The following example shows the error message generated when an attempt is made to insert a zero value into a DATE, DATETIME, or DATETIME(6) column if the data_conversion_compatibility_level is set to 7.0 or higher.

SQL

SET GLOBAL data_conversion_compatibility_level = '7.0';

SQL

CREATE TABLE dates (dt DATETIME);
INSERT INTO dates VALUES ('0000-00-00');

ERROR 2363 (HY000): Leaf Error: Invalid DATE/TIME in type conversion

Refer to Time and Date 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.

Data Type Conversion

On this page

Handling invalid source database DATE and DATETIME values during Flow migrations

Best Practices to Avoid Data Corruption

Was this article helpful?

On this page

Was this article helpful?