Grails
  1. Grails
  2. GRAILS-8365

totalCount creates additional order by statement

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0-RC1
    • Fix Version/s: 2.0-RC3
    • Component/s: Persistence
    • Labels:
    • Environment:
      Win 7 (64) Java 6_0_29 (64), Grails 2.0.0.BUILD-SNAPSHOT as of 11/22/11

      Description

      This is a follow up of http://jira.grails.org/browse/GRAILS-8206
      When totalCount is invoked on a result set, there is an additional "order by lower" in the generated "select count" query if there is a "sort 'property'" in the domain class' mapping section, e.g.
      static mapping

      { sort 'someProperty' }

        Issue Links

          Activity

          Hide
          Fat Zopilot added a comment - - edited

          Well, the following produces invalid SQL using totalCount (at least Postgres says so; there is no problem with the in-memory DB).

          class SomeDomainClass {

          String company;

          static constraints = {
          }

          static mapping =

          { sort "company" }

          }

          class SomeService {

          def serviceMethod()

          { def result = SomeDomainClass.list(offset : 0, max : 10); // assert result instanceof PagedResultList; log.info("Number of entities: "+result.totalCount); }

          }

          This is the SQL produced: select count as y0_ from some_domain_class this_ order by lower(this_.company) asc
          and this is the error that is raised by Postgres:
          ERROR: column "this_.company" must appear in the GROUP BY clause or be used in an aggregate function.

          Postgres is known to be more strict than others regarding clean SQL ....

          To reproduce:
          Put PSQL JDBC driver in lib
          Create sa user without pwd
          create DB named g2
          run-app (the demo project as uploaded)

          Show
          Fat Zopilot added a comment - - edited Well, the following produces invalid SQL using totalCount (at least Postgres says so; there is no problem with the in-memory DB). class SomeDomainClass { String company; static constraints = { } static mapping = { sort "company" } } class SomeService { def serviceMethod() { def result = SomeDomainClass.list(offset : 0, max : 10); // assert result instanceof PagedResultList; log.info("Number of entities: "+result.totalCount); } } This is the SQL produced: select count as y0_ from some_domain_class this_ order by lower(this_.company) asc and this is the error that is raised by Postgres: ERROR: column "this_.company" must appear in the GROUP BY clause or be used in an aggregate function. Postgres is known to be more strict than others regarding clean SQL .... To reproduce: Put PSQL JDBC driver in lib Create sa user without pwd create DB named g2 run-app (the demo project as uploaded)

            People

            • Assignee:
              Graeme Rocher
              Reporter:
              Fat Zopilot
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development