"Unknown column" error due to missing JOIN when using Coalesce as part of a FilteredRelation's condition

[Daniel Schaffer]

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.