Grails where count gives incorrect results with projection

[monetschemist]

The count() method of detached criteria / where gives incorrect results when a projection is used in the where {}. In fact it appears that detachedCriteria.count() returns the same value as the first returned projection count.

Steps to Reproduce

This query

def c = gtest01.TestA.where {
    projections {
        groupProperty 'grouping'
        count()
    }
}
c.list().each { t ->
    println "t $t"
}
println c.count()

produces the following result:

t [1, 12]
t [2, 16]
t [3, 9]
t [4, 18]
t [5, 5]
t [6, 20]
t [7, 13]
t [8, 7]
12

Note that there are 8 results returned, not 12 as indicated by count(); and that the value returned by count is the same as the value of count() in the first result from the projection (this is consistent across three separate cases I have tried).

Expected Behaviour

As noted above, detachedCriteria.count() in the above example should return 8.

Actual Behaviour

Instead, detachedCriteria.count() in the above example returns 12, which appears to be the same value as the count returned from the first projection / grouping result.

Environment Information

• Operating System: Linux 4.13.0-41-generic #46-Ubuntu SMP Wed May 2 13:38:30 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
• GORM Version: 6.1.9.RELEASE
• Grails Version (if using Grails): 3.3.5
• JDK Version: 1.8.0_171

Example Application

https://github.com/monetschemist/grails-where-count-problem

[monetschemist]

Just wondering if anyone has any thoughts on this problem?

Thanks in advance.

[tircnf]

@monetschemist -- Just ran into this same problem today. We were trying to have a pageable search and use the same detachedCriteria for both the list and the count, but it breaks when our list method has a projection.

If you examine the sql that gets created when using .count() you can see that it just adds an additional count(*) to the select list. That is why you are seeing those results. the count() method is just grabbing the first row.

We finally just had to create two different criterias. One for fetching the results and one for counting. It is really easy to mess things up though. :(

[monetschemist]

@tircnf wow so wonderful to run into someone else trying to do complicated shzt and struggling! I wonder if we could exchange some stories to mutual benefit? I'd love to put good info here if anyone wants to listen.