#31639: Incorrect result when using __year lookup in aggregation filter
-------------------------------------+-------------------------------------
Reporter: Baptiste | Owner: nobody
Mispelon |
Type: Bug | Status: new
Component: Database | Version: master
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 |
-------------------------------------+-------------------------------------
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).
--
Ticket URL: <https://code.djangoproject.com/ticket/31639>
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/052.0f0ac7344febad812049cc649e6fe08c%40djangoproject.com.
