Grails

I'm not a 100% sure, but I think I had the same problem with Apache Derby and a Date as the type for ordering.

Email threads discussing this issue on hsqldb

http://www.nabble.com/Criteria.list%28%29-with-pagination-and-sort-causes-SQLException-td16314980.html
http://www.nabble.com/HSQLDB-error%3A-Not-in-aggregate-function-or-group-by-clause-td16264121.html
http://www.nabble.com/GORM%2C-Criteria%3A-weird-problem-with-sort-and-pagination-tt16290145.html

The exception thrown is:

java.sql.SQLException: Not in aggregate function or group by clause: org.hsqldb.Expression@c48626 in statement [ ... ]

1 remark: the select count() query does NOT fail, if the "order by" field is String

For at least one database, Informix IDS 11.10, it does fail regardless of what type the order by field is.

I think it's duplicate with GRAILS-2690 which has been fixed

I just tested this with the latest from SVN and it's working fine now. Seems the other issue noted above by Cui did resolve this. Thanks for the note, does commenting like this alert the moderators or assignee so they can close it out?

Thanks for the report

For the test to fail, two things are required;
1> it MUST use HSQLDB
2> pagination parameters MUST be specified.

If I comment out the max: and offset: entries, it works fine. Note that
toSort is a String so it's slightly different than what's been
discussed on several threads, so perhaps it deserves a new JIRA.

If I leave in the pagination parameters, but use MySql rather than
HSQLDB, it also works. So I'm pretty sure it's a problem
with HSQLDB. Which raises the question whether it should
be submitted in the Grails JIRA at all, but you can call that one.

class ExampleController {

    def index = { }
    def problem = {
        def results = Example.createCriteria().list(
                max: 32,
                offset: 0,
                getSearchCrit())

        [myList : results]
    }

    def getSearchCrit = {
        return {
            and {
                like('toSort', '%string%')
            }
            order("toSort", "asc")
        }
    }
}

FWIW, here's the test class:

class ExampleControllerTests extends GroovyTestCase {

    void testProblem() {
        new Example(toSort:"string 1").save(flush:true)
        new Example(toSort:"string 2").save(flush:true)
        ExampleController ec = new ExampleController()
        def res = ec.problem()

        assertEquals res.myList.size(), 2
    }
}

And, for completeness, here's the domain class...

class Example {
    String toSort
}

Postgresql report the exception even with "id" column.
select count from book order by id;
exception:
ERROR: column "book.id" must appear in the GROUP BY clause or be used in an aggregate function

• • • • • • • • • • Error **********

ERROR: column "book.id" must appear in the GROUP BY clause or be used in an aggregate function
SQL state: 42803

Guys, I found this error some time ago but just got time yesterday to take a good look a it.
The error is being generated in the HibernateCriteriaBuilder when pagination and order parameters are processed and the totalCount process ( count ) is fired.
To avoid it, I rearranged a minor part of the code and added a temporary storage for the order criterions, that are added after the totalCount process is fired and before the actual select for results.
Since I really am a SVN guy and don't have a clue about GIT I'm attaching the modified version of the latest file downloaded this morning for github.com
If you instruct me howto submit a patch with git I'll gladly will.

file attached

Thanks for the patch

I'm still experiencing this error with grails 1.2.1. My criteria queries run fine on mysql 5.1. but throw an exception with hsqldb:

 
java.sql.SQLException: Not in aggregate function or group by clause:
org.hsqldb.Expression@78d377df in statement
[select top ? count(*) as y0_ from wiedervorlage this_ order by this_.betreff asc]

Code snippet:

def c1 = Wiedervorlage.createCriteria()
def query = {
  !params.kontakt?.id ?: eq('kontakt.id', Long.parseLong(params.kontakt.id))
  maxResults(params.max)
  !params.sort ?: order(params.sort, params.order)
}
c1.list(query)

I'm also seeing this issue with HQL and Grails 1.2.2 when using a GROUP BY clause:

java.sql.SQLException: Not in aggregate function or group by clause: org.hsqldb.Expression@49e1d547 in statement [select tag2_.name as col_0_0_, testoutcom0_.test_result_id as col_1_0_, count(*) as col_2_0_, testresult3_.id as id21_, testresult3_.version as version21_, testresult3_.include_in_calculations as include3_21_, testresult3_.is_failure as is4_21_, testresult3_.name as name21_ from test_outcome testoutcom0_ inner join test_outcome_tag tags1_ on testoutcom0_.id=tags1_.test_outcome_tags_id inner join tag tag2_ on tags1_.tag_id=tag2_.id inner join test_result testresult3_ on testoutcom0_.test_result_id=testresult3_.id where testoutcom0_.test_run_id=? and tag2_.id=? group by testoutcom0_.test_result_id]