[Django] #36149: Composite primary key subquery lookup prevent usage or specify fields and are not implemented for exact

[Django]

#36149: Composite primary key subquery lookup prevent usage or specify fields and
are not implemented for exact
-------------------------------------+-------------------------------------
Reporter: Simon | Owner: (none)
Charette |
Type: Bug | Status: assigned
Component: Database | Version: 5.2
layer (models, ORM) |
Severity: Release | Keywords:
blocker |
Triage Stage: | Has patch: 1
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I started playing with tuple lookup support for composite primary key and
I noticed two main problems.

First `pk__in=query` lookups completely disallow specifying which fields
should be used in the select clause of the right-hand-side and implicitly
set them to the left-hand-side name which assumes the same set of left-
hand-side field name is shared by the right-hand-side which is a bad
assumption

{{{#!diff
diff --git a/tests/composite_pk/models/tenant.py
b/tests/composite_pk/models/tenant.py
index 6286ed2354..c85869afa7 100644
--- a/tests/composite_pk/models/tenant.py
+++ b/tests/composite_pk/models/tenant.py
@@ -44,6 +44,7 @@ class Comment(models.Model):
related_name="comments",
)
text = models.TextField(default="", blank=True)
+ integer = models.IntegerField(default=0)


class Post(models.Model):
diff --git a/tests/composite_pk/test_filter.py
b/tests/composite_pk/test_filter.py
index fe942b9e5b..78383655a0 100644
--- a/tests/composite_pk/test_filter.py
+++ b/tests/composite_pk/test_filter.py
@@ -182,6 +182,30 @@ def test_filter_comments_by_pk_in(self):
Comment.objects.filter(pk__in=pks).order_by("pk"),
objs
)

+ def test_filter_comments_by_pk_in_subquery(self):
+ self.assertSequenceEqual(
+ Comment.objects.filter(
+ pk__in=Comment.objects.filter(pk=self.comment_1.pk),
+ ),
+ [self.comment_1],
+ )
+ self.assertSequenceEqual(
+ Comment.objects.filter(
+
pk__in=Comment.objects.filter(pk=self.comment_1.pk).values(
+ "tenant_id", "id"
+ ),
+ ),
+ [self.comment_1],
+ )
+ self.comment_2.integer = self.comment_1.id
+ self.comment_2.save()
+ self.assertSequenceEqual(
+ Comment.objects.filter(
+ pk__in=Comment.objects.values("tenant_id", "integer"),
+ ),
+ [self.comment_1],
+ )
+
def test_filter_comments_by_user_and_order_by_pk_asc(self):
self.assertSequenceEqual(
Comment.objects.filter(user=self.user_1).order_by("pk"),
}}}

Allowing fields to be specified requires lifting a `QuerySet`
[https://github.com/django/django/blob/8eca4077f60fa0705ecfd9437c9ceaeef7a3808b/django/db/models/query.py#L1967-L1970
resolving time constraint] which was entirely by-passable by passing
`queryset.query` instead as the right-hand-side.

The tuple exact lookup should also allow querysets with a single element
to be specified as right-hand-side which is currently disallowed

{{{#!diff
diff --git a/tests/composite_pk/test_filter.py
b/tests/composite_pk/test_filter.py
index fe942b9e5b..78383655a0 100644
--- a/tests/composite_pk/test_filter.py
+++ b/tests/composite_pk/test_filter.py
@@ -450,6 +474,30 @@ def test_non_outer_ref_subquery(self):
with self.assertRaisesMessage(ValueError, msg):
Comment.objects.filter(pk=pk)

+ def test_filter_comments_by_pk_exact_subquery(self):
+ self.assertSequenceEqual(
+ Comment.objects.filter(
+ pk=Comment.objects.filter(pk=self.comment_1.pk)[:1],
+ ),
+ [self.comment_1],
+ )
+ self.assertSequenceEqual(
+ Comment.objects.filter(
+
pk__in=Comment.objects.filter(pk=self.comment_1.pk).values(
+ "tenant_id", "id"
+ )[:1],
+ ),
+ [self.comment_1],
+ )
+ self.comment_2.integer = self.comment_1.id
+ self.comment_2.save()
+ self.assertSequenceEqual(
+ Comment.objects.filter(
+ pk__in=Comment.objects.values("tenant_id", "integer"),
+ )[:1],
+ [self.comment_1],
+ )
+
def test_outer_ref_not_composite_pk(self):
subquery = Comment.objects.filter(pk=OuterRef("id")).values("id")
queryset = Comment.objects.filter(id=Subquery(subquery))
}}}

Lastly lifting the constraint that prevented subqueries returning more
than one column from being used as a right-hand-side required adjusting
the existing `exact` and `in` lookup logic to disallow left-hand-side and
right-hand-side with mismatching number of fields.
--
Ticket URL: <https://code.djangoproject.com/ticket/36149>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36149: Composite primary key subquery lookup prevent usage or specify fields and
are not implemented for exact
-------------------------------------+-------------------------------------

Reporter: Simon Charette | Owner: Simon

| Charette
Type: Bug | Status: assigned

Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | 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 Sarah Boyce):

* cc: Csirmaz Bendegúz (added)
* has_patch: 1 => 0
* owner: (none) => Simon Charette
* stage: Unreviewed => Accepted

Comment:

Thank you!
--
Ticket URL: <https://code.djangoproject.com/ticket/36149#comment:1>

[Django]

#36149: Composite primary key subquery lookup prevent usage or specify fields and
are not implemented for exact
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Sarah Boyce):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36149#comment:2>

[Django]

#36149: Composite primary key subquery lookup prevent usage or specify fields and
are not implemented for exact
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Sarah Boyce):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36149#comment:3>

[Django]

#36149: Composite primary key subquery lookup prevent usage or specify fields and
are not implemented for exact
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* needs_better_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/36149#comment:4>

[Django]

#36149: Composite primary key subquery lookup prevent usage or specify fields and
are not implemented for exact
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution:

Keywords: | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/36149#comment:5>

[Django]

#36149: Composite primary key subquery lookup prevent usage or specify fields and
are not implemented for exact
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
| Charette

Type: Bug | Status: closed

Component: Database layer | Version: 5.2
(models, ORM) |

Severity: Release blocker | Resolution: fixed

Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce <42296566+sarahboyce@…>):

* resolution: => fixed
* status: assigned => closed

Comment:

In [changeset:"41239fe34d64e801212dccaa4585e4802d0fac68" 41239fe3]:
{{{#!CommitTicketReference repository=""
revision="41239fe34d64e801212dccaa4585e4802d0fac68"
Fixed #36149 -- Allowed subquery values against tuple exact and in
lookups.

Non-tuple exact and in lookups have specialized logic for subqueries that
can
be adapted to properly assign select mask if unspecified and ensure the
number
of involved members are matching on both side of the operator.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36149#comment:6>

[Django]

#36149: Composite primary key subquery lookup prevent usage or specify fields and
are not implemented for exact
-------------------------------------+-------------------------------------
Reporter: Simon Charette | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Sarah Boyce <42296566+sarahboyce@…>):

In [changeset:"dc1c9b4ddd3ca64279da7c97b5023fbedf2340e2" dc1c9b4]:
{{{#!CommitTicketReference repository=""
revision="dc1c9b4ddd3ca64279da7c97b5023fbedf2340e2"
[5.2.x] Fixed #36149 -- Allowed subquery values against tuple exact and in

lookups.

Non-tuple exact and in lookups have specialized logic for subqueries that
can
be adapted to properly assign select mask if unspecified and ensure the
number
of involved members are matching on both side of the operator.

Backport of 41239fe34d64e801212dccaa4585e4802d0fac68 from main.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36149#comment:7>