-- Aggregates and keys with no stored leaf (leaf -1: an expression, count(*), a grouping set, a spine) next to the code that indexes the layout by the leaf of
-- a spec: every shape runs on the pushdown and on the normal plan with equal results; the sanitizer lane (heap-buffer-overflow before the array of leaves) is
-- what finds a missing guard.
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_helpers;
SET client_min_messages = warning;
SET max_parallel_workers_per_gather = 0;
CREATE TABLE ls (id int, g int, a int, b bigint, n numeric(10,2), f float8, t text, d date, doc jsonb) WITH (autovacuum_enabled = off);
INSERT INTO ls SELECT i, i % 7, (i * 31) % 1000, (i * 977) % 100000, ((i * 91) % 20000) / 100.0, (i % 71) / 7.0, 'k' || (i % 13), date '2024-01-01' + i % 90,
	jsonb_build_object('tags', jsonb_build_array('x' || i % 5, 'y' || i % 3), 'v', i % 17)
FROM generate_series(1, 6000) i;
VACUUM (FREEZE, ANALYZE) ls;
CREATE INDEX ls_col ON ls USING sift_col (id, g, a, b, n, f, t, d, doc) WITH (paths = 'doc->v int4, doc->tags[] text');
CREATE FUNCTION ls_rows(q text, agg bool) RETURNS text LANGUAGE sql AS $$ SELECT sift_helpers.rows_agg(q, agg) $$;
SELECT n, ls_rows(q, true) IS NOT DISTINCT FROM ls_rows(q, false) AS same
FROM (VALUES
	(1, 'SELECT g, min(a + 1), max(a * 2) FROM ls GROUP BY g'),
	(2, 'SELECT g, min(f * 2), max(f + b) FROM ls GROUP BY g'),
	(3, 'SELECT min(n * 2), max(n + a) FROM ls'),
	(4, 'SELECT g, count(DISTINCT a + g), count(DISTINCT t) FROM ls GROUP BY g'),
	(5, 'SELECT count(*), count(a), count(DISTINCT a) FROM ls'),
	(6, 'SELECT (a + b) % 5, count(*), sum(a) FROM ls GROUP BY 1'),
	(7, 'SELECT g, a % 3, count(*) FROM ls GROUP BY GROUPING SETS ((g), (a % 3), ())'),
	(8, 'SELECT t, count(*) FILTER (WHERE a > 500 AND g < 3) FROM ls GROUP BY t'),
	(9, 'SELECT g, sum(a) FROM ls WHERE a > 100 AND (g = 1 OR g = 4) AND NOT (b < 5000) GROUP BY g'),
	(10, 'SELECT g, count(*) FROM ls WHERE (a + g) > 500 AND t LIKE ''k1%'' GROUP BY g'),
	(11, 'SELECT g, count(*) FROM ls WHERE doc @> ''{"v": 3}'' AND doc -> ''tags'' @> ''["x1", "y1"]'' GROUP BY g'),
	(12, 'SELECT g, count(*) FROM ls WHERE EXISTS (SELECT 1 FROM jsonb_array_elements_text(doc -> ''tags'') e WHERE e = ''x1'' OR e = ''y2'') GROUP BY g'),
	(13, 'SELECT g, count(*) FROM ls WHERE EXISTS (SELECT 1 FROM jsonb_array_elements_text(doc -> ''tags'') e WHERE NOT (e = ''x1'')) GROUP BY g'),
	(14, 'SELECT d, count(*), min(a) FROM ls WHERE d >= date ''2024-02-01'' AND d < date ''2024-03-01'' GROUP BY d'),
	(15, 'SELECT g, a FROM ls ORDER BY a + g DESC, id LIMIT 5'),
	(16, 'SELECT g, sum(a) FROM ls GROUP BY g ORDER BY sum(a) + g DESC LIMIT 3'),
	(17, 'SELECT g, avg(f), stddev(n) FROM ls GROUP BY g'),
	(18, 'SELECT g, count(*) FROM ls WHERE a IN (1, 2, 3) OR b IS NULL OR NOT (n > 10) GROUP BY g')
) v(n, q) ORDER BY n;
 n  | same 
----+------
  1 | t
  2 | t
  3 | t
  4 | t
  5 | t
  6 | t
  7 | t
  8 | t
  9 | t
 10 | t
 11 | t
 12 | t
 13 | t
 14 | t
 15 | t
 16 | t
 17 | t
 18 | t
(18 rows)

DROP TABLE ls;
DROP FUNCTION ls_rows(text, bool);
DROP EXTENSION pg_sift_helpers;
DROP EXTENSION pg_sift;