db-jdo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: Negative VOTE Issue 143: Aggregating null-valued expressions
Date Thu, 17 Nov 2005 17:08:33 GMT
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  
> 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.

I think the description in the specification is too narrow; my mistake.

<spec 14.6.9>
count(<expression>): the count of the number of instances of this  
expression is returned; the expression can be “this” or a variable 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  
expression is returned; the expression is preceded by an optional  
"distinct" followed by “this”, a navigational expression that  
terminates in a single-valued field, or a variable name
sum(<numeric field expression>): the sum of field expressions is  
returned; the expression is preceded by an optional "distinct"
min(<field expression>): the minimum value of the field expressions  
is returned; the expression is preceded by an optional "distinct"
max(<field expression>): the maximum value of the field expressions  
is returned; the expression is preceded by an optional "distinct"
avg(<numeric field expression>): the average value of all field  
expressions is returned; the expression is preceded by an optional  
"distinct"
</proposed 14.6.9>

With the definition as it currently exists, select count(manager)  
from Employee is not legal, but it certainly should be. It counts the  
number of employees with non-null managers. And select count(distinct  
dept.manager) from Employee counts the number of managers.
>
> 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?

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

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!


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message