See below: ----- Original Message ----- > From: "Steve Ebersole" <st...@hibernate.org> > To: "Gail Badner" <gbad...@redhat.com>, "Hibernate Dev" > <hibernate-dev@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 <gbad...@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@lists.jboss.org > > https://lists.jboss.org/mailman/listinfo/hibernate-dev > > > _______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
