id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
36647	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."	Bug	closed	Database layer (models, ORM)	4.2	Normal	invalid		Simon Charette	Unreviewed	0	0	0	0	0	0