Data type conversion behavior in MySQL tenants in strict mode

When the sql_mode parameter is set to strict mode (by specifying STRICT_TRANS_TABLES or STRICT_ALL_TABLES), data type conversion failures during DML statements (such as INSERT, UPDATE, REPLACE, and DELETE) and SELECT statements are handled differently.

Core differences

Behavior when strict mode is ON

DML statements

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

Behavior characteristics:

• The statement execution fails, and a clear error message is returned.
• All modifications made by the statement are rolled back.
• Data is not partially updated or inserted as data 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, 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 length exceeds the VARCHAR(10) definition, and an error is returned.

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

SELECT statements

When sql_mode is set to strict mode, SELECT statements are more lenient in handling data type conversion failures 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.
• A warning is generated, which 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, a warning is generated, and it may be treated as 0 or NULL.

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

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

Principle: 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 inspection. Strict mode focuses more on other aspects (such as ONLY_FULL_GROUP_BY) in SELECT and tends to prioritize flexibility and partial result availability for type conversion failures.

Behavior when strict mode is OFF

DML statements

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

Behavior characteristics:

• The statement execution succeeds, and a warning is output.
• The failed conversion data is replaced with the default value of the corresponding type (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 when strict mode is ON, generating warnings and continuing to execute.

Important considerations

STRICT_TRANS_TABLES vs. STRICT_ALL_TABLES

In MySQL, these two modes behave similarly in DML statements for type conversion failures (both return 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, STRICT_TRANS_TABLES and STRICT_ALL_TABLES typically behave the same in MySQL tenants of OceanBase Database regarding type conversion failures.

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 but mainly prevents those that are completely unconvertible or result in complete data loss/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 setting. This is standard SQL behavior.

Other modes

Strict mode is often used in conjunction 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 ERROR, the DML statement has completely failed. If a result set is returned but a warning exists, 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, OceanBase Database's execution plan output (EXPLAIN) can provide more detailed insights.