Mismatch in SQL generation: cube-query fails while view-query succeds for multi-stage measure filtering

[victormorenoi]

Describe the bug
When querying a view, the generated SQL query is different from the one generated when querying the corresponding cube. This difference causes the query to fail for the cube but work correctly for the view.

The issue occurs when selecting a dimension and two measures, one of which is multi-stage, while filtering by the multi-stage measure included in the select.

To Reproduce

1. Create a measure with multi-stage ranking:

- name: rank_price_avg_by_retailer
  sql: "rank() over (partition by {retailer_name} order by {pp_price_avg} desc)"
  type: number   
  multi_stage: true

2. Run a query selecting a dimension and two measures, filtering by rank_price_avg_by_retailer.
3. Compare the generated SQL query for the cube vs. the view.
4. The cube-generated query fails with an SQL compilation error.

Expected behavior
The query generated for the Cube should match the one generated for the View or at least be structured in a way that does not cause an error.

Screenshots
The REST-API is the same, changing mart_daily_plp_ai_view by mart_daily_plp (cube name).

Minimally reproducible Cube Schema

cubes:
  - name: mart_daily_plp
    sql_table: MART_DAILY_PLP
    dimensions:
      - name: brand_name
        sql: "{CUBE}.BRAND_NAME"
        type: string
      - name: price
        sql: "case when {CUBE}.PRICE <= 0 then null else {CUBE}.PRICE end"
        type: number

    measures:
      - name: pp_price_avg
        title: "Average Price"
        description: "Average price of all offers"
        sql: "{price}"
        type: avg       
        filters: 
          - sql: "{price} is not null"
      - name: rank_price_avg_by_retailer
        sql: "rank() over (partition by {retailer_name} order by {pp_price_avg} desc)"
        type: number   
        multi_stage: true

views:

  - name: mart_daily_plp_ai_view
    cubes:
      - join_path: mart_daily_plp
        includes: "*"

Version:
Last Version 1.2.23

Additional context
The Cube-generated SQL query attempts to filter on rank_price_avg_by_retailer directly within cte_5, causing an SQL compilation error:

SQL compilation error: error line 17 at position 563 invalid identifier '"mart_daily_plp".RETAILER_NAME'

This happens because the generated query includes the filtering condition within cte_5 itself:

WHERE (
  rank() over (
    partition by "mart_daily_plp".retailer_name
    order by "mart_daily_plp__pp_price_avg" desc
  ) = '1'
)

However, when querying the view, the filtering is applied in a separate CTE (cte_6), which avoids this issue. The View-generated query follows this structure:

cte_6 AS (
  SELECT
    "mart_daily_plp_ai_view__brand_name" "mart_daily_plp_ai_view__brand_name",
    "mart_daily_plp__rank_price_avg_by_retailer" "mart_daily_plp_ai_view__rank_price_avg_by_retailer"
  FROM (
    SELECT
      q_0."mart_daily_plp_ai_view__brand_name",
      "mart_daily_plp__rank_price_avg_by_retailer" "mart_daily_plp__rank_price_avg_by_retailer"
    FROM
      cte_5 as q_0
    WHERE
      ("mart_daily_plp__rank_price_avg_by_retailer" = '1')
    ORDER BY 2 DESC
  ) AS cte_6_join
)

In the View-generated query, the ranking is first computed in cte_5, and then the filtering is applied in cte_6. This approach prevents the invalid identifier error seen in the cube-generated query.

This suggests that cube may not be correctly handling multi-stage measures in cases where filtering is applied within the same CTE where the measure is defined.