Repository: django/django
File: tests/expressions/tests.py (Python)

Code before:
        self.assertNotEqual(
            qs.query.annotations["is_ceo"],
            qs.query.annotations["small_company"],
        )

    def test_subquery_sql(self):
        employees = Employee.objects.all()
        employees_subquery = Subquery(employees)
        self.assertIs(employees_subquery.query.subquery, True)
        self.assertIs(employees.query.subquery, False)
        compiler = employees_subquery.query.get_compiler(connection=connection)
        sql, _ = employees_subquery.as_sql(compiler, connection)
        self.assertIn("(SELECT ", sql)

    def test_in_subquery(self):
        # This is a contrived test (and you really wouldn't write this query),
        # but it is a succinct way to test the __in=Subquery() construct.
        small_companies = Company.objects.filter(num_employees__lt=200).values("pk")
        subquery_test = Company.objects.filter(pk__in=Subquery(small_companies))
        self.assertCountEqual(subquery_test, [self.foobar_ltd, self.gmbh])
        subquery_test2 = Company.objects.filter(
            pk=Subquery(small_companies.filter(num_employees=3))
        )
        self.assertCountEqual(subquery_test2, [self.foobar_ltd])

    def test_uuid_pk_subquery(self):
        u = UUIDPK.objects.create()
        UUID.objects.create(uuid_fk=u)
        qs = UUIDPK.objects.filter(id__in=Subquery(UUID.objects.values("uuid_fk__id")))
        self.assertCountEqual(qs, [u])

    def test_nested_subquery(self):
        inner = Company.objects.filter(point_of_contact=OuterRef("pk"))
        outer = Employee.objects.annotate(is_point_of_contact=Exists(inner))
        contrived = Employee.objects.annotate(
            is_point_of_contact=Subquery(
                outer.filter(pk=OuterRef("pk")).values("is_point_of_contact"),
                output_field=BooleanField(),
            ),
        )
        self.assertCountEqual(contrived.values_list(), outer.values_list())

    def test_nested_subquery_join_outer_ref(self):
        inner = Employee.objects.filter(pk=OuterRef("ceo__pk")).values("pk")
        qs = Employee.objects.annotate(
            ceo_company=Subquery(
                Company.objects.filter(
                    ceo__in=inner,
                    ceo__pk=OuterRef("pk"),
                ).values("pk"),
            ),

Review comment (bug):
I looked into doing something similar for JSONField, but it fails pretty badly, so this might be a follow-up ticket:

Rough test:
```diff
diff --git a/tests/model_fields/test_jsonfield.py b/tests/model_fields/test_jsonfield.py
index 5a9cf9ad7a..32471d910e 100644
--- a/tests/model_fields/test_jsonfield.py
+++ b/tests/model_fields/test_jsonfield.py
@@ -925,6 +925,20 @@ class TestQuerying(TestCase):
             self.objs[3:5],
         )
 
+    @skipUnlessDBFeature("supports_json_field_contains")
+    def test_lookups_subquery(self):
+        qs = NullableJSONModel.objects.values("value__foo")[:1]
+        for lookup in JSONField.get_lookups():
+            with self.subTest(lookup=lookup):
+                if lookup == "isnull":
+                    continue  # not allowed, rhs must be a literal boolean.
+                if lookup in {"has_keys", "has_any_keys"}:
+                    rhs = [Subquery(qs)]
+                else:
+                    rhs = Subquery(qs)
+                qs = NullableJSONModel.objects.filter(**{f"value__foo__{lookup}": rhs})
+                self.assertGreaterEqual(len(qs), 0)
+
     @skipUnlessDBFeature("supports_json_field_contains")
     def test_array_key_contains(self):
         tests = [
```
***
```
  File "/usr/local/lib/python3.12/site-packages/psycopg/cursor.py", line 97, in execute
    raise ex.with_traceback(None)
django.db.utils.ProgrammingError: the query has 5 placeholders but 19 parameters were passed
```


Code after:
        self.assertNotEqual(
            qs.query.annotations["is_ceo"],
            qs.query.annotations["small_company"],
        )

    def test_subquery_sql(self):
        employees = Employee.objects.all()
        employees_subquery = Subquery(employees)
        self.assertIs(employees_subquery.query.subquery, True)
        self.assertIs(employees.query.subquery, False)
        compiler = employees_subquery.query.get_compiler(connection=connection)
        sql, _ = employees_subquery.as_sql(compiler, connection)
        self.assertIn("(SELECT ", sql)

    def test_in_subquery(self):
        # This is a contrived test (and you really wouldn't write this query),
        # but it is a succinct way to test the __in=Subquery() construct.
        small_companies = Company.objects.filter(num_employees__lt=200).values("pk")
        subquery_test = Company.objects.filter(pk__in=Subquery(small_companies))
        self.assertCountEqual(subquery_test, [self.foobar_ltd, self.gmbh])
        subquery_test2 = Company.objects.filter(
            pk=Subquery(small_companies.filter(num_employees=3))
        )
        self.assertCountEqual(subquery_test2, [self.foobar_ltd])

    def test_lookups_subquery(self):
        smallest_company = Company.objects.order_by("num_employees").values("name")[:1]
        for lookup in CharField.get_lookups():
            if lookup == "isnull":
                continue  # not allowed, rhs must be a literal boolean.
            if (
                lookup == "in"
                and not connection.features.allow_sliced_subqueries_with_in
            ):
                continue
            if lookup == "range":
                rhs = (Subquery(smallest_company), Subquery(smallest_company))
            else:
                rhs = Subquery(smallest_company)
            with self.subTest(lookup=lookup):
                qs = Company.objects.filter(**{f"name__{lookup}": rhs})
                self.assertGreater(len(qs), 0)

    def test_uuid_pk_subquery(self):
        u = UUIDPK.objects.create()
        UUID.objects.create(uuid_fk=u)
        qs = UUIDPK.objects.filter(id__in=Subquery(UUID.objects.values("uuid_fk__id")))
        self.assertCountEqual(qs, [u])

    def test_nested_subquery(self):
        inner = Company.objects.filter(point_of_contact=OuterRef("pk"))