Return-Path: 
 <jdo-dev-return-2199-apmail-db-jdo-dev-archive=www.apache.org@db.apache.org>
Delivered-To: apmail-db-jdo-dev-archive@www.apache.org
Received: (qmail 64635 invoked from network); 17 Nov 2005 16:15:21 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199)
  by minotaur.apache.org with SMTP; 17 Nov 2005 16:15:21 -0000
Received: (qmail 54396 invoked by uid 500); 17 Nov 2005 16:15:20 -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
Delivered-To: moderator for jdo-dev@db.apache.org
Received: (qmail 64153 invoked by uid 99); 17 Nov 2005 15:24:09 -0000
X-ASF-Spam-Status: No, hits=0.0 required=10.0
	tests=
X-Spam-Check-By: apache.org
Received-SPF: pass (asf.osuosl.org: local policy)
Message-ID: <437CA07C.2000007@tralfamadore.com>
Date: Thu, 17 Nov 2005 15:23:40 +0000
From: Wes Biggs <wes@tralfamadore.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
 rv:1.7.3) Gecko/20040910
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: JDO Expert Group <jdo-experts-ext@Sun.COM>
CC: Apache JDO project <jdo-dev@db.apache.org>
Subject: Re: Negative VOTE Issue 143: Aggregating null-valued expressions
References: <52E63249-B46E-4764-B62C-923DB281B0AA@sun.com>
In-Reply-To: <52E63249-B46E-4764-B62C-923DB281B0AA@sun.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Checked: Checked by ClamAV on apache.org
X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N

Quite possibly my foot will soon be in my mouth, but in the PFD 14.6.9, 
the target expression for count must be "this" or a variable name (well, 
it says "can" -- am I reading this incorrectly?).  If this is truly a 
restriction, I do not see the relevance of this addition, which only 
makes sense when talking about counting field expressions or the results 
of calculations.

An edge case to consider:  If null values are allowed as primary keys in 
an object using application identity, the likely SQL "select 
count(primary_key_column) from my_table" may incorrectly omit a valid 
instance, meaning that "SELECT COUNT(this) FROM MyClass" returns a 
different value than Collection.size() when invoked on "SELECT this FROM 
MyClass".  Acceptable?

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