Opened 2 years ago

Last modified 2 years ago

#25507 new Bug

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

Reported by: Antoine Catton Owned by: nobody
Component: Database layer (models, ORM) Version: 1.8
Severity: Normal Keywords: RawSQL filter ORM sql
Cc: 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 (5)

comment:1 Changed 2 years ago by Antoine Catton

Keywords: RawSQL filter ORM sql added

comment:2 Changed 2 years ago by Antoine Catton

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

comment:3 Changed 2 years ago by Anssi Kääriäinen

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 Changed 2 years ago by Tim Graham

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 Changed 2 years ago by Josh Smeaton

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.

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