cube-js/cube

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

Open

#6373 aperta il 30 mar 2023

Vedi su GitHub
 (2 commenti) (0 reazioni) (0 assegnatari)Rust (1965 fork)batch import
help wanted

Metriche repository

Star
 (19.563 star)
Metriche merge PR
 (Merge medio 5g 16h) (138 PR mergiate in 30 g)

Descrizione

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."

Guida contributor