Investigation of Implicit Type Conversion Issues in OceanBase Update and Select Statements

Author: Ren Zhongyu, member of the Aikexing DBA team, specializes in fault analysis and performance optimization.

Original source: Original submission.

1 Problem Description

A confusing data‑type implicit conversion issue was encountered while using OceanBase. The conclusion is simple, and the troubleshooting steps are shared here.

Issue 1: Error Statement

The client project occasionally fails when executing an UPDATE statement, reporting the following error (masked):

ERROR bad SQL grammar [update renzy set at=current_timestamp,expire_at=(cast(unix_timestamp(current_timestamp(3) as unsigned) +?)), order_id= ? where id = ? and (expire_at < current_timestamp or order_id = ?)]  java.sql.SQLSyntaxErrorException: (conn=1277168) Incorrect value.

Check OceanBase version:

./observer -V
observer (OceanBase 3.2.3.2)
REVISION: 105000092022092216-445151f0edb502e00ae5839dfd92627816b2b822

Show table structure and data:

MySQL [test]> show create table renzy\G
*************************** 1. row ***************************
Table: renzy
Create Table: CREATE TABLE `renzy` (
  `id` varchar(64) NOT NULL,
  `at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `order_id` varchar(64) NOT NULL,
  `expire_at` bigint(20) NOT NULL,
  `vt` timestamp NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMPRESSION='zstd_1.3.8' REPLICA_NUM=3 BLOCK_SIZE=16384 USE_BLOOM_FILTER=FALSE TABLET_SIZE=134217728 PCTFREE=0

MySQL [test]> select * from renzy;
+----+---------------------+---------------------+------------+---------------------+
| id | at                  | order_id            | expire_at  | vt                  |
+----+---------------------+---------------------+------------+---------------------+
| 1  | 2023-07-07 14:57:13 | 0:[email protected]       | 1716040750 | 2023-07-07 14:57:13 |
+----+---------------------+---------------------+------------+---------------------+
1 row in set (0.02 sec)

2 Troubleshooting

Issue 1: Error Statement

Execute the failing SQL directly:

update renzy set at=CURRENT_TIMESTAMP, expire_at=(cast(unix_timestamp(current_timestamp(3)) as unsigned) + 30000000), order_id='0:[email protected]' where id='1' and (expire_at < CURRENT_TIMESTAMP or order_id='0:[email protected]')
ERROR 1292 (22007): Incorrect values.

Issue 2

If the primary‑key ID does not match any row, the UPDATE statement does not raise an error.

# table does not have id=2 data
update renzy set acquired_at=CURRENT_TIMESTAMP, expire_at=(cast(unix_timestamp(current_timestamp(3)) as unsigned) + 30000000), order_id='0:[email protected]' where id='2' and (expire_at < CURRENT_TIMESTAMP or order_id='0:[email protected]')
Query OK.

SQL audit output and logs

Analyzing sql$gv_audit reveals:

The statement plan is a local plan dispatched to node 0.71.

Error code 4219 indicates an invalid datetime value.

MySQL [oceanbase]> select trace_id,svr_ip,ret_code,retry_cnt,usec_to_time(request_time),elapsed_time,execute_time,plan_type,query_sql from gv$sql_audit where query_sql like 'update id%' and ret_code != 0 order by request_time desc limit 5;
+-----------------------------------+-------------+----------+-----------+----------------------------+--------------+--------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| trace_id                          | svr_ip      | ret_code | retry_cnt | usec_to_time(request_time) | elapsed_time | execute_time | plan_type | query_sql                                                                                                                                                                                                                                                                               |
+-----------------------------------+-------------+----------+-----------+----------------------------+--------------+--------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| YB420CF10047-0005FBCCEF6E3635-0-0| 12.241.0.71 | -4219    | 0         | 2023-06-06 15:32:08.375051 | 689          | 611          | 1         | update id  set acquired_at=CURRENT_TIMESTAMP, expire_at=(cast(unix_timestamp(current_timestamp(3)) as unsigned) + 30000000), order_id='0:[email protected]' where id='2' and (expire_at < CURRENT_TIMESTAMP or order_id='0:[email protected]') |
·····
5 rows in set (5.21 sec)

Key error logs

#grep YB420CF10047-0005FBCCEF6E3635-0-0 observer.log.20230606153309
[2023-06-06 15:32:08.375202] WARN  [LIB.TIME] int_to_ob_time_with_date (ob_time_convert.cpp:1618) ... datetime is invalid or out of range(ret=-4219, int64=0)
[2023-06-06 15:32:08.375211] WARN  [LIB.TIME] int_to_datetime (ob_time_convert.cpp:329) ... failed to convert integer to datetime(ret=-4219)
... (additional similar warnings) ...

The first warning originates from int_to_ob_time_with_date , indicating an invalid datetime.

3 OceanBase Implicit Conversion

Conversion between bigint and datetime cannot be compared directly; an integer must be transformed into a time type using OceanBase’s internal logic, not the MySQL from_unixtime function.

Supported implicit conversion formats are YYYYMMDD and YYYYMMDDHHMMSS .

Source code excerpt for int_to_datetime :

int ObTimeConverter::int_to_datetime(int64_t int_part, int64_t dec_part, const ObTimeConvertCtx &cvrt_ctx, int64_t &value, const ObDateSqlMode date_sql_mode) {
  int ret = OB_SUCCESS;
  dec_part = (dec_part + 500) / 1000;
  if (0 == int_part) {
    value = ZERO_DATETIME;
  } else {
    ObTime ob_time(DT_TYPE_DATETIME);
    ObDateSqlMode local_date_sql_mode = date_sql_mode;
    if (cvrt_ctx.is_timestamp_) {
      local_date_sql_mode.allow_invalid_dates_ = false;
    }
    if (OB_FAIL(int_to_ob_time_with_date(int_part, ob_time, false, local_date_sql_mode))) {
      LOG_WARN("failed to convert integer to datetime", K(ret));
    } else if (OB_FAIL(ob_time_to_datetime(ob_time, cvrt_ctx, value))) {
      LOG_WARN("failed to convert datetime to seconds", K(ret));
    }
  }
  value += dec_part;
  if (OB_SUCC(ret) && !is_valid_datetime(value)) {
    ret = OB_DATETIME_FUNCTION_OVERFLOW;
    LOG_WARN("datetime filed overflow", K(ret), K(value));
  }
  return ret;
}

Further code for int_to_ob_time_with_date shows the validation logic and the range checks that cause the error.

int ObTimeConverter::int_to_ob_time_with_date(int64_t int64, ObTime &ob_time, bool is_dayofmonth, const ObDateSqlMode date_sql_mode) {
  int ret = OB_SUCCESS;
  int32_t *parts = ob_time.parts_;
  if (is_dayofmonth && 0 == int64) {
    // set all parts to zero
  } else if (int64 < power_of_10[2]) {
    ret = OB_INVALID_DATE_VALUE;
    LOG_WARN("datetime integer is out of range", K(ret), K(int64));
  } else if (int64 < power_of_10[8]) {
    // YYYYMMDD format handling
  } else if (int64 / power_of_10[6] < power_of_10[8]) {
    // YYYYMMDDHHMMSS format handling
  } else {
    ret = OB_INVALID_DATE_VALUE;
    LOG_WARN("datetime integer is out of range", K(ret), K(int64));
  }
  if (OB_SUCC(ret)) {
    apply_date_year2_rule(parts[0]);
    if (OB_FAIL(validate_datetime(ob_time, is_dayofmonth, date_sql_mode))) {
      LOG_WARN("datetime is invalid or out of range", K(ret), K(int64));
    } else if (ZERO_DATE != parts[DT_DATE]) {
      parts[DT_DATE] = ob_time_to_date(ob_time);
    }
  }
  return ret;
}

Only the two formats above are recognized; any other integer representation triggers the “datetime integer is out of range” warning.

4 Root Causes

Issue 3: SELECT returns unexpected result

The bigint value 1716040750 stored in EXPIRE_AT cannot be directly compared with CURRENT_TIMESTAMP . OceanBase attempts to convert the integer using the rules above, fails, and substitutes a default value (0), which satisfies the WHERE clause 0 < 1686042749 , causing the row to be returned.

Issue 1: UPDATE throws error

OceanBase enables strict SQL_MODE by default. When an implicit conversion fails during UPDATE, the strict mode blocks execution and returns an error, unlike MySQL which may allow the operation.

Issue 2: UPDATE with non‑existent primary key does not error

The UPDATE operator first performs a table‑get; if the primary‑key lookup yields no rows, the subsequent filter is skipped, so no error is raised.

Why UPDATE errors while SELECT shows “wrong” values

UPDATE follows the call chain common_int_datetime → int_to_datetime → int_to_ob_time_with_date and respects strict mode, whereas SELECT follows only int_to_datetime → int_to_ob_time_with_date and does not enforce the same strictness.

MySQL [test]> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_ALL_TABLES,NO_ZERO_IN_DATE          |
+--------------------------------------------+
1 row in set (0.01 sec)

MySQL [test]> update renzy set at=CURRENT_TIMESTAMP, expire_at=(cast(unix_timestamp(current_timestamp(3)) as unsigned) + 30000000), order_id='0:[email protected]' where id='1' and (expire_at < CURRENT_TIMESTAMP or order_id='0:[email protected]');
ERROR 1292 (22007): Incorrect value

MySQL [test]> set sql_mode='';
Query OK, 0 rows affected (0.01 sec)

MySQL [test]> update renzy set at=CURRENT_TIMESTAMP, expire_at=(cast(unix_timestamp(current_timestamp(3)) as unsigned) + 30000000), order_id='0:[email protected]' where id='1' and (expire_at < CURRENT_TIMESTAMP or order_id='0:[email protected]');
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

5 Conclusion

Ask the client project to rewrite the SQL logic. Change EXPIRE_AT < CURRENT_TIMESTAMP to EXPIRE_AT < unix_timestamp(CURRENT_TIMESTAMP) .

Emphasize SQL best practices to avoid implicit conversions.

Keywords: #OceanBase #DataType #ImplicitConversion