Unnecessary qoutes around table name with in subquery

[Bálint Balina]

I am using a combination of subquery with filtered relations, and i receive the error: missing FROM-clause entry for table "table alias"

    from django.db.models import FilteredRelation, Q, OuterRef, Subquery
    from django.db.models.functions import Coalesce
    
    qs = models.Product.objects.annotate(
        profile1=FilteredRelation('product_profiles', condition=Q(product_profiles__id=1)),
        profile2=FilteredRelation('product_profiles', condition=Q(product_profiles__id=2)),
        category_name=Subquery(
            models.ProductCategoryRelationship.objects.filter(
                product_profile=Coalesce(OuterRef('profile1__id'), OuterRef('profile2__id'))
            ).values('category__name')[:1]
        ),
    ).values('id', 'category_name')

The above expression generates the below query

SELECT "product"."id",
       (SELECT U2."name"
        FROM "product_category_relationship" U0
                 INNER JOIN "product_category" U2 ON (U0."category_id" = U2."id")
        WHERE U0."product_profile_id" = (COALESCE("profile1"."id", "T3"."id"))) AS "x"
FROM "product"
         LEFT OUTER JOIN "product_profile" profile1 ON ("product"."id" = profile1."product_id" AND (profile1."id" = 1))
         LEFT OUTER JOIN "product_profile" T3 ON ("product"."id" = T3."product_id" AND (T3."id" = 2))

where in the subquery's coalesce part there is "T3"."id" instead of T3."id".

I have drilled down the problem somewhat, and the problematic code is this:

    # django.db.models.sql.compiler.py::383
    
    def quote_name_unless_alias(self, name):
        """
        A wrapper around connection.ops.quote_name that doesn't quote aliases
        for table names. This avoids problems with some SQL dialects that treat
        quoted strings specially (e.g. PostgreSQL).
        """
        if name in self.quote_cache:
            return self.quote_cache[name]
        if ((name in self.query.alias_map and name not in self.query.table_map) or
                name in self.query.extra_select or (
                    name in self.query.external_aliases and name not in self.query.table_map)):
            self.quote_cache[name] = name
            return name
        r = self.connection.ops.quote_name(name)
        self.quote_cache[name] = r
        return r

There is that complex condition about the alias being a table name or not, which can't see the outer query's aliases.

ipdb> self.query.alias_map
OrderedDict([('U0', <django.db.models.sql.datastructures.BaseTable object at 0x7fac19d66e80>), ('U1', <django.db.models.sql.datastructures.Join object at 0x7fac19d4d100>), ('U2', <django.db.models.sql.datastructures.Join object at 0x7fac19dc07f0>)])

ipdb> self.query.table_map
{'product_category_relationship': ['U0'], 'product_profile': ['U1'], 'product_category': ['U2']}

ipdb> self.query.extra_select
{}

ipdb> self.query.external_aliases
set()

[Mariusz Felisiak]

Thanks for the detailed report, it's a duplicate of #29214 (see also #30009).