Data type conversion behavior for MySQL-compatible tenants in strict mode

When a MySQL-compatible tenant enables strict mode in the sql_mode parameter (by setting STRICT_TRANS_TABLES or STRICT_ALL_TABLES), the system handles data type conversion failures differently during DML statements (such as INSERT, UPDATE, REPLACE, and DELETE) and SELECT statements.

Key differences

Behavior when strict mode is on

DML statements

When sql_mode includes strict mode, DML statements will terminate execution immediately upon encountering data type conversion failures.

Behavior characteristics:

• Statement execution fails and returns a clear error message
• All changes made by the statement are rolled back
• Data will not be partially updated or inserted if it does not match the target column type

Examples:

INSERT INTO t (int_col) VALUES ('abc');
-- The string 'abc' cannot be converted to an integer, and an error is returned.

UPDATE t SET date_col = '2024-02-30';
-- '2024-02-30' is an invalid date (February does not have 30 days), and an error is returned.

INSERT INTO t (varchar10_col) VALUES ('This string is way too long');
-- The string exceeds the VARCHAR(10) limit, and an error is returned.

Principle: DML operations directly modify persistent data. Strict mode ensures data accuracy and consistency. Allowing incorrect type conversions could result in data corruption or violate business logic. Therefore, OceanBase Database chooses to fail and roll back in the case of risky conversions.

SELECT statements

When sql_mode includes strict mode, SELECT statements handle type conversion failures more leniently than DML statements.

Characteristics:

• The statement execution does not entirely stop upon a type conversion failure.
• Failed values are treated as NULL or default values.
• A warning is generated, which can be viewed using SHOW WARNINGS;.
• The query continues and returns results.

Examples:

SELECT * FROM t WHERE int_col = 'abc';
-- 'abc' cannot be converted to an integer. A warning is generated. It may be considered as 0 or NULL.

SELECT DATE_ADD('2024-02-30', INTERVAL 1 DAY);
-- An invalid date. A warning is generated. The function returns NULL.

SELECT CAST('abc' AS UNSIGNED);
-- An attempt is made to convert the string to an unsigned integer. A warning is generated, and it returns 0.

Principle: SELECT statements are query operations that do not modify persistent data. Their primary goal is to return results for users to check. In strict mode, SELECT statements prioritize other aspects such as ONLY_FULL_GROUP_BY and focus on elasticity and the availability of partial results for type conversion failures.

Behavior when strict mode is off

DML statements

In non-strict mode, DML statements continue to execute upon a type conversion failure.

Characteristics:

• The statement execution succeeds, and a warning is output.
• The failed data is replaced with the default value of the corresponding type.
• Data is written to the table.

Examples:

CREATE TABLE test(a INT, b INT);
INSERT INTO test VALUES('abc', 'abc');
SELECT * FROM test;

Execution result:

+------+------+
| a    | b    |
+------+------+
|    0 |    0 |
+------+------+
1 row in set (0.039 sec)

SELECT statements

In non-strict mode, SELECT statements behave similarly to those in strict mode ON, generating warnings and continuing execution.

Considerations

STRICT_TRANS_TABLES vs STRICT_ALL_TABLES

In MySQL, these two modes handle type conversion failures in DML statements in the same way (both report an error). The main difference is in how they handle non-transactional tables, such as MyISAM tables. In OceanBase Database, the underlying transaction engine behaves the same way regardless of the sql_mode setting. Therefore, in a MySQL-compatible tenant of OceanBase Database, the behaviors of STRICT_TRANS_TABLES and STRICT_ALL_TABLES in handling type conversion failures are usually the same.

Implicit conversion rules

Even if no conversion fails, implicit conversion may still cause precision loss or semantic change. Strict mode does not prevent all implicit conversions. It mainly prevents those that cannot be converted at all or will result in complete data loss or invalid data, such as failed conversion from strings to numbers, invalid dates, and excessively long strings.

Explicit conversion

When you use the CAST() function for explicit conversion, if the conversion fails, NULL is returned with a warning in any statement type and with any sql_mode setting. This is a standard SQL behavior.

Other modes

Strict mode is often used together with other modes such as ERROR_FOR_DIVISION_BY_ZERO. These modes affect the handling of specific errors such as division by zero and invalid dates.

Troubleshooting

• Error (ERROR) vs. warning (WARNING): If the client receives an ERROR, it means that the DML statement has completely failed. If the client receives a warning but a result set, the conversion error occurred in a SELECT statement.

• Check sql_mode: Use SELECT @@sql_mode; to check whether strict mode is enabled.

• View the execution plan and logs: In complex situations, the execution plan output of OceanBase Database (EXPLAIN) can provide more clues.