cube-js/cube

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

Aperta

#6373 aperta il 30 mar 2023

 (3 commenti) (0 reazioni) (0 assegnatari)Rust (1965 fork)batch import
help wanted

Metriche repository

Star
 (19.563 stelle)
Metriche merge PR
 (Metriche PR in attesa)

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