Data type conversion behavior of a MySQL tenant in strict mode

When a MySQL tenant is in strict mode (by setting STRICT_TRANS_TABLES or STRICT_ALL_TABLES), the system handles data type conversion failures differently when executing DML statements (such as INSERT, UPDATE, REPLACE, and DELETE) and SELECT statements.

Core differences

Behavior when strict mode is ON

DML statements

When sql_mode is set to strict mode, DML statements will immediately terminate upon encountering a data type conversion failure.

Behavior characteristics:

• The statement execution fails and returns a clear error message.
• All modifications made by the statement are rolled back.
• Data will not be partially updated or inserted with values that do not match the target column type.

Examples:

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

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

INSERT INTO t (varchar10_col) VALUES ('This string is way too long');
-- The string length exceeds the VARCHAR(10) definition, resulting in an error.

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

SELECT statements

When sql_mode is set to strict mode, SELECT statements handle data type conversion failures more leniently compared to DML statements.

Behavior characteristics:

• The statement execution does not get interrupted due to type conversion failures.
• Failed conversion values are treated as NULL or default values.
• Warning messages are generated, which can be viewed using SHOW WARNINGS;.
• The query continues execution and returns the result set.

Examples:

SELECT * FROM t WHERE int_col = 'abc';
-- 'abc' cannot be converted to an integer, generating a warning, which may be treated as 0 or NULL.

SELECT DATE_ADD('2024-02-30', INTERVAL 1 DAY);
-- Invalid date, generating a warning, function returns NULL.

SELECT CAST('abc' AS UNSIGNED);
-- Attempt to convert string to unsigned integer, generating a warning, returns 0.

Principle: SELECT is a query operation that does not modify persistent data. Its main goal is to return results for user inspection. Strict mode focuses more on other aspects (such as ONLY_FULL_GROUP_BY) and prioritizes flexibility and partial result availability for type conversion failures in SELECT.

Behavior when strict mode is OFF

DML statements

In non-strict mode, DML statements continue execution even if type conversion fails.

Behavior characteristics:

• The statement executes successfully and outputs warning messages.
• Failed conversion data is replaced with the corresponding type's default value 0.
• Data is written to the table.

Example:

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, the behavior of SELECT statements is similar to when strict mode is ON, with warnings generated and execution continuing.

Important considerations

STRICT_TRANS_TABLES vs. STRICT_ALL_TABLES

In MySQL, both modes handle type conversion failures in DML statements similarly (both result in errors). The main difference lies in how they handle non-transactional tables (such as MyISAM). Since OceanBase Database's underlying transaction engine does not differ across different sql_mode settings, the behavior of STRICT_TRANS_TABLES and STRICT_ALL_TABLES in handling type conversion failures is typically the same in OceanBase Database's MySQL tenant.

Implicit conversion rules

Even without conversion failures, implicit conversions can lead to precision loss or semantic changes. Strict mode does not block all implicit conversions; it mainly prevents those that are completely unconvertible or result in complete data loss or invalidity (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, NULL is returned and a warning is generated, regardless of the statement type or sql_mode. This is standard SQL behavior.

Other modes

Strict mode is often set alongside 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 ERROR, the DML statement has completely failed. If a result set is returned with a warning, it indicates a conversion issue in a SELECT statement.

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

• View execution plans and logs: For complex scenarios, the execution plan output (EXPLAIN) in OceanBase Database can provide more detailed clues.