Result type of AVG over BIGINT column results in type INT128

[mrotteveel]

The result type of aggregate function AVG over a BIGINT column results in a column type INT128. This makes no sense because the average can never exceed the maximum of the input type. Similarly, AVG over INTEGER should return INTEGER, not BIGINT, etc.

SQL> set sqlda_display on;
SQL> select avg(cast(1 as bigint)) from rdb$database;

INPUT message field count: 0

OUTPUT message field count: 1
01: sqltype: 32752 INT128 Nullable scale: 0 subtype: 0 len: 16
  :  name: AVG  alias: AVG
  : table:   owner:

                                          AVG
=============================================
                                            1

SQL>

Expected would be sqltype: 580 INT64

[mrotteveel]

This also applies for NUMERIC/DECIMAL : for precision < 9 it expands to precision 18, for precision between 10 and 18 it expands precision to 38.

[mrotteveel]

Also DECFLOAT(16).

[AlexPeshkoff]

I doubt it's worth changing old datatypes - just cause incompatibilities in old applications. What about bigint => int128 & DECFLOAT(16 => 34) I will fix - just wait a little, may be somebody will see problems with it.

[mlazdans]

Agree. And this "bug" crashes PHP extension too even on databases that never use INT128 type.

[mrotteveel]

@mlazdans The workaround is to configure DataTypeCompatibility to 3.0 (or 2.5) in either firebird.conf or databases.conf.