[
https://issues.apache.org/jira/browse/CALCITE-2030?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-2030:
---------------------------------
Description:
As described at CALCITE-1945 , aggregates are rounded to their input types.
PostgreSQL has a commonly-used DECIMAL type with unusual behavior for
unspecified precision: the precision is stored with the same number of decimals
as the input number. However, the JDBC driver reports the precision of the
column to be 0, which causes Calcite's rounding logic to perform queries like:
{code}SELECT CAST(SUM("price") / COUNT(*) AS DECIMAL(19, 0))
FROM "products"
GROUP BY “category"{code}
This will cause in the average price result to be rounded to an integer. One
solution would be to just cast it to a "DECIMAL" instead of a "DECIMAL(19, 0)"
in {{PostgresqlSqlDialect.getCastSpec(RelDataType)}}.
A possible alternative solution that wouldn't be DB-specific could be to just
always cast to the SQL TYPE_NAME for the column, this making it so Calcite
doesn't need to perform any precision logic itself. That is, if a database
reports a column's TYPE_NAME is "CrAzY_nUmBeR", then Calcite's aggregate
casting logic would simple execute:
{code}SELECT CAST(SUM("price") / COUNT(*) AS CrAzY_nUmBeR)
FROM "products"
GROUP BY “category"{code}
More discussion of the issue can be seen at:
https://mail-archives.apache.org/mod_mbox/calcite-dev/201711.mbox/%[email protected]%3e
was:
As described at https://issues.apache.org/jira/browse/CALCITE-1945 , aggregates
are rounded to their input types. PostgreSQL has a commonly-used DECIMAL type
with unusual behavior for unspecified precision: the precision is stored with
the same number of decimals as the input number. However, the JDBC driver
reports the precision of the column to be 0, which causes Calcite's rounding
logic to perform queries like:
{{SELECT CAST(SUM("price") / COUNT(*) AS DECIMAL(19, 0)) FROM "products" GROUP
BY “category"}}
This will cause in the average price result to be rounded to an integer. One
solution would be to just cast it to a "DECIMAL" instead of a "DECIMAL(19, 0)"
in PostgresqlSqlDialect.getCastSpec(RelDataType).
A possible alternative solution that wouldn't be DB-specific could be to just
always cast to the SQL TYPE_NAME for the column, this making it so Calcite
doesn't need to perform any precision logic itself. That is, if a database
reports a column's TYPE_NAME is "CrAzY_nUmBeR", then Calcite's aggregate
casting logic would simple execute:
{{SELECT CAST(SUM("price") / COUNT(*) AS CrAzY_nUmBeR) FROM "products" GROUP BY
“category"}}
More discussion of the issue can be seen at:
https://mail-archives.apache.org/mod_mbox/calcite-dev/201711.mbox/%[email protected]%3e
See also: https://issues.apache.org/jira/browse/CALCITE-1945
> Aggregates executed against PostgreSQL DECIMAL columns perform lossy casts
> --------------------------------------------------------------------------
>
> Key: CALCITE-2030
> URL: https://issues.apache.org/jira/browse/CALCITE-2030
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.14.0
> Reporter: Marc Prud'hommeaux
> Assignee: Julian Hyde
> Priority: Minor
>
> As described at CALCITE-1945 , aggregates are rounded to their input types.
> PostgreSQL has a commonly-used DECIMAL type with unusual behavior for
> unspecified precision: the precision is stored with the same number of
> decimals as the input number. However, the JDBC driver reports the precision
> of the column to be 0, which causes Calcite's rounding logic to perform
> queries like:
> {code}SELECT CAST(SUM("price") / COUNT(*) AS DECIMAL(19, 0))
> FROM "products"
> GROUP BY “category"{code}
> This will cause in the average price result to be rounded to an integer. One
> solution would be to just cast it to a "DECIMAL" instead of a "DECIMAL(19,
> 0)" in {{PostgresqlSqlDialect.getCastSpec(RelDataType)}}.
> A possible alternative solution that wouldn't be DB-specific could be to just
> always cast to the SQL TYPE_NAME for the column, this making it so Calcite
> doesn't need to perform any precision logic itself. That is, if a database
> reports a column's TYPE_NAME is "CrAzY_nUmBeR", then Calcite's aggregate
> casting logic would simple execute:
> {code}SELECT CAST(SUM("price") / COUNT(*) AS CrAzY_nUmBeR)
> FROM "products"
> GROUP BY “category"{code}
> More discussion of the issue can be seen at:
> https://mail-archives.apache.org/mod_mbox/calcite-dev/201711.mbox/%[email protected]%3e
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
