9:52 a.m.
@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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira