Segment filter is incorrectly generated when querying pre-aggregation with rollingWindow measure
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
**Describe the bug**
When querying a pre-aggregation for a `rollingWindow` measure and trying to filter on a segment the sql generated uses the `sql` attribute from the segment instead of the proper column name in the pre-aggregation. Removing the segment allows proper sql generation and so does switching to a non-`rollingWindow` measure that is defined in the same pre-aggregation.
**To Reproduce**
1. Define a Cube with a normal and a rollingWindow measure, and a segment.
```js
cube('Order', {
sql: 'select * from order',
measures: {
amountSum: {
sql: 'amount',
type: 'sum',
},
rollingAmountSum: {
sql: 'amount',
type: 'sum',
rollingWindow: {
trailing: '3 month',
offset: 'end',
},
},
dimensions: {
created: {
sql: 'created',
type: 'time',
},
},
segments: {
openOnly: {
sql: 'open = true',
},
},
}
```
3. Define a pre-aggregation with those attributes
```js
preAggregations: {
main: {
measures: [amountSum, rollingAmountSum],
segments: [openOnly],
timeDimension: created,
granularity: 'month',
}
}
```
5. View the generated SQL once the pre-aggregations are built
```
// Query
{
measures: ['Order.rollingAmountSum'],
timeDimensions: [
{ dimension: 'Order.created', dateRange: ['2022-06-01', '2022-08-31'], granularity: 'month },
},
segments: ['Order.openOnly'],
}
// SQL generated
SELECT
"Order.created_series"."date_from" "order__created_month",
sum("order__rolling_amount_sum")
FROM (
-- rolling window select stuff
...
) AS "Order.created_series"
LEFT JOIN (
SELECT
"order__created_month" "order__created_month",
sum("order__rolling_amount_sum") "order__rolling_amount_sum",
FROM
dev_pre_aggregations.order_main AS "order_rolling_amount_sum_cumulative__order_created_per_month"
WHERE
(
"order".open = true -- This is using the sql attribute instead of the column
)
...
```
This SQL causes an error:
`Internal: Error during planning: No field named 'order.open'. Valid fields are 'order__amount_sum', 'order__rolling_amount_sum', 'order__open_only', 'order__created_month'.`
**Expected behavior**
The generated SQL should be to just use the column name in the pre-aggregation with a variable substitution
eg CORRECT SQL.
```sql
WHERE
("order__open_only" = $ 1)
```
NOTE: switching to the non-rollingWindow measure in the query generates the above correct sql. All other filters are applied correctly.
**Version:**
0.30.50
Contributor guide
Assessment
This issue has not been assessed yet.