[ http://opensource.atlassian.com/projects/hibernate/browse/EJB-355?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Gail Badner resolved EJB-355.
-----------------------------
Assignee: Gail Badner
Resolution: Duplicate
> query generates invalid sql: Column 'id' in where clause is ambiguous
> ---------------------------------------------------------------------
>
> Key: EJB-355
> URL: http://opensource.atlassian.com/projects/hibernate/browse/EJB-355
> Project: Hibernate Entity Manager
> Issue Type: Bug
> Components: EntityManager
> Affects Versions: 3.3.2.GA
> Environment: MySQL 5.0.1
> Reporter: sagi mann
> Assignee: Gail Badner
>
> Entities:
> @Entity
> @Inheritance(strategy=InheritanceType.JOINED)
> //@DiscriminatorColumn(name="classname", discriminatorType=DiscriminatorType.STRING, length=255)
> public abstract class JoinedBaseClass implements Serializable {
>
> @Id
> @GeneratedValue
> public Long getId() { return this.id; }
> public void setId(Long id) { this.id = id; }
> private Long id;
>
> }
> @Entity
> //@DiscriminatorValue(value="joinedsubclass1")
> public class JoinedSubClass1 extends JoinedBaseClass {
>
> @Basic
> public String getSubProp1() { return bp1; }
> public void setSubProp1(String bp) { this.bp1 = bp; }
> private String bp1;
> }
>
> Code in EJB
> em.createQuery("delete from JoinedSubClass1 c where c.id = 3")
> .executeUpdate();
> The generated SQL (from MySQL trace file):
> create temporary table if not exists HT_JoinedSubClass1 (id bigint not null)
> insert into HT_JoinedSubClass1 select joinedsubc0_.id as id from JoinedSubClass1 joinedsubc0_ inner join JoinedBaseClass joinedsubc0_1_ on joinedsubc0_.id=joinedsubc0_1_.id where id=3
>
> Note the final 'where' clause. Shouldn't it be where joinedsubc0_.id=3 instead of where id=3 ? When I manually tried to replace the where clause (just to see if the query gives correct output), it works... but Hibernate insists on generating an invalid query. I'm not sure what I need to do in order to instruct Hibernate to generate the correct syntax - this could be a bug.
> Full stack trace of any exception that occurs:
> SQL Error: 1052, SQLState: 23000
> Column 'id' in where clause is ambiguous
> EJB5018: An exception was thrown during an ejb invocation on [TestBean]
> javax.ejb.EJBException
> at com.sun.ejb.containers.BaseContainer.processSystemException(BaseContainer.java:3869)
> at com.sun.ejb.containers.BaseContainer.completeNewTx(BaseContainer.java:3769)
> at com.sun.ejb.containers.BaseContainer.postInvokeTx(BaseContainer.java:3571)
> at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:1354)
> at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:1316)
> at com.sun.ejb.containers.EJBObjectInvocationHandler.invoke(EJBObjectInvocationHandler.java:210)
> at com.sun.ejb.containers.EJBObjectInvocationHandlerDelegate.invoke(EJBObjectInvocationHandlerDelegate.java:117)
> at $Proxy87.removeSubClass1Bulk(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)
> at java.lang.reflect.Method.invoke(Method.java:585)
> at com.sun.corba.ee.impl.presentation.rmi.ReflectiveTie._invoke(ReflectiveTie.java:154)
> at com.sun.corba.ee.impl.protocol.CorbaServerRequestDispatcherImpl.dispatchToServant(CorbaServerRequestDispatcherImpl.java:687)
> at com.sun.corba.ee.impl.protocol.CorbaServerRequestDispatcherImpl.dispatch(CorbaServerRequestDispatcherImpl.java:227)
> at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequestRequest(CorbaMessageMediatorImpl.java:1846)
> at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:1706)
> at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleInput(CorbaMessageMediatorImpl.java:1088)
> at com.sun.corba.ee.impl.protocol.giopmsgheaders.RequestMessage_1_2.callback(RequestMessage_1_2.java:223)
> at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:806)
> at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.dispatch(CorbaMessageMediatorImpl.java:563)
> at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.doWork(CorbaMessageMediatorImpl.java:2567)
> at com.sun.corba.ee.impl.orbutil.threadpool.ThreadPoolImpl$WorkerThread.run(ThreadPoolImpl.java:555)
> Caused by: javax.persistence.EntityExistsException: org.hibernate.exception.ConstraintViolationException: could not insert/select ids for bulk delete
> at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:612)
> at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:58)
> at pu1.TestBean.removeSubClass1Bulk(TestBean.java:55)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:585)
> at com.sun.enterprise.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1067)
> at com.sun.enterprise.security.SecurityUtil.invoke(SecurityUtil.java:176)
> at com.sun.ejb.containers.BaseContainer.invokeTargetBeanMethod(BaseContainer.java:2895)
> at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:3986)
> at com.sun.ejb.containers.EJBObjectInvocationHandler.invoke(EJBObjectInvocationHandler.java:203)
> ... 17 more
> Caused by: org.hibernate.exception.ConstraintViolationException: could not insert/select ids for bulk delete
> at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
> at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
> at org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute(MultiTableDeleteExecutor.java:102)
> at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:396)
> at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
> at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141)
> at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
> at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:49)
> ... 27 more
> Caused by: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Column 'id' in where clause is ambiguous
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1011)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
> at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
> at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)
> at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)
> at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)
> at org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute(MultiTableDeleteExecutor.java:93)
> ... 32 more
--
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