ProgrammingError: non-integer constant in GROUP BY with Case When and annotate Count

[Guillaume LEBRETON]

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.

[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? 🤔

[David Sanders]

Marking invalid. Please reopen if more information comes to light :)

[David Sanders]

PS: I tested on Postgres & SQLite

[Guillaume LEBRETON]

Replying to 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

[Mariusz Felisiak]

It was fixed by b7b28c7c189615543218e81319473888bc46d831.

[Guillaume LEBRETON]

Replying to 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 ?

[Mariusz Felisiak]

Replying to Guillaume LEBRETON:

Replying to 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 ​supported versions policy.