Marc Prud'hommeaux created CALCITE-2030:
-------------------------------------------
Summary: 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/%[email protected]%3e
See also: https://issues.apache.org/jira/browse/CALCITE-1945
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
