There is a bug in the rewriting of SELECT gender, COUNT(movie_id) as role_count FROM imdb_ijs.actors JOIN imdb_ijs.roles ON roles.actor_id = actors.id GROUP BY gender ORDER BY gender

[ngrislain]

minimal_bug.ipynb.zip

# Bounds are added to some columns
dataset = (Dataset.from_database('imdb', engine, 'imdb_ijs')
  .imdb_ijs.actors.gender.with_possible_values(['M', 'F'])
  .imdb_ijs.actors.id.with_unique_constraint())

# Privacy unit definition
privacy_unit = [
    # The column `id` directly defines the privacy unit (here we want to protect actors' privacy)
    ("actors", [], "id"),
    # The column `actor_id` refers to the column `id` of table `actors`, the `id` of which defines the privacy unit
    ("roles", [("actor_id", "actors", "id")], "id"),
]
# Privacy budget (see https://en.wikipedia.org/wiki/Differential_privacy)
budget = {"epsilon": 1.0, "delta": 1e-3}

# We create a basic aggregation relation
query = "SELECT gender, COUNT(movie_id) as role_count FROM imdb_ijs.actors JOIN imdb_ijs.roles ON roles.actor_id = actors.id GROUP BY gender ORDER BY gender"
relation = dataset.relation(query)
# The relation is turned into a DP equivalent
relation_with_dp_event = relation.rewrite_with_differential_privacy(
    dataset,
    privacy_unit,
    budget,
)
# Rewritten relation
dp_relation = relation_with_dp_event.relation()
# The rewritten query
dp_query = dp_relation.to_query()
# Privacy loss (see https://github.com/google/differential-privacy/tree/main/python/dp_accounting)
dpe = relation_with_dp_event.dp_event()
print(dpe)```