-- A numeric without a precision is stored as an image and read through a dictionary of numerics built from the images' digits: the entries must be the
-- very values numeric_in gives (digits, weight and display scale), whatever the shape of the number. Every aggregate is run with the scan forced and
-- with sift.enable_agg = off and the binary forms of the results compared.
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_helpers;
SET max_parallel_workers_per_gather = 0;
CREATE TABLE shapes (id serial, g int, x numeric);
INSERT INTO shapes (g, x)
SELECT i % 3, v::numeric
FROM (VALUES (0), (0.00), (1), (-1), (0.5), (-0.5), (12345678.9012), (-12345678.9012), (1200), (1200.00), (0.0001), (0.00001234), (100000000000000000000),
	(1e30), (123456789012345678901234567890.123456789), (-0.0000000000000001), (1.10), (7), (10000), (0.1), (99999999.99999999), (1e-60), (1e60),
	(1e-1000), (1.5e200), (0.0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001),
	('NaN'), ('Infinity'), ('-Infinity')) a(v), generate_series(1, 20) i;
CREATE INDEX shapes_col ON shapes USING sift_col (g, x);
NOTICE:  sift_col index "shapes_col": 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('shapes_col');
VACUUM (FREEZE, ANALYZE) shapes;
CREATE FUNCTION differences(q text) RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE d bigint;
BEGIN
	SET LOCAL sift.agg_force = off;
	SET LOCAL sift.enable_agg = off;
	EXECUTE format('CREATE TEMP TABLE r_off ON COMMIT DROP AS %s', q);
	SET LOCAL sift.enable_agg = on;
	SET LOCAL sift.agg_force = on;
	EXECUTE format('CREATE TEMP TABLE r_on ON COMMIT DROP AS %s', q);
	SELECT count(*) INTO d FROM ((TABLE r_on EXCEPT ALL TABLE r_off) UNION ALL (TABLE r_off EXCEPT ALL TABLE r_on)) x;
	DROP TABLE r_on, r_off;
	RETURN d;
END $$;
CREATE FUNCTION scanned(q text) RETURNS boolean LANGUAGE sql AS $$ SELECT sift_helpers.plan_mentions_siftagg_forced(q) $$;
SELECT scanned('SELECT g, min(x), sum(x) FROM shapes GROUP BY g') AS scanned;
 scanned 
---------
 t
(1 row)

SELECT differences('SELECT g, encode(numeric_send(min(x)), ''hex'') AS mn, encode(numeric_send(max(x)), ''hex'') AS mx, count(DISTINCT x) AS cd FROM shapes GROUP BY g') AS min_max_distinct;
 min_max_distinct 
------------------
                0
(1 row)

SELECT differences('SELECT g, encode(numeric_send(sum(x)), ''hex'') AS s, encode(numeric_send(avg(x)), ''hex'') AS a FROM shapes WHERE x < ''Infinity'' GROUP BY g') AS sum_avg;
 sum_avg 
---------
       0
(1 row)

SELECT differences('SELECT x::text AS t, count(*) AS n FROM shapes GROUP BY x') AS group_by_value;
 group_by_value 
----------------
              0
(1 row)

SELECT differences('SELECT g, sum(x) FILTER (WHERE x > 0), min(x) FILTER (WHERE x < 0) FROM shapes GROUP BY g') AS filtered;
 filtered 
----------
        0
(1 row)

DROP FUNCTION differences(text), scanned(text);
DROP TABLE shapes;
DROP EXTENSION pg_sift_helpers;
DROP EXTENSION pg_sift;