I'm experiencing some unexpected behavior when I try to delete an entity in which a JPQL WHERE condition behaves as expected for a SELECT query but not for a DELETE query. Background: I'm using JBoss 4.0.5.GA in the ejb3 configuration with a MySQL 5.0 data source. This problem involves three entities and one stateless session bean. The entities are: | @Entity | public class Account implements Serializable { | @Id | @GeneratedValue(strategy = GenerationType.AUTO) | private Long id; | | @OneToOne(cascade = CascadeType.ALL,fetch=FetchType.EAGER) | @PrimaryKeyJoinColumn | private Profile profile; | | ... | } | | @Entity | public class Profile { | @Id | @GeneratedValue(strategy = GenerationType.AUTO) | private Long id; | | @OneToOne(cascade=CascadeType.ALL, fetch=FetchType.EAGER, mappedBy="profile") | private Account account; | | @Column(unique=true) | private String email; | | ... | } | | @Entity | @Inheritance(strategy=InheritanceType.JOINED) | public class Notification implements Serializable { | | @Id | @GeneratedValue(strategy = GenerationType.AUTO) | private Long id; | | @ManyToOne | private Account recipient; | | ... | } | The problem comes when I try to remove an instance of Notification while applying a filter based on part of the user's login credential (specifically, the email address). The following three code snippets were used in the stateless session bean: | /* Version 1 - Select query with email; then remove */ | Notification n = (Notification) em.createQuery("SELECT object(n) " + | "FROM Notification n " + | "WHERE n.id=:id AND n.recipient.profile.email=:email " | ) | .setParameter("id", id) | .setParameter("email", email) | .getSingleResult(); | em.remove(n); | | /* Version 2 - Delete query with Email */ | int res= em.createQuery("DELETE " + | "FROM Notification n " + | "WHERE n.id=:id AND n.recipient.profile.email=:email " | ) | .setParameter("id", id) | .setParameter("email", email) | .executeUpdate(); | | /* Version 3 - Delete query with Id */ | int res= em.createQuery("DELETE " + | "FROM Notification n " + | "WHERE n.id=:id AND n.recipient.profile.id=:pid" | ) | .setParameter("id", id) | .setParameter("pid", profileId) | .executeUpdate(); | Versions 1 and 3 both work. Version 2 fails with a multi-part exception, the root of which is: | Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'email' in 'where clause' | at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936) | at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870) | at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573) | at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665) | at com.mysql.jdbc.Connection.execSQL(Connection.java:3176) | at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153) | at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1404) | at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1318) | at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1303) | at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:251) | at org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute(MultiTableDeleteExecutor.java:93) | ... 64 more | | The exception seems strange, however, because the email column does exist on the Profile table and because the same WHERE syntax works for the SELECT. Obviously, I can work around this ("Don't use version 2!"), but I'd like to figure out if this is a bug[1] or if I'm doing something wrong. [1] I checked Jira for bugs relating to "executeUpdate" and "MultiTableDeleteExecutor", but I didn't spot anything relevent. View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4008875#... Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&a...