Tesseract planner: measure-side fact copy of a multiplied-measure join-back renders FILTER_PARAMS as 1 = 1 (unbounded scan)

[icoolguy1995]

Describe the bug

When a calculated measure (type: number) is queried together with a dimension from a one_to_many-joined cube, the Tesseract SQL planner generates the expected full-key plan: a keys subquery joined back to a second copy of the fact source by primary key.

The keys-side copy of the fact source is rendered with the query's filters. The measure-side copy is rendered with no filter context at all: every FILTER_PARAMS template inside the cube's sql: falls back to ALWAYS_TRUE and is emitted as 1 = 1.

Query results are still correct (the keys side constrains the output), but the database has to build the join against the entire unfiltered fact table — every tenant, all time. The legacy planner (CUBEJS_TESSERACT_SQL_PLANNER=false) renders the same predicates into both copies for the identical schema and query.

To Reproduce

model/cubes/orders.yml:

cubes:
  - name: orders
    # Multi-tenant fact table. FILTER_PARAMS pushes the query's tenant and
    # date predicates into the source scan.
    sql: >-
      SELECT * FROM orders
      WHERE {FILTER_PARAMS.orders.tenant_id.filter('tenant_id')}
        AND {FILTER_PARAMS.orders.created_at.filter('created_at')}

    joins:
      - name: order_tags
        sql: "{CUBE}.id = {order_tags}.order_id"
        relationship: one_to_many

    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
      - name: tenant_id
        sql: tenant_id
        type: string
      - name: created_at
        sql: created_at
        type: time

    measures:
      - name: count
        type: count
      - name: buyers
        sql: user_id
        type: count_distinct
      - name: orders_per_buyer
        sql: "{count} / nullif({buyers}, 0)"
        type: number

  - name: order_tags
    sql: SELECT * FROM order_tags

    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true
      - name: tag
        sql: tag
        type: string

Query:

{
  "measures": ["orders.orders_per_buyer"],
  "dimensions": ["order_tags.tag"],
  "timeDimensions": [
    { "dimension": "orders.created_at", "dateRange": ["2026-07-29", "2026-08-27"] }
  ],
  "filters": [
    { "member": "orders.tenant_id", "operator": "equals", "values": ["t1"] }
  ]
}

Generated SQL with the Tesseract planner (ClickHouse dialect, abridged only for whitespace):

WITH cte_0 AS (
  SELECT `keys`.`order_tags__tag`,
         count(`orders_key_orders`.id) / nullif(COUNT(DISTINCT `orders_key_orders`.user_id), 0)
  FROM (
    SELECT DISTINCT `orders_key_order_tags`.tag `order_tags__tag`, `orders_key_orders`.id `orders__id`
    FROM (SELECT * FROM orders
          WHERE (tenant_id = ?)
            AND (created_at >= parseDateTimeBestEffort(?) AND created_at <= parseDateTimeBestEffort(?))
         ) AS `orders_key_orders`                                   -- keys side: bounded
    LEFT JOIN order_tags AS `orders_key_order_tags` ON ...
    WHERE (`orders_key_orders`.created_at >= ... ) AND (`orders_key_orders`.tenant_id = ?)
  ) AS `keys`
  LEFT JOIN (SELECT * FROM orders
             WHERE 1 = 1
               AND 1 = 1                                            -- measure side: both FILTER_PARAMS gone
            ) AS `orders_key_orders`
    ON `keys`.`orders__id` = `orders_key_orders`.id
  GROUP BY 1
)
SELECT ... FROM cte_0

Same schema and query with CUBEJS_TESSERACT_SQL_PLANNER=false — the measure-side copy keeps the predicates:

LEFT JOIN (SELECT * FROM orders
           WHERE (tenant_id = ?)
             AND (created_at >= parseDateTimeBestEffort(?) AND created_at <= parseDateTimeBestEffort(?))
          ) AS `orders_key__orders`
  ON `keys`.`orders__id` = `orders_key__orders`.id

Expected behavior

The measure-side copy of the fact source should carry the same pushed-down predicates as the keys-side copy. This is result-identical: the keys subquery already applies those predicates over the same fact columns, and the join-back is by primary key — every joinable row already satisfies them. Filtering the measure side can only shrink the hash build, never change the result.

Impact

On a large fact table the unfiltered hash build exceeds the database's memory
limit and the query fails.

Where it appears to happen

In rust/cube/cubesqlplanner/cubesqlplanner/src/planner/planners/multiplied_measures_query_planner.rs, aggregate_subquery_plan builds the measure-side source as a bare pk_cube.into() — no filter context — while keys_subquery is planned with the query's filters. The physical builder then renders the cube's SQL with an empty used-filter set, and FILTER_PARAMS evaluation falls back to ALWAYS_TRUE. Same fallback as #10606, reached via a different path (there segments break subtree extraction; here the source is planned filterless by construction).

A possible fix: when planning the bare-cube source of the aggregate subquery, thread through the subset of the query's filters whose members belong to the key cube (member filters and time-dimension ranges). Cumulative/rolling measures are planned outside this branch, so window-extension semantics should be unaffected. Happy to contribute a patch with regression tests if the approach is acceptable.

Version

• Repro compiled with @cubejs-backend/schema-compiler 1.7.24 (Tesseract planning via @cubejs-backend/native 1.6.65 — both versions exhibit it), ClickHouse dialect; the planning defect is dialect-independent.
• Workaround: CUBEJS_TESSERACT_SQL_PLANNER=false produces bounded copies (aware the legacy planner is scheduled for removal, hence this report).