Error: missing FROM-clause entry for table
- 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"
}
}
```

`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"
}
}
```

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"
}
}
```

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
Assessment
This issue has not been assessed yet.