Window expression are not allowed in conditional statements used only in the SELECT clause.

[Mariusz Felisiak]

Django raises NotSupportedError when using window expressions in conditional statements used only in the SELECT clause, e.g.

Employee.objects.annotate(
    lag=Window(
        expression=Lag(expression='salary', offset=1),
        partition_by=F('department'),
        order_by=[F('salary').asc(), F('name').asc()],
    ),
    is_changed=Case(
        When(salary=F('lag'), then=Value(False)),
        default=Value(True), output_field=BooleanField()
    ),
)

The SQL standard disallows referencing window functions in the WHERE clause but in this case it's only used in the SELECT clause so this should be possible.

Thanks utapyngo for the report.

Regression in 4edad1ddf6203326e0be4bdb105beecb0fe454c4.

[Alex Aktsipetrov]

So it seems a bugfix would envolve moving the raise from build_filter to add_q.
And we would have to propagate the necessity of the raise somehow, in WhereNode or as an additional return param.

[Simon Charette]

Given build_filter now calls _add_q on the master branch the solution will probably involve adding a new kwarg to disable the check_filterable check instead instead.

[Alex Aktsipetrov]

Yeah, that works too (and is easier to implement).

Btw the comment in _add_q Add a Q-object to the current filter seems to be misleading, considering the usage in CASE-WHEN...

[Simon Charette]

Agreed that the comment is misleading, it has been for a while though. The whole interactions between _add_q, add_q, and build_filter could probably be better expressed as add_q, _build_q, and build_filter as only add_q actually append nodes to the current query. The other methods only make sure query.aliases contains Join references to table necessary for the filter conditions.

[Mariusz Felisiak]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In bf12273d:

Fixed #31060 -- Reallowed window expressions to be used in conditions outside of queryset filters.

Regression in 4edad1ddf6203326e0be4bdb105beecb0fe454c4.

Thanks utapyngo for the report.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 8af07712:

[3.0.x] Fixed #31060 -- Reallowed window expressions to be used in conditions outside of queryset filters.

Regression in 4edad1ddf6203326e0be4bdb105beecb0fe454c4.

Thanks utapyngo for the report.

Backport of bf12273db4e53779546e2ac7b65c0ce8e3c8a640 from master.