On 3/8/2011 7:42 AM, Gordon Yorke wrote:
> Hello Linda,
> The TREAT function should not restrict sub-classes from the result.
> If that was the goal then the TYPE function could be used. TREAT should
> allow reference to a mapped attribute of a sub-class and all of that
> classes sub-classes. Any super-classes would be restricted from the
> result.
>
Definitely agree. I was just assuming Book as a leaf-node subtype when writing
the example.
> for an explicit example:
>
> select b.name, b.isbn from Orders o, Products b WHERE b.DTYPE IN("Book",
> "Hard Cover", "Trade Paperback") and o.productId = b.productId
>
> --Gordon
>
> Linda DeMichiel wrote:
>> Hi Evan,
>>
>> I am assuming that we are borrowing the syntax, not the
>> implementation, since SQL TREAT was targeted at ADTs, so
>> the provider is doing the mapping.
>>
>> E.g., to take the first of these sample queries, depending on
>> the inheritance mapping used, etc., you might get something
>> like one of the following:
>>
>> SELECT b.name, b.isbn
>> FROM Orders o, Products b
>> WHERE b.DTYPE = "Book"
>> AND o.productId = p.productId
>>
>> or
>>
>> SELECT p.name, b.isbn
>> FROM Orders o, Products p, Books b
>> WHERE p.DTYPE = "Book"
>> AND o.productId = p.productId
>> AND p.productId = b.productId
>>
>>
>> Similarly for the second query (assuming joined inheritance):
>>
>> SELECT e.*
>> FROM Employee e, Projects p, LargeProjects l
>> WHERE p.empId = e.empId
>> AND p.projId = l.projId
>> AND p.DTYPE = "LargeProject"
>> AND l.budget > 1000
>>
>>
>>
>> On 3/7/2011 3:03 PM, Evan Ireland wrote:
>>> Linda,
>>>
>>> If we decide that TREAT(x as T) returns NULL when the value x does
>>> not have type T, then does that produce portability issues, or can we
>>> suppose that it is always possible to map to appropriate DB syntax
>>> that will produce a NULL?
>>>
>>> -----Original Message-----
>>> From: Linda DeMichiel [mailto:linda.demichiel_at_oracle.com] Sent:
>>> Monday, March 07, 2011 12:46 PM
>>> To: jsr338-experts_at_jpa-spec.java.net
>>> Subject: [jsr338-experts] query improvements: downcasting
>>>
>>> As promised, we'll kickoff our work with some of the hopefully easier
>>> items to get us warmed up. We'll start with some topics in the query
>>> area.
>>>
>>> First up -- downcasting.
>>>
>>>
>>> We've gotten a number of requests to support a downcasting functionality
>>> in the query language. This is useful particularly for polymorphic
>>> relationships where the intent is to access subtype-specific attributes.
>>>
>>> SQL supports this via the TREAT ... AS operator:
>>>
>>> TREAT (expression AS datatype)
>>>
>>> where datatype is a subtype of the static type of the expression.
>>>
>>>
>>> The extension of this syntax to JPQL would be straightforward.
>>>
>>> For example,
>>>
>>> SELECT b.name, b.ISBN
>>> FROM Order o JOIN TREAT(o.product AS Book) b
>>>
>>> SELECT e FROM Employee e JOIN TREAT(e.projects AS LargeProject) lp
>>> WHERE lp.budget > 1000
>>>
>>> SELECT e FROM Employee JOIN e.projects p
>>> WHERE TREAT(p AS LargeProject).budget > 1000 OR
>>> TREAT(p AS SmallProject).name LIKE "Persist%" OR
>>> p.description LIKE "COST OVERRUN"
>>>
>>>
>>> An open issue however is the handling of the case where the
>>> instance passed to TREAT is not of the same type or a subtype of the
>>> specified datatype.
>>>
>>> Databases seem to differ as to whether the result should be null or
>>> whether an error is raised. We need to decide this as well.
>>>
>>> I think the above examples illustrate the utility of defining the
>>> semantics as providing a filtering as well as downcasting semantics
>>> (i.e., taking the approach of using null semantics).
>>>
>>> The Criteria API already provides the Expression method
>>> <X> Expression<X> as(Class<X> type);
>>>
>>> However, note that the definition of this explicitly states: "This
>>> method does not cause type conversion: the runtime type is not
>>> changed. Warning: may result in a runtime failure. "
>>>
>>>
>>> Opinions?
>>>
>>> thanks,
>>>
>>> -Linda
>>>
>>>