[hibernate-issues] [Hibernate-JIRA] Commented: (EJB-355) query generates invalid sql: Column 'id' in where clause is ambiguous

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