OuterRef in exclude() or ~Q() uses wrong model.

[Oskar Persson]

The following test (added to tests/queries/test_qs_combinators) fails when trying to exclude results using OuterRef()

def test_exists_exclude(self):
    # filter()
    qs = Number.objects.annotate(
        foo=Exists(
            Item.objects.filter(tags__category_id=OuterRef('pk'))
        )
    ).filter(foo=True)
    print(qs)  # works

    # exclude()
    qs = Number.objects.annotate(
        foo =Exists(
            Item.objects.exclude(tags__category_id=OuterRef('pk'))
        )
    ).filter(foo=True)
    print(qs)  # crashes

    # filter(~Q())
    qs = Number.objects.annotate(
        foo =Exists(
            Item.objects.filter(~Q(tags__category_id=OuterRef('pk')))
        )
    ).filter(foo=True)
    print(qs)  # crashes

It results in the following error

ValueError: This queryset contains a reference to an outer query and may only be used in a subquery

[Mariusz Felisiak]

Thanks for the report. ValueError was fixed in 35431298226165986ad07e91f9d3aca721ff38ec. Provided test fails on master with

django.db.utils.ProgrammingError: missing FROM-clause entry for table "V0"
LINE 1: ...(U1."tag_id" = U2."id") WHERE U2."category_id" = ("V0"."id")...

It looks that OuterRef resolves to "V0"."id" instead of "queries_number"."id"

SELECT ...
FROM "queries_number" 
WHERE EXISTS(
    SELECT ...
    FROM "queries_item" V0
    WHERE NOT (V0."id" IN (
        SELECT U1."item_id"
        FROM "queries_item_tags" U1 INNER JOIN "queries_tag" U2 ON (U1."tag_id" = U2."id")
        WHERE U2."category_id" = "V0"."id"
    ))

Issue can be reproduce also without annotation

Number.objects.filter(Exists(Item.objects.exclude(tags__category_id=OuterRef('pk'))))

[Simon Charette]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In 13a8884a:

Fixed #30739 -- Fixed exclusion of multi-valued lookup against outer rhs.

OuterRef right hand sides have to be nested, just like F rhs have to,
during the subquery pushdown split_exclude performs to ensure they are
resolved against the outer query aliases.