Annotation with Coalesce subquery unexpectedly included in group by

[Joseph Yu]

In the documentation ​https://docs.djangoproject.com/en/4.2/topics/db/aggregation/#order-of-annotate-and-values-clauses, the .values() call dictates the group by if you have an .annotation() with aggregation function (e.g. sum).

Somehow if you proceed to add more field annotations without any aggregation function, this isn't included in the group by in the generated SQL query. For some reason, if you have a field annotation with coalesce, it gets added in the group by.

Model definition:

class A:
  name = models.CharField() # not an FK
  amount = models.DecimalField()  

class B:
  name = models.CharField()
  foo = models.CharField()

class BSnapshot:
  version_name = models.TextField()
  name = models.CharField()
  foo = models.CharField()

Query:

    B.objects.bulk_create([
        B(name='Alice', foo='live_alice_foo'),
        B(name='Bob', foo='live_bob_foo'),
        B(name='Eve', foo='live_eve_foo'),
    ])

    BSnapshot.objects.bulk_create([
        BSnapshot(version_name='v1', name='Alice', foo='snap_v1_alice'),
        BSnapshot(version_name='v2', name='Alice', foo='snap_v2_alice'),
        BSnapshot(version_name='v1', name='Charlie', foo='snap_v1_charlie'),
    ])

    A.objects.bulk_create([
        A(name='Alice', amount=Decimal('10.50')),
        A(name='Alice', amount=Decimal('5.25')),
        A(name='Bob', amount=Decimal('7.00')),
        A(name='Charlie', amount=Decimal('3.00')),
        A(name='Dennis', amount=Decimal('4.00')),  # no B or snapshot -> will be filtered out
    ])

    version_name = 'v1'
    live_foo = B.objects.filter(name=OuterRef('name'))
    snapshot_foo = BSnapshot.objects.filter(name=OuterRef('name'), version_name=version_name)

    foo = Coalesce(
        Subquery(snapshot_foo.values('foo')[:1]),
        Subquery(live_foo.values('foo')[:1])
    )

    version_1 = (
        A.objects
        .values('name')
        .order_by('name')
        .annotate(amount_sum=Sum('amount'))
        .annotate(foo=foo)
        .filter(foo__isnull=False)
    )

Generated SQL query:

SELECT "app_a"."name" AS "name",
       (CAST(SUM("app_a"."amount") AS NUMERIC)) AS "amount_sum",
       COALESCE(
                  (SELECT U0."foo" AS "foo"
                   FROM "app_bsnapshot" U0
                   WHERE (U0."name" = ("app_a"."name")
                          AND U0."version_name" = 'v1')
                   LIMIT 1),
                  (SELECT U0."foo" AS "foo"
                   FROM "app_b" U0
                   WHERE U0."name" = ("app_a"."name")
                   LIMIT 1)) AS "foo"
FROM "app_a"
WHERE COALESCE(
                 (SELECT U0."foo" AS "foo"
                  FROM "app_bsnapshot" U0
                  WHERE (U0."name" = ("app_a"."name")
                         AND U0."version_name" = 'v1')
                  LIMIT 1),
                 (SELECT U0."foo" AS "foo"
                  FROM "app_b" U0
                  WHERE U0."name" = ("app_a"."name")
                  LIMIT 1)) IS NOT NULL
GROUP BY 1,
         3
ORDER BY 1 ASC

This is inconsistent with what is in the documentation and has a potential performance impact if suddenly something gets added in the group by clause.

[Natalia Bidart]

Hello Joseph Yu, thank you for your ticket. Can you please confirm behavior using a supported version of Django (4.2 is on security-maintenance mode only)? Ideally, using latest main, if that's not possible, or trying 6.0a1 or 5.2 otherwise. Also, could you please provide a fully functional and runnable Python/Django snippet? We would appreciate if you ensure that your models inherit from models.Model and that every necessary import is included.

As for the observed behavior, I understand that this happens because of how Django's ORM determines which expressions must be included in the SQL GROUP BY when both aggregation and non-aggregate annotations are present. After a .values() call, Django uses the selected fields to define the grouping. When a second .annotate() adds an expression such the one proposed, the ORM treats it as a computed expression that may depend on grouped columns.

Having said all the above, I think this report seems better suited to be a support request. The best place to get answers to your issue is using any of the user support channels from ​this link.

Since the goal of this issue tracker is to track confirmed issues about Django itself, I'll be closing this ticket as invalid following the ​ticket triaging process. If, after consultation in the Django Forum, you find out that this is indeed a bug in Django, please re-open with the specific details.