cube-js / cube-js/cube

Error: missing FROM-clause entry for table

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

Description

**Describe the bug**
In some circumstances it is not possible to use measures with filters

**To Reproduce**
1. Create this minimal Cube model:

Show model

```yaml
cubes:
- name: "dev_applications"
sql: >
select 1 as application_id, true as is_accepted, 1 as user_id
union all
select 2 as application_id, false as is_accepted, 2 as user_id

dimensions:
- name: application_id
sql: application_id
type: number
primary_key: true

- name: user_id
sql: user_id
type: number

joins:
- name: dev_users
relationship: many_to_one
sql: "{CUBE}.user_id = {dev_users.user_id}"

measures:
- name: total_count
type: count
sql: application_id

- name: accepted_count
type: count
sql: is_accepted
filters:
- sql: "{CUBE}.is_accepted"

- name: "dev_purchases"
sql: >
select 1 as purchase_id, 1 as user_id
union all
select 2 as purchase_id, 1 as user_id

dimensions:
- name: purchase_id
sql: purchase_id
type: number
primary_key: true

- name: user_id
sql: user_id
type: number

joins:
- name: dev_users
relationship: many_to_one
sql: "{CUBE}.user_id = {dev_users.user_id}"

measures:
- name: total_count
type: count
sql: purchase_id

- name: "dev_users"
sql: >
select 1 as user_id
union all
select 2 as user_id

dimensions:
- name: user_id
sql: user_id
type: number
primary_key: true

joins:
- name: dev_applications
relationship: one_to_many
sql: "{CUBE}.user_id = {dev_applications.user_id}"

- name: dev_purchases
relationship: one_to_many
sql: "{CUBE}.user_id = {dev_purchases.user_id}"

views:
- name: view_users
public: true

cubes:
- join_path: dev_users
includes:
- user_id

- join_path: "dev_users.dev_applications"
alias: applications
prefix: true
includes:
- total_count
- accepted_count

- join_path: "dev_users.dev_purchases"
alias: purchases
prefix: true
includes:
- total_count
```

2. Try a few queries that work fine

`applications_total_count` + `applications_accepted_count`
```json
{
"limit": 5000,
"measures": [
"view_users.applications_total_count",
"view_users.applications_accepted_count"
],
"dimensions": [
"view_users.user_id"
],
"timeDimensions": [],
"order": {
"view_users.applications_total_count": "desc"
}
}
```
![SCR-20240222-qmdm](https://github.com/cube-js/cube/assets/8266564/e3937097-502b-4fd0-922b-3162e92b8ca8)

`applications_total_count` + `purchases_total_count`
```json
{
"limit": 5000,
"measures": [
"view_users.applications_total_count",
"view_users.purchases_total_count"
],
"dimensions": [
"view_users.user_id"
],
"order": {
"view_users.applications_total_count": "desc"
}
}
```
![SCR-20240222-qmod](https://github.com/cube-js/cube/assets/8266564/5836e2e1-9de6-4fc1-9198-4eb328b6b2cc)

3. Try to combine these queries and see a `missing FROM-clause entry for table "dev_purchases"` error
```json
{
"limit": 5000,
"measures": [
"view_users.purchases_total_count",
"view_users.applications_accepted_count"
],
"dimensions": [
"view_users.user_id"
],
"order": {
"view_users.applications_total_count": "desc"
}
}
```
![SCR-20240222-qmsj](https://github.com/cube-js/cube/assets/8266564/62428085-fa27-4d9e-9d0a-e1b3ec51da97)

And indeed, generated SQL does not specify the table `"dev_purchases"`

Show generated SQL

```sql
SELECT
q_0."view_users__user_id",
count("dev_purchases".purchase_id) "view_users__purchases_total_count",
"dev_applications__accepted_count" "view_users__applications_accepted_count"
FROM
(
SELECT
"keys"."view_users__user_id",
count(
CASE
WHEN (
"dev_applications_key__dev_applications".is_accepted
) THEN "dev_applications_key__dev_applications".is_accepted
END
) "dev_applications__accepted_count"
FROM
(
SELECT
DISTINCT "dev_applications_key__dev_users".user_id "view_users__user_id",
"dev_applications_key__dev_applications".application_id "dev_applications__application_id"
FROM
(
select
1 as user_id
union all
select
2 as user_id
) AS "dev_applications_key__dev_users"
LEFT JOIN (
select
1 as purchase_id,
1 as user_id
union all
select
2 as purchase_id,
1 as user_id
) AS "dev_applications_key__dev_purchases" ON "dev_applications_key__dev_users".user_id = "dev_applications_key__dev_purchases".user_id
LEFT JOIN (
select
1 as application_id,
true as is_accepted,
1 as user_id
union all
select
2 as application_id,
false as is_accepted,
2 as user_id
) AS "dev_applications_key__dev_applications" ON "dev_applications_key__dev_users".user_id = "dev_applications_key__dev_applications".user_id
) AS "keys"
LEFT JOIN (
select
1 as application_id,
true as is_accepted,
1 as user_id
union all
select
2 as application_id,
false as is_accepted,
2 as user_id
) AS "dev_applications_key__dev_applications" ON "keys"."dev_applications__application_id" = "dev_applications_key__dev_applications".application_id
GROUP BY
1
) as q_0
LIMIT
5000
```

**Expected behavior**
It is possible to combine measures defined on different cubes that contain filters

**Version:**
Was able to reproduce it on `v0.34.50` and `v0.34.56`

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.