Select `cte.field_name` not working with UNION CTE

[duong-ly]

Failed SQL

WITH test AS (
  SELECT
    name
  FROM
    cities
  UNION
  SELECT
    name
  FROM
    countries
)
SELECT
  test.name
from
  test;

Logical Plan Initial planning error: Error during planning: No field named 'test.name'. Valid fields are 'name'.

Version: 0.34.52

Additional context Hi team, I was trying to query this to Cube SQL API and got this error. Looks like there is a bug/unimplemented use case within the planning stage. Some more context:

• I have 2 cube instances, 1 is cities and the other is countries, both have name attributes.
• I was trying to union these 2 tables and then select name property from the CTE test and get this error
• Yes, I have tried SELECT name from test and it works, but test.name shouldn't fail since this is valid in PostgreSQL

Please let me know if there is anything wrong, thanks a lot team.

[igorlukanin]

Hi @duong-ly 👋 Thanks for reporting this!

Could you please use set the CUBESQL_SQL_PUSH_DOWN env var to true, upgrade to the latest version, and try again?

[duong-ly]

hi @igorlukanin,

I have set CUBESQL_SQL_PUSH_DOWN to true but it's still getting error

This is my current version:

[igorlukanin]

I was able to reproduce this! I can confirm that a query like this (no CTE) would work:

with test as (select id from cube1) select test.id from test;

...while a query like this (with CTE) would not work:

with test as (select id from cube1 union select id from cube2) select test.id from test;