[hibernate-dev] TREAT operator and joined inheritance (HHH-9862)

[Steve Ebersole]

As for the "multi-select" case you mention, JPA actually does not mention
support for TREAT in select clauses.  In fact it explicitly lists support
for TREAT in the from and where clause.  So because it explicitly mentions
those, I'd say it implicitly excludes support for them in select clause.

<quote>
The use of the TREAT operator is supported for downcasting within path
expressions in the FROM and
WHERE clauses. ...
</quote>

So unfortunately there is no "properly" in this case because JPA does not
define what is proper.  There is just what we deem to be appropriate.

There is a lot of difficulty in getting the inner/outer join right here.  The
difficulty is knowing the context that the TREAT occurs in in the code that
is building the join fragment.  Ultimately this is done
in org.hibernate.persister.entity.AbstractEntityPersister#determineSubclassTableJoinType.
But that method has no visibility into whether this is driven by a select
or a where or a from or ...

And in fact I'd argue that its not just a question of select versus from.
Its really more a question of how many other treats occur for that same
"from element" and whether they are used in an conjunctive (AND) or
disjunctive (OR) way.  But I am not convinced we'd ever be able to get the
inner/outer join right in all these cases.  At the least the contextual
info we'd need is well beyond what we have available to us given the
current SQL generation engine here.  And even if we did have all the
information available to us. I am not sure it is reasonable way to apply
restrictions.

Maybe a slightly different way to look at this is better.  Rather that
attempting to alter the outer join (which is what Hibernate would use for
the subclasses) to be inner joins in certain cases, maybe we instead just
use a type restriction.  Keeping in mind that by default Hibernate will
want to render the joins for subclasses as outer joins, I think this is
easiest to understand with some examples

1) "select p.id, p.name from Pet p where treat(p as Cat).felineProperty =
'x' or treat(p as Dog).canineProperty = 'y'"
So by default Hibernate would want to render SQL here like:
select ...
from Pet p
   left outer join Dog d on ...
   left outer join Cat c on ..
where c.felineProperty = 'x'
  or d.canineProperty = 'y'

which is actually perfect in this case.

2) "select p.id, p.name from Pet p where treat(p as Cat).felineProperty =
'x' and treat(p as Dog).canineProperty = 'y'"
Hibernate would render SQL like:
from Pet p
   left outer join Dog d on ...
   left outer join Cat c on ..
where c.felineProperty = 'x'
  and d.canineProperty = 'y'

which again is actually perfect here.

As it turns out the original "alter join for treat" support was done to
handle the case of a singular restriction:

3) "select p.id, p.name from Pet p where treat(p as Cat).felineProperty <>
'x'"
Hibernate would render SQL like:
from Pet p
   left outer join Dog d on ...
   left outer join Cat c on ..
where c.felineProperty <> 'x'

the problem here is that Dogs can also be returned.  In retrospect looking
at all these cases I think it might have been better to instead render a
restriction for the type into the where:

from Pet p
   left outer join Dog d on ...
   left outer join Cat c on ..
where ( <type-case-statement> and c.felineProperty <> 'x' )

(<type-case-statement> is the case statement that is used to restrict based
on concrete type).  Now we will only get back Cats.  The nice thing is that
this approach works no matter the and/or context:

select ...
from Pet p
   left outer join Dog d on ...
   left outer join Cat c on ..
where ( <type-case-statement> and c.felineProperty = 'x' )
  or ( <type-case-statement> and d.canineProperty = 'y' )

from Pet p
   left outer join Dog d on ...
   left outer join Cat c on ..
where  ( <type-case-statement> and c.felineProperty = 'x' )
  and  ( <type-case-statement> and d.canineProperty = 'y' )


I'd have to think through treats in the from-clause a bit more.

On Mon, Jun 15, 2015 at 3:27 PM Gail Badner <gbadner at redhat.com> wrote:

> JPA 2.1 shows examples of using multiple downcasts in a restriction:
>
> 4.4.9 Downcasting
>
> SELECT e FROM Employee e
> WHERE TREAT(e AS Exempt).vacationDays > 10
>       OR TREAT(e AS Contractor).hours > 100
>
> 6.5.7 Downcasting
>
> Example 3:
> CriteriaQuery<Employee> q = cb.createQuery(Employee.class);
> Root<Employee> e = q.from(Employee.class);
> q.where(
>    cb.or(cb.gt(cb.treat(e, Exempt.class).get(Exempt_.vacationDays),
>                10),
>    cb.gt(cb.treat(e, Contractor.class).get(Contractor_.hours),
>                100)));
>
> These don't work in Hibernate for joined inheritance because Hibernate
> uses an inner join for the downcasts.
>
> I've added a FailureExpected test case for this:
> https://github.com/hibernate/hibernate-orm/commit/1ec76887825bebda4c02ea2bc1590d374aa4415b
>
> IIUC, inner join is correct when TREAT is used in a JOIN clause. If TREAT
> is only used for restrictions in the WHERE clause, I *think* it should be
> an outer join. Is that correct?
>
> HHH-9862 also mentions that Hibernate doesn't work properly when there are
> multiple select expressions using different downcasts, as in:
>
> CriteriaBuilder cb = entityManager.getCriteriaBuilder();
> CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
> Root<Pet> root = query.from(Pet.class);
> query.multiselect(
> root.get("id"),
> root.get("name"),
> cb.treat(root, Cat.class).get("felineProperty"),
> cb.treat(root, Dog.class).get("canineProperty")
> );
>
> I don't think this should work, at least not with implicit joins. Is this
> valid?
>
> Thanks,
> Gail
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>