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
31639	Allow using transforms in F() and OuterRef().	Baptiste Mispelon	Ian Foote	"Consider two models `Client` and `Bill` defined like so:
{{{#!python
from django.db import models


class Client(models.Model):
    name = models.CharField(max_length=50)


class Bill(models.Model):
    client = models.ForeignKey('Client', on_delete=models.CASCADE, related_name='bills')
    issued_on = models.DateField()
}}}

I was trying to get a `Bill` queryset where each bill is annotated with the year of the previous bill (same client) and came up with the following code which gave me incorrect results:
{{{#!python
from django.db.models import F, Max, Q
from django.db.models.functions import ExtractYear


filter_Q = Q(client__bills__issued_on__year__lt=F('issued_on__year'))
annotation = Max(ExtractYear('client__bills__issued_on'), filter=filter_Q)

# Returns wrong annotations (as if the filter=... was not there)
Bill.objects.annotate(previous_year=annotation)
}}}

However if I use `ExtractYear` instead of the `__year` lookup then I get the correct results:
{{{#!python
filter_Q = Q(client__bills__issued_on__year__lt=ExtractYear('issued_on'))
}}}

I would assume that `F('issued_on__year')` should be strictly equivalent to `ExtractYear('issued_on')` but somehow it's not the case here. I believe that's a bug.

Here's a small testcase that summarizes the issue:
{{{#!python
from django.db.models import F, Max, Q
from django.db.models.functions import ExtractYear
from django.test import TestCase

from .models import Bill, Client


class ReproductionTestCase(TestCase):
    @classmethod
    def setUpTestData(cls):
        c = Client.objects.create(name=""Baptiste"")
        c.bills.create(issued_on='2020-01-01')
        c.bills.create(issued_on='2019-01-01')
        c.bills.create(issued_on='2019-07-01')
        c.bills.create(issued_on='2018-01-01')


    def test_extractyear(self):
        filter_Q = Q(client__bills__issued_on__year__lt=ExtractYear('issued_on'))
        annotation = Max(ExtractYear('client__bills__issued_on'), filter=filter_Q)

        queryset = Bill.objects.annotate(previous_year=annotation).order_by('issued_on')
        expected = [None, 2018, 2018, 2019]
        self.assertQuerysetEqual(queryset, expected, transform=lambda bill: bill.previous_year)

    def test_f_object_and_lookup(self):
        filter_Q = Q(client__bills__issued_on__year__lt=F('issued_on__year'))
        annotation = Max(ExtractYear('client__bills__issued_on'), filter=filter_Q)

        queryset = Bill.objects.annotate(previous_year=annotation).order_by('issued_on')
        expected = [None, 2018, 2018, 2019]
        self.assertQuerysetEqual(queryset, expected, transform=lambda bill: bill.previous_year)
}}}

Not sure if it's related but while debugging this I came across #29396. Before that change, evaluating the queryset raised an `IndexError` (both when using `ExtractYear` and with the `__year` lookup)."	New feature	closed	Database layer (models, ORM)	dev	Normal	fixed		Baptiste Mispelon Gordon Wrigley	Ready for checkin	1	0	0	0	0	0