cube-js/cube

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

Open

#6 373 ouverte le 30 mars 2023

Voir sur GitHub
 (2 commentaires) (0 réactions) (0 assignés)Rust (1 965 forks)batch import
help wanted

Métriques du dépôt

Stars
 (19 563 stars)
Métriques de merge PR
 (Merge moyen 5j 16h) (138 PRs mergées en 30 j)

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

Guide contributeur