Opened 3 years ago
Last modified 23 months ago
#33766 closed Bug
"Unknown column" error due to missing JOIN when using Coalesce as part of a FilteredRelation's condition — at Version 4
Description (last modified by ) ¶
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`))))
Change History (4)
comment:1 by , 3 years ago
comment:2 by , 3 years ago
Description: | modified (diff) |
---|
Sure, I've updated the ticket with a link to a repro repo
comment:3 by , 3 years ago
Description: | modified (diff) |
---|
comment:4 by , 3 years ago
Description: | modified (diff) |
---|
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.