#34771: order_by on annotated field that's not present in values/values_list
causes
SQL syntax error
-------------------------------------+-------------------------------------
Reporter: Yitao Xiong | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 3.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: mysql | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Yitao Xiong):
Thanks for your reply Natalia!
I tried this with `main` and it behaved the same:
{{{
In [5]: User.objects.annotate(random_stuff=Value(False,
output_field=BooleanField())).values('id').order_by('random_stuff')
....
ProgrammingError: (1064, "You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax
to use near 'bool) ASC LIMIT 21' at line 1")
In [6]: print(User.objects.annotate(random_stuff=Value(False,
output_field=BooleanField())).values('id').order_by('random_stuff').query)
SELECT `auth_user`.`id` FROM `auth_user` ORDER BY CAST(False AS bool) ASC
In [7]: from django import VERSION
In [8]: VERSION
Out[8]: (5, 0, 0, 'alpha', 0)
In [9]:
}}}
For our specific scenario that caused this error, it wasn't really an
intentional skipping on the annotated fields, it's more like this (it also
wasn't the User model at all, using it to avoid exposing our business
logics, apologize if that added confusions):
{{{
# Some generic queryset pre-assembling with annotations
base_queryset =
User.objects.annotate(random_field...).order_by('random_field')
...
# Later in a specific path, needing some extra information from the
base_queryset
additional_lookup = base_queryset.filter(...).values('id', 'email')
}}}
So it's more like the field was annotated first, and developer just didn't
care about it when later reusing the queryset. Using my example, this
query with the `annotate` and `values` changed orders will produce the
same error:
{{{
User.objects.annotate(random_stuff=Value(False,
output_field=BooleanField())).order_by('random_stuff').values('id')
...
ProgrammingError: (1064, "You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax
to use near 'bool) ASC LIMIT 21' at line 1")
}}}
To real-life use cases, I can see some cases when you want the rows
returned in a specific order, but doesn't care about the value it's been
ordered by.
A user-login log table can be an example - you want the last 10 logged in
users, but you don't really care about when they logged in. If we specific
are looking for order with annotations, maybe an API usage log table would
be a good example? You wanna see the 10 users who used the API last so you
annotate with their max access time and order by it, without needing the
actual values.
--
Ticket URL: <https://code.djangoproject.com/ticket/34771#comment:4>
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/01070189e5b466bb-1826f92a-e69f-40fb-91c4-6c51e68263e7-000000%40eu-central-1.amazonses.com.
