[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3476) Generation of invalid SQL by HQL Delete

Generation of invalid SQL by HQL Delete --------------------------------------- Key: HHH-3476 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3476 Project: Hibernate Core Issue Type: Bug Components: core Affects Versions: 3.1.3, 3.2.6 Environment: MS SQL Server 2000, Hibernate 3.1.3 and Hibernate 3.2.6 GA Reporter: Daniel Nunes Attachments: grupo.rar, hibernate.cfg.xml Hibernate generates invalid DML command for HQL Delete statement. When doing: Integer vals[] = {1,3}; Query q = ugDAO.createQuery("delete UsuarioGrupo usuariorGrp"+ " where usuariorGrp.usuario.idusuario = :idUsr"+ " and usuariorGrp.grupo.idgrupo in (:idsGrupo)"+ " and usuariorGrp.grupo.cxpostal.idcxpostal = :idCx"); q.setParameterList("idsGrupo", vals); q.setInteger("idUsr", 77); q.setInteger("idCx", 2); It generates an SQL like this: Hibernate: delete from vanpix.dbo.usuario_grupo, vanpix.dbo.grupo grupo1_ where idusuario=? and (idgrupo in (? , ?)) and idcxpostal=? 17:26:22,406 DEBUG IntegerType:133 - binding '77' to parameter: 1 17:26:22,406 DEBUG IntegerType:133 - binding '1' to parameter: 2 17:26:22,406 DEBUG IntegerType:133 - binding '3' to parameter: 3 17:26:22,406 DEBUG IntegerType:133 - binding '2' to parameter: 4 17:26:22,453 WARN JDBCExceptionReporter:77 - SQL Error: 170, SQLState: 37000 17:26:22,453 ERROR JDBCExceptionReporter:78 - Line 1: Incorrect syntax near ','. Throwing the exception: Exception in thread "main" 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 com.pix.tests.HibernateTests.main(HibernateTests.java:37) Caused by: java.sql.SQLException: Line 1: Incorrect syntax near ','. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254) at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631) at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584) at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546) at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:505) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105) at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:75) ... 5 more The HBMs are in the attachment.