[hibernate-issues] [Hibernate-JIRA] Created: (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
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira