Opened 5 years ago

Closed 5 years ago

Last modified 5 years 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 by Mariusz Felisiak, 5 years ago

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

comment:2 by Alexandr Artemyev, 5 years ago

Cc: Alexandr Artemyev added

comment:3 by Alex Aktsipetrov, 5 years ago

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 by Alex Aktsipetrov, 5 years ago

Owner: changed from nobody to Alex Aktsipetrov
Status: newassigned

comment:5 by Simon Charette, 5 years ago

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 by Alex Aktsipetrov, 5 years ago

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 by Simon Charette, 5 years ago

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 by Mariusz Felisiak, 5 years ago

Has patch: set

comment:9 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

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 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

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