Grails
  1. Grails
  2. GRAILS-8390

totalCount throws error when order is specified

    Details

    • Type: Bug Bug
    • Status: Reopened
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.0-RC2
    • Fix Version/s: None
    • Component/s: Persistence
    • Environment:
      OS X

      Description

      An error is thrown whenever totalCount is called on a criteria object and order is specified either as a default sort order in the GORM mapping or if order is specified in the criteria itself. An error like the following is thrown:

      Column "THIS_.DATE_CREATED" must be in the GROUP BY list; SQL statement: select count as y0_ from post this_ order by this_.date_created desc limit ?

      I've attached a sample project that shows this behavior with a criteria object. To reproduce this error, add two new Post objects and view the list to see the error.

        Issue Links

          Activity

          Hide
          Craig Burke added a comment -

          Hi Jeff,

          I'm not sure this is actually a duplicate. This is different than the issue in RC1. In RC2 it actually throws an error instead of just returning an incorrect count.

          Show
          Craig Burke added a comment - Hi Jeff, I'm not sure this is actually a duplicate. This is different than the issue in RC1. In RC2 it actually throws an error instead of just returning an incorrect count.
          Hide
          Goran Ehrsson added a comment -

          This issue has popped up as an regression in Grails 2.2.3!
          I upgraded an application from 2.1.4 to 2.2.3 and got hit by this.
          I don't know if this is the right issue to track this, or if we should re-open one of the related/duplicate issues, or create a new for Grails 2.2.3.

          I have a sample (Book/Author) project that reproduces the issue, but I have no permission to attach it to this JIRA.

          def author = Author.findByName("...")
          Book.countByAuthor(author)
          
          Caused by: org.h2.jdbc.JdbcSQLException: Column "THIS_.NAME" must be in the GROUP BY list; SQL statement:
          select count(*) as y0_ from book this_ where this_.author_id=? order by lower(this_.name) asc [90016-164]
          	at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
          	at org.h2.message.DbException.get(DbException.java:169)
          	at org.h2.message.DbException.get(DbException.java:146)
          	at org.h2.expression.ExpressionColumn.getValue(ExpressionColumn.java:178)
          
          Show
          Goran Ehrsson added a comment - This issue has popped up as an regression in Grails 2.2.3! I upgraded an application from 2.1.4 to 2.2.3 and got hit by this. I don't know if this is the right issue to track this, or if we should re-open one of the related/duplicate issues, or create a new for Grails 2.2.3. I have a sample (Book/Author) project that reproduces the issue, but I have no permission to attach it to this JIRA. def author = Author.findByName( "..." ) Book.countByAuthor(author) Caused by: org.h2.jdbc.JdbcSQLException: Column "THIS_.NAME" must be in the GROUP BY list; SQL statement: select count(*) as y0_ from book this_ where this_.author_id=? order by lower(this_.name) asc [90016-164] at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) at org.h2.message.DbException.get(DbException.java:169) at org.h2.message.DbException.get(DbException.java:146) at org.h2.expression.ExpressionColumn.getValue(ExpressionColumn.java:178)
          Hide
          Graeme Rocher added a comment -

          Please try attach now. I reopened the issue

          Show
          Graeme Rocher added a comment - Please try attach now. I reopened the issue
          Hide
          Goran Ehrsson added a comment -

          Attached project that reproduces the issue in Grails 2.2.3.

          Show
          Goran Ehrsson added a comment - Attached project that reproduces the issue in Grails 2.2.3.
          Hide
          Goran Ehrsson added a comment - - edited

          It's named queries (countBy...) that has the problem now. Criteria queries (count) seems to work ok. See also GRAILS-8380

          Show
          Goran Ehrsson added a comment - - edited It's named queries (countBy...) that has the problem now. Criteria queries (count) seems to work ok. See also GRAILS-8380
          Hide
          Jeff Scott Brown added a comment -

          Goran,

          You said "named queries" and "countBy". Is the problem with named queries or is it with dynamic finders like countByLastName('Nugent')?

          Show
          Jeff Scott Brown added a comment - Goran, You said "named queries" and "countBy". Is the problem with named queries or is it with dynamic finders like countByLastName('Nugent')?
          Show
          Jeff Scott Brown added a comment - Some relevant tests: https://github.com/grails-plugins/grails-hibernate-plugin/blob/master/test/unit/org/codehaus/groovy/grails/orm/hibernate/NamedCriteriaTests.groovy#L64
          Hide
          Goran Ehrsson added a comment -

          Sorry for my mixup! I meant dynamic finders, countByXxxxxx

          Show
          Goran Ehrsson added a comment - Sorry for my mixup! I meant dynamic finders, countByXxxxxx

            People

            • Assignee:
              Jeff Scott Brown
              Reporter:
              Craig Burke
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:

                Development