-
Notifications
You must be signed in to change notification settings - Fork 1.9k
Open
@hujinbo
Description
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:
- Enable Rust SQL Planner:
CUBEJS_TESSERACT_SQL_PLANNER=true - Define a subquery dimension that references a measure with aggregation
- Use this subquery dimension only in filters (not in dimensions or measures arrays)
- 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
Metadata
Metadata
Assignees
Labels
No labels