#272 Let AVG function return a sufficient scale

engine (144)
Lukas Eder

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


  • Lukas Eder

    Lukas Eder - 2011-10-15

    The same probably applies for the MEDIAN function and maybe some others...

  • Fred Toussi

    Fred Toussi - 2011-10-17
    • priority: 5 --> 1
    • assigned_to: nobody --> fredt
    • status: open --> closed
  • Fred Toussi

    Fred Toussi - 2011-10-17

    Thanks for the reminder.
    Variable scale implemented for 2.2.6 for TINYINT, SMALLINT, INT and BIGINT as:

    Connection property sql.avg_scale
    SQL statement SET DATABASE SQL AVG SCALE <number> with default 0

    No change of behaviour unless the user specifies the scale.


Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.

No, thanks