[#HHH-374] EJB3 example delete query doesn't work in Hibernate.

Hibernate Core

EJB3 example delete query doesn't work in Hibernate.

Created: 19/Apr/05 04:40 PM Updated: 15/Jul/05 09:10 AM

Component/s:

core

Affects Version/s:

3.0 final

Fix Version/s:

3.1 beta 1

Time Tracking:

Not Specified

Description

« Hide

Here's the first example of a delete query in the EJB3 Persistence API spec (second public draft):

DELETE
FROM Customer c
WHERE c.status = 'inactive'
AND (NOT EXISTS
(SELECT c FROM Customer c JOIN c.orders))

This query does not work in Hibernate 3 because support for aliases in bulk queries were dropped during development. This similar query probably would work:

DELETE
FROM Customer
WHERE status = 'inactive'
AND (NOT EXISTS
(SELECT c FROM Customer c JOIN c.orders))

All

Comments

Work Log

Change History

FishEye

Sort Order:

[ Permalink | « Hide ]

Gavin King added a comment - 19/Apr/05 05:01 PM

OK, I thought this was a buggy example until I checked the BNF, and there is indeed an optional alias in the definition of DELETE. I'm not sure how that snuck in there. Since that is not a standard part of SQL, we should change the EJB-QL spec.

I'll see about getting that changed in the spec.

[ Permalink | « Hide ]

Robert Watkins added a comment - 19/Apr/05 06:03 PM

*rofl* Fair enough. :)

[ Permalink | « Hide ]

Christian Bauer added a comment - 20/Apr/05 05:25 AM

It would be great if you'd stop linking to your blog on our systems.

[ Permalink | « Hide ]

Christian Bauer added a comment - 20/Apr/05 05:33 AM

Actually, how can I turn this feature of in JIRA? I've never seen it before...

[ Permalink | « Hide ]

Christian Bauer added a comment - 20/Apr/05 05:37 AM

OK, got it. Stupid kiddie trackback stuff. I wonder what they'll invent next year... newsgroups maybe.

[ Permalink | « Hide ]

Robert Watkins added a comment - 20/Apr/05 06:03 AM

Tell me about it; it's a combination of the trackback feature of JIRA, and the auto-discover-trackbacks-to-ping feature of Moveable Type.

Nuke the links if you want; I'll try and stop them in the future.

[ Permalink | « Hide ]

Gavin King added a comment - 20/Apr/05 10:07 AM

I've gone back to the EJB group on this one, and apparently no-one agrees with me that aliases should not be supported in DELETE and UPDATE. So we need to add support for this.

[ Permalink | « Hide ]

Steve Ebersole added a comment - 20/Apr/05 12:19 PM

Another thing we need to consider is deletes against an entity participating in a many-to-many. There'd never be a way to bulk delete these (provided the association table contained actual FKs)...

[ Permalink | « Hide ]

Gavin King added a comment - 20/Apr/05 02:19 PM

I agree that this is difficult in general, and my understanding is that everyone considers this is out of the scope of the bulk delete feature. There is now some active discussion on this stuff in the group.

[ Permalink | « Hide ]

Robert Watkins added a comment - 20/Apr/05 04:31 PM

At the least, Gavin, I think the lifecycle needs to become explicit. From a Hibernate perspective, I've also got concerns (though I haven't had a chance to prove or disprove) about the impact on objects in the second-level cache; we already have issues where external database calls can corrupt our cache because the objects are deleted, and I'd hate to think that the bulk delete has this effect as well.

[ Permalink | « Hide ]

Steve Ebersole added a comment - 20/Apr/05 05:16 PM

Currently it does not clean up the second level cache. However, I have a JIRA task already to implement this.

http://opensource.atlassian.com/projects/hibernate/browse/HHH-352

[ Permalink | « Hide ]

Steve Ebersole added a comment - 20/Apr/05 05:18 PM

Also note that its easy enough for you to do this manually in the meantime, using SessionFactory.evict()

[ Permalink | « Hide ]

Steve Ebersole added a comment - 27/Jun/05 09:39 AM

from-element aliases are now supported as optional in the bulk dml operations

[ Permalink | « Hide ]

Steve Ebersole added a comment - 15/Jul/05 09:10 AM

Just to clarify a bit further. As long as only one "from element" appears in the from clause, aliases are now completely optional in any hql statement, whether DML-style statement or select statement.