I had noticed that issue, but it purports to be closed for 1.14.0, which I am
using.
It only seems to affect AVG; other aggregates don’t appear to be rounded. E.g.:
0: jdbc:calcite:schemaType=JDBC> select min("price"), sum("price"),
avg("price") from "products";
+---------------------+---------------------+---------------------+
| EXPR$0 | EXPR$1 | EXPR$2 |
+---------------------+---------------------+---------------------+
| 9.99 | 200151.00 | 20 |
+---------------------+---------------------+---------------------+
yields the server-side log:
2017-10-30 08:16:00 EDT [4272-13] dgdemo@dvdstore LOG: execute <unnamed>:
SELECT MIN("price"), CASE WHEN COUNT(*) = 0 THEN NULL ELSE SUM("price") END,
CAST(CASE WHEN COUNT(*) = 0 THEN NULL ELSE SUM("price") END / COUNT(*) AS
DECIMAL(19, 0)) FROM “products"
Is this something I can get around by implementing my own RelDataTypeSystem? If
so, I’ll experiment with that.
-Marc
> On Oct 29, 2017, at 7:11 PM, Julian Hyde <[email protected]> wrote:
>
> See https://issues.apache.org/jira/browse/CALCITE-1945
> <https://issues.apache.org/jira/browse/CALCITE-1945>.
>
>> On Oct 29, 2017, at 3:31 PM, Marc Prud'hommeaux <[email protected]> wrote:
>>
>>
>> When I run the following against a Calcite connection containing the
>> PostgreSQL "dvdstore" sample database:
>>
>> select avg(products.price) from dvdstore.products group by products.category
>>
>> The following SQL is executed on the server:
>>
>> SELECT CAST(SUM("price") / COUNT(*) AS DECIMAL(19, 0)) FROM "products" GROUP
>> BY “category"
>>
>> Is there some way I can prevent Calcite from rounding it (price is a decimal
>> type)? Is there some reason it isn’t just sending the aggregate as an AVG?
>>
>> -Marc
>>
>
