Skip to content

Error when filtering a view by a multi_stage measureΒ #9314

@victormorenoi

Description

@victormorenoi

Describe the bug
When executing a query on a view with multi_stage=true measure in a filter, I encounter an error, although the same query works correctly when run on the corresponding cube.
Specifically, a measure defined as multi_stage=true causes the error when queried from the view as a filter. However, if I call the measure multi_stage=true as a measure it work fine.
Both measures work correctly when the data is queried directly from the cube instead of the view.

To Reproduce

When executing a query on the mart_media view with multi_stage=true measures, I encounter an error, although the same query works correctly when run on the mart_media_base cube.
Specifically, the measure brand_name_filter, which is defined as multi_stage=true, causes the error in the view. However, the count_total measure, which is also defined as multi_stage=true, works fine.
Both measures work correctly if the data is queried from the cube instead of the view.

Steps to reproduce the behavior:

  • Define a cube test_cube with the provided schema.
  • Create a view test_cube_view that joins the test_cube cube.
  • Run a query involving the brand_name_filter and count_total measures in the view.
  • Observe that the count_total measure works fine, but the brand_name_filter measure fails with an error.

Expected behavior
The brand_name_filter measure (with multi_stage=true) should work correctly when queried from the view, similar to how the count_total measure works.

Screenshots

Image

Minimally reproducible Cube Schema
CUBE

cubes:
  - name: test_cube
    sql_table: data_table

    dimensions:

      - name: unique_key
        sql: "{CUBE}.\"UNIQUE_KEY\""
        type: string
        primary_key: true
     
      - name: brand_name
        sql: "{CUBE}.\"BRAND_NAME\""
        type: string

      - name: retailer_name
        sql: "{CUBE}.\"RETAILER_NAME\""
        type: string
      
      - name: datagroup_id
        sql: "{CUBE}.\"DATAGROUP_ID\""
        type: string
      
      - name: date
        sql: "{CUBE}.\"DATE\""
        type: time
      
      - name: multibrand_weight
        sql: "{CUBE}.\"MULTIBRAND_WEIGHT\""
        type: number

      - name: category_name
        sql: "{CUBE}.\"NAME\""
        type: string

    measures:

      - name: brand_name_filter
        type: string
        multi_stage: true
        sql: brand_name

      - name: count
        type: sum
        sql: multibrand_weight

      - name: count_total
        type: sum
        multi_stage: true
        sql: "{count}"
        group_by:
          - retailer_name
          - category_name

      - name: share
        sql: "{count} * 100 / {count_total}"
        type: number
        format: percent

    access_policy:

      - role: client_user
        member_level:
          includes: "*"
        row_level:
          filters:
            - member: datagroup_id
              operator: equals
              values: [ "{ securityContext.datagroup_id }" ]

VIEW

views:
  
  - name: test_cube_view

    cubes:
      - join_path: test_cube
        includes: "*"

Version:
latest (1.2.17)

Additional context
The issue only arises when querying the measures from the view test_cube_view, specifically with the brand_name_filter measure (defined as multi_stage=true). The same query works as expected when queried from the cube test_cube.

I think the issue is do to multiStageQuery:false

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions