[Django] #36647: Annotation with Coalesce subquery unexpectedly included in group by

[Django]

#36647: Annotation with Coalesce subquery unexpectedly included in group by
-------------------------------------+-------------------------------------
Reporter: Joseph Yu | Type: Bug
Status: new | Component: Database
| layer (models, ORM)
Version: 4.2 | Severity: Normal
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36647>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36647: Annotation with Coalesce subquery unexpectedly included in group by
-------------------------------------+-------------------------------------

Reporter: Joseph Yu | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 4.2
(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
-------------------------------------+-------------------------------------

Changes (by Natalia Bidart):

* cc: Simon Charette (added)
* resolution: => invalid
* status: new => closed

Comment:

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
[https://docs.djangoproject.com/en/dev/faq/help/#how-do-i-do-x-why-
doesn-t-y-work-where-can-i-go-to-get-help 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
[https://docs.djangoproject.com/en/dev/internals/contributing/triaging-
tickets/#closing-tickets 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36647#comment:1>