Cannot order query by constant value on PostgreSQL

[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? ;-)

[Tim Graham]

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

[Sven R. Kunze]

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?

[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?

[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

[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.

[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;
                                              ^

[Tim Graham]

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

[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.

[Can Sarıgöl]

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. the reason of this, the value has no output_field. I added a commit about it. Could you review?

[Mariusz Felisiak]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In cc80979f:

Refs #26192 -- Added tests for ordering by constant value.

[Mariusz Felisiak <felisiak.mariusz@…>]

In f6075fb3:

Fixed #26192 -- Fixed crash of ordering by constants on PostgreSQL.

Thanks Simon Charette for the review.