Re: [Firebird-test] AVG is not properly defined

[Paul R. <pr...@ib...>]

Skopalik Slavomir wrote:
>>From description of AVG function (Original FireBird Lang. Ref.) I can't
> determine result of this:
> CREATE TABLE test( id INTEGER NOT NULL CONSTRAINT unq UNIQUE,
>                    text VARCHAR(32));
> 

The second insert generates this error:

   SQL> INSERT INTO test VALUES(6,null);
   Statement failed, SQLCODE = -803

   violation of PRIMARY or UNIQUE KEY constraint "UNQ" on table "TEST"


Perhaps you have a special version of Firebird?

Anyway, once I recreated the table without the unique index I tried this:

SELECT AVG(id*1.0) FROM test1;

                   AVG
=====================

                   5.6


which is the answer I expected.

Dialect 3 databases do integer arithmetic on integers. It doesn't 
promote them to floats in anticipation of our expectations. It is 
arguable whether the new behaviour is correct. Partisans of that 
argument have fought it out twice now in ib-support.

I'll leave you to experiment with the variations:

SQL> SELECT AVG(id*1.00) FROM test

etc.


Paul



-- 

Paul Reeves
http://www.ibphoenix.com
Supporting users of Firebird and InterBase