Five CTE Traps That Return the Wrong Answer Quietly

Part 8 of The Practical Data Analyst. Part 1 covered window functions, Part 3 covered JOIN traps, Part 5 covered aggregation traps. This one covers the WITH clause, the part of SQL most analysts reach for to make a long query readable, and the part where readability hides the most silent wrong answers.

The setup: twelve orders, and the numbers that are actually true

Everything below runs on SQLite 3.45.1, in memory, with one small table. Four customers, twelve orders, one month column so the filter-order trap has something to work with.

CREATE TABLE orders (order_id INTEGER PRIMARY KEY,
                     customer_id TEXT, amount REAL, month TEXT);
INSERT INTO orders VALUES
 (1,'A',200,'Feb'),(2,'A',150,'Mar'),(3,'A',50,'Mar'),
 (4,'B',90,'Feb'), (5,'B',300,'Mar'),(6,'B',80,'Mar'),
 (7,'C',400,'Feb'),(8,'C',120,'Mar'),(9,'C',60,'Mar'),
 (10,'D',70,'Feb'),(11,'D',110,'Mar'),(12,'D',45,'Mar');

Ground truth, verified before any trap ran: 12 rows, $1,675 total. Keep those two numbers in mind. Every trap below is a query that returns something else while looking correct.

Trap 1: the CTE ran once, and the random value refused to change

A CTE feels like a stored result. Write it once, reference it twice, and the natural assumption is that it computed once and both references read the same rows. In SQLite that assumption held. In other engines it does not, and the difference is invisible in the query text.

WITH r AS (SELECT random() AS x)
SELECT (SELECT x FROM r) AS a, (SELECT x FROM r) AS b;

I ran this five times. The two columns were identical in all five runs. Then I added one hint:

WITH r AS NOT MATERIALIZED (SELECT random() AS x)
SELECT (SELECT x FROM r) AS a, (SELECT x FROM r) AS b;

Now the two columns differed in all five runs. Same query shape, opposite behavior, decided by one keyword.

The SQLite documentation states the rule plainly: since version 3.35.0, a CTE referenced more than once is treated as if MATERIALIZED, and a CTE referenced once is treated as if NOT MATERIALIZED. Materialized means computed once into an ephemeral table and read from there. Not materialized means substituted as a subquery at every reference, so a volatile function like random() runs fresh each time. PostgreSQL 12 and later follow a similar rule in the other direction: a side-effect-free CTE referenced once is folded into the outer query by default, and only a multiply-referenced CTE stays materialized.

The trap is assuming either behavior is universal. Code that relies on "the CTE runs once" breaks when the engine inlines it. Code that relies on "it runs fresh each time" breaks when the engine materializes it. The practical cases are random sampling, CURRENT_TIMESTAMP inside a CTE, and any expensive function you expected to run once and pay for once.

The fix: say what you mean. Use AS MATERIALIZED when two references must see the same rows. Use AS NOT MATERIALIZED when you want the planner to inline it. And test the assumption with a volatile value before you trust it, because the default changes between engines and between versions.

Trap 2: the recursion with no end

Recursive CTEs generate rows until the recursive term returns nothing. Forget the stop condition and the query never finishes. The dangerous part is how easy it is to hide the problem with LIMIT.

WITH RECURSIVE cnt(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cnt
)
SELECT n FROM cnt LIMIT 5;

This returns 1, 2, 3, 4, 5 and looks like a working counter. Remove the LIMIT and it never returns at all. I measured how far it would go: with LIMIT 100000, the largest value produced was 100000. There is no ceiling in the query itself. The LIMIT is a cap on what you read, not a stop condition on what the CTE generates.

The fix is a guard in the recursive term, not in the outer query:

WITH RECURSIVE cnt(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cnt WHERE n < 5
)
SELECT n FROM cnt;

This returns exactly 1, 2, 3, 4, 5 and then stops, because the row with n = 5 has no row with n < 5 to grow from. The guard belongs on the recursive term because that is the part that decides whether another row exists. A LIMIT on the outer query only decides how many of the infinite rows you watch.

Trap 3: the filter moved, and $235 moved with it

This is the trap that costs real money, because both versions of the query are reasonable SQL and they answer different questions. The question: top two orders per customer, restricted to March. There are two places to put the March filter, and they give two different totals.

Filter inside the CTE, before the window function:

WITH ranked AS (
  SELECT customer_id, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders WHERE month = 'Mar'
)
SELECT SUM(amount) FROM ranked WHERE rn <= 2;

Result: $915.

Window first, filter outside the CTE:

WITH ranked AS (
  SELECT customer_id, amount, month,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders
)
SELECT SUM(amount) FROM ranked WHERE rn <= 2 AND month = 'Mar';

Result: $680. The gap is $235 on a $1,675 table, about 14% of the total.

The per-customer breakdown shows where it went:

Customer	Filter first	Window first
A	200	150
B	380	300
C	180	120
D	155	110
Total	915	680

Take customer A. Its orders are $200 in February, $150 in March, $50 in March. Filter first ranks only the March orders, so its top two are $150 and $50, totalling $200. Window first ranks all three orders, so its top two are the $200 February order and the $150 March order, and only the March one survives the outer filter, totalling $150. The February order did not disappear. It pushed the March $50 order out of the top two.

The first query answers "the top two March orders per customer". The second answers "the top two orders per customer, keeping the ones that happened in March". Both are legitimate questions. The trap is writing one while meaning the other, because the difference is the position of a single WHERE clause and nothing in the result flags it.

The fix: decide which question you are asking before you write the filter, and read the query back as a sentence. If the sentence says "top two in March", the filter goes inside, before the window. If it says "top two overall, then keep March", the filter goes outside, after it.

Trap 4: the CTE that stole the table's name

A CTE name shadows a table of the same name for the whole statement. The outer query reads the CTE, not the table, and nothing warns you.

The setup needs a second table. A staging table holds three rows totalling $400:

CREATE TABLE orders_staging (order_id INTEGER PRIMARY KEY,
                             customer_id TEXT, amount REAL, month TEXT);
INSERT INTO orders_staging VALUES
 (101,'A',200,'Mar'),(102,'B',120,'Mar'),(103,'C',80,'Mar');

Now the trap:

WITH orders AS (SELECT * FROM orders_staging)
SELECT COUNT(*), SUM(amount) FROM orders;

Result: 3 rows, $400. The same SELECT run as its own statement against the real orders table returns 12 rows, $1,675. One WITH clause changed the answer by $1,275, and the query text still says FROM orders in both cases.

One honest detail from the experiment: I first tried to build the CTE from the orders table itself, and SQLite refused with "circular reference: orders". The shadowing needs the CTE built from another source, which is exactly how it happens in practice: a CTE named after a table, defined from a staging table or a filtered copy, pasted into a script that also queries the real table. Every later reference in that statement silently reads the CTE.

The fix is a naming habit: never name a CTE after a table in the same statement. Prefix CTEs by role, filtered_orders or stg_orders, so a collision is visible at a glance. If you inherit a query with a CTE named after a table, rename the CTE before you trust any number it produced.

Trap 5: UNION ate a path

A recursive CTE that walks a hierarchy has a choice in its compound operator, and the choice changes the count. UNION dedupes. UNION ALL does not. When a node is reachable by two paths, UNION counts it once.

The hierarchy is a diamond. Root leads to a and b. Both a and b lead to leaf. Leaf leads to end.

CREATE TABLE edges (parent TEXT, child TEXT);
INSERT INTO edges VALUES
 ('root','a'),('root','b'),('a','leaf'),('b','leaf'),('leaf','end');

With UNION:

WITH RECURSIVE reach(n) AS (
  SELECT 'root'
  UNION
  SELECT child FROM edges JOIN reach ON edges.parent = reach.n
)
SELECT n FROM reach ORDER BY n;

Result: 5 rows. Root, a, b, leaf, end.

With UNION ALL, the same query returns 7 rows: root, a, b, leaf, leaf, end, end. Leaf appears twice because two paths reach it, and end appears twice because two paths pass through leaf to reach it.

The trap is counting. If you are counting distinct nodes, 5 is correct. If you are counting paths, descendants, or anything where multiplicity matters, 5 silently undercounts by two. The difference between the two queries is four characters, UNION versus UNION ALL, and the wrong one returns a clean, plausible, smaller number.

The fix: default to UNION ALL in recursive CTEs unless you have a specific reason to dedupe, and state the reason in a comment. UNION is the right choice when the hierarchy can cycle, because deduping is what stops the recursion from looping forever. That is a real reason. "It looked tidier" is not.

The verdict

All five traps share one shape. A CTE looks like a table and behaves like a query fragment whose execution the planner controls: when it runs, how many times it runs, what name it answers to, and whether duplicates survive. The query text shows none of that. The only defence is to run the query and check the answer against a number you already know, which is why every trap above starts from the same 12 rows and $1,675.

One more check, reported because it found nothing. I also tested whether joining through a CTE costs anything at scale: 100,000 orders across 5,000 customers, median of three runs. The direct join took 0.0666 seconds. The CTE version took 0.0648 seconds, a ratio of 0.97, with the identical answer of $25,445,984.65 across 5,000 customers. The execution plan looked worse for the CTE version, scanning the CTE output where the direct join searched an index, but the clock said otherwise. Do not rewrite working CTEs on plan-reading alone. Measure first, and when the measurement says there is no problem, say so.

Key takeaways

1. A CTE referenced twice is materialized in SQLite, and the same query can inline elsewhere. Test the assumption with a volatile value before you rely on it.
2. Every recursive CTE needs a stop condition in the recursive term. LIMIT hides a runaway; it does not fix it.
3. A filter inside the CTE and a filter outside it answer different questions when a window function sits between them. Put the filter where the question lives.
4. Never name a CTE after a table in the same statement. The CTE wins, silently, for exactly one statement.
5. UNION dedupes paths in a recursive CTE. Use UNION ALL when multiplicity matters, and say which one you meant.

Which of these five has bitten you, and what did it cost before you caught it?

Related on Everyday Data Science: Window Functions Are SQL's Most Underused Feature. Five Patterns Cover Nearly Everything. · Five JOIN Traps That Quietly Rewrite Your Numbers. One Orders Table Reported $620, $1,240 and $2,040. · Five Aggregation Traps That Rewrite Your Totals. I Measured Each One on Twelve Rows.

Sources: SQLite WITH clause documentation · SQLite window functions documentation · PostgreSQL WITH queries and CTE materialization