Mailing-List: contact jdo-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: jdo-dev@db.apache.org
Received-SPF: neutral (asf.osuosl.org: local policy)
From: "Matthew T. Adams" <matthew.adams@xcalia.com>
To: <jdo-dev@db.apache.org>, <jdo-experts-ext@Sun.COM>
Subject: JDOQL Subquery proposals (was: RE: any plans to support subqueries
 with similar concept as in sql)
Date: Fri, 9 Jun 2006 15:47:53 -0700
Message-ID: <007101c68c16$c6c66de0$6963a8c0@DELLZILLA>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit
Thread-index: AcaKQr7JtN3B5YO+S16LS3m0QnHZswBo6pOQ
In-Reply-To: <1149692242.4486e952a564e@webmail.jpox.org>

Hi everyone,

Here are 2 proposals discussed in the Fri 9 Jun JDO conference call
regarding support for subqueries in JDOQL, including single-string and Query
API enhancements, inspired by JPOX's proposed enhancement, documented in
JPOX JIRA issue CORE-2861
(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
string-based and API-based usages.  In a nutshell, both proposals hinge on
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

PS:  Martin was on the hook to describe an alternative proposal based on a
future object pattern.  See separate proposal from him.

<proposals>

Query API support
=================
Proposal:  Introduce new method Query.addSubquery(String variableName, Query
subquery)

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
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");
superquery1a.addSubquery("averageIncome", subquery1a); // binds subquery to
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;");

Query subquery2a = pm.newQuery("SELECT FROM Person WHERE gender == 'M' &&
children.size() > 0");
superquery2a.addSubquery("fathers", subquery2a); // binds subquery to
superquery


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
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.
  * 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;
current JDOQL string spec doesn't allow for subqueries at all.
  * Grammar of the variables clause undergoes the same enhancements in both
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.
  * Possibility that type of candidate collection of superquery must be
derived (see example 2A above), or may not be known.
  * Possibility of using variables in place of both parameters and candidate
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
(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
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
a valid JDOQL expression.  Variables would continue to be
semicolon-delimited.  Additionally, Query.toString(), for queries that
employ subqueries, returns JDOQL strings that use this syntax.

Example 1S:  Find people with above average income using subquery (similar
to example 1A)

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

Example 2S:  Find average income of fathers using a subquery (similar to
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.
  * Grammar of the variables clause undergoes the same enhancements in both
the single-string and the API.
  * Compatible with Query API proposal above.

Cons:
  * More verbose than introducing "WITH" keyword.  Implicit, unnamed
variables would not be supported, unless the following syntax were supported
(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;
  This syntax is admittedly less verbose and more like SQL subqueries, but
leaves open the typing and naming of the implicit, unnamed variables.
  * Possibility of using variables in place of both parameters and candidate
collections.


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

* Can type derivation & coercion of JDOQL variables be performed in all
cases?
* These proposals use the assignment operator ("=").  Should we use
assignment via the JDOQL keyword "AS" instead or in addition to the
assignment operator?
* This proposal requires that JDOQL variables be allowed to substitute for
both JDOQL parameters and candidate collections.  Should this be allowed?


</proposals>

>-----Original Message-----
>From: Erik Bengtson [mailto:erik@jpox.org] 
>Sent: Wednesday, June 07, 2006 7:57 AM
>To: jdo-experts-ext@sun.com; jdo-dev@db.apache.org
>Subject: any plans to support subqueries with similar concept as in sql
>
>Hi,
>
>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,
>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.
>
>Regardless the above issue, are there plans to expand the 
>JDOQL or even JDO 2 in
>general based on new user requests/requirements?
>
>Regards,
>
>Erik Bengtson
>