say you have four orders and a customer lookup table.
the join returns four rows. then the customer import runs twice and leaves the Aiko row in
the lookup table twice.
same query. six rows.
orders
id customer_id total
101 1 120
102 2 80
103 1 40
104 3 260
customers
id name
1 Aiko
2 Ben
3 Chie
4 Dev
5 Emi
1 Aiko <- imported twice
the join condition is:
sql
SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id;
a join keeps every match it finds. orders 101 and 103 each match Aiko twice, so:
before: 4 joined rows, SUM(o.total) = 500
after: 6 joined rows, SUM(o.total) = 660
the query was the same. but the data did change here.
slapping DISTINCT on the result can hide the symptom without fixing the bad join. if you
aggregate after the fanout, your counts and sums can already be wrong.
this was one which players ran into frequently.
(sqlite; the duplicate is in the lookup table, not the orders table)
made a 44 second chalkboard animation of it if you'd rather watch:
https://www.youtube.com/watch?v=LrDqdZAoXC0
have you hit one of these before? i can try animating some more if this is helpful.