[
https://issues.apache.org/jira/browse/CALCITE-2030?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Marc Prud'hommeaux updated CALCITE-2030:
----------------------------------------
Description:
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:
{{xxx}}
{{
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/%3c4aaa715a-1b1c-4bfa-bae2-ed6f9ff06...@apache.org%3e
See also: https://issues.apache.org/jira/browse/CALCITE-1945
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/%3c4aaa715a-1b1c-4bfa-bae2-ed6f9ff06...@apache.org%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 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:
> {{xxx}}
> {{
> 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/%3c4aaa715a-1b1c-4bfa-bae2-ed6f9ff06...@apache.org%3e
> See also: https://issues.apache.org/jira/browse/CALCITE-1945
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
