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

[peterklingelhofer]

---

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:

// 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` },
    },
  },
});

-- 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;

# 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):

{
  "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 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:

   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:

   private async partitionRanges(ignoreMatchedDateRange?: boolean): Promise<PartitionRanges> {
     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:

   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 — "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 —
  "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 —
  "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.

[paveltiunov]

Confirmed via code inspection and a unit test. Root cause matches the report exactly, at the exact lines cited.

PreAggregationPartitionRangeLoader.loadPreAggregations() (packages/cubejs-query-orchestrator/src/orchestrator/PreAggregationPartitionRangeLoader.ts:424-439): when a rollupLambda member's own build range has zero intersection with the query's matchedTimeDimensionDateRange, partitionRanges() substitutes a placeholder pointing at that member's own most-recent partition ([buildRange[1], buildRange[1]]), purely so the outer UNION ALL has a table to reference.

The completeness filter a few lines later (~L276-279) only checks that a candidate partition was built out to its own natural boundary (buildRangeEnd === partitionRange[1]) — it never checks whether the partition's range actually intersects the requested query range at all. Since a genuinely-built "hot" partition's buildRangeEnd trivially equals its own partitionRange[1], this placeholder passes the filter regardless of the lastRollupLambda bypass, and is unioned into the query as if it were valid data — exactly the "silently substitute an out-of-range placeholder partition" behavior described.

I added a failing test that pins this down directly against PreAggregationPartitionRangeLoader.loadPreAggregations() (no real DB/driver needed — PreAggregationLoader.loadPreAggregation is mocked to return a canned, fully-built partition):
packages/cubejs-query-orchestrator/test/unit/PreAggregations.test.ts — rollupLambda out-of-range placeholder bypass (issue #11317) › excludes hot rollup from the union when its build range has zero overlap with the query

I also tried the naive fix (dropping the this.preAggregation.lastRollupLambda || short-circuit) and confirmed it does not fix this case — the placeholder's buildRangeEnd genuinely equals its own partitionRange[1] (it's not stale, just irrelevant), so that condition passes either way. A real fix needs the completeness check to also verify the candidate partition actually intersects matchedTimeDimensionDateRange (i.e. wasn't produced via the placeholder substitution), not just that it was fully built to its own boundary — consistent with the "Suggested fix direction" in the report.

Left only the reproduction test on branch claude/serene-mendel-i3otnb.

Marking this as a confirmed Bug.

---

Generated by Claude Code