Data type conversion behavior in strict mode for 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 differently during DML operations (such as INSERT, UPDATE, REPLACE, and DELETE) and SELECT statements.

Core differences

Behavior when strict mode is ON

DML statements

When strict mode is enabled in the sql_mode parameter, DML statements will immediately terminate execution 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.

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

SELECT statements

When strict mode is enabled in the sql_mode parameter, SELECT statements handle data type conversion failures more leniently compared to DML statements.

Behavior characteristics:

• The statement execution does not fail due to type conversion failures.
• Failed conversion values are treated as NULL or default values.
• Warning information is generated and can be viewed using SHOW WARNINGS;.
• The query continues to execute 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, the function returns NULL.

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

Reason: SELECT is a query operation that does not modify persistent data. Its main goal is to return as much result data as possible for user verification. Strict mode in SELECT focuses more on other aspects (such as ONLY_FULL_GROUP_BY) and tends to prioritize flexibility and partial result availability when type conversions fail.

Behavior when strict mode is OFF

DML statements

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

Behavior characteristics:

• The statement execution succeeds, and warning information is output.
• Failed conversion data is replaced with the default value of the corresponding type (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 being generated and execution continuing.

Important considerations

STRICT_TRANS_TABLES vs. STRICT_ALL_TABLES

In MySQL, both modes handle type conversion failures in DML operations the same way (by returning an error). 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 regarding type conversion failures is typically the same in OceanBase Database's MySQL tenants.

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 primarily prevents those that are completely unconvertible or result in complete data loss/invalidity (such as failed string-to-number conversions, invalid dates, and inserting excessively long strings).

Explicit conversion

When using the CAST() function for explicit conversion, if the conversion fails, it will return NULL and generate a warning in any statement type and any sql_mode. This is standard SQL behavior.

Other modes

Strict mode is often set alongside other modes such as ERROR_FOR_DIVISION_BY_ZERO. These modes affect how specific errors like 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 warnings, it indicates a conversion issue in the 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 from OceanBase Database (using EXPLAIN) can provide more detailed clues.