-- A column the per-row aggregates have as NULL in every arm (a column of a generated series, a bucket the arms do not break down) can be an outer
-- group key: it is NULL in every group. Every query runs as the two-level scan, as the arms alone (sift.agg_two_level off) and as the normal
-- plan; the answers must be the same.
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_helpers;
-- the tuples through the arms are in the price of the scan: on these few rows the normal plan is the cheaper one, and the test asks about the scan
SET sift.agg_force = on;
SET max_parallel_workers_per_gather = 0;
SET seq_page_cost = 1000;
SET random_page_cost = 1000;

CREATE TABLE events (id int PRIMARY KEY, parent_id int, kind text, doc jsonb) WITH (autovacuum_enabled = false);
INSERT INTO events
SELECT i, CASE WHEN i % 31 = 0 THEN NULL ELSE i % 600 END, CASE WHEN i % 17 = 0 THEN NULL ELSE 'k' || i % 5 END,
       CASE WHEN i % 13 = 0 THEN NULL
            ELSE jsonb_build_object('parts', (SELECT jsonb_agg(jsonb_build_object('cat', 'cat_' || (i + j) % 6, 'score', (i * j) % 10))
                                              FROM generate_series(1, 1 + i % 4) j)) END
FROM generate_series(1, 12000) i;
CREATE INDEX events_col ON events USING sift_col (id, parent_id, kind, doc) WITH (paths = 'doc.parts[]{cat text, score float8}');
VACUUM (FREEZE, ANALYZE) events;

-- the arms per row and category, and per row, with the columns given as NULL constants; the outer query is the aggregates and the tail
CREATE FUNCTION report(cols text, tail text, keys text DEFAULT 'category, bucket_date') RETURNS text LANGUAGE sql AS $$
	SELECT format($q$SELECT %s, round(avg(score)::numeric, 6) AS value, sum(n)::int AS n, count(DISTINCT id) AS ids, count(DISTINCT parent_id) AS parents
	  FROM (SELECT e.id, e.parent_id, %s, o.cat AS category, avg(o.score) AS score, count(*) AS n
	        FROM events e CROSS JOIN LATERAL jsonb_to_recordset(e.doc['parts']) o(cat text, score float8) GROUP BY e.id, e.parent_id, o.cat
	        UNION ALL
	        SELECT e.id, e.parent_id, %s, NULL::text, avg(o.score), count(*)
	        FROM events e CROSS JOIN LATERAL jsonb_to_recordset(e.doc['parts']) o(cat text, score float8) GROUP BY e.id, e.parent_id) t
	  GROUP BY %s ORDER BY 1, 2$q$, keys, cols, cols, tail)
$$;

CREATE FUNCTION pushed(q text) RETURNS bool LANGUAGE sql AS $$ SELECT sift_helpers.plan_is_agg_scan(q) $$;

CREATE FUNCTION same(q text) RETURNS bool LANGUAGE plpgsql AS $$
DECLARE a bigint; b bigint; n bigint;
BEGIN
	SET sift.enable_agg = off;
	EXECUTE format('CREATE TEMP TABLE r_off AS %s', q);
	RESET sift.enable_agg;
	EXECUTE format('CREATE TEMP TABLE r_on AS %s', q);
	SET sift.agg_two_level = off;
	EXECUTE format('CREATE TEMP TABLE r_arms AS %s', q);
	RESET sift.agg_two_level;
	SELECT count(*) INTO a FROM ((TABLE r_on EXCEPT TABLE r_off) UNION ALL (TABLE r_off EXCEPT TABLE r_on)) d;
	SELECT count(*) INTO b FROM ((TABLE r_arms EXCEPT TABLE r_off) UNION ALL (TABLE r_off EXCEPT TABLE r_arms)) d;
	SELECT count(*) INTO n FROM r_off;
	DROP TABLE r_on, r_arms, r_off;
	RETURN a = 0 AND b = 0 AND n > 0 AND pushed(q);
END $$;

-- a date NULL in every arm next to a key the arms break down
SELECT same(report('NULL::date AS bucket_date', 'category, bucket_date')) AS date_and_category;
-- only the NULL column: one group
SELECT same(report('NULL::date AS bucket_date', 'bucket_date', 'bucket_date')) AS only_null;
-- two columns of two types, in the middle of the keys
SELECT same(report('NULL::date AS bucket_date, NULL::int AS bucket_no', 'bucket_date, category, bucket_no', 'bucket_date, category, bucket_no')) AS two_nulls;
-- grouping sets over a NULL column: its rows and the totals
SELECT same(report('NULL::date AS bucket_date', 'ROLLUP (category, bucket_date)')) AS rollup;
SELECT same(report('NULL::date AS bucket_date', 'GROUPING SETS ((category, bucket_date), (bucket_date), ())')) AS sets;
-- a HAVING and a test of the NULL column
SELECT same(report('NULL::date AS bucket_date', 'category, bucket_date HAVING bucket_date IS NULL AND count(DISTINCT id) > 100')) AS having_null;

CREATE FUNCTION key_lines(q text) RETURNS SETOF text LANGUAGE plpgsql AS $$
DECLARE l text;
BEGIN
	FOR l IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP
		IF l ~ 'Group Keys' THEN RETURN NEXT trim(l); END IF;
	END LOOP;
END $$;
SELECT * FROM key_lines(report('NULL::date AS bucket_date', 'category, bucket_date'));

-- rows that were updated and deleted since the index was built are read from the heap
UPDATE events SET doc = jsonb_set(doc, '{parts,0,score}', '9') WHERE id % 17 = 0 AND doc IS NOT NULL;
UPDATE events SET parent_id = (parent_id + 1) % 600 WHERE id % 19 = 0;
DELETE FROM events WHERE id % 23 = 0;
SELECT same(report('NULL::date AS bucket_date', 'category, bucket_date')) AS dirty_rows;
SELECT same(report('NULL::date AS bucket_date', 'ROLLUP (category, bucket_date)')) AS dirty_rollup;

-- a hash that does not fit the memory
SET work_mem = '64kB';
SET hash_mem_multiplier = 1;
SELECT same(report('NULL::date AS bucket_date', 'category, bucket_date')) AS small_memory;
RESET work_mem;
RESET hash_mem_multiplier;

DROP FUNCTION key_lines(text), same(text), pushed(text), report(text, text, text);
-- the shared database of a plain installcheck goes on with the next test
SET client_min_messages = warning;
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT USAGE ON SCHEMA public TO PUBLIC;
RESET client_min_messages;
DROP EXTENSION pg_sift_helpers;
DROP EXTENSION pg_sift;