SQL-API: Column concatenation failing

[ZiggiZagga]

Failed SQL
select 'Some Prefix'||some_column from some_cube

Error
Error during rewrite: Can't detect Cube query and it may be not supported yet.

Logical Plan
Error during planning

Version:
v0.35.54

Additional context
This query works:
select some_column||'Some Suffix' from some_cube

[igorlukanin]

Hi @ZiggiZagga 👋

I see that you have CUBESQL_SQL_PUSH_DOWN set to false (or not set at all). It means that Cube can only use post-processing (and push-down queries are not enabled). You can read more about this terminology on the docs: https://cube.dev/docs/product/apis-integrations/sql-api#fundamentals, https://cube.dev/docs/product/apis-integrations/sql-api/query-format#post-processing-and-pushdown

To make this query work with post-processing, you can rewrite is as follows:

Alternatively, you can set CUBESQL_SQL_PUSH_DOWN to true and run a query like this:

There are slight differences in query semantics between these two variants, you can read more in the blog: https://cube.dev/blog/query-push-down-in-cubes-semantic-layer

[ZiggiZagga]

Hi @igorlukanin,

Thank you! I set CUBESQL_SQL_PUSH_DOWN to true, and the query now works. Unfortunately, I can't use the Cube SQL API for now because I am having problems with CTEs in LEFT JOIN or IN conditions. Issues have already been filed (#8422 and #8413). I will keep an eye on the progress of these issues and hopefully consider using the SQL API again in the near future.

[ZiggiZagga]

Works when CUBESQL_SQL_PUSH_DOWN is set to true