Use operator set or notSet in rollup_join

[Zawar92]

Describe the bug Can't use the set or notSet operator in rollup_join.

To Reproduce Steps to reproduce the behaviour:

Create two cubes, A and B. Both belong to different data sources. Cube B has the joins as it is a base cube. Define rollup_join in it. In the query, add a filter with any dimension that is set or notSet,

You'll get an error: To join across data sources, use rollupJoin with Cube Store. If rollupJoin is defined, this error indicates it doesn't match the query. Please use Rollup Designer to verify its definition. Found data sources: default, delivery

Version: [e.g. 1.3.85]

[igorlukanin]

@Zawar92 It would be great if you can provide a minimal data model and an example query that allow to reproduce this.

[Zawar92]

@igorlukanin,

cubes:
  - name: items
    sql: >
      SELECT
        *
      FROM
        datamart.items
    data_source: delivery

    joins:

    dimensions:

      - name: connection_id
        sql: connection_id
        type: string

      - name: items_id
        sql: items_id
        type: string
        primary_key: true

      - name: systems
        sql: systems
        type: string

      - name: items_date
        sql: CAST(items_date AS timestamp)
        type: time

    pre_aggregations:
      - name: items_rollup
        type: rollup
        dimensions:
          - items_id
          - items_date
          - systems
          - connection_id
        indexes:
          - name: id_index
            columns:
              - items_id
        refresh_key:
          every: 4 hour

cubes:
  - name: orders
    sql: >
      SELECT
        *
      FROM
        datamart.orders
    data_source: default

    joins:

      - name: items
        relationship: many_to_one
        sql: "{CUBE.id} = {items.id}"


    dimensions:
      - name: orders_id
        sql: orders_id
        type: string
        primary_key: true

      - name: orders_date
        sql: orders_date
        type: time

      - name: orders_name
        sql: orders_name
        type: string

    pre_aggregations:
      - name: orders_rollup
        type: rollup
        dimensions:
          - orders_id
          - orders_date
          - orders_name
        indexes:
          - name: id_index
            columns:
              - orders_id
              - orders_date
        refresh_key:
          every: 4 hour

      - name: orders_missing_items_rollup
        type: rollup_join
        dimensions:
          - CUBE.orders_id
          - CUBE.orders_date
          - CUBE.orders_name
          - items.items_date
        rollups:
          - items.items_rollup
          - CUBE.orders_rollup
        indexes:
          - name: join_index
            columns:
              - CUBE.orders_id
              - CUBE.orders_date

Query:

As soon as the filter is added, I receive the error. Without set/notSet filter, it works fine.

{
  "dimensions": [
    "orders.orders_id",
    "orders.orders_date",
    "orders.orders_name",
    "items.items_date"
  ],
  "filters": [
    {
      "member": "items.items_date",
      "operator": "notSet"
    }
  ]
}

[igorlukanin]

I was able to reproduce this with the following data model on v1.5.0:

cubes:
  - name: items
    sql: >
      SELECT 1 AS connection_id, 11 AS items_id, 'A' AS systems, '2025-01-01'::TIMESTAMP AS items_date
    data_source: delivery

    joins:

    dimensions:

      - name: connection_id
        sql: connection_id
        type: string

      - name: items_id
        sql: items_id
        type: string
        primary_key: true

      - name: systems
        sql: systems
        type: string

      - name: items_date
        sql: CAST(items_date AS timestamp)
        type: time

    pre_aggregations:
      - name: items_rollup
        type: rollup
        dimensions:
          - items_id
          - items_date
          - systems
          - connection_id
        indexes:
          - name: id_index
            columns:
              - items_id

  - name: orders
    sql: >
      SELECT 111 AS orders_id, 'B' AS orders_name, '2026-01-01'::TIMESTAMP AS orders_date
    data_source: default

    joins:

      - name: items
        relationship: many_to_one
        sql: "{CUBE.orders_id} = {items.items_id}"


    dimensions:
      - name: orders_id
        sql: orders_id
        type: string
        primary_key: true

      - name: orders_date
        sql: orders_date
        type: time

      - name: orders_name
        sql: orders_name
        type: string

    pre_aggregations:
      - name: orders_rollup
        type: rollup
        dimensions:
          - orders_id
          - orders_date
          - orders_name
        indexes:
          - name: id_index
            columns:
              - orders_id
              - orders_date

      - name: orders_missing_items_rollup
        type: rollup_join
        dimensions:
          - CUBE.orders_id
          - CUBE.orders_date
          - CUBE.orders_name
          - items.items_date
        rollups:
          - items.items_rollup
          - CUBE.orders_rollup
        indexes:
          - name: join_index
            columns:
              - CUBE.orders_id
              - CUBE.orders_date

Maybe this code fragment is the root cause:

/cube/packages/cubejs-schema-compiler/src/adapter/PreAggregations.ts:573-586

  public static collectFilterDimensionsWithSingleValueEqual(filters, map) {
    for (const f of filters) {
      if (f.operator === 'equals') {
        map.set(f.expressionPath(), Math.min(map.get(f.expressionPath()) || 2, f.values.length));
      } else if (f.operator === 'and') {
        const res = this.collectFilterDimensionsWithSingleValueEqual(f.values, map);
        if (res == null) return null;
      } else {
        return null;  // 🔴 Your "set" operator hits this line
      }
    }
    return map;
  }

[Zawar92]

@igorlukanin,

It worked with the version v1.3.13. Any suggestions from your side would be helpful.

[Zawar92]

@igorlukanin Can you give me any suggestions regarding this issue? How can I overcome this?