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

[Joseph Marques (JIRA)]

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


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.

-- 
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