db-jdo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Wes Biggs <...@tralfamadore.com>
Subject Re: Negative VOTE Issue 143: Aggregating null-valued expressions
Date Thu, 17 Nov 2005 15:23:40 GMT
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?


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!

View raw message