#33766: "Unknown column" error due to missing JOIN when using Coalesce as part
of a
FilteredRelation's condition
-------------------------------------+-------------------------------------
Reporter: Daniel Schaffer | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: filteredrelation | Triage Stage:
coalesce | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Daniel Schaffer:
Old description:
> 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.
New description:
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`))))
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33766#comment:4>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/010701812c2f65cd-ad4afee0-4090-4509-8fc2-45ebff1bca41-000000%40eu-central-1.amazonses.com.
