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

When a MySQL-compatible tenant is set to strict mode by enabling the STRICT_TRANS_TABLES or STRICT_ALL_TABLES mode 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 that encounter data type conversion failures will immediately terminate.

Behavior characteristics:

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

Example:

INSERT INTO t (int_col) VALUES ('abc');
-- The string 'abc' cannot be converted to an integer, and the operation fails.

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

INSERT INTO t (varchar10_col) VALUES ('This string is way too long');
-- The string length exceeds the VARCHAR(10) definition, and the operation fails.

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 strict mode is enabled in the sql_mode parameter, SELECT statements handle data type conversion failures more leniently than 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 messages are generated and can be viewed using SHOW WARNINGS;.
• The query continues to execute and returns the result set.

Example:

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);
-- 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. In strict mode, SELECT statements focus more on other aspects (such as ONLY_FULL_GROUP_BY) and 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 to execute even if type conversion fails.

Behavior characteristics:

• The statement execution succeeds, and warning messages are output.
• Failed conversion data is replaced with the default value 0 of the corresponding type.
• 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, SELECT statements behave similarly to when strict mode is ON, generating warnings and continuing execution.

Important considerations

STRICT_TRANS_TABLES vs. STRICT_ALL_TABLES

In MySQL, both modes handle type conversion failures in DML operations similarly (by reporting 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 way in MySQL-compatible tenants 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; it mainly prevents those that are completely unconvertible or result in complete data loss/invalidity (such as failed string-to-number conversions, invalid dates, or 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 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 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, OceanBase Database's execution plan output (EXPLAIN) can provide more detailed clues.