skip to content

A SQL report over a UNION ALL unpivot, with WHERE region = 'EU' after the last branch, shows every region for January to November and only EU for December; what went wrong, and how do you verify an unpivot?

level: seniorimportance: nice to knowfreq 22%

answer

  1. what does one WHERE belong to
  2. compare with where ORDER BY applies
  3. one clause for the whole stack
  4. count rows, then total each month

basics

~20 s

A WHERE clause belongs to one SELECT, so after the last branch it filters December only. Filter around the stack or use LATERAL VALUES, then verify with row counts and per-month totals against the source.

solid answer

~50 s

`WHERE` belongs to one `SELECT`, not to the whole stack, so written after the last branch it filters December and leaves the other eleven months returning every region. Nothing errors, because the syntax is valid. The confusion is understandable: an `ORDER BY` or `LIMIT` at the end does apply to the combined result. The fix is to wrap the stack in a derived table or CTE and filter outside it, or switch to `CROSS JOIN LATERAL (VALUES ...)`, which has one `WHERE`. Repeating the filter in all twelve branches also works, but invites the same bug on the next edit. A sibling bug is a branch labelled `3` that selects `feb_sales`, which is equally silent. So I verify any unpivot the same way: the row count equals source rows times columns, less any NULLs dropped, and each column's total matches its label's total.

code

sql · 16 lines
sql
WITH source_totals (month_no, total) AS (
  SELECT 1, SUM(jan_sales) FROM sales_wide
  UNION ALL
  SELECT 2, SUM(feb_sales) FROM sales_wide
  UNION ALL
  SELECT 3, SUM(mar_sales) FROM sales_wide
),
long_totals AS (
  SELECT month_no, SUM(amount) AS total
  FROM long_sales
  GROUP BY month_no
)
SELECT s.month_no, s.total AS source_total, l.total AS long_total
FROM source_totals AS s
LEFT JOIN long_totals AS l ON l.month_no = s.month_no
WHERE s.total IS DISTINCT FROM l.total;

go deeper

for a junior

Remember that each SELECT in a UNION ALL has its own WHERE. A filter written at the bottom applies to the last SELECT only, not to the whole statement.

for a middle

Contrast WHERE with ORDER BY and LIMIT, which do apply to the combined output at the end of a set operation. Show the rewrite with a derived table or CTE.

for a senior

Read the symptom's shape: a step at exactly the last month points at the last branch. Then reconcile row counts and per-column totals, since a yearly total cannot see mislabelled months.

for a principal

Reconciliation should be routine rather than one person's habit. Decide which checks run in every load, and who gets alerted when a reshape step's numbers stop matching the source.

## The bug Each `SELECT` in a set operation has its own `FROM` and its own `WHERE`. When the whole statement is on the page it looks like one query with a filter at the bottom, but the grammar disagrees. ```sql SELECT product_id, region, 1 AS month_no, jan_sales AS amount FROM sales_wide UNION ALL SELECT product_id, region, 2, feb_sales FROM sales_wide UNION ALL SELECT product_id, region, 3, mar_sales FROM sales_wide WHERE region = 'EU'; ``` The filter here belongs to the third `SELECT` alone. On a two-product sample with one EU and one US product, the first two months return both products and the third returns only the EU one, which is exactly the shape of the report in the question: every region for most of the year, then a sudden drop in the last month. ## Why it is easy to miss - **Nothing fails.** The statement is valid, so there is no error to investigate. - **The result looks reasonable.** The final month is the most recent, and a low recent figure often gets explained away as late data. - **The neighbouring clauses behave differently.** An `ORDER BY` or a `LIMIT` at the end of a set operation applies to the combined output, and the PostgreSQL documentation shows that a trailing `LIMIT` belongs to the whole union. People carry that expectation over to `WHERE`, where it does not hold. ## The fixes, compared | Fix | Filter written | Weakness | |---|---|---| | Filter in every branch | Twelve times | The next edit can miss one | | Wrap in derived table or CTE | Once | Relies on the planner to push it down | | `CROSS JOIN LATERAL (VALUES ...)` | Once | Spelling varies by engine | Wrapping looks like this, and it works on every engine that has CTEs: ```sql WITH long_sales AS ( SELECT product_id, region, 1 AS month_no, jan_sales AS amount FROM sales_wide UNION ALL SELECT product_id, region, 2, feb_sales FROM sales_wide UNION ALL SELECT product_id, region, 3, mar_sales FROM sales_wide ) SELECT product_id, month_no, amount FROM long_sales WHERE region = 'EU'; ``` On a large table, read the plan to see that the filter reaches each branch, rather than being applied after every branch has read everything. Planners typically push it down, and how far varies by engine. ## Related bugs with the same signature A long, repetitive statement invites copy-and-paste errors, and an unpivot's are mostly silent: - **Swapped labels**, such as `3` selecting `feb_sales` and `2` selecting `mar_sales`, move money between months and leave the yearly total correct. - **A repeated column**, with `feb_sales` in two branches, overstates one month and loses another. - **A missing branch** leaves eleven months and a smaller total. - **`UNION` in place of `UNION ALL`** in one spot removes duplicates from everything stacked so far, because these operators run left to right, so the effect depends on where the keyword sits. Because none of these raises an error, reading the query is weak protection. You want checks against the source. ## How to verify an unpivot Run these as ordinary queries, or better as tests in the load. 1. **Row count.** Expect source rows times unpivoted columns. If blanks are dropped, subtract the number of NULL cells. A difference points to a missing branch, a stray filter or a `UNION` that merged rows. 2. **Per-column totals.** For each month, `SUM(jan_sales)` from the wide table should equal `SUM(amount)` for label 1 in the long table. This catches repeated columns, and also mislabelling, which a yearly total misses. 3. **Key uniqueness.** If `product_id` is unique in the source, then `(product_id, month_no)` must be unique in the output. A duplicate shows a repeated label, and a later primary key or join would suffer from it. If the source key is not unique, the duplicates start there, so check that first. 4. **Per-group spot check.** Count rows per month and per region. The bug in this question shows up immediately as a one-month step in that count. Check 2 needs care with an all-NULL column, because `SUM` returns NULL on both sides, and `NULL = NULL` is not true. Use `IS NOT DISTINCT FROM` where it is available, or wrap both sides in `COALESCE`. Moving to one `WHERE` removes this particular bug class, but it does not remove the need to reconcile, because a `VALUES` list can still pair the wrong label with a column.

  • The yearly total of an unpivot matches the source exactly, yet a monthly chart looks wrong. What would you suspect?
    A mislabelled or swapped branch, such as label `3` reading `feb_sales` while label `2` reads `mar_sales`. Every amount is still present once, so the yearly total agrees and only the month attribution is wrong. A per-column check catches it, comparing each source column's total with its label's total. A yearly check cannot see it.
  • Does a filter wrapped around a UNION ALL stack mean every branch reads the whole table?
    Not necessarily. Planners typically push a simple predicate on a non-label column down into each branch, so each one filters as it reads. How far this goes varies by engine and by query shape, so read the plan on your engine before assuming either way. Each branch is still a separate read, though, which `LATERAL (VALUES ...)` avoids.

  • A WHERE after the last branch of a UNION ALL filters the whole combined result.
  • A filter on one branch would raise an error, so a clean run proves it is correct.
  • A matching yearly total proves that each month is labelled correctly.
  • Moving to LATERAL VALUES means an unpivot no longer needs reconciling against its source.