Opened 17 months ago

Closed 17 months ago

Last modified 16 months ago

#31060 closed Bug (fixed)

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

Reported by: Mariusz Felisiak Owned by: Alex Aktsipetrov
Component: Database layer (models, ORM) Version: 3.0
Severity: Release blocker Keywords:
Cc: Mads Jensen, Alex Aktsipetrov, Alexandr Artemyev Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (10)

comment:1 Changed 17 months ago by Mariusz Felisiak

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

comment:2 Changed 17 months ago by Alexandr Artemyev

Cc: Alexandr Artemyev added

comment:3 Changed 17 months ago by 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.

comment:4 Changed 17 months ago by Alex Aktsipetrov

Owner: changed from nobody to Alex Aktsipetrov
Status: newassigned

comment:5 Changed 17 months ago by 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.

comment:6 Changed 17 months ago by 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...

comment:7 Changed 17 months ago by 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.

comment:8 Changed 17 months ago by Mariusz Felisiak

Has patch: set

comment:9 Changed 17 months ago by Mariusz Felisiak <felisiak.mariusz@…>

Resolution: fixed
Status: assignedclosed

In bf12273d:

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

Regression in 4edad1ddf6203326e0be4bdb105beecb0fe454c4.

Thanks utapyngo for the report.

comment:10 Changed 16 months ago by 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.

Note: See TracTickets for help on using tickets.
Back to Top