[Django] #33766: "Unknown column" error due to missing JOIN when using Coalesce as part of a FilteredRelation's condition
Django
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.
Django
FilteredRelation's condition
-------------------------------------+-------------------------------------
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
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/33766#comment:1>
Django
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:
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
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.
>
> {{{
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33766#comment:3>
Django
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.
New description:
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>