#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
-------------------------------------+-------------------------------------
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 - 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.
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 - 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.
--
Comment (by Daniel Schaffer):
Sure, I've updated the ticket with a link to a repro repo
--
Ticket URL: <https://code.djangoproject.com/ticket/33766#comment:2>
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/010701812c2a39b1-5bd2d2ec-a803-42dc-9e11-9cd059e744c0-000000%40eu-central-1.amazonses.com.
