CTE not working as expected via SQL-API

[ZiggiZagga]

Failed SQL

Config:

cube('orders', {
  sql_table: 'orders',
});

Query via SQL-API

WITH test_filter AS(
SELECT 'order-1' as filter_1
)
SELECT * FROM orders WHERE order_id IN(SELECT filter_1 FROM test_filter)

Error

Error during rewrite: Error during planning: No field named '__subquery-0.filter_1'. Valid fields are '..'.. Please check logs for additional information.

Version: [e.g. 0.4.5]

Additional context Somehow using CTEs with the SQL-API, is not working as expected, for example when used in IN Condition or as LEFT JOIN

[igorlukanin]

I was able to reproduce this with the following data model on v0.36.4 and the query pushdown in the SQL API on:

cubes:
  - name: orders
    sql: SELECT 1 AS id

    dimensions:
      - name: id
        sql: id
        type: number

Query:

WITH test_filter AS (
  SELECT 123 as filter_1
)
SELECT *
FROM orders
WHERE id IN (
  SELECT filter_1 FROM test_filter
);

I believe this is because IN (subquery) is still to be implemented in the SQL API.