Hibernate appears to generate invalid SQL for delete queries on Informix.
The generated SQL uses table aliases, which are not valid in Informix delete queries.
Hibernate version: CVS HEAD
Mapping documents:N/A
Code between sessionFactory.openSession() and session.close():
Code:
Transaction transaction = session.beginTransaction();
Query query = session.createQuery("delete from com.axxia.pkc.plan.afcl.domain.Workflow workflow where workflow.processInstanceId=1");
query.executeUpdate();
Full stack trace of any exception that occurs:
14:11:58,721 DEBUG AbstractBatcher:343 - preparing statement
14:11:58,971 DEBUG JDBCExceptionReporter:49 - SQL Exception
java.sql.SQLException: A syntax error has occurred.
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:355)
at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3086)
at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3396)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2259)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2179)
at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1082)
at com.informix.jdbc.IfxResultSet.executePrepare(IfxResultSet.java:182)
at com.informix.jdbc.IfxPreparedStatement.setupExecutePrepare(IfxPreparedStatement.java:197)
at com.informix.jdbc.IfxPreparedStatement.<init>(IfxPreparedStatement.java:175)
at com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java:1907)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:351)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:73)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:66)
at org.hibernate.hql.ast.UpdateStatementExecutor.execute(UpdateStatementExecutor.java:60)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:294)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:808)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:89)
at com.axxia.util.test.EntityManagerTest.setUp(EntityManagerTest.java:62)
at junit.framework.TestCase.runBare(TestCase.java:125)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:421)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:305)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:186)
14:11:58,987 WARN JDBCExceptionReporter:57 - SQL Error: -201, SQLState: 42000
14:11:58,987 ERROR JDBCExceptionReporter:58 - A syntax error has occurred.
14:11:59,002 DEBUG JDBCExceptionReporter:49 - could not execute update query [delete from workflow workflow0_ where workflow0_.process_instance_id=1]
java.sql.SQLException: A syntax error has occurred.
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:355)
at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3086)
at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3396)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2259)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2179)
at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1082)
at com.informix.jdbc.IfxResultSet.executePrepare(IfxResultSet.java:182)
at com.informix.jdbc.IfxPreparedStatement.setupExecutePrepare(IfxPreparedStatement.java:197)
at com.informix.jdbc.IfxPreparedStatement.<init>(IfxPreparedStatement.java:175)
at com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java:1907)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:351)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:73)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:66)
at org.hibernate.hql.ast.UpdateStatementExecutor.execute(UpdateStatementExecutor.java:60)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:294)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:808)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:89)
at com.axxia.util.test.EntityManagerTest.setUp(EntityManagerTest.java:62)
at junit.framework.TestCase.runBare(TestCase.java:125)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:421)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:305)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:186)
14:11:59,018 WARN JDBCExceptionReporter:57 - SQL Error: -201, SQLState: 42000
14:11:59,018 ERROR JDBCExceptionReporter:58 - A syntax error has occurred.
Name and version of the database you are using:
Informix Dynamic Server 9.40.TC4
The generated SQL (show_sql=true):
delete from workflow workflow0_ where workflow0_.process_instance_id=1
Debug level Hibernate log excerpt:
14:11:58,612 DEBUG QueryTranslatorImpl:206 - parse() - HQL: delete from com.axxia.pkc.plan.afcl.domain.Workflow workflow where workflow.processInstanceId=1
14:11:58,627 DEBUG AST:226 - --- HQL AST ---
\-[DELETE] CommonAST: 'delete'
+-[FROM] CommonAST: 'implied-from-so-i-can-use-the-fromClause-rule-during-analysis-phase'
| +-[DOT] CommonAST: '.'
| | +-[DOT] CommonAST: '.'
| | | +-[DOT] CommonAST: '.'
| | | | +-[DOT] CommonAST: '.'
| | | | | +-[DOT] CommonAST: '.'
| | | | | | +-[DOT] CommonAST: '.'
| | | | | | | +-[IDENT] CommonAST: 'com'
| | | | | | | \-[IDENT] CommonAST: 'axxia'
| | | | | | \-[IDENT] CommonAST: 'pkc'
| | | | | \-[IDENT] CommonAST: 'plan'
| | | | \-[IDENT] CommonAST: 'afcl'
| | | \-[IDENT] CommonAST: 'domain'
| | \-[IDENT] CommonAST: 'Workflow'
| \-[ALIAS] CommonAST: 'workflow'
\-[WHERE] CommonAST: 'where'
\-[EQ] CommonAST: '='
+-[DOT] CommonAST: '.'
| +-[IDENT] CommonAST: 'workflow'
| \-[IDENT] CommonAST: 'processInstanceId'
\-[NUM_INT] CommonAST: '1'
14:11:58,643 DEBUG FromElement:79 - com.axxia.pkc.plan.afcl.domain.Workflow (workflow) -> workflow0_
14:11:58,643 DEBUG FromReferenceNode:48 - Resolved : workflow -> workflow0_.id
14:11:58,659 DEBUG DotNode:441 - getDataType() : processInstanceId -> org.hibernate.type.LongType@ae97c4
14:11:58,659 DEBUG FromReferenceNode:48 - Resolved : workflow.processInstanceId -> workflow0_.process_instance_id
14:11:58,690 DEBUG AST:226 - --- SQL AST ---
\-[DELETE] QueryNode: 'delete' querySpaces (workflow)
+-[FROM] FromClause: 'implied-from-so-i-can-use-the-fromClause-rule-during-analysis-phase' FromClause{implied-from-so-i-can-use-the-fromClause-rule-during-analysis-phase}
| \-[FROM_FRAGMENT] FromElement: 'workflow workflow0_' FromElement{explicit,not a collection join,classAlias=workflow,role=null,tableName=workflow,tableAlias=workflow0_,colums={,className=com.axxia.pkc.plan.afcl.domain.Workflow}}
\-[WHERE] SqlNode: 'where'
\-[EQ] SqlNode: '='
+-[DOT] DotNode: 'workflow0_.process_instance_id' {propertyName=processInstanceId,dereferenceType=4,propertyPath=processInstanceId,path=workflow.processInstanceId,tableAlias=workflow0_,className=com.axxia.pkc.plan.afcl.domain.Workflow,classAlias=workflow}
| +-[ALIAS_REF] IdentNode: 'workflow0_.id' {alias=workflow, className=com.axxia.pkc.plan.afcl.domain.Workflow, tableAlias=workflow0_}
| \-[IDENT] IdentNode: 'processInstanceId' {originalText=processInstanceId}
\-[NUM_INT] LiteralNode: '1'
14:11:58,690 DEBUG QueryTranslatorImpl:177 - HQL: delete from com.axxia.pkc.plan.afcl.domain.Workflow workflow where workflow.processInstanceId=1
14:11:58,690 DEBUG QueryTranslatorImpl:178 - SQL: delete from workflow workflow0_ where workflow0_.process_instance_id=1
14:11:58,721 DEBUG AbstractBatcher:258 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
14:11:58,721 DEBUG SQL:292 - delete from workflow workflow0_ where workflow0_.process_instance_id=1
Hibernate: delete from workflow workflow0_ where workflow0_.process_instance_id=1