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:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!
