[
https://issues.apache.org/jira/browse/CALCITE-4911?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17452913#comment-17452913
]
Stamatis Zampetakis commented on CALCITE-4911:
----------------------------------------------
I have the impression that simply casting a DECIMAL(19,2) to DECIMAL(7,2)
should not always throw an exception. If the values in the DECIMAL(19,2) column
do not fit in DECIMAL(7,2) then yes it should throw. I think this is what the
SQL standard also says.
When in doubt try to also test what other DBMS such as postgres are doing in
the same situation and add include the info in the JIRA.
> 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)
