Repository navigation
Applying filters from WHERE clause with SET #54
Description
Using the sample data from the documentation, run the following query:
select year, AGGREGATE(revenue) at (set year=year-1) as last, AGGREGATE(revenue) as this from sales_v where region='North' order by year;
This returns:
year, last, this
2023, NULL, 37000
2024, 111000, 59000
The filter region='North' is not being applied in the computation of last.
Is there a way to have the filter apply? I tried various versions of at (visible) and none worked. I could not find a clear statement in the yardstick documentation or the original paper. Of course, I can use a subquery but it seems like there should be a more compact version.
Activity
DanCory commentedon Oct 29, 2026
There is an existing yardstick test for
However, this test doesn't have a WHERE clause. Adding a WHERE clause in this case does not have it apply.
Fix grouping, alias, expression, and modifier lowering (#69)
nicosuave commentedon Nov 10, 2026
Fixed in #69:
VISIBLEwas being ignored when combined withSET, so useAT (VISIBLE SET year = year - 1)to keep theWHEREfilter; plainAT (SET ...)still drops it by design and now warns.