3:26 p.m.
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/1ec76887825bebda4c02ea2...
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
1 p.m.
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(a)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/1ec76887825bebda4c02ea2...
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(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
7:02 p.m.
See below:
----- Original Message -----
From: "Steve Ebersole" <steve(a)hibernate.org>
To: "Gail Badner" <gbadner(a)redhat.com>, "Hibernate Dev"
<hibernate-dev(a)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(a)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/1ec76887825bebda4c02ea2...
>
> 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(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>
1:47 p.m.
org.hibernate.persister.entity.Queryable#getTypeDiscriminatorMetadata
On Tue, Jun 16, 2015 at 7:02 PM Gail Badner <gbadner(a)redhat.com> wrote:
See below:
----- Original Message -----
> From: "Steve Ebersole" <steve(a)hibernate.org>
> To: "Gail Badner" <gbadner(a)redhat.com>, "Hibernate Dev"
<
hibernate-dev(a)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(a)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/1ec76887825bebda4c02ea2...
> >
> > 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(a)lists.jboss.org
> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
> >
>
3233
days inactive
3235
days old
3 comments
2 participants
participants (2)
-
Gail Badner -
Steve Ebersole