jsr338-experts@jpa-spec.java.net

[jsr338-experts] Re: query improvements: downcasting

From: Gordon Yorke <gordon.yorke_at_oracle.com>
Date: Tue, 15 Mar 2011 14:23:17 -0300

No matter what keyword we go with the function format is more readable
in the WHERE clause and we should be consistent throughout the JPQL
statement.
--Gordon

Emmanuel Bernard wrote:
> I think the syntax could look like the following
>
> SELECT COALESCE(b.title, t.name) FROM Order o JOIN TREAT(o.product AS Book) b JOIN TREAT(o.product AS Toaster) t
>
> =>
>
> 1. SELECT COALESCE(b.title, t.name) FROM Order o JOIN o.product INSTANCEOF Book AS b JOIN o.product INSCANCEOF Toaster AS t
>
> or
>
> 2. SELECT COALESCE(b.title, t.name) FROM Order o JOIN INSTANCEOF(o.product, Book) AS b JOIN INSTANCEOF(o.product, Toaster) AS t
>
> Note that AS is left to identification variables and not reused for something else like in the TREAT(... AS ...)
>
> SELECT c
> FROM Customer c JOIN treat(c.order AS BigOrder) bigOrder
> JOIN treat(bigOrder.exclusiveProduct AS Yacht) y
> WHERE y.countryRegistered = "Spain" OR bigOrder.cost > 10000000
>
> =>
>
> 1. SELECT c
> FROM Customer c JOIN c.order INSTANCEOF BigOrder AS bigOrder
> JOIN bigOrder.exclusiveProduct INSTANCEOF Yacht AS y
> WHERE y.countryRegistered = "Spain" OR bigOrder.cost > 10000000
>
> or
>
> 2. SELECT c
> FROM Customer c JOIN INSTANCEOF(c.order, BigOrder) AS bigOrder
> JOIN INSTANCEOF(bigOrder.exclusiveProduct, Yacht) AS y
> WHERE y.countryRegistered = "Spain" OR bigOrder.cost > 10000000
>
> I tend to prefer approach #1 (keyword) over #2 (method)
>
> On 15 mars 2011, at 00:10, Linda DeMichiel wrote:
>
>
>> Hi Emmanuel,
>>
>> How do I use INSTANCEOF in the FROM clause?
>>
>> Would it allow for multiple downcasts, e.g.
>>
>> SELECT c
>> FROM Customer c JOIN treat(c.order AS BigOrder) bigOrder
>> JOIN treat(bigOrder.exclusiveProduct AS Yacht) y
>> WHERE y.countryRegistered = "Spain" OR bigOrder.cost > 10000000
>>
>> thanks,
>>
>> Linda
>>
>>
>> On 3/13/2011 2:59 PM, Emmanuel Bernard wrote:
>>
>>> Like some here, I am not a fan of TREAT as a method name.
>>> Why not try and explore the more OO approach of using instanceof:
>>> - it could be more flexible
>>> - it is clearer to OO people
>>> - w are not tied to the inconsistencies in DBs implementations of TREAT
>>> //notice the use of parenthesis
>>> WHERE ( INSTANCEOF(p, LargeProject) AND p.budget > 1000 )
>>> OR ( INSTANCEOF(p, SmallProject ) AND p.name LIKE "Persist%" )
>>> OR p.description LIKE "COST OVERRUN" //alternative syntax
>>> WHERE ( p INSTANCEOF LargeProject AND p.budget > 1000 )
>>> OR ( p INSTANCEOF SmallProject AND p.name LIKE "Persist%" )
>>> OR p.description LIKE "COST OVERRUN" On 11 mars 2011, at 21:23, Linda DeMichiel wrote:
>>>
>>>> Hi all,
>>>>
>>>> Outside of a few more minor aspects, it looks like we're on the same
>>>> page with regard to TREAT, so I'll start trying to spec it out more
>>>> formally.
>>>>
>>>> Here's where I think we are:
>>>>
>>>> 1) We'll support the use of TREAT for downcasting within path
>>>> expressions in the FROM and WHERE clauses.
>>>>
>>>> 2) If the target type is not a (proper or improper) subtype of
>>>> the static type of the first argument, the query is invalid (and
>>>> an exception is thrown).
>>>>
>>>> 3) If during query execution the first argument is not a (proper or
>>>> improper) subtype of the target type, we'll assume "null semantics"--
>>>> i.e. in the case of a join, the referenced object does not participate
>>>> in the result, and in the case of a restriction, the associated
>>>> predicate is false.
>>>>
>>>> 4) We'll add treat() to the criteria API
>>>>
>>>> BTW, I have a suspicion that trying to integrate this into the
>>>> spec will reveal a few more issues.
>>>>
>>>> If you disagree with the above, please speak now.
>>>>
>>>> thanks,
>>>>
>>>> -Linda
>>>>
>>>> p.s. I am still regarding the exact name as potentially subject to
>>>> change, but that is the easiest part to change later in the spec :-)
>>>>
>>>>
>>>> On 3/11/2011 8:51 AM, Matthew Adams wrote:
>>>>
>>>>> On Fri, Mar 11, 2011 at 10:11 AM, Rainer Kwesi Schweigkoffer
>>>>> <kwesi_at_sap.com> wrote:
>>>>>
>>>>>> Matthew Adams, am 10 Mar 2011 hast Du um 17:12 zum Thema "[jsr338-experts] Re: query improvements: downcast" geschrieben :
>>>>>>
>>>>>>
>>>>>>> If so, then I would restate **for the deferred,
>>>>>>> not-currently-specified projection use of TREAT AS** that if either
>>>>>>> Book is not assignment-compatible with Product or the filter results
>>>>>>> in type-incompatible instances with the projection expression, an
>>>>>>> exception is thrown.
>>>>>>>
>>>>>> Do you see a specific use case that would require to admit TREAT AS for
>>>>>> the select clause as well ?
>>>>>>
>>>>>>
>>>>> I suppose not.
>>>>> Theoretically, using TREAT AS in the projection (the select clause) is
>>>>> not required. If the user wants to exclude certain types from the
>>>>> query, the mechanism by which I'd recommend that they do it would be
>>>>> to use FROM ... TREAT ... AS. To use SELECT ... TREAT ... AS requires
>>>>> that the filter only return objects of the expected type; if they want
>>>>> objects of that type, then why not just use FROM ... TREAT ... AS? It
>>>>> seems like they're just asking for an exception to be thrown if they
>>>>> use SELECT ... TREAT ... AS. The only behavior that they'd get with
>>>>> SELECT ... TREAT ... AS is either an exception if their query was
>>>>> wrong or the same behavior as FROM ... TREAT ... AS. Seems
>>>>> unnecessary to me.
>>>>> Let's see if anyone comes up with a use case for SELECT ... TREAT ...
>>>>> AS that can't be solved with a FROM ... TREAT ... AS. I'd be
>>>>> surprised if there were.
>>>>> -matthew
>>>>>
>
>