[Django] #32636: QuerySet.values()/values_list() crashes on a combined queryset ordered by "extra" select.
Django
"extra" select.
-------------------------------------+-------------------------------------
Reporter: Mariusz | Owner: nobody
Felisiak |
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) | Keywords: queryset combined
Severity: Normal | union difference intersection extra
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
`QuerySet.values()`/`values_list()` crashed on a combined queryset ordered
by "extra" select, e.g.
{{{
def
test_union_multiple_models_with_values_list_and_order_by_extra_select_different_fields(self):
from .models import Celebrity
reserved_name = ReservedName.objects.create(name='rn1', order=0)
celebrity = Celebrity.objects.create(name='John Doe')
qs1 = Celebrity.objects.extra(select={'extra_name':
'greatest_fan_id'})
qs2 = ReservedName.objects.extra(select={'extra_name': 'name'})
self.assertSequenceEqual(
qs1.union(qs2).order_by('extra_name').values_list('pk',
flat=True),
[reserved_name.pk, celebrity.pk],
)
}}}
tries to execute:
{{{
SELECT "queries_celebrity"."id", (greatest_fan_id) AS "__orderbycol2" FROM
"queries_celebrity"
UNION
SELECT "queries_reservedname"."id", (greatest_fan_id) AS "__orderbycol2"
FROM "queries_reservedname"
ORDER BY (2)
}}}
and crashes with:
{{{
======================================================================
ERROR:
test_union_multiple_models_with_values_list_and_order_by_extra_select_different_fields
(queries.test_qs_combinators.QuerySetSetOperationTests)
----------------------------------------------------------------------
Traceback (most recent call last):
File "django/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
File "django/django/db/backends/sqlite3/base.py", line 416, in execute
return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: no such column: greatest_fan_id
}}}
It's related with #32627 but it's not a regression in
464a4c0c59277056b5d3c1132ac1b4c6085aee08. It crashes in Django 3.1.X with:
{{{
File "django/django/db/models/sql/query.py", line 1912, in add_fields
join_info = self.setup_joins(name.split(LOOKUP_SEP), opts, alias,
allow_many=allow_m2m)
AttributeError: 'NoneType' object has no attribute 'split'
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32636>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
"extra" select.
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: queryset combined | Triage Stage: Accepted
union difference intersection |
extra |
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
Comment:
OK. The idea being that the `extra` should come from the respective
queryset, not the first one.
Do it the other way around and the test passes `qs2.union(qs1)...`
(Because Celebrity has a `name`, I suppose).
--
Ticket URL: <https://code.djangoproject.com/ticket/32636#comment:1>
Django
"extra" select.
-------------------------------------+-------------------------------------
| Wobrock
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: queryset combined | Triage Stage: Accepted
union difference intersection |
extra |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: David Wobrock (added)
* owner: nobody => David Wobrock
* has_patch: 0 => 1
* status: new => assigned
Comment:
[https://github.com/django/django/pull/14280 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/32636#comment:2>
Django
"extra" select.
-------------------------------------+-------------------------------------
| Wobrock
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: queryset combined | Triage Stage: Accepted
union difference intersection |
extra |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 0 => 1
Comment:
It would seem that postgres is smarter than others in this case.
Even with correctly generated SQL, the suggest test method in the ticket
[https://djangoci.com/job/pull-requests-bionic/database=postgres,label
=bionic-
pr,python=python3.9/11241/testReport/junit/queries.test_qs_combinators/QuerySetSetOperationTests/test_union_multiple_models_with_values_and_order_by_extra_select_different_fields/
description fails] with:
{{{
UNION types integer and character varying cannot be matched
}}}
since 'name' and 'greatest_fan_id' do not have a matching type.
--
Ticket URL: <https://code.djangoproject.com/ticket/32636#comment:3>
Django
"extra" select.
-------------------------------------+-------------------------------------
| Wobrock
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: queryset combined | Triage Stage: Accepted
union difference intersection |
extra |
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham):
Is there a special reason for fixing this rather than adhering to the
[https://docs.djangoproject.com/en/dev/ref/models/querysets/#extra
documented policy for QuerySet.extra()]?
This is an old API that we aim to deprecate at some point in the future.
Use it only if you cannot express your query using other queryset methods.
If you do need to use it, please file a ticket using the QuerySet.extra
keyword with your use case (please check the list of existing tickets
first) so that we can enhance the QuerySet API to allow removing extra().
We are no longer improving or fixing bugs for this method.
--
Ticket URL: <https://code.djangoproject.com/ticket/32636#comment:4>
Django
"extra" select.
-------------------------------------+-------------------------------------
| Wobrock
Type: Bug | Status: assigned
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: queryset combined | Triage Stage: Accepted
union difference intersection |
extra |
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* needs_better_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/32636#comment:5>
Django
"extra" select.
-------------------------------------+-------------------------------------
| Wobrock
Component: Database layer | Version: 3.2
(models, ORM) |
Keywords: queryset combined | Triage Stage: Accepted
union difference intersection |
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* keywords: queryset combined union difference intersection extra =>
queryset combined union difference intersection QuerySet.extra
* status: assigned => closed
* resolution: => wontfix
Comment:
Replying to [comment:4 Tim Graham]:
> Is there a special reason for fixing this rather than adhering to the
[https://docs.djangoproject.com/en/dev/ref/models/querysets/#extra
documented policy for QuerySet.extra()]?
>
> This is an old API that we aim to deprecate at some point in the
future. Use it only if you cannot express your query using other queryset
methods. If you do need to use it, please file a ticket using the
QuerySet.extra keyword with your use case (please check the list of
existing tickets first) so that we can enhance the QuerySet API to allow
removing extra(). We are no longer improving or fixing bugs for this
method.
Agreed, let's not create a precedent.
--
Ticket URL: <https://code.djangoproject.com/ticket/32636#comment:6>