#35396: QuerySet filters incorrectly pushed to the inner query when applied
after a
window function filter
-------------------------------------+-------------------------------------
Reporter: Gary Chen | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 4.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I may be doing something funky here with my window function, but I'm
trying to get the first row in each partition of a window, then filter the
results by another column. I expect the last filter to be applied to the
outer query created by the window function filter, but it's "pushed" up to
the inner query leading to incorrect results. Filtering on window
functions was introduced in 4.2 and I wonder if this is a case that wasn't
caught.
== Example
A simple model:
{{{#!python
class Player(Model):
name = CharField()
city = CharField()
score = IntegerField()
active = BooleanField()
}}}
Some data:
||= id =||= name =||= city =||= score =||=active=||
||0||Cary||Phoenix||17||false||
||1||Joe||Phoenix||15||true||
||2||Katie||Phoenix||13||true||
||3||Bob||Springfield||12||true||
||4||Alice||Springfield||10||true||
The queryset:
{{{#!python
Player.objects.annotate(
first=Window(
expression=functions.FirstValue("id"),
partition_by=[F("city")],
order_by=("-score"),
),
).filter(id=F("first"), active=True)
}}}
The generated sql looks like this:
{{{#!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`
FROM
`myapp_player`
WHERE
`myapp_player`.`active` = True
) `qualify`
WHERE
`col1` = (`first`)
}}}
This would return this result:
||= id =||= name =||= city =||= score =||=active=||
||1||Joe||Phoenix||15||true||
||3||Bob||Springfield||12||true||
== Expected
I would expect the generated SQL from that queryset to look like this:
{{{#!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`
FROM
`myapp_player`
) `qualify`
WHERE
`col1` = (`first`) AND `col5` = True
}}}
With a result of:
||= id =||= name =||= city =||= score =||=active=||
||3||Bob||Springfield||12||true||
--
Ticket URL: <https://code.djangoproject.com/ticket/35396>
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/0107018f06f40843-8407417c-8ee5-4461-8c97-82e4fb22cd7e-000000%40eu-central-1.amazonses.com.
