[
https://issues.apache.org/jira/browse/CALCITE-5838?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17742225#comment-17742225
]
pengfei.zhan commented on CALCITE-5838:
---------------------------------------
Yes, this is a solution to the problem.
But in most scenarios, this algorithm from calcite looks not very elegant.
{code:java}
int dout =Math.min( p1 - s1 + s2, maxNumericPrecision);
int scale = Math.max(6, s1 + p2 + 1);
scale = Math.min(scale,maxNumericPrecision - dout);
scale = Math.min(scale, getMaxNumericScale());
{code}
In most cases, if p1 = 38 and s1 < s2, then dout equals to maxNumericPrecision
with no doubt, which for hive is 38. According to `scale =
Math.min(scale,maxNumericPrecision - dout);` This will always produce a scale
of 0. With `scale = Math.min(scale, getMaxNumericScale());` the final scale
will be 0. For a decimal datatype, this looks unacceptable.
Of course, if the numerical integers involved in the computation are indeed
very large, then the final computation will inevitably result in an overflow.
> derive wrong decimal divide type
> --------------------------------
>
> Key: CALCITE-5838
> URL: https://issues.apache.org/jira/browse/CALCITE-5838
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.34.0
> Reporter: pengfei.zhan
> Priority: Major
>
> Given a table metadata structure as follows:
> {code:sql}
> create table `tbl_test`(`CRR` decimal(38,2), `DT` varchar(4096)) ROW FORMAT
> DELIMITED FIELDS TERMINATED BY '\t';
> {code}
> If the following query is executed,
> {code:sql}
> select sum(CRR_0/1000000000.02) from (select sum(CRR) as CRR_0 from tbl_test
> where DT='20230705') T1
> {code}
> The RexCall CRR_0/1000000000.02 derived a decimal type of decimal(38, 0). I
> referred the derived type in spark, but it gives decimal(38, 4). It looks
> like the inferred type is more reasonable.
> [hive doc |
> https://cwiki.apache.org/confluence/download/attachments/27362075/Hive_Decimal_Precision_Scale_Support.pdf]
> [sql server doc | https://msdn.microsoft.com/en-us/library/ms190476.aspx]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
