Regression: CASE WHEN is not working in SELECT: WHEN expression did not return a BooleanArray if non boolean values are used

[wasd171]

Describe the bug Since v0.34.51 it is not possible to use CASE WHEN in SELECT when consuming Cube's SQL API

To Reproduce Steps to reproduce the behavior:

1. Create a minimal cube

cubes:
  - name: "survey_results"
    sql: >
      select 1 as id, true as would_recommend
      union all
      select 2 as id, false as would_recommend

    dimensions:
      - name: survey_result_id
        sql: id
        type: number
        primary_key: true

      - name: would_recommend
        sql: would_recommend
        type: string

2. Execute an SQL query:

SELECT
  survey_result_id,
  would_recommend
FROM
  survey_results

3. So far all good. Modify this query slightly and run again:

SELECT
  survey_result_id,
  (case when would_recommend then 'Yes' else 'No' end) as 'Would recommend'
FROM
  survey_results

4. Experience the following error:

db query error: pq: Error during rewrite: Unexpected panic. Reason: Unexpected expression node: AggregateAggrExpr([]). Please check logs for additional information. QUERY: SELECT survey_result_id, (CASE WHEN would_recommend THEN 'Yes' ELSE 'No' END) AS 'Would recommend' FROM survey_results

And the following logs from Cube:

thread 'tokio-runtime-worker' panicked at /__w/cube/cube/rust/cubesql/cubesql/src/compile/rewrite/converter.rs:1009:14:
Unexpected expression node: AggregateAggrExpr([])
2024-02-22 14:59:55,851 ERROR [cubesql::compile] It may be this query is not supported yet. Please post an issue on GitHub https://github.com/cube-js/cube.js/issues/new?template=sql_api_query_issue.md or ask about it in Slack https://slack.cube.dev.
SQL API Error: 91616914-8553-4b4f-b1fc-7bf532557911-span-1 (34ms)
--
{
  "sql": "SELECT\n  survey_result_id,\n  (case when would_recommend then 'Yes' else 'No' end) as 'Would recommend'\nFROM\n  survey_results\n"
}
--
{
  "securityContext": {},
  "appName": "NULL",
  "protocol": "postgres",
  "apiType": "sql"
}
Error during rewrite: Unexpected panic. Reason: Unexpected expression node: AggregateAggrExpr([]). Please check logs for additional information.
Cube SQL Error: undefined
--
"SELECT survey_result_id, (CASE WHEN would_recommend THEN 'Yes' ELSE 'No' END) AS 'Would recommend' FROM survey_results"
--
{
  "apiType": "sql",
  "appName": "NULL",
  "protocol": "postgres",
  "sanitizedQuery": "SELECT survey_result_id, (CASE WHEN would_recommend THEN 'Yes' ELSE 'No' END) AS 'Would recommend' FROM survey_results"
}
Error during rewrite: Unexpected panic. Reason: Unexpected expression node: AggregateAggrExpr([]). Please check logs for additional information.

Expected behavior The query does not fail and CASE WHEN executes successfully (it worked like that up to version v0.34.50)

Version: Loosely all versions greater than v0.34.50, I have not tested every release, but quite a few

Additional context Might be related to this issue: https://github.com/cube-js/cube/issues/7476 and this commit: https://github.com/cube-js/cube/commit/40726ba3ae27a53a4e6531886513b545a27b1858

[igorlukanin]

Hi @wasd171 👋

Could you please check what happens if you use the latest version and set CUBESQL_SQL_PUSH_DOWN=true? It might help resolve this one.

[wasd171]

Hi @igorlukanin

I've tried your suggestion and now I am getting a different error:

thread 'tokio-runtime-worker' panicked at /github/home/.cargo/git/checkouts/arrow-datafusion-9d92f730d741a4c6/531b925/datafusion/physical-expr/src/expressions/case.rs:390:18:
WHEN expression did not return a BooleanArray
SQL API Error: b6700513-a19d-4740-8d83-35d48fbad301-span-1 (25ms)
--
{
  "sql": "SELECT\n  survey_result_id,\n  (case when would_recommend then 'Yes' else 'No' end) as 'Would recommend'\nFROM\n  survey_results"
}
--
{
  "securityContext": {},
  "appName": "NULL",
  "protocol": "postgres",
  "apiType": "sql"
}
Error during processing PostgreSQL message: CubeError: Unexpected panic. Reason: WHEN expression did not return a BooleanArray
Cube SQL Error: undefined
{
  "appName": "NULL",
  "protocol": "postgres",
  "apiType": "sql"
}
Error during processing PostgreSQL message: CubeError: Unexpected panic. Reason: WHEN expression did not return a BooleanArray

[paveltiunov]

@wasd171 It means there are non-boolean values within would_recommend however it shouldn't fail though.

[wasd171]

@paveltiunov I think that my schema contains only boolean values for the would_recommend

cubes:
  - name: "survey_results"
    sql: >
      select 1 as id, true as would_recommend
      union all
      select 2 as id, false as would_recommend

    dimensions:
      - name: survey_result_id
        sql: id
        type: number
        primary_key: true

      - name: would_recommend
        sql: would_recommend
        type: string

[wasd171]

Hi @paveltiunov

Could you please update me on this one? I would be happy to provide more information for debugging

[wasd171]

@igorlukanin using v0.35.25 with CUBESQL_SQL_PUSH_DOWN=true has resolved it, however it still breaks without the env variable