[hibernate-issues] [Hibernate-JIRA] Updated: (ANN-837) @Where clause may trigger an ambiguous column error
Bob Harrod (JIRA)
noreply at atlassian.com
Thu Jun 18 16:40:33 EDT 2009
[ http://opensource.atlassian.com/projects/hibernate/browse/ANN-837?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bob Harrod updated ANN-837:
---------------------------
Attachment: jpaTest.zip
The following test case will showcase the error. Please run the "test" ant task.
The test cases are configured to access a PostgreSQL database. Please see resources\persistence.xml for more information.
As configured, the output will be as follows:
Hibernate:
select
nextval ('sq_login_user')
Hibernate:
select
nextval ('sq_login_unit')
Hibernate:
select
nextval ('sq_login_unit_user')
Hibernate:
insert
into
login_user
(_audit_login_user_id, _created, first_name, last_name, _updated, user_name, id)
values
(?, ?, ?, ?, ?, ?, ?)
Hibernate:
insert
into
login_unit
(_audit_login_user_id, contact_information_id, _created, unit_name, _updated, id)
values
(?, ?, ?, ?, ?, ?)
Hibernate:
insert
into
login_unit_user
(_audit_login_user_id, _created, login_unit_id, login_user_id, _updated, id)
values
(?, ?, ?, ?, ?, ?)
Hibernate:
select
loginunitu0_.id as id1_,
loginunitu0_._audit_login_user_id as column4_1_,
loginunitu0_._created as column2_1_,
loginunitu0_.login_unit_id as login5_1_,
loginunitu0_.login_user_id as login6_1_,
loginunitu0_._updated as column3_1_
from
login_unit_user loginunitu0_
where
(
_is_active <> 0
)
order by
loginunitu0_.id
Hibernate:
select
loginunit0_.id as id0_4_,
loginunit0_._audit_login_user_id as column5_0_4_,
loginunit0_.contact_information_id as contact6_0_4_,
loginunit0_._created as column2_0_4_,
loginunit0_.unit_name as unit3_0_4_,
loginunit0_._updated as column4_0_4_,
loginuser1_.id as id2_0_,
loginuser1_._audit_login_user_id as column7_2_0_,
loginuser1_._created as column2_2_0_,
loginuser1_.first_name as first3_2_0_,
loginuser1_.last_name as last4_2_0_,
loginuser1_._updated as column5_2_0_,
loginuser1_.user_name as user6_2_0_,
loginuser2_.id as id2_1_,
loginuser2_._audit_login_user_id as column7_2_1_,
loginuser2_._created as column2_2_1_,
loginuser2_.first_name as first3_2_1_,
loginuser2_.last_name as last4_2_1_,
loginuser2_._updated as column5_2_1_,
loginuser2_.user_name as user6_2_1_,
contactinf3_.id as id3_2_,
contactinf3_._audit_login_user_id as column7_3_2_,
contactinf3_._created as column2_3_2_,
contactinf3_.email_address as email3_3_2_,
contactinf3_.phone_number as phone4_3_2_,
contactinf3_.point_of_contact_name as point5_3_2_,
contactinf3_._updated as column6_3_2_,
loginuser4_.id as id2_3_,
loginuser4_._audit_login_user_id as column7_2_3_,
loginuser4_._created as column2_2_3_,
loginuser4_.first_name as first3_2_3_,
loginuser4_.last_name as last4_2_3_,
loginuser4_._updated as column5_2_3_,
loginuser4_.user_name as user6_2_3_
from
login_unit loginunit0_
left outer join
login_user loginuser1_
on loginunit0_._audit_login_user_id=loginuser1_.id
left outer join
login_user loginuser2_
on loginuser1_._audit_login_user_id=loginuser2_.id
left outer join
contact_information contactinf3_
on loginunit0_.contact_information_id=contactinf3_.id
left outer join
login_user loginuser4_
on contactinf3_._audit_login_user_id=loginuser4_.id
where
loginunit0_.id=?
and (
_is_active <> 0
)
E
Time: 1.453
There was 1 error:
1) testAddAndQuery(jpa.model.LoginUnitTest)javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not load an entity: [jpa.model.LoginUnit#1]
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:614)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:76)
at jpa.model.LoginUnitTest.testAddAndQuery(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
Caused by: org.hibernate.exception.SQLGrammarException: could not load an entity: [jpa.model.LoginUnit#1]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1895)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:71)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:65)
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3072)
at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:434)
at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:415)
at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:165)
at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:223)
at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:126)
at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:905)
at org.hibernate.impl.SessionImpl.internalLoad(SessionImpl.java:873)
at org.hibernate.type.EntityType.resolveIdentifier(EntityType.java:590)
at org.hibernate.type.EntityType.resolve(EntityType.java:412)
at org.hibernate.engine.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:139)
at org.hibernate.loader.Loader.initializeEntitiesAndCollections(Loader.java:877)
at org.hibernate.loader.Loader.doQuery(Loader.java:752)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
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.getResultList(QueryImpl.java:67)
... 16 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.loadEntity(Loader.java:1881)
... 41 more
FAILURES!!!
> @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: jpaTest.zip, 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
More information about the hibernate-issues
mailing list