【 使用环境 】生产环境
【 OB or 其他组件 】OB
【 使用版本 】5.7.25-OceanBase_CE-v4.3.5.3
【问题描述】在更新数据时null类型错误导致无法更新
【复现路径】在更新数据时，对datetime字段中插入NULL报错
使用case when 把字符串转为NULL，插入datetime字段中报错，但改为null时不再报错。
dbeaver显示，转为NULL的值为字符型[NULL], 转为NULL的值为不确定（？）类型的null。
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：
麻烦提供一下表结构及问题sql
CREATE TABLE table1 (
sfk_id bigint(20) NOT NULL AUTO_INCREMENT ,
col1 datetime DEFAULT NULL
) ORGANIZATION INDEX AUTO_INCREMENT = 4849591 AUTO_INCREMENT_MODE = ‘ORDER’ DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0;
#更新语句，发生报错
UPDATE table1 SET col1 = (select CASE WHEN col1 in (’\N’,’’) THEN NULL
WHEN LENGTH(col1) = 7 THEN DATE_FORMAT(CONCAT(col1, ‘-01’), ‘%Y-%m-%d %H:%i:%s’)
ELSE DATE_FORMAT(col1, ‘%Y-%m-%d %H:%i:%s’)
END as col1 from table2 where id = 11913215) WHERE sfk_id = 1;
#更新语句，不再报错
UPDATE table1 SET col1 = (select CASE WHEN col1 in (’\N’,’’) THEN null
WHEN LENGTH(col1) = 7 THEN DATE_FORMAT(CONCAT(col1, ‘-01’), ‘%Y-%m-%d %H:%i:%s’)
ELSE DATE_FORMAT(col1, ‘%Y-%m-%d %H:%i:%s’)
END as col1 from table2 where id = 11913215) WHERE sfk_id = 1;
测了下用企业版4.2.1.11在命令行和DBeaver都没报错，可能确实是版本问题
Server version: OceanBase 4.2.1.11 (r111010032025041110-c619217ab98c90c0883191ec778f7ba4a2f5566e) (Built Apr 11 2025 10:44:02)
少一张table2表的建表sql
重新安装吗
学习了
CREATE TABLE table2 (
id int(10) unsigned NOT NULL ,
col1 varchar(100) DEFAULT NULL,
PRIMARY KEY (id)
) ORGANIZATION INDEX AUTO_INCREMENT = 29185147 AUTO_INCREMENT_MODE = ‘ORDER’ DEFAULT CHARSET = utf8mb4 ROW_FORMAT = COMPRESSED COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0;
insert into table2 (id,col1)
value(11913215,’\N’)
可以贴一下dbeaver的报错
SQL 错误 [1292] [22001]: Data truncation: Incorrect datetime value for column ‘col1’ at row 1
关注一下
