SQLite Forum

AVG() with Infinity behavior changed in 3.43.0

AVG() with Infinity behavior changed in 3.43.0

(1) By flavorjones on 2023-08-27 19:05:48 [source]

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


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

In 3.43.0:

$ cat repro-infinity.sql | ./sqlite3.43.0 :memory: -json

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

(2) By flavorjones on 2023-08-28 02:18:58 in reply to 1 [link] [source]

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

(3) By Richard Hipp (drh) on 2023-08-28 12:17:16 in reply to 1 [link] [source]

Thank you for the bug report.

A similar problem with SUM() over infinity in window functions goes back a long way. Here is a query that gets the wrong sum() in all versions of SQLite going back to 3.25.0:

WITH t1(x) AS (VALUES(1.0),(2.0),(3.0),(+9.0e+999),(5.0),(6.0),(7.0))

(4) By Richard Hipp (drh) on 2023-08-28 13:04:20 in reply to 3 [link] [source]

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.

(7) By flavorjones on 2023-08-30 15:22:37 in reply to 4 [link] [source]

@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

$ cat repro-infinity.sql | ./sqlite3.43.0 :memory: -json

$ cat repro-infinity.sql | ./sqlite3-trunk :memory: -json

(8) By Richard Hipp (drh) on 2023-08-30 16:09:15 in reply to 7 [link] [source]

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

(9) By flavorjones on 2023-08-30 18:38:16 in reply to 8 [link] [source]

Confirmed, latest trunk works. Thank you very much!

(5) By Richard Hipp (drh) on 2023-08-28 15:25:13 in reply to 3 [link] [source]

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.

(6) By anonymous on 2023-08-28 20:44:12 in reply to 5 [link] [source]

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