rollupLambda silently drops/loses data outside the last-listed rollup's build range

---
Bug report
---

**Describe the bug**

A `rollupLambda` pre-aggregation that blends two `rollup` members (a small,
frequently-refreshed "hot" rollup and a larger, less-frequently-refreshed
"batch" rollup, in the documented `rollups: [batch, hot]` oldest-to-newest
order) can silently return **incomplete or entirely empty results** for
queries whose requested date range is not fully covered by the *last-listed*
("hot") member's own build range:

- Queries whose date range falls entirely **outside** the hot rollup's build
range are supposed to be served entirely by the batch rollup. Instead, the
hot rollup's single most-recent (structurally-irrelevant) partition gets
unioned into the result as if it were valid data, purely because it is the
last-listed `rollupLambda` member.
- Queries whose date range **straddles** the hot/batch boundary (the common
"last N months ending today" shape) can silently drop the entire
batch-covered portion of the range, returning HTTP 200 with a
plausible-looking but incomplete total and no warning, error, or indication
that data is missing.

This reproduces identically whether the two rollups share the same
`partitionGranularity` or use different granularities (e.g. `day` for hot,
`month` for batch) — granularity mismatch is not the variable that matters.
It also reproduces on both Postgres and Athena-backed cubes; Postgres is used
below purely because it's easy to run locally.

**To Reproduce**

1. Define a cube with two `rollup` pre-aggregations and one `rollupLambda`
that blends them (schema below): `batch` covers everything older than the
last 14 days, refreshed daily; `hot` covers the last 14 days, refreshed
every 10 minutes; `lambda` is `{ type: 'rollupLambda', rollups: [batch, hot] }`.
2. Seed a table with rows spanning well over a year, so both the batch and
hot build ranges have real data.
3. Run a query whose date range falls entirely within `hot`'s build range
(e.g. last 5 days) — this works correctly.
4. Run a query whose date range falls entirely within `batch`'s territory,
i.e. more than 14 days before "today" (e.g. `[today-150d, today-100d]`).
5. Run a query whose date range straddles the boundary, ending "today" (e.g.
`[today-90d, today]`) — the realistic "last N months" shape.
6. Inspect the generated SQL (via the `usedPreAggregations` field of the
`/cubejs-api/v1/load` response, or Cubestore's `system.query_cache`
table). For steps 4 and 5, the hot rollup's single most-recent partition
is unioned into the query even though it is completely irrelevant to the
requested range — see "Root cause" below for why this can (and, in the
originating production incident, did) turn into fully dropped rows rather
than harmlessly-filtered ones.

**Expected behavior**

When a `rollupLambda` member's build range has zero overlap with the
requested query range, that member should either be excluded from the union
entirely, or the query should fall back to the source database / return a
clear error — not silently substitute an out-of-range placeholder partition
that gets treated as valid data. A query spanning both members should always
return the full, correct union of both, with no silent gaps.

**Minimally reproducible Cube Schema**

Generic "IoT sensor readings" domain, no external dependencies beyond
Postgres:

```javascript
// schema/SensorReadings.js
cube(`SensorReadings`, {
sql: `SELECT * FROM public.sensor_readings`,

measures: {
count: { type: `count` },
avgTemperature: { sql: `temperature`, type: `avg` },
},

dimensions: {
id: { sql: `id`, type: `number`, primaryKey: true },
deviceId: { sql: `device_id`, type: `number` },
sensorType: { sql: `sensor_type`, type: `string` },
readingTs: { sql: `reading_ts`, type: `time` },
},

preAggregations: {
// Declared first only so Cube's declaration-order pre-aggregation
// matching (see findPreAggregationForQuery below) picks the lambda
// blend instead of one of the raw member rollups for a plain query.
lambda: {
type: `rollupLambda`,
rollups: [SensorReadings.batch, SensorReadings.hot],
},
batch: {
type: `rollup`,
external: true,
measures: [SensorReadings.count, SensorReadings.avgTemperature],
dimensions: [SensorReadings.deviceId, SensorReadings.sensorType],
timeDimension: SensorReadings.readingTs,
granularity: `day`,
partitionGranularity: `day`, // reproduces the same way with `month` here
buildRangeStart: { sql: `SELECT '2000-01-01'::timestamp` },
buildRangeEnd: { sql: `SELECT (CURRENT_DATE - INTERVAL '14 days')::timestamp` },
refreshKey: { every: `1 day` },
},
hot: {
type: `rollup`,
external: true,
measures: [SensorReadings.count, SensorReadings.avgTemperature],
dimensions: [SensorReadings.deviceId, SensorReadings.sensorType],
timeDimension: SensorReadings.readingTs,
granularity: `day`,
partitionGranularity: `day`,
buildRangeStart: { sql: `SELECT (CURRENT_DATE - INTERVAL '14 days')::timestamp` },
buildRangeEnd: { sql: `SELECT CURRENT_DATE::timestamp` },
refreshKey: { every: `10 minute` },
},
},
});
```

```sql
-- seed/init.sql
CREATE TABLE sensor_readings (
id BIGSERIAL PRIMARY KEY,
device_id INT NOT NULL,
sensor_type TEXT NOT NULL,
temperature NUMERIC(5,2) NOT NULL,
reading_ts TIMESTAMP NOT NULL
);

-- One row per day for the last 400 days, so both the batch and hot
-- build ranges have real data to work with.
INSERT INTO sensor_readings (device_id, sensor_type, temperature, reading_ts)
SELECT
(1 + (n % 5)) AS device_id,
(ARRAY['ambient','coolant','exhaust'])[1 + (n % 3)] AS sensor_type,
(18 + (n % 15))::numeric(5,2) AS temperature,
(CURRENT_DATE - (n || ' days')::interval) AS reading_ts
FROM generate_series(0, 400) AS n;
```

```yaml
# docker-compose.yml
services:
postgres:
image: postgres:16-alpine
environment:
POSTGRES_USER: cube
POSTGRES_PASSWORD: cube
POSTGRES_DB: sensors
ports:
- "15432:5432"
volumes:
- ./seed:/docker-entrypoint-initdb.d

cubestore:
image: cubejs/cubestore:latest
platform: linux/amd64 # only needed on arm64 hosts, e.g. Apple Silicon
environment:
- CUBESTORE_SERVER_NAME=cubestore:3030
ports:
- "3030:3030"

cube:
image: cubejs/cube:latest
platform: linux/amd64 # only needed on arm64 hosts
depends_on:
- postgres
- cubestore
environment:
- CUBEJS_DB_TYPE=postgres
- CUBEJS_DB_HOST=postgres
- CUBEJS_DB_PORT=5432
- CUBEJS_DB_NAME=sensors
- CUBEJS_DB_USER=cube
- CUBEJS_DB_PASS=cube
- CUBEJS_CUBESTORE_HOST=cubestore
- CUBEJS_CUBESTORE_PORT=3030
- CUBEJS_DEV_MODE=true
- CUBEJS_API_SECRET=repro-secret
ports:
- "4000:4000"
volumes:
- ./schema:/cube/conf/model # "schema" dir is deprecated in 1.7.x, use "model"
```

Query used against `/cubejs-api/v1/load` (swap the `dateRange` for each of
the three scenarios above):

```json
{
"measures": ["SensorReadings.count"],
"timeDimensions": [{
"dimension": "SensorReadings.readingTs",
"dateRange": ["2026-02-20", "2026-04-11"]
}]
}
```

**Live verification performed (this report)**

The schema/compose files above were run end-to-end (Cube `1.7.4`, Postgres
`16`, Cubestore `latest`, all via Docker) and the generated SQL was inspected
via the `usedPreAggregations` field of the `/cubejs-api/v1/load` response,
which is the same information Cubestore's `system.query_cache` table exposes.
This confirms the mechanism described in "Root cause" is live and exactly as
the source predicts:

- For a query date range entirely inside `hot`'s build range (last 5 days),
the lambda correctly unions only `hot`'s 6 real partitions; `batch`'s
contribution is present but wrapped in `WHERE 1 = 0` (`count: 6`, correct).
- For a query date range entirely inside `batch`'s territory
(`2026-02-20`..`2026-04-11`, i.e. >14 days before "today"), the generated
SQL unions in `sensor_readings_hot20260720...` — **`hot`'s single
most-recent partition** — even though that partition's date has no
relationship whatsoever to the requested range. This is the exact
placeholder-substitution + `lastRollupLambda` bypass described below,
observed live rather than inferred from source alone.
- For the straddling case (`[today-90d, today]`), both members' partitions
are unioned, including the same always-included `hot` tail.

In this specific, freshly-seeded local run, the end-user-visible symptom
stayed masked: the outer query's date filter dropped the irrelevant
placeholder row, and `batch`'s partitions here are always freshly and fully
built (Postgres, on-demand, sub-second), so they pass the
`buildRangeEnd === partitionRange[1]` completeness check on
[`PreAggregationPartitionRangeLoader.ts:277`](#root-cause) and are not
excluded. Getting the *full* zero-row / silently-truncated-total symptom
additionally requires that check to fail for `batch` too — i.e. for
`batch`'s own selected partitions to not (yet) be fully built/refreshed to
their natural partition boundary. That is a timing/staleness condition
inherent to a live, continuously-refreshing deployment with a
less-frequently-refreshed batch layer (the exact `hot`/`batch` split this
feature exists for) — e.g. an Athena/S3-partitioned batch rollup whose
catalog/build cadence lags — and it is not something a fresh, instantly-built
local Postgres sandbox reproduces on demand. The schema/compose files above
are provided as a verified-working base that demonstrates the core
placeholder+bypass mechanism live; reproducing the full dropped-rows symptom
on demand additionally needs an artificially staled/partially-built `batch`
member, which was not independently re-created in this environment.

**Version**

Cube.js / Cube Core version: confirmed present on current `master`
(`1.7.4`, commit `89b63d3`, checked 2026-07-20). Independently reported and
still open at least as far back as `1.6.54` through `1.7.2`.

**Additional context**

### Root cause

This is the combination of two pieces of logic in
`@cubejs-backend/query-orchestrator`, plus one piece in
`@cubejs-backend/schema-compiler` that sets up the condition for the first
two to matter:

1. **`lastRollupLambda` is set purely by list position.**
[`packages/cubejs-schema-compiler/src/adapter/PreAggregations.ts:1221`](https://github.com/cube-js/cube/blob/89b63d38b35350147a8c006c125fbe0041570465/packages/cubejs-schema-compiler/src/adapter/PreAggregations.ts#L1221):

```ts
referencedPreAggregations[i] = {
...referencedPreAggregations[i],
preAggregation: {
...referencedPreAggregations[i].preAggregation,
unionWithSourceData: i === referencedPreAggregations.length - 1 ? preAggObj.preAggregation.unionWithSourceData : false,
rollupLambdaId: `${cube}.${preAggregationName}`,
lastRollupLambda: i === referencedPreAggregations.length - 1,
rollupLambdaTimeDimensionsReference: preAggObj.references.timeDimensions,
}
};
```

Per Cube's own documented convention, `rollups` is ordered oldest-to-newest,
so the *last* entry — the "hot" rollup in this setup — is the one flagged
`lastRollupLambda: true`.

2. **An out-of-range member gets a placeholder pointing at its own last partition.**
[`packages/cubejs-query-orchestrator/src/orchestrator/PreAggregationPartitionRangeLoader.ts:424-439`](https://github.com/cube-js/cube/blob/89b63d38b35350147a8c006c125fbe0041570465/packages/cubejs-query-orchestrator/src/orchestrator/PreAggregationPartitionRangeLoader.ts#L424-L439):

```ts
private async partitionRanges(ignoreMatchedDateRange?: boolean): Promise {
const buildRange = await this.loadBuildRange();

let dateRange = PreAggregationPartitionRangeLoader.intersectDateRanges(
buildRange,
ignoreMatchedDateRange ? undefined : this.preAggregation.matchedTimeDimensionDateRange,
);

if (!dateRange) {
// If there's no date range intersection between query data range and pre-aggregation build range
// use last partition so outer query can receive expected table structure.
dateRange = [buildRange[1], buildRange[1]];
}
...
```

When a member's own build range has zero intersection with the query's
requested date range, Cube doesn't skip the member — it substitutes the
member's single most-recent partition purely so the outer `UNION ALL`
query has *a* table with the right column structure to reference.

3. **The completeness check that would normally filter that placeholder back out is skipped for the last-listed member.**
[`packages/cubejs-query-orchestrator/src/orchestrator/PreAggregationPartitionRangeLoader.ts:276-285`](https://github.com/cube-js/cube/blob/89b63d38b35350147a8c006c125fbe0041570465/packages/cubejs-query-orchestrator/src/orchestrator/PreAggregationPartitionRangeLoader.ts#L276-L285):

```ts
const filteredResults = loadResults.filter(
r => (this.preAggregation.lastRollupLambda || reformatInIsoLocal(r.buildRangeEnd) === reformatInIsoLocal(r.partitionRange[1])) &&
rollupLambdaResults.every(result => !result[1].buildRangeEnd || reformatInIsoLocal(result[1].buildRangeEnd) < reformatInIsoLocal(r.partitionRange[0]))
);
if (filteredResults.length === 0) {
emptyResult = true;
loadResults = [loadResults[loadResults.length - 1]];
} else {
loadResults = filteredResults;
}
```

For every *non-last* member, a result only survives this filter if its
`buildRangeEnd` exactly equals its own partition's natural end — i.e. that
partition was actually, fully built out to that point, not just used as a
structural placeholder. For the `lastRollupLambda` member, the
`this.preAggregation.lastRollupLambda ||` short-circuits that check
entirely: an out-of-range placeholder partition from step 2 is treated as
a perfectly valid contributor.

The combined effect: if *no* member's result survives the filter,
`emptyResult` is set and **every** selected table gets `WHERE 1 = 0`
appended (see `unionTargetTableName` construction a few lines below,
`loadPreAggregations()` around line 296-300) — HTTP 200, zero rows,
batch never queried. If the batch member's own selected partitions also
fail the freshness check (e.g. because the less-frequently-refreshed
batch layer hasn't finished building out to the requested range yet — a
realistic condition for any deployment where batch refreshes lag behind
hot), this is exactly what happens for a query entirely in batch's
territory. If batch's partitions *do* pass (as in the fully-built local
repro above), the placeholder row is merely harmlessly filtered by the
outer date predicate — but the underlying bypass is identical, and any
staleness on the batch side flips it from "harmless" to "silently wrong."

### Related issues

These appear related and worth cross-referencing (all confirmed open/closed
as of 2026-07-20):

- [cube-js/cube#9638](https://github.com/cube-js/cube/issues/9638) — "Rollup
Lambda wrong sql query when using timezone" (open). Different trigger
(timezone-localized vs. non-localized batch/hot boundary timestamps
producing a data-loss gap), but the same `rollupLambda` boundary-handling
area.
- [cube-js/cube#10398](https://github.com/cube-js/cube/issues/10398) —
"Intermittent Data Inconsistency: T-2 Day Data Missing in Range Queries
but Available in Single-Date Lookups" (open). Symptom (data silently
missing from range queries only) is consistent with the
`lastRollupLambda`-bypass + staleness interaction described above.
- [cube-js/cube#3294](https://github.com/cube-js/cube/issues/3294) —
"Support variable partition granularity for pre-aggregations" (closed,
reclassified as a feature request). Documents the closely related, more
general problem that pre-aggregation *selection* doesn't validate whether
a pre-aggregation's build range actually covers the requested query range
at all — the same underlying gap, one layer up from the `rollupLambda`-
specific bypass reported here.

### Suggested fix direction

The `lastRollupLambda ||` short-circuit on
`PreAggregationPartitionRangeLoader.ts:277` should not unconditionally waive
the completeness check — at minimum, a placeholder partition (one produced
via the `buildRange[1], buildRange[1]]` substitution in `partitionRanges()`,
i.e. one that does not actually intersect
`this.preAggregation.matchedTimeDimensionDateRange`) should still be
excluded, or the `emptyResult`/fallback-to-source logic should trigger,
rather than treating a structurally-necessary-but-otherwise-irrelevant
placeholder as real data for the last member specifically.

Start with the rollupLambda handling in packages/cubejs-schema-compiler/src/adapter/PreAggregations.ts:1221 and the partition-range logic in packages/cubejs-query-orchestrator/src/orchestrator/PreAggregationPartitionRangeLoader.ts:277 and 424-439. Run the provided Postgres and Cubestore reproduction, inspect usedPreAggregations and generated SQL for out-of-range members, and verify that queries spanning or missing a member return complete data without irrelevant placeholder partitions.

Written by the indexing model from the issue text.