[CALCITE-2030] Aggregates executed against PostgreSQL DECIMAL columns perform lossy casts

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 1.14.0
• Assignee: Unassigned
• Reporter: Marc Prud'hommeaux
• Created: 02/Nov/17 15:01
• Updated: 22/Feb/19 01:22

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:

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/[e-mail]%3e

Issue Links

• is related to: CALCITE-1945 Allow custom return types for AVG/VARIANCE/STDDEV/COVAR