Grails

totalCount creates additional order by statement

Details

  • Type: Bug Bug
  • Status: Closed 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

is duplicated by

Bug - A problem which impairs or prevents the functions of the product. GRAILS-8390 totalCount throws error when order is specified

  • Major - Major loss of function.
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed can be reopened.
is related to

Bug - A problem which impairs or prevents the functions of the product. GRAILS-8206 totalCount is broken

  • Blocker - Blocks development and/or testing work, production could not run.
  • Closed - The issue is considered finished, the resolution is correct. Issues which are closed can be reopened.

Improvement - An improvement or enhancement to an existing feature or task. GRAILS-8380 Automatically disable order by clause for count() with where/named queries

  • Major - Major loss of function.
  • In Progress - This issue is being actively worked on at the moment by the assignee.

Activity

Hide
Permalink
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
Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: