cube-js / cube-js/cube

`rollupLambda` with `unionWithSourceData` always queries the source, even when the requested date range is entirely historical

Open
#11,682 0 comments 0 reactions 1 assignee Claimed by @ovr View on GitHub
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Describe the bug**
When a `rollupLambda` pre-aggregation uses `unionWithSourceData: true`, Cube issues a live query against the source on every request, including when the requested date range lies entirely within already-built rollup partitions. The live query is lower-bounded by the end of the last matched partition and has no upper bound, so it aggregates every row after that point - all of which are discarded by the outer query.

**To Reproduce**
see repro (Postgres + Cube 1.7.28, `docker compose up -d`).

**Expected behavior**
Either skip the source query when `matchedTimeDimensionDateRange` does not overlap `]buildRangeEnd, ∞[`, or bound the live query by the requested range.

**Minimally reproducible Cube Schema**

```javascript
cube(`Events`, {
sql: `SELECT * FROM public.events`,

preAggregations: {
eventsLambda: {
type: `rollupLambda`,
unionWithSourceData: true,
rollups: [Events.eventsRollup],
},

eventsRollup: {
type: `rollup`,
measures: [Events.count],
timeDimension: Events.ts,
granularity: `day`,
partitionGranularity: `month`,
external: true,
scheduledRefresh: false,
buildRangeStart: { sql: `SELECT DATE '2024-01-01'` },
buildRangeEnd: { sql: `SELECT CURRENT_DATE` },
},
},

measures: {
count: { type: `count` },
},

dimensions: {
id: { sql: `id`, type: `number`, primaryKey: true },
status: { sql: `status`, type: `string` },
ts: { sql: `ts`, type: `time` },
},
});
```

```yaml
services:
postgres:
image: postgres:16
environment:
POSTGRES_USER: cube
POSTGRES_PASSWORD: cube
POSTGRES_DB: repro
ports:
- 5433:5432
volumes:
- ./init:/docker-entrypoint-initdb.d
healthcheck:
test: ["CMD-SHELL", "pg_isready -U cube -d repro"]
interval: 3s
timeout: 3s
retries: 20

cube:
image: cubejs/cube:v1.7.28
depends_on:
postgres:
condition: service_healthy
ports:
- 4001:4000
environment:
CUBEJS_DEV_MODE: "true"
CUBEJS_LOG_LEVEL: info
CUBEJS_DB_TYPE: postgres
CUBEJS_DB_HOST: postgres
CUBEJS_DB_PORT: 5432
CUBEJS_DB_NAME: repro
CUBEJS_DB_USER: cube
CUBEJS_DB_PASS: cube
CUBEJS_API_SECRET: repro-secret
volumes:
- ./cube.js:/cube/conf/cube.js
- ./model:/cube/conf/model
- cubestore:/cube/conf/.cubestore

volumes:
cubestore:
```

```sql
CREATE TABLE events (
id serial PRIMARY KEY,
ts timestamp NOT NULL,
status text NOT NULL
);

-- Faible volume sur le mois qui sera interrogé (fevrier 2024) : 696 lignes.
INSERT INTO events (ts, status)
SELECT g, 'queried'
FROM generate_series(timestamp '2024-02-01', timestamp '2024-02-29 23:00', interval '1 hour') g;

-- Fort volume APRES le mois interroge : ~1.3M lignes.
-- Aucune de ces lignes ne peut contribuer au resultat d'une requete sur fevrier 2024.
INSERT INTO events (ts, status)
SELECT g, 'noise'
FROM generate_series(timestamp '2024-03-01', timestamp '2026-08-01', interval '1 minute') g;

CREATE INDEX ON events (ts);
```

**Results**

With the request :
```json
{
"measures": ["Events.count"],
"timeDimensions": [
{
"dimension": "Events.ts",
"dateRange": ["2024-02-01", "2024-02-29"]
}
]
}
```

| | With `rollupLambda` | Without|
|---|---|---|
| Mesure 1 | 286 ms | 50 ms |
| Mesure 2 | 252 ms | 29 ms |
| Mesure 3 | 244 ms | 30 ms |
| Mesure 4 | 251 ms | 29 ms |
| **Médiane** | **~251 ms** | **~29 ms** |
| Requêtes live vers Postgres | 5 | **0** |
| Résultat | 696 | 696 |

**Version:**
1.7.28 (behaviour identical on master, 1.7.29)

**Additional context**

Issue created with the help of AI for both the writing and the repro steps

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.