#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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/01070199c2df0475-34c431ac-7efe-4241-82b5-d4ffa0c4a5cd-000000%40eu-central-1.amazonses.com.
