[hibernate-issues] [Hibernate-JIRA] Resolved: (HHH-2812) DELETE hql statement generating bad SQL on postgres 8.2

DELETE hql statement generating bad SQL on postgres 8.2 ------------------------------------------------------- Key: HHH-2812 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2812 Project: Hibernate3 Issue Type: Bug Components: query-hql Affects Versions: 3.2.3 Environment: JBoss [Trinity] 4.2.0.GA (build: SVNTag=JBoss_4_2_0_GA date=200705111440) Reporter: Joseph Marques Assignee: Diego Pires Plentz Here's is an object model: class Alert { int id; ... AlertDefinition alertDefinition; ... } class AlertDefinition { int id; ... Resource resource; ... } class Resource { int id; ... } Here is some incredibly simple hql: DELETE Alert AS alert WHERE alert.alertDefinition.resource.id = 0 And this is what it translates to on postgres: DELETE FROM alert, alert_definition WHERE resource_id=0 Unless a user knows that this doesn't translate correctly, this is the error they will get when they try to execute it: WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: 42601 ERROR [JDBCExceptionReporter] ERROR: syntax error at or near "," ERROR ... javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute update query Though postgres does support this...in an albeit awkward form. You can only select from a single table, but you can use a list of "related" tables (http://www.postgresql.org/docs/8.1/static/sql-delete.html). So, it should have looked something like: DELETE FROM alert USING alert_definition WHERE resource_id=0 As the documentation specifies, it's possible to use a more common sub-select syntax like: DELETE FROM alert WHERE id IN ( SELECT alert1.id FROM alert a1, alert_definition ad1 WHERE a1.alert_definition_id = ad1.id AND ad1.resource_id = 0 ) And here's the corresponding hql that I ended up using to achieve this: DELETE Alert AS a WHERE a.id IN ( SELECT ia.id FROM Alert ia WHERE ia.alertDefinition.resource.id = 0 ) However, I still think the query translator should support the join syntax instead of forcing the sub-select syntax.