Mailing-List: contact jdo-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: jdo-dev@db.apache.org
Received-SPF: pass (asf.osuosl.org: local policy)
Date: Wed, 16 Nov 2005 16:51:22 -0800
From: Craig L Russell <Craig.Russell@Sun.COM>
Subject: Negative VOTE Issue 143: Aggregating null-valued expressions
Sender: Craig.Russell@Sun.COM
To: JDO Expert Group <jdo-experts-ext@Sun.COM>,
        Apache JDO project <jdo-dev@db.apache.org>
Message-id: <52E63249-B46E-4764-B62C-923DB281B0AA@sun.com>
MIME-version: 1.0
Content-type: multipart/alternative;
 boundary="Boundary_(ID_odV7E9INd+z86F1+VVYWBA)"

--Boundary_(ID_odV7E9INd+z86F1+VVYWBA)
Content-type: text/plain; format=flowed; delsp=yes; charset=US-ASCII
Content-transfer-encoding: 7BIT

Javadogs,

Please comment if you have any issues with the proposal.

Issue 143
H
Treatment of null values in JDOQL COUNT

JDOQL currently says nothing about the treatment of null values in  
the COUNT clause of a query. Based on the SQL treatment, and the fact  
that JDOQL is intended to be executed by the back end datastore (see  
below) I propose adding this to the JDOQL chapter:

<proposed>
If null values are aggregated, they do not participate in the  
aggregate result. If all of the expressions to be aggregated evaluate  
to null, the result is the same as if there were no instances that  
match the filter.
</proposed>

The behavior of aggregates is described in Part 2 of the ANSI spec,  
section 10.9.

1) A null column is excluded from a COUNT( colName ) aggregate. This  
is described in the section 10.9 under General Rules 4a. The database  
is supposed to raise a warning: "warning--null value eliminated in  
set function"

2) Unless you specify the DISTINCT keyword, the COUNT aggregate will  
not filter out duplicates. Each row, regardless of whether it is a  
duplicate, will go into the tally. This is described in 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/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!


--Boundary_(ID_odV7E9INd+z86F1+VVYWBA)--