本文作者分享了Oceanbase时间与数值类型隐式转换导致查询结果不符合预期或“不正确”问题的排查思路。
作者：任仲禹
爱可生 DBA 团队成员，擅长故障分析和性能优化，文章相关技术问题，欢迎大家一起讨论。
本文来源：原创投稿
- 爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
之前在 OceanBase 使用中碰到了一个“令人费解”的数据类型隐式转换问题。结论比较简单，特跟大家分享下排查思路。
问题描述
某客户项目组执行更新 SQL 语句时会偶发失败，报错如下：
脱敏处理后
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.
查询 OceanBase 版本。
./observer -V
observer (OceanBase 3.2.3.2)
REVISION: 105000092022092216-445151f0edb502e00ae5839dfd92627816b2b822
查看表结构和数据。
MySQL [test]> show create table renzyG
*************************** 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:16632@172.1 | 1716040750 | 2023-07-07 14:57:13 |
+----+---------------------------+---------------+------------+---------------------------------+
1 row in set (0.02 sec)
问题排查
问题 1：报错语句
直接执行报错的 SQL 语句。
update renzy set at=CURRENT_TIMESTAMP, expire_at=(cast(unix_timestamp(current_timestamp(3)) as unsigned) + 30000000), order_id= '0:16632@172.24.64.1' where and (expire_at int_to_datetime -> int_to_ob_time_with_date
问题 3：尝试使用 SELECT 查询结果
表中 EXPIRE_AT 存储的是未来时间（1716040750），与当前时间（1686042749）做比较，查询结果理应不输出结果才对？（先记录为问题 3：查询结果不符合预期）
# 表中只有 1 行记录，且 EXPIRE_AT 的值为 1716040750
MySQL [mock_db]> select * from renzy where EXPIRE_AT select unix_timestamp(CURRENT_TIMESTAMP);
+--------------------------------------------+
| unix_timestamp(CURRENT_TIMESTAMP) |
+--------------------------------------------+
| 1686042749 |
+--------------------------------------------+
1 row in set (0.03 sec)
SELECT 查询的报错日志。
[2023-06-06 17:08:54.307371] WARN [LIB.TIME] int_to_ob_time_with_date (ob_time_convert.cpp:1618) [38763][0][YB420CF10047-0005FBCCEF6F9E6D-0-0] [lt=10] [dc=0] datetime is invalid or out of range(ret=-4219, int64=0)
[2023-06-06 17:08:54.307382] WARN [LIB.TIME] int_to_datetime (ob_time_convert.cpp:329) [38763][0][YB420CF10047-0005FBCCEF6F9E6D-0-0] [lt=10] [dc=0] failed to convert integer to datetime(ret=-4219)
这里 SELECT 的调用链路：int_to_datetime -> int_to_ob_time_with_date
以上就是存疑的几个问题，那在具体分析前，先了解一下前置知识点：OceanBase 的隐式转换。
OceanBase 的隐式转换
数据类型 bigint 与 datetime 的值是没法直接比较，需要先将 int 转换为时间类型，这就是所谓的隐式转换，所以这里 OceanBase 是如何转很重要。
-
int 类型转换成 OceanBase 认可的时间类型（即 OBTime）并不是用的 from_unixtime 这个函数，而是用的 OceanBase 自己内部的逻辑。
-
源码中涉及 int，double，string 类型隐式转换的逻辑如下：
int_to_datetime
////////////////////////////////
// int / double / string -> datetime / date / time / year.
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;
}
最终调用的 int_to_ob_time_with_date：
////////////////////////////////
// int / uint / string -> ObTime / ObInterval int_to_datetime -> int_to_ob_time_with_date
SELECT: int_to_datetime -> int_to_ob_time_with_date
UPDATE 多了上层的 common_int_datetime，调用入口不一样，而根据这个方法，大致猜测是因为 SQL_MODE 的严格模式导致。
验证下猜想，看起来清空 SQL_MODE 后，UPDATE 果然能插入成功。
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:16632@172.24.64.1' where and (expire_at 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:16632@172.24.64.1' where and (expire_at
