Repository: django/django
File: tests/queries/test_qs_combinators.py (Python)

Code before:
            ReservedName.objects.annotate(
                number=Subquery(qs1.union(qs2).values("num")),
            )
            .filter(number__isnull=False)
            .values_list("order", flat=True),
            [8, 1],
        )

    def test_union_in_subquery_related_outerref(self):
        e1 = ExtraInfo.objects.create(value=7, info="e3")
        e2 = ExtraInfo.objects.create(value=5, info="e2")
        e3 = ExtraInfo.objects.create(value=1, info="e1")
        Author.objects.bulk_create(
            [
                Author(name="a1", num=1, extra=e1),
                Author(name="a2", num=3, extra=e2),
                Author(name="a3", num=2, extra=e3),
            ]
        )
        qs1 = ExtraInfo.objects.order_by().filter(value=OuterRef("num"))
        qs2 = ExtraInfo.objects.order_by().filter(value__lt=OuterRef("extra__value"))
        qs = (
            Author.objects.annotate(
                info=Subquery(qs1.union(qs2).values("info")[:1]),
            )
            .filter(info__isnull=False)
            .values_list("name", flat=True)
        )
        self.assertCountEqual(qs, ["a1", "a2"])
        # Combined queries don't mutate.
        self.assertCountEqual(qs, ["a1", "a2"])

    @skipUnlessDBFeature("supports_slicing_ordering_in_compound")
    def test_union_in_with_ordering(self):
        qs1 = Number.objects.filter(num__gt=7).order_by("num")
        qs2 = Number.objects.filter(num__lt=2).order_by("num")
        self.assertNumbersEqual(
            Number.objects.exclude(id__in=qs1.union(qs2).values("id")),
            [2, 3, 4, 5, 6, 7],
            ordered=False,
        )

    @skipUnlessDBFeature(
        "supports_slicing_ordering_in_compound", "allow_sliced_subqueries_with_in"
    )
    def test_union_in_with_ordering_and_slice(self):
        qs1 = Number.objects.filter(num__gt=7).order_by("num")[:1]
        qs2 = Number.objects.filter(num__lt=2).order_by("-num")[:1]
        self.assertNumbersEqual(
            Number.objects.exclude(id__in=qs1.union(qs2).values("id")),
            [0, 2, 3, 4, 5, 6, 7, 9],

Review comment (bug):
Previously the SQL would be

```sql
SELECT *
FROM "queries_note"
WHERE EXISTS
    (SELECT 1 AS "a"
     FROM "queries_annotation" V0
     INNER JOIN "queries_annotation_notes" V2 ON (V0."id" = V2."annotation_id")
     WHERE (EXISTS
              (SELECT U0."id"
               FROM "queries_tag" U0
               WHERE U0."id" = ("queries_annotation"."tag_id") INTERSECT
                 SELECT U0."id"
                 FROM "queries_tag" U0 WHERE U0."id" = ("queries_note"."tag_id")
               LIMIT 1)
            AND V2."note_id" IN ("queries_note"."id"))
     LIMIT 1)
```

Which would crash because of the `U0."id" = ("queries_annotation"."tag_id")` part.

This was due to the `tags.filter(id=OuterRef("tag_id"))` part resolving to `"queries_annotation"."tag_id"` before `"queries_annotation"` was re-labelled to `V0` when the `Exists(Annotation...)` annotation was resolved in the `Note.objects.filter` clause.

The changes to `sql.Query.change_aliases` make sure that when this happens the re-label of `{"queries_annotation": "V0"}` percolate all the way to `tags` in order to replace the `WHERE` clause to `U0."id" = ("V0"."tag_id")`.

Code after:
            [
                ReservedName(name="rn1", order=8),
                ReservedName(name="rn2", order=1),
                ReservedName(name="rn3", order=5),
            ]
        )
        qs1 = Number.objects.filter(num__gt=7, num=OuterRef("order"))
        qs2 = Number.objects.filter(num__lt=2, num=OuterRef("order"))
        self.assertCountEqual(
            ReservedName.objects.annotate(
                number=Subquery(qs1.union(qs2).values("num")),
            )
            .filter(number__isnull=False)
            .values_list("order", flat=True),
            [8, 1],
        )

    @skipUnlessDBFeature("supports_select_intersection")
    def test_intersection_in_nested_subquery(self):
        tag = Tag.objects.create(name="tag")
        note = Note.objects.create(tag=tag)
        annotation = Annotation.objects.create(tag=tag)
        tags = Tag.objects.order_by()
        tags = tags.filter(id=OuterRef("tag_id")).intersection(
            tags.filter(id=OuterRef(OuterRef("tag_id")))
        )
        qs = Note.objects.filter(
            Exists(
                Annotation.objects.filter(
                    Exists(tags),
                    notes__in=OuterRef("pk"),
                )
            )
        )
        self.assertIsNone(qs.first())
        annotation.notes.add(note)
        self.assertEqual(qs.first(), note)

    def test_union_in_subquery_related_outerref(self):
        e1 = ExtraInfo.objects.create(value=7, info="e3")
        e2 = ExtraInfo.objects.create(value=5, info="e2")
        e3 = ExtraInfo.objects.create(value=1, info="e1")
        Author.objects.bulk_create(
            [
                Author(name="a1", num=1, extra=e1),
                Author(name="a2", num=3, extra=e2),
                Author(name="a3", num=2, extra=e3),
            ]
        )
        qs1 = ExtraInfo.objects.order_by().filter(value=OuterRef("num"))
        qs2 = ExtraInfo.objects.order_by().filter(value__lt=OuterRef("extra__value"))