Data type conversion behavior in strict mode of MySQL tenants

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

Core differences

Behavior when strict mode is ON

DML statements

When sql_mode contains strict mode, DML statements will immediately terminate execution upon encountering a data type conversion failure.

Characteristics:

• The statement fails and returns a clear error message.
• All modifications made by the statement are rolled back.
• Data is not partially updated or inserted into a column of a type that does not match the target column type.

Examples:

INSERT INTO t (int_col) VALUES ('abc');
-- The string 'abc' cannot be converted to an integer, so the statement fails 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), so the statement fails and an error is returned.

INSERT INTO t (varchar10_col) VALUES ('This string is way too long');
-- The string is longer than 10 characters, so the statement fails and an error is returned.

Reason: DML operations directly modify persistent data. Strict mode ensures data accuracy and consistency. Allowing risky type conversions could lead to data corruption or violate business logic, so OceanBase Database chooses to fail and roll back upon encountering dangerous conversions.

SELECT statements

When sql_mode contains strict mode, SELECT statements are more lenient when encountering data type conversion failures compared to DML statements.

Characteristics:

• The statement does not fail and continues execution due to type conversion failures.
• Failed conversion values are treated as NULL or default values.
• A warning is generated, which can be viewed using SHOW WARNINGS;.
• The query continues execution and returns a result set.

Examples:

SELECT * FROM t WHERE int_col = 'abc';
-- 'abc' cannot be converted to an integer, so a warning is generated, and it may be treated as 0 or NULL.

SELECT DATE_ADD('2024-02-30', INTERVAL 1 DAY);
-- '2024-02-30' is an invalid date, so a warning is generated, and the function returns NULL.

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

Reason: SELECT statements are query operations that do not modify persistent data. Their main goal is to return results for user inspection. In strict mode, SELECT statements focus on other aspects (such as ONLY_FULL_GROUP_BY) and prioritize flexibility and partial result availability when type conversion failures occur.

Behavior when strict mode is OFF

DML statements

In non-strict mode, DML statements continue execution upon encountering type conversion failures.

Characteristics:

• The statement executes successfully and outputs a warning message.
• The failed conversion data is replaced with the corresponding type's default value of 0.
• 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, generating warnings and continuing execution.

Considerations

STRICT_TRANS_TABLES vs STRICT_ALL_TABLES

In MySQL, both modes handle type conversion failures in DML operations by returning an error. The main difference lies in how they handle non-transactional tables (such as MyISAM). However, since OceanBase Database's transaction engine behaves the same way under different sql_mode values, STRICT_TRANS_TABLES and STRICT_ALL_TABLES typically have no significant difference in how they handle type conversion failures in MySQL tenants of OceanBase Database.

Implicit conversion rules

Even when no conversion failure occurs, implicit conversion can still lead to precision loss or semantic changes. Strict mode does not block all implicit conversions; it mainly prevents those that result in complete data loss or invalid data (such as failed string-to-number conversion, invalid dates, or inserting excessively long strings).

Explicit conversion

When using the CAST() function for explicit conversion, if the conversion fails, the result will be NULL and a warning will be generated, regardless of the statement type or sql_mode. This behavior aligns with the SQL standard.

Other modes

Strict mode is often set in combination with other modes like ERROR_FOR_DIVISION_BY_ZERO. These modes affect how specific errors, such as division by zero or invalid dates, are handled.

Troubleshooting

• Error (ERROR) vs Warning (WARNING): If the client returns an ERROR, the DML statement has completely failed. If the result set is returned but a warning is present, it indicates that a conversion issue occurred in the SELECT statement.

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

• View execution plans and logs: For complex scenarios, OceanBase Database's execution plan output (EXPLAIN) can provide more detailed insights.