#35396: QuerySet filters incorrectly pushed to the inner query when applied
after a
window function filter
-------------------------------------+-------------------------------------
Reporter: Gary Chen | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(models, ORM) |
Severity: Normal | Resolution: wontfix
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):
* cc: Simon Charette (added)
* resolution: => wontfix
* status: new => closed
Comment:
Changing the behavior would trivial, adjust `Where.split_having_qualify`
to always return everything in `qualify_node` when it's present and
nothing in `where_node` or `having_node` but I don't think it's the right
thing to do.
The real problem here is the ambiguity of what should be done when users
filter against non-windowed and windowed expressions at the same time.
The current implementation defaults to respecting what fetching the result
set without the qualify outer query emulation would do to make sure non-
window referencing filters are applied against the set of rows windowered
over.
In other words if you do
{{{#!python
objects = list(Player.objects.annotate(
first=Window(
expression=functions.FirstValue("id"),
partition_by=[F("city")],
order_by=("-score"),
),
).filter(active=True))
}}}
And you iterate over `objects` you'll only get two matches of `id` and
`first`. The way it's implemented is coherent with how
[https://docs.snowflake.com/en/sql-reference/constructs/qualify QUALIFY is
implemented] in backends that support it.
What I suspect you want here is more control over subquery wrapping
instead (see #24462).
If you want to want to filter out highest score by city you should also
window by `active` and filter against it
{{{#!python
Player.objects.annotate(
first=Window(
expression=functions.FirstValue("id"),
partition_by=[F("city")],
order_by=("-score"),
),
first_active=Window(
expression=functions.FirstValue("active"),
partition_by=[F("city")],
order_by=("-score"),
),
).filter(id=F("first"), first_active=True)
}}}
{{{#!sql
SELECT
*
FROM
(
SELECT
`myapp_player`.`id` AS `col1`,
`myapp_player`.`name` AS `col2`,
`myapp_player`.`city` AS `col3`,
`myapp_player`.`score` AS `col4`,
`myapp_player`.`active` AS `col5`,
FIRST_VALUE(`myapp_player`.`id`) OVER (
PARTITION BY `myapp_player`.`city`
ORDER BY
`myapp_player`.`score` DESC
) AS `first`,
FIRST_VALUE(`myapp_player`.`active`) OVER (
PARTITION BY `myapp_player`.`city`
ORDER BY
`myapp_player`.`score` DESC
) AS `first_active`
FROM
`myapp_player`
) `qualify`
WHERE
`col1` = (`first`) AND `first_active` = True
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35396#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018f0768d1bb-67dfb589-ba33-4651-8c86-9aec5fd89732-000000%40eu-central-1.amazonses.com.
