Composite primary key subquery lookup prevent usage or specify fields and are not implemented for exact

[Simon Charette]

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

tests/composite_pk/models/tenant.py

@@ -44 +44 @@
         related_name="comments",
     )
     text = models.TextField(default="", blank=True)
+    integer = models.IntegerField(default=0)
 
 
 class Post(models.Model):

tests/composite_pk/test_filter.py

@@ -182 +182 @@
                     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 ​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

tests/composite_pk/test_filter.py

@@ -450 +474 @@
         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.

[Sarah Boyce]

Thank you!

[Sarah Boyce <42296566+sarahboyce@…>]

In 41239fe3:

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.

[Sarah Boyce <42296566+sarahboyce@…>]

In dc1c9b4:

[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.