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

[Steve Ebersole]

org.hibernate.persister.entity.Queryable#getTypeDiscriminatorMetadata


On Tue, Jun 16, 2015 at 7:02 PM Gail Badner <gbadner at redhat.com> wrote:

> 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
> > >
> >
>