You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Marc Prud'hommeaux (JIRA)" <ji...@apache.org> on 2017/11/02 15:03:00 UTC
[jira] [Updated] (CALCITE-2030) Aggregates executed against
PostgreSQL DECIMAL columns perform lossy casts
[ 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:
{{ 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-ED6F9FF06CC6@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-ED6F9FF06CC6@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:
> {{ 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-ED6F9FF06CC6@apache.org%3e
> See also: https://issues.apache.org/jira/browse/CALCITE-1945
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)