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

comment:1 by Sven R. Kunze, 9 years ago

Description: modified (diff)

comment:2 by Sven R. Kunze, 9 years ago

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

comment:3 by Tim Graham, 9 years ago

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 by Sven R. Kunze, 9 years ago

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 by Tim Graham, 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 Sven R. Kunze, 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 interested in how the order clause looks.

Ours:

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

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

comment:7 by Tim Graham, 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 Sven R. Kunze, 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 Tim Graham, 9 years ago

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 by Sven R. Kunze, 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.

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

comment:11 by Can Sarıgöl, 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. the reason of this, the value has no output_field. I added a commit about it. Could you review?

Version 0, edited 5 years ago by Can Sarıgöl (next)

comment:12 by Can Sarıgöl, 5 years ago

Has patch: set
Owner: changed from nobody to Can Sarıgöl
Status: newassigned
Version: 1.8master

comment:13 by Mariusz Felisiak, 5 years ago

Has patch: unset

comment:14 by Can Sarıgöl, 5 years ago

Has patch: set

comment:15 by Can Sarıgöl, 5 years ago

Owner: Can Sarıgöl removed
Status: assignednew

comment:16 by Can Sarıgöl, 5 years ago

Cc: Can Sarıgöl added

comment:17 by Mariusz Felisiak, 5 years ago

Owner: set to Mariusz Felisiak
Status: newassigned

comment:18 by Mariusz Felisiak, 5 years ago

Triage Stage: AcceptedReady for checkin

comment:19 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

In cc80979f:

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

comment:20 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

Resolution: fixed
Status: assignedclosed

In f6075fb3:

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

Thanks Simon Charette for the review.

Note: See TracTickets for help on using tickets.
Back to Top