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

@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 ?