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.