Bug in Hibernate Queries?

[OverZealous]

I'm trying to figure out how to find a user associated with a specific company and with a specific role in Spring Security.  I've tried it using criteria and where queries.

I'm using standard Spring Security domain classes (User, Role, and UserRole).  I've added a Company domain class which is associated with User (User belongs to a Company).

My query looks like:

  def accountManager = Role.findByAuthority('ROLE_ACCOUNT_MANAGER')

  def fooCompany = Company.get(1)

  UserRole.createCriteria().get {

eq 'role', accountManager

user {

eq "company", fooCompany

}

projections {

distinct('user')

}

  }

But this throws an error:

  org.h2.jdbc.JdbcSQLException: Column "USER_ALIAS1_.COMPANY_ID" not found; SQL statement:

  select distinct this_.user_id as y0_ from user_role this_ where this_.role_id=? and (user_alias1_.company_id=?)

So I tried simplifying it.  With a super simple (where) query like this:

  UserRole.find { user.email == "[hidden email]" }

I get this error:

  org.h2.jdbc.JdbcSQLException: "USER_ALIAS1_.EMAIL" not found; SQL statement:

  select this_.role_id as role1_257_0_, this_.user_id as user2_257_0_ from user_role this_ where (user_alias1_.email=?)

In both cases, it looks like Hibernate is not seeing the relationship correctly, because it never joins the User table.

UserRole is not associated bi-directionally with User or Role, but that shouldn't prevent simple, basic queries, should it?

I'm running Grails 2.0.3 with these plugins (among others that shouldn't affect this):

• hibernate 2.0.3
• spring-security-core 1.2.7.2
• multi-tenant-single-db 0.8.2
• hibernate-hijacker 0.8.1
• hawk-eventing 0.5.1

I found a previous thread on this, but it was never resolved: http://grails.1312388.n4.nabble.com/Querying-and-paginating-join-classes-generated-by-spring-security-td4396881.html

I also couldn't find anything in JIRA.

It sure seems like a serious bug.  Any ideas what would cause this?  More importantly, is there any way to make a single query to get this result without resorting to HQL?

Thanks,

- Phil DeJarnett

[ideasculptor]

On Thu, May 3, 2012 at 10:28 PM, Phil DeJarnett <[hidden email]> wrote:

I'm trying to figure out how to find a user associated with a specific company and with a specific role in Spring Security.  I've tried it using criteria and where queries.

I'm using standard Spring Security domain classes (User, Role, and UserRole).  I've added a Company domain class which is associated with User (User belongs to a Company).

My query looks like:

  def accountManager = Role.findByAuthority('ROLE_ACCOUNT_MANAGER')

  def fooCompany = Company.get(1)

  UserRole.createCriteria().get {

eq 'role', accountManager

user {

eq "company", fooCompany

}

projections {

distinct('user')

}

  }

But this throws an error:

  org.h2.jdbc.JdbcSQLException: Column "USER_ALIAS1_.COMPANY_ID" not found; SQL statement:

  select distinct this_.user_id as y0_ from user_role this_ where this_.role_id=? and (user_alias1_.company_id=?)

So I tried simplifying it.  With a super simple (where) query like this:

  UserRole.find { user.email == "[hidden email]" }

I get this error:

  org.h2.jdbc.JdbcSQLException: "USER_ALIAS1_.EMAIL" not found; SQL statement:

  select this_.role_id as role1_257_0_, this_.user_id as user2_257_0_ from user_role this_ where (user_alias1_.email=?)

In both cases, it looks like Hibernate is not seeing the relationship correctly, because it never joins the User table.

UserRole is not associated bi-directionally with User or Role, but that shouldn't prevent simple, basic queries, should it?

I don't do criteria queries, but hql would look something like this:

select u from UserRole r join r.user u where u.email = ?

or 

select distinct u from UserRole u join r.user u join r.role r where u.company = ? and r.role = ?

or, to forgo loading the Role and Company in separate statements, just do this:

select distinct u from UserRole u join r.user u join r.role r where u.company.id = ? and r.role.role = ?

why the resistance to using HQL, which is both simple and much clearer than most criteria definitions I read?  If the HQL doesn't work, then it seems likely that you've got a problem with your mapping, as it works fine for me with the following domain objects (with appropriate class and property names, of course - my Person.account property is analogous to your User.company property):

class Authority {

    String authority

    static mapping = {

        cache true

        table name:"authority"

        id generator:"identity",column:"authority_id",sqlType:"bigserial"

    }

    static constraints = {

        authority blank: false, unique: true

    }

}

class Person {

    transient springSecurityService

    String username

    String password

    String email

    boolean enabled

    boolean accountExpired

    boolean accountLocked

    boolean passwordExpired

    static belongsTo = [ account : Account ]

    static constraints = {

        username blank: false, unique: true

        password blank: false

        email blank: false, nullable: true, unique: true, email: true

    }

    static mapping = {

        table name:"person"

        id generator:"identity",column:"person_id",sqlType:"bigserial"

        password column: '`password`'

    }

    Set<Authority> getAuthorities() {

        PersonAuthority.findAllByPerson(this).collect { it.authority } as Set

    }

    def beforeInsert() {

        encodePassword()

    }

    def beforeUpdate() {

        if (isDirty('password')) {

            encodePassword()

        }

    }

    protected void encodePassword() {

        password = springSecurityService.encodePassword(password, username)

    }

}

import org.apache.commons.lang.builder.HashCodeBuilder

class PersonAuthority implements Serializable {

    Person person

    Authority authority

    boolean equals(other) {

        if (!(other instanceof PersonAuthority)) {

            return false

        }

        other.person?.id == person?.id &&

            other.authority?.id == authority?.id

    }

    int hashCode() {

        def builder = new HashCodeBuilder()

        if (person) builder.append(person.id)

        if (authority) builder.append(authority.id)

        builder.toHashCode()

    }

    static PersonAuthority get(long personId, long authorityId) {

        find 'from PersonAuthority where person.id=:personId and authority.id=:authorityId',

            [personId: personId, authorityId: authorityId]

    }

    static PersonAuthority create(Person person, Authority authority, boolean flush = false) {

        new PersonAuthority(person: person, authority: authority).save(flush: flush, insert: true)

    }

    static boolean remove(Person person, Authority authority, boolean flush = false) {

        PersonAuthority instance = PersonAuthority.findByPersonAndAuthority(person, authority)

        if (!instance) {

            return false

        }

        instance.delete(flush: flush)

        true

    }

    static void removeAll(Person person) {

        executeUpdate 'DELETE FROM PersonAuthority WHERE person=:person', [person: person]

    }

    static void removeAll(Authority authority) {

        executeUpdate 'DELETE FROM PersonAuthority WHERE authority=:authority', [authority: authority]

    }

    static mapping = {

        table name:"person_authority"

        id composite: ['authority', 'person']

        version false

    }

}

[OverZealous]

Samuel,

If the HQL doesn't work, then it seems likely that you've got a problem with your mapping, as it works fine for me with the following domain objects (with appropriate class and property names, of course - my Person.account property is analogous to your User.company property):

My classes look almost exactly like yours (as they should, since they are generated by the SpringSecurity plugin).  Look at the errors I am getting — they don't make any sense for the queries I'm running.  Hibernate is not joining the tables correctly.

There's definitely something broken, because I just tested, and the HQL below works, while the exact same where query fails:

  UserRole.executeQuery("from UserRole ur where user.company = ? and role = ?", [AppCtx.currentTenant, Role.accountManager]).user

  UserRole.find{ user.company == AppCtx.currentTenant && role == Role.accountManager }.user

The first works, the second fails with

  org.h2.jdbc.JdbcSQLException: Column "USER_ALIAS1_.COMPANY_ID" not found; SQL statement:

  select this_.role_id as role1_10_0_, this_.user_id as user2_10_0_ from user_role this_ where ((user_alias1_.company_id=?) and this_.role_id=?) [42122-164]

why the resistance to using HQL, which is both simple and much clearer than most criteria definitions I read?

• HQL is only slightly better than just writing raw SQL.  Why use Hibernate at all if I have to resort to HQL for simple queries?
• It is made of strings that are either hard-coded or concatenated together.
• An HQL query can't be extended or reused in any meaningful way.
• An HQL query can't be put into a named query or a DetachedCriteria for concatenating complex queries.
• HQL is only easier to read if you like SQL, but it's awful compared to where queries.  If a criteria is that complicated, it probably needs to be broken down into multiple DetachedCriteria or namedQueries and strung together to make it more legible.  ie: User.activeUsers.withRole(Role.administrator).list()
• It's also harder to refactor, although this isn't usually as big of a problem with an IDE.

It's a matter of personal preference, but I see the situation exactly the opposite of you.  Look at the two examples above: I think the where query is clearer, it's definitely shorter, and less error-prone, because of the compile-time validation and in-place arguments.

Criteria are necessary to write namedQueries, but I just realized earlier today that you can just use normal methods to effectively make a "namedQuery" that returns DetachedCriteria.

- Phil DeJarnett

[OverZealous]

Just a follow-up:

I just created a brand new Grails 2.0.3 application, and added the spring-security-core plugin.  I ran s2-quickstart com.example User Role, then tried this query in the shell:

  com.example.UserRole.find{ user.username == "fred" }

And got:

  2012-05-04 12:13:22,768 [main] ERROR util.JDBCExceptionReporter  - Column "USER_ALIAS1_.USERNAME" not found; SQL statement:

  select this_.role_id as role1_0_0_, this_.user_id as user2_0_0_ from user_role this_ where (user_alias1_.username=?) [42122-164]

That's about as basic as you can get.  I guess I need to raise a JIRA.

- Phil DeJarnett

Phil DeJarnett wrote:

Samuel,

If the HQL doesn't work, then it seems likely that you've got a problem with your mapping, as it works fine for me with the following domain objects (with appropriate class and property names, of course - my Person.account property is analogous to your User.company property):

My classes look almost exactly like yours (as they should, since they are generated by the SpringSecurity plugin).  Look at the errors I am getting — they don't make any sense for the queries I'm running.  Hibernate is not joining the tables correctly.

There's definitely something broken, because I just tested, and the HQL below works, while the exact same where query fails:

  UserRole.executeQuery("from UserRole ur where user.company = ? and role = ?", [AppCtx.currentTenant, Role.accountManager]).user

  UserRole.find{ user.company == AppCtx.currentTenant && role == Role.accountManager }.user

The first works, the second fails with

  org.h2.jdbc.JdbcSQLException: Column "USER_ALIAS1_.COMPANY_ID" not found; SQL statement:

  select this_.role_id as role1_10_0_, this_.user_id as user2_10_0_ from user_role this_ where ((user_alias1_.company_id=?) and this_.role_id=?) [42122-164]

why the resistance to using HQL, which is both simple and much clearer than most criteria definitions I read?

• HQL is only slightly better than just writing raw SQL.  Why use Hibernate at all if I have to resort to HQL for simple queries?
• It is made of strings that are either hard-coded or concatenated together.
• An HQL query can't be extended or reused in any meaningful way.
• An HQL query can't be put into a named query or a DetachedCriteria for concatenating complex queries.
• HQL is only easier to read if you like SQL, but it's awful compared to where queries.  If a criteria is that complicated, it probably needs to be broken down into multiple DetachedCriteria or namedQueries and strung together to make it more legible.  ie: User.activeUsers.withRole(Role.administrator).list()
• It's also harder to refactor, although this isn't usually as big of a problem with an IDE.

It's a matter of personal preference, but I see the situation exactly the opposite of you.  Look at the two examples above: I think the where query is clearer, it's definitely shorter, and less error-prone, because of the compile-time validation and in-place arguments.

Criteria are necessary to write namedQueries, but I just realized earlier today that you can just use normal methods to effectively make a "namedQuery" that returns DetachedCriteria.

- Phil DeJarnett

[OverZealous]

Second follow-up:

I've narrowed it down.  It's caused by the composite id.  I manually recreated the same class layout, without any plugins.  If I create the tables without id composite: ['bar', 'baz'], the queries work as expected.  However, if I create them using the mapping, I get the same error.

So, two questions:

1: Would removing the composite ID cause performance problems?

2: Is this a real and genuine bug?

Thanks,

- Phil DeJarnett

Phil DeJarnett wrote:

Samuel,

My classes look almost exactly like yours (as they should, since they are generated by the SpringSecurity plugin).  Look at the errors I am getting — they don't make any sense for the queries I'm running.  Hibernate is not joining the tables correctly.

There's definitely something broken, because I just tested, and the HQL below works, while the exact same where query fails:

  UserRole.executeQuery("from UserRole ur where user.company = ? and role = ?", [AppCtx.currentTenant, Role.accountManager]).user

  UserRole.find{ user.company == AppCtx.currentTenant && role == Role.accountManager }.user

The first works, the second fails with

  org.h2.jdbc.JdbcSQLException: Column "USER_ALIAS1_.COMPANY_ID" not found; SQL statement:

  select this_.role_id as role1_10_0_, this_.user_id as user2_10_0_ from user_role this_ where ((user_alias1_.company_id=?) and this_.role_id=?) [42122-164]

• HQL is only slightly better than just writing raw SQL.  Why use Hibernate at all if I have to resort to HQL for simple queries?
• It is made of strings that are either hard-coded or concatenated together.
• An HQL query can't be extended or reused in any meaningful way.
• An HQL query can't be put into a named query or a DetachedCriteria for concatenating complex queries.
• HQL is only easier to read if you like SQL, but it's awful compared to where queries.  If a criteria is that complicated, it probably needs to be broken down into multiple DetachedCriteria or namedQueries and strung together to make it more legible.  ie: User.activeUsers.withRole(Role.administrator).list()
• It's also harder to refactor, although this isn't usually as big of a problem with an IDE.

It's a matter of personal preference, but I see the situation exactly the opposite of you.  Look at the two examples above: I think the where query is clearer, it's definitely shorter, and less error-prone, because of the compile-time validation and in-place arguments.

Criteria are necessary to write namedQueries, but I just realized earlier today that you can just use normal methods to effectively make a "namedQuery" that returns DetachedCriteria.

- Phil DeJarnett

[ideasculptor]

On Fri, May 4, 2012 at 9:15 AM, Phil DeJarnett <[hidden email]> wrote:

Just a follow-up:

I just created a brand new Grails 2.0.3 application, and added the spring-security-core plugin.  I ran s2-quickstart com.example User Role, then tried this query in the shell:

  com.example.UserRole.find{ user.username == "fred" }

And got:

  2012-05-04 12:13:22,768 [main] ERROR util.JDBCExceptionReporter  - Column "USER_ALIAS1_.USERNAME" not found; SQL statement:

  select this_.role_id as role1_0_0_, this_.user_id as user2_0_0_ from user_role this_ where (user_alias1_.username=?) [42122-164]

That's about as basic as you can get.  I guess I need to raise a JIRA.

Looks like it.  I always find it VERY disturbing when I find bugs this fundamental (and I've found a few of them since 2.0.x release), as it indicates that whatever testing the grails codebase goes through prior to release is really not doing a sufficient job of exercising even common code paths, let alone difficult corner cases and the like. At this point, I'm kind of committed to my app running in grails 2, but stuff like this sure makes me very nervous.  And I've had mixed responses to filing a JIRA, too, to be honest.  I only seem to really get a response when I file a JIRA that actually contains at least significant detail about a fix if not a suggested patch.  When I did that, a high priority JIRA that had languished for several years was fixed overnight, though only in 2.1, so now I have to manually patch each 2.0.x release.  

Your best bet, I suspect, would be to file a JIRA that includes a patch that adds one or more unit tests that fail.  If you can convince someone to commit that to git, then they'll at least have to deal with the test failures until it is fixed.  My hypothesis is that grails development, not surprisingly, seems to be mostly driven by work on sites the grails dev team have on contract.  So things that impact their ability to do that work get fixed, everything else gets put on the back burner, even when it has a high priority in JIRA.  Since the grails team are clearly not running into this problem with criteria and where queries (a severe bug without a workaround wouldn't get released if it was impacting them), I wouldn't count on a rapid fix without some kind of external impetus.  You're more likely to see a comment about how they welcome code contributions - which is a nice thought, but not really why most of us have gravitated to using a framework for development.

In short - welcome to your new life as an HQL developer.  Or enjoy your exploration of the grails source code related to criteria query building.  It would seem that the grails code structures are not getting correctly translated into hibernate Criteria, so maybe the fix will be relatively simple.

--sam

 

- Phil DeJarnett

Phil DeJarnett wrote:

Samuel,

If the HQL doesn't work, then it seems likely that you've got a problem with your mapping, as it works fine for me with the following domain objects (with appropriate class and property names, of course - my Person.account property is analogous to your User.company property):

My classes look almost exactly like yours (as they should, since they are generated by the SpringSecurity plugin).  Look at the errors I am getting — they don't make any sense for the queries I'm running.  Hibernate is not joining the tables correctly.

There's definitely something broken, because I just tested, and the HQL below works, while the exact same where query fails:

  UserRole.executeQuery("from UserRole ur where user.company = ? and role = ?", [AppCtx.currentTenant, Role.accountManager]).user

  UserRole.find{ user.company == AppCtx.currentTenant && role == Role.accountManager }.user

The first works, the second fails with

  org.h2.jdbc.JdbcSQLException: Column "USER_ALIAS1_.COMPANY_ID" not found; SQL statement:

  select this_.role_id as role1_10_0_, this_.user_id as user2_10_0_ from user_role this_ where ((user_alias1_.company_id=?) and this_.role_id=?) [42122-164]

why the resistance to using HQL, which is both simple and much clearer than most criteria definitions I read?

• HQL is only slightly better than just writing raw SQL.  Why use Hibernate at all if I have to resort to HQL for simple queries?
• It is made of strings that are either hard-coded or concatenated together.
• An HQL query can't be extended or reused in any meaningful way.
• An HQL query can't be put into a named query or a DetachedCriteria for concatenating complex queries.
• HQL is only easier to read if you like SQL, but it's awful compared to where queries.  If a criteria is that complicated, it probably needs to be broken down into multiple DetachedCriteria or namedQueries and strung together to make it more legible.  ie: User.activeUsers.withRole(Role.administrator).list()
• It's also harder to refactor, although this isn't usually as big of a problem with an IDE.

It's a matter of personal preference, but I see the situation exactly the opposite of you.  Look at the two examples above: I think the where query is clearer, it's definitely shorter, and less error-prone, because of the compile-time validation and in-place arguments.

Criteria are necessary to write namedQueries, but I just realized earlier today that you can just use normal methods to effectively make a "namedQuery" that returns DetachedCriteria.

- Phil DeJarnett

[ideasculptor]

On Fri, May 4, 2012 at 12:41 PM, Phil DeJarnett <[hidden email]> wrote:

Second follow-up:

I've narrowed it down.  It's caused by the composite id.  I manually recreated the same class layout, without any plugins.  If I create the tables without id composite: ['bar', 'baz'], the queries work as expected.  However, if I create them using the mapping, I get the same error.

So, two questions:

1: Would removing the composite ID cause performance problems?

Probably not.  You could use a normal id and then just have normal foreign key relationships.  I can't see how that would have a significant performance impact.  It's a decent workaround.

 

2: Is this a real and genuine bug?

I suspect so.  The next question is whether it is a grails bug or a hibernate bug.  The only obvious way to determine that is to make a little example in plain java code with hibernate mappings and see if the same problem exists.  I tend to start with the assumption that such problems are grails-specific, as hibernate gets such widespread use that it would be next to impossible for such an easy to trigger bug to exist for long without all kinds of issues being raised.  You'd see a lot of hits with a google search if the problem is in hibernate and you are using hibernate code that has been released for more than a week.  However, I've definitely seen some grails devs respond almost immediately to similar problems with "it's a hibernate problem" until proven otherwise. YMMV, but it may be worth proving the case before filing the JIRA if you want a rapid response.

--sam

 

 

Thanks,

- Phil DeJarnett

Phil DeJarnett wrote:

Samuel,

My classes look almost exactly like yours (as they should, since they are generated by the SpringSecurity plugin).  Look at the errors I am getting — they don't make any sense for the queries I'm running.  Hibernate is not joining the tables correctly.

There's definitely something broken, because I just tested, and the HQL below works, while the exact same where query fails:

  UserRole.executeQuery("from UserRole ur where user.company = ? and role = ?", [AppCtx.currentTenant, Role.accountManager]).user

  UserRole.find{ user.company == AppCtx.currentTenant && role == Role.accountManager }.user

The first works, the second fails with

  org.h2.jdbc.JdbcSQLException: Column "USER_ALIAS1_.COMPANY_ID" not found; SQL statement:

  select this_.role_id as role1_10_0_, this_.user_id as user2_10_0_ from user_role this_ where ((user_alias1_.company_id=?) and this_.role_id=?) [42122-164]

• HQL is only slightly better than just writing raw SQL.  Why use Hibernate at all if I have to resort to HQL for simple queries?
• It is made of strings that are either hard-coded or concatenated together.
• An HQL query can't be extended or reused in any meaningful way.
• An HQL query can't be put into a named query or a DetachedCriteria for concatenating complex queries.
• HQL is only easier to read if you like SQL, but it's awful compared to where queries.  If a criteria is that complicated, it probably needs to be broken down into multiple DetachedCriteria or namedQueries and strung together to make it more legible.  ie: User.activeUsers.withRole(Role.administrator).list()
• It's also harder to refactor, although this isn't usually as big of a problem with an IDE.

It's a matter of personal preference, but I see the situation exactly the opposite of you.  Look at the two examples above: I think the where query is clearer, it's definitely shorter, and less error-prone, because of the compile-time validation and in-place arguments.

Criteria are necessary to write namedQueries, but I just realized earlier today that you can just use normal methods to effectively make a "namedQuery" that returns DetachedCriteria.

- Phil DeJarnett

[ideasculptor]

On Fri, May 4, 2012 at 12:48 PM, Samuel Gendler <[hidden email]> wrote:

On Fri, May 4, 2012 at 12:41 PM, Phil DeJarnett <[hidden email]> wrote:

Second follow-up:

I've narrowed it down.  It's caused by the composite id.  I manually recreated the same class layout, without any plugins.  If I create the tables without id composite: ['bar', 'baz'], the queries work as expected.  However, if I create them using the mapping, I get the same error.

So, two questions:

1: Would removing the composite ID cause performance problems?

Probably not.  You could use a normal id and then just have normal foreign key relationships.  I can't see how that would have a significant performance impact.  It's a decent workaround.

And please post here with the details of your final workaround, as I'll probably make the same change in my code in order to avoid having a junior developer bump into this problem 6 months from now, when I've forgotten all about this conversation or am not consulted on why things aren't working.

--sam

[OverZealous]

Samuel Gendler wrote:

And please post here with the details of your final workaround, as I'll probably make the same change in my code in order to avoid having a junior developer bump into this problem 6 months from now, when I've forgotten all about this conversation or am not consulted on why things aren't working.

--sam

Barring any future recommendations, I've just commented out the composite ID with a note in my UserRole class.  This seems to be working fine.

JIRA for bug: http://jira.grails.org/browse/GRAILS-9086

I haven't created a testcase, I'll look into doing that if I get some time later.  I did attach a minimal application showing the error.

- Phil

[Roberto Guerra]

Try this:

Foo.findAll { bar == Bar.findByName('bar') }

On Fri, May 4, 2012 at 2:44 PM, Phil DeJarnett <[hidden email]> wrote:

Samuel Gendler wrote:

And please post here with the details of your final workaround, as I'll probably make the same change in my code in order to avoid having a junior developer bump into this problem 6 months from now, when I've forgotten all about this conversation or am not consulted on why things aren't working.

--sam

Barring any future recommendations, I've just commented out the composite ID with a note in my UserRole class.  This seems to be working fine.

JIRA for bug: http://jira.grails.org/browse/GRAILS-9086

I haven't created a testcase, I'll look into doing that if I get some time later.  I did attach a minimal application showing the error.

- Phil

--
The Journey Is The Reward.

[OverZealous]

Roberto Guerra wrote:

Try this:

Foo.findAll { bar == Bar.findByName('bar') }

Thanks, but that turns it into 2 queries.  Not only is that less efficient, but it doesn't solve the very real bug.  :-\

That example was just a simplistic version to show the issue.  What I really want is more advanced, as I noted earlier in the discussion:

def user = UserRole.where{

user.company == myCompany && role == accountManager

}.get{

projections {

distinct 'user'

}

}

Which (without the bug) should find the account manager for a given company in a single query.  To break it apart, I'd have to return a list of all users for the given company, then run an "in" against the IDs.  At that point, I might as well just loop through the users!  :-)

Thanks, though!

- Phil DeJarnett