Opened 9 years ago
Closed 5 years ago
#26192 closed Bug (fixed)
Cannot order query by constant value on PostgreSQL
Reported by: | Sven R. Kunze | Owned by: | Mariusz Felisiak |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | |
Cc: | Can Sarıgöl | Triage Stage: | Ready for checkin |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
MyModel.objects.annotate(my_column=Value('asdf')).order_by('my_column').values_list('id') ProgrammingError: non-integer constant in ORDER BY LINE 1: ...odel"."id" FROM "mymodel" ORDER BY 'asdf' ASC...
Does it qualify as a bug this time? ;-)
Change History (20)
comment:1 by , 9 years ago
Description: | modified (diff) |
---|
comment:2 by , 9 years ago
Summary: | Same as #26185 but without extra but with annotate → Same as #26185 but with annotate and without extra |
---|
comment:3 by , 9 years ago
Resolution: | → invalid |
---|---|
Status: | new → closed |
Summary: | Same as #26185 but with annotate and without extra → Cannot order query by constant value |
comment:4 by , 9 years ago
Resolution: | invalid |
---|---|
Status: | closed → new |
I don't see what the use of ordering by a constant string value is.
Reducing code complexity (e.g. fewer ifs).
The code overview from #26192:
# 1 create complex queryset ... more code # 2 annotate and add extra where ... more code # 3 add order (maybe referring to the extra column) ... more code # 4 wrap paginator around queryset ... more code # 5 create values_list of paged queryset ... more code # 6 evaluate <<<< crash
The code above spread over several files and few thousand lines builds up a quite complex query.
Each point contribute to the final queryset (actually more than one queryset). In order to reduce coupling and code complexity, adding a constant column make things straightforward (otherwise we would need to check if the column was added).
it looks like this is a database limitation.
Are you sure? From what I know of SQL, it's possible to order by column name (a string) or by column index (a number). Django just creates invalid SQL.
Why does Django not refer the column order_by by name (or index) and instead inserts a value?
comment:5 by , 9 years ago
Sorry for misunderstanding, however, I cannot reproduce a crash on the stable/1.8.x
branch:
from django.db.models import Value from polls.models import Question Question.objects.annotate(my_column=Value('asdf')).order_by('my_column').values_list('id') [(1,), (2,), (3,), (4,), (5,), (6,), (7,), (8,), (9,), (10,)]
Tested on SQLite, PostgreSQL, and MySQL.
Can you provide more details to reproduce?
comment:6 by , 9 years ago
Interesting. We upgraded to 1.8.9 and use PostgreSQL 9.3.10. Our testsuite runs without errors.
Can you provide your query string? I would be interesting in how the order clause looks.
Ours:
SELECT "mymodel"."id" FROM "app_mymodel" ORDER BY 'asdf' ASC
comment:7 by , 9 years ago
str(Question.objects.annotate(my_column=Value('asdf')).order_by('my_column').values_list('id').query) 'SELECT "polls_question"."id" FROM "polls_question" ORDER BY asdf ASC'
PostgreSQL 9.5.0 and psycopg2 2.6.1 here.
comment:8 by , 9 years ago
psycopg2 2.5.1
psql:
=# SELECT "mymodel"."id" FROM "mymodel" ORDER BY 'asdf' ASC; ERROR: non-integer constant in ORDER BY LINE 1: ...odel"."id" FROM "mymodel" ORDER BY 'asdf' ASC... ^ =# SELECT "mymodel"."id" FROM "mymodel" ORDER BY "asdf" ASC; ERROR: column "asdf" does not exist LINE 1: ...odel"."id" FROM "mymodel" ORDER BY "asdf" ASC... ^ SELECT "mymodel"."id" FROM "mymodel" ORDER BY asdf ASC; ERROR: column "asdf" does not exist LINE 1: ...odel"."id" FROM "mymodel" ORDER BY asdf ASC; ^
comment:9 by , 9 years ago
Summary: | Cannot order query by constant value → Cannot order query by constant value on PostgreSQL |
---|---|
Triage Stage: | Unreviewed → Accepted |
I can reproduce on PostgreSQL now (not sure if the issue affects other databases).
Looks like the query needs to be something like SELECT "polls_question"."id" FROM "polls_question" ORDER BY 'asdf'::text ASC;
comment:10 by , 9 years ago
That seems to work.
On the other hand, your comment ("I don't see what the use of ordering by a constant string value is") made me think of another way:
SELECT "polls_question"."id" FROM "polls_question";
Dropping the order clause entirely might even solve that problem for other databases.
comment:11 by , 5 years ago
Hi I added a PR.
IMO when we use value expression in order by but don't include in select columns, the query occurs incorrectly.
comment:12 by , 5 years ago
Has patch: | set |
---|---|
Owner: | changed from | to
Status: | new → assigned |
Version: | 1.8 → master |
comment:13 by , 5 years ago
Has patch: | unset |
---|
comment:14 by , 5 years ago
Has patch: | set |
---|
comment:15 by , 5 years ago
Owner: | removed |
---|---|
Status: | assigned → new |
comment:16 by , 5 years ago
Cc: | added |
---|
comment:18 by , 5 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
I don't see what the use of ordering by a constant string value is. Anyway, it looks like this is a database limitation.