#35833: Annotation yielding an empty set causes entire QuerySet to become empty
-------------------------------------+-------------------------------------
Reporter: Jacob Walls | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.1
(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
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
The way Postgres set-returning annotations work is definitely unintuitive
if you are used to subqueries which forces you to have a single row and
implicitly use `OUTER` instead of `INNER` semantics so I wouldn't be too
harsh on yourself here.
What I mean is that the fact subqueries which are somewhat set-returning
constructs behave in an `OUTER JOIN LATERAL` manner
{{{#!sql
SELECT
author.id, (
SELECT post.id
FROM post
WHERE post.author_id = author.id
ORDER BY id LIMIT 1
) first_post_id
FROM author
}}}
[https://dbfiddle.uk/A8JxBxnF Is equivalent to]
{{{#!sql
SELECT
author.id,
first_post.id
FROM author
LEFT OUTER JOIN LATERAL (
SELECT post.*
FROM post
WHERE post.author_id = author.id
ORDER BY id
LIMIT 1
) first_post ON true
}}}
||= id =||= first_post_id =||
|| 1 || 1 ||
|| 2 || null ||
While other set-returning functions behave in a `INNER JOIN LATERAL`
manner
{{{#!sql
SELECT
author.id,
jsonb_array_elements(jsonb_path_query_array(post_ids, '$[0 to 0]'))
first_post_id
FROM author
}}}
[https://dbfiddle.uk/d-22xqgK Is equivalent to]
{{{#!sql
SELECT
author.id,
first_post.id AS first_post_id
FROM author
INNER JOIN LATERAL (
SELECT * FROM jsonb_array_elements(jsonb_path_query_array(post_ids, '$[0
to 0]'))
) first_post(id) ON true
}}}
||= id =||= first_post_id =||
|| 1 || 1 ||
Is definitely breaks the principle of least astonishment, at least it did
for me!
--
Ticket URL: <https://code.djangoproject.com/ticket/35833#comment:6>
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/01070192982e10e2-45737710-88be-412e-8249-8ab9b27a0a53-000000%40eu-central-1.amazonses.com.
