Why does UNION drop a receipt from another branch?

Prepare the diagnostic input

Receipt 001 is local to each branch, so East and West can legitimately share the same local ID and quantity. UNION removes identical projected rows. Include source scope and use UNION ALL for independent accepted records rather than treating their equal values as retry duplicates.

Use a fresh SQLite test database for the complete setup below. Every record is fictional; quantities are units and input monetary columns ending in cents use integer currency cents. Calculated averages may include fractions of a cent. Input previews include every table in this small fixture.

CREATE TABLE east_receipts(local_id TEXT PRIMARY KEY,qty INTEGER);
CREATE TABLE west_receipts(local_id TEXT PRIMARY KEY,qty INTEGER);
INSERT INTO east_receipts VALUES ('001',5);
INSERT INTO west_receipts VALUES ('001',5);

Reproduce the misleading result and correct it

SELECT SUM(qty) AS total_qty FROM (SELECT local_id,qty FROM east_receipts UNION SELECT local_id,qty FROM west_receipts);

SELECT SUM(qty) AS total_qty FROM (SELECT 'East' AS branch,local_id,qty FROM east_receipts
UNION ALL SELECT 'West',local_id,qty FROM west_receipts);

Two five-unit receipts should total ten units. The wrong merge leaves one five-unit row. Adding branch identity preserves their origin and allows a later audit of each receipt instead of merely forcing the total to match.

Check the boundary before applying the fix

Add West receipt 002 for five units. The corrected total becomes 15, and both West receipts remain independent local identities.

For the boundary test, recreate the complete setup in another fresh synthetic database, apply only the change below, then rerun the corrected query. Do not apply a test mutation to a live system.

INSERT INTO west_receipts VALUES ('002',5);

UNION ALL does not prevent repeated imports of the same branch receipt. Idempotent ingestion needs a stable branch-plus-local-ID constraint upstream.

The displayed bad, corrected and boundary results were executed in SQLite and checked against separately authored expected rows. This demonstrates the stated fixture only; it does not test other database engines, production performance or your live schema.