cube-js/cube
View on GitHubSQL expressions in dimension definitions are not auto-wrapped in parentheses
Open
#6,373 opened on Mar 30, 2023
help wanted
Repository metrics
- Stars
- (19,563 stars)
- PR merge metrics
- (Avg merge 5d 16h) (138 merged PRs in 30d)
Description
From Slack:
Given the following sample schema:
cube:
- name: items
sql: SELECT * FROM items
dimensions:
- name: id
sql: id
type: number
primaryKey: true
- name: b
sql: b
type: boolean
- name: c
sql: c
type: boolean
- name: d
sql: d
type: boolean
- name: a
sql: "{CUBE.b} AND {CUBE.c} AND NOT {cube.d}"
type: boolean
When I run the following query:
SELECT
id,
a
FROM
items
WHERE
a IS NULL
I expect output that looks something like the following(records where A is null):
id | a
---|---
1 |
2 |
3 |
4 |
5 |
However what I get is closer to the following:
id | a
---|-----
1 | TRUE
2 | TRUE
3 | TRUE
4 | FALSE
5 | TRUE
Why this arises becomes more clear after looking at the generated SQL:
SELECT
id,
b AND c AND NOT d
FROM
items
WHERE
b AND c AND NOT d IS NULL
This comes as a in the SQL is replaced with b AND c AND NOT d which creates b AND c AND NOT d IS NULL which is not what I intended. Having come from Looker what I expected/intended as for something like the following.
SELECT
id,
(b AND c AND NOT d)
FROM
items
WHERE
(b AND c AND NOT d) IS NULL
@paveltiunov thinks that "ideally Cube should handle this."