Hi Linda,
what I understood from your proposal is implicit joins in an ORDER BY
clause are treated as outer joins, correct?
But how about implicit joins in other clauses of the query, e.g. the
SELECT clause.
The following query excludes persons not having an address from the result
select p.address from Person p
where this one includes them:
select a from Person p LEFT OUTER JOIN p.address a
I am uncomfortable with defining different semantics of an implicit join
depending on whether it is used in an ORDER BY clause versus a SELECT or
WHERE clause. Is GROUP BY treated the same as ORDER BY, because it
should not filter what is returned, too? But HAVING is used to filter
over groups ... This sounds confusing to me.
> That was the original intent,
> and why, for example, we disallow relationship joins in the orderby
> clause.
Did we disallow relationships in an ORDER BY clause? An orderby_item
might be a state_field_path_expression which might include a
relationship navigation, as long as the navigated relationship is
included in the SELECT clause. Am I wrong?
Regards Michael
>
>
> On 8/29/2012 6:26 AM, Oliver Gierke wrote:
>> The problem is that adding an order by clause leaks a side effect
>> into what is actually returned from the query, which shouldn't be the
>> case.
>>
>> Yes, it's outlined in the spec but it's also inconsistent in regards
>> of mapping metadata being applied to the JPQL expression. An
>> @Column(name = "Foo") get's regarded, why shouldn't an
>> @ManyToOne(optional = true) be?
>>
>> I think it get's more obvious if you consider a more simple case:
>>
>> select p from Person p
>>
>> VS.
>>
>> select p from Person p order by p.address.city
>>
>> The former includes people without addresses, the latter does not?
>> And this is totally inexplainable from a pure mapping metadata point
>> of view. I wonder why the two lines defining path expressions being
>> forced into inner joins have made it into the spec in the first
>> place. Were the side effects not considered? Why explicitly not
>> applying the mapping metadata?
>>
>
>
> If address is an embeddable, this query is legit. If address is
> an entity (as in your original example, the order-by is not).
>
> Perhaps what needs clarification is the notion of "result".
>
> What the spec says (section 4.4.4) is:
>
> Path expression navigability is composed using "inner join"
> semantics. That is, if the value of a non-terminal field in the
> path expression is null, the path is considered to have no value,
> and does not participate in the determination of the result.
>
> Perhaps what this should say is:
>
> Path expression navigability is composed using "inner join"
> semantics. That is, if the value of a non-terminal field in the
> path expression is null, the path is considered to have no value,
> and does not participate in the determination of the *contents* of
> the result of the query.
>
>
> With order-by in play, there are two aspects to the result:
>
> What is actually retrieved from the database ("contents of the
> result"), and how it is ordered.
>
> In my view, the order-by clause should only affect the ordering of
> what is returned, not filter it. That was the original intent,
> and why, for example, we disallow relationship joins in the orderby
> clause.
>
>
>> Cheers,
>> Ollie
>>
>> PS: I've summarized my findings in this gist
>> (https://gist.github.com/3497047). The comments might be interesting
>> to consider.
>>
>> Am 29.08.2012 um 14:47 schrieb Steve
>> Ebersole<steve.ebersole_at_redhat.com>:
>>
>>> Not sure how this "side-effect" is "unpleasant and not easy to grasp".
>>> It is explicitly called out in the spec.
>>>
>>> -1 for changing implicit joins to result in inner or outer joins
>>> depending on the mapping. In such a case you can no longer see what
>>> will happen just by looking at the query itself, which in my opinion is
>>> far more "unpleasant and not easy to grasp".
>>>
>>>
>>> On 08/28/2012 07:44 AM, Oliver Gierke wrote:
>>>> Hi all,
>>>>
>>>> I just came across a JPQL spec scenario that seems to be a bit
>>>> weird and I wonder whether there's something we should do about.
>>>> Suppose you have a Person with optional Addresses:
>>>>
>>>> @Entity
>>>> class Person {
>>>>
>>>> @OneToOne(nullable = true) Address address;
>>>> }
>>>>
>>>> @Entity
>>>> class Address {
>>>> String city;
>>>> }
>>>>
>>>> Now the query scenario here is that we'd like to get all Persons
>>>> sorted by the Address' city:
>>>>
>>>> select p from Person p left outer join p.address order by
>>>> p.address.city
>>>>
>>>> Surprisingly, this query will not return Persons not having an
>>>> Address associated for the following reason: JPA 2.0 spec section
>>>> 4.4.4. defines path expressions as follows:
>>>>
>>>>> Path expression navigability is composed using “inner join”
>>>>> semantics. That is,
>>>>> if the value of a non-terminal field in the path expression is
>>>>> null, the path is
>>>>> considered to have no value, and does not participate in the
>>>>> determination of
>>>>> the result.
>>>>
>>>> That apparently forces persistence providers into adding an
>>>> additional inner join to the query which rules out the Persons
>>>> without Addresses in the first place. I think it's rather
>>>> unfortunate to have this path expression definition applied to
>>>> order by clauses as users probably don't expect adding a sort
>>>> definition would strengthen the actual query criteria. So here are
>>>> my questions:
>>>>
>>>> 1. Why was the path expression navigability defined as such in the
>>>> first place and not as considering the mapping metadata (nullable =
>>>> true -> outer join, nullable = false -> inner join). Not saying
>>>> this is utterly wrong, just want to understand the probably
>>>> available reasons.
>>>> 2. Should/can this definition be changed to require consideration
>>>> of the mapping information? The path expression definition is very
>>>> much written with the purpose of defining selection criterias which
>>>> is what they are effectively not used for when used in ORDER BY
>>>> clauses. The current state leaves JPQL in the weird state that
>>>> adding a sorting criteria affects the returned items not only in
>>>> order but also in which items are returned at all, a side-effect
>>>> which is unpleasant and not easy to grasp.
>>>>
>>>> Cheers,
>>>> Ollie
>>>>
>>
--
*Michael Bouschen*
*Prokurist*
akquinet tech_at_spree GmbH
Bülowstr. 66, D-10783 Berlin
Fon: +49 30 235 520-33
Fax: +49 30 217 520-12
Email: michael.bouschen_at_akquinet.de
Web: www.akquinet.de <http://www.akquinet.de>
akquinet tech_at_spree GmbH, Berlin
Geschäftsführung: Martin Weber, Dr. Torsten Fink
Amtsgericht Berlin-Charlottenburg HRB 86780 B
USt.-Id. Nr.: DE 225 964 680