1 Reply Latest reply on Jan 31, 2007 9:36 PM by Bustanil Arifin

    JPQL WHERE syntax works for SELECT but not DELETE

    Tim Otten Newbie

      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.