Subquery dimension in filters generates invalid SQL with aggregate function in WHERE clause

[hujinbo]

---

Describe the bug

When using the Rust SQL Planner (CUBEJS_TESSERACT_SQL_PLANNER=true), a subquery dimension that appears only in filters (not in dimensions or measures) causes the query to generate invalid SQL. The aggregate function from the subquery dimension's SQL is directly placed in the WHERE clause, resulting in a syntax error.

---

To Reproduce

Steps:

1. Enable Rust SQL Planner: CUBEJS_TESSERACT_SQL_PLANNER=true
2. Define a subquery dimension that references a measure with aggregation
3. Use this subquery dimension only in filters (not in dimensions or measures arrays)
4. Execute the query and observe SQL syntax error

---

Expected behavior

The query should generate a proper subquery JOIN for the subquery dimension and reference the aggregated result in the WHERE clause.

Actual behavior

The generated SQL incorrectly places the aggregate function directly in the WHERE clause:

  WHERE ...
      AND (sum(`dim_order_for_crowd`.order_divide_fee) > '0')

This causes a SQL syntax error because aggregate functions cannot be used in WHERE clauses without a subquery/HAVING.

---

Minimally reproducible Cube Schema

---
cubes:
- name: dwd_order_pay_detail
  sql_table: ods_ecrp_kd_order
  description: |-
    订单的付款明细数据
  joins:
  - name: dim_date
    relationship: many_to_one
    sql: "{dim_date.date_key} = DATE({CUBE}.order_paytime)"
  - name: dim_kd_view_customer
    relationship: many_to_one
    sql: "{CUBE}.view_id = {dim_kd_view_customer.view_id} AND {CUBE}.sys_customer_id\
      \ = {dim_kd_view_customer.sys_customer_id}"
  dimensions:
  - name: sys_trade_id
    type: number
    title: 系统订单ID
    sql: "{CUBE}.sys_trade_id"
    primary_key: true
    sub_query: false
    description: 系统订单ID
    propagate_filters_to_sub_query: false
  - name: out_order_id
    type: number
    title: 子订单ID
    sql: "{CUBE}.out_order_id"
    primary_key: true
    sub_query: false
    description: 子订单ID
    propagate_filters_to_sub_query: false
  measures:
  - name: order_pay_amount
    type: sum
    sql: "{CUBE}.order_divide_fee"
    title: 付款金额
    description: 付款金额指标
    filters:
    - sql: "{CUBE}.order_paytime IS NOT NULL"
  - name: order_pay_customer_num
    type: count_distinct
    sql: "{CUBE}.sys_customer_id"
    title: 付款人数
    description: 付款人数指标
    filters:
    - sql: "{CUBE}.order_paytime IS NOT NULL"

- name: dim_date
  sql_table: dim_date
  description: 时间维度表
  joins:
  - name: dwd_order_pay_detail
    relationship: one_to_many
    sql: "{CUBE.date_key} = DATE({dwd_order_pay_detail}.order_paytime)"
  dimensions:
  - name: date_key
    type: time
    title: 时间维度
    sql: "{CUBE}.date_key"
    primary_key: true
    sub_query: false
    description: 作为所有指标全局的统计周期限定
    propagate_filters_to_sub_query: false
  measures: []

- name: dim_kd_view_customer
  sql_table: ods_ecrp_kd_view_customer
  description: 体系客户维度表
  joins:
  - name: dim_order_for_crowd
    relationship: one_to_many
    sql: "{CUBE.view_id} = {dim_order_for_crowd}.view_id AND {CUBE.sys_customer_id}\
      \ = {dim_order_for_crowd}.sys_customer_id"
  dimensions:
  - name: view_id
    type: number
    title: 体系ID
    sql: "{CUBE}.view_id"
    primary_key: true
    sub_query: false
    description: 体系ID
    propagate_filters_to_sub_query: false
  - name: sys_customer_id
    type: number
    title: 用户ID
    sql: "{CUBE}.sys_customer_id"
    primary_key: true
    sub_query: false
    description: 用户ID
    propagate_filters_to_sub_query: false
  - name: dynamic_filtered_consumption
    type: number
    title: 客户在指定条件下的消费总额
    sql: "{dim_order_for_crowd.aa}"
    primary_key: false
    sub_query: true
    description: |-
      **重要：此值完全由查询中的filters动态决定！**
      它表示客户在 filters 中对 dim_order_for_crowd 表所设定的条件范围内，其所有订单金额（order_divide_fee）的总和。
      **警告:** 单独使用金额条件而不设时间范围，会计算客户全历史消费。
    propagate_filters_to_sub_query: false
  measures: []

- name: dim_order_for_crowd
  sql_table: ods_ecrp_kd_order
  description: 体系客户维度表的订单级别的细粒度维度表，存放了客户的订单明细数据。
  joins: []
  dimensions:
  - name: sys_trade_id
    type: number
    title: 系统主订单号
    sql: "{CUBE}.sys_trade_id"
    primary_key: true
    sub_query: false
    description: 系统主订单号
    propagate_filters_to_sub_query: false
  - name: out_order_id
    type: string
    title: 子订单号
    sql: "{CUBE}.out_order_id"
    primary_key: true
    sub_query: false
    description: 子订单号
    propagate_filters_to_sub_query: false
  measures:
  - name: aa
    type: sum
    sql: "{CUBE}.order_divide_fee"
    title: 无

Query (MQL):

{
  "query": {
    "measures": [
      "dwd_order_pay_detail.order_pay_amount"
    ],
    "timeDimensions": [
      {
        "dimension": "dim_date.date_key",
        "dateRange": [
          "2025-01-01 00:00:00",
          "2025-01-31 23:59:59"
        ]
      }
    ],
    "filters": [
      {
        "member": "dim_kd_view_customer.dynamic_filtered_consumption",
        "operator": "gt",
        "values": [
          0
        ]
      }
    ]
  }
}

Generated SQL (Invalid):

SELECT sum(CASE
		WHEN (`dwd_order_pay_detail`.order_paytime IS NOT NULL) THEN `dwd_order_pay_detail`.order_divide_fee
	END) AS `dwd_order_pay_detail__order_pay_amount`
FROM ods_ecrp_kd_order `dwd_order_pay_detail`
	LEFT JOIN dim_date `dim_date` ON `dim_date`.date_key = DATE(`dwd_order_pay_detail`.order_paytime)
	LEFT JOIN ods_ecrp_kd_view_customer `dim_kd_view_customer`
	ON `dwd_order_pay_detail`.view_id = `dim_kd_view_customer`.view_id
		AND `dwd_order_pay_detail`.sys_customer_id = `dim_kd_view_customer`.sys_customer_id
WHERE `dim_date`.date_key >= TIMESTAMP('2025-01-01T00:00:00.000')
	AND `dim_date`.date_key <= TIMESTAMP('2025-01-31T23:59:59.000')
	AND (sum(`dim_order_for_crowd`.order_divide_fee) > '0')   -- ❌ Invalid SQL
LIMIT 20

Error: SQL syntax error - aggregate functions are not allowed in WHERE clause.

---

Version

Cube.js version: 1.3.73(with CUBEJS_TESSERACT_SQL_PLANNER=true)

Database: MySQL