Opened 2 years ago

Last modified 2 years ago

#26192 new Bug

Cannot order query by constant value on PostgreSQL

Reported by: Sven R. Kunze Owned by: nobody
Component: Database layer (models, ORM) Version: 1.8
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Sven R. Kunze)

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 (10)

comment:1 Changed 2 years ago by Sven R. Kunze

Description: modified (diff)

comment:2 Changed 2 years ago by Sven R. Kunze

Summary: Same as #26185 but without extra but with annotateSame as #26185 but with annotate and without extra

comment:3 Changed 2 years ago by Tim Graham

Resolution: invalid
Status: newclosed
Summary: Same as #26185 but with annotate and without extraCannot order query by constant value

I don't see what the use of ordering by a constant string value is. Anyway, it looks like this is a database limitation.

comment:4 Changed 2 years ago by Sven R. Kunze

Resolution: invalid
Status: closednew

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 Changed 2 years ago by Tim Graham

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 Changed 2 years ago by Sven R. Kunze

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 interested in how the order clause looks.

Ours:

SELECT "mymodel"."id" FROM "app_mymodel" ORDER BY 'asdf' ASC

Last edited 2 years ago by Sven R. Kunze (previous) (diff)

comment:7 Changed 2 years ago by Tim Graham

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 Changed 2 years ago by Sven R. Kunze

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 Changed 2 years ago by Tim Graham

Summary: Cannot order query by constant valueCannot order query by constant value on PostgreSQL
Triage Stage: UnreviewedAccepted

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;

reference

comment:10 Changed 2 years ago by Sven R. Kunze

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.

Last edited 2 years ago by Sven R. Kunze (previous) (diff)
Note: See TracTickets for help on using tickets.
Back to Top