\set ECHO none
\pset format unaligned

SELECT
    provenance ();

SELECT DISTINCT ON (city)
    city, count(*)
FROM
    personnel
GROUP BY city;

SELECT
    *
FROM
    personnel
INTERSECT ALL
SELECT
    *
FROM
    personnel;

-- DISTINCT on the value of an aggregate whose values cannot be enumerated
-- (see explode_agg_value for the ones that can)
SELECT DISTINCT AVG(id) FROM personnel GROUP BY city;

-- EXCEPT on the value of an aggregate that cannot be enumerated (an AVG is a
-- quotient no exact arithmetic reproduces from the contributions)
SELECT city, AVG(id) FROM personnel GROUP BY city
EXCEPT
SELECT city, AVG(id) FROM personnel WHERE city='Paris' GROUP BY city;

-- UNION (non-ALL) whose other arm aggregates nothing at that column
SELECT COUNT(*) FROM personnel GROUP BY city UNION SELECT 1;

-- GROUP BY on the value of an aggregate whose values cannot be enumerated
-- (a count(), a min(), a max(), a choose() and a sum() over an integer column
-- are exploded into one row per value instead, see explode_agg_value)
SELECT total, COUNT(*) FROM (SELECT city, AVG(id) AS total FROM personnel GROUP BY city) t GROUP BY total;

-- Hand-made provsql column (collides with the auto-added provenance column)
SELECT name, provenance() AS provsql FROM personnel;

-- Hand-made provsql column from a plain expression
SELECT name AS provsql FROM personnel;

-- Hand-made provsql column from another uuid column
SELECT u AS provsql FROM (SELECT public.uuid_generate_v4() AS u, name FROM personnel) s;

-- A provsql column named by PostgreSQL: a cast takes the column's name
SELECT provsql::text FROM personnel;

-- Hand-made provsql column inside a set-operation arm
SELECT name, provenance() AS provsql FROM personnel
UNION ALL
SELECT name, provenance() AS provsql FROM personnel;

-- EXCEPT ALL over tracked relations, at the top level and nested
SELECT name FROM personnel EXCEPT ALL SELECT name FROM personnel;
SELECT name FROM (SELECT name FROM personnel EXCEPT ALL SELECT name FROM personnel) t;
WITH c AS (SELECT name FROM personnel EXCEPT ALL SELECT name FROM personnel) SELECT * FROM c;

-- A subquery over a tracked relation in a query level the rewriting does not
-- engage on -- a LATERAL body computing an array -- is evaluated on the data as
-- it is, and a warning says so.  Fetching a token (SELECT provsql FROM ...) is
-- not reading data.  A FROM-less block whose sublinks are the whole condition
-- is no longer among these: its bodies are lifted into a FROM of its own
-- (sublink_no_relation).
CREATE TABLE lateral_array AS
  SELECT p.id, cardinality(b.ids) AS n
  FROM (SELECT DISTINCT id FROM personnel WHERE id < 3) p
       LEFT JOIN LATERAL (SELECT ARRAY(SELECT q.id FROM personnel q
                                       WHERE q.id = p.id) AS ids) b ON true;
SELECT remove_provenance('lateral_array');
SELECT id, n FROM lateral_array ORDER BY id;
DROP TABLE lateral_array;
SELECT (SELECT provsql FROM personnel WHERE id = 1) IS NOT NULL AS token;

-- A condition on an aggregate result other than a comparison or IS [NOT]
-- NULL (x = ANY(array_agg(...))) is refused, in WHERE as in HAVING.
SELECT city FROM (SELECT city, array_agg(id) AS ids FROM personnel
                  GROUP BY city) t WHERE 3 = ANY(ids);
SELECT city FROM personnel GROUP BY city HAVING 3 = ANY(array_agg(id));

-- crosstab (tablefunc) runs its source query itself and builds its rows in C:
-- over a tracked relation it is refused, as the provenance column of that
-- query would break the row type it declares.  Over an untracked relation
-- (here a copy whose provenance is removed), and with provsql.active off, it
-- answers, the same rows.
CREATE EXTENSION IF NOT EXISTS tablefunc;
CREATE TABLE ct_plain AS SELECT id, city, classification FROM personnel;
SELECT remove_provenance('ct_plain');
SELECT * FROM crosstab(
  'SELECT city, classification, count(*) FROM personnel GROUP BY 1, 2 ORDER BY 1, 2',
  $$VALUES ('secret'), ('top_secret')$$) AS ct(city text, s bigint, t bigint);
SELECT * FROM crosstab(
  'SELECT city, classification, count(*) FROM ct_plain GROUP BY 1, 2 ORDER BY 1, 2',
  $$VALUES ('secret'), ('top_secret')$$) AS ct(city text, s bigint, t bigint)
ORDER BY city;
SET provsql.active = off;
SELECT * FROM crosstab(
  'SELECT city, classification, count(*) FROM personnel GROUP BY 1, 2 ORDER BY 1, 2',
  $$VALUES ('secret'), ('top_secret')$$) AS ct(city text, s bigint, t bigint)
ORDER BY city;
SET provsql.active = on;
DROP TABLE ct_plain;
DROP EXTENSION tablefunc;

-- A locking clause (FOR UPDATE, FOR SHARE, SKIP LOCKED), at any level of a
-- statement over a tracked relation, is outside the semantics: refused.
SELECT id FROM personnel FOR UPDATE;
SELECT p.id FROM personnel p LEFT JOIN personnel q ON q.id = p.id + 1
  WHERE q.id IS NULL FOR UPDATE OF p SKIP LOCKED;
SELECT id FROM personnel WHERE id NOT IN
  (SELECT id FROM personnel WHERE city = 'Paris' FOR SHARE);
-- FOR KEY SHARE is let through: PostgreSQL's foreign-key checks take it over
-- the referenced table, at commit too for a deferred constraint.
CREATE TABLE lk_parent(id int PRIMARY KEY);
CREATE TABLE lk_child(pid int REFERENCES lk_parent DEFERRABLE INITIALLY DEFERRED);
INSERT INTO lk_parent VALUES (1);
SELECT add_provenance('lk_parent');
BEGIN;
INSERT INTO lk_child VALUES (1);
COMMIT;
INSERT INTO lk_child VALUES (2);
SELECT pid FROM lk_child;
DROP TABLE lk_child, lk_parent;