After a user tenant enables strict mode in the sql_mode parameter (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
Statement type |
Strict Mode OFF |
Strict Mode ON |
Design concept |
|---|---|---|---|
| DML statements (INSERT, UPDATE, DELETE, etc.) |
Execution successful. Filled in failed data with the default value 0. Output warning information. |
Execution failed. Roll back the entire transaction. If a DML statement contains a SELECT subquery, the type conversions within the SELECT subquery also adhere to the strict mode restrictions. |
Data consistency first Reject risky data transformations in strict mode Ensure data integrity and transaction security |
| SELECT Statement (Query operation) |
Continue execution Generate a warning Return partial results or attempt conversion |
Continue execution Generate a warning Return partial results or attempt conversion |
Data query operations Priority is given to ensuring query availability Potential issues are indicated by warnings |
Behavior when Strict Mode is ON
DML statements
When the sql_mode includes strict mode, DML statements will be terminated immediately if a data type conversion fails.
Behavior:
- Statement execution failed, and a clear error message is returned.
- All modifications made by the statement are rolled back.
- Data will not be partially updated or inserted with data that does not match the target column type.
Example:
INSERT INTO t (int_col) VALUES ('abc');
-- String 'abc' cannot be converted to an integer. Error reported on failure.
UPDATE t SET date_col = '2024-02-30';
-- '2024-02-30' is an invalid date (February has no 30th), causing a failure and an error.
INSERT INTO t (varchar10_col) VALUES ('This string is way too long');
-- String length exceeds the definition of VARCHAR(10), error reported on failure
Principle: DML operations directly modify persistent data. Strict mode ensures data accuracy and consistency. Allowing erroneous type conversions may lead to data corruption or business logic violations; therefore, OceanBase AI Database chooses to fail and roll back directly when encountering dangerous conversions.
SELECT Statement
When sql_mode includes strict mode, the behavior of a SELECT statement in case of a data type conversion failure is more lenient than that of DML statements.
Behavior:
- Statement execution will not be interrupted entirely due to a type conversion failure.
- Values that fail the conversion are treated as
NULLor their default values. - Generates warning messages, which can be viewed using the
SHOW WARNINGS;statement - 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, generating a warning. It may be treated as 0 or NULL.
SELECT DATE_ADD('2024-02-30', INTERVAL 1 DAY);
-- Invalid date. A warning is issued and the function returns NULL.
SELECT CAST('abc' AS UNSIGNED);
-- Attempt to convert string to unsigned integer, warning generated, returned 0
Principle: The SELECT statement is a query operation that does not modify persistent data. Its primary goal is to return results for user inspection as efficiently as possible. In strict mode, SELECT statements place more emphasis on other aspects (such as ONLY_FULL_GROUP_BY), and tend to prioritize flexibility and partial result availability over handling type conversion failures.
Behavior when strict mode is OFF
DML statements
In non-strict mode, DML statements will continue to execute if a type conversion fails.
Behavior:
- The statement is executed successfully, and a warning message is output.
- Replace data that failed the conversion with a 0 of the corresponding type.
- Data is written to a 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 statement
In non-strict mode, the behavior of SELECT statements is similar to that in strict mode with the ON keyword: warnings are generated but execution continues.
Important considerations
STRICT_TRANS_TABLES vs STRICT_ALL_TABLES
In MySQL, these two modes are consistent in their handling of type conversion failures during DML operations (both report an error). The main difference lies in the handling of non-transactional tables (such as MyISAM). Since OceanBase's underlying transaction engine behaves identically under different sql_mode settings, in a user tenant of OceanBase AI Database, STRICT_TRANS_TABLES and STRICT_ALL_TABLES typically result in the same behavior for type conversion failures.
Implicit conversion rules
Even when no conversion fails, implicit conversions can lead to precision loss or semantic changes. Strict mode does not prevent all implicit conversions; it primarily prevents those that are impossible to perform or would result in complete data loss or invalidity (such as converting a string to a number failing, invalid dates, or inserting excessively long strings).
Explicit conversion
When using the CAST() function for explicit conversion, if the conversion fails, it returns NULL and generates a warning in any statement type and under any sql_mode. This is standard SQL behavior.
Other modes
Strict mode is typically set together with other modes, such as ERROR_FOR_DIVISION_BY_ZERO. These modes affect the handling of specific errors, such as division by zero and invalid dates.
Troubleshooting
Error (
ERROR) vs Warning (WARNING): A client return ofERRORindicates that the DML statement failed completely. Returning a result set but with warnings means a conversion issue occurred in theSELECTstatement.Check
sql_mode: UseSELECT @@sql_mode;to confirm whether strict mode is enabled.View execution plans and logs: For complex cases, the execution plan output (
EXPLAIN) in OceanBase AI Database can provide more detailed clues.