[hibernate-dev] TREAT operator and joined inheritance (HHH-9862)
Gail Badner
gbadner at redhat.com
Tue Jun 16 20:02:43 EDT 2015
See below:
----- Original Message -----
> From: "Steve Ebersole" <steve at hibernate.org>
> To: "Gail Badner" <gbadner at redhat.com>, "Hibernate Dev" <hibernate-dev at lists.jboss.org>
> Sent: Tuesday, June 16, 2015 11:00:49 AM
> Subject: Re: [hibernate-dev] TREAT operator and joined inheritance (HHH-9862)
>
> 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>
>
Yes, I noticed this as well.
> 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.
We have some unit tests that have a single TREAT select expression on the root entity using HQL and CriteriaBuilder:
Using HQL (https://hibernate.atlassian.net/browse/HHH-8637):
org.hibernate.test.jpa.ql.TreatKeywordTest.testFilteringDiscriminatorSubclasses
org.hibernate.test.jpa.ql.TreatKeywordTest.testFilteringJoinedSubclasses
Using CriteriaBuilder (https://hibernate.atlassian.net/browse/HHH-9549):
org.hibernate.jpa.test.criteria.TreatKeywordTest.treatRoot
org.hibernate.jpa.test.criteria.TreatKeywordTest.treatRootReturnSuperclass
As you can see, Hibernate supports one TREATed root entity in a SELECT clause (no projections). Should we limit Hibernate support to that use case?
>
> 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 agree that using <type-case-statement> should cover these cases.
For joined subclasse, it looks like <type-case-statement> is generated from the CaseFragment returned by JoinedSubclassEntityPersister#discriminatorFragment. I imagine there is something similar for single-table inheritance, but I haven't found it yet.
> 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
> >
>
More information about the hibernate-dev
mailing list