Delete SQL with table alias in named query

[epbernard]

This is a current limitation of Hibernate. Actually, afaik alias are not useful in delete cases (at least the one you point)

[sebersole]

Yes, Hibernate currently does not support aliases on update and/or delete statements. Gavin is asking for clarification on this point within the spec committee.

As Emmanuel points out, aliases are meant (in SQL as well as HQL/EJBQL) to differentiate between multiple object references. This is cmpletely unecessary in EJBQL update and delete statements since they only allow a single entity reference to be deleted from. The alias is completely superfluous here.

Now if the clarification here comes back that we need to support it, then we will...

[sebersole]

As a clarification, aliases would be needed for certain correlated subqueries.

Basically this section of the draft is pretty sketchy and I simply implemented it the way it was easiest for us to do within Hibernate as-is, until the expectations here become better defined.

Another thing that needs clarification here is exactly what constitutes a "new_value" in the "BNF" (term used generously) proposed in the draft for updates? What exactly is allowed here?

Etc...

[kkoster]

Thanks for the reply. FYI, this was not an attack on the implementation within the JBoss container. It was just a comment on seeming discrepencies of the implementation with the spec. As I said, for the moment, this is an acceptable limitation. For most delete operations this will not create an issue. But, as you have already pointed out, any delete operation that would require a correlated subquery in the WHERE clause would benefit from this.

Also, I didn't find Draft 3 of the spec to sketchy on this point. Here is the quote from the text (pp. 72, 73) that supports this:

"Bulk update and delete operations apply to entities of a single entity bean class (together with its subclasses, if any). Only one entity bean abstract schema type may be specified in the FROM or UPDATE clause.

The syntax of these operations is as follows:

update_statement ::= update_clause [where_clause]
update_clause ::= UPDATE abstract_schema_name [[AS] identification_variable]
SET [identification_variable].state_field = new_value
{, [identification_variable.]state_field = new_value}*

delete_statement ::= delete_clause [where_clause]
delete_clause ::= DELETE FROM abstract_schema_name [[AS] identification_variable]

The syntax of the WHERE clause is described in Section 3.5.
Portable applications should not perform bulk update and delete operations within a transaction that has previously accessed entity instances whose state could be affected by such operations.

Examples:

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

DELETE FROM Customer c WHERE c.status = ?inactive? AND c.orders IS EMPTY UPDATE customer c SET c.status = ?outstanding? WHERE c.balance < 10000 AND 1000 > (SELECT COUNT(o) FROM customer cust JOIN cust.order o)"

Is there a more current version of the spec I should be reviewing?

As far as the 'new_value' referred to in the UPDATE statement, it would seem to be implicit that it must resolve to a value that is equivalent in type to the state field to which it is being assigned. This could be a constant or the value bound to a paramater.

Regards,
Karl

[sebersole]

As far as the 'new_value' referred to in the UPDATE statement, it would seem to be implicit that it must resolve to a value that is equivalent in type to the state field to which it is being assigned. This could be a constant or the value bound to a paramater.

But see you use words like "seem" and "could". Well certainly it needs to resolve to the type.

This could be a constant or the value bound to a paramater.

So not an arithmetic expression? Not a function call? Not a reference to another state_field? Hibernate lets you do all these currently, btw. But your interpretation of the spec means we should rip out that capability.

As far as the aliases go, those examples still do not show an example needing aliases at all, because none of them use correlated subqueries. Even worse, they reuse the same alias in the subqueries as used in the outer queries, which is completely bad form. So we just have a feeling that this part of the spec has not been looked at hard in total, and are waiting to make sure that the "BNF" is truly the intended ambiguity level, or whether this portion will now get some review/revision.

[kkoster]

Steve, chill out. Read the prior post. This is not an attack on the implementation, geeze. I am just trying to work out what seem to be discrepencies between what the current implementation is and what the spec describes.

I am not even using UPDATE's at this point. The 'new_value' issue was brought up by you. I am not trying to be confrontational here. I also fully understand that the spec is in a state of flux.

What I have to go by is the Draft documentation and some implementation documentation from the JBoss site. When I run into a discrepency, I query this forum. If the answer is "It is what it is", fine. I have no problem with it.

Are the examples in the Draft contrived and poorly conceived? Sure they are. So what? The examples are not the spec. They are mearly there to aid in visualization of usage. The spec, at this point, says something is available, the implementation doesn't support it. Is this a big deal? No, the spec is not finalized and there are trivial work arounds for the discrepency.

Noting this discrepency on this forum is simply a request for guidance as to the direction the implementation is taking with respect to the spec in its current form. That's it.

I don't think you will find any statements in my post that says JBoss's implementation of the spec "should" or "should not" do something. Merely that I percieve a discrpency with the spec and am looking for clarification.

So, relax. Hibernate is a great product. The annotation support is a great acheivement. The EJB3 spec is looking like it will pose serious competition to other vendor specific ORM implementations. Life is good.

Regards,
Karl

[sebersole]

Not at all trying to be argumentative, I apologize if thats how it came across. I'm just trying to explain that there are ambiguities in the spec as-is.

[bill.burke]

Karl,

Can you put together two things?

1) What JBoss EJB3/Hibernate annotations is lacking in regards to the spec.

2) What EJB3 EDR2 specification is lacking EXACTLY and submit it to [e-mail]? It will help the Expert Group flush out problems regarding UPDATE/DELETE.

Thanks