Re: 7.0.3 and 7.1.3 different results?

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