#35833: Annotation yielding an empty set causes entire QuerySet to become empty
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):
* cc: Simon Charette (added)
Comment:
I could not reproduce the difference of `count` and `len` locally with the
provided test case when `set_returning = True` is appropriately set; both
result in returning no rows when dealing with empty sequence references.
This is definitely a bug if we can reproduce the mismatch.
> So I guess my expectation is that I can use the annotation as written
without interfering with my queryset as a whole.
I'm not sure how much of a this is a bug over a misunderstanding of how
`JSONB_ARRAY_ELEMENTS` and set-returning functions actually works. AFAIK
when using a set-returning function in a `SELECT` clause (which is what
`annotate` does) Postgres performs
[https://en.wikipedia.org/wiki/Cartesian_product a Cartesian product] of
the existing rows and the each values returned for the row. The Cartesian
product of any set with the empty set is the empty set.
If you want to avoid the empty set product then you must avoid providing a
value to `JSONB_ARRAY_ELEMENTS` that results in one. In the case of `NULL`
this can be avoided using `Coalesce` but you also have to deal with the
empty array case. Something like the following is possibly what you're
after?
{{{#!python
class JsonbArrayLength(Func):
arity = 1
ouput_field = models.IntegerField()
function = "jsonb_array_length"
empty_set_condition = Q(provisionaledits=None) |
LesserThan(JsonbArrayLength("provisionaledits", 1))
empty_set_value = Value(["<empty>"], JSONField())
TileModel.objects.annotate(
annotation=JsonbArrayElements(
Case(
When(empty_set_condition, then=empty_set_value),
default="provisionaledits",
)
)
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35833#comment:3>
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 on the web visit
https://groups.google.com/d/msgid/django-updates/01070192908aad29-c34461f7-fcf8-4c32-84ce-d6314f8beeb0-000000%40eu-central-1.amazonses.com.
