cube-js / cube-js/cube

Better error message when joining original sql pre-aggregation to some non-aggregated data

Open
#1,181 1 comment 0 reactions 0 assignees View on GitHub
backend:server Roadmap: 2021 Q1
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 10h
Merged PRs (30d)
203

Description

**Describe the bug**
you can't use original sql pre-aggregation and join to some non-aggregated data. When you try to, Cube attempts to join with the main, analytics database and the pre-agg database (which could be external).

**To Reproduce**
```sql
SELECT date_trunc('day', (cast("t1".report_date AS TIMESTAMP)::timestamptz AT TIME ZONE 'UTC')) "t1__report_date_day",
sum("t1".sales) "t1__sales"
FROM ananlytics_db.t1 AS "t1"
LEFT JOIN stb_pre_aggregations_dev.companies_main_rc4lezhz_aljrstgp_1fnp97r AS "companies" ON "t1".company_id = "companies".id
WHERE (cast("t1".report_date AS TIMESTAMP) > $1::timestamptz)
AND ("companies".company_id = $2)
GROUP BY 1
ORDER BY 1 DESC LIMIT 10000
```
```
ERROR: relation "ananlytics_db.t1" does not exist
```

**Expected behavior**
Throw a warning with a descriptive message, not try to perform a query in the preagg db that will throw an exception.

**Version:**
0.20.12

Contributor guide

Open the contributing guide

Research direction

Start by tracing how Cube handles a query that combines original SQL pre-aggregations with non-aggregated data, using the SQL reproduction as the entry point. Compare the current pre-aggregation database query path with the expected warning behavior; done means the invalid cross-database attempt is replaced by a descriptive warning.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.