-- A group key computed from a date column (a lookup key: extract, date_trunc, date + 1) is cached by value in a table over the range of the values it has seen, up to 4
-- million values; a column that holds both infinities (-infinity and infinity are the ends of the 32-bit range) spans 2^32 and made the scan ask for a 16 GB table
-- ("invalid memory alloc request size 17179869248"). Such a key keeps its ids in a hash table, like a key of an integer column, and the rows are those of the normal plan.
-- (the rows of the two infinities must fall in a unit of their own: the test needs more than one unit, 200,000 rows)
CREATE EXTENSION pg_sift;
SET client_min_messages = warning;	-- the NOTICE of the order array gives bytes a row that follow the sample of ANALYZE
CREATE EXTENSION pg_sift_helpers;
SET default_statistics_target = 10000;
SET max_parallel_workers_per_gather = 0;
CREATE TABLE dinf (id int, d date, v int);
INSERT INTO dinf SELECT i, date '2020-01-01' + i % 400, i FROM generate_series(1, 200000) i;
INSERT INTO dinf VALUES (200001, 'infinity', 1), (200002, '-infinity', 2), (200003, NULL, 3);
CREATE INDEX dinf_col ON dinf USING sift_col (id, d, v);
VACUUM (FREEZE, ANALYZE) dinf;

CREATE FUNCTION plan_of(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE l text;
BEGIN
	FOR l IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP
		IF l LIKE '%Custom Scan (SiftAgg)%' THEN RETURN 'SiftAgg'; END IF;
	END LOOP;
	RETURN 'normal';
END $$;
CREATE FUNCTION differences(q text) RETURNS bigint LANGUAGE sql AS $$ SELECT sift_helpers.differences_agg(q) $$;

-- the scan is forced (the table is small): every key must be answered by it, with the rows of the normal plan
SET cpu_tuple_cost = 100;
SELECT k, plan_of(format('SELECT %s, sum(v) FROM dinf GROUP BY 1', k)) AS plan, differences(format('SELECT %s, sum(v) FROM dinf GROUP BY 1', k)) AS differences
FROM (VALUES ('extract(dow FROM d)'), ('d + 1'), ('date_trunc(''month'', d)'), ('d::text'), ('d')) v(k) ORDER BY k;
RESET cpu_tuple_cost;

-- a unit whose dates are all one infinity covers the range [2^31 - 1, 2^31 - 1] (or [-2^31, -2^31]): the loop that fills the new end of the range table stepped over it
-- (int32 overflow in the bound) and wrote to memory far outside the table, in a parallel worker that began with such a unit
CREATE TABLE dedge (id int, d date, v int);
INSERT INTO dedge SELECT i, 'infinity', i FROM generate_series(1, 3000) i;
INSERT INTO dedge SELECT i, '-infinity', i FROM generate_series(3001, 6000) i;
CREATE INDEX dedge_col ON dedge USING sift_col (id, d, v);
VACUUM (FREEZE, ANALYZE) dedge;
SET cpu_tuple_cost = 100;
SET max_parallel_workers_per_gather = 2; SET parallel_setup_cost = 0; SET parallel_tuple_cost = 0; SET min_parallel_table_scan_size = 0; SET min_parallel_index_scan_size = 0;
SET parallel_leader_participation = off;
SELECT q, plan_of(q) AS plan, differences(q) AS differences
FROM (VALUES ('SELECT d, count(*), sum(v) FROM dedge GROUP BY d'), ('SELECT d, count(*) FROM dedge WHERE id <= 3000 GROUP BY d'), ('SELECT d, count(*) FROM dedge WHERE id > 3000 GROUP BY d'),
             ('SELECT d, sum(v) FROM dedge WHERE id % 1000 = 1 GROUP BY d')) v(q) ORDER BY q;
RESET cpu_tuple_cost;

DROP TABLE dedge;
DROP FUNCTION plan_of(text), differences(text);
DROP TABLE dinf;
DROP EXTENSION pg_sift_helpers;
DROP EXTENSION pg_sift;