#33766: "Unknown column" error due to missing JOIN when using Coalesce as part
of a
FilteredRelation's condition
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
DanielSchaffer |
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) | Keywords: filteredrelation
Severity: Normal | coalesce
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/33766>
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/010701812b289a17-0eb88da5-c299-4317-bda2-8d648ad5eff6-000000%40eu-central-1.amazonses.com.
