Return-Path: 
 <jdo-dev-return-2227-apmail-db-jdo-dev-archive=www.apache.org@db.apache.org>
Delivered-To: apmail-db-jdo-dev-archive@www.apache.org
Received: (qmail 16277 invoked from network); 18 Nov 2005 20:17:47 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199)
  by minotaur.apache.org with SMTP; 18 Nov 2005 20:17:47 -0000
Received: (qmail 564 invoked by uid 500); 18 Nov 2005 20:17:47 -0000
Mailing-List: contact jdo-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:jdo-dev-help@db.apache.org>
List-Unsubscribe: <mailto:jdo-dev-unsubscribe@db.apache.org>
List-Post: <mailto:jdo-dev@db.apache.org>
List-Id: <jdo-dev.db.apache.org>
Reply-To: jdo-dev@db.apache.org
Delivered-To: mailing list jdo-dev@db.apache.org
Received: (qmail 552 invoked by uid 99); 18 Nov 2005 20:17:47 -0000
Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49)
    by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 18 Nov 2005 12:17:47 -0800
X-ASF-Spam-Status: No, hits=0.2 required=10.0
	tests=HTML_MESSAGE,MISSING_HEADERS,UNPARSEABLE_RELAY
X-Spam-Check-By: apache.org
Received-SPF: pass (asf.osuosl.org: local policy)
Received: from [192.18.98.34] (HELO brmea-mail-3.sun.com) (192.18.98.34)
    by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 18 Nov 2005 12:19:19 -0800
Received: from fe-amer-10.sun.com ([192.18.108.184])
	by brmea-mail-3.sun.com (8.12.10/8.12.9) with ESMTP id jAIKHO3F000732
	for <jdo-dev@db.apache.org>; Fri, 18 Nov 2005 13:17:24 -0700 (MST)
Received: from conversion-daemon.mail-amer.sun.com by mail-amer.sun.com
 (Sun Java System Messaging Server 6.2-4.02 (built Sep  9 2005))
 id <0IQ600M0130Y9M00@mail-amer.sun.com>
 (original mail from Craig.Russell@Sun.COM) for jdo-dev@db.apache.org; Fri,
 18 Nov 2005 13:17:24 -0700 (MST)
Received: from [129.145.133.145] by mail-amer.sun.com
 (Sun Java System Messaging Server 6.2-4.02 (built Sep  9 2005))
 with ESMTPSA id <0IQ6007I730ZC611@mail-amer.sun.com> for
 jdo-dev@db.apache.org; Fri, 18 Nov 2005 13:17:24 -0700 (MST)
Date: Fri, 18 Nov 2005 12:17:22 -0800
From: Craig L Russell <Craig.Russell@Sun.COM>
Subject: Re: Negative VOTE Issue 143: Aggregating null-valued expressions
In-reply-to: <437D11E8.20701@spree.de>
Sender: Craig.Russell@Sun.COM
Cc: JDO Expert Group <jdo-experts-ext@Sun.COM>,
        Apache JDO project <jdo-dev@db.apache.org>
Message-id: <C584EAE5-7BA5-44BB-AF01-55D904894F1F@sun.com>
MIME-version: 1.0
X-Mailer: Apple Mail (2.746.2)
Content-type: multipart/alternative;
 boundary="Boundary_(ID_xjKCJiQPDiRj1Df3t0zXfQ)"
References: <52E63249-B46E-4764-B62C-923DB281B0AA@sun.com>
 <437CA07C.2000007@tralfamadore.com>
 <72FF684E-6E27-404C-AB24-99307F6BFC88@sun.com> <437D11E8.20701@spree.de>
X-Virus-Checked: Checked by ClamAV on apache.org
X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N

--Boundary_(ID_xjKCJiQPDiRj1Df3t0zXfQ)
Content-type: text/plain; format=flowed; delsp=yes; charset=WINDOWS-1252
Content-transfer-encoding: QUOTED-PRINTABLE

One last issue. Some SQL dialects don't allow distinct with min and =
=20
max (it doesn't make any difference to the semantics of min and max).=
 =20
I propose disallowing it from JDOQL.

Here's the latest proposal:

<proposed 14.6.9>
A14.6.9-5 [The result expressions include:
=93this=94: indicates that the candidate instance is returned
<field>: this indicates that a field is returned as a value; the =
=20
field might be in the candidate class or in a class referenced by a =
=20
variable
<variable>: this indicates that a variable=92s value is returned as a=
 =20
persistent instance
<aggregate>: this indicates that an aggregate of multiple values is =
=20
returned; if null values are aggregated, they do not participate in =
=20
the aggregate result; if all of the expressions to be aggregated =
=20
evaluate to null, the result is the same as if there were no =20
instances that match the filter.
count(<expression>): the count of the number of instances of the =
=20
expression is returned; the expression is preceded by an optional =
=20
=93distinct=94 and can be =93this=94, a navigational expression that =
=20
terminates in a single-valued field, or a variable name
sum(<numeric field expression>): the sum of field expressions is =
=20
returned; the expression is preceded by an optional "distinct"
min(<orderable field expression>): the minimum value of the field =
=20
expression is returned
max(<orderable field expression>): the maximum value of the field =
=20
expression is returned
avg(<numeric field expression>): the average value of all field =20
expressions is returned; the expression is preceded by an optional =
=20
"distinct"
<field expression>: the value of an expression using any of the =20
operators allowed in queries applied to fields is returned
<navigational expression>: this indicates a navigational path through=
 =20
single-valued fields or variables as specified by the Java language =
=20
syntax; the navigational path starts with the keyword =93this=94, a =
=20
variable, a parameter, or a field name followed by field names =20
separated by dots.
<parameter>: one of the parameters provided to the query.
</proposed 14.6.9>

On Nov 17, 2005, at 3:27 PM, Michael Bouschen wrote:

> Hi Craig,
>
> I agree with the proposed change.
>
> Regards Michael
>
>> Hi Wes,
>>
>> On Nov 17, 2005, at 7:23 AM, Wes Biggs wrote:
>>
>>> Quite possibly my foot will soon be in my mouth, but in the PFD =
=20
>>> 14.6.9, the target expression for count must be "this" or a =20
>>> variable name (well, it says "can" -- am I reading this =20
>>> incorrectly?).  If this is truly a restriction, I do not see the =
=20
>>> relevance of this addition, which only makes sense when talking =
=20
>>> about counting field expressions or the results of calculations.
>>
>>
>> I think the description in the specification is too narrow; my =
=20
>> mistake.
>> <spec 14.6.9>
>> count(<expression>): the count of the number of instances of this =
=20
>> expression is returned; the expression can be =93this=94 or a vari=
able =20
>> name
>> </spec 14.6.9>
>>
>> I think it should be
>>
>> <proposed 14.6.9>
>> count(<expression>): the count of the number of instances of this =
=20
>> expression is returned; the expression is preceded by an optional =
=20
>> "distinct" followed by =93this=94, a navigational expression that =
=20
>> terminates in a single-valued field, or a variable name
>> sum(<numeric field expression>): the sum of field expressions is =
=20
>> returned; the expression is preceded by an optional "distinct"
>> min(<field expression>): the minimum value of the field =20
>> expressions is returned; the expression is preceded by an optional=
 =20
>> "distinct"
>> max(<field expression>): the maximum value of the field =20
>> expressions is returned; the expression is preceded by an optional=
 =20
>> "distinct"
>> avg(<numeric field expression>): the average value of all field =
=20
>> expressions is returned; the expression is preceded by an optional=
 =20
>> "distinct"
>> </proposed 14.6.9>
>>
>> With the definition as it currently exists, select count(manager) =
=20
>> from Employee is not legal, but it certainly should be. It counts =
=20
>> the number of employees with non-null managers. And select count=
=20
>> (distinct dept.manager) from Employee counts the number of manager=
s.
>>
>>>
>>> An edge case to consider:  If null values are allowed as primary =
=20
>>> keys in an object using application identity, the likely SQL =
=20
>>> "select count(primary_key_column) from my_table" may incorrectly =
=20
>>> omit a valid instance, meaning that "SELECT COUNT(this) FROM =
=20
>>> MyClass" returns a different value than Collection.size() when =
=20
>>> invoked on "SELECT this FROM MyClass".  Acceptable?
>>
>>
>> Primary keys cannot be null. So I don't think this is an issue.
>>
>> Craig
>>
>>>
>>> Wes
>>>
>>> Craig L Russell wrote:
>>>
>>>> Javadogs,
>>>>
>>>> Please comment if you have any issues with the proposal.
>>>>
>>>> Issue 143
>>>> H
>>>> Treatment of null values in JDOQL COUNT JDOQL currently says =
=20
>>>> nothing about the treatment of null values in the COUNT clause =
=20
>>>> of a query. Based on the SQL treatment, and the fact that JDOQL =
=20
>>>> is intended to be executed by the back end datastore (see below)=
 =20
>>>> I propose adding this to the JDOQL chapter:
>>>>
>>>> <proposed>
>>>> If null values are aggregated, they do not participate in the =
=20
>>>> aggregate result. If all of the expressions to be aggregated =
=20
>>>> evaluate to null, the result is the same as if there were no =
=20
>>>> instances that match the filter.
>>>> </proposed>
>>>>
>>>> The behavior of aggregates is described in Part 2 of the ANSI =
=20
>>>> spec, section 10.9.
>>>>
>>>> 1) A null column is excluded from a COUNT( colName ) aggregate. =
=20
>>>> This is described in the section 10.9 under General Rules 4a. =
=20
>>>> The database is supposed to raise a warning: "warning--null =
=20
>>>> value eliminated in set function"
>>>>
>>>> 2) Unless you specify the DISTINCT keyword, the COUNT aggregate =
=20
>>>> will not filter out duplicates. Each row, regardless of whether =
=20
>>>> it is a duplicate, will go into the tally. This is described in =
=20
>>>> the same section underGeneral Rules 4b.
>>>>
>>>> For the record, Derby exhibits this ANSI behavior. To summarize:
>>>>
>>>> -- the following query eliminates rows with null in column "a"
>>>> select count( a ) from foo;
>>>>
>>>> -- the following query eliminates rows with null in column "a"
>>>> -- and eliminates duplicates
>>>> select count( distinct a ) from foo;
>>>>
>>>>
>>>> Craig Russell
>>>>
>>>> Architect, Sun Java Enterprise System http://java.sun.com/=20
>>>> products/jdo
>>>>
>>>> 408 276-5638 mailto:Craig.Russell@sun.com
>>>>
>>>> P.S. A good JDO? O, Gasp!
>>>>
>>>>
>>>
>>
>> Craig Russell
>>
>> Architect, Sun Java Enterprise System http://java.sun.com/products=
/=20
>> jdo
>>
>> 408 276-5638 mailto:Craig.Russell@sun.com
>>
>> P.S. A good JDO? O, Gasp!
>>
>>
>
>
> --=20
> Michael Bouschen=09=09Tech@Spree Engineering GmbH
> mailto:mbo.tech@spree.de=09http://www.tech.spree.de/
> Tel.:++49/30/235 520-33=09=09Buelowstr. 66=09=09=09
> Fax.:++49/30/2175 2012=09=09D-10783 Berlin=09=09=09
>

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jd=
o
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!


--Boundary_(ID_xjKCJiQPDiRj1Df3t0zXfQ)--