The behavior of the AVG() function has changed with respect to handling infinity in 3.43.0.

Reproduction:

create table foo (bar float);
insert into foo values (37.5);
insert into foo values (1e10000);
select avg(bar) from foo;

In 3.42.0:

$ $ cat repro-infinity.sql | ./sqlite3.42.0 :memory: -json
[{"avg(bar)":9.0e+999}]

In 3.43.0:

$ cat repro-infinity.sql | ./sqlite3.43.0 :memory: -json
[{"avg(bar)":null}]

This seems like unintended behavior, so I figured I'd bring it up here to see if anybody has some insight.

In case it's helpful information, the behavior change was introduced in this commit:

commit 45d75628
Author: drh <>
Date:   2023-07-06 13:19:10 +0000

    Experimental attempt to boost the accuracy of sum() using the
    Kahan-Babuska-Neumaier algorithm.
    
    FossilOrigin-Name: ebc5edd3b10c1102b07b9fb0d6837266b81e55504ef883b9b8a7ad5e8ab29dd2

The original problem (but not the issue with window function SUM()s) should now be fixed on trunk and on branch-3.42.

flavorjones: please verify that this fix works in your application and report back.

Another variant of the same problem is this:

WITH t1(x) AS (VALUES(1.0),(-9e+999),(2.0),(+9e+999),(3.0))
 SELECT sum(x) FROM t1;

After summing the first two values, the result is +Infinity. Then when the fourth value comes in, the system attempts to add -Infinity to +Infinity and gets back NaN. SQLite translates this into NULL. That problem has been in the SQLite code for time out of mind. It is not clear how one might fix it.

PostgreSQL works around this issue by raising a fatal exception on the query if it ever encounters a +Infinity or -Infinity on a DOUBLE PRECISION value.

The SQLite approach will continue to be to return NULL if the system cannot determine if the result should be +Infinity, -Infinity, or something in between. This is the approach that IEEE 754 math takes (if you assume that NaN is the same as NULL). The SQLite approach to dealing with infinities in SUM() is now documented.

Agree it's not clear. Consider an imaginary number construct and resolve to either +infinity or -infinity with bias flags set during expression computation?

@drh Thanks for your time and attention on this.

Although SUM() looks good, AVG() is still returning NULL on trunk.

$ cat repro-infinity.sql 
create table foo (bar float);
insert into foo values (37.5);
insert into foo values (1e10000);
select sum(bar) from foo;
select avg(bar) from foo;

$ cat repro-infinity.sql | ./sqlite3.42.0 :memory: -json
[{"sum(bar)":9.0e+999}]
[{"avg(bar)":9.0e+999}]

$ cat repro-infinity.sql | ./sqlite3.43.0 :memory: -json
[{"sum(bar)":null}]
[{"avg(bar)":null}]

$ cat repro-infinity.sql | ./sqlite3-trunk :memory: -json
[{"sum(bar)":9.0e+999}]
[{"avg(bar)":null}]

Please try one more time, with the latest trunk check-in or the latest check-in on the branch-3.43 branch.

Confirmed, latest trunk works. Thank you very much!