cube-js / cube-js/cube

End time in a dateRange makes cube confused about preaggregations: consider support of open date ranges

Open
#3,982 9 comments 0 reactions 0 assignees View on GitHub
help wanted
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Summary**
If I make a request for `{ ... , "dateRange": ["2022-01-26 10:00" , "2022-01-26"]}` then that works totally fine, it finds the relevant pre-aggregation no problem.
However, `{ ... , "dateRange": ["2022-01-26 10:00" , "2022-01-26 12:00"]}` does not work. `{"error":"Error: No pre-aggregation exists for that query"}`.

This seems wrong: the preaggregation evidently does exist for the whole of 2022-01-26 (because the first request works), and so requesting only a part of that day should not throw an error.

**To Reproduce**

Docker compose file:
```
version: "2.2"

services:
cube:
image: cubejs/cube:latest
environment:
- CUBEJS_DB_TYPE=postgres
... connection to the db ...
- CUBEJS_API_SECRET=SECRET
- CUBEJS_DEV_MODE=true
- CUBEJS_ROLLUP_ONLY=true
volumes:
- .:/cube/conf
network_mode: "host"
```
Note that I am forcing queries to be served only if a preaggregation exists.

cube schema:

```
cube("Solar", {
sql: `
SELECT
most_recent.date,
most_recent.sp,
most_recent.forecast,
FROM
most_recent
`,
refreshKey: {
every: "5 minute",
},
measures: {
forecast: {
sql: "forecast",
type: "sum",
},
},
dimensions: {
time: {
sql: `(date + ((sp-1) * (INTERVAL '30 min')))`,
type: `time`,
primaryKey: true,
shown: true,
},
},
preAggregations: {
Aggregation1: {
external: true,
measures: [ CUBE.forecast ],
timeDimension: CUBE.time,
granularity: `minute`,
refreshKey: { every: `15 minute` },
buildRangeStart: {
sql: `SELECT NOW() - interval '1 days'`,
},
},
},
});
```
Note that my time dimension is calculated, in the data each day is composed of 48 "sp".

api request that fails:
```
http://localhost:4000/cubejs-api/v1/load?query={%22measures%22:[%22Solar.forecast%22],%22timeDimensions%22:[{%22dimension%22:%22Solar.time%22,%22granularity%22:%22minute%22,%22dateRange%22:[%222022-01-24%2010:00:00%22,%222022-01-26%2010:00%22]}],%22dimensions%22:[]}&queryType=multi
```

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.