The bug explains the current behavior. The behavior is not what everyone would like, but it is what it is. I happen to like it because it is simple. The behavior is this: if have a column c of type T, then AVG(c) will have type T. If c is an INTEGER, then AVG will return an INTEGER. If you cast that result to DOUBLE, surprise surprise, that DOUBLE has no fractional part.
The solution is to convert the column before applying AVG: AVG(CAST(c AS DOUBLE)) will return DOUBLE. Julian > On Oct 30, 2017, at 5:54 AM, Marc Prud'hommeaux <[email protected]> wrote: > > > 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 >>> >> >
