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