LOAD 'pg_diffix';
SET pg_diffix.noise_layer_sd = 7;
SET pg_diffix.low_count_layer_sd = 2;
SET pg_diffix.low_count_min_threshold = 2;
SET ROLE diffix_test;
SET pg_diffix.session_access_level = 'anonymized_trusted';
----------------------------------------------------------------
-- Sanity checks
----------------------------------------------------------------
SELECT diffix.access_level();
    access_level    
--------------------
 anonymized_trusted
(1 row)

----------------------------------------------------------------
-- Basic queries
----------------------------------------------------------------
SELECT COUNT(*) FROM test_customers;
 count 
-------
     4
(1 row)

SELECT COUNT(*) FROM test_purchases;
 count 
-------
    23
(1 row)

SELECT COUNT(city), COUNT(DISTINCT city) FROM test_customers;
 count | count 
-------+-------
     4 |     1
(1 row)

SELECT COUNT(DISTINCT cid) FROM test_purchases;
 count 
-------
     9
(1 row)

SELECT city, COUNT(DISTINCT id) FROM test_customers GROUP BY 1;
 city | count 
------+-------
 *    |    16
 Rome |     8
(2 rows)

SELECT COUNT(*) FROM test_customers WHERE planet = 'Earth';
 count 
-------
    17
(1 row)

----------------------------------------------------------------
-- Basic queries - sum
----------------------------------------------------------------
SELECT SUM(id), diffix.sum_noise(id) FROM test_customers;
 sum | sum_noise 
-----+-----------
  34 |        58
(1 row)

SELECT SUM(discount), diffix.sum_noise(discount) FROM test_customers;
    sum    | sum_noise 
-----------+-----------
 7.8854094 |         8
(1 row)

SELECT city, SUM(id), diffix.sum_noise(id) FROM test_customers GROUP BY 1;
 city | sum | sum_noise 
------+-----+-----------
 *    | 122 |        54
 Rome |  64 |        60
(2 rows)

SELECT city, SUM(discount), diffix.sum_noise(discount) FROM test_customers GROUP BY 1;
 city |    sum    | sum_noise 
------+-----------+-----------
 *    | 12.012415 |       9.5
 Rome |  4.601863 |       4.4
(2 rows)

----------------------------------------------------------------
-- Basic queries - avg
----------------------------------------------------------------
SELECT city, AVG(discount), diffix.avg_noise(discount) FROM test_customers GROUP BY 1
EXCEPT
SELECT city, SUM(discount) / COUNT(discount), diffix.sum_noise(discount) / COUNT(discount) FROM test_customers GROUP BY 1;
 city | avg | avg_noise 
------+-----+-----------
(0 rows)

SELECT city, AVG(id), diffix.avg_noise(id) FROM test_customers GROUP BY 1
EXCEPT
SELECT city, SUM(id)::float8 / COUNT(id), diffix.sum_noise(id) / COUNT(id) FROM test_customers GROUP BY 1;
 city | avg | avg_noise 
------+-----+-----------
(0 rows)

----------------------------------------------------------------
-- Reporting noise
----------------------------------------------------------------
SELECT diffix.count_noise(*), diffix.count_noise(city), diffix.count_noise(DISTINCT city) FROM test_customers;
 count_noise |    count_noise    | count_noise 
-------------+-------------------+-------------
           7 | 6.800000000000001 |           0
(1 row)

----------------------------------------------------------------
-- Basic queries - expanding constants in target expressions
----------------------------------------------------------------
SELECT 1 FROM test_patients;
 ?column? 
----------
        1
        1
        1
        1
(4 rows)

SELECT cast(1 as real) FROM test_patients;
 float4 
--------
      1
      1
      1
      1
(4 rows)

SELECT 1, COUNT(*) FROM test_patients;
 ?column? | count 
----------+-------
        1 |     4
(1 row)

SELECT 1, city FROM test_customers;
 ?column? | city 
----------+------
        1 | *
        1 | *
        1 | *
        1 | *
        1 | *
        1 | *
        1 | *
        1 | *
        1 | *
        1 | *
        1 | *
        1 | *
        1 | *
        1 | *
        1 | *
        1 | *
        1 | Rome
        1 | Rome
        1 | Rome
        1 | Rome
        1 | Rome
        1 | Rome
        1 | Rome
        1 | Rome
(24 rows)

SELECT city, 'aaaa' FROM test_customers;
 city | ?column? 
------+----------
 *    | aaaa
 *    | aaaa
 *    | aaaa
 *    | aaaa
 *    | aaaa
 *    | aaaa
 *    | aaaa
 *    | aaaa
 *    | aaaa
 *    | aaaa
 *    | aaaa
 *    | aaaa
 *    | aaaa
 *    | aaaa
 *    | aaaa
 *    | aaaa
 Rome | aaaa
 Rome | aaaa
 Rome | aaaa
 Rome | aaaa
 Rome | aaaa
 Rome | aaaa
 Rome | aaaa
 Rome | aaaa
(24 rows)

----------------------------------------------------------------
-- Multi-AID queries
----------------------------------------------------------------
SELECT city FROM test_patients GROUP BY 1;
 city 
------
 *
(1 row)

SELECT COUNT(*), COUNT(city), COUNT(DISTINCT city) FROM test_patients;
 count | count | count 
-------+-------+-------
     4 |     4 |     0
(1 row)

----------------------------------------------------------------
-- LCF & Filtering
----------------------------------------------------------------
SELECT city FROM test_customers;
 city 
------
 *
 *
 *
 *
 *
 *
 *
 *
 *
 *
 *
 *
 *
 *
 *
 *
 Rome
 Rome
 Rome
 Rome
 Rome
 Rome
 Rome
 Rome
(24 rows)

SELECT city FROM test_customers GROUP BY 1 HAVING length(city) <> 4;
 city 
------
 *
(1 row)

SELECT COUNT(*), COUNT(city), COUNT(DISTINCT city) FROM test_customers WHERE city = 'London';
 count | count | count 
-------+-------+-------
     0 |     0 |     0
(1 row)

-- LCF doesn't depend on the bucket seed, both queries should have same noisy threshold.
SELECT diffix.floor_by(age, 30), COUNT(*) FROM test_patients GROUP BY 1;
 floor_by | count 
----------+-------
          |    23
(1 row)

SELECT diffix.floor_by(age, 30), diffix.floor_by(age, 106), COUNT(*) FROM test_patients GROUP BY 1, 2;
 floor_by | floor_by | count 
----------+----------+-------
          |          |     9
(1 row)

----------------------------------------------------------------
-- Empty tables
----------------------------------------------------------------
SELECT COUNT(*), COUNT(city), COUNT(DISTINCT city) FROM empty_test_customers;
 count | count | count 
-------+-------+-------
     0 |     0 |     0
(1 row)

----------------------------------------------------------------
-- WHERE clauses
----------------------------------------------------------------
-- Filtering and grouping produce identical results
(SELECT count(*) FROM test_customers GROUP BY city HAVING city = 'Berlin')
UNION
(SELECT count(*) FROM test_customers WHERE city = 'Berlin');
 count 
-------
    17
(1 row)

(SELECT count(*) FROM test_customers GROUP BY city, diffix.round_by(discount, 2)
  HAVING city = 'Berlin' AND diffix.round_by(discount, 2) = 0)
UNION
(SELECT count(*) FROM test_customers WHERE city = 'Berlin' AND diffix.round_by(discount, 2) = 0);
 count 
-------
     7
(1 row)

(SELECT count(*) FROM test_customers WHERE diffix.round_by(discount, 2) = 0 GROUP BY city HAVING city = 'Berlin')
UNION
(SELECT count(*) FROM test_customers WHERE city = 'Berlin' AND diffix.round_by(discount, 2) = 0);
 count 
-------
     7
(1 row)