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.

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

Here's the stringified representation of the query - note the missing `JOIN` to `django_filtered_relation_coalesce_repro_workersubstitution`, even though it's referenced in the `COALESCE` expression:

{{{
SELECT
  `django_filtered_relation_coalesce_repro_job`.`id`,
  `django_filtered_relation_coalesce_repro_job`.`company_id`,
  `django_filtered_relation_coalesce_repro_job`.`worker_id`,
  CASE WHEN job_worker_preference.`allow_assignments` THEN 1 ELSE 0 END AS `is_allowed`
FROM `django_filtered_relation_coalesce_repro_job`
INNER JOIN `django_filtered_relation_coalesce_repro_company`
ON (`django_filtered_relation_coalesce_repro_job`.`company_id` = `django_filtered_relation_coalesce_repro_company`.`id`)
LEFT OUTER JOIN `django_filtered_relation_coalesce_repro_workerpreference` job_worker_preference
ON (`django_filtered_relation_coalesce_repro_company`.`id` = job_worker_preference.`company_id` AND
    ((job_worker_preference.`company_id` = `django_filtered_relation_coalesce_repro_job`.`company_id` AND
      job_worker_preference.`worker_id` = COALESCE(`django_filtered_relation_coalesce_repro_job`.`worker_id`,
                                                   `django_filtered_relation_coalesce_repro_workersubstitution`.`worker_id`))))

}}}"	Bug	new	Database layer (models, ORM)	3.2	Normal		filteredrelation coalesce		Unreviewed	0	0	0	0	0	0