Re: JDOQL Subquery proposals

[Craig L Russell]

Hi Michael,





Select employees who make more than the average of all employees?


Craig

On Jun 22, 2006, at 3:18 PM, Michael Bouschen wrote:

I came up with some more queries that I'd like to discuss as we consider how to do subqueries. I haven't found a use case for subqueries that themselves contain subqueries... Select employees who make more than the average of their department employees? Select employees who make more than the average of the employees in their department at the same pay scale? Select employees named Joe who make more than the average of all employees? Select employees named Joe who make more than the average of all employees at the same pay scale? Select employees who make more than the average of all employees at the same pay scale?

Hi,




SELECT FROM Employee WHERE this.salary > averageSalary



(1) Use a collection variable in the FROM clause of the subquery:
 SELECT FROM Employee WHERE this.salary > averageSalary
  VARIABLES Collection emps = this.department.employees;
     float averageSalary = SELECT AVG(this.salary) FROM emps



SELECT FROM Employee WHERE this.salary > averageSalary
 VARIABLES Employee e;



 SELECT e FROM Employee e JOIN e.department d
 WHERE e.salary > (SELECT AVG(e2.salary) FROM d.employees e2)



Regards Michael

I agree to what Craig mentioned in the JDO TCK minutes: method addSubquery separates the outer from the subquery. So it looks like that this approach cannot support correlated subqueries where the subquery iterates a collection field defined in the outer query. My preference is using variables declaration to support subqueries even in the Query API case. A query selecting employees earning more than the average salary could be: VARIABLES float averageSalary = SELECT AVG(this.salary) FROM Employee But I still have problems with a correlated subquery. I'm looking for a query selecting employees earning more than the average salary of their department. The difference to the query above is that the candidates collection of the subquery: the employees collection of the department of the current employee and not the entire Employee extent. Here are some ideas to stimulate the discussion, but I'm not quite happy with these. One issue is that 'this' is ambiguous: I would think that 'this' always refers to an instance of the inner most scope. But this means the subquery cannot directly access a field of the outer query. So the outer query declares a variable emps that may be used in the inner query. Maybe we need a special syntax to access the 'this' from the outer query. (2) The second form does not define any FROM clause, instead it uses a variable bound to a collection field of the outer query: float averageSalary = SELECT AVG(e.salary) WHERE this.department.employees.contains(e) Since the subquery does not have a FROM clause, 'this' refers to the current Employee from the outer query. BTW, this is the query as Java Persistence API Query (formerly EJB QL): About the open issue: using the assignment operator ("=") versus the JDOQL keyword "AS": I vote for the assignment operator. The "AS" keyword is used in the result expression of the form 'expr AS name'. In a variable declaration it would have the form 'type name AS expr'. It might be confusing that the variable declaration swaps the order, because here the expr is right of the keyword, where it is on the other side in the result expression.

Hi everyone,

Here are 2 proposals discussed in the Fri 9 Jun JDO conference call

JPOX JIRA issue CORE-2861

regarding support for subqueries in JDOQL, including single-string and Query API enhancements, inspired by JPOX's proposed enhancement, documented in (http://www.jpox.org/servlet/jira/browse/CORE-2861). The string and API enhancement proposals described here are designed to be used hand- in-hand,

as the folks on the call wanted to continue to provide compatible

the use of the exising facility to declare JDOQL variables (not JDOQL
parameters) to bind subqueries to superqueries.

Please read thoroughly, consider, and comment.

--matthew


future object pattern.  See separate proposal from him.

<proposals>

Query API support
=================

subquery)


declared for the superquery.  The implementation handles coercing the
subquery's result into the type of the variable(s) declared in the
superquery.  Queries can be nested to arbitrary levels.

Example 1A:  Find people with above average income

Query superquery1a = pm.newQuery("SELECT FROM Person WHERE income >
averageIncome");
superquery1a.declareVariables("BigDecimal averageIncome;");

Query subquery1a = pm.newQuery("SELECT avg(income) FROM Person");

superquery


Example 2A:  Find average income of fathers using subquery

Query superquery2a = pm.newQuery("SELECT avg(income) FROM fathers");
// in next line, Collection<Parent> derived from subquery
superquery2a.declareVariables("Collection fathers;");


children.size() > 0");
superquery2a.addSubquery("fathers", subquery2a); // binds subquery to
superquery



forms (see JDO 2.0 spec sections 14.6.5 & 14.6.13).

Query superquery3a = pm.newQuery("SELECT avg(income) FROM fathers");
// in next line, Collection<Parent> derived from subquery
superquery3a.declareVariables(
    "Collection fathers = SELECT FROM Person WHERE gender == 'M' &&
children.size() > 0;");

Pros:
  * Maintains backward compatibility.

current JDOQL string spec doesn't allow for subqueries at all.

the single-string and the API.
  * Compatible with single-string enhancement proposal below

Cons:
  * Type coercion becomes more complicated than just autoboxing.
  * Requires that variables may always have to be explicitly defined.

derived (see example 2A above), or may not be known.

collections.



Single-string proposal
======================
Proposal:  Use the existing VARIABLES JDOQL keyword in order to be
compatible with the Query API proposal above.

This proposal is very similar to JPOX JIRA CORE-2861

string-based and API-based usages. In a nutshell, both proposals hinge on PS: Martin was on the hook to describe an alternative proposal based on a Proposal: Introduce new method Query.addSubquery(String variableName, Query This proposal entails utilizing the current Query API's declareVariables facility and adding a method Query.addSubquery(String,Query) to support subqueries. Essentially, a subquery is bound to a superquery via a variable superquery1a.addSubquery("averageIncome", subquery1a); // binds subquery to Query subquery2a = pm.newQuery("SELECT FROM Person WHERE gender == 'M' && Example 3A: Find average income of fathers using a single Query instance Note: this example's usage is required if the grammar specification of the variables clause remains the same (as it currently is) in the API and string * Enhances performance by allowing for the deferral of query execution until entire query with subqueries is defined. Current Query API support requires the execution of the subquery, then execution of the superquery; * Grammar of the variables clause undergoes the same enhancements in both * Possibility that type of candidate collection of superquery must be * Possibility of using variables in place of both parameters and candidate (http://www.jpox.org/servlet/jira/browse/CORE-2861), which proposes the introduction of a new JDOQL keyword "WITH" to introduce typed and named subquery results. To make this more compatible with the Query API proposal

above and to avoid the need to introduce a new keyword to JDOQL, the

a valid JDOQL expression.  Variables would continue to be

employ subqueries, returns JDOQL strings that use this syntax.


to example 1A)

SELECT FROM Person WHERE income > averageIncome
  VARIABLES float averageIncome =
    SELECT avg(income) FROM Person;


example 2A)

SELECT avg(income) FROM parents
  VARIABLES Collection parents =
    SELECT FROM Parent WHERE gender == 'M' && children.size() > 0;

Pros:
  * Continues to use existing JDOQL keywords.

the single-string and the API.
  * Compatible with Query API proposal above.

Cons:
  * More verbose than introducing "WITH" keyword.  Implicit, unnamed

(from example 2S), where the tokens "VARIABLES Collection parents" is
implied.
  SELECT avg(income) FROM parents =
    SELECT FROM Parent WHERE gender == 'M' && children.size() > 0;

leaves open the typing and naming of the implicit, unnamed variables.

collections.


Open issues
===========


cases?
* These proposals use the assignment operator ("=").  Should we use
assignment via the JDOQL keyword "AS" instead or in addition to the
assignment operator?



</proposals>

existing JDOQL keyword "VARIABLES" would be used to introduce typed and named subqueries, except that the variable(s) would be intialized via the assignment operator, "=", or the "AS" keyword (TBD) at declaration time with semicolon-delimited. Additionally, Query.toString(), for queries that Example 1S: Find people with above average income using subquery (similar Example 2S: Find average income of fathers using a subquery (similar to * Grammar of the variables clause undergoes the same enhancements in both variables would not be supported, unless the following syntax were supported This syntax is admittedly less verbose and more like SQL subqueries, but * Possibility of using variables in place of both parameters and candidate * Can type derivation & coercion of JDOQL variables be performed in all * This proposal requires that JDOQL variables be allowed to substitute for both JDOQL parameters and candidate collections. Should this be allowed?

-----Original Message-----

From: Erik Bengtson [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 07, 2006 7:57 AM

To: [EMAIL PROTECTED]; jdo-dev@db.apache.org


Hi,


etc..

In JPOX it will implemented as exemplified here
http://www.jpox.org/servlet/jira/browse/CORE-2861

Sadly, JDOQL 2 is not capable to compete with JPQL in this aspect.


general based on new user requests/requirements?

Regards,

Erik Bengtson

Subject: any plans to support subqueries with similar concept as in sql We need the ability to work with multiple sets in the same query. It includes performing operations between sets, numeric functions like average or sum, Regardless the above issue, are there plans to expand the JDOQL or even JDO 2 in

--
Michael Bouschen                [EMAIL PROTECTED] Engineering GmbH
mailto:[EMAIL PROTECTED]        http://www.tech.spree.de/
Tel.:++49/30/235 520-33         Buelowstr. 66                   
Fax.:++49/30/2175 2012          D-10783 Berlin                  

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!

smime.p7s
Description: S/MIME cryptographic signature