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

[igorlukanin]

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

[github-actions[bot]]

If you are interested in working on this issue, please leave a comment below and we will be happy to assign the issue to you. If this is the first time you are contributing a Pull Request to Cube.js, please check our contribution guidelines. You can also post any questions while contributing in the #contributors channel in the Cube.js Slack.

[Wardormeur]

I have another scenario where a comparison is used resulting in the following error: Arrow error: Compute error: Error: Syntax error: Unexpected keyword IS at The culprite is the same, in a different scenario:

    name: my_date
    sql: CAST({CUBE}.reference_date AS TIMESTAMP) <= CURRENT_TIMESTAMP
    type: boolean

When being cast as a boolean, it append IS TRUE at the end. This result in CURRENT_TIMESTAMP IS TRUE which is invalid ... CASE WHEN (CAST('my_table'.reference_date AS TIMESTAMP) <= CURRENT_TIMESTAMP IS TRUE

To fix it, you have to add explicit parentheses around the whole comparison Such as

    name: my_date
    sql: (CAST({CUBE}.reference_date AS TIMESTAMP) <= CURRENT_TIMESTAMP)
    type: boolean

I would agree that the point of the tool is to make it less painful to write SQL, this requires us to debug the query for what should be a classic scenario Cheers