R2 SQL: 40004 "partition N not available" on a global aggregate over a UNION ALL with asymmetric branch partitioning (EXPLAIN returns 200)

> Reported from a production Cloudflare Workers application using R2 SQL (beta) against an R2 Data Catalog warehouse. Everything below is measured; no production identifiers or row values are included.

---

## 1. Summary

A `SELECT` whose `FROM` is a two-branch `UNION ALL` fails at **execution** with
HTTP 400 and error code `40004`, while `EXPLAIN` on the identical statement
returns 200. The two branches are assigned **different partition counts**
(1 vs N), and the operator consuming the union — `NetworkCoalesceExec` — appears
to address input partitions **by index**, so it requests an index the 1-partition
branch does not have. The error text names exactly that index and that count.

The failure is **data-shape dependent and intermittent**. It occurs when the two
`UNION ALL` branches receive an asymmetric partition count under a
`NetworkCoalesceExec` boundary (measured), but the same asymmetric plan has been
observed returning both 400 AND 200 at execution: on 2026-08-31 one tenant
failed a 90-day range (400) while succeeding a 7-day one (200) in the same
minute, and a second tenant SUCCEEDED a 90-day range whose EXPLAIN plan was
structurally identical to a capture that had failed hours earlier. So EXPLAIN plan determinism (8/8
byte-identical captures) is NOT execution determinism — the static plan and the
runtime task assignment can diverge. The trigger is understood; the exact
condition deciding 400 vs 200 for one asymmetric plan is not (runtime assignment
is not exposed by EXPLAIN).

### Error (verbatim)

```
400 [40004] invalid query: partition 1 not available.
The head plan AggregateExec of the stage just has 1 partitions
```

---

## 2. Environment

| Fact | Value |
|---|---|
| Product | R2 SQL (beta) |
| Warehouse | `cafe24-analytics` |
| Namespace | `default` |
| Tables | `default.daily_visitors` (rollup, partitioned by `date`), `default.events_v2` (raw event stream) |
| Client | `wrangler r2 sql query` and the R2 SQL HTTP API — both reproduce |
| First observed | 2026-08-29 (production error tracking) |
| Measured for this report | 2026-08-31 |

`EXPLAIN`, `SHOW` and `DESCRIBE` do not scan data and are free per the R2 SQL
pricing documentation, so the plan captures below cost nothing to reproduce.

No mall identifiers, visitor identifiers or row values from production appear in
this document. `` and `` below are placeholders.

---

## 3. The failing query

Shape (the application's merged funnel — five conditional distinct counts over a
union of a rollup branch and a today branch):

```sql
SELECT
COUNT(DISTINCT visitor_id) AS pv,
COUNT(DISTINCT CASE WHEN did_click = 1 THEN visitor_id END) AS click,
COUNT(DISTINCT CASE WHEN did_buy_click = 1 THEN visitor_id END) AS buy,
COUNT(DISTINCT CASE WHEN did_cart_click= 1 THEN visitor_id END) AS cart,
COUNT(DISTINCT CASE WHEN did_convert = 1 THEN visitor_id END) AS conv
FROM (
-- branch A: COMPLETED days, from the rollup table
SELECT visitor_id, did_click, did_buy_click, did_cart_click, did_convert
FROM (
SELECT v.date AS date, v.visitor_id AS visitor_id,
MAX(v.did_click) AS did_click,
MAX(v.did_buy_click) AS did_buy_click,
MAX(v.did_cart_click) AS did_cart_click,
MAX(v.did_convert) AS did_convert
FROM default.daily_visitors v
INNER JOIN (
SELECT date, MAX(run_id) AS run_id
FROM default.daily_visitors
WHERE mall_id = '__rollup__' AND visitor_id = '__complete__'
AND date IN ('', '', ...)
GROUP BY date
) cr ON v.date = cr.date AND v.run_id = cr.run_id
WHERE v.mall_id = ''
AND v.date IN ('', '', ...)
GROUP BY v.mall_id, v.date, v.visitor_id
LIMIT 100000
) completed

UNION ALL

-- branch B: TODAY, from the raw event stream
SELECT visitor_id,
MAX(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) AS did_click,
MAX(CASE WHEN event_type = 'click' AND click_type = 'buy' THEN 1 ELSE 0 END) AS did_buy_click,
MAX(CASE WHEN event_type = 'click' AND click_type = 'cart' THEN 1 ELSE 0 END) AS did_cart_click,
MAX(CASE WHEN event_type = 'conversion' THEN 1 ELSE 0 END) AS did_convert
FROM default.events_v2
WHERE mall_id = '' AND timestamp >= AND timestamp <
AND visitor_id IS NOT NULL
GROUP BY visitor_id
) merged
```

Result: **HTTP 400, `[40004]`** — intermittently, for a data shape that produces the asymmetric plan (see the note in the Summary on why it is not every execution).

---

## 4. The plan that fails

`EXPLAIN` on the statement above returns **HTTP 200** and the physical plan
below (excerpted at the boundary that matters):

```
[Stage 5] => NetworkCoalesceExec: output_partitions=18, input_tasks=3
DistributedUnionExec: t0:[c0] t1:[c1(0/2)] t2:[c1(1/2)]
```

`t0` — the completed/rollup branch — is assigned a **single** partition, while
the today branch is split across `t1`/`t2`. The consumer above the union
declares 18 output partitions over 3 input tasks and addresses them by index;
the reported "partition 1 not available … just has 1 partitions" is precisely
`t0` being asked for index 1.

A later capture at higher data volume shows the same asymmetry with a different
split — the today branch grew from 2 partitions to 3:

```
[Stage 5] => NetworkCoalesceExec: output_partitions=24, input_tasks=4
DistributedUnionExec: t0:[c0] t1:[c1(0/3)] t2:[c1(1/3)] t3:[c1(2/3)]
```

The invariant across both captures: **only the today branch splits; the rollup
branch stays at one partition.**

### Plan determinism

`EXPLAIN` was executed **8 consecutive times** on the failing statement. All 8
returned a byte-identical plan (sha256 of the full plan text: `120803b52e41…`),
with no variation in the union assignment or the boundary counts.

Caveat: the 8 runs shared a short time window and therefore likely a single
cluster condition. This establishes determinism *for a given data shape*, not
across load or time.

---

## 5. Minimal passing variants

Both were executed against the same warehouse in the same session.

### 5a. Empty today branch → 200

With a `todayWhere` window that selects no rows, branch B contributes nothing:

```
DistributedUnionExec: t0:[c0] t1:[c1]
NetworkCoalesceExec: output_partitions=2, input_tasks=2
```

Symmetric 1-and-1 assignment; **HTTP 200**. This is why the failure looks
intermittent from the outside: it disappears exactly when today has no traffic.

### 5b. A `GROUP BY` above the union → 200

Adding a grouping key above the union changes the boundary operator:

```
NetworkShuffleExec: output_partitions=6, input_tasks=3
RepartitionExec: Hash([...])
```

The union assignment stays asymmetric — the fix is not that the asymmetry goes
away, but that a hash repartition replaces the index-addressed coalesce, so
there is no partition index to be missing. **HTTP 200**.

> This variant is **not** a usable workaround for the original query: grouping
> changes the aggregate's meaning (the sum of per-group distinct counts is not
> the global distinct count — measured 2,616 vs the correct 2,448). It is
> included solely as evidence for where the defect lives.

---

## 6. Ruled out

Each of these was executed and observed, not reasoned about:

| Hypothesis | Verdict |
|---|---|
| The inner `LIMIT 100000` triggers it | **Refuted.** A variant with no `LIMIT` and a narrow today window still returns `40004`. |
| `COUNT(DISTINCT …)` triggers it | **Refuted.** A bare `count(*)` over the same union also returns `40004`. |
| Only exact-distinct aggregation is affected | **Refuted** by the `count(*)` result above; switching to `approx_distinct` does not avoid it. |
| It is a planning failure | **Refuted.** `EXPLAIN` returns 200 for the exact statement that fails at execution, so the failure is in distributed execution/assignment, not planning. |
| It is time-of-day dependent | **Refuted** as a *time* dependency — reproduced at midday. But it IS data-shape / range dependent: the same mall fails a 90d range and passes a 7d range in the same minute. |
| Retrying helps | **No basis.** R2 SQL publishes no retry semantics for this error class. (The failure is not deterministic per data shape — an identical plan has returned both 400 and 200 — so a retry MIGHT succeed, but nothing documents or guarantees it.) |

---

## 7. What we would like from the fix

Either of these resolves it for us:

1. `NetworkCoalesceExec` (or whatever consumes `DistributedUnionExec`) tolerates
branches with differing partition counts, rather than addressing input
partitions by a shared index space; or
2. the planner assigns `UNION ALL` branches a consistent partition count, or
inserts a repartition when they differ — the behaviour variant 5b already
exhibits.

A documented error/retry contract for the 4xxxx code space would also let
clients distinguish "retry" from "never retry"; today `40004` is indistinguishable
from an ordinary client SQL error at the transport level.

---

## 8. Our containment (for context)

**Root-cause fix (a value-safe SQL rewrite that avoids the failing boundary).** A
value-preserving rewrite of the merged funnel DOES exist and has been adopted (implemented as task 2.0 of the same change): wrapping
the `UNION ALL` in an inner `GROUP BY visitor_id` + `MAX(did_*)` (the same shape
`buildReturningMergedSql` already uses — zero 40004s in 90 days of Sentry data for that query) inserts
a `NetworkShuffleExec` + `RepartitionExec: Hash([visitor_id])` boundary directly
above the union, so the index-aligned-partition requirement that produces the
40004 is gone. Measured 2026-08-31: EXPLAIN shows the shuffle boundary on all 6
tested (mall, range) cells; on the 3 cells executed, the five funnel values were
IDENTICAL to the exact raw baseline (two malls, 7d and 90d, pv 7 to 6,773).
`MAX(did_*)` is value-preserving because a visitor flagged on ANY branch stays
flagged, and the outer `COUNT(DISTINCT)` then counts already-unique visitor rows.

**Why the containment below is KEPT despite the fix.** The rewrite is proven
VALUE-SAFE, not proven EFFICACIOUS: this batch never captured a BASE 400 paired
with a rewrite 200 on the SAME request, because the failure is intermittent — so
"the rewrite fixed it" cannot be asserted from measurement, only that it changes
no value and produces the pass-boundary plan. The containment therefore stays as
a second layer: if the rewrite ever fails to prevent a 40004 on some unobserved
shape, the funnel still degrades to `null`/raw-fallback rather than a measured
zero.

The application also does not attempt to out-SQL the planner beyond that one
boundary-shaping wrap. On a merged-funnel
rejection it re-issues the funnel **once** against the raw event stream — a
shape with no `UNION ALL`, which structurally avoids the defect — and if that
also fails the funnel is reported as `null` (unavailable) rather than as a
measured zero. See `contain-r2sql-funnel-degrade` and
`app/features/analytics/application/services/dashboard-query.service.ts`
(`mergedFunnelWithRawFallback`).

**Accuracy consequence, stated plainly.** When a merged-funnel execution fails
(intermittent — §1; §4 establishes only PLAN determinism, not execution
determinism) and the raw fallback serves the funnel instead, that response is
approximate. Before the root-cause fix above was adopted this fallback was the
common path for today-inclusive presets on malls the Durable Object serving
tier does not answer; with the fix adopted it narrows to the executions the
fix does not rescue. The merged query counts with exact
`COUNT(DISTINCT visitor_id)`
(`app/features/analytics/application/rollup/rollup-read.ts:314`); the raw
fallback uses the raw path's existing distinct policy — exact for a single-day
range, `approx_distinct` (HyperLogLog) for a multi-day one
(`dashboard-query.service.ts:3856` `distinctExpr`). So on that path the funnel,
and the 방문자 / 전환율 KPIs derived from it, are **estimates**, with no marker
in the UI saying so.

This is an improvement, not a regression — before this change the same
condition rendered a confident `0`. It is recorded here because the accepted
trade-off ("approximate on a recovered fallback") reads as exceptional in
`design.md` while the measurement says it is the steady state. Two facts bound
it: neither Cloudflare nor upstream DataFusion documents an error bound for
`approx_distinct` (both state only the algorithm), and the single measurement
this repo has is a DO-exact 324 vs approx 321 — 0.93 %. Forcing the fallback to
exact was considered and NOT done: Cloudflare's own guidance is to prefer
`approx_distinct` on large datasets, and "any aggregate with `DISTINCT`" is
budget-gated with a 400, so forcing exact would trade a known small error for a
new failure mode.

Start by reproducing the R2 SQL query and comparing its EXPLAIN plan with execution, focusing on the DistributedUnionExec and NetworkCoalesceExec boundary described in the issue. Review app/features/analytics/application/rollup/rollup-read.ts:314 and app/features/analytics/application/services/dashboard-query.service.ts, including mergedFunnelWithRawFallback and distinctExpr. Done means the global UNION ALL aggregate no longer returns 40004 for asymmetric branch partitioning, or the documented error/retry behavior is provided.

Written by the indexing model from the issue text.