-- The arms of a UNION ALL that run in one pass keep the output of a sub-select in a group key (x IN (SELECT ..)) while the key also reads a
-- column of the lookup join: the columns of the join are renumbered over the pass, the sub-select's own parameter (numbered after those of the
-- sub-selects in front of it) is not one of them.
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_helpers;
SET max_parallel_workers_per_gather = 0;
SET seq_page_cost = 1000;
SET random_page_cost = 1000;
CREATE TABLE zones (id int PRIMARY KEY, name text, tier int);
INSERT INTO zones SELECT i, 'z' || i % 7, i % 3 FROM generate_series(0, 49) i;
CREATE TABLE kinds (kind text);
INSERT INTO kinds VALUES ('a'), ('b');
CREATE TABLE ev (id int PRIMARY KEY, zone_id int, day date, kind text, price float8) WITH (autovacuum_enabled = false);
INSERT INTO ev SELECT i, i % 50, '2026-03-01'::date + i % 40, (ARRAY['a', 'b', 'c'])[1 + i % 3], (i % 13) + 0.5 FROM generate_series(1, 6000) i;
CREATE INDEX ev_col ON ev USING sift_col (id, zone_id, day, kind, price);
NOTICE:  sift_col index "ev_col": 2 automatic choices (rollup_columns, track_changes)
DETAIL:  rollup_columns = day, kind; track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ev_col');
VACUUM (FREEZE, ANALYZE) ev;
ANALYZE zones, kinds;
CREATE FUNCTION arms_shared(q text) RETURNS int LANGUAGE sql AS $$ SELECT sift_helpers.agg_arms_in_one_pass(q) $$;
CREATE FUNCTION same(q text) RETURNS bool LANGUAGE sql AS $$ SELECT sift_helpers.agg_equals_normal_all(q) $$;
\set arms 'SELECT CASE WHEN e.kind IN (SELECT kind FROM kinds WHERE kind = ''q1'') THEN ''q1'' WHEN e.kind IN (SELECT kind FROM kinds WHERE kind = ''q2'') THEN ''q2'' WHEN e.kind IN (SELECT kind FROM kinds WHERE kind = ''q3'') THEN ''q3'' WHEN e.kind IN (SELECT kind FROM kinds WHERE kind = ''q4'') THEN ''q4'' WHEN e.kind IN (SELECT kind FROM kinds WHERE kind = ''q5'') THEN ''q5'' WHEN e.kind IN (SELECT kind FROM kinds) THEN z.name END AS a, NULL::text AS b, count(*) AS n, sum(e.price) AS s FROM ev e JOIN zones z ON z.id = e.zone_id GROUP BY 1 UNION ALL SELECT CASE WHEN e.kind IN (SELECT kind FROM kinds WHERE kind = ''q1'') THEN ''q1'' WHEN e.kind IN (SELECT kind FROM kinds WHERE kind = ''q2'') THEN ''q2'' WHEN e.kind IN (SELECT kind FROM kinds WHERE kind = ''q3'') THEN ''q3'' WHEN e.kind IN (SELECT kind FROM kinds WHERE kind = ''q4'') THEN ''q4'' WHEN e.kind IN (SELECT kind FROM kinds WHERE kind = ''q5'') THEN ''q5'' WHEN e.kind IN (SELECT kind FROM kinds) THEN z.name END, e.kind, count(*), sum(e.price) FROM ev e JOIN zones z ON z.id = e.zone_id GROUP BY 1, 2'
SELECT arms_shared(:'arms') AS arms_shared, same(:'arms') AS arms_same;
 arms_shared | arms_same 
-------------+-----------
           2 | t
(1 row)

-- 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;