FILTER_PARAMAS add redundant where clause check
- 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
Assessment
This issue has not been assessed yet.