cube-js/cube

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

Aberta

#6.373 aberto em 30 de mar. de 2023

 (3 comentários) (0 reação) (0 responsável)Rust (1.965 forks)batch import
help wanted

Métricas do repositório

Stars
 (19.563 estrelas)
Métricas de merge de PR
 (Métricas PR pendentes)

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

Guia do colaborador