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

[Bob Harrod (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/ANN-837?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=33437#action_33437 ] 

Bob Harrod commented on ANN-837:
--------------------------------

What would you like - are java objects enough or do you need build file, sql scripts?  

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