Incorrect SQL generated for a fixed-dimension measure if it's only present in filters

[michofwood]

Describe the bug
When filtering on a multi_stage measure present in the projection list, works as expected.
When filtering on a multi_stage measure NOT-preset in the projection list, generated query SQL is wrong and fails to execute.

To Reproduce
Steps to reproduce the behavior:

1. Using the https://cube.dev/docs/product/data-modeling/concepts/multi-stage-calculations#fixed-dimension percent_of_total cube

Run following SQL:

select 
	country
	,product
	,measure(revenue) revenue
	,measure(country_revenue_percentage) country_revenue_percentage
	from percent_of_total
where country_revenue_percentage > 0.44
group by 1,2
order by 1,2

Returns 4 filtered rows

Run following SQL:

select 
	country
	,product
	,measure(revenue) revenue
	from percent_of_total
where country_revenue_percentage > 0.44
group by 1,2
order by 1,2

Errors with:

SQL Error [XX000]: ERROR: Arrow error: Compute error: Error: ER_PARSE_ERROR: Getting analyzing error from line 6, column 7 to line 6, column 95. Detail message: WHERE clause cannot contain aggregations.

First instance generated SQL:

select
	`percent_of_total`.country `percent_of_total__country`,
	`percent_of_total`.product `percent_of_total__product`,
	sum(`percent_of_total`.revenue) `percent_of_total__revenue`
from
	(
	select
		1 as id,
		1000 as revenue,
		'A' as product,
		'USA' as country
union all
	select
		2 as id,
		2000 as revenue,
		'B' as product,
		'USA' as country
union all
	select
		3 as id,
		3000 as revenue,
		'A' as product,
		'Austria' as country
union all
	select
		4 as id,
		4000 as revenue,
		'B' as product,
		'Austria' as country
union all
	select
		5 as id,
		5000 as revenue,
		'A' as product,
		'Netherlands' as country
union all
	select
		6 as id,
		6000 as revenue,
		'B' as product,
		'Netherlands' as country ) as `percent_of_total`
group by
	1,
	2
order by
	3 desc),
cte_1 as (
select
	`q_0`.`percent_of_total__country` `percent_of_total__country`,
	`q_0`.`percent_of_total__product` `percent_of_total__product`,
	sum(sum(`q_0`.`percent_of_total__revenue`)) over (partition by q_0.percent_of_total__country ) `percent_of_total__country_revenue`
from
	cte_0 as `q_0`
group by
	1,
	2
order by
	3 desc),
cte_2 as (
select
	coalesce(`q_0`.`percent_of_total__country`, `q_1`.`percent_of_total__country`) `percent_of_total__country`,
	coalesce(`q_0`.`percent_of_total__product`, `q_1`.`percent_of_total__product`) `percent_of_total__product`,
	`q_0`.`percent_of_total__revenue` / nullif(`q_1`.`percent_of_total__country_revenue`, 0) `percent_of_total__country_revenue_percentage`
from
	cte_0 as `q_0`
inner join cte_1 as `q_1` on
	((`q_0`.`percent_of_total__country` = `q_1`.`percent_of_total__country`
		or ((`q_0`.`percent_of_total__country` is null)
			and (`q_1`.`percent_of_total__country` is null))))
	and ((`q_0`.`percent_of_total__product` = `q_1`.`percent_of_total__product`
		or ((`q_0`.`percent_of_total__product` is null)
			and (`q_1`.`percent_of_total__product` is null)))))
select
	coalesce(`q_0`.`percent_of_total__country`, `q_1`.`percent_of_total__country`) `percent_of_total__country`,
	coalesce(`q_0`.`percent_of_total__product`, `q_1`.`percent_of_total__product`) `percent_of_total__product`,
	`q_0`.`percent_of_total__revenue` `percent_of_total__revenue`,
	`q_1`.`percent_of_total__country_revenue_percentage` `percent_of_total__country_revenue_percentage`
from
	(
	select
		`main_percent_of_total`.country `percent_of_total__country`,
		`main_percent_of_total`.product `percent_of_total__product`,
		sum(`main_percent_of_total`.revenue) `percent_of_total__revenue`
	from
		(
		select
			1 as id,
			1000 as revenue,
			'A' as product,
			'USA' as country
	union all
		select
			2 as id,
			2000 as revenue,
			'B' as product,
			'USA' as country
	union all
		select
			3 as id,
			3000 as revenue,
			'A' as product,
			'Austria' as country
	union all
		select
			4 as id,
			4000 as revenue,
			'B' as product,
			'Austria' as country
	union all
		select
			5 as id,
			5000 as revenue,
			'A' as product,
			'Netherlands' as country
	union all
		select
			6 as id,
			6000 as revenue,
			'B' as product,
			'Netherlands' as country ) as `main_percent_of_total`
	group by
		1,
		2) as `q_0`
inner join cte_2 as `q_1` on
	((`q_0`.`percent_of_total__country` = `q_1`.`percent_of_total__country`
		or ((`q_0`.`percent_of_total__country` is null)
			and (`q_1`.`percent_of_total__country` is null))))
	and ((`q_0`.`percent_of_total__product` = `q_1`.`percent_of_total__product`
		or ((`q_0`.`percent_of_total__product` is null)
			and (`q_1`.`percent_of_total__product` is null))))
where
	(`q_1`.`percent_of_total__country_revenue_percentage` > '0.44')
order by
	1 asc,
	2 asc
limit 50000

Second instance generated wrong SQL (missing multi-stage cte's calculation alltogether)

select
	`q_0`.`percent_of_total__country` `percent_of_total__country`,
	`q_0`.`percent_of_total__product` `percent_of_total__product`,
	`q_0`.`percent_of_total__revenue` `percent_of_total__revenue`
from
	(
	select
		`main_percent_of_total`.country `percent_of_total__country`,
		`main_percent_of_total`.product `percent_of_total__product`,
		sum(`main_percent_of_total`.revenue) `percent_of_total__revenue`
	from
		(
		select
			1 as id,
			1000 as revenue,
			'A' as product,
			'USA' as country
	union all
		select
			2 as id,
			2000 as revenue,
			'B' as product,
			'USA' as country
	union all
		select
			3 as id,
			3000 as revenue,
			'A' as product,
			'Austria' as country
	union all
		select
			4 as id,
			4000 as revenue,
			'B' as product,
			'Austria' as country
	union all
		select
			5 as id,
			5000 as revenue,
			'A' as product,
			'Netherlands' as country
	union all
		select
			6 as id,
			6000 as revenue,
			'B' as product,
			'Netherlands' as country ) as `main_percent_of_total`
	group by
		1,
		2) as `q_0`
where
	(`q_0`.`percent_of_total__revenue` / nullif(sum(`q_0`.`percent_of_total__revenue`), 0) > '0.44')
order by
	1 asc,
	2 asc
limit 50000

Minimally reproducible Cube Schema

  - name: percent_of_total
    sql: >
      SELECT 1 AS id, 1000 AS revenue, 'A' AS product, 'USA' AS country UNION ALL
      SELECT 2 AS id, 2000 AS revenue, 'B' AS product, 'USA' AS country UNION ALL
      SELECT 3 AS id, 3000 AS revenue, 'A' AS product, 'Austria' AS country UNION ALL
      SELECT 4 AS id, 4000 AS revenue, 'B' AS product, 'Austria' AS country UNION ALL
      SELECT 5 AS id, 5000 AS revenue, 'A' AS product, 'Netherlands' AS country UNION ALL
      SELECT 6 AS id, 6000 AS revenue, 'B' AS product, 'Netherlands' AS country
 
    dimensions:
      - name: product
        sql: product
        type: string
 
      - name: country
        sql: country
        type: string
 
    measures:
      - name: revenue
        sql: revenue
        format: currency
        type: sum
      
      - name: country_revenue
        multi_stage: true
        sql: "{revenue}"
        format: currency
        type: sum
        group_by:
          - country
      
      - name: country_revenue_percentage
        multi_stage: true
        sql: "{revenue} / NULLIF({country_revenue}, 0)"
        type: number

Version:
1.3.36

[igorlukanin]

Thanks for reporting this @michofwood 👋

I was able to reproduce this with 1.3.39. We'll take a look.

[michofwood]

Igor, thanks for the fine support.

I just want to add that my sql may be arguably wrong and should have use the HAVING clause.

select 
	country
	,product
	,measure(revenue) revenue
from percent_of_total
group by 1,2
having
   measure(country_revenue_percentage) > 0.44
order by 1,2

I just want to confirm that with HAVING, we get the same behaviour as with the WHERE clause.

Thanks.

[igorlukanin]

Hey @michofwood,

I've overlooked it initially, this is actually a really great observation on HAVING.

So, here are three queries:

select 
	country
	,product
	,measure(revenue) revenue
	,measure(country_revenue_percentage) country_revenue_percentage_alias
	from percent_of_total
group by 1,2
having country_revenue_percentage_alias > 0.44
order by 1,2;

select 
	country
	,product
	,measure(revenue) revenue
	,measure(country_revenue_percentage) country_revenue_percentage_alias
	from percent_of_total
group by 1,2
having measure(country_revenue_percentage) > 0.44
order by 1,2;

select 
	country
	,product
	,measure(revenue) revenue
	from percent_of_total
group by 1,2
having measure(country_revenue_percentage) > 0.44
order by 1,2;

First two of them correctly run, the third one errors out—however, it shouldn't. So, the bug is still there.

That being said, conditions on measures should definitely be applied via HAVING, not WHERE. It's enforced with the following error message now: ERROR: Compute error: aggregate functions are not allowed in WHERE.

The bug still needs to be addressed though.