-- sum() and avg() of float8 raise PostgreSQL's overflow error where a plain double sum would hide it as Infinity, and not when an input is itself infinite or NaN;
-- the guard reads the selected rows through the kernel of the largest absolute value. Rows a FILTER or a NULL hides do not count. Every query runs as the scan and as
-- the normal plan (outcome: the rows, or the error), and the answers must be the same; `scan` says the plan is the scan's.
CREATE EXTENSION pg_sift;
SET max_parallel_workers_per_gather = 0;
SET client_min_messages = warning;
-- g1 holds finite large values that overflow their sum (1e308 twice), g2 an Infinity, g3 a NaN, g4 small values; hid marks the rows a FILTER hides
CREATE TABLE fg (id int, g int, k text, x float8, hid bool) WITH (autovacuum_enabled = false);
INSERT INTO fg
SELECT i, g, 'k' || g,
       CASE WHEN g = 1 AND i % 50 < 2 THEN 1e308 WHEN g = 2 AND i % 50 = 0 THEN 'Infinity'::float8 WHEN g = 3 AND i % 50 = 0 THEN 'NaN'::float8
            WHEN i % 41 = 0 THEN NULL ELSE (i % 1000)::float8 / 7 END,
       i % 50 < 2
FROM (SELECT i, 1 + i % 4 AS g FROM generate_series(1, 20000) i) s;
CREATE INDEX fg_col ON fg USING sift_col (id, g, k, x, hid);
VACUUM (FREEZE, ANALYZE) fg;
\ir test/agg_helpers.sql
\set ECHO none
SELECT w, pushed(q) AS scan, left(outcome(q, true), 60) AS answer, outcome(q, false) = outcome(q, true) AS same
FROM (SELECT w, format('SELECT %s FROM fg WHERE %s', sel, cond) AS q
	  FROM (VALUES
		('sum, one group, overflow',          'sum(x)', 'g = 1'),
		('sum, one group, hidden by WHERE',   'sum(x)', 'g = 1 AND NOT hid'),
		('avg, one group, overflow',          'avg(x)', 'g = 1'),
		('sum, infinity',                     'sum(x)', 'g = 2'),
		('sum, nan',                          'sum(x)', 'g = 3'),
		('avg, nan and infinity',             'avg(x)', 'g = 2 OR g = 3'),
		('sum, small values',                 'sum(x)', 'g = 4'),
		('avg, small values',                 'avg(x)', 'g = 4'),
		('sum FILTER hides the overflow',     'sum(x) FILTER (WHERE NOT hid)', 'g = 1'),
		('sum by key, overflow in one',       'k, sum(x)', 'true GROUP BY k'),
		('sum by key, overflow hidden',       'k, sum(x)', 'NOT hid GROUP BY k'),
		('avg by key',                        'k, avg(x)', 'g <> 1 AND g <> 3 GROUP BY k'),
		('sum by key and id range',           'id / 5000, sum(x)', 'g = 4 GROUP BY 1')
	  ) v(w, sel, cond)) t;
                w                | scan |                            answer                            | same 
---------------------------------+------+--------------------------------------------------------------+------
 sum, one group, overflow        | t    | 22003 value out of range: overflow                           | t
 sum, one group, hidden by WHERE | t    | [{"sum": 334359.4285714286}]                                 | t
 avg, one group, overflow        | t    | 22003 value out of range: overflow                           | t
 sum, infinity                   | t    | [{"sum": 347644.85714285716}]                                | t
 sum, nan                        | t    | [{"sum": "NaN"}]                                             | t
 avg, nan and infinity           | t    | [{"avg": "NaN"}]                                             | t
 sum, small values               | t    | [{"sum": 349072.85714285704}]                                | t
 avg, small values               | t    | [{"avg": 71.5606513207989}]                                  | t
 sum FILTER hides the overflow   | t    | [{"sum": 334359.4285714286}]                                 | t
 sum by key, overflow in one     | t    | 22003 value out of range: overflow                           | t
 sum by key, overflow hidden     | t    | [{"k": "k1", "sum": 334359.4285714286}, {"k": "k2", "sum": 3 | t
 avg by key                      | t    | [{"k": "k2", "avg": 71.26790839336965}, {"k": "k4", "avg": 7 | t
 sum by key and id range         | t    | [{"sum": 87219.00000000001, "?column?": 3}, {"sum": 87236.71 | t
(13 rows)

DROP FUNCTION outcome, runq, runs_in_scan, pushed;
DROP TABLE fg;
DROP EXTENSION pg_sift;