-- A lateral child, or a sub-select the scan asks once, that reads a column of a query above the aggregate's own stays on the normal plan: the scan would
-- never see the parameter.
CREATE EXTENSION pg_sift;
SET max_parallel_workers_per_gather = 0;
CREATE TABLE facts (id int, kind text, payload text);
INSERT INTO facts SELECT i, CASE WHEN i % 2 = 0 THEN 'a' ELSE 'b' END, repeat('payload', 50) FROM generate_series(1, 20000) i;
CREATE INDEX facts_col ON facts USING sift_col (id, kind);
NOTICE:  sift_col index "facts_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('facts_col');
CREATE TABLE lookup_values (kind text, region text, label text);
INSERT INTO lookup_values VALUES ('a', 'east', 'ae'), ('b', 'east', 'be'), ('a', 'west', 'aw'), ('b', 'west', 'bw');
CREATE TABLE region_kinds (kind text, region text);
INSERT INTO region_kinds VALUES ('a', 'east'), ('b', 'west'), ('b', 'east');
CREATE TABLE regions (id int, region text);
INSERT INTO regions VALUES (1, 'east'), (2, 'west'), (3, 'north');
CREATE TABLE plain_facts AS SELECT id, kind, payload FROM facts;
VACUUM (FREEZE, ANALYZE) facts;
ANALYZE plain_facts;
ANALYZE lookup_values;
ANALYZE regions;
ANALYZE region_kinds;
CREATE FUNCTION run_query(q text, agg bool) RETURNS text LANGUAGE plpgsql AS $$
DECLARE r text;
BEGIN
  PERFORM set_config('sift.enable_agg', agg::text, true);
  EXECUTE 'SELECT coalesce(string_agg(t::text, E''\n'' ORDER BY t::text), '''') FROM (' || q || ') t' INTO r;
  RETURN r;
EXCEPTION WHEN OTHERS THEN RETURN 'ERROR: ' || SQLERRM;
END $$;
CREATE FUNCTION check_query(q text) RETURNS TABLE (pushed bool, same bool, result text) LANGUAGE plpgsql AS $$
DECLARE l text;
BEGIN
  result := run_query(q, false);
  same := result = run_query(q, true);
  pushed := false;
  PERFORM set_config('sift.enable_agg', 'on', true);
  FOR l IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP
    pushed := pushed OR l LIKE '%Custom Scan (SiftAgg)%';
  END LOOP;
  RETURN NEXT;
END $$;
-- The child reads the grandparent's row: every outer row has its own answer.
SELECT * FROM check_query($q$
 SELECT r.id, (SELECT count(*) FILTER (WHERE l.label = 'ae')
   FROM facts f LEFT JOIN LATERAL (SELECT label FROM lookup_values m WHERE m.kind = f.kind AND m.region = r.region LIMIT 1) l ON true)
 FROM regions r
$q$);
 pushed | same |  result   
--------+------+-----------
 f      | t    | (1,10000)+
        |      | (2,0)    +
        |      | (3,0)
(1 row)

-- Not lateral: a sub-select of the FROM list reads the outer query too.
SELECT * FROM check_query($q$
 SELECT r.id, (SELECT count(*) FILTER (WHERE l.label = 'ae')
   FROM facts f LEFT JOIN (SELECT kind, label FROM lookup_values m WHERE m.region = r.region) l ON l.kind = f.kind)
 FROM regions r
$q$);
 pushed | same |  result   
--------+------+-----------
 f      | t    | (1,10000)+
        |      | (2,0)    +
        |      | (3,0)
(1 row)

-- A condition on the columns of a derived table that PostgreSQL does not pull up (GROUP BY, DISTINCT, a set operation, HAVING) is pushed into the plan of
-- the derived table as a parameter of the outer row: the sub-select does not hold it, but the scan would read the derived table once for all the outer rows.
SELECT * FROM check_query($q$
 SELECT r.id, (SELECT count(*) FROM facts f JOIN (SELECT kind, region FROM lookup_values GROUP BY 1, 2) d ON d.kind = f.kind WHERE d.region = r.region)
 FROM regions r
$q$);
 pushed | same |  result   
--------+------+-----------
 f      | t    | (1,20000)+
        |      | (2,20000)+
        |      | (3,0)
(1 row)

SELECT * FROM check_query($q$
 SELECT r.id, (SELECT count(*) FROM facts f JOIN (SELECT DISTINCT kind, region FROM lookup_values) d ON d.kind = f.kind WHERE d.region = r.region)
 FROM regions r
$q$);
 pushed | same |  result   
--------+------+-----------
 f      | t    | (1,20000)+
        |      | (2,20000)+
        |      | (3,0)
(1 row)

SELECT * FROM check_query($q$
 SELECT r.id, (SELECT count(*) FROM facts f
   JOIN (SELECT kind, region FROM lookup_values UNION SELECT kind, region FROM lookup_values) d ON d.kind = f.kind WHERE d.region = r.region)
 FROM regions r
$q$);
 pushed | same |  result   
--------+------+-----------
 f      | t    | (1,20000)+
        |      | (2,20000)+
        |      | (3,0)
(1 row)

SELECT * FROM check_query($q$
 SELECT r.id, (SELECT count(*) FROM facts f JOIN (SELECT kind, count(*) c FROM lookup_values GROUP BY 1) d ON d.kind = f.kind WHERE d.c = r.id)
 FROM regions r
$q$);
 pushed | same |  result   
--------+------+-----------
 f      | t    | (1,0)    +
        |      | (2,20000)+
        |      | (3,0)
(1 row)

SELECT * FROM check_query($q$
 SELECT r.id, (SELECT count(*) FROM facts f
   JOIN (SELECT kind FROM lookup_values GROUP BY kind HAVING count(*) > r.id) d ON d.kind = f.kind)
 FROM regions r
$q$);
 pushed | same |  result   
--------+------+-----------
 f      | t    | (1,20000)+
        |      | (2,0)    +
        |      | (3,0)
(1 row)

-- The same through a lateral sub-select, a materialized or inlined CTE and a VALUES list.
SELECT * FROM check_query($q$
 SELECT r.id, x.c FROM regions r LEFT JOIN LATERAL
  (SELECT count(*) c FROM facts f JOIN (SELECT kind, region FROM lookup_values GROUP BY 1, 2) d ON d.kind = f.kind WHERE d.region = r.region) x ON true
$q$);
 pushed | same |  result   
--------+------+-----------
 f      | t    | (1,20000)+
        |      | (2,20000)+
        |      | (3,0)
(1 row)

SELECT * FROM check_query($q$
 SELECT r.id, (SELECT count(*) FROM facts f
   JOIN (WITH c AS MATERIALIZED (SELECT kind, region FROM lookup_values GROUP BY 1, 2) SELECT kind, region FROM c) d ON d.kind = f.kind WHERE d.region = r.region)
 FROM regions r
$q$);
 pushed | same |  result   
--------+------+-----------
 f      | t    | (1,20000)+
        |      | (2,20000)+
        |      | (3,0)
(1 row)

SELECT * FROM check_query($q$
 SELECT r.id, (SELECT count(*) FROM facts f
   JOIN (VALUES ('a', 'east'), ('b', 'west'), ('b', 'east')) d(kind, region) ON d.kind = f.kind WHERE d.region = r.region)
 FROM regions r
$q$);
 pushed | same |  result   
--------+------+-----------
 f      | t    | (1,20000)+
        |      | (2,10000)+
        |      | (3,0)
(1 row)

-- Control: the condition inside the derived table, and a constant one: the first is read per outer row, the second once.
SELECT * FROM check_query($q$
 SELECT r.id, (SELECT count(*) FROM facts f
   JOIN (SELECT kind FROM lookup_values WHERE region = r.region GROUP BY kind) d ON d.kind = f.kind)
 FROM regions r
$q$);
 pushed | same |  result   
--------+------+-----------
 f      | t    | (1,20000)+
        |      | (2,20000)+
        |      | (3,0)
(1 row)

SELECT * FROM check_query($q$
 SELECT r.id, (SELECT count(*) FROM facts f JOIN (SELECT kind, region FROM lookup_values GROUP BY 1, 2) d ON d.kind = f.kind WHERE d.region = 'west')
 FROM regions r
$q$);
 pushed | same |  result   
--------+------+-----------
 t      | t    | (1,20000)+
        |      | (2,20000)+
        |      | (3,20000)
(1 row)

-- A function that reads the grandparent's column.
SELECT * FROM check_query($q$
 SELECT r.id, (SELECT count(*) FROM facts f CROSS JOIN LATERAL generate_series(1, r.id) g(i) WHERE g.i = 1 + f.id % 4)
 FROM regions r
$q$);
 pushed | same |  result   
--------+------+-----------
 f      | t    | (1,5000) +
        |      | (2,10000)+
        |      | (3,15000)
(1 row)

-- The aggregate of the sub-select reads the outer column itself: a plan parameter the scan rejects already.
SELECT * FROM check_query($q$
 SELECT r.id, (SELECT count(*) FROM facts f LEFT JOIN LATERAL (SELECT label FROM lookup_values m WHERE m.kind = f.kind LIMIT 1) l ON true
   WHERE l.label = 'ae' AND r.region = 'east')
 FROM regions r
$q$);
 pushed | same |  result   
--------+------+-----------
 f      | t    | (1,10000)+
        |      | (2,0)    +
        |      | (3,0)
(1 row)

-- A hashed NOT IN of the aggregate's WHERE asks its sub-select once; the sub-select reads the outer row through a CTE or a sub-select of its own, which
-- PostgreSQL plans from a copy.
SELECT * FROM check_query($q$
 SELECT r.id, (SELECT count(*) FROM facts f
   WHERE f.kind NOT IN (WITH c AS MATERIALIZED (SELECT kind FROM region_kinds k WHERE k.region = r.region) SELECT kind FROM c))
 FROM regions r
$q$);
 pushed | same |  result   
--------+------+-----------
 f      | t    | (1,0)    +
        |      | (2,10000)+
        |      | (3,20000)
(1 row)

SELECT * FROM check_query($q$
 SELECT r.id, (SELECT count(*) FROM facts f
   WHERE f.kind NOT IN (SELECT s.kind FROM (SELECT kind FROM region_kinds k WHERE k.region = r.region LIMIT 10) s))
 FROM regions r
$q$);
 pushed | same |  result   
--------+------+-----------
 f      | t    | (1,0)    +
        |      | (2,10000)+
        |      | (3,20000)
(1 row)

SELECT * FROM check_query($q$
 SELECT r.id, (SELECT count(*) FROM facts f
   WHERE f.kind NOT IN (SELECT kind FROM region_kinds k WHERE k.region = r.region))
 FROM regions r
$q$);
 pushed | same |  result   
--------+------+-----------
 f      | t    | (1,0)    +
        |      | (2,10000)+
        |      | (3,20000)
(1 row)

-- Control: the same tests of a sub-select that reads no outer row are asked once, and the outer rows replay the groups.
SELECT * FROM check_query($q$
 SELECT r.id, (SELECT count(*) FROM facts f
   WHERE f.kind NOT IN (WITH c AS MATERIALIZED (SELECT kind FROM region_kinds k WHERE k.region = 'west') SELECT kind FROM c))
 FROM regions r
$q$);
 pushed | same |  result   
--------+------+-----------
 t      | t    | (1,10000)+
        |      | (2,10000)+
        |      | (3,10000)
(1 row)

-- Control: a child that reads the sub-select's own tables only is still joined by the scan, and the outer rows replay its groups.
SELECT * FROM check_query($q$
 SELECT r.id, (SELECT count(*) FILTER (WHERE l.label = 'ae')
   FROM facts f LEFT JOIN LATERAL (SELECT label FROM lookup_values m WHERE m.kind = f.kind AND m.region = 'east' LIMIT 1) l ON true)
 FROM regions r
$q$);
 pushed | same |  result   
--------+------+-----------
 t      | t    | (1,10000)+
        |      | (2,10000)+
        |      | (3,10000)
(1 row)

SELECT * FROM check_query($q$
 SELECT count(*) FILTER (WHERE l.label = 'ae') FROM facts f
 LEFT JOIN LATERAL (SELECT label FROM lookup_values m WHERE m.kind = f.kind AND m.region = 'east' LIMIT 1) l ON true
$q$);
 pushed | same | result  
--------+------+---------
 t      | t    | (10000)
(1 row)

-- A table with no column index is not scanned: the sub-selects of its query are planned by PostgreSQL only.
SELECT * FROM check_query($q$
 SELECT count(*) FILTER (WHERE l.label = 'ae') FROM plain_facts f
 LEFT JOIN LATERAL (SELECT label FROM lookup_values m WHERE m.kind = f.kind AND m.region = 'east' LIMIT 1) l ON true
$q$);
 pushed | same | result  
--------+------+---------
 f      | t    | (10000)
(1 row)

DROP FUNCTION check_query(text);
DROP FUNCTION run_query(text, bool);
DROP TABLE facts, plain_facts, lookup_values, region_kinds, regions;
DROP EXTENSION pg_sift;