Count(xxx, distinct=True) generates incorrect sql
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Storm |
New
|
Undecided
|
Unassigned |
Bug Description
Basically, doing this (warning, pseudo code):
store.execute(
Select(
tables=[A],
where=xxxx)
)
results in sql like:
select count(distinct a,b,c) from A where xxx
From my understanding, the above sql is not valid for Postgres, although
it works with other databases like mysql etc. What Postgres wants is this:
select count(distinct (a,b,c)) from A where xxx
Note the extra () around the column list. I think also the above may be
more correct from an sql standpoint?
At the moment, using storm's current implementation of Count() you get
the following error:
ProgrammingError: function count(integer, integer, integer, integer)
does not exist LINE 1: SELECT COUNT(DISTINCT a,b,c) ... HINT: No
function matches the given name and argument types. You might need to
add explicit type casts.
The fix is to change expr.py:
Current:
if count.distinct:
return "COUNT(DISTINCT %s)" % column
New:
if count.distinct:
return "COUNT(DISTINCT (%s))" % column