On Wed, 3 Oct 2001, Josh Berkus wrote:
> Joel,
>
> > A query such as "select sum(pages) from job_documents where
> > delivery_type='print'" returned 0 in version 7.0.3 if there were no
> > rows
> > matching the query. In 7.1.3 the result is NULL if no rows match the
> > query. Why the change? Which result is "correct" according to the
> > SQL
> > standard?
>
> While I wasn't aware of the change between versions, returning no rows
> is correct for any aggregate except COUNT, which returns 0. Although,
> now that you mention it, I'm not quite sure why that's the rule. I
> mean, shouldn't COUNT return no rows, too?
>
> Goes to show you that the SQL standard isn't even the model of perfect
> consistency ....
>
> BTW, returning no rows is somewhat different than returning NULL. WHat
> you should be seeing is:
Actually it seems to me that one NULL row is correct...
7.9 <query specification>1) Case: a) If T is not a grouped table, then Case: i) If the <select list> contains a <set
functionspecifica- tion> that contains a reference to a column of T or di- rectly contains a <set function
specification>that does not contain an outer reference, then T is the argument or argument source of each
such<set function specification> and the result of the <query specification> is a table con- sisting of 1
row.The i-th value of the row is the value specified by the i-th <value expression>.
