Projections.count() and Projections.countDistinct() both result in the same query

Answer 1

I guess this is because your 'id' field is a unique field, and hibernate knows that. Does the query give a different result when you use 'distinct'?

Answer 2

Maybe the field ID is defined as unique anyway? Try using a different field, which is not unique.

Answer 3

-> Following answar is not correct... I will leave it here for a while though...

you should do

criteria = criteria.setProjection(Projections.countDistinct("id"));

instead of

criteria.setProjection(Projections.countDistinct("id"));

(or is this not needed?)

Answer 4

hi,

try debugging the method toSqlString in class CountProjection. Looking at the code I can only image that distinct was not true; I see no other reason why the distinct would not be included:

    public String toSqlString(Criteria criteria, int position, CriteriaQuery criteriaQuery) 
throws HibernateException {
    StringBuffer buf = new StringBuffer();
    buf.append("count(");
    if (distinct) buf.append("distinct ");
    return buf.append( criteriaQuery.getColumn(criteria, propertyName) )
        .append(") as y")
        .append(position)
        .append('_')
        .toString();
}

regards,
Stijn

Answer 5

"Problem" solved. Reason: PEBKAC (Problem Exists Between Keyboard And Chair). I had a branch in my code and didn't realize that the branch was executed. That branch used rowCount() instead of countDistinct().

Answer 6

Thre is bug in 3.5.1

After

cr.setProjection(Projections.countDistinct("memberId"));

SQL result is count(memberId)

bo not count(distinct memberId)

after debug ....

public final class Projections {

    public static CountProjection countDistinct(String propertyName) {
        return new CountProjection(propertyName).setDistinct();
    }

distinct is noticed but not used.

public class CountProjection extends AggregateProjection {
    private boolean distinct;

    protected CountProjection(String prop) {
        super("count", prop);
    }

    public String toString() {
        if ( distinct ) {
            return "distinct " + super.toString();
        }
        else {
            return super.toString();
        }
    }

    public CountProjection setDistinct() {
        distinct = true;
        return this;
    }
}

and ... in AggregateProjection only getFunctionName() = functionName = "count" is used !!!

public class AggregateProjection extends SimpleProjection {

protected AggregateProjection(String functionName, String propertyName) {
    this.functionName = functionName;
    this.propertyName = propertyName;
}

public String getFunctionName() {
    return functionName;
}

public String getPropertyName() {
    return propertyName;
}

    ...

public String toSqlString(Criteria criteria, int loc, CriteriaQuery criteriaQuery)
        throws HibernateException {
    final String functionFragment = getFunction( criteriaQuery ).render(
            buildFunctionParameterList( criteria, criteriaQuery ),
            criteriaQuery.getFactory()
    );
    return functionFragment + " as y" + loc + '_';
}

protected SQLFunction getFunction(CriteriaQuery criteriaQuery) {
    return getFunction( getFunctionName(), criteriaQuery );
}

Answer 7

After search

http://opensource.atlassian.com/projects/hibernate/browse/HHH-4957