[Django] #26192: Same as #26185 but without extra but with annotate
Django
----------------------------------------------+--------------------
Reporter: srkunze | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.8
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
{{{
MyModel.objects.annotate(my_column=Value(0)).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? ;-)
--
Ticket URL: <https://code.djangoproject.com/ticket/26192>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: srkunze | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by srkunze):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Old description:
> {{{
> MyModel.objects.annotate(my_column=Value(0)).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? ;-)
New description:
{{{
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? ;-)
--
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: srkunze | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: srkunze | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.8
(models, ORM) |
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* status: new => closed
* resolution: => invalid
Comment:
I don't see what the use of ordering by a constant string value is.
Anyway, it looks like this is a database limitation.
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:3>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by srkunze):
* status: closed => new
* resolution: invalid =>
Comment:
> 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?
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:4>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by timgraham):
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?
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:5>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by srkunze):
Interesting. We upgraded to 1.8.9 and use PostgreSQL 9.3.10. Our testsuite
runs normally.
Can you provide your query string? I would be interesting in how the order
clause looks.
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:6>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by timgraham):
{{{
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:7>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by srkunze):
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;
^
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: srkunze | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* stage: Unreviewed => Accepted
Comment:
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;`
[http://www.postgresql.org/message-
id/03ff01cda7f3$5201beb0$f6053c10$@yahoo.com reference]
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:9>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by srkunze):
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 when requesting from PostgreSQL might
solve that problem even for other databases.
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:10>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Can Sarıgöl):
Hi I added a [https://github.com/django/django/pull/11358 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?
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:11>
Django
-------------------------------------+-------------------------------------
| Sarıgöl
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Can Sarıgöl):
* owner: nobody => Can Sarıgöl
* status: new => assigned
* has_patch: 0 => 1
* version: 1.8 => master
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:12>
Django
-------------------------------------+-------------------------------------
Reporter: Sven R. Kunze | Owner: Can
| Sarıgöl
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* has_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:13>
Django
-------------------------------------+-------------------------------------
Reporter: Sven R. Kunze | Owner: Can
| Sarıgöl
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Can Sarıgöl):
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:14>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Can Sarıgöl):
* owner: Can Sarıgöl => (none)
* status: assigned => new
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:15>
Django
-------------------------------------+-------------------------------------
Reporter: Sven R. Kunze | Owner: (none)
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Can Sarıgöl):
* cc: Can Sarıgöl (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:16>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* status: new => assigned
* owner: (none) => felixxm
Comment:
[https://github.com/django/django/pull/11396 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:17>
Django
-------------------------------------+-------------------------------------
Reporter: Sven R. Kunze | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:18>
Django
-------------------------------------+-------------------------------------
Reporter: Sven R. Kunze | Owner: felixxm
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak <felisiak.mariusz@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"f6075fb333bae29ee213b050e91eaadef75496dd" f6075fb3]:
{{{
#!CommitTicketReference repository=""
revision="f6075fb333bae29ee213b050e91eaadef75496dd"
Fixed #26192 -- Fixed crash of ordering by constants on PostgreSQL.
Thanks Simon Charette for the review.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:20>
Django
-------------------------------------+-------------------------------------
Reporter: Sven R. Kunze | Owner: felixxm
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"cc80979f011c72f8b4b5f35a5a36f049bc07bf0e" cc80979f]:
{{{
#!CommitTicketReference repository=""
revision="cc80979f011c72f8b4b5f35a5a36f049bc07bf0e"
Refs #26192 -- Added tests for ordering by constant value.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26192#comment:19>