LOAD 'pg_diffix';
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 = 'publish_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 |            10 |            2
 *      |             2 |             
(3 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 |    10
(3 rows)

SELECT count(*), city FROM test_customers GROUP BY city;
 count |  city  
-------+--------
     2 | *
     7 | Rome
    10 | 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)