Verify a CTE Rewrite Without Losing Duplicate Rows

A query can look cleaner after a CTE rewrite and still lose rows. Comparing the first page or the total count does not establish equivalence. This PostgreSQL lab compares both directions and keeps duplicate rows in the comparison.

1. Compare the same data and the same output contract

Use a disposable PostgreSQL practice database and one psql session. This example was checked on PostgreSQL 18.6. If you need an isolated practice database, follow the practice-database guide. For introducing WITH itself, use the course lesson.

The output here is customer and amount for paid sales of at least 10.00. Two separate sales may produce identical output rows, and the customer can be NULL. Amounts use exact numeric values. The fixture also includes an unpaid sale, an amount below the threshold and a NULL amount.

2. Run the complete comparison in one session

Save this as compare.sql. Temporary tables/views belong to the session, and the final rollback removes this lab's transaction changes. Do not paste only the later queries into a new connection: those temporary objects will not exist there.

BEGIN;
CREATE TEMP TABLE cte_sales (
  sale_id integer PRIMARY KEY, customer text, amount numeric(10,2), paid boolean
);
INSERT INTO cte_sales VALUES
  (1, 'Ada', 20.00, true), (2, 'Ada', 20.00, true),
  (3, NULL, 10.00, true), (4, NULL, 10.00, true),
  (5, 'Bo', 5.00, true), (6, 'Cy', 99.00, false),
  (7, 'Di', NULL, true);

CREATE TEMP VIEW cte_before AS
SELECT customer, amount FROM (
  SELECT customer, amount FROM cte_sales WHERE paid = true
) AS paid_rows WHERE amount >= 10.00;

CREATE TEMP VIEW cte_after AS
WITH paid_rows AS (
  SELECT customer, amount FROM cte_sales WHERE paid = true
), qualifying_rows AS (
  SELECT customer, amount FROM paid_rows WHERE amount >= 10.00
)
SELECT customer, amount FROM qualifying_rows;

SELECT 'before_minus_after' AS direction, count(*) AS differences FROM (
  SELECT * FROM cte_before EXCEPT ALL SELECT * FROM cte_after
) AS missing
UNION ALL
SELECT 'after_minus_before', count(*) FROM (
  SELECT * FROM cte_after EXCEPT ALL SELECT * FROM cte_before
) AS added;

SELECT customer, amount FROM cte_after
ORDER BY customer NULLS LAST, amount;

CREATE TEMP VIEW cte_bad AS
WITH paid_rows AS (
  SELECT DISTINCT customer, amount FROM cte_sales WHERE paid = true
)
SELECT customer, amount FROM paid_rows WHERE amount >= 10.00;

SELECT 'plain_except' AS comparison, count(*) AS differences FROM (
  (SELECT * FROM cte_before EXCEPT SELECT * FROM cte_bad)
  UNION ALL
  (SELECT * FROM cte_bad EXCEPT SELECT * FROM cte_before)
) AS set_difference
UNION ALL
SELECT 'except_all', count(*) FROM (
  (SELECT * FROM cte_before EXCEPT ALL SELECT * FROM cte_bad)
  UNION ALL
  (SELECT * FROM cte_bad EXCEPT ALL SELECT * FROM cte_before)
) AS bag_difference;

SELECT customer, amount FROM cte_before
EXCEPT ALL SELECT customer, amount FROM cte_bad;
ROLLBACK;

With the practice-container variables from the linked setup guide, run the file from PowerShell:

Get-Content ./compare.sql -Raw | docker --context $pgContext exec -i $pgName psql -X -v ON_ERROR_STOP=1 -U practice -d practice_lab
if ($LASTEXITCODE -ne 0) { throw 'Comparison script failed.' }

Or open psql in your disposable database and run \i compare.sql using a path readable by that psql process. -X skips startup customization; ON_ERROR_STOP makes a script error stop the run. If an interactive run stops before rollback, issue ROLLBACK; before continuing.

3. Read both directions of the good comparison

before_minus_after | 0
after_minus_before | 0

Zero in both directions means neither result has extra row occurrences for this fixture. The ordered result has four rows: Ada/20.00 twice, followed by NULL/10.00 twice. psql normally displays NULL as a blank field. The amount-NULL row is excluded because its threshold comparison is unknown.

Both queries select the same columns in the same order with compatible types. EXCEPT ALL compares row occurrences, including matching NULL-containing rows. A manual equality join on nullable columns needs different handling because NULL = NULL is not true.

4. See why plain EXCEPT misses the broken rewrite

The deliberately bad CTE adds DISTINCT. It collapses two Ada rows into one and two NULL-customer rows into one. The comparison prints:

plain_except | 0
except_all   | 2

Plain EXCEPT sees the same distinct values on each side. EXCEPT ALL exposes the missing occurrence of Ada/20.00 and NULL/10.00. Adding DISTINCT merely to make a join “look right” can silently change a report. Fix the cause of duplication or preserve it according to the report's actual contract.

5. Check ordering and performance separately

The comparison establishes a multiset of rows, not presentation order. Keep a final ORDER BY for a report. This fixture sorts customer with NULLS LAST, then amount; identical output rows remain indistinguishable. For paginated business rows, include a unique stable key as the final tie-breaker. A LIMIT without a deterministic order can compare different subsets.

Equal results do not imply equal performance. After correctness checks, inspect a representative plan with EXPLAIN; use EXPLAIN ANALYZE only when executing that query is appropriate. CTE planning depends on the query and PostgreSQL version, so do not assume every CTE is materialized or faster. For live changing data, compare against one consistent snapshot rather than running the two versions at unrelated times.

6. Exercise a filter change

In the good rewrite only, change amount >= 10.00 to amount > 10.00. Run the complete script again. Expected answer: before-minus-after becomes 2, after-minus-before stays 0, because both NULL-customer sales at exactly 10.00 are missing. Restore the inclusive threshold. This tests the boundary without changing duplicate handling.

References: PostgreSQL EXCEPT and ALL, ordering guarantees and CTE evaluation.