[
https://issues.apache.org/jira/browse/CALCITE-4911?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
duan xiong updated CALCITE-4911:
--------------------------------
Description:
In Calcite:
{code:java}
select avg(comm) as a from "scott".emp{code}
This SQL physical plan is:
{noformat}
EnumerableProject(A=[CAST(/(CAST(CASE(=($1, 0), null:DECIMAL(19, 2),
$0)):DECIMAL(7, 2), $1)):DECIMAL(7, 2)])
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.
was:
In Calcite:
{code:java}
select avg(comm) as a from "scott".emp{code}
This SQL physical plan is:
{noformat}
EnumerableProject(A=[CAST(/(CAST(CASE(=($1, 0), null:DECIMAL(19, 2),
$0)):DECIMAL(7, 2), $1)):DECIMAL(7, 2)])
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.
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.
> 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}
> EnumerableProject(A=[CAST(/(CAST(CASE(=($1, 0), null:DECIMAL(19, 2),
> $0)):DECIMAL(7, 2), $1)):DECIMAL(7, 2)])
> 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)
