\set ECHO none
\pset format unaligned

-- A searched CASE whose guards are aggregate comparisons and whose branches are
-- aggregates lowers to an agg_case gate_case (the aggregate-carrier analogue of
-- the RV CASE).  Its moments are evaluated EXACTLY by possible-worlds
-- decomposition -- E[pick^k] = Σ_i P(region_i)·E[value_i^k | region_i] over the
-- first-match regions -- so every assertion below runs under rv_mc_samples = 0
-- (no Monte Carlo).  Correlation between a guard and its branch (shared input
-- tuples) is carried by the conditioning, exactly as HAVING carries it.

SET provsql.rv_mc_samples = 0;

-- Two independent tuples, each present with probability 0.5.
CREATE TABLE cs(g int, x numeric, y numeric, z numeric);
INSERT INTO cs VALUES (1,1,10,100),(1,5,20,200);
SELECT add_provenance('cs');
DO $$ BEGIN PERFORM set_prob(provenance(), 0.5) FROM cs; END $$;

-- CASE WHEN sum(x) > 3 THEN sum(y) ELSE sum(z).  Possible worlds:
--   {}      no row of the group: the CASE has no value
--   {t1}    sum(x)=1  -> sum(z)=100
--   {t2}    sum(x)=5  -> sum(y)=20
--   {t1,t2} sum(x)=6  -> sum(y)=30
-- each world 0.25, so the three defined ones weigh 1/3 each:
-- E=50, E[pick^2]=3766.67, Var=1266.67.
CREATE TABLE pick AS
  SELECT g, CASE WHEN sum(x) > 3 THEN sum(y) ELSE sum(z) END AS p FROM cs GROUP BY g;

SET provsql.active = off;
-- The branch lowered to a gate_case carried by an agg_token.
SELECT get_gate_type(p::uuid) AS root_gate FROM pick;
-- The token's cell carries the actual-world CASE value -- both tuples are
-- present in the actual data, so sum(x)=6 > 3 selects sum(y)=30 -- and
-- agg_token_value_text resolves the same display from the bare UUID.
SELECT p AS display FROM pick;
SELECT agg_token_value_text(p::uuid) AS display_from_uuid FROM pick;
SELECT round(expected(p)::numeric,4)  AS e_pick,
       round(variance(p)::numeric,4)  AS var_pick,
       round(moment(p,2)::numeric,4)  AS m2_pick
FROM pick;
SET provsql.active = on;
DROP TABLE pick; DROP TABLE cs;

-- Certain tuples (probability 1): sum(x)=6 > 3 selects sum(y)=30 deterministically.
CREATE TABLE cd(g int, x numeric, y numeric, z numeric);
INSERT INTO cd VALUES (1,1,10,100),(1,5,20,200);
SELECT add_provenance('cd');
DO $$ BEGIN PERFORM set_prob(provenance(), 1.0) FROM cd; END $$;
CREATE TABLE pickd AS
  SELECT g, CASE WHEN sum(x) > 3 THEN sum(y) ELSE sum(z) END AS p FROM cd GROUP BY g;
SET provsql.active = off;
SELECT round(expected(p)::numeric,4) AS e_certain,
       round(variance(p)::numeric,4) AS var_certain
FROM pickd;
SET provsql.active = on;
DROP TABLE pickd; DROP TABLE cd;

-- MIN / MAX branches (a two-way max/min switch on the aggregate).  Three
-- tuples, one certain so the selected branch is never an empty group.
CREATE TABLE cm(g int, x numeric, y numeric);
INSERT INTO cm VALUES (1,1,10),(1,5,20),(1,3,7);
SELECT add_provenance('cm');
DO $$ BEGIN PERFORM set_prob(provenance(), 0.6) FROM cm WHERE x IN (1,5); END $$;
DO $$ BEGIN PERFORM set_prob(provenance(), 1.0) FROM cm WHERE x = 3; END $$;
CREATE TABLE pickm AS
  SELECT g, CASE WHEN sum(x) > 6 THEN max(y) ELSE min(y) END AS p FROM cm GROUP BY g;
SET provsql.active = off;
-- Actual world: sum(x)=9 > 6 selects max(y)=20.
SELECT p AS display_minmax FROM pickm;
SELECT round(expected(p)::numeric,4) AS e_minmax,
       round(variance(p)::numeric,4) AS var_minmax
FROM pickm;
SET provsql.active = on;
DROP TABLE pickm; DROP TABLE cm;

-- Constant branch (`ELSE 0`): lifted into a value gate, so the branch is a
-- Dirac and its conditional moment is exact.  Worlds (t1,t2 each 0.5):
--   {}->0, {t1}->0, {t2}->sum(y)=20, {t1,t2}->sum(y)=30  => E=12.5, Var=168.75.
CREATE TABLE cc(g int, x numeric, y numeric);
INSERT INTO cc VALUES (1,1,10),(1,5,20);
SELECT add_provenance('cc');
DO $$ BEGIN PERFORM set_prob(provenance(), 0.5) FROM cc; END $$;
CREATE TABLE pickc AS
  SELECT g, CASE WHEN sum(x) > 3 THEN sum(y) ELSE 0 END AS p FROM cc GROUP BY g;
-- Actual-world guard false: the default (a value gate) is displayed.
CREATE TABLE pickc2 AS
  SELECT g, CASE WHEN sum(x) > 100 THEN sum(y) ELSE 0 END AS p FROM cc GROUP BY g;
SET provsql.active = off;
SELECT round(expected(p)::numeric,4) AS e_const,
       round(variance(p)::numeric,4) AS var_const
FROM pickc;
SELECT p AS display_default FROM pickc2;
SET provsql.active = on;
DROP TABLE pickc; DROP TABLE pickc2; DROP TABLE cc;

-- Arithmetic branch (`sum(y)+sum(z)`): no exact possible-worlds moment for the
-- arithmetic combination, so that branch's conditional moment is estimated by
-- the Monte-Carlo scalar path (the region probabilities stay exact).  Worlds:
--   {} has no row, {t1}->sum(z)=100, {t2}->sum(y)+sum(z)=220, {t1,t2}->330,
--   so E = (100+220+330)/3 = 216.67.
CREATE TABLE ca(g int, x numeric, y numeric, z numeric);
INSERT INTO ca VALUES (1,1,10,100),(1,5,20,200);
SELECT add_provenance('ca');
DO $$ BEGIN PERFORM set_prob(provenance(), 0.5) FROM ca; END $$;
CREATE TABLE picka AS
  SELECT g, CASE WHEN sum(x) > 3 THEN sum(y)+sum(z) ELSE sum(z) END AS p FROM ca GROUP BY g;
SET provsql.rv_mc_samples = 500000;
SET provsql.monte_carlo_seed = 1;
SET provsql.active = off;
-- Actual world: sum(x)=6 > 3 selects sum(y)+sum(z) = 330 (an arith gate,
-- whose actual-world value agg_arith_make recorded in extra).
SELECT p AS display_arith FROM picka;
SELECT abs(expected(p) - 216.67) < 5 AS arith_branch_mc_close FROM picka;
SET provsql.active = on;
DROP TABLE picka; DROP TABLE ca;

RESET provsql.rv_mc_samples;
RESET provsql.monte_carlo_seed;

-- Conditional-on-defined semantics: the moment of a CASE conditions on
-- its value being DEFINED (the MIN/MAX convention), NULL only when it
-- never is.  Rows 10 and 100 each present with probability 1/2:
--   {10,100} sum=110 >= 100 -> sum 110 ; {100} -> sum 100 ;
--   {10} -> min 10 ; {} -> min over nothing: undefined (excluded,
--   the defined mass renormalises).
-- E[pick | defined]   = (110+100+10)/4 / (3/4) = 220/3  = 73.3333...
-- Var[pick | defined] = 22200/3 - (220/3)^2 = 18200/9   = 2022.2222...
CREATE TABLE cd2(g int, x numeric);
INSERT INTO cd2 VALUES (1, 10), (1, 100);
SELECT add_provenance('cd2');
DO $$ BEGIN PERFORM set_prob(provenance(), 0.5) FROM cd2; END $$;
SET provsql.rv_mc_samples = 0;
CREATE TABLE pickd2 AS
  SELECT g, CASE WHEN sum(x) >= 100 THEN sum(x) ELSE min(x) END AS p
  FROM cd2 GROUP BY g;
SET provsql.active = off;
SELECT round(expected(p)::numeric, 4) AS e_cond_defined,
       round(variance(p)::numeric, 4) AS var_cond_defined
FROM pickd2;
-- Conditioning on both rows absent: the CASE's value is never defined.
SELECT expected(p,
         (SELECT provenance_times(a.nt, b.nt)
            FROM (SELECT provenance_not(provsql) AS nt FROM cd2 WHERE x = 10) a,
                 (SELECT provenance_not(provsql) AS nt FROM cd2 WHERE x = 100) b))
       IS NULL AS never_defined_null
FROM pickd2;
SET provsql.active = on;
DROP TABLE pickd2; DROP TABLE cd2;
RESET provsql.rv_mc_samples;

-- A simple-form CASE (CASE <arg> WHEN ...) over aggregates is read as the
-- searched CASE it means (CASE WHEN <arg> = ... THEN ...), and lowered like
-- one: the value is an agg_token, tracked.  It used to be left alone, its
-- branches degraded through the agg_token cast back to the CASE's type, with
-- the usual warning -- never bare agg_token datums under a numeric CASE type,
-- which would be reinterpreted as a garbage varlena and corrupt (or crash on)
-- the materialised tuple.  That degradation still protects searched CASEs on
-- a schema whose upgrade path predates agg_case.
CREATE TABLE cf(g int, x numeric);
INSERT INTO cf VALUES (1, 10), (1, 100);
SELECT add_provenance('cf');
CREATE TABLE pickf AS
  SELECT g, CASE g WHEN 1 THEN sum(x) ELSE min(x) END AS p FROM cf GROUP BY g;
SET provsql.active = off;
SELECT g, p, pg_typeof(p) AS p_type FROM pickf;
SET provsql.active = on;
DROP TABLE pickf; DROP TABLE cf;

-- A branch whose value is NULL -- an aggregate over the padded rows of an
-- outer join only, a NULL constant -- is NULL in every world.  A CASE of a
-- type other than a number (a timestamp) has the truth of its condition
-- exploded into rows, one per truth, the branch it picks read as plain SQL.
CREATE TABLE cn_t(tag text, e int, w int);
CREATE TABLE cn_p(id int, score int, d date);
INSERT INTO cn_t VALUES ('a', 1, 2), ('b', 3, NULL), ('c', NULL, 4);
INSERT INTO cn_p VALUES (1, 10, '2020-01-01'), (2, 20, '2021-01-01'),
                        (3, 30, '2019-01-01');
SELECT add_provenance('cn_t');
SELECT add_provenance('cn_p');
DO $$ BEGIN PERFORM set_prob(provenance(), 0.5) FROM cn_p; END $$;
CREATE TABLE cn_r AS
  SELECT t.tag,
         CASE WHEN max(e.score) > max(w.score) THEN max(e.score)
              ELSE max(w.score) END AS s,
         round(expected(CASE WHEN max(e.score) > max(w.score) THEN max(e.score)
                             ELSE max(w.score) END)::numeric, 4) AS e_s,
         CASE WHEN max(e.d) > max(w.d) THEN max(e.d) ELSE max(w.d) END AS d
  FROM cn_t t LEFT JOIN cn_p e ON e.id = t.e LEFT JOIN cn_p w ON w.id = t.w
  GROUP BY t.tag;
SELECT remove_provenance('cn_r');
SELECT tag, s::text AS s, e_s, d FROM cn_r ORDER BY tag;
DROP TABLE cn_r;
CREATE TABLE cn_r AS
  SELECT t.tag, CASE WHEN max(e.score) > 15 THEN max(e.score) END AS s
  FROM cn_t t JOIN cn_p e ON e.id = t.e GROUP BY t.tag;
SELECT remove_provenance('cn_r');
SELECT tag, s::text AS s FROM cn_r ORDER BY tag;
DROP TABLE cn_r, cn_t, cn_p;


-- COALESCE(aggregate, constant) is the CASE it means, CASE WHEN agg IS NOT
-- NULL THEN agg ELSE constant END, so it lowers to the same gate: its guard is
-- the NullTest lowering, delta(+Kn), a row the aggregate reads a value from is
-- present.  Over the two rows of the first group, each present with
-- probability one half, sum(v) is NULL in the world holding only the
-- NULL-valued row, so coalesce(sum(v), 0) is 0 there and 5 in the two worlds
-- holding the other row: E = (0 + 5 + 5)/3 = 3.333333 and the variance is
-- 50/3 - 100/9 = 5.555556, both conditional on the group existing as every
-- moment is.  Both rows of the second group are NULL-valued, so the default
-- answers in every world the group exists in; the third group is certain of
-- its single row.  A default needs not be constant: an expression of the row
-- that holds no aggregate -- the grouping key here -- is the same in every
-- world, so it is lifted into a value gate like a constant.  A third argument
-- is left as the query wrote it and read as a plain value.
--
-- GREATEST and LEAST of an aggregate and such an expression are the CASE they
-- mean as well, and SQL's reading of a NULL argument as "no value" is what
-- their two NULL guards carry: GREATEST(NULL, 10) is 10, where a plain
-- "CASE WHEN a > b" would fall to its ELSE and answer NULL.  Over the first
-- group, whose two rows are one null-valued and one of 5, the sum is NULL in
-- the world holding only the first (one world in three where the group exists)
-- and 5 in the other two, so GREATEST(sum(v), 2) takes 2 and 5, E = 4, and
-- LEAST(sum(v), 2) is 2 in every one of them.  The third group is certain of
-- its 7.
CREATE TABLE cc(g int, v int);
INSERT INTO cc VALUES (1,NULL),(1,5),(2,NULL),(2,NULL),(3,7);
SELECT add_provenance('cc');
DO $$ BEGIN PERFORM set_prob(provenance(), 0.5) FROM cc; END $$;
CREATE TABLE cc_r AS
  SELECT g, coalesce(sum(v), 0) AS s0, coalesce(max(v), -1) AS mx,
         coalesce(count(*), 0) AS cnt, coalesce(sum(v), g) AS dflt_var,
         coalesce(sum(v), NULL, 0) AS three
  FROM cc GROUP BY g;
SET provsql.active = off;
SELECT g, s0::text AS s0, round(expected(s0, provsql)::numeric, 6) AS e_s0,
       round(variance(s0, provsql)::numeric, 6) AS var_s0,
       mx::text AS mx, round(expected(mx, provsql)::numeric, 6) AS e_mx,
       cnt::text AS cnt, dflt_var::text AS dflt_var, three::text AS three,
       round(probability(provsql)::numeric, 6) AS p
FROM cc_r ORDER BY g;
SET provsql.active = on;
DROP TABLE cc_r;
CREATE TABLE cc_r AS
  SELECT g, GREATEST(sum(v), 2) AS gt, LEAST(sum(v), 2) AS ls FROM cc GROUP BY g;
SET provsql.active = off;
SELECT g, gt::text AS gt, round(expected(gt, provsql)::numeric, 6) AS e_gt,
       ls::text AS ls, round(expected(ls, provsql)::numeric, 6) AS e_ls
FROM cc_r ORDER BY g;
SET provsql.active = on;
DROP TABLE cc_r;
-- The NULL argument: the second group has no value in any world, so both are
-- the other argument, as SQL says.
CREATE TABLE cc_r AS
  SELECT GREATEST(sum(v), 2) AS gt, LEAST(sum(v), 2) AS ls FROM cc WHERE g = 2;
SET provsql.active = off;
SELECT gt::text AS gt, ls::text AS ls FROM cc_r;
SET provsql.active = on;
DROP TABLE cc_r, cc;
-- NULLIF(aggregate, value) is the CASE it means as well, CASE WHEN agg = value
-- THEN NULL ELSE agg END, so it lowers to the same gate: its guard is the
-- comparison lowering, and a NULL aggregate falls to the ELSE, which is what
-- SQL answers there too.  The compared value bites in some worlds and not in
-- others, so the answer is neither the aggregate everywhere nor NULL
-- everywhere: over the two rows of the first group, each present with
-- probability one half, sum(v) is 1, 2 or 3, so NULLIF(sum(v), 3) is 1, 2 and
-- NULL, E = 1.5 over the worlds where it has a value.  The second group holds
-- one row of 5, which the compared value never equals.
CREATE TABLE cn(g int, v int);
INSERT INTO cn VALUES (1,1),(1,2),(2,5);
SELECT add_provenance('cn');
DO $$ BEGIN PERFORM set_prob(provenance(), 0.5) FROM cn; END $$;
CREATE TABLE cn_r AS
  SELECT g, nullif(sum(v), 3) AS n3, nullif(count(*), 1) AS n1 FROM cn GROUP BY g;
SET provsql.active = off;
SELECT g, n3::text AS n3, round(expected(n3, provsql)::numeric, 6) AS e_n3,
       n1::text AS n1, round(probability(provsql)::numeric, 6) AS p
FROM cn_r ORDER BY g;
SET provsql.active = on;
DROP TABLE cn_r;
-- Compared against an aggregate of its own: two aggregates on one row, which
-- the CASE would read as two branches, so it stays a plain value and says so.
SELECT nullif(sum(v), min(v)) AS n FROM cn WHERE g = 1;
SELECT remove_provenance('cn');
DROP TABLE cn;

-- An arm that is arithmetic over aggregates, not an aggregate itself: the
-- guards of the CASE read it as they read one, its NULL-ness included (it is
-- NULL where an operand is), so GREATEST / LEAST carries it instead of
-- freezing.  Two rows, 3 and 4, each present with probability one half:
-- sum(v)*2 is NULL, 6, 8 and 14 over the four worlds, so GREATEST(.., 9) is
-- 9, 9, 9 and 14 -- the empty world counts here, its value being the other
-- argument -- and E = 10.25; LEAST(.., 9) is 9, 6, 8 and 9, E = 8.
CREATE TABLE ca(v bigint);
INSERT INTO ca VALUES (3),(4);
SELECT add_provenance('ca');
DO $$ BEGIN PERFORM set_prob(provenance(), 0.5) FROM ca; END $$;
CREATE TABLE ca_r AS
  SELECT greatest(sum(v) * 2, 9) AS gt, least(sum(v) * 2, 9) AS ls FROM ca;
SET provsql.active = off;
SELECT gt::text AS gt, round(expected(gt, provsql)::numeric, 6) AS e_gt,
       ls::text AS ls, round(expected(ls, provsql)::numeric, 6) AS e_ls
FROM ca_r;
SET provsql.active = on;
DROP TABLE ca_r;

-- An arm that is itself a guarded selection: a GREATEST of a LEAST, and the
-- other way round.  The outer guard then compares a guarded selection with a
-- value, which the probability side expands arm by arm (CaseCmpExpander)
-- rather than meeting a value gate it has no operation for.  Over the same
-- four worlds: greatest(1, least(4, sum)) is 4, 3, 4, 4 -- the empty world
-- reads the NULL sum as no value, so the LEAST is its other argument -- and
-- E = 3.75; least(greatest(sum, 5), 6) is 5, 5, 5, 6, E = 5.25.
CREATE TABLE ca_r AS
  SELECT greatest(1, least(4, sum(v))) AS gl, least(greatest(sum(v), 5), 6) AS lg
  FROM ca;
SET provsql.active = off;
SELECT gl::text AS gl, round(expected(gl, provsql)::numeric, 6) AS e_gl,
       lg::text AS lg, round(expected(lg, provsql)::numeric, 6) AS e_lg
FROM ca_r;
SET provsql.active = on;
DROP TABLE ca_r;

-- The same comparison in a HAVING, where it used to be refused as a complex
-- HAVING expression: greatest(sum, 2) is 2, 3, 4 and 7 over the four worlds,
-- so it exceeds 3 in the two holding the row of 4 -- probability 1/2 -- and
-- COALESCE onto a constant reads the same way.
CREATE TABLE ca_h AS
SELECT 'greatest > 3' AS q, round(probability(provenance())::numeric, 6) AS p
  FROM (SELECT 1 AS k FROM ca GROUP BY 1 HAVING greatest(sum(v), 2) > 3) s
UNION ALL
SELECT 'coalesce > 3', round(probability(provenance())::numeric, 6)
  FROM (SELECT 1 AS k FROM ca GROUP BY 1 HAVING coalesce(sum(v), 0) > 3) s
UNION ALL
-- The selection on the right of the comparison: the same reading.
SELECT '3 < greatest', round(probability(provenance())::numeric, 6)
  FROM (SELECT 1 AS k FROM ca GROUP BY 1 HAVING 3 < greatest(sum(v), 2)) s
UNION ALL
-- One on each side, which takes a round of the expansion per side: 2 > 5,
-- 3 > 3, 4 > 4 and 7 > 5 over the four worlds, so only the last one, 1/4.
SELECT 'greatest > least', round(probability(provenance())::numeric, 6)
  FROM (SELECT 1 AS k FROM ca GROUP BY 1
        HAVING greatest(sum(v), 2) > least(sum(v), 5)) s;
SELECT remove_provenance('ca_h');
SELECT * FROM ca_h ORDER BY 1;
DROP TABLE ca_h;
SELECT remove_provenance('ca');
DROP TABLE ca;

-- Grouped, where a world may hold no row of the group at all: the reading has
-- to keep that world out, which the guards of the expansion do (they are 𝟘
-- there) rather than letting a constant arm answer for an absent group.  Rows
-- 3 and 5 in the first group, 7 in the second, each present with probability
-- one half: greatest(1, least(4, sum)) is 3, 4 and 4 over the worlds where
-- the first group exists, E = 11/3, and 4 over the one where the second does.
CREATE TABLE cg(g int, v int);
INSERT INTO cg VALUES (1,3),(1,5),(2,7);
SELECT add_provenance('cg');
DO $$ BEGIN PERFORM set_prob(provenance(), 0.5) FROM cg; END $$;
CREATE TABLE cg_r AS
  SELECT g, greatest(1, least(4, sum(v))) AS gl FROM cg GROUP BY g;
SET provsql.active = off;
SELECT g, gl::text AS gl, round(expected(gl, provsql)::numeric, 6) AS e_gl
FROM cg_r ORDER BY g;
SET provsql.active = on;
DROP TABLE cg_r;
SELECT remove_provenance('cg');
DROP TABLE cg;

-- IS [NOT] NULL of an EXPRESSION over aggregates, in HAVING: not the
-- aggregate's own NULL-ness but what follows from it, arm by arm for a CASE
-- (which GREATEST / LEAST and COALESCE become) and operand by operand for
-- arithmetic, which is strict.  One group of two rows, one of them NULL-valued
-- and each present with probability one half: sum(v) reads no value in the
-- world holding only the NULL row, so GREATEST(sum(v), min(v)) is NULL there
-- and nowhere else -- 1/4 -- and has a value in the two worlds holding the
-- row of 5 -- 1/2.  Arithmetic over it is NULL exactly where it is, and
-- COALESCE onto a constant nowhere, whence the zero.
CREATE TABLE cu(g int, v int);
INSERT INTO cu VALUES (1,NULL),(1,5);
SELECT add_provenance('cu');
DO $$ BEGIN PERFORM set_prob(provenance(), 0.5) FROM cu; END $$;
CREATE TABLE cu_r AS
SELECT 'greatest IS NULL' AS q,
       round(probability(provenance())::numeric, 6) AS p
  FROM (SELECT g FROM cu GROUP BY g
        HAVING greatest(sum(v), min(v)) IS NULL) s
UNION ALL
SELECT 'greatest IS NOT NULL', round(probability(provenance())::numeric, 6)
  FROM (SELECT g FROM cu GROUP BY g
        HAVING greatest(sum(v), min(v)) IS NOT NULL) s
UNION ALL
SELECT 'sum * 2 IS NULL', round(probability(provenance())::numeric, 6)
  FROM (SELECT g FROM cu GROUP BY g HAVING sum(v) * 2 IS NULL) s
UNION ALL
SELECT 'coalesce IS NULL', round(probability(provenance())::numeric, 6)
  FROM (SELECT g FROM cu GROUP BY g HAVING coalesce(sum(v), 0) IS NULL) s;
SELECT remove_provenance('cu_r');
SELECT * FROM cu_r ORDER BY 1;
DROP TABLE cu_r;
SELECT remove_provenance('cu');
DROP TABLE cu;

-- A simple-form CASE over the aggregate columns of a subquery, in a WHERE
-- (TPC-DS 39: CASE mean WHEN 0 THEN 0 ELSE stdev / mean END > 1): read as the
-- searched CASE, the condition moves to the aggregation's HAVING like any
-- other; it was refused as a complex HAVING.  Five rows at one half; the
-- probabilities are those of the enumeration of the 32 worlds.
CREATE TABLE csc(id int, k int, x int);
INSERT INTO csc VALUES (1, 1, 1), (2, 1, 5), (3, 1, 9), (4, 2, 0), (5, 2, 4);
SELECT add_provenance('csc');
DO $$ BEGIN PERFORM set_prob(provenance(), 0.5) FROM csc; END $$;
CREATE TABLE csc_r AS
  SELECT k, round(probability_evaluate(provenance())::numeric, 6) AS p
  FROM (SELECT k, stddev_samp(x) AS sd, avg(x) AS mean FROM csc GROUP BY k) f
  WHERE CASE mean WHEN 0 THEN 0 ELSE sd / mean END > 0.5;
SELECT remove_provenance('csc_r');
SELECT k, p FROM csc_r ORDER BY k;
DROP TABLE csc_r;
SELECT remove_provenance('csc');
DROP TABLE csc;

-- A COALESCE (or GREATEST / LEAST) gives the aggregate a value in the world
-- where the group has no row, so a grouped HAVING on it says nothing of
-- whether the group exists: the group's delta stays.  One group of two rows,
-- 10 and 20, each present with probability one half: the group exists in
-- three worlds out of four, with a sum of at least 10, so coalesce(sum, 0) = 0
-- and greatest(sum, 0) = 0 hold in none (they read 1/4, the empty world,
-- before), and NOT (coalesce(sum, 0) >= 25) in the two worlds of one row
-- (3/4 before).
CREATE TABLE ce(g int, v int);
INSERT INTO ce VALUES (1, 10), (1, 20);
SELECT add_provenance('ce');
DO $$ BEGIN PERFORM set_prob(provenance(), 0.5) FROM ce; END $$;
CREATE TABLE ce_h AS
SELECT 'coalesce = 0' AS q, round(probability(provenance())::numeric, 6) AS p
  FROM (SELECT g FROM ce GROUP BY g HAVING coalesce(sum(v), 0) = 0) s
UNION ALL
SELECT 'greatest = 0', round(probability(provenance())::numeric, 6)
  FROM (SELECT g FROM ce GROUP BY g HAVING greatest(sum(v), 0) = 0) s
UNION ALL
SELECT 'not coalesce >= 25', round(probability(provenance())::numeric, 6)
  FROM (SELECT g FROM ce GROUP BY g
        HAVING NOT (coalesce(sum(v), 0) >= 25)) s;
SELECT remove_provenance('ce_h');
SELECT * FROM ce_h ORDER BY 1;
DROP TABLE ce_h;

-- The same comparison in the select list is one truth per world, exploded
-- into a row per truth as for a bare aggregate (it was read frozen).  Grouped,
-- the false row holds where the group exists below 25 (1/2); without GROUP BY
-- the row of the aggregation is there in the empty world too, where the sum
-- is 0 (3/4).
CREATE TABLE ce_s AS
SELECT 'grouped' AS q, coalesce(sum(v), 0) >= 25 AS ok,
       round(probability(provenance())::numeric, 6) AS p
  FROM ce GROUP BY g
UNION ALL
SELECT 'scalar', coalesce(sum(v), 0) >= 25,
       round(probability(provenance())::numeric, 6)
  FROM ce;
SELECT remove_provenance('ce_s');
SELECT * FROM ce_s ORDER BY 1, 2;
DROP TABLE ce_s;

-- An untracked relation of the database outer-joined to a tracked one is
-- certain (provenance one), so the join is lowered (it was refused): every
-- group of ce_k keeps its row, the one with no row in ce among them, and the
-- COALESCE counts it 0.  Group 1 meets 25 in the world holding both rows
-- (1/4) and misses it in the three others (3/4), the empty one included;
-- group 2 misses it in every world.
CREATE TABLE ce_k(g int);
INSERT INTO ce_k VALUES (1), (2);
CREATE TABLE ce_j AS
SELECT k.g, coalesce(sum(ce.v), 0) >= 25 AS ok,
       round(probability(provenance())::numeric, 6) AS p
  FROM ce_k k LEFT JOIN ce ON ce.g = k.g
  GROUP BY k.g;
SELECT remove_provenance('ce_j');
SELECT * FROM ce_j ORDER BY 1, 2;
DROP TABLE ce_j;
DROP TABLE ce_k;

-- A Boolean aggregate given a default by COALESCE and read as an integer is
-- the indicator CASE it means, the default answering where the group has no
-- row.  Over the same group (10 and 20, one half each): bool_or(v >= 15) holds
-- where the row of 20 is, 1/2, which read alone is conditional on the group
-- existing, 1/2 / 3/4 = 2/3; with false as the default it is 1/2, in either
-- spelling, and with the default 5 it is 1/2 + 5 x 1/4 = 1.75, a bigint
-- default making the indicator a bigint.  bool_and(v <
-- 15) with true as the default holds in the empty world and in the one of the
-- row of 10 alone, 1/2, against 1/4 / 3/4 = 1/3 read alone.
CREATE TABLE ce_b AS
SELECT round(expected(bool_or(v >= 15)::int)::numeric, 6) AS or_alone,
       round(expected(coalesce(bool_or(v >= 15), false)::int)::numeric, 6)
         AS or_false,
       round(expected(coalesce(bool_or(v >= 15)::int, 0))::numeric, 6)
         AS or_int_0,
       round(expected(coalesce(bool_or(v >= 15)::int, 5))::numeric, 6)
         AS or_int_5,
       round(expected(coalesce(bool_or(v >= 15)::int, 5::bigint))::numeric, 6)
         AS or_bigint_5,
       round(expected(bool_and(v < 15)::int)::numeric, 6) AS and_alone,
       round(expected(coalesce(bool_and(v < 15), true)::int)::numeric, 6)
         AS and_true
  FROM ce GROUP BY g;
SELECT remove_provenance('ce_b');
SELECT * FROM ce_b;
DROP TABLE ce_b;
SELECT remove_provenance('ce');
DROP TABLE ce;

SELECT 'ok'::text AS agg_case_done;