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
33766	"""Unknown column"" error due to missing JOIN when using Coalesce as part of a FilteredRelation's condition"	Daniel Schaffer	nobody	"When using the `Coalesce` function as part of the condition of a `FilteredRelation`, the query fails with an ""Unknown column"" error if any of the fields referenced by `Coalesce` requires a JOIN. This appears to be due to the JOIN not actually getting added to the query - the error references the column with a `T##` prefix, but when inspecting the generated query, there is no JOIN statement to a matching table or alias.

{{{
Jobs.objects.annotate(
    worker_preference=FilteredRelation(
        relation_name=""company__workerpreference"",
        condition=Q(
            company__workerpreference__worker=Coalesce(F(""worker""), F(""substitute__worker"")),
            company__workerpreference__company=F(""company""),
        )
    )
}}}

This can be worked around by creating a separate annotation for the result of the `Coalesce` function:

{{{
Jobs.objects.annotate(
    actual_worker=Coalesce(F(""worker""), F(""substitute__worker"")),
    worker_preference=FilteredRelation(
        relation_name=""company__workerpreference"",
        condition=Q(
            company__workerpreference__worker=F(""actual_worker""),
            company__workerpreference__company=F(""company""),
        )
    )
}}}

However, I suspect there may be an underlying issue with how JOINs are detected and added to a query when there are nested field references like this."	Bug	new	Database layer (models, ORM)	3.2	Normal		filteredrelation coalesce		Unreviewed	0	0	0	0	0	0