bug(access-policies): row_level filters on a cube used as the optional side of a join collapse the LEFT JOIN, dropping unmatched rows

[mjvankampen]

Note: below is written by AI based on our findings and checked by me.

Describe the bug

Access policy row_level.filters are emitted into the outer WHERE of the generated query, i.e. after joins. When the filtered cube is the joined (optional) side of a join, the predicate is evaluated against the padded columns of unmatched rows, which can never satisfy it. The LEFT JOIN silently becomes an INNER JOIN, and rows belonging to the main cube disappear.

The joins reference documents that "All joins are generated as LEFT JOIN" and that "The semantics of INNER JOIN can be achieved with additional filtering", so the mechanism itself is documented — but a row_level policy triggers it implicitly, with no way to opt out. Adding a tenancy policy to cube B changes the result set of queries against cube A, with no error and no warning.

To Reproduce

cubes:
  - name: orders
    sql_table: public.orders
    joins:
      - name: shipments
        sql: "{CUBE.id} = {shipments.order_id}"
        relationship: one_to_one
    dimensions:
      - name: id
        sql: id
        type: string
        primary_key: true
      - name: tenant_id
        sql: tenant_id
        type: string
    measures:
      - name: count
        type: count

  - name: shipments
    sql_table: public.shipments
    access_policy:
      - group: "*"
        member_level:
          includes: "*"
        row_level:
          filters:
            - member: tenant_id
              operator: equals
              values: ["{ securityContext.tenant_id }"]
    dimensions:
      - name: order_id
        sql: order_id
        type: string
        primary_key: true
      - name: tenant_id
        sql: tenant_id
        type: string
      - name: carrier
        sql: carrier
        type: string

Seed two orders with tenant_id = 't1', one with a matching shipment and one without. Then query with security context {"tenant_id": "t1"}:

{ "dimensions": ["orders.id", "shipments.carrier"] }

Expected behavior

Two rows, with a NULL carrier for the order that has no shipment. The policy should constrain which shipments are visible, not which orders are returned.

Actual behavior

One row. WHERE shipments.tenant_id = 't1' is applied after the LEFT JOIN, so the unmatched order is discarded. Because the filtering happens before GROUP BY, an aggregating query loses the group entirely rather than returning it with NULL measures — so the record is absent from the response rather than present-but-empty.

Suggested fix

Push a joined cube's row_level filters into the join's ON clause, or into a subquery for that cube, which preserves both tenant isolation and LEFT JOIN semantics. At minimum, document the interaction — the access policies page does not currently mention joins, and the behaviour is invisible until someone notices missing rows.

Impact

On a multi-tenant ClickHouse deployment this silently hid ~25% of conversations (>80% for the worst-affected tenant) from an end-user-facing list. The workaround we settled on is filtering inside the cube's own sql via COMPILE_CONTEXT, which the multitenancy docs scope to the "users need access to different databases" case rather than same-database row-level security.

Version:

Cube Cloud, ClickHouse data source.