[Django] #25507: Can't count when using annotate(field=RawSQL(...))
Django
-------------------------------+--------------------
Reporter: acatton | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.8
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------
The simplest test case for this is:
{{{
>>> User.objects.annotate(foo=RawSQL('%s', ['value', ])).count()
Traceback (most recent call last):
File "<console>", line 1, in <module>
File ".../django/db/models/query.py", line 371, in count
return self.query.get_count(using=self.db)
File ".../django/db/models/sql/query.py", line 483, in get_count
q = self.clone()
File ".../django/db/models/sql/query.py", line 464, in get_aggregation
for (alias, annotation), val
File ".../django/db/models/sql/compiler.py", line 852, in execute_sql
return val[0:self.col_count]
File ".../django/db/backends/utils.py", line 79, in execute
return super(CursorDebugWrapper, self).execute(sql, params)
File ".../django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File ".../django/db/utils.py", line 95, in __exit__
# Note that we are intentionally not using @wraps here for performance
File ".../django/utils/six.py", line 658, in reraise
raise value.with_traceback(tb)
File ".../django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: non-integer constant in GROUP BY
LINE 1: ...M "accounts_user" GROUP BY "accounts_user"."id", ('value')) ...
^
}}}
This error only happens when using PostgreSQL.
--
Ticket URL: <https://code.djangoproject.com/ticket/25507>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: acatton | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.8
Keywords: RawSQL filter ORM | Triage Stage:
sql | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by acatton):
* keywords: => RawSQL filter ORM sql
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/25507#comment:1>
Django
Reporter: acatton | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: RawSQL filter ORM | Triage Stage:
sql | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by acatton):
* component: Uncategorized => Database layer (models, ORM)
* type: Uncategorized => Bug
* severity: Normal => Release blocker
* needs_tests: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/25507#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: acatton | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: RawSQL filter ORM | Triage Stage:
sql | Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by akaariai):
Who knew you can't group by a constant in PostgreSQL. Seems rather
pointless limitation in my opinion, especially considering you can use
functions that return a constant from the query's point of view (now(),
txid_current, `'foo'||'bar'` etc).
To fix this we need a way to tell Django a given value is a constant. For
RawSQL the way would be to add a flag constant=True to the constructor.
But this seems too complex.
If we make sure you can use Value() annotations with group by, then we
could just document that RawSQL with a constant value + aggregation do not
work on PostgreSQL. Use Value() instead.
--
Ticket URL: <https://code.djangoproject.com/ticket/25507#comment:3>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Keywords: RawSQL filter ORM | Triage Stage: Accepted
sql |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* severity: Release blocker => Normal
* needs_tests: 1 => 0
* stage: Unreviewed => Accepted
Comment:
I don't see why this is a release blocker. Depending on the complexity of
the patch, we might be able to backport to 1.8 under the "bug in a new
feature" rationale, but it doesn't seem to be a critical issue as far as I
can tell.
--
Ticket URL: <https://code.djangoproject.com/ticket/25507#comment:4>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
Keywords: RawSQL filter ORM | Triage Stage: Accepted
sql |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by jarshwah):
I agree, this doesn't need to be a release blocker. The rationale behind
the query not working on postgres is that non-integer constants in a group
by do not actually affect the query at all. It'd arguably be a warning
"are you sure you meant.." but I really don't think this should blow up.
That's a postgres issue though, so not much point debating the merits.
I think documenting the limitation is enough as akaariai suggested. Will a
parameterised constant `Value()` not blow up like the static RawSQL? If
so, then that's the obvious solution to document.
--
Ticket URL: <https://code.djangoproject.com/ticket/25507#comment:5>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
Keywords: RawSQL filter ORM | Triage Stage: Accepted
sql |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Can Sarıgöl):
* cc: Can Sarıgöl (added)
Comment:
Hi, this problem was solved by
[https://github.com/django/django/commit/3f32154f40a855afa063095e3d091ce6be21f2c5
#diff-0edd853580d56db07e4020728d59e193R451 Simon's PR]. Does it need to
backport?
--
Ticket URL: <https://code.djangoproject.com/ticket/25507#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Antoine Catton | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution:
Keywords: RawSQL filter ORM | Triage Stage: Accepted
sql |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by GitHub <noreply@…>):
In [changeset:"12b7956fc3735101fcad597047b80b57efb5048a" 12b7956f]:
{{{
#!CommitTicketReference repository=""
revision="12b7956fc3735101fcad597047b80b57efb5048a"
Refs #25507 -- Added tests for using QuerySet.count() with a RawSQL
annotation.
Fixed in 3f32154f40a855afa063095e3d091ce6be21f2c5
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25507#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Antoine Catton | Owner: nobody
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: RawSQL filter ORM | Triage Stage: Accepted
sql |
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* status: new => closed
* version: 1.8 => master
* resolution: => fixed
Comment:
Fixed in 3f32154f40a855afa063095e3d091ce6be21f2c5.
Can Thanks for checking that, it doesn't qualify for a backport.
--
Ticket URL: <https://code.djangoproject.com/ticket/25507#comment:8>