Fixed #36464 -- Fixed "__in" tuple lookup on backends lacking native support.

When native support for tuple lookups is missing in a DB backend, it can
be emulated with an EXISTS clause. This is controlled by the backend
feature flag "supports_tuple_lookups".

The mishandling of subquery right-hand side in `TupleIn` (added to
support `CompositePrimaryKey` in Refs #373) was likely missed because
the only core backend we test with the feature flag disabled
(Oracle < 23.4) supports it natively.

Thanks to Nandana Raol for the report, and to Sarah Boyce, Jacob Walls,
and Natalia Bidart for reviews.

1 files changed, 18 insertions, 3 deletions

diff --git a/django/db/models/fields/tuple_lookups.py b/django/db/models/fields/tuple_lookups.py
index 6a4e322589..62818a37c4 100644
--- a/django/db/models/fields/tuple_lookups.py
+++ b/django/db/models/fields/tuple_lookups.py
@@ -1,9 +1,10 @@
 import itertools
 
 from django.core.exceptions import EmptyResultSet
-from django.db.models import Field
+from django.db import models
 from django.db.models.expressions import (
     ColPairs,
+    Exists,
     Func,
     ResolvedOuterRef,
     Subquery,
@@ -25,7 +26,7 @@ from django.db.models.sql.where import AND, OR, WhereNode
 class Tuple(Func):
     allows_composite_expressions = True
     function = ""
-    output_field = Field()
+    output_field = models.Field()
 
     def __len__(self):
         return len(self.source_expressions)
@@ -351,7 +352,21 @@ class TupleIn(TupleLookupMixin, In):
         rhs = self.rhs
         if not rhs:
             raise EmptyResultSet
-        if not self.rhs_is_direct_value():
+        if isinstance(rhs, Query):
+            rhs_exprs = itertools.chain.from_iterable(
+                (
+                    select_expr
+                    if isinstance((select_expr := select[0]), ColPairs)
+                    else [select_expr]
+                )
+                for select in rhs.get_compiler(connection=connection).get_select()[0]
+            )
+            rhs = rhs.clone()
+            rhs.add_q(
+                models.Q(*[Exact(col, val) for col, val in zip(self.lhs, rhs_exprs)])
+            )
+            return compiler.compile(Exists(rhs))
+        elif not self.rhs_is_direct_value():
             return super(TupleLookupMixin, self).as_sql(compiler, connection)
 
         # e.g.: (a, b, c) in [(x1, y1, z1), (x2, y2, z2)] as SQL: