"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.

            job_worker_preference=FilteredRelation(
                relation_name="company__worker_preferences",
                condition=Q(
                    company__worker_preferences__worker=Coalesce(F("worker"), F("worker_substitutions__worker")),
                    company__worker_preferences__company=F("company"),
                )
            ),
            is_allowed=Case(When(job_worker_preference__allow_assignments=True, then=1), default=0, output_field=BooleanField())

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

            actual_worker=Coalesce(F("worker"), F("worker_substitutions__worker")),
            job_worker_preference=FilteredRelation(
                relation_name="company__worker_preferences",
                condition=Q(
                    company__worker_preferences__worker=F("actual_worker"),
                    company__worker_preferences__company=F("company"),
                )
            ),
            is_allowed=Case(When(job_worker_preference__allow_assignments=True, then=1), default=0, output_field=BooleanField())

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.

I've reproduced the issue in this repro: ​https://github.com/DanielSchaffer/django_filtered_relation_coalesce_repro.

django_filtered_relation_coalesce_repro/test/test_job_manager.py contains a failing test that reproduces the issue, and a passing test that demonstrates the workaround.

[Simon Charette]

Thank you for your report, please provide a minimal set of models that reproduce the issue and the resulting traceback and query if possible.

That'll make the job of volunteers trying to validate your report way easier.

[Daniel Schaffer]

Sure, I've updated the ticket with a link to a repro repo