[Bug] Hive外部表读取Orc表结构的decimal数据类型时，数据不一致

[shuxian6688]

Search before asking

• I had searched in the issues and found no similar issues.

Version

Doris: doris-2.0.2-rc05-ae923f7
Hive: 2.1

What's Wrong?

使用hive外部表方式读取hive中表数据，表结构为Orc结构，数据类型为Decimal 类型进行聚合时，发现在doris中精度丢失，然后导致两边数据不一致

What You Expected?

doris中读取出来的数据一致

How to Reproduce?

No response

Anything Else?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[shuxian6688]

[Yulei-Yang]

I will look into it

[Yulei-Yang]

pls show me the creation stmt of catalog 'jdbc_hive_catalog' @shuxian6688

[shuxian6688]

CREATE CATALOG jdbc_hive_catalog PROPERTIES (
'type'='hms',
'hive.metastore.uris' = 'thrift://xxxx.xxx.xxx.xx:9083',
'hive.version'='2.1.1'
);

[Yulei-Yang]

cannot reproduce in latest 2.0 doris。you can verify latest 2.0 with your data or check below case in your current environment:
run it in hive:
CREATE TABLE orc_d(
id int,
name string,
account decimal(10,0))
stored as orc;
insert overwrite table orc_d values (1, 'Amy', 10.11),(2, 'Bob', 20.22),(3, 'Carl', 30.33);

and run select sum(account) from orc_d; in doris to do a check

[shuxian6688]

你上面的这个表我测试了，没问题，但是当我使用分区表测试就会出现结果不一致的情况，测试结果发现在doris2.02中得到的聚合结果有时比Hive中的结果小100倍，有时10000倍。但是如果直接聚合没有条件的话,结果又是一致的。 下面是我的测试过程

CREATE TABLE dw_app.app_acc_mngreport_bu_netprofit_check_monthly_test(
busi_quarter string COMMENT '业务季度',
plate_code string COMMENT '板块编码',
plate_name string COMMENT '板块名称',
bu_code string COMMENT '事业部编码',
bu_name string COMMENT '事业部名称',
dept_code string COMMENT '部门编码',
dept_name string COMMENT '部门名称',
thismon_erp decimal(28,4) COMMENT 'ERP毛利'
)
COMMENT ''
PARTITIONED BY (
busi_month string COMMENT '业务月度')
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'

insert overwrite dw_app.app_acc_mngreport_bu_netprofit_check_monthly_test partition(busi_month)
('2023-Q3','10','MGM','11','xxxxx','1112','xxxxx',-884810.0500,'2023-08'),
('2023-Q3','08','MCD','19','xxxxx','4305','xxxxx', 1404447.0000,'2023-08'),
('2023-Q3','06','MWT','46','xxxxx','1526','xxxxx', 898690.8300 ,'2023-08'),
('2023-Q3','10','MGM','66','xxxxx','6605','xxxxx', -82070.1672,'2023-08'),
('2023-Q3','02','MHA','02','xxxxx','02','xxxxx',0.0000,'2023-08'),
('2023-Q3','13','MHS','61','xxxxx','61','xxxxx',0.0000,'2023-09'),
('2023-Q3','10','MGM','11','xxxxx','11','xxxxx',0.0000,'2023-09'),
('2023-Q3','10','MGM','66','xxxxx','6606','xxxxx', 369567.8575,'2023-09'),
('2023-Q3','06','MWT','39','xxxxx','39','xxxxx',0.0000,'2023-09'),
('2023-Q3','05','MGF','83','xxxxx','8304','xxxxx',7171083.9000,'2023-09'),
('2023-Q4','06','MWT','69','xxxxx','1518','xxxxx',3830683.2600,'2023-10'),
('2023-Q4','12','MFA','06','xxxxx','0623','xxxxx',0.0000,'2023-10'),
('2023-Q4','06','MWT','46','xxxxx','1526','xxxxx',757784.4800,'2023-10'),
('2023-Q4','05','MGF','49','xxxxx','1215','xxxxx',592047.6100,'2023-10'),
('2023-Q4','08','MCD','19','xxxxx','1418','xxxxx',0.0000,'2023-10')

下面的聚合结果为：4422730.8700
select sum(thismon_erp) as thismon_erp from dw_app.app_acc_mngreport_bu_netprofit_check_monthly_test where busi_month= '2023-10' and dept_code in ('1518','1215') -- sum = 4422730.8700

CREATE CATALOG jdbc_hive_catalog PROPERTIES (
'type'='hms',
'hive.metastore.uris' = 'thrift://xxxx.xxx.xxx.xx:9083',
'hive.version'='2.1.1'
);

下面的聚合结果为：44227.3087
select sum(thismon_erp) as thismon_erp from jdbc_hive_catalog.dw_app.app_acc_mngreport_bu_netprofit_check_monthly_test where busi_month= '2023-10' and dept_code in ('1518','1215') -- sum = 44227.3087

[shuxian6688]

[Yulei-Yang]

latest v2.0 has already fixed it. Check #26548, #25977, #26633, #27872

[github-actions]

We're closing this issue because it hasn't been updated in a while.
If you'd like to revive this issue, please reopen it and feel free a maintainer to remove the Stale tag!