Count(xxx, distinct=True) generates incorrect sql

Bug #675377 reported by Ian Booth
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Storm
New
Undecided
Unassigned

Bug Description

Basically, doing this (warning, pseudo code):

store.execute(
    Select(columns=[Count((a,b,c), distinct=True)],
    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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.