With the +Inf and -Inf at the same column, the TOTAL aggregation function returns Null value.

CREATE TABLE t0(c0);
INSERT INTO t0 VALUES(1e900);
INSERT INTO t0 VALUES(-1e900);
INSERT INTO t0 VALUES(10);

SELECT TOTAL(c0) from t0;
-- Null
SELECT TOTAL(aggr) from (SELECT total(c0) as aggr from t0 where rowid<=2 UNION ALL SELECT total(c0) as aggr from t0 where rowid >=3);
-- 10.0

The first SELECT returns Null because of the +Inf and -Inf.

Based on the document:

The result of total() is always a floating point value.

I think the result is unexpected.

And also, the Null affect the result of the second SELECT, which should be equivalent to the first SELECT. The results are inconsistent.

Environment:

Version: 3.39.0

Commit ID: 2277f9ba

OS: Ubuntu 20.04

Configuration Options: ./configure --enable-all

Compiler: gcc-9

Client: CLI

That's because you overflowed the Binary-64 range representation:

So-called "REAL" or floating point values are stored in the IEEE 754 Binary-64 format. This gives a range of positive values between approximately 1.7976931348623157e+308 and 4.9406564584124654e-324 with an equivalent range of negative values. A binary64 can also be 0.0 (and -0.0), positive and negative infinity and "NaN" or "Not-a-Number".

Hence, what you actually stored is:

sqlite> SELECT c0 FROM t0;
Inf
-Inf
10

Now, if I remember correctly, Inf + (-Inf) is undefined under IEEE 754, and since the document you linked to also states:

The sum() and total() aggregate functions return the sum of all non-NULL values in the group.

SQLite's NULL return is arguably correct, since all three inputs are not NULL (nor, as another responder mentioned, NaN-degenerated-to-NULL), but a meaningful total cannot be computed. Returning 10 or 10.0 would be absolutely wrong.

If you don't overflow Binary-64, things work as expected:

sqlite> CREATE TABLE t1(c1);
sqlite> INSERT INTO t1 VALUES(1e308);
sqlite> INSERT INTO t1 VALUES(-1e308);
sqlite> INSERT INTO t1 VALUES(10);
sqlite> SELECT c1 FROM t1;
1.0e+308
-1.0e+308
10
sqlite> SELECT TOTAL(c1) FROM t1;
10.0

Thanks for your clarify!

I understand that a meaningful TOTAL cannot be computed, but is it possible to return a Inf as well? Otherwise, the second SELECT returns 10, which is also incorrect.

is it possible to return a Inf as well?

That's up to Dr. Hipp, but why would Inf + (-Inf) + 10 = Inf make any more sense than -Inf, or NULL, or 42.0?

Otherwise, the second SELECT returns 10, which is also incorrect.

Every part of your second query performs exactly as documented, so I'm not sure what you mean by "incorrect":

sqlite> .nullvalue '!!!NULL!!!'

sqlite> SELECT c0 FROM t0 WHERE rowid <= 2;
Inf
-Inf
sqlite> SELECT total(c0) FROM t0 WHERE rowid <= 2;
!!!NULL!!!

sqlite> SELECT c0 FROM t0 WHERE rowid >= 3;
10.0
sqlite> SELECT total(c0) FROM t0 WHERE rowid >= 3;
10.0

sqlite> SELECT total(c0) AS aggr FROM t0 WHERE rowid <= 2 UNION ALL
        SELECT total(c0) AS aggr FROM t0 WHERE rowid >= 3;
!!!NULL!!!
10.0
sqlite> SELECT total(aggr) FROM (
          SELECT total(c0) AS aggr FROM t0 WHERE rowid <= 2 UNION ALL
          SELECT total(c0) AS aggr FROM t0 WHERE rowid >= 3
        );
10.0

Also, the sentence you quoted needs to be understood in its context:

The result of total() is always a floating point value. The result of sum() is an integer value if all non-NULL inputs are integers. If any input to sum() is neither an integer nor a NULL, then sum() returns a floating point value which is an approximation of the mathematical sum.

In other words, "total() always returns a floating point" is meant to contrast with "sum() sometimes returns a float, and sometimes an integer".

Thanks!

It is indeed a bit confusing for me. I got no document on how to calculate Inf in the TOTAL/SUM.