【BUG反馈】SUM汇总在特性条件差出现4016内部错误

OceanBase
#1

【 使用环境 】生产环境
【 使用版本 】4.3.0.1

【建表语句】
CREATE TABLE sumtest (
Id int(11) NOT NULL AUTO_INCREMENT,
CardName longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
Amount1 decimal(65, 30) NULL DEFAULT NULL,
Amount2 decimal(65, 30) NULL DEFAULT NULL,
PRIMARY KEY (Id) USING BTREE
) ENGINE = oceanbase CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

【问题SQL】
SELECT
SUM( z.QiMoAmount ) AS QiMoAmount
FROM
(
SELECT
COALESCE(a.Amount1,0) - COALESCE (a.Amount2,0) AS QiMoAmount
FROM
(
SELECT
SUM( COALESCE ( Amount1, 0 ) ) AS Amount1,
SUM( COALESCE ( Amount2, 0 ) ) AS Amount2,
CardName AS CardBrandName
FROM
sumtest
GROUP BY
CardName
) AS a
WHERE
1 = 1
) AS z;

日志 (84.3 KB)

#3

报错信息日志什么的贴出来啊

#4

收到，感谢反馈。

#5

SUMBUG.zip (84.3 KB)

#6

[2024-07-08 18:41:28.750037] WDIAG [SQL.ENG] init (ob_aggregate_processor.cpp:8005) [4173][T1_L0_G0][T1][YB427F000001-00061AA71B39E7E2-0-0] [lt=14][errcode=-4016] unexpected precision(ret=-4016, arg_prec=82, res_prec=81)
[2024-07-08 18:41:28.750051] WDIAG [SQL.ENG] inner_open (ob_groupby_op.cpp:57) [4173][T1_L0_G0][T1][YB427F000001-00061AA71B39E7E2-0-0] [lt=13][errcode=-4016] failed to init(ret=-4016)
[2024-07-08 18:41:28.750057] WDIAG [SQL.ENG] inner_open (ob_scalar_aggregate_op.cpp:30) [4173][T1_L0_G0][T1][YB427F000001-00061AA71B39E7E2-0-0] [lt=5][errcode=-4016] failed to inner_open(ret=-4016)
[2024-07-08 18:41:28.750062] WDIAG [SQL.ENG] open (ob_operator.cpp:770) [4173][T1_L0_G0][T1][YB427F000001-00061AA71B39E7E2-0-0] [lt=4][errcode=-4016] Open this operator failed(ret=-4016, op_type=“PHY_SCALAR_AGGREGATE”)

#7

是什么意思呢？

#8

感谢反馈问题，已经反馈给研发老师在看，后续有进展了会及时回贴。

#9

@远航 我们当前 sumtest 表是否可以调整一下表结构或者查询的sql

该问题在后续版本会进行修复，到时候就不用采用上面的方式来规避了。

# 将 decimal(65,30) 调整为 decimal(30,15)

CREATE TABLE sumtest (
Id int(11) NOT NULL AUTO_INCREMENT,
CardName longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
Amount1 decimal(30, 15) NULL DEFAULT NULL,
Amount2 decimal(30, 15) NULL DEFAULT NULL,
PRIMARY KEY (Id) USING BTREE
) ENGINE = oceanbase CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;


或者修改查询sql：
SELECT
SUM( z.QiMoAmount ) AS QiMoAmount
FROM
(
SELECT
cast(COALESCE(a.Amount1,0) - COALESCE (a.Amount2,0) as decimal(65, 30)) AS QiMoAmount
FROM
(
SELECT
SUM( COALESCE ( Amount1, 0 ) ) AS Amount1,
SUM( COALESCE ( Amount2, 0 ) ) AS Amount2,
CardName AS CardBrandName
FROM
sumtest
GROUP BY
CardName
) AS a
WHERE
1 = 1
) AS z;
#10

可以调整，经过测试，如果CardName 不用longtext而改用varchar也能规避问题。
无论是改decimal还是longtext，我们业务是不受影响的，主要能让Oceanbase兼容性越来越好就行。

1 个赞
#11

收到，再次感谢。

#12

计划在ob432_bp1上会带上该修复。

1 个赞