According to the SQL 2008 standard, this holds true for the AVG function:
If AVG is specified and DT is exact numeric, then the declared type of the result is an implementation-
defined exact numeric type with precision not less than the precision of DT and scale not
less than the scale of DT.
In case of HSQLDB (and Derby, SQL Server), the returned precision and scale seem to be the same as that of the function argument. If the argument has a zero scale (e.g. integer), then the result is rounded. In all other RDBMS against which I run integration tests with jOOQ, the scale is increased to the necessary value. I.e.
create table test (t int);
insert into test values (1), (2);
select avg(t) from test;
The above returns 1 in HSQLDB, Derby (value is truncated)
It returns 2 in SQL Server (value is rounded)
It returns 1.5 in DB2, H2, Ingres, MySQL, Oracle, Postgres, SQLite, Sybase ASE, Sybase SQL Anywhere
I think the latter should be the preferred behaviour. After all, it's OK with the SQL standard.
Note: This is a workround for HSQLDB:
select avg(cast(t as numeric(2, 1))) from test
Log in to post a comment.