LOAD 'pg_diffix';
SET pg_diffix.strict = false;
SET pg_diffix.low_count_min_threshold = 2;
SET pg_diffix.low_count_layer_sd = 0;
SET ROLE diffix_test;
SET pg_diffix.session_access_level = 'anonymized_trusted';
----------------------------------------------------------------
-- Post processing anonymized results
----------------------------------------------------------------
SELECT * FROM
  (SELECT count(*) AS num_purchases FROM test_purchases) x,
  (SELECT count(*) AS num_customers FROM test_customers) y;
 num_purchases | num_customers 
---------------+---------------
            34 |            16
(1 row)

SELECT
  coalesce(patients.city, customers.city) AS city,
  customers.count AS num_customers,
  patients.count AS num_patients
FROM
  (SELECT city, count(*) FROM test_patients GROUP BY 1) patients
FULL OUTER JOIN
  (SELECT city, count(*) FROM test_customers GROUP BY 1) customers
ON patients.city = customers.city;
  city  | num_customers | num_patients 
--------+---------------+--------------
 Rome   |             7 |            2
 Berlin |             9 |            6
 *      |             2 |             
(3 rows)

SELECT city, count(*)
FROM test_customers
GROUP BY city
HAVING city LIKE 'B%';
  city  | count 
--------+-------
 Berlin |     9
(1 row)

SELECT age, count(*)
FROM test_patients
GROUP BY age
HAVING age IS NULL OR age > 40;
 age | count 
-----+-------
     |     2
  57 |     2
  64 |     2
(3 rows)

-- Prevent post-processing filters from being pushed down
EXPLAIN SELECT age, count(*)
FROM test_patients
GROUP BY age
HAVING (age IS NULL OR age > 65) AND count(*) > 5;
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Custom Scan (BucketScan)
   Filter: (((age IS NULL) OR (age > 65)) AND ((diffix.anon_count_star(id, name)) > 5))
   ->  HashAggregate
         Group Key: age
         ->  Seq Scan on test_patients
(5 rows)

EXPLAIN SELECT *
FROM (
  SELECT age, count(*)
  FROM test_patients
  GROUP BY age
) x
WHERE (x.age IS NULL OR x.age > 65) AND x.count > 5;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Subquery Scan on x
   Filter: (((x.age IS NULL) OR (x.age > 65)) AND (x.count > 5))
   ->  Custom Scan (BucketScan)
         ->  HashAggregate
               Group Key: test_patients.age
               ->  Seq Scan on test_patients
(6 rows)

EXPLAIN SELECT *
FROM (
  SELECT age, count(*)
  FROM test_patients
  GROUP BY age
  HAVING age < 65
) x
WHERE x.age > 15;
                 QUERY PLAN                  
---------------------------------------------
 Subquery Scan on x
   Filter: (x.age > 15)
   ->  Custom Scan (BucketScan)
         Filter: (age < 65)
         ->  HashAggregate
               Group Key: test_patients.age
               ->  Seq Scan on test_patients
(7 rows)

----------------------------------------------------------------
-- Miscellaneous queries
----------------------------------------------------------------
DO $$
BEGIN
  PERFORM count(*) FROM test_customers;
END;
$$;
-- Order of labels and aggregates is respected
SELECT city, count(*) FROM test_customers GROUP BY city;
  city  | count 
--------+-------
 *      |     2
 Rome   |     7
 Berlin |     9
(3 rows)

SELECT count(*), city FROM test_customers GROUP BY city;
 count |  city  
-------+--------
     2 | *
     7 | Rome
     9 | Berlin
(3 rows)

-- Same aggregate can be selected multiple times
SELECT count(*), count(*) FROM test_customers;
 count | count 
-------+-------
    16 |    16
(1 row)

-- Get rejected because of disallowed utility statement
COPY test_customers TO STDOUT;
ERROR:  [PG_DIFFIX] Statement requires direct access level.
ALTER TABLE test_customers DROP COLUMN id;
ERROR:  [PG_DIFFIX] Statement requires direct access level.
-- EXPLAIN is censored
EXPLAIN SELECT city FROM test_customers LIMIT 4;
                  QUERY PLAN                  
----------------------------------------------
 Limit
   ->  Custom Scan (BucketScan)
         ->  HashAggregate
               Group Key: city
               ->  Seq Scan on test_customers
(5 rows)

EXPLAIN (COSTS false) SELECT city FROM test_customers LIMIT 4;
                  QUERY PLAN                  
----------------------------------------------
 Limit
   ->  Custom Scan (BucketScan)
         ->  HashAggregate
               Group Key: city
               ->  Seq Scan on test_customers
(5 rows)

-- EXPLAIN is blocked
EXPLAIN ANALYZE SELECT city FROM test_customers LIMIT 4;
ERROR:  [PG_DIFFIX] EXPLAIN ANALYZE is not allowed for queries involving personal tables
EXPLAIN (COSTS) SELECT city FROM test_customers LIMIT 4;
ERROR:  [PG_DIFFIX] COSTS option is not allowed for queries involving personal tables
-- EXPLAIN is left intact for non-anonymizing queries
EXPLAIN SELECT name FROM test_products LIMIT 4;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Limit  (cost=0.00..0.07 rows=4 width=32)
   ->  Seq Scan on test_products  (cost=0.00..22.00 rows=1200 width=32)
(2 rows)

EXPLAIN (ANALYZE, SUMMARY false, TIMING false, COSTS true) SELECT name FROM test_products LIMIT 4;
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.07 rows=4 width=32) (actual rows=4 loops=1)
   ->  Seq Scan on test_products  (cost=0.00..22.00 rows=1200 width=32) (actual rows=4 loops=1)
(2 rows)

-- EXPLAIN prints group/sort names
EXPLAIN SELECT city FROM test_customers ORDER BY 1;
                  QUERY PLAN                  
----------------------------------------------
 Sort
   Sort Key: city
   ->  Custom Scan (BucketScan)
         ->  HashAggregate
               Group Key: city
               ->  Seq Scan on test_customers
(6 rows)