id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
32839	Unnecessary qoutes around table name with in subquery	Bálint Balina	nobody	"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()

}}}"	Bug	closed	Database layer (models, ORM)	3.2	Normal	duplicate	Subquery, annotate, FilteredRelation, OuterRef		Unreviewed	0	0	0	0	0	0