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.
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
>>
>>