[Django] #31060: Window expression are not allowed in

[Django]

#31060: Window expression are not allowed in
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.0
layer (models, ORM) |
Severity: Release | Keywords:
blocker |
Triage Stage: Accepted | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.

--
Ticket URL: <https://code.djangoproject.com/ticket/31060>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

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

-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

--
Ticket URL: <https://code.djangoproject.com/ticket/31060#comment:1>

[Django]

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

-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Alexandr Artemyev):

* cc: Alexandr Artemyev (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/31060#comment:2>

[Django]

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

-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (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.

--
Ticket URL: <https://code.djangoproject.com/ticket/31060#comment:3>

[Django]

#31060: Window expression are not allowed in conditional statements used only in
the SELECT clause.
-------------------------------------+-------------------------------------

Reporter: felixxm | Owner: Alex
| Aktsipetrov
Type: Bug | Status: assigned

Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Alex Aktsipetrov):

* owner: nobody => Alex Aktsipetrov
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/31060#comment:4>

[Django]

#31060: Window expression are not allowed in conditional statements used only in
the SELECT clause.
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: Alex
| Aktsipetrov
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (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.

--
Ticket URL: <https://code.djangoproject.com/ticket/31060#comment:5>

[Django]

#31060: Window expression are not allowed in conditional statements used only in
the SELECT clause.
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: Alex
| Aktsipetrov
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (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...

--
Ticket URL: <https://code.djangoproject.com/ticket/31060#comment:6>

[Django]

#31060: Window expression are not allowed in conditional statements used only in
the SELECT clause.
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: Alex
| Aktsipetrov
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (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.

--
Ticket URL: <https://code.djangoproject.com/ticket/31060#comment:7>

[Django]

#31060: Window expression are not allowed in conditional statements used only in
the SELECT clause.
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: Alex
| Aktsipetrov
Type: Bug | Status: assigned
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* has_patch: 0 => 1

Comment:

[https://github.com/django/django/pull/12185 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/31060#comment:8>

[Django]

#31060: Window expression are not allowed in conditional statements used only in
the SELECT clause.
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: Alex
| Aktsipetrov

Type: Bug | Status: closed

Component: Database layer | Version: 3.0
(models, ORM) |

Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak <felisiak.mariusz@…>):

* status: assigned => closed
* resolution: => fixed

Comment:

In [changeset:"bf12273db4e53779546e2ac7b65c0ce8e3c8a640" bf12273d]:
{{{
#!CommitTicketReference repository=""
revision="bf12273db4e53779546e2ac7b65c0ce8e3c8a640"
Fixed #31060 -- Reallowed window expressions to be used in conditions
outside of queryset filters.

Regression in 4edad1ddf6203326e0be4bdb105beecb0fe454c4.

Thanks utapyngo for the report.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31060#comment:9>

[Django]

#31060: Window expression are not allowed in conditional statements used only in
the SELECT clause.
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: Alex
| Aktsipetrov
Type: Bug | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Release blocker | Resolution: fixed

Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak <felisiak.mariusz@…>):

In [changeset:"8af07712df54efbd1d3d9130ab90236f530278c1" 8af07712]:
{{{
#!CommitTicketReference repository=""
revision="8af07712df54efbd1d3d9130ab90236f530278c1"
[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.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/31060#comment:10>