id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
35396	QuerySet filters incorrectly pushed to the inner query when applied after a window function filter	Gary Chen	nobody	"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||
"	Bug	closed	Database layer (models, ORM)	4.2	Normal	wontfix		Simon Charette	Unreviewed	0	0	0	0	0	0