cube-js/cube

SQL expressions in dimension definitions are not auto-wrapped in parentheses

Open

#6,373 opened on Mar 30, 2023

View on GitHub
 (2 comments) (0 reactions) (0 assignees)Rust (1,965 forks)batch import
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."

Contributor guide