Bug when trying to use the `multi_stage` and `rolling_window` parameters to reference other measures

[tomredlev-as]

Describe the bug
I'm trying to use the multi_stage feature on a BigQuery configuration to do the following calculations :

• a metric as a number with some filters for each row
• a first sum, grouped by day
• a second sum that will use the previous one to get a cumulative value, using a rolling window of 1 month (tested with 30 days too)
  As of now, we get the following error while trying to do so :

thread '<unnamed>' panicked at /__w/cube/cube/rust/cubesqlplanner/cubesqlplanner/src/planner/base_query.rs:71:22:
called `Result::unwrap()` on an `Err` value: CubeError { message: "Error setting field message", cause: Internal(None), backtrace: Some(<disabled>) }
Internal Server Error: 7a1f3070-7239-4933-b0cb-b920891a6bfa-span-1 (19ms)
--
"{\"measures\":[\"test.rolling_count_month_to_include\"],\"dimensions\":[\"test.time\"]}"
--
{
  "securityContext": {
    ...
  }
} 
Error: internal error in Neon module: called `Result::unwrap()` on an `Err` value: CubeError { message: "Error setting field message", cause: Internal(None), backtrace: Some(<disabled>) }
    at buildSqlAndParams (/cube/node_modules/@cubejs-backend/native/js/index.ts:464:17)
    at BigqueryQuery.findPreAggregationForQueryRust (/cube/node_modules/@cubejs-backend/schema-compiler/src/adapter/BaseQuery.js:942:48)
    at PreAggregations.findPreAggregationForQuery (/cube/node_modules/@cubejs-backend/schema-compiler/src/adapter/PreAggregations.ts:871:50)
    at BigqueryQuery.externalPreAggregationQuery (/cube/node_modules/@cubejs-backend/schema-compiler/src/adapter/BaseQuery.js:787:59)
    at /cube/node_modules/@cubejs-backend/server-core/src/core/CompilerApi.js:217:30
    at DataSchemaCompiler.withQuery (/cube/node_modules/@cubejs-backend/schema-compiler/src/compiler/DataSchemaCompiler.ts:483:14)
    at getSqlFn (/cube/node_modules/@cubejs-backend/server-core/src/core/CompilerApi.js:216:47)
    at QueryCache.cache (/cube/node_modules/@cubejs-backend/schema-compiler/src/adapter/QueryCache.ts:22:28)
    at CompilerApi.getSql (/cube/node_modules/@cubejs-backend/server-core/src/core/CompilerApi.js:237:57)
    at async Promise.all (index 0)

To Reproduce
Steps to reproduce the behavior:

1. Go to the web interface of cube (tested in local and on the cloud version)
2. Click on the cube 'test' and choose the metric 'rolling_count_month_to_include'
3. See error

Expected behavior
We would like to be able to generate a SQL code allowing us to do the upper calculation directly in CubeJS without using the pre-aggregation.

Screenshots

Minimally reproducible Cube Schema

cubes:
  - name: test
    sql: >
      SELECT 1 AS value, TIMESTAMP('2025-01-01') AS time, 0.1 AS probability, true as to_include UNION ALL
      SELECT 2 AS value, TIMESTAMP('2025-01-01') AS time, 0.2 AS probability, false as to_include UNION ALL
      SELECT 3 AS value, TIMESTAMP('2025-01-21') AS time, 0.3 AS probability, true as to_include UNION ALL
      SELECT 4 AS value, TIMESTAMP('2025-01-21') AS time, 0.4 AS probability, true as to_include UNION ALL
      SELECT 5 AS value, TIMESTAMP('2025-02-01') AS time, 0.5 AS probability, true as to_include UNION ALL
      SELECT 6 AS value, TIMESTAMP('2025-02-11') AS time, 0.6 AS probability, false as to_include UNION ALL
      SELECT 7 AS value, TIMESTAMP('2025-02-21') AS time, 0.7 AS probability, true as to_include UNION ALL
      SELECT 8 AS value, TIMESTAMP('2025-03-01') AS time, 0.8 AS probability, false as to_include UNION ALL
      SELECT 9 AS value, TIMESTAMP('2025-03-11') AS time, 0.9 AS probability, true as to_include UNION ALL
      SELECT 10 AS value, TIMESTAMP('2025-03-21') AS time, 0.1 AS probability, false as to_include UNION ALL
      SELECT 11 AS value, TIMESTAMP('2025-03-31') AS time, 0.2 AS probability, true as to_include UNION ALL
      SELECT 12 AS value, TIMESTAMP('2025-04-01') AS time, 0.3 AS probability, false as to_include

    dimensions:
      - name: time
        sql: time
        type: time

    measures:
      - name: expected_probability_to_include
        sql: "value * probability"
        type: number
        filters:
          - sql: "to_include = true"

      - name: expected_probability_to_include_grouped
        sql: "{expected_probability_to_include}"
        type: sum
        multi_stage: true
        group_by:
          - time

      - name: rolling_count_month_to_include
        sql: "{expected_probability_to_include_grouped}"
        type: sum
        multi_stage: true
        rolling_window:
          trailing: 1 month

Versions:

• [1.3.43]
• [1.3.40]
• [1.3.30]

Additional context

• We have activated the environment variable CUBEJS_TESSERACT_SQL_PLANNER
• We are forced to add the multi_stage = true on the rolling_window measure
• we tried simpler versions without the group by too

[igorlukanin]

Hey @tomredlev-as 👋

First of all, I see that expected_probability_to_include is not actually a good measure since it really doesn't do any aggregation. It should be defined as a dimension. You probably want to do something like this:

    dimensions:
      # ...

      - name: expected_probability
        sql: "value * probability"
        type: number

    measures:
      - name: expected_probability_to_include
        sql: "{expected_probability}"
        type: sum # Or something else -- it should do the aggregation anyway, though
        filters:
          - sql: "to_include = true"
        
      # ...

Second, here's how it should work but it doesn't:

cubes:
  - name: test
    sql: >
      SELECT 1 AS value, '2025-01-01'::TIMESTAMP AS time, 0.1 AS probability, true as to_include UNION ALL
      SELECT 2 AS value, '2025-01-01'::TIMESTAMP AS time, 0.2 AS probability, false as to_include UNION ALL
      SELECT 3 AS value, '2025-01-21'::TIMESTAMP AS time, 0.3 AS probability, true as to_include UNION ALL
      SELECT 4 AS value, '2025-01-21'::TIMESTAMP AS time, 0.4 AS probability, true as to_include UNION ALL
      SELECT 5 AS value, '2025-02-01'::TIMESTAMP AS time, 0.5 AS probability, true as to_include UNION ALL
      SELECT 6 AS value, '2025-02-11'::TIMESTAMP AS time, 0.6 AS probability, false as to_include UNION ALL
      SELECT 7 AS value, '2025-02-21'::TIMESTAMP AS time, 0.7 AS probability, true as to_include UNION ALL
      SELECT 8 AS value, '2025-03-01'::TIMESTAMP AS time, 0.8 AS probability, false as to_include UNION ALL
      SELECT 9 AS value, '2025-03-11'::TIMESTAMP AS time, 0.9 AS probability, true as to_include UNION ALL
      SELECT 10 AS value, '2025-03-21'::TIMESTAMP AS time, 0.1 AS probability, false as to_include UNION ALL
      SELECT 11 AS value, '2025-03-31'::TIMESTAMP AS time, 0.2 AS probability, true as to_include UNION ALL
      SELECT 12 AS value, '2025-04-01'::TIMESTAMP AS time, 0.3 AS probability, false as to_include

    dimensions:
      - name: time
        sql: time
        type: time

      - name: expected_probability
        sql: "value * probability"
        type: number

    measures:
      - name: expected_probability_to_include
        sql: "{expected_probability}"
        type: sum
        filters:
          - sql: "to_include = true"

      - name: expected_probability_to_include_grouped
        sql: "{expected_probability_to_include}"
        type: sum
        multi_stage: true
        add_group_by:
          - time.day

      - name: rolling_count_month_to_include
        sql: "{expected_probability_to_include_grouped}"
        type: sum
        multi_stage: true
        rolling_window:
          trailing: 1 month

@waralexrom and I think it's a bug in Tesseract and it needs to be fixed:

However, it does work like this—and I believe solves what you want to do here. The important thing is that you need to have a filter on the time dimension: try removing it and you'll see that you have a rolling window for each day you group by. Want to know for a specific day? Add it to a filter. Want to know for the current day? Add it to the filter anyway.

[tomredlev-as]

Thank you for the feedback, I will look into this solution !

EDIT : It seems to work perfectly on our use-case, thank you so much !