[hibernate-dev] 6.0 - multi-table mutations

[Steve Ebersole]

While working on 6 I discovered that we maybe do not do the best job in
regards to "bulk update/delete" queries against multi-table entities
(secondary tables, joined inheritance, etc).  This short-coming exists in
previous versions.  Essentially, when a strategy is not explicitly
specified we fallback to using an "id table" and performing the SQL updates
or deletes using the id table as a sub-query.  The problem is that for some
databases this breaks when the ids are composite due to the database not
having complete support for tuples.  For example, H2 does not allow the
sub-query for an in-predicate to return more than one column: so a query
like `... where (id1, id2) in (select val1, val2 from ...)` will not work.
This tuple concern is something I had not considered when I first wrote
this feature.

Obviously with 6 we have a chance to improve this.  So I have been thinking
about some ways this might be improved.  The guiding principle here was to
make this as implicit as possible...

Because certain strategies will not work when the ids are composite, I
think the first consideration is whether we want to allow the strategy to
be definable per-entity.  The idea would be to allow for the most efficient
strategy to be used generally (whatever that might be for the given
app/database) but to pick an alternative strategy in cases where that
"fallback" one will not work.  The Dialect should certainly play a part in
this strategy selection.

And lastly, we should consider whether it is beneficial to leverage these
strategies when performing normal mutations (merge, persist, etc).  I think
really this comes down to whether we think there is a benefit to handling
these via CTE if the database supports that as opposed to sending
individual updates or deletes against each table.

Thoughts?