Hibernate version: 3.0.5

I just had a developer come to me attempting to do a query something like this:

This doesn't seem to work because of the path operation in the where clause (note, Address has a many-to-one association through its 'owner' property to a Person object -- a much simplified example, but it illustrates the point).

At first glance, I didn't understand why. Then I started considering what the SQL would look like. Maybe something like this:

The solution, of course, is that I should mimic this in my HQL:

I tried to look this up in the documentation, and I noticed this:

So here's my questions:
1) Does using a path (owner.lastName) in a bulk delete where clause constitute an "implicit join" (which I assume it does)?
2) Would it be possible to more explicitly state this in section 14.3 of the reference? The text was (at least to me) not immediately obvious until after I had run into the problem.
3) Is this likely (or even possible) to become a feature in a later version of hibernate?

Should be

According to the reference manual here: http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#batch-direct,


Nonetheless, I tried a similar query anyway:


and got this:

You are right, my bad. Well Hibernate doc also says


That explains why query does not work. So you I guess you have to use SQL

Well, not quite. As I mentioned earlier, I can write some HQL that works that is more like the SQL:

So I don't have to resort to direct SQL.

Really, what I had in mind here, though, was to feel out whether the other syntax might be a reasonable feature. To wit:

I think we've established that using property paths in the where-clause of an HQL bulk delete doesn't work. The solution in my case was that it had to be written as an IN constraint using a subselect.

Thinking through things, I understand now why HQL does not support paths in the where-clause, but because I'm used to using paths in select statements, it never occurred to me that they wouldn't work in a bulk delete. I believe this is what the documentation means when it refers to "implicit joins":

However, the documentation did not really make this obvious to me, and I didn't really realize the implications of the quoted text until after I ran into the problem. If nothing else comes of this thread, I would ask that whoever maintains the reference consider adding a bit more text to explicitly discuss property paths -- perhaps with an example or two.

The other goal I had was to feel out whether this could ever be a hibernate feature. I understand that it could be rather complex, as hibernate would have to know how to convert something like this:

Into SQL that uses a subquery like this:

You don't have to be a ORM expert to see that something like this might grow fairly complex fairly quickly. But if it could be done, I feel it would be a very useful addition to the HQL language. After all, my initial intuition was to use property paths to achieve the desired results. You can really see where this would be powerful in more complex situations. Consider:

The SQL for this would not be simple, but the HQL cetainly is!

That said, though, I'm not in the habit of just tossing feature requests into a project's tracking software before I know whether the change is feasible (or in this case, even possible). In fact, I usually prefer to let someone who is more involved in the project make the decision of whether it should be considered for a new feature altogether

thoughts?

There was discussion of this as I implemented the bulk features. The basic reason for not currently allowing it is simply that alot of databases do not allow joins in delete statements (otherwise we could simply pass off the join onto the db). Now, of course, we could "interpret" the intent of the join here and munge the resulting SQL into a subquery form as you suggest, but that does not seem reasonable to me given that you can quite easily just write the query using the subquery form in the first place. Certainly, I can add some more discussion of what "implicit join" means to the docs if you really think that helps.

Hmmm. I fear that even "munging" (a good word!) into a subquery form would be dangerously full of subtle complications that would make it impossible to reliably do with all databases and valid HQL queries. Nonetheless, I still think this would be a beautiful feature to add to HQL. I do, of course, understand why it isn't though. Consider it a wish-list item that'll probably never happen :-)

I do think it would be great of the docs could be clarified a bit. They are clear to me now that I understand what's happening under the covers of Hibernate, but I didn't really think of owner.lastName = ? as a join at all, but rather a where-clause conditional. This of course, was shortsighted on my part, but if the docs warned me of the pothole, maybe I could have avoided the "smack-myself-in-the-head-and-say-of-course!" moment :-)

Thanks for the clarification!

No problem. Considering we use the term "implicit join" quite a bit, I'll add a section about implicit vs. explicit joins in the HQL chapter and appropriately link to it from the HQL-bulk section.

http://opensource2.atlassian.com/projects/hibernate/browse/HHH-989

That would be excellent. Thanks again, Steve.