[hibernate-issues] [Hibernate-JIRA] Created: (ANN-837) @Where clause may trigger an ambiguous column error

[Bob Harrod (JIRA)]

@Where clause may trigger an ambiguous column error
---------------------------------------------------

                 Key: ANN-837
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/ANN-837
             Project: Hibernate Annotations
          Issue Type: Bug
          Components: binder
    Affects Versions: 3.3.1.GA
         Environment: Hibernate Core 3.3.1GA, Annotations 3.4.0GA, PostgreSQL 8.3
            Reporter: Bob Harrod
         Attachments: LoginUnit.java, LoginUnitUser.java, LoginUser.java

The following annotations are being used to simulate a soft delete strategy:

@Entity
@Table(name = "login_unit_user")
@SQLDelete( sql="UPDATE login_unit_user SET _is_active = 0 WHERE id = ?")
@Where(clause="_is_active <> 0")
public class LoginUnitUser extends DomainObject implements Serializable{...}

and

@Entity
@Table(name = "login_unit")
@SQLDelete( sql="UPDATE login_unit SET _is_active = 0 WHERE id = ?")
@Where(clause="_is_active <> 0")
public class LoginUnit extends DomainObject implements Serializable{...}

In the attached code, both LoginUnit and LoginUnitUser contain a system column - "_is_active".  This column represents a flag which indicates whether or not the row is "active" or "deleted".  When these two hibernate model objects are queried, hibernate does not properly append the table alias to this additional where clause that it uses during it's database query.  The error raised is:

org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.loader.Loader.doList(Loader.java:2231)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
	at org.hibernate.loader.Loader.list(Loader.java:2120)
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
	at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361)
	at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148)
	at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
	at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:88)
	... 35 more
Caused by: org.postgresql.util.PSQLException: ERROR: column reference "_is_active" is ambiguous
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1608)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1343)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:194)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
	at org.hibernate.loader.Loader.doQuery(Loader.java:697)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
	at org.hibernate.loader.Loader.doList(Loader.java:2228)
	... 43 more


An example query with the hibernate generated aliases (loginuser0_, loginunitu1_, loginunit2_) is below:

Hibernate:
    select
        loginuser0_.id as id21_,
        loginuser0_._audit_login_user_id as column7_21_,
        loginuser0_._created as column2_21_,
        loginuser0_.first_name as first3_21_,
        loginuser0_.last_name as last4_21_,
        loginuser0_._updated as column5_21_,
        loginuser0_.user_name as user6_21_
    from
        login_user loginuser0_,
        login_unit_user loginunitu1_,
        login_unit loginunit2_
    where
        (
            _is_active <> 0
        )
        and (
            _is_active <> 0
        )
        and loginuser0_.id=loginunitu1_.login_user_id
        and loginunitu1_.login_unit_id=loginunit2_.id
        and loginuser0_.user_name=?
        and loginunit2_.id=? limit ?

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira