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

[Diego Pires Plentz (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2812?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Diego Pires Plentz resolved HHH-2812.
-------------------------------------

      Assignee: Diego Pires Plentz
    Resolution: Duplicate

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

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