[jira] [Comment Edited] (CALCITE-4911) CAST operation should throw an exception when cast DECIMAL type lose significant digits

["duan xiong (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17452683#comment-17452683 ] 

duan xiong edited comment on CALCITE-4911 at 12/3/21, 9:06 AM:
---------------------------------------------------------------

According to the implementation, RelDataTypeSystemImpl to [derive the avg type|https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeSystemImpl.java#L241] According to the argument type. Maybe we need to derive the avg type(When The SUM operator return decimal) by 
{code:java}
deriveDecimalDivideType(typeFactory,
    deriveSumType(typeFactory,argumentType),
    typeFactory.createSqlType(SqlTypeName.BIGINT)); {code}
To stay consistent with AggregateReduceFunctionsRule. 


was (Author: nobigo):
According to the implementation, RelDataTypeSystemImpl to [derive the avg type|https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/rel/type/RelDataTypeSystemImpl.java#L241] According to the argument type. Maybe we need to derive the avg type by 
{code:java}
return deriveDecimalDivideType(typeFactory,
    deriveSumType(typeFactory,argumentType),
    typeFactory.createSqlType(SqlTypeName.BIGINT)); {code}
To stay consistent with AggregateReduceFunctionsRule. 

> CAST operation should throw an exception when cast DECIMAL type lose significant digits
> ---------------------------------------------------------------------------------------
>
>                 Key: CALCITE-4911
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4911
>             Project: Calcite
>          Issue Type: Bug
>    Affects Versions: 1.28.0
>            Reporter: duan xiong
>            Priority: Major
>
> In Calcite:
> {code:java}
> select avg(comm) as a from "scott".emp{code}
> This SQL physical plan is:
> {noformat}
> EnumerableCalc(expr#0..1=[{inputs}], expr#2=[0], expr#3=[=($t1, $t2)], expr#4=[null:DECIMAL(19, 2)], expr#5=[CASE($t3, $t4, $t0)], expr#6=[CAST($t5):DECIMAL(7, 2)], expr#7=[/($t6, $t1)], expr#8=[CAST($t7):DECIMAL(7, 2)], A=[$t8])
>  EnumerableAggregate(group=[{}], agg#0=[$SUM0($6)], agg#1=[COUNT($6)])
>    EnumerableTableScan(table=[[scott, EMP]]){noformat}
> As above:
> The Calcite tries to cast DECIMAL(19,2) to DECIMAL(7,2). Actually, this cast should throw an exception but not.[digits number from 17 to 5]
> According to SQL Standard's description about <CAST specification>:
> If TD is exact numeric, then
> Case:
> a) If SD is exact numeric or approximate numeric, then
> Case:
> i) If there is a representation of SV in the data type *TD that does not lose any leading significant digits* after rounding or truncating if necessary, then TV is that representation. The choice of whether to round or truncate is implementation-defined.
> ii) Otherwise, {*}an exception condition is raised{*}: data exception------numeric value out of range.



--
This message was sent by Atlassian Jira
(v8.20.1#820001)