doris2.0.14 enable_nereids_planner为true时decimal精度丢失

Viewed 35
2.0

计算语句，真实场景：select cast(0.001337850000 as decimalv3(38,12)) * cast(34750.000000 as decimalv3(38,6))

set session enable_nereids_planner = true; 可以改变行为，且false的模式是符合预期：
image.png

下面是测试结果和对比：
image.png
image.png
select cast(0.001337850000 as decimalv3(38,12)) * cast(34750.000000 as decimalv3(38,6))
结果四舍五入了
2.0和1.2的结果不一样。我理解是两个值超过了界限么？

我们调整check_overflow_for_decimal 和 decimal_overflow_scale参数也不生效，如果decimal_overflow_scale的默认值6改成18，我觉得也可以解决我们的问题

image.png

测试案例：
create table tb_dbainfo(
cl_d1 decimalV3(38,18) NULL,
cl_d2 decimalV3(38,18) null,
cl_string string null
)ENGINE=OLAP
DUPLICATE KEY(cl_d1)
DISTRIBUTED BY HASH(cl_d1) BUCKETS 8
PROPERTIES (
"replication_allocation" = "tag.location.default: 3"
);

INSERT INTO tb_dbainfo (cl_d1,cl_d2,cl_string) VALUES('1.111111111111111111','9999999999.999999999999999999','999999999999999999.999999999999999999');
INSERT INTO tb_dbainfo (cl_d1,cl_d2,cl_string) VALUES('1.222222222222222222','9999999999.999999999999999999','999999999999999999.999999999999999999');
INSERT INTO tb_dbainfo (cl_d1,cl_d2,cl_string) VALUES('1.333333333333333333','9999999999.999999999999999999','999999999999999999.999999999999999999');
INSERT INTO tb_dbainfo (cl_d1,cl_d2,cl_string) VALUES('1.444444444444444444','9999999999.999999999999999999','999999999999999999.999999999999999999');
INSERT INTO tb_dbainfo (cl_d1,cl_d2,cl_string) VALUES('1.555555555555555555','9999999999.999999999999999999','999999999999999999.999999999999999999');
INSERT INTO tb_dbainfo (cl_d1,cl_d2,cl_string) VALUES('1.666666666666666666','9999999999.999999999999999999','999999999999999999.999999999999999999');
INSERT INTO tb_dbainfo (cl_d1,cl_d2,cl_string) VALUES('1.777777777777777777','9999999999.999999999999999999','999999999999999999.999999999999999999');
INSERT INTO tb_dbainfo (cl_d1,cl_d2,cl_string) VALUES('1.888888888888888888','9999999999.999999999999999999','999999999999999999.999999999999999999');
INSERT INTO tb_dbainfo (cl_d1,cl_d2,cl_string) VALUES('1.999999999999999999','9999999999.999999999999999999','999999999999999999.999999999999999999');

select cl_d1,cl_d2,cl_d1+cl_d2,cl_d1*cl_d2,cl_d2-cl_d1,cl_d2/cl_d1 from tb_dbainfo order by cl_d1 asc;

dbaops3
1 Answers

升级2.1吧，2.0上的decimal规则不会去动了。旧优化器的行为明显更不合理。保留了太多小数部分，按照输入为38，12和38,18 计算，整数部分极容易溢出。
2.1可以调整decimal_overflow_scale来决定保留多少小数部分。

徐振超@SelectDB6501