count() crashes with a RawSQL annotation.

[Antoine Catton]

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.

[Anssi Kääriäinen]

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.

[Tim Graham]

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.

[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.

[Can Sarıgöl]

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

[GitHub <noreply@…>]

In 12b7956f:

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

Fixed in 3f32154f40a855afa063095e3d091ce6be21f2c5

[Mariusz Felisiak]

Fixed in 3f32154f40a855afa063095e3d091ce6be21f2c5.

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