[Django] #34449: ProgrammingError: non-integer constant in GROUP BY with Case When and annotate Count
Django
annotate Count
-------------------------------------+-------------------------------------
Reporter: Guillaume | Owner: nobody
LEBRETON |
Type: Bug | Status: new
Component: Database | Version: 4.1
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 had a suprising error, that appears so far only with postgres. Lets have
this models and tests:
{{{
# models.py
class Person(models.Model):
name = models.CharField(max_length=10, blank=True, null=True)
class QuantitativeAttribute(models.Model):
value = models.PositiveIntegerField()
name = models.CharField(max_length=10)
person = models.ForeignKey(Person, on_delete=models.CASCADE)
# tests.py
class QuantitativeTestCase(TestCase):
@classmethod
def setUpTestData(cls):
cls.p1 = Person.objects.create(name='p1')
QuantitativeAttribute.objects.create(
person=cls.p1,
value=27,
name='age',
)
def test_annotate_fail(self):
"""This test is successfull with sqlite"""
expected_qs = [{'alarm': 'warning', 'number': 1, 'pk': 1}]
qs = Person.objects\
.all()\
.annotate(number=Count('quantitativeattribute'))
qs = qs.annotate(alarm=Case(
When(id__in=[], then=Value('danger',
output_field=models.CharField())),
default=Value('warning')
))
self.assertQuerysetEqual(qs.values('pk', 'number', 'alarm'),
expected_qs)
# => raises django.db.utils.ProgrammingError: non-integer constant
in GROUP BY
# LINE 1: ...ibute"."person_id") GROUP BY "argent_person"."id",
'warning'
def test_annotate_success(self):
"""This test is successfull with sqlite and postgres"""
expected_qs = [{'alarm': 'warning', 'number': 1, 'pk': 1}]
qs = Person.objects\
.all()\
.annotate(number=Count('quantitativeattribute'))
qs = qs.annotate(alarm=Case(
# When(id__in=[], then=Value('danger',
output_field=models.CharField())),
default=Value('warning')
))
self.assertQuerysetEqual(qs.values('pk', 'number', 'alarm'),
expected_qs)
}}}
It appear that in the case of the first test, django adds an unwanted
groupby argument when database is postgresql(v15), but everithing is ok
with sqlite.
Note that is did search for a similar issues but none seemed to be the
exact same problem.
--
Ticket URL: <https://code.djangoproject.com/ticket/34449>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
annotate Count
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by David Sanders):
Hi, thanks for the report but I can't reproduce this on latest main or 4.1
using the example supplied. Inspecting the queryset shows that Django is
correctly using the column number for the alarm annotation:
{{{
qs = Person.objects.all().annotate(number=Count("quantitativeattribute"))
qs = qs.annotate(
alarm=Case(
When(id__in=[], then=Value("danger", output_field=CharField())),
default=Value("warning"),
)
)
qs = qs.values("pk", "number", "alarm")
print(qs.query)
}}}
result:
{{{
SELECT "ticket_34449_person"."id",
COUNT("ticket_34449_quantitativeattribute"."id") AS "number", warning AS
"alarm" FROM "ticket_34449_person" LEFT OUTER JOIN
"ticket_34449_quantitativeattribute" ON ("ticket_34449_person"."id" =
"ticket_34449_quantitativeattribute"."person_id") GROUP BY
"ticket_34449_person"."id", 3
}}}
Maybe double check the supplied example isn't leaving anything out? 🤔
--
Ticket URL: <https://code.djangoproject.com/ticket/34449#comment:1>
Django
annotate Count
-------------------------------------+-------------------------------------
Reporter: Guillaume LEBRETON | Owner: nobody
Component: Database layer | Version: 4.1
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => invalid
Comment:
Marking invalid. Please reopen if more information comes to light :)
--
Ticket URL: <https://code.djangoproject.com/ticket/34449#comment:2>
Django
annotate Count
-------------------------------------+-------------------------------------
Reporter: Guillaume LEBRETON | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by David Sanders):
PS: I tested on Postgres & SQLite
--
Ticket URL: <https://code.djangoproject.com/ticket/34449#comment:3>
Django
annotate Count
-------------------------------------+-------------------------------------
Reporter: Guillaume LEBRETON | Owner: nobody
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: closed => new
* resolution: invalid =>
Comment:
Replying to [comment:2 David Sanders]:
> Marking invalid. Please reopen if more information comes to light :)
Hello, you're right, on main the issue is not there, but seems to be in
4.1.7, which appear to be the lastest version available via pip. So maybe
that's a bug that was corrected recently ?
I made this branch from a fork, that i derived from the 4.1.7 tag.
https://github.com/Guilouf/django/tree/4.1.7-bug
I you launch the the command ` python runtests.py z_bug` the test should
fail like in my first example. You will aslo need to configure
user/password in the test_sqlite.py file (which is juste a setting file
from what i understood.)
I am running with python 3.11 and postgres 15, and here is my pip freeze
output.
{{{
aiohttp==3.8.4
aiosignal==1.3.1
aiosmtpd==1.4.4.post2
argon2-cffi==21.3.0
argon2-cffi-bindings==21.2.0
asgiref==3.6.0
async-generator==1.10
async-timeout==4.0.2
atpublic==3.1.1
attrs==22.2.0
bcrypt==4.0.1
black==23.3.0
certifi==2022.12.7
cffi==1.15.1
charset-normalizer==3.1.0
click==8.1.3
colorama==0.4.6
-e
git+https://github.com/Guilouf/django.git@8029f2c03a94cab7c3525bdfaa66c2025722d2ec#egg=Django
docutils==0.19
exceptiongroup==1.1.1
frozenlist==1.3.3
geoip2==4.6.0
h11==0.14.0
idna==3.4
Jinja2==3.1.2
MarkupSafe==2.1.2
maxminddb==2.2.0
multidict==6.0.4
mypy-extensions==1.0.0
numpy==1.24.2
outcome==1.2.0
packaging==23.0
pathspec==0.11.1
Pillow==9.4.0
platformdirs==3.2.0
psycopg2==2.9.5
pycparser==2.21
pymemcache==4.0.0
PySocks==1.7.1
pytz==2023.3
PyYAML==6.0
redis==4.5.4
requests==2.28.2
selenium==4.8.3
sniffio==1.3.0
sortedcontainers==2.4.0
sqlparse==0.4.3
tblib==1.7.0
trio==0.22.0
trio-websocket==0.10.2
tzdata==2023.3
urllib3==1.26.15
wsproto==1.2.0
yarl==1.8.2
}}}
As it should now totally reproducible i re open the ticket
--
Ticket URL: <https://code.djangoproject.com/ticket/34449#comment:4>
Django
annotate Count
-------------------------------------+-------------------------------------
Reporter: Guillaume LEBRETON | Owner: nobody
Component: Database layer | Version: 4.1
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => fixed
Comment:
It was fixed by b7b28c7c189615543218e81319473888bc46d831.
--
Ticket URL: <https://code.djangoproject.com/ticket/34449#comment:5>
Django
annotate Count
-------------------------------------+-------------------------------------
Reporter: Guillaume LEBRETON | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Guillaume LEBRETON):
Replying to [comment:5 Mariusz Felisiak]:
> It was fixed by b7b28c7c189615543218e81319473888bc46d831.
Ok, thanks, do you think it will be fixed in a 4.1.8 release later on or
only in 4.2.x ?
--
Ticket URL: <https://code.djangoproject.com/ticket/34449#comment:6>
Django
annotate Count
-------------------------------------+-------------------------------------
Reporter: Guillaume LEBRETON | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak):
Replying to [comment:6 Guillaume LEBRETON]:
> Replying to [comment:5 Mariusz Felisiak]:
> > It was fixed by b7b28c7c189615543218e81319473888bc46d831.
>
> Ok, thanks, do you think it will be fixed in a 4.1.8 release later on or
only in 4.2.x ?
Unfortunately, it doesn't qualify for a backport based on our
[https://docs.djangoproject.com/en/dev/internals/release-process
/#supported-versions supported versions policy].
--
Ticket URL: <https://code.djangoproject.com/ticket/34449#comment:7>