LOAD 'pg_diffix';
SET pg_diffix.noise_layer_sd = 0;
SET pg_diffix.low_count_layer_sd = 0;
SET pg_diffix.outlier_count_min = 1;
SET pg_diffix.outlier_count_max = 1;
SET pg_diffix.top_count_min = 3;
SET pg_diffix.top_count_max = 3;
SET ROLE diffix_test;
SET pg_diffix.session_access_level = 'publish_trusted';
----------------------------------------------------------------
-- Sanity checks
----------------------------------------------------------------
SELECT diffix.access_level();
  access_level   
-----------------
 publish_trusted
(1 row)

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

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

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

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

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

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

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

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

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

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

----------------------------------------------------------------
-- Multi-AID queries
----------------------------------------------------------------
SELECT city FROM test_patients GROUP BY 1;
  city  
--------
 Rome
 Berlin
(2 rows)

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

----------------------------------------------------------------
-- LCF & Filtering
----------------------------------------------------------------
SELECT id FROM test_customers;
 id 
----
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
(16 rows)

SELECT city FROM test_customers;
  city  
--------
 *
 *
 Rome
 Rome
 Rome
 Rome
 Rome
 Rome
 Rome
 Berlin
 Berlin
 Berlin
 Berlin
 Berlin
 Berlin
 Berlin
(16 rows)

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

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

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

----------------------------------------------------------------
-- Minimal count
----------------------------------------------------------------
-- Zero for global aggregation
SELECT COUNT(DISTINCT planet) FROM test_customers;
 count 
-------
     1
(1 row)

-- `low_count_min_threshold` for queries with GROUP BY
SELECT city, COUNT(DISTINCT city) FROM test_customers GROUP BY 1;
  city  | count 
--------+-------
 *      |     2
 Rome   |     2
 Berlin |     2
(3 rows)

SELECT discount, COUNT(DISTINCT id) FROM test_customers GROUP BY 1;
 discount | count 
----------+-------
          |     2
      1.5 |     2
      0.5 |     4
        1 |     5
        2 |     2
(5 rows)