-
Notifications
You must be signed in to change notification settings - Fork 2
Expand file tree
/
Copy pathmisc.sql
More file actions
113 lines (89 loc) · 3.25 KB
/
Copy pathmisc.sql
File metadata and controls
113 lines (89 loc) · 3.25 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
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;
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;
SELECT city, count(*)
FROM test_customers
GROUP BY city
HAVING city LIKE 'B%';
SELECT age, count(*)
FROM test_patients
GROUP BY age
HAVING age IS NULL OR age > 40;
SELECT 'London' IN (SELECT city FROM test_customers);
-- 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;
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;
EXPLAIN SELECT *
FROM (
SELECT age, count(*)
FROM test_patients
GROUP BY age
HAVING age < 65
) x
WHERE x.age > 15;
----------------------------------------------------------------
-- 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;
SELECT count(*), city FROM test_customers GROUP BY city;
-- Same aggregate can be selected multiple times
SELECT count(*), count(*) FROM test_customers;
-- Get rejected because of disallowed utility statement
COPY test_customers TO STDOUT;
ALTER TABLE test_customers DROP COLUMN id;
-- EXPLAIN is censored
EXPLAIN SELECT city FROM test_customers LIMIT 4;
EXPLAIN (COSTS false) SELECT city FROM test_customers LIMIT 4;
-- EXPLAIN is blocked
EXPLAIN ANALYZE SELECT city FROM test_customers LIMIT 4;
EXPLAIN (COSTS) SELECT city FROM test_customers LIMIT 4;
-- EXPLAIN is left intact for non-anonymizing queries
EXPLAIN SELECT name FROM test_products LIMIT 4;
EXPLAIN (ANALYZE, SUMMARY false, TIMING false, COSTS true) SELECT name FROM test_products LIMIT 4;
-- EXPLAIN prints group/sort names
EXPLAIN SELECT city FROM test_customers ORDER BY 1;
-- Allow queries that exclude all rows
SELECT FROM test_customers WHERE FALSE;
SELECT TRUE AS "_" FROM test_customers WHERE 1 <> 1 LIMIT 0;
SELECT id FROM test_customers WHERE NULL = NULL;
-- JOIN between personal tables produces multiple AIDs
EXPLAIN VERBOSE SELECT COUNT(*) FROM test_customers c JOIN test_purchases pur ON c.id = cid;
-- Tolerate `diffix.agg_noise` in direct access level
SET pg_diffix.session_access_level = 'direct';
SELECT diffix.sum_noise(discount), diffix.count_noise(*) FROM test_customers;
-- Rejects marking an AID column.
CALL diffix.mark_not_filterable('test_customers', 'id');
CALL diffix.mark_filterable('test_customers', 'id');