-- A sub-select that reads a statement-level common table expression is a plan read once, not a plan per row.
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
SET max_parallel_workers_per_gather = 0;
CREATE TABLE facts (id int, dim_a_id int, day date, amount float8, payload text);
INSERT INTO facts
SELECT i, i % 50, date '2026-01-01' + (i % 120), CASE WHEN i % 7 = 0 THEN NULL ELSE i % 13 END, repeat('payload', 50)
FROM generate_series(1, 100000) i;
CREATE INDEX facts_col ON facts USING sift_col (id, dim_a_id, day, amount);
VACUUM (FREEZE, ANALYZE) facts;
\ir test/agg_check_query.sql
\set ECHO none
-- Overlapping, duplicate, NULL and empty periods: every matching period multiplies the facts.
SELECT * FROM check_query($q$
 WITH periods AS MATERIALIZED (
  SELECT * FROM (VALUES ('a', daterange('2026-01-10', '2026-02-10', '[]')), ('b', daterange('2026-02-01', '2026-03-20', '[]')),
   ('b', daterange('2026-02-01', '2026-03-20', '[]')), ('c', NULL), ('d', daterange('2030-01-01', '2030-02-01', '[]'))) p (period, range)),
 spans AS (SELECT DISTINCT period, range FROM periods)
 SELECT s.period, count(*) AS n, count(DISTINCT f.id) AS ids, sum(f.amount) AS total
 FROM facts f JOIN spans s ON f.day <@ s.range GROUP BY 1
$q$);
 pushed | eligible | differences 
--------+----------+-------------
 t      | t        |           0
(1 row)

-- Periods preserved by an outer join keep their empty buckets, with duplicate and NULL periods among them.
SELECT * FROM check_query($q$
 WITH periods AS MATERIALIZED (
  SELECT 'm' || m AS period, daterange(date '2026-01-01' + m * 10, date '2026-01-01' + m * 10 + 12, '[]') AS range
  FROM generate_series(0, 14) m
  UNION ALL SELECT 'm0', daterange(date '2026-01-01', date '2026-01-13', '[]')
  UNION ALL SELECT 'empty', daterange('2030-01-01', '2030-02-01', '[]')
  UNION ALL SELECT 'null', NULL),
 spans AS (SELECT DISTINCT period, range FROM periods)
 SELECT s.period, count(f.id) AS n, sum(f.amount) AS total
 FROM spans s LEFT JOIN facts f ON f.day <@ s.range GROUP BY 1
$q$);
 pushed | eligible | differences 
--------+----------+-------------
 t      | t        |           0
(1 row)

-- A common table expression of the sub-select itself is read once as well.
SELECT * FROM check_query($q$
 SELECT s.period, count(*) AS n
 FROM facts f JOIN (
  WITH p AS (SELECT * FROM (VALUES ('a', date '2026-01-10'), ('b', date '2026-02-10')) v (period, since))
  SELECT DISTINCT period, since FROM p) s ON f.day = s.since GROUP BY 1
$q$);
 pushed | eligible | differences 
--------+----------+-------------
 t      | t        |           0
(1 row)

DROP FUNCTION check_query(text);
DROP TABLE facts;
DROP EXTENSION pg_sift;