Opened 8 years ago

Closed 5 years ago

#25507 closed Bug (fixed)

count() crashes with a RawSQL annotation.

Reported by: Antoine Catton Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: RawSQL filter ORM sql
Cc: Can Sarıgöl Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (8)

comment:1 by Antoine Catton, 8 years ago

Keywords: RawSQL filter ORM sql added

comment:2 by Antoine Catton, 8 years ago

Component: UncategorizedDatabase layer (models, ORM)
Needs tests: set
Severity: NormalRelease blocker
Type: UncategorizedBug

comment:3 by Anssi Kääriäinen, 8 years ago

Summary: Can't count when using annotate(field=RawSQL(...))Can't count when using annotate(field=RawSQL(sql)) when sql is a constant

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.

comment:4 by Tim Graham, 8 years ago

Needs tests: unset
Severity: Release blockerNormal
Triage Stage: UnreviewedAccepted

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.

comment:5 by Josh Smeaton, 8 years ago

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.

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

Cc: Can Sarıgöl added

Hi, this problem was solved by Simon's PR. Does it need to backport? if not we can close.

Last edited 5 years ago by Can Sarıgöl (previous) (diff)

comment:7 by GitHub <noreply@…>, 5 years ago

In 12b7956f:

Refs #25507 -- Added tests for using QuerySet.count() with a RawSQL annotation.

Fixed in 3f32154f40a855afa063095e3d091ce6be21f2c5

comment:8 by Mariusz Felisiak, 5 years ago

Resolution: fixed
Status: newclosed
Summary: Can't count when using annotate(field=RawSQL(sql)) when sql is a constantcount() crashes with a RawSQL annotation.
Version: 1.8master

Fixed in 3f32154f40a855afa063095e3d091ce6be21f2c5.

Can Thanks for checking that, it doesn't qualify for a backport.

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