cube-js / cube-js/cube

FILTER_PARAMAS add redundant where clause check

Open
#9,911 0 comments 0 reactions 1 assignee Claimed by @igorlukanin View on GitHub
question
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 10h
Merged PRs (30d)
203

Description

When we use FILTER_PARAS in sub-query
[](https://cube.dev/docs/product/data-modeling/reference/context-variables#filter_params)

By default cube adds where clause in subQuery and in outer query as well, that is causing many issues.

e.g.

```
SELECT
`dbcontact`.employment_id AS `dbcontact__employment_id`
FROM contact AS `dbcontact`
LEFT JOIN (
SELECT DISTINCT
contact_id
FROM listMember
WHERE
(list_id = '4118')
) AS `contact_list`
ON `dbcontact`.employment_id = `contact_list`.contact_id
WHERE (`contact_list`.list_id = '4118')
GROUP BY 1
ORDER BY 1 DESC
LIMIT 10;
```

cubes:

```
cubes:
- name: contactList
sql: >
SELECT distinct contact_id from listMember
WHERE (0=0) AND ${FILTER_PARAMS.contactList.listId.filter('list_id')}
data_source: default

dimensions:
- name: contactId
sql: contact_id
type: number
primary_key: true

- name: listId
sql: list_id
type: number
```

```

cubes:
- name: dbcontact
sql_table: contact
data_source: default

joins:
- name: contactList
sql: ${CUBE}.employment_id = ${contactList.contactId}
relationship: one_to_one

dimensions:
- name: employmentId
title: Contact ID
sql: ${CUBE}.employment_id
type: number
primaryKey: true
```

This is client request

```
query: {
"dimensions": [
"dbcontact.employmentId"
],
"filters": [
{
"member": "contactList.listId",
"operator": "equals",
"values": [
4118,12
]
}
],
"order": {
"dbcontact.employmentId": "desc"
},
"segments": null,
"limit": 10
}

```

This query will not work because extra where clause has been added in outer query and we are not exposing that column in subQuery, because we need distinct contact_id, if we expose list_id then there will be no distinct records and we will get duplicate records.

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.