\set ECHO none
\pset format unaligned

CREATE TABLE result_formula AS SELECT
  *, sr_formula(provenance(),'personnel_name') AS formula FROM (
    (SELECT DISTINCT city FROM personnel)
  EXCEPT
    (SELECT p1.city
     FROM personnel p1, personnel p2
     WHERE p1.city = p2.city AND p1.id < p2.id
     GROUP BY p1.city)
  ) t
ORDER BY city;

SELECT remove_provenance('result_formula');
SELECT city, replace(formula,'(Dave ⊗ Nancy) ⊕ (Dave ⊗ Magdalen)','(Dave ⊗ Magdalen) ⊕ (Dave ⊗ Nancy)') AS formula FROM result_formula;

DROP TABLE result_formula;

CREATE TABLE result_formula AS SELECT
  *, sr_formula(provenance(),'personnel_name') AS formula FROM (
    (SELECT city
     FROM personnel
     WHERE FALSE)
  EXCEPT
    (SELECT city
     FROM personnel p)
  ) t
ORDER BY city;

SELECT remove_provenance('result_formula');
SELECT * FROM result_formula;

DROP TABLE result_formula;

-- The difference of the algebra has the NOT-IN semantics of the ICDE 2026
-- paper (§IV-B):
--   ⟪q1 − q2⟫ = {{ (u, α ⊖ ⊕_{β:(u,β)∈q2} β) | (u,α) ∈ q1 }}
-- The sum ⊕β ranges over ALL right tuples equal to u (the right arm is grouped
-- first), and every left tuple is kept.  With a duplicate-valued right arm this
-- differs from the old per-match ⊕(α⊖βi).  EXCEPT is ε of that difference.
CREATE TABLE ea_a(x int);
CREATE TABLE ea_b(x int);
INSERT INTO ea_a VALUES (1);
INSERT INTO ea_b VALUES (1),(1);
SELECT add_provenance('ea_a');
SELECT add_provenance('ea_b');
DO $$ BEGIN
  PERFORM set_prob(provsql, 0.5) FROM ea_a;
  PERFORM set_prob(provsql, 0.5) FROM ea_b;
END $$;

-- SQL's EXCEPT ALL is not that operator (its rows differ as soon as the left
-- operand has duplicates) and is refused over tracked relations.
SELECT x FROM ea_a EXCEPT ALL SELECT x FROM ea_b;

-- One left row, two equal right rows: one row with provenance a ⊖ (b1 ⊕ b2),
-- so P = P(a ∧ ¬b1 ∧ ¬b2) = 0.5^3 = 0.125 (not the old 0.375), and exactly one
-- row (not two).
CREATE TABLE ea_set AS
  SELECT round(probability_evaluate(provenance())::numeric, 4) AS p
  FROM (SELECT x FROM ea_a EXCEPT SELECT x FROM ea_b) t;
SELECT remove_provenance('ea_set');
SELECT count(*) AS n_rows, min(p) AS p_except FROM ea_set;
DROP TABLE ea_set;

DROP TABLE ea_a;
DROP TABLE ea_b;

-- A left arm whose column is text, a right arm whose column is varchar, and
-- a set-returning function in the left arm, which keeps it a subquery scan:
-- each arm is read with its own type.
CREATE TABLE ea_v(v varchar(12));
INSERT INTO ea_v VALUES ('d2'),('d4');
SELECT add_provenance('ea_v');
CREATE TABLE ea_v_r AS
  SELECT m, present(provenance())
  FROM (SELECT 'd' || generate_series(1, 4) AS m EXCEPT SELECT v FROM ea_v) t;
SELECT remove_provenance('ea_v_r');
SELECT m, present FROM ea_v_r ORDER BY m;
DROP TABLE ea_v_r;
CREATE TABLE ea_v_r AS
  SELECT v, present(provenance())
  FROM (SELECT v FROM ea_v EXCEPT SELECT 'd' || generate_series(1, 2)) t;
SELECT remove_provenance('ea_v_r');
SELECT v, present FROM ea_v_r ORDER BY v;
DROP TABLE ea_v_r;
DROP TABLE ea_v;