#24254: Passing a query to "pk__in" of another query may result in invalid SQL
-------------------------------------+-------------------------------------
Reporter: bronger | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.7
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by rtpg):
I've hit this issue as well, reaching an identical situation of `__in` +
ordering + distinct + slicing.
Typing this out, I realised that if you have ordering and slicing, then
you'll need the values in the `SELECT` (see the Postgres error mentioned
before).
So I would propose a "tuple unwrapping" in that case. So the `pk__in=qs`
property would expand to SQL like `SELECT U1."id" FROM (SELECT DISTINCT
... ) U1`
{{{
In [61]: print
MyModel.objects.filter(pk__in=MyModel.objects.all().distinct()[:10]).query
# invalid syntax due to missing tuple unwrapping but includes proper
ordering
SELECT "test_mymodel"."id", "test_mymodel"."name" FROM "test_mymodel"
WHERE "test_mymodel"."id" IN
(SELECT DISTINCT "test_mymodel"."id", "test_mymodel"."name" FROM
"test_mymodel" ORDER BY "test_mymodel"."name" ASC LIMIT 10)
ORDER BY "test_mymodel"."name" ASC
# proposed solution with tuple unwrapping
SELECT "test_mymodel"."id", "test_mymodel"."name" FROM "test_mymodel"
WHERE "test_mymodel"."id" IN
(SELECT U0."id" FROM
(SELECT DISTINCT "test_mymodel"."id", "test_mymodel"."name" FROM
"test_mymodel" ORDER BY "test_mymodel"."name" ASC LIMIT 10) U0
)
ORDER BY "test_mymodel"."name" ASC
}}}
I think we could always do wrapping and unwrapping of `__in` subqueries.
The cost is pretty negligible in Postgres (an extra select node), and
avoids having to figure out the innards of the subquery select.
--
Ticket URL: <https://code.djangoproject.com/ticket/24254#comment:7>
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 post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/065.8c17a540762f7cb7acdc2545e58db886%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
