#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 django-updates+unsubscr...@googlegroups.com.
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.
