[hibernate-issues] [Hibernate-JIRA] Created: (EJB-379) org.hibernate.exception.SQLGrammarException when trying to delete THIS where THIS.relation[manyToOne].member=:givenMember

[Martin Makundi (JIRA)]

org.hibernate.exception.SQLGrammarException when trying to delete THIS where THIS.relation[manyToOne].member=:givenMember
-------------------------------------------------------------------------------------------------------------------------

                 Key: EJB-379
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/EJB-379
             Project: Hibernate Entity Manager
          Issue Type: Bug
    Affects Versions: 3.3.2.GA
         Environment: MySQL 5 or newer
            Reporter: Martin Makundi
            Priority: Critical


The delete fails and throws org.hibernate.exception.SQLGrammarException when trying to issue delete:

createQuery("DELETE FROM " 
+ TheClassIWantToDelete.class.getSimpleName() + " mhl WHERE " 
          + "mhl.theBridgingClass.theSelectingClass=:theSelectingClass" 
          + " AND (mhl.value=0 OR mhl.value IS NULL)")
          .setParameter("theSelectingClass", theSelectingClass);

I have three Entities:

@Entity
public class TheClassIWantToDelete{
    @Id Long id;
    @ManyToOne(optional=false)
    private TheBridgingClass theBridgingClass;
    @Column
    private Integer value;
   ...
}

@Entity
public class TheBridgingClass{
    @Id Long id;
    @ManyToOne(optional=false)
    private TheSelectingClass theSelectingClass;
   ...
}

@Entity
public class TheSelectingClass{
    @Id Long id;
    ...
}

The result is (trace):
Hibernate: delete from TheClassIWantToDelete, TheBridgingClass theBridgingCl1_ where theSelectingClass_id=? and (value=0 or value is null)
2008-08-31 12:00:00,912 59846 [btpool0-3] ERROR JDBCExceptionReporter  - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where theSelectingClass_id=5 and (value=0 or value is null)' at line 1

java.lang.reflect.InvocationTargetException
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute update query
	at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:637)
	at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:58)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute update query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
	at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:84)
	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)
	... 38 more
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where project_id=5 and (monthlyHoursLimit=0 or monthlyHoursLimit is null)' at line 1
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2941)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1541)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1455)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1440)
	at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:75)
	... 43 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